Please explain to me how this correct answer was found. I have the problem posted with the correct answers and work given but it does not show me how everything was calculated. I would appreciate somebody does this in excel showcasing the formulas, thanks.

Problem 8-10

You are building a free cash flow to the firm model. You expect sales to grow from $2 billion for the year that just ended to $4 billion five years from now. Assume that the company will not become any more or less efficient in the future. Use the following information to calculate the value of the equity on a per-share basis.

1. Assume that the company currently has $720 million of net PP&E.
2. The company currently has $240 million of net working capital.
3. The company has operating margins of 10 percent and has an effective tax rate of 31 percent.
4. The company has a weighted average cost of capital of 8 percent. This is based on a capital structure of two-thirds equity and one-third debt.
5. The firm has 1 million shares outstanding.

Do not round intermediate calculations. Round your answer to the nearest cent.

$

Hide Feedback

Incorrect

Post Submission Feedback

Solution

Sales (millions)	$2,000
Growth for 5 years = (4/2)1/5-1	14.87%
Operating margins	10%
Tax rate	31%
WACC	8%
PP&E/Sales ($720/$2,000)	36%
NWC/Sales ($240/$2,000)	12%

($ millions)

Year	1		2		3		4		5		6
Sales	$2,297.40		$2,639.02		$3,031.43		$3,482.20		$4,000.00		$4,594.79
EBIT	$229.74		$263.90		$303.14		$348.22		$400.00		$459.48
Taxes	-$71.22		-$81.81		-$93.97		-$107.95		-$124.00		-$142.44
NOPAT	$158.52		$182.09		$209.17		$240.27		$276.00		$317.04
Net Cap Ex	-$107.06		-$122.98		-$141.27		-$162.28		-$186.41		-$214.13
Change in NWC	-$35.69		-$40.99		-$47.09		-$54.09		-$62.14		-$71.38
FCF	$15.77		$18.11		$20.81		$23.90		$27.46		$31.54
Terminal Value									$394.25
PV of Cash Flows	$14.60		$15.53		$16.52		$17.57		$287.01
EV	$351.23
Less Debt (1/3 of EV)	-$117.08
Equity Value	$234.15

Equity Value per Share = $234.15 million / 1 million shares = $234.15

Note: While the calculations above show values rounded to 2 decimal places, unrounded values should be used to calculate the required values.