Please review the attached spreadsheet and help determine if I have done this correctly.

Especially looking to find out if numbers in Q3 of Part B should be dollar amounts or percentages. Also need help with explaining implications in Q4 part B.

Part A A B C 1 SCENARIO A 2 FREE-CASH-FLOW VALUATION OF EQUITY Assumptions: PERIOD YEAR Profit from operations (EBIT) Income tax rate Depreciation & amortization expense Net working capital from balance sheet forecast Capital expenditures Long-term growth rate Wt-Avg. C of C (K-wacc) Market Value of Debt Number of Shares Redundant Assets 10.8% 57.0 10.0 0.0 PERIOD YEAR 0 2003 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 481.0 21 Cash Flow from Operations (CFFO) 22 +/- Change in Net Working Capital 23 +/- Capital Expenditures 24 Free Cash Flow (FCF) 25 Terminal Value (TV) 26 Sum of FCF + TV 29 30 31 32 33 34 35 36 37 38 39 40 41 Present Value Market Value of Debt Valuation of Equity Redundant assets Adjusted Value of Equity Number of Shares Value of Equity per Share PERIOD YEAR 48 Depreciation & amortization expense 49 Net working capital from balance sheet forecast 50 Capital expenditures 51 Long-term growth rate 52 Wt-Avg. C of C (K-wacc) 53 Market Value of Debt 54 Number of Shares 2 2005 138.0 35.0% 16.0 760.0 3.0 3 2006 165.0 35.0% 16.0 912.0 3.0 4 2007 199.0 35.0% 17.0 1094.4 3.0 I PERIOD YEAR 57 58 59 EBIT after tax (EBIAT) 60 + Depreciation 61 =Cash Flow from Operations (CFFO) 62 +/- Change in Net Working Capital 63 +/- Capital Expenditures 64 =Free Cash Flow (FCF) 65 +Terminal Value (TV) 66 =Sum of FCF + TV Present Value - Market Value of Debt = Valuation of Equity +Redundant assets =Adjusted Value of Equity / Number of Shares Value of Equity per Share J 5 2008 238.0 From the forecast 35.0% From the forecast 17.0 roughly 10% of Fixed Assets, no other way to estimate 1313.3 Base year 2003 from balance sheet-see calculation at row 36 2.0 From the forecast, 2003 base year is 157 4.0% Reasonable estimate From cost of capital template From 2003 balance sheet Assume 10,000 - enter as 10 (000 omitted) None mentioned in the case 1 2004 74.8 15.0 89.8 152.4 3.0 (65.6) 2 2005 89.7 16.0 105.7 126.7 3.0 (24.0) 3 2006 107.3 16.0 123.3 152.0 3.0 (31.8) 4 2007 129.4 17.0 146.4 182.4 3.0 (39.1) (65.6) (24.0) (31.8) (39.1) 5 2008 154.7 17.0 171.7 218.9 2.0 (49.2) (752.3) (801.5) 1792 697 1094 2150 837 1313 481.0 1 2004 90.0 35.0% 15.0 498.0 3.0 2 2005 95.0 35.0% 16.0 523.0 3.0 3 2006 100.0 35.0% 16.0 548.0 3.0 4 2007 105.0 35.0% 17.0 576.0 3.0 5 2008 109.0 From the forecast 35.0% From the forecast 17.0 roughly 10% of Fixed Assets, no other way to estimate 605.0 Base year 2003 from balance sheet 2.0 From the forecast, 2003 base year is 157 2.0% growth rate in industry over long term 10.8% 57.0 10.0 0.0 55 Redundant Assets 56 75 76 77 78 79 80 81 H MAKE ENTRIES IN BLUE-COLORED CELLS 0 2003 46 Profit from operations (EBIT) 47 Income tax rate 74 G SCENARIO B 45 72 73 1 2004 115.0 35.0% 15.0 633.4 3.0 NWC Calculation using numbers from forecasted balance sheet CA 1037 1244 1493 CL 404 484 581 NWC 633 760 912 44 69 70 71 F (608.0) 57.0 -665.0 0.0 -665.0 10.0 This small business is valued in its aggregate (all shares) rather than its per share value. -$66.50 42 FREE-CASH-FLOW VALUATION OF EQUITY 43 Assumptions: 67 68 E MAKE ENTRIES IN BLUE-COLORED CELLS 0 2003 19 EBIT after tax (EBIAT) 20 Depreciation 27 28 D 0 2003 From cost of capital template From 2003 balance sheet Assume 10,000 - enter as 10 (000 omitted) None mentioned in the case 1 2004 58.5 15.0 73.5 (17.0) (3.0) 53.5 2 2005 61.8 16.0 77.8 (25.0) (3.0) 49.8 3 2006 65.0 16.0 81.0 (25.0) (3.0) 53.0 4 2007 68.3 17.0 85.3 (28.0) (3.0) 54.3 53.5 49.8 53.0 54.3 5 2008 70.9 17.0 87.9 (29.0) (2.0) 56.9 658.9 715.8 592.4 57.0 535.4 0.0 535.4 10.0 This small business is valued in its aggregate (all shares) rather than its per share value. $53.54 NWC Calculation using numbers from forecasted balance sheet, Wk2 Q2 CA 815 856 898 943 CL 317 333 350 367 NWC 498 523 548 576 990 385 605 82 Q1 - Answer in the box below. 83 Mark Cartwright is trying to sell his business. He asked you to value the business for him, so he can decide how to price it. 84 You ran two scenarios of the forecast, then you ran the FCF EQUITY VALUATION MODEL for each scenario, A & B above. 85 Reconcile the two scenarios by examining their inputs and outputs, and recommend to Mark how much you think his business is worth 86 Include a justification based on your analysis and reconcilation of the two scenarios. HINT: How do Scenario A&B assumptions (inputs) differ? 87 88 89 The two scenarios differ in terms of inputs provided to them. The scenarios differ in the EBIT, net working capital and long term growth values. 90 Although scenario 1 has higher values of EBIT and long term growth rate, the value of equity per share is negative for it. 91 The value of equity per share is greater for scenario 2, hence we would use this scenario to value Mark's business. 92 Mark's business is worth $53.54 per share or the total value of equity is $535.4 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 Page 1 MARKET MULTIPLES (COMPARABLES) VALUATION OF EQUITY Market Multiples of Peers Price /revenue market multiple of peer company Price/EBITDA market multiple of peer company Price /Earnings market multiple of peer company Mkt Val of Eq/Book Val mkt mult of Equity of peer co Target company data Target company revenue Target company EBITDA Target company earnings (net income) Target company book value of equity Target company number of shares Valuation Calculations Valuation based on avg revenue market multiple Valuation based on avg EBITDA market multiple Valuation based on avg earnings market multiple Valuation based on avg book value market multiple Summary FREE CASH FLOW MODEL SCENARIO A FREE CASH FLOW MODEL SCENARIO B REVENUE MARKET MULTIPLE EBITDA MARKET MULTIPLE EARNINGS MARKET MULTIPLE BOOK VALUE MARKET MULTIPLE CURRENT MARKET PRICE Peer A 0.3 12.0 14.0 2.4 2694.0 86.0 44.0 348.0 10.0 from col B Target Co Data 2694.0 86.0 44.0 348.0 -$665,000 $535,400 $808,200 $1,032,000 $616,000 $835,200 There is no current market price Q2 - Answer in the box below: After you finished the FCF Valuation (previous tab), you learned of a business similar to Cartwright Lumber The data for that sale, a peer company, are already entered for you, in the Q2 Market Multiples template, cel Interpret that analysis, following the steps explained in chapter 6. Explain the results of your Market Multiples analysis in the box provided. The market multiples analysis aims at determining the value of a firm based on the valuations of the peers in the same indust In this the price/revenue, price/ebidta, price/earnings and market value of equity/book value for the peer companies is noted The market multiples are then averaged for all the peers and multiplied by the revnue, ebidta, etc. values of the target compa This analysis is done to compare the target company with peers. According to this analysis the valuations for Cartwright Lumber are Valuation based on avg revenue market multiple Valuation based on avg EBITDA market multiple Valuation based on avg earnings market multiple Valuation based on avg book value market multiple 80.82 103.2 61.6 83.52 Q3 - Answer in the box below: Reconcile the FCF Valuation results with the Market Multiples Valuation results. HINT: Use Summary at row FREE CASH FLOW MODEL SCENARIO A FREE CASH FLOW MODEL SCENARIO B REVENUE MARKET MULTIPLE EBITDA MARKET MULTIPLE EARNINGS MARKET MULTIPLE BOOK VALUE MARKET MULTIPLE Q4 - Answer in the box below: -$665,000 $535,400 $808,200 $1,032,000 $616,000 $835,200 Instead of the FCF Valuation and the Market Multiples Valuation, is it valid to use a simple capitalization for such as the #2 formula on page 97 of the Cohen Finance Workbook? Calculate the value of Cartwright using that formula and discuss the implications. Dividend given by Cartwright is 0, so based on simple capitalization formula, the valuation for the firm would be 0 none Peer B MAKE ENTRIES IN BLUE-COLORED CELLS none none none Average Peer C Peer D Peer E Mkt Mult 0.3 12.0 14.0 2.4 adjust function if less than 5 Target company is Cartwright- the one being valued peers Use EBIT because EBITDA is not given ALREADY ADJUSTED BY BLANKING EMPTY CELLS Not relevant- no shares outstanding from Col G BxC Average Aggregate Mkt Mult Valuation 0.3 808.20 12.0 1032.00 14.0 616.00 2.4 835.20 C/B55 Per Share Valuation $80.82 See formulas in cells for source of data $103.20 $61.60 $83.52 from previous tab B32 from previous tab B72 from E19 from E20 from E21 from E22 re is no current market price, this is a small business, its shares are not listed or traded OTC ilar to Cartwright Lumber that was sold recently to a new owner. ket Multiples template, cells B4.B7. the peers in the same industry. r the peer companies is noted. tc. values of the target company to get the total valuation of the company INT: Use Summary at row 24. a simple capitalization formula, e firm would be 0 LREADY ADJUSTED Y BLANKING