Question

Consider the following income statement

                                            

Net Sales                        2,600.00

Cost of Goods Sold        -1,400.00

SG&A Expenses               -400.00

Depreciation                 -150.00

Other Operating Expenses -100.00

Operating income               550.00

Interest Expenses          -200.00

Income Before Tax             350.00

Income Tax (25%)          -140.00

Net Profit After Taxes      210.00

1. Reformat it as a common size income statement
2. Calculate the net profit margin and gross profit margin
3. The company has a $15 Capex and $10 change in net working capital. Calculate the FCF.
4. Calculate the value of the firm if the required rate of return is 3% and the growth rate is 1.3%
5. Calculate the NOPAT
6. The company`s WACC is 4.2% and invested capital $500. Calculate the EVA
7. Calculate the EVA Return on Capital
8. The firm trades at $22 per share and has 150 outstanding shares. Calculate the MVA

Please show it in Excel, thanks!

Answer 1

	common Size income statement
1-	Inome statement		% of net sales =Individual value/total of net sales
	Net Sales	2,600.00	100.00%
	Cost of Goods Sold	1,400.00	53.85%
	SG&A Expenses	400	15.38%
	Depreciation	150	5.77%
	Other Operating Expenses	100	3.85%
	Operating income	550	21.15%
	Interest Expenses	200	7.69%
	Income Before Tax	350	13.46%
	Income Tax (25%)	140	5.38%
	Net Profit After Taxes	210	8.08%
2-	Gross profit Margin = gross profit/net sales	(net sales-cost of goods sold)/net sales	(2600-1400)/2600	0.461538462
	net profit margin = net profit/sales	210/2600		8.08%
3-	Free cash flow = net profit+depreciation-changes in working capital-capital expenditure	210+150-10-15		335
4-	value of firm = (expected FCF)/(required rate of return-growth rate)	(339.355/(3%-1.3%))		19962.05882
	expected FCF = current FCF*(1+g)^n	335*1.013	339.355
5-	NOPAT =Operating profit-tax	550-140	410
6-	EVA = NOPAT-(WACC*invested capital)	410-(500*4.2%)	389
7-	return on capital	net profit/total capital invested	210/500	42.00%
8-	MVA = NOPAT-(market value*WACC)	410-(3300*4.2%)		271.4
	market value	150*22