Question

4,568,199 30.00% Sample Question 5 (x marks) (a) From the extracts of the company's Year ending 30-Jun-13 financial statements, work out last year's Cash $11,345,576 (year ending 30 June 2013) free cash flow and Other assets $154,269,130 Number of shares on issue display the FCF in cell C23. Debt and liabilities $57,546,119 Company tax rate Equity (book value) $108,068,5871 (b) Apply the annual growth rates in cells D22 to G22 to project the free cash flows in cells Year ending 30-Jun-13 Growth rate D23 to G23. Sales revenue $84,326,549 Operating expenses -$41,749,655 (c) In cell F24, work out the Terminal Value in Depreciation -$14,891,032 the year to June 2016, assuming constant EBIT growth thereafter. Interest paid $6,356,917 Tax payable (d) From the FCFs and Terminal Value, work Net income out the the value per share of the equity in this company and display this is cell C33. Capital expenditure $14,685,050 Increase in working capital $2,989,184 FCF in cell C23 --- 30-Jun-13 30-Jun-14 5.50% 30-Jun-15 7.00% 30-Jun-16 8.50% 30-Jun-17 4.25% and thereafter Year ending Growth rate in FCFS Free Cash Flows (FCFS) Terminal value FCF4+1 (WACC - 84+1) Weighted average cost of capital 12.65% per annum Enterprise value <-- Cash $11,345,576 <-- =C4 Debt and liabilities -$57,546,119 <----C6 Equity (market value) Number of shares 4,568,199 <-- =G5 Value per share 4-

Please show how to find the values for the yellow cells in excel with the excel functions required to get the answer. thanks

Answer 1

FCF	EBIT - Taxes + Depreciation & Amortization - Capex – Change in Working Capital,
Sales	$    84,326,549.00
Opex	$ (41,749,655.00)
dep	$ (14,891,032.00)
EBIT	$    27,685,862.00
int paid	$    (6,356,917.00)
EBT	$    21,328,945.00
tax @ 30%	$       6,398,683.50
Net Income	$    14,930,261.50
Capex	$    14,685,050.00
Inc in Working capital	$       2,989,184.00
	30-Jun-13	30-Jun-14	30-Jun-15	30-Jun-16	30-Jun-17
Growth rate		5.50%	7%	8.50%	4.25%
FCF	$    18,503,976.50	$ 19,521,695.21	$ 20,888,213.87	$    22,663,712.05	$ 23,626,919.81
Terminal Value				$ 281,272,854.92
discount factor		0.887705282	0.788020667	0.699530109
PV	$    18,503,976.50	$ 17,329,511.95	$ 16,460,344.24	$ 212,612,779.72
Total	$ 264,906,612.41
WACC	12.65%
No of shares	4568199
Value per share	$                     57.99
cash	$    11,345,576.00
Debt	$ (57,546,119.00)
MC=	no of shares*price per share
MC=	$ 264,906,612.41
EV	​EV=MC+Total Debt−C
	where MC = Market capitalization
	C= cash and cash equivalent
EV	$ 311,107,155.41