Calculate free cash flows to the firm using the forecasted financial statements provided. Calculate the terminal value using the growing perpetuity formula. What is the enterprise value of the firm’s operations?

WACC for Target as of January 2020

Key assumptions: Market risk premium of 5%, tax rate of 21%

The spreadsheet template includes financial data on the (1) January 2020 treasury curve, (2) Target’s income statement, (3) Target’s balance sheet, (4) Target’s recent equity returns and January 2020 market capitalization, and (5) Target’s debt structure and credit rating.

a) Compute the equity beta for Target using the recent returns.

b) Compute the cost of equity using CAPM.

c) Compute the cost of debt, using both the average cost of the firm’s existing debt method and the yield-to-maturity method. Decide which of these methods is more appropriate for the ultimate cost of debt input to WACC – place this estimate in cell B23.

d) Compute the WACC.

Free Cash Flows and Valuation

Assumptions
Tax Rate	21%
WACC	7%
Terminal Growth Rate	2%

Income Statement
	t = 0	Forecast t = 1	Forecast t = 2	Forecast t = 3	Forecast t = 4
Sales	$5,000.00	$5,500.00	$5,940.00	$6,296.40	$6,611.22
COGS	($4,500.00)	($4,950.00)	($5,286.60)	($5,603.80)	($5,817.87)
EBITDA	$500.00	$550.00	$653.40	$692.60	$793.35
Depreciation Expense	($175.00)	($187.18)	($211.24)	($234.33)	($256.67)
EBIT	$325.00	$362.82	$442.16	$458.28	$536.68
Interest Expense	($125.00)	($125.00)	($125.00)	($125.00)	($125.00)
Profit before tax	$200.00	$237.82	$317.16	$333.28	$411.68
Tax	($42.00)	($49.94)	($66.60)	($69.99)	($86.45)
Net Income	$158.00	$187.88	$250.56	$263.29	$325.23
Dividends	($15.80)	($18.79)	($25.06)	($26.33)	($32.52)
Retained Earnings	$142.20	$169.09	$225.50	$236.96	$292.70

Balance Sheet
	t = 0	Forecast t = 1	Forecast t = 2	Forecast t = 3	Forecast t = 4
Cash	$650.00	$494.09	$433.59	$438.89	$526.96
Other Current Assets	$500.00	$550.00	$594.00	$629.64	$661.12
Fixed Assets
Gross PPE	$3,500.00	$3,987.18	$4,462.42	$4,910.58	$5,356.14
Accumulated Depreciation	$500.00	$687.18	$898.42	$1,132.74	$1,389.41
Net PPE	$3,000.00	$3,300.00	$3,564.00	$3,777.84	$3,966.73
Total Assets	$4,150.00	$4,344.09	$4,591.59	$4,846.37	$5,154.82
Current Liabilities	$250.00	$275.00	$297.00	$314.82	$330.56
Debt	$2,500.00	$2,500.00	$2,500.00	$2,500.00	$2,500.00
Common Stock	$100.00	$100.00	$100.00	$100.00	$100.00
Additional Paid-in-Capital	$1,000.00	$1,000.00	$1,000.00	$1,000.00	$1,000.00
Accumulated Retained Earnings	$300.00	$469.09	$694.59	$931.55	$1,224.26
Total Liabilities and Equity	$4,150.00	$4,344.09	$4,591.59	$4,846.37	$5,154.82

Firm Free Cash Flows	t = 0	Forecast t = 1	Forecast t = 2	Forecast t = 3	Forecast t = 4
Net Income
+Interest Expense
-Tax rate * Interest Expense
Net operating profit after taxes (NOPAT)
+Depreciation Expense
-Capital Expenditures
-Change in Net Working Capital
FCF to Firm						#N/A
Terminal Value (t=4 value)						#N/A

PV(Explicit Forecast FCFs)		#N/A
PV(Terminal Value)		#N/A
Enterprise Value		#N/A

Weighted Average Cost of Capital Calculations

	Dollar Value	Weight
Equity Value
Debt Value
Firm Value

Tax Rate	21%
Cost of Equity
Risk-free Rate		#N/A
Equity Beta		#N/A
Market Risk Premium	5%
Cost of Equity		#N/A

Cost of Debt
Average Cost Method		#N/A
Yield-to-Maturity Method		#N/A
Cost of Debt Input to WACC		#N/A
WACC		#N/A

Treasury Yield Curve

Source: https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yieldYear&year=2020
Date	1 mo	2 mo	3 mo	6 mo	1 yr	2 yr	3 yr	5 yr	7 yr	10 yr	20 yr	30 yr
1/2/2020	1.53	1.55	1.54	1.57	1.56	1.58	1.59	1.67	1.79	1.88	2.19	2.33
1/3/2020	1.52	1.55	1.52	1.55	1.55	1.53	1.54	1.59	1.71	1.8	2.11	2.26
1/6/2020	1.54	1.54	1.56	1.56	1.54	1.54	1.56	1.61	1.72	1.81	2.13	2.28
1/7/2020	1.52	1.53	1.54	1.56	1.53	1.54	1.55	1.62	1.74	1.83	2.16	2.31
1/8/2020	1.5	1.53	1.54	1.56	1.55	1.58	1.61	1.67	1.78	1.87	2.21	2.35
1/9/2020	1.53	1.55	1.54	1.56	1.54	1.58	1.59	1.65