| SCENARIO A |
|
|
|
|
|
|
| FREE-CASH-FLOW VALUATION OF EQUITY |
MAKE ENTRIES IN BLUE-COLORED CELLS |
|
| Assumptions: |
|
|
|
|
|
|
| PERIOD |
0 |
1 |
2 |
3 |
4 |
5 |
| YEAR |
2003 |
2004 |
2005 |
2006 |
2007 |
2008 |
| Profit from operations (EBIT) |
|
115.0 |
138.0 |
165.0 |
199.0 |
238.0 |
| Income tax rate |
|
35.0% |
35.0% |
35.0% |
35.0% |
35.0% |
| Depreciation & amortization expense |
|
15.0 |
16.0 |
16.0 |
17.0 |
17.0 |
| Net working capital from balance sheet forecast |
481.0 |
633.4 |
760.0 |
912.0 |
1094.4 |
1313.3 |
| Capital expenditures |
|
3.0 |
3.0 |
3.0 |
3.0 |
2.0 |
| Long-term growth rate |
|
|
|
|
|
4.0% |
| Wt-Avg. C of C (K-wacc) |
10.8% |
|
|
|
|
|
| Market Value of Debt |
57.0 |
|
|
|
|
|
| Number of Shares |
10.0 |
|
|
|
|
|
| Redundant Assets |
0.0 |
|
|
|
|
|
|
|
|
|
|
|
|
| PERIOD |
0 |
1 |
2 |
3 |
4 |
5 |
| YEAR |
2003 |
2004 |
2005 |
2006 |
2007 |
2008 |
| EBIT after tax (EBIAT) |
|
74.8 |
89.7 |
107.3 |
129.4 |
154.7 |
| Depreciation |
|
15.0 |
16.0 |
16.0 |
17.0 |
17.0 |
| Cash Flow from Operations (CFFO) |
|
89.8 |
105.7 |
123.3 |
146.4 |
171.7 |
| +/- Change in Net Working Capital |
|
152.4 |
126.7 |
152.0 |
182.4 |
218.9 |
| +/- Capital Expenditures |
|
3.0 |
3.0 |
3.0 |
3.0 |
2.0 |
| Free Cash Flow (FCF) |
|
(65.6) |
(24.0) |
(31.8) |
(39.1) |
(49.2) |
| Terminal Value (TV) |
|
|
|
|
|
(752.3) |
| Sum of FCF + TV |
|
(65.6) |
(24.0) |
(31.8) |
(39.1) |
843.8 |
|
|
|
|
|
|
|
| Present Value |
377.3 |
|
|
|
|
|
| Market Value of Debt |
57.0 |
|
|
|
|
|
| Valuation of Equity |
320.3 |
|
|
|
|
|
| Redundant assets |
0.0 |
|
|
|
|
|
| Adjusted Value of Equity |
320.3 |
|
|
|
|
|
| Number of Shares |
10.0 |
|
|
|
|
|
| Value of Equity per Share |
$32.03 |
|
|
|
|
|
| SCENARIO B |
|
|
|
|
|
|
| FREE-CASH-FLOW VALUATION OF EQUITY |
MAKE ENTRIES IN BLUE-COLORED CELLS |
| Assumptions: |
|
|
|
|
|
|
| PERIOD |
0 |
1 |
2 |
3 |
4 |
5 |
| YEAR |
2003 |
2004 |
2005 |
2006 |
2007 |
2008 |
| Profit from operations (EBIT) |
|
90.0 |
95.0 |
100.0 |
105.0 |
109.0 |
| Income tax rate |
|
35.0% |
35.0% |
35.0% |
35.0% |
35.0% |
| Depreciation & amortization expense |
|
15.0 |
16.0 |
16.0 |
17.0 |
17.0 |
| Net working capital from balance sheet forecast |
481.0 |
498.0 |
523.0 |
548.0 |
576.0 |
605.0 |
| Capital expenditures |
|
3.0 |
3.0 |
3.0 |
3.0 |
2.0 |
| Long-term growth rate |
|
|
|
|
|
2.0% |
| Wt-Avg. C of C (K-wacc) |
10.8% |
|
|
|
|
|
| Market Value of Debt |
57.0 |
|
|
|
|
|
| Number of Shares |
10.0 |
|
|
|
|
|
| Redundant Assets |
0.0 |
|
|
|
|
|
|
|
|
|
|
|
|
| PERIOD |
0 |
1 |
2 |
3 |
4 |
5 |
| YEAR |
2003 |
2004 |
2005 |
2006 |
2007 |
2008 |
| EBIT after tax (EBIAT) |
|
58.5 |
61.8 |
65.0 |
68.3 |
70.9 |
| + Depreciation |
|
15.0 |
16.0 |
16.0 |
17.0 |
17.0 |
| =Cash Flow from Operations (CFFO) |
|
73.5 |
77.8 |
81.0 |
85.3 |
87.9 |
| +/- Change in Net Working Capital |
|
(17.0) |
(25.0) |
(25.0) |
(28.0) |
(29.0) |
| +/- Capital Expenditures |
|
(3.0) |
(3.0) |
(3.0) |
(3.0) |
(2.0) |
| =Free Cash Flow (FCF) |
|
53.5 |
49.8 |
53.0 |
54.3 |
56.9 |
| +Terminal Value (TV) |
|
|
|
|
|
658.9 |
| =Sum of FCF + TV |
|
53.5 |
49.8 |
53.0 |
54.3 |
715.8 |
|
|
|
|
|
|
|
| Present Value |
592.4 |
|
|
|
|
|
| - Market Value of Debt |
57.0 |
|
|
|
|
|
| = Valuation of Equity |
535.4 |
|
|
|
|
|
| +Redundant assets |
0.0 |
|
|
|
|
|
| =Adjusted Value of Equity |
535.4 |
|
|
|
|
|
| / Number of Shares |
10.0 |
0 |
|
|
|
|
| Value of Equity per Share |
$53.54 |
|
|
|
|
|
Q1 Mark Cartwright is trying to sell his business. He asked you, as a GW MBA, to value the business for him, so he can decide how to price it. You ran two scenarios of the forecast, then you ran the FCF VALUATION MODEL for each scenario, A & B above. Reconcile the two scenarios by examining their inputs and outputs, and recommend to Mark how much you think his business is worth. Include a justification based on your analysis and reconcilation of the two scenarios. HINT: How do Scenario A&B assumptions (inputs) differ?
| MARKET MULTIPLES (COMPARABLES) VALUATION OF EQUITY |
MAKE ENTRIES IN BLUE-COLORED CELLS |
|
|
none |
none |
none |
none |
Average |
| Market Multiples of Peers |
Peer A |
Peer B |
Peer C |
Peer D |
Peer E |
Mkt Mult |
| Price /revenue market multiple of peer company |
0.3 |
|
|
|
|
0.3 |
| Price/EBITDA market multiple of peer company |
12.0 |
|
|
|
|
12.0 |
| Price /Earnings market multiple of peer company |
14.0 |
|
|
|
|
14.0 |
| Mkt Val of Eq/Book Val mkt mult of Equity of peer co |
2.4 |
|
|
|
|
2.4 |
|
|
|
|
|
|
|
| Target company data |
|
Target company is Cartwright- the one being valued |
| Target company revenue |
2694.0 |
|
|
|
|
|
| Target company EBITDA |
86.0 |
Use EBIT because EBITDA is not given |
|
| Target company earnings (net income) |
44.0 |
|
|
|
|
|
| Target company book value of equity |
348.0 |
|
|
|
|
|
| Target company number of shares |
10.0 |
Not relevant- no shares outstanding |
|
|
|
|
|
|
|
|
|
|
from col B |
from Col G |
BxC |
C/B55 |
|
|
|
Target Co |
Average |
Aggregate |
Per Share |
|
|
| Valuation Calculations |
Data |
Mkt Mult |
Valuation |
Valuation |
|
|
| Valuation based on avg revenue market multiple |
2694.0 |
0.3 |
808.20 |
$80.82 |
See formulas in cells for source of data |
| Valuation based on avg EBITDA market multiple |
86.0 |
12.0 |
1032.00 |
$103.20 |
|
|
| Valuation based on avg earnings market multiple |
44.0 |
14.0 |
616.00 |
$61.60 |
|
|
| Valuation based on avg book value market multiple |
348.0 |
2.4 |
835.20 |
$83.52 |
|
|
|
|
|
|
|
|
|
| Summary |
|
|
|
|
|
|
| FREE CASH FLOW MODEL SCENARIO A |
$320,300 |
from previous tab B32 |
|
|
|
| FREE CASH FLOW MODEL SCENARIO B |
$535,400 |
from previous tab B72 |
|
|
|
| REVENUE MARKET MULTIPLE |
$808,200 |
from E19 |
|
|
|
|
| EBITDA MARKET MULTIPLE |
$1,032,000 |
from E20 |
|
|
|
|
| EARNINGS MARKET MULTIPLE |
$616,000 |
from E21 |
|
|
|
|
| BOOK VALUE MARKET MULTIPLE |
$835,200 |
from E22 |
|
|
|
|
|
|
|
|
|
|
|
| CURRENT MARKET PRICE |
There is no current market price, this is a small business, its shares are not listed or traded OTC |
Q2 After you finished the FCF Valuation (previous tab), you learned of a business similar to Cartwright Lumber that was sold recently to a new owner.
Explain the results of your Market Multiples analysis in the box provided.
Q3 Reconcile the FCF Valuation results with the Market Multiples Valuation results.
Q4 Instead of the FCF Valuation and the Market Multiples Valuation, is it valid to use a simple capitalization formula, such as the formula on page 97 of the Cohen Finance Workbook? Calculate the value of Cartwright using that formula and discuss the implications.