University of Massachusetts Boston Financial Modeling Valuation Models Excel Task

FIRM AND PROJECT VALUATION
Five Equivalent Methods
Inputs
Valuation Object
Valuation Object
Date 0 Proj Investment or Firm Cap
Tax Rate
Unlevered Cost of Equity Capital
Riskfree Rate=Cost of Riskfree Debt
Infinte Horizon Growth Rate
Firm
Project
1
$340.00
1.0%
4.4%
5.5%
8.6%
Using Up/Down arrows Update values from Exibit 3
Infinite Horizon
Include Infinite Horizon?
Yes
No
1
Cash Flows
Date
Revenues
Expenses
Gross Earnings
Depreciation
Earnings Bef Interest & Tax (EBIT)
Interest Expense
Earnings Before Tax
Taxes
Earnings
Add Back Depreciation
Cash Flow from Operations
New Invest in Plant and Equipment
After-Tax Salvage Value
New Invest in Working Capital
Cash Flows from Investments
New Borrowing (Repayment)
Free Cash Flow to Equity (FCFE)
0
1
$440.00
$180.00
First Stage: Finite Horizon
2
3
4
$740.00
$660.00
$825.00
$505.00
$735.00
$340.00
2nd Stage:
Infin Horiz
5
6
$565.00
$690.00
$650.00
$285.00
$10.00
$110.00
$10.00
$20.00
$130.00
$140.00
$5.50
$5.56
$5.94
$6.00
$6.49
$6.60
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$10.00
$110.00
$10.00
$20.00
$130.00
$140.00
Show your formula
Show your formula
Show your formula
Show your formula
Show your formula
($73.00)
($53.00)
($74.00)
($56.00)
($165.79)
($13.00)
($69.00)
($70.00)
$0.00
($13.00)
($83.00)
($13.00)
($86.00)
($13.00)
($66.00)
($13.00)
($87.00)
$1.00
$7.00
$1.00
$9.00
$2.00
$10.32
($85.00)
($59.00)
($86.00)
($60.00)
($81.00)
($168.47)
($13.00)
($178.79)
= Dividends
($85.00)
($59.00)
($86.00)
($60.00)
($81.00)
($168.47)
Interest
Less New Borrowing (Repayment)
Cash Flow to Debtholders (CFD)
$5.50
($1.00)
$4.50
$5.56
($7.00)
($1.45)
$5.94
($1.00)
$4.94
$6.00
($9.00)
($3.01)
$6.49
($2.00)
$4.49
$6.60
($10.32)
($3.72)
Tax Shield Benefit
Free Cash Flow to the Firm (FCFF)
$0.06
($80.56)
$0.06
($60.50)
$0.06
($81.12)
$0.06
($63.06)
$0.06
($76.57)
$0.07
($172.25)
Alternative Way to get FCFF
Earnings
After-tax Interest Expense
Net Oper. Profit After Tax (NOPAT)
Depreciation
Cash Flows from Investments
Free Cash Flow to the Firm (FCFF)
$0.00
$5.45
$5.45
$10.00
($86.00)
($70.56)
$0.00
$5.50
$5.50
$110.00
($66.00)
$49.50
$0.00
$5.88
$5.88
$10.00
($87.00)
($71.12)
$0.00
$5.94
$5.94
$20.00
($69.00)
($43.06)
$0.00
$6.43
$6.43
$130.00
($83.00)
$53.43
$0.00
$6.53
$6.53
$140.00
($178.79)
($32.25)
$101.00
$315.00
$416.00
$108.00
$264.00
$372.00
$109.00
$340.00
$449.00
$118.00
$380.00
$498.00
$120.00
$331.00
$451.00
$5.45
$14.93
($9.48)
$5.50
$18.27
($12.77)
$5.88
$16.33
($10.45)
$5.94
$19.72
($13.78)
$6.43
$21.87
($15.45)
1
($70.56)
$635.69
$0.06
($1.11)
First Stage: Finite Horizon
2
3
4
$49.50
($71.12)
($43.06)
$614.17
$712.31
$786.71
$0.06
$0.06
$0.06
($1.21)
($1.32)
($1.44)
Debt (D)
Book Value of Equity
Total Capital
$100.00
$240.00
$340.00
Economic Profit
Net Oper. Profit After Tax (NOPAT)
Capital Charge
Economic Profit
(1.) Adjusted Present Value (APV)
Date
Free Cash Flow to the Firm (FCFF)
Value of the Unlevered Firm
Tax Shield Benefit
Value of the Tax Shield Benefit
Value of the Firm (APV Method)
0
$541.32
($1.01)
$540.31
5
$53.43
$767.90
$0.06
($1.57)
$6.53
$19.81
($13.27)
2nd Stage:
Infin Horiz
6
($32.25)
$0.07
– Date 0 Firm Capital
Value Added by Firm (APV Method)
$340.00
$200.31
(2) Free Cash Flow to Equity (FCFE)
Date
Debt + Equity (D+E)
Equity (E)
Levered Cost of Equity Capital
Free Cash Flow to Equity (FCFE)
Value of Equity (E)
Value of Debt (D)
Value of the Firm (FCFE Method)
– Date 0 Firm Capital
Value Added by Firm (FCFE Method)
0
$540.31
$440.31
$2,787.49
$100.00
$2,887.49
$340.00
$2,547.49
1
$634.59
$533.59
4.15%
($85.00)
$2,988.18
$101.00
First Stage: Finite Horizon
2
3
4
$612.95
$710.98
$785.27
$504.95
$601.98
$667.27
4.19%
4.16%
4.20%
($59.00)
$3,172.44
$108.00
($86.00)
$3,390.56
$109.00
($60.00)
$3,592.99
$118.00
5
$766.33
$646.33
4.21%
($81.00)
$3,825.10
$120.00
(3) Free Cash Flow to the Firm (FCFF)
Date
Equity Weight (E / (D+E))
Debt Weight (D / (D+E))
Cost of Firm Capital (WACC)
Free Cash Flow to the Firm (FCFF)
Value of the Firm (FCFF Method)
– Date 0 Firm Capital
Value Added by Firm (FCFF Method)
0
81.5%
18.5%
$540.31
$340.00
$200.31
1
84.1%
15.9%
4.39%
($70.56)
$634.59
First Stage: Finite Horizon
2
3
4
82.4%
84.7%
85.0%
17.6%
15.3%
15.0%
4.39%
4.39%
4.39%
$49.50
$612.95
($71.12)
$710.98
($43.06)
$785.27
(4) Dividend Discount Model (DDM)
First Stage: Finite Horizon
5
84.3%
15.7%
4.39%
$53.43
$766.33
2nd Stage:
Infin Horiz
6
4.20%
($168.47)
2nd Stage:
Infin Horiz
6
4.39%
($32.25)
2nd Stage:
Infin Horiz
Year
Dividend
Value of Equity (E)
Value of Debt (D)
Value of the Firm (DDM Method)
– Date 0 Firm Capital
Value Added by Firm (DDM Method)
0
$2,787.49
$100.00
$2,887.49
$340.00
$2,547.49
1
($85.00)
$2,988.18
$101.00
2
($59.00)
$3,172.44
$108.00
3
($86.00)
$3,390.56
$109.00
4
($60.00)
$3,592.99
$118.00
(5) Residual Income (RI)
Year
Economic Profit
Economic Profit on Salvage Value
Value of the Economic Profit
+ Date 0 Book Value of the Firm
Value of the Firm (RI Method)
– Date 0 Firm Capital
Value Added by Firm (RI Method)
0
$200.31
$340.00
$540.31
$340.00
$200.31
1
($9.48)
$218.59
First Stage: Finite Horizon
2
3
4
($12.77)
($10.45)
($13.78)
$240.95
$261.98
$287.27
5
($81.00)
$3,825.10
$120.00
6
($168.47)
2nd Stage:
Infin Horiz
5
6
($15.45)
($13.27)
$0.00
$315.33
Do not delete
4
1
44
55
86
29
27
55
57
44
79
57
37
29
68
40
31
45
55
45
46
57
58
37
57
36
54
47
47
47
47
40
50
47
47
46
52
46
54
47
35

Purchase answer to see full
attachment