Question

Attached is a spreadsheet showing the most recent financial statements for Wall Enterprises. Please do your calculations on the spreadsheet. Using the percent of sales method forecast the financial statements for the next three years. Sales are anticipated to grow by 10%, 8%, and 5% thereafter. The WACC is 9%. Balance the balance sheets by using the Line of Credit or adding a Marketable Securities line if needed. The interest rate on all debt is 8% and is based on the debt outstanding at the end of the prior year. Dividends are forecast to grow by 10% each year. No additional long-term debt or commons stock will be sold. Make a note of anything you assumed in your forecast. Calculate the FCF and the terminal value to determine the value of the company. Given the 10 million shares outstanding what is the implied stock price? If an investor is willing to pay $250 million for 25% of the company, assuming a terminal value of 8X EBIT in 2019, (ignore the DCF calculations you just did) what return on investment does that provide? Is that likely to be an acceptable investment to the investor?

Wall Enterprises
Balance Sheet 12/31/16		(In millions)
		2016
Cash		$                     20
Accounts receivable		280
Inventory		400
Total Current Assets		$                  700
Net fixed Assets		500
Total Assets		$               1,200
Accounts payable		$                     80
Line of credit		0
Total Current Liabilites		$                     80
Long-term Debt		500
Total Liabilites		$                  580
Common Stock		420
Retained Earnings		200
Total Stockholders Equity		620
Total Liabilites and Equity		$               1,200
Income Statement Year Ending 2016			(In millions)
Sales		$               2,000
Operating Costs		1,800
Depreciation		50
EBIT		$                  150
Interest		40
EBT		$                  110
Taxes (40%)		44
Net Income		$                     66
Dividends		20
Addition to RE		$                     46
Common Shares		10

Answer 1

Share Price:
WACC=9%
WACC=Wd*Rd*(1-t)+We*Re
9%=.45*8%*(1-.4)+.55*Re
Re=(9%-.45*8%*.6)/.55
12.44%
	Amount	W
Debt	500	45%
Equity	620	55%
	1,120	100%
P0=D0(1+g)/r-g
P0=2*(1+.1)/(.1244-.1)
P0	90.16	per share
Share price at the end of 2019
WACC=9%
WACC=Wd*Rd*(1-t)+We*Re
9%=.38*8%*(1-.4)+.62*Re
Re=(9%-.38*8%*.6)/.62
11.57%
	Amount	W
Debt	500	38%
Equity	809	62%
	1,309	100%
P0=D0(1+g)/r-g
P0=2.66*(1+.1)/(.1157-.1)
P0	186.37	per share
Pv of the shares	186.37/(1+.09)^3
Pv of the shares	144
EBIT	199
Terminal value	1592	Million
25% terminal value	398	Million
Investor willing to pay	250	Million
So not acceptable

Formula spread sheets