PLEASE USE EXCEL FUNCTIONS ONLY AND DESCRIBE THE METHOD FOR UNDERSTANDING PURPOSES!, THANKS!

PLEASE USE EXCEL FUNCTIONS ONLY AND DESCRIBE THE METHOD FOR UNDERSTANDING PURPOSES!, THANKS!

5. Refer to the table containing a company's financial statements below: 4,320,601 30.00% Year ending Cash Other assets Debt and liabilities Equity (book value) 30-Jun-18 Number of shares on issue $6,105,465 Company tax rate $87,554,597 Weighted average cost of capital $32,190,949 Growth rate in FCFs from 2022 $61,469,113 11.44% per annum 5.48% per annum Year ending Sales revenue Operating expenses Depreciation EBIT Interest paid Tax payable Net income Growth 30-Jun-18 rate $78,528,535 3.20% $45,500,703 2.70% $22,321,855 4.10% $10,705,977 $3,908,199 7.30% $2,039,333 $4,758,445 30-Jun-19 30-Jun-20 30-Jun-21 30-Jun-22 $81,041,448 $83,634,774 $86,311,087 $89,073,042 $46,729,222 $47,990,911 $49,286,666 $50,617,406 $23,237,051 $24,189,770 $25,181,551 $26,213,994 $11,075,175 $11,454,093 $11,842,871 $12,241,642 $4,193,498 $4,499,623 $4,828,095 $5,180,546 $2,064,503 $2,086,341 $2,104,433 $2,118,329 $4,817,174 $4,868,129 $4,910,343 $4,942,767 Capital expenditure $16,170,094 6.00% Increase in working capital $2,551,414 2.50% $17,140,300 $18,168,718 $19,258,841 $20,414,371 $2,615,199 $2,680,579 $2,747,594 $2,816,284 (a) Work out last year's (year ending 30 June 2018) free cash flows and the projected free cash flows (30 June 2019 - 30 June 2022) from these extracts from the company's financial statements. (3 marks) (b) Work out the Terminal Value of the free cash flows in 30 June 2021, using constant growth rate from 2022. (2 marks) (c) Work out the enterprise value from the free cash flows and the terminal value. (2 marks) (d) Display the value per share of the equity in this company. (2 marks) (e) Recalculate the value per share, based on mid-year discounting (with a nominal conversion of the annual discount rate) of the free cash flows. (1 mark) 5. Refer to the table containing a company's financial statements below: 4,320,601 30.00% Year ending Cash Other assets Debt and liabilities Equity (book value) 30-Jun-18 Number of shares on issue $6,105,465 Company tax rate $87,554,597 Weighted average cost of capital $32,190,949 Growth rate in FCFs from 2022 $61,469,113 11.44% per annum 5.48% per annum Year ending Sales revenue Operating expenses Depreciation EBIT Interest paid Tax payable Net income Growth 30-Jun-18 rate $78,528,535 3.20% $45,500,703 2.70% $22,321,855 4.10% $10,705,977 $3,908,199 7.30% $2,039,333 $4,758,445 30-Jun-19 30-Jun-20 30-Jun-21 30-Jun-22 $81,041,448 $83,634,774 $86,311,087 $89,073,042 $46,729,222 $47,990,911 $49,286,666 $50,617,406 $23,237,051 $24,189,770 $25,181,551 $26,213,994 $11,075,175 $11,454,093 $11,842,871 $12,241,642 $4,193,498 $4,499,623 $4,828,095 $5,180,546 $2,064,503 $2,086,341 $2,104,433 $2,118,329 $4,817,174 $4,868,129 $4,910,343 $4,942,767 Capital expenditure $16,170,094 6.00% Increase in working capital $2,551,414 2.50% $17,140,300 $18,168,718 $19,258,841 $20,414,371 $2,615,199 $2,680,579 $2,747,594 $2,816,284 (a) Work out last year's (year ending 30 June 2018) free cash flows and the projected free cash flows (30 June 2019 - 30 June 2022) from these extracts from the company's financial statements. (3 marks) (b) Work out the Terminal Value of the free cash flows in 30 June 2021, using constant growth rate from 2022. (2 marks) (c) Work out the enterprise value from the free cash flows and the terminal value. (2 marks) (d) Display the value per share of the equity in this company. (2 marks) (e) Recalculate the value per share, based on mid-year discounting (with a nominal conversion of the annual discount rate) of the free cash flows. (1 mark)