WACC ASSIGNMENT

WACCwithBalanceSheets1.xlsm

FinancialStatements

Balance Sheet (End of December in Millions of Dollars)
Assets	2018	2017	2016	Liabilities & Equity	2018	2017	2016
Cash	$3,146.0	$2,144.0	$2,300.0	Accounts payable	$1,189.0	$1,149.0	$1,077.0
Short-term investments	$0.0	$0.0	$0.0	Accruals	$1,129.0	$1,039.0	$1,166.0
Receivables	$2,261.0	$2,807.0	$2,000.0	Current liabilities	$2,318.0	$2,188.0	$2,243.0
Inventories	$2,132.0	$2,076.0	$1,927.0	Short-term debt	$995.0	$996.0	$45.0
Total current assets	$7,539.0	$7,027.0	$6,227.0	Total current liabilities	$3,313.0	$3,184.0	$2,288.0
				Long-term debt	$5,037.0	$4,908.0	$4,835.0
Net fixed assets	$1,516.0	$1,720.0	$1,531.0	Total liabilities	$8,350.0	$8,092.0	$7,123.0
				Common stock	$105.0	$105.0	$105.0
				Retained earnings	$600.0	$550.0	$530.0
				Total common equity	$705.0	$655.0	$635.0
Total assets	$9,055.0	$8,747.0	$7,758.0	Total liab. & equity	$9,055.0	$8,747.0	$7,758.0
Income Statement	2018	2017	2016	Step 1: Input the following for common stock
Net sales	$13,198.0	$12,397.0	$12,575.0	Price per share →	$20.00
Operating costs	$7,750.0	$7,108.0	$7,184.0	Shares outstanding →	10	(in millions)
EBITDA	$5,448.0	$5,289.0	$5,391.0
Deprec. & amort.	$2,300.0	$2,200.0	$2,100.0
EBIT	$3,148.0	$3,089.0	$3,291.0
Interest expense	$233.0	$248.0	$295.0
EBT	$2,915.0	$2,841.0	$2,996.0
Taxes	$503.0	$502.0	$476.0
Common div.	$10.0	$10.0	$10.0

Clear All

Step 1: Click here to find book value

Step 2: Click here to find market value

Step 3: Click here to find the weights of debt and equity based upon book value and market value

Calculating Weighted Average Cost of Capital (WACC)
	2018	2017	2016
Short-term debt	$995.0	$996.0	$45.0
Long-term debt	$754.0	$580.0	$520.0
Interest expense	$233.0	$248.0	$295.0
Income before taxes	$2,915.0	$2,841.0	$2,996.0
Income tax expense	$503.0	$502.0	$476.0
Dividend (most recent)	$1.0
Current stock price, P	$20.00
Average shares outstanding	10.00
Growth rate, g	8.0%
Risk-free rate, rRF	0.25%
Company risk, β	1.60
Market return, rM	7.00%
	Book Value	Market Value
Weight of debt, wd	ERROR:#REF!	ERROR:#REF!
Weight of equity, we	ERROR:#REF!	ERROR:#REF!

Step 1: Click here to calculate the before-tax and after-tax cost of debt

Step 2: Click here to calculate the cost of equity (CAPM)

Step 3: Click here to calculate the cost of equity (dividend growth)

Step 4: Click here to calculate WACC using book and market weights

Click here to clear the WACC calculations

Click here to carry over the weights of debt and equity before proceeding

Inputs
Step 1: Calculate the after-tax cost of debt, rD(1-Tc)
	Base Case
rD	0.0%	14.0%
Tc	0.0%	29.0%
Step 2a: Calculate the cost of equity (rE) using CAPM			Output
	Base Case				Base Case
Risk-free rate, rRF	0.25%	0.25%	After-tax cost of debt	rD(1-Tc)	0.0%
Market risk, β	1.60	1.00	Cost of equity (CAPM)	rRF + β(rM-rRF)	11.1%
Market rate, rM	7.00%	7.00%	Cost of equity (Dividend growth)	(D1/P) + g	13.0%
				WACC-CAPM	ERROR:#REF!
Step 2b: Calculate the cost of equity (rE) with dividend growth model				WACC- DIV	ERROR:#REF!
	Base Case
Dividend (most recent)	$ 1.00	$1.20
Current stock price, P	$20.00	$20.00
Growth rate, g	8.0%	15.00%
Step 3: Percentage of debt and equity (market value) in capital structure
	Base Case
wD	ERROR:#REF!	43.0%
wE	ERROR:#REF!	57.0%
Sum of weights	ERROR:#REF!	100.0%

Click here to calculate WACC

Clear All

	Spinner
rD	14
Tc	29
rRF	25
β	10
rM	700
Most recent annual dividend (D1)	$12.00
Current stock price (P)	$200.00
Growth rate (g)	150
wD	0	4300.00%	0
wE

Balance Sheet (End of December in Millions of Dollars)								Investor Supplied Capital
Assets	31-Dec-11	31-Dec-10	31-Dec-09	Liabilities & Equity	31-Dec-11	31-Dec-10	31-Dec-09	Book Value	% of Total	Market Value	% of Total
Cash	10	15	25	Accounts payable	$60	$30	$20
Short-term investments	0	65	0	Accruals	$140	$130	$150
Receivables	$335	275	360	Current liabilities	$200	$160	$170
Inventories	$615	415	310	Short-term debt	$110	$60	$100	$110		$110
Total current assets	$960	$770	$695	Total current liabilities	$310	$220	$270
				Long-term debt	$754	$580	$520	$754		$754
Net fixed assets	$1,000	$870	$925	Total liabilities	$1,064	$800	$790	$864	49.1%	$864	46.4%
				Common stock	$130	$130	$130	$130
				Retained earnings	$766	$710	$700	$766
				Total common equity	$896	$840	$830	$896	50.9%	$1,000	53.6%
Total assets	$1,960	$1,640	$1,620	Total liab. & equity	$1,960	$1,640	$1,620	$1,760		$1,864
Income Statement	31-Dec-11	31-Dec-10	31-Dec-09	Step 1: Input the following for common stock
Net sales	$3,000.0	$2,850.0	$2,250.0	Price per share →	$20.00
Operating costs	$2,616.2	$2,497.0	$1,887.0	Shares outstanding →	50
EBITDA	$383.8	$353.0	$363.0
Deprec. & amort.	$100.0	$90.0	$80.0						Book Value	Market Value
EBIT	$283.8	$263.0	$283.0					Weight of debt, wd	49.1%	46.4%
Interest expense	$88.0	$60.0	$65.0					Weight of equity, we	50.9%	53.6%
EBT	$195.8	$203.0	$218.0
Taxes	$52.7	$59.9	$65.6
Common div.	$60.0	$53.0	$59.5