Hello Finstat.

I have really appreciated your help so far.

Can you help with the attached? From what I can tell and what the instructor has told us is that this is less involved than the previous weeks so I would like to offer you $10 for this one. I hope that is acceptable.

Case Cartwright Lumber Company, continued from Wk2 Learning Objectives 1 Review calculation of cost of capital (k-wacc). SEE THE FLOW DIAGRAM TAB- YOU ARE NOW WO 2 Understand the foibles of equity valuation. 3 Appreciate that the capital budgeting analysis you did in Wk4 'values' one project standing alone - the Merseyside project. The valuation analysis this week values the whole business - all the projects - using similar methodology based on free cash flow. 4 Realize that market price is not the same as intrinsic value. Reading Questions Cohen Finance Workbook chapter 6: 1 Cost of Capital material starts on p 70 - already assigned. 2 Cost of Capital Template explained on p 75 - already assigned, should be familiar by now 3 Read chapter 6 on equity valuation 4 Pps 99-102 explain how to use FCF Equity Valuation template 5 Pps 102-105 explain how to use Market Multiples template See Q1 and Q2 Tabs for the questions. Scroll down to see the questions. Solutions for Wks 1&2 are included on separate tabs if you want to review them. Recall what you learned about CAPM in MBAD 6233 Wk3. Group work is encouraged...but...when you write your answers in this template, the work must be your ow Doing otherwise violates academic integrity rules. THERE IS NO SINGLE CORRECT ANSWER TO THIS CASE. THE PURPOSE OF THE ASSIGNMENT IS TO LEARN THE PROCESS OF FINANCIAL STATEMENT ANALYSIS AND FORECASTING. PERFECTION IS NOT EXPECTED. THIS IS WORK-IN-PROCESS; NOT FINISHED PRODUCT...I.E., A LEARNING EXPERIENCE. BUT, YOU MUST MAKE A CLEAR RECOMMENDATION BASED ON THE RESULTS OF YOUR ANALYSIS. DIAGRAM TAB- YOU ARE NOW WORKING ON THE BLUE-COLORED ANALYSIS familiar by now emplate, the work must be your own independent work. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 A B C D E Do not let line length overflow a box. Hit ENTER when the number of characters fills the row. F G H I J K L M N O P Q1: ROE, ROA, ROIC are given for Cartwright Lumber and Apex Lumber. Read the Cohen Finance Workbook chapters 1 & 2, concentrating on the how-to's of ratio analysis in chapter 2. If you need it, chapter 1 acts as an accounting refresher. Interest rate Income tax rate Cartwright Lumber 6.0% 17.0% Apex Lumber 8.0% 17.0% Debt Equity TOTAL LIAB+EQUITY 585 348 933 100 833 933 EBIT - Interest expense Earnings before tax - Income tax Earnings after tax 86 35.1 50.9 8.7 42.2 86 8 78 13.3 64.7 Ratio ROE ROA ROIC Fraction Ratio 12.1% 42.2/348 4.5% 42.2/933 7.7% 86*.83/933 Fraction 7.8% 64.7/833 6.9% 64.7/933 7.7% 86*.83/933 RETURN ON EQUITY Numerator Earnings after tax Denominator Equity RETURN ON ASSETS RETURN ON INVESTED CAPITAL Earnings after tax EBIT * (1-Tax rate) Total Liab+Eq Total Liab+Eq 1a: Why is Cartwright ROE higher than Apex ROE? Is it better? Why? Why not? Write answer in box. Parsing the numerator and denominator, for Cartwright earnings after tax is 42.2, lower than Apex 64.7, BUT, Cartwright equity is much lower at 348 than Apex at 833. The numerator and denominator reveal the essence of financial leverage, Cartwright has higher Higher leverage (debt to equity) means higher ROE. No indication of better or worse is implied. Depends on other info. Think of DuPont equation - profitability X efficiency X leverage = ROE .....higher leverage, higher ROE - direct relationship Higher leverage means greater default risk - is it bad? - maybe, maybe not....will be unraveled later in the course...always somewhat subjective. Depends on analysis of business risk (green-coded) and financial risk (red-coded) on IS/BS Model. When EBIT is predictably stable and high enough to cover interest expense, default risk may be lower, justifying higher leverage. Interest expense is increasing, which means that debt is increasing, substantially, increasing financial risk. 1b: Why is Cartwright ROA lower than Apex ROA? What does it tell you about the two companies? Write answer in box. is classified in the balance sheet as short-term borrowing - not included in the ratios on row 32 & 33. No implication that one is better or worse than another. Parsing the numerator and denominator, numerators are the same as in the ROE metric above, denominators are identical, so Apex with higher earnings after tax has higher ROA. 1c: How do the Cartwright & Apex ROICs compare? What does this suggest about the two companies? Write answer in box. The benefit of the ROIC measure of return is that the impact of leverage is removed, both are 7.7% because they have the same EBIT and total liabilities plus equity. If one has higher business risk (higher fixed cost ratio, riskier industry, more volatility in sales year-to-year...comparison weakens. If ratios reflect long-run situation, interpretation strengthens. If ratios reflect transitory events, interpretation weakens. Overall, different ratios measure different things. Look carefully at numerator and denominator before you interpret the meaning of the metric and beware of generalizations. Scoring Rubric a. 9 points for knowing that leverage drives ROE, minus 2 for lack of specificity b. 9 points - same as b, minus 2 as above. c. 9 points for knowing that ROIC removes impact of leverage, minus 2 as above. TOTAL 27 points of 100 points for S1 see formulas in cells. 6 points for citing specifics about the trends 6 points for citing coverage ratio in addition to debt ratio Q 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 A E F G H Do not let line length overflow a box. Hit ENTER when the number of characters fills the row. I J L M N O P Read the Cartwright Lumber Company case study. First, read the first and last paragraphs of the case. Discern what the case is about, the issues, and the decision to be made. Next, peruse the case exhibits and preliminarily digest what they tell you. Then, read the assignment questions so you know what you have to do for the assignment. Finally, read the rest of the case. Cohen Finance Workbook chapter 2 goes with this assignment. It explains ratio analysis in detail. Cartwright's financial statements are below for your convenience. Financial ratios are automatically calculated in the panels below the financial statements - at row 180. Examine the cell contents to learn how the ratio calculations work in Excel. Later, when you need to do calculations in Excel, you will know how. Never type-in in calculations done outside of Excel. Wk2 continues with a forecast of Cartwright's financial statements and external financing needs. 1a: Using the ratios below, appraise the trend (2001-03) of Cartwright's liquidity. Cite specific ratios to justify your analysis. Current ratio 1.8 1.6 1.5 -19.4% Quick ratio 0.9 0.7 0.7 -24.0% Days sales in receivables 36.8 40.3 42.9 16.8% Days cost of sales in inventory 71.4 82.8 78.2 9.6% Days cost of sales in payables 37.0 48.8 47.9 29.4% Liquidity is in decline for all ratios above. Quick ratio decline exceeds current ratio decline because days in rec increased more than increase in inventory. Customers are taking longer to pay. Is it Cartwright's choice or customers'? Day payables increased the most - indicating trouble in paying suppliers on time. 1b: Using the ratios below, appraise the trend (2001-03) of Cartwright's leverage. Cite specific ratios to justify your analysis. Long-term debt to total capital 19.2% 15.8% 12.6% -34.4% Long-term debt to equity 23.7% 18.8% 14.4% -39.4% Times interest earned 3.8 3.1 2.6 -32.2% Full burden coverage 3.2 2.5 2.2 -32.9% SCORING RUBRIC always be aware of numerator and denominator and how they change...extent of change in numerator and extent of change in denominator...think analytically Excel HINT: Copy ratios from below row 180 using copy, then Paste Special, VALUES. 6 points for citing general decline 3 points each for citing specific sources of decline TOTAL 12 points minus 2 if trend not discussed calculate %age change in Col H as (ending amount-beginning amount)/beginning amountsee formulas in cells. 6 points for citing specifics about the trends 6 points for citing coverage ratio in addition to debt ratio TOTAL 12 points minus 2 if trend not discussed Interest expense is increasing, which means that debt is increasing, substantially, increasing financial risk. Row 32 and 33 debt ratios show long-term debt decreasing, which is true...but, the debt increase is classified in the balance sheet as short-term borrowing - not included in the ratios on row 32 & 33. 1c: Using the ratios below, appraise the trend (2001-03) of Cartwright's asset use (efficiency). Cite specific ratios to justify your analysis. Fixed asset turnover 13.5 14.4 17.2 Total asset turnover 2.9 2.7 2.9 27.4% 1.1% 8 points for citing general increase 4 points for distinguishing the two ratios TOTAL 12 points minus 2 if trend not discussed Fixed asset turnover is healthy, indicating that fixed assets (growing little) drive increased sales. Total asset turnover changes little, because receivables and inventory are increasing as sales increases. 1d: Using the ratios below, appraise the trend (2001-03) of Cartwright's profitability. Cite specific ratios to justify your analysis. Gross margin (GM) 28.0% 28.6% 27.6% -1.3% 8.3% -10.6% -9.6% 10.1% 0.5 point 58 Operating profit margin (OPM) Return on sales (ROS) 2.9% 1.8% 3.0% 1.7% 3.2% 1.6% 59 60 Return on total assets (ROTA) Return on equity (ROE) 5.2% 11.5% 4.6% 11.2% 4.7% 12.6% 61 62 63 64 65 66 67 68 69 8.5% Return on invested capital (ROIC 7.1% 6.9% 7.7% Small 1.3% gross margin decrease indicates small increase in cost of sales. Good 8.3% increase in operating profit margin indicates costs are well controlled and decreasing in face of rising sales - excellent performance. Big 10.6% drop on sales driven by interest expense (financial), not operations which are sound; same for return on assets decline. ROE increase driven by leverage increase-a negative. Solid ROIC increase driven by solid operating performance. 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 K Q2: FINANCIAL STATEMENT ANALYSIS WITH RATIOS 0.5 point 0.5 point 0.5 point 0.5 point Total 0.5 point 4 points 5 points 12 points distinction between IS and BS ratios ROIC removes impact of leverage minus 2 if trend not discussed 1e: Interpret the DuPont Formula ratios by explaining if its four ratios are a valid substitute for the ratios in Q1a-1d above. Cite specifics. Profitability Efficiency 1.8% 2.9 1.7% 2.7 1.6% 2.9 2.2 2.4 2.7 Leverage -10.6% 1.1% 21.9% 10.1% ROE Check 11.5% 11.2% 12.6% Profitability and efficiency ratios are the same; profitability is ROS and does not reveal GM or OPM. Leverage uses unique numerator & denominator, no coverage ratio included, nothing on liquidity ratios, days in rec,invn,pay. Substantial ROE growth driven by large, significant 21.9% increase in leverage (financial risk) and small insignificant 1.1% increase in efficiency and significant 10.6% decrease in profitability. Note that decrease in profitability is caused by increased interest expense, not operations...because operating profit margin increased 8.3%. 1f: Based on Cartwright's 2001-03 performance, make a qualitative summary judgment about it. Operations excellent, good customer relationships, good supplier relationships in spite of slow payment period. Growth rapid, but requires increasing financing of working capital (receivables and inventory). Increased interest expense on financing drags down profits (difference between EBIT and net profit) and weakens ratios. ROE growth misleading, driven by financial leverage more than operating performance. Use of temporary financing (short term) puts Cartwright under control of bank - no loan - no business. Temporary financing of permanent needs is a dangerous plight for him to be in. 4 points 4 points 4 points Total 12 points no liquidity ratio no coverage ratio otherwise comprehensive 4 points 4 points 4 points 1 points Total 13 points operations - top of IS financing - bottom of IS weak balance sheet connect hi growth to hi debt Q2 total 73 points of 100 for S1 Summary judgment: solid operating performance, underfinanced due to rapid growth increasing receivables and inventory, requiring increased borrowing from bank and suppliers...the segue to Wk 2 forecast. CARTWRIGHT LUMBER COMPANY (000 omitted) INCOME STATEMENTS -1 2002 0 2003 Revenue Cost of sales Gross profit Other operating income Distribution costs Administrative costs Depreciation & amortization exp Other operating costs Restructuring costs Total operating costs Profit from operations (EBIT) Interest, financing expense Income from investments Disposal of operations Profit before tax Income tax Profit after tax Minority interest Other Net profit Dividends Other Reinvested in the business PERIOD JANUARY 1-DECEMBER 31 -2 2001 1697.0 1222.0 475.0 0.0 425.0 0.0 0.0 0.0 0.0 1647.0 50.0 13.0 0.0 0.0 37.0 6.0 31.0 0.0 0.0 31.0 0.0 0.0 31.0 2013.0 1437.0 576.0 0.0 515.0 0.0 0.0 0.0 0.0 1952.0 61.0 20.0 0.0 0.0 41.0 7.0 34.0 0.0 0.0 34.0 0.0 0.0 34.0 2694.0 1950.0 744.0 0.0 658.0 0.0 0.0 0.0 0.0 2608.0 86.0 33.0 0.0 0.0 53.0 9.0 44.0 0.0 0.0 44.0 0.0 0.0 44.0 PER SHARE DATA Market price Extraordinary items per share Earnings per share - primary Earnings per share - fully diluted Dividends per share Price/earnings ratio Common shares outstanding 0.00 #DIV/0! #DIV/0! 0.00 #DIV/0! #DIV/0! 0.00 0.00 #DIV/0! #DIV/0! 0.00 #DIV/0! #DIV/0! 0.00 0.00 #DIV/0! #DIV/0! 0.00 #DIV/0! #DIV/0! 0.00 -1 2002 0 2003 GO TO ROW 222...FIND 2 SUPPLEMENTARY QUESTIONS-ANSWERS ABOUT 'BUSINESS RISK' AND 'FINANCIAL RISK' THIS WILL BE IMPORTANT GOING FORWARD BALANCE SHEETS PERIOD AS OF DECEMBER 31 ASSETS Current assets: Cash & equivalents Investments Trade receivables Inventory Other Total current assets Non-current assets: Property, plant & equipment-gro Accumulated deprec. & amort. Property, plant & equipment-net Investment property Goodwill Other 1 Other 2 Total non-current assets Total assets LIABILITIES AND EQUITY Current liabilities: Trade & other payables Retirement benefit obligation Tax liabilities Leases due in 1 year Loans, debt due in 1 year Other Total current liabilities Non-current liabilities: Retirement benefit obligation Deferred tax liabilities Finance leases due after 1 year Loans, debts due after 1 year Other Total non-current liabilities Total liabilities Stockholder's equity: Preferred stock Common stock Paid-in surplus Other Retained earnings Total equity Minority interest Total liabilities & equity -2 2001 58.0 0.0 171.0 239.0 0.0 468.0 48.0 0.0 222.0 326.0 0.0 596.0 41.0 0.0 317.0 418.0 0.0 776.0 126.0 0.0 126.0 0.0 0.0 0.0 0.0 126.0 594.0 140.0 0.0 140.0 0.0 0.0 0.0 0.0 140.0 736.0 157.0 0.0 157.0 0.0 0.0 0.0 0.0 157.0 933.0 124.0 0.0 0.0 0.0 112 24.0 260.0 192.0 0.0 0.0 0.0 153 30.0 375.0 256.0 0.0 0.0 0.0 240 39.0 535.0 0.0 0.0 0.0 64.0 0.0 64.0 324.0 0.0 0.0 0.0 57.0 0.0 57.0 432.0 0.0 0.0 0.0 50.0 0.0 50.0 585.0 0.0 0.0 0.0 0.0 270.0 270.0 0.0 594.0 0.0 0.0 0.0 0.0 304.0 304.0 0.0 736.0 0.0 0.0 0.0 0.0 348.0 348.0 0.0 933.0 FINANCIAL RATIOS PERIOD YEAR Liquidity Ratios Current ratio Quick ratio Days sales in receivables Days cost of sales in inventory Days cost of sales in payables Leverage Ratios Long-term debt to total capital Long-term debt to equity Times interest earned Full burden coverage Asset-Use (Efficiency) Ratios Fixed asset turnover Total asset turnover -2 2001 -1 2002 0 2003 1.8 0.9 36.8 71.4 37.0 1.6 0.7 40.3 82.8 48.8 1.5 0.7 42.9 78.2 47.9 19.2% 23.7% 3.8 3.2 15.8% 18.8% 3.1 2.5 12.6% 14.4% 2.6 2.2 13.5 2.9 14.4 2.7 17.2 2.9 Profitability Ratios Gross margin Operating profit margin Return on sales Return on total assets Return on equity (ROE) Return on invested capital (ROIC 28.0% 2.9% 1.8% 5.2% 11.5% 7.1% 28.6% 3.0% 1.7% 4.6% 11.2% 6.9% 27.6% 3.2% 1.6% 4.7% 12.6% 7.7% DuPont Formula - ROE Profitability Efficiency Leverage ROE Check 11.5% 1.8% 2.9 2.2 11.5% 11.2% 1.7% 2.7 2.4 11.2% 12.6% 1.6% 2.9 2.7 12.6% Compound Annual Growth Rates #DIV/0! Revenues #DIV/0! Gross profit #DIV/0! Operating profit (EBIT) #DIV/0! Total assets 18.6% 21.3% 22.0% 23.9% 33.8% 29.2% 41.0% 26.8% Appraise Cartwright's 'business risk'. Cartwright's business risk driven by changes in sales and changes in operating expenses, which drive changes in EBIT. BUSINESS RISK is coded GREEN on IS/BS Model, On the operating expense side, business risk is low because of cost control and efficient operations - controllable by linking IS and BS. It is analogous to OPERATING LEVERAGE; Mr. Cartwright. variation in EBIT driven by variations in sales and operating expenses. On the sales side, he has less control, subject to economic forces and competition. His customer and supplier relationsh Always consider degree of fixed cost in interpreting business risk - higher are good. He uses generous credit terms to build sales - but his customers (small builders) likely have cash flow problempercentage of fixed cost to total cost, higher business risk. of their own - so quality of receivables is low - bad debt risk. See Cohen Finance Workbook pps 29-32 Business risk is moderate, not on cost side, but on possibility of sales decline and collection of receivables. Appraise Cartwright's 'financial risk'. it is increasing - and highly risky. Most of his debt (82.8%) is short term debt, not revealed in the ratio analysis. short term debt 240 debt 290 long term debt 50 debt+equit 638 45.5% equity 348 ratio 82.8% st debt to If the bank does not roll over the short-term loan, and Cartwright can't find another lender, he would be forced to liquidate the business to repay the loan, or face bankruptcy. New equity is off the table because Cartwright recently bought out his equity partner - he does not want a partner. FINANCIAL RISK is coded RED on IS/BS Model, linking IS and BS. Interest expense is a fixed cost, the driver behind FINANCIAL LEVERAGE. See Cohen Finance Workbook, pps 109-11 Q A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 B C D E F G H 2005 2006 2007 2008 I J K L M ENTER DATA IN BLUE-COLORED CELLS Year Net Sales Growth rate in net sales Cost of goods soldet sales GS&A expenseset sales Long-term debt Current portion long-term debt Interest rate Tax rate Dividend/earnings after tax Current assetset sales Net fixed assets Current liabilities/ net sales Owner's equity (net worth) 2004 $2,694 2004 33.6% 20% 20% 20% 20% 72.4% 72.4% 72.4% 72.4% 72.4% 24.4% 24.4% 24.4% 24.4% 24.4% $ 50 $ 43 $ 37 $ 30 $ 23 $ 7 ### $ 7 $ 7 $ 7 8.0% 8.0% 8.0% 8.0% 8.0% 35.0% 35.0% 35.0% 35.0% 35.0% 0.0% 0.0% 0.0% 0.0% 0.0% 28.8% 28.8% 28.8% 28.8% 28.8% $ 157 $ 160 $ 163 $ 166 $ 168 11.2% 11.2% 11.2% 11.2% 11.2% $348 16 17 INCOME STATEMENT Year Net sales Cost of goods sold Gross profit GSA expense EBIT Interest expense Earnings before tax Tax Earnings after tax Dividends paid Additions to retained earnings Equations Forecast 2004 2004 2005 2006 2007 =B3+(B3*C4) $3,600 $4,320 $5,184 $6,220 =C5*C20 2,606 3,127 3,752 4,502 =C20-C21 994 1,193 1,432 1,718 =C6*C20 879 1,055 1,266 1,519 =C22-C23 115 138 165 199 =(C7+C8)*C9 5 4 4 3 =C24-C25 110 134 162 196 =C10*C26 39 47 57 68 =C26-C27 72 87 105 127 =C11*C28 0 0 0 0 =C28-C29 72 87 105 127 BALANCE SHEET Current assets Net fixed assets Total assets =C12*C20 =C13 =C33+C34 18 19 20 21 22 23 24 25 26 27 28 29 30 2008 $7,464 5,403 2,061 1,823 238 2 236 83 153 0 153 31 32 33 34 35 1,037 157 1,194 1,244 160 1,404 1,493 163 1,656 1,792 166 1,958 2,150 168 2,318 404 50 420 873 484 43 507 1,034 581 37 612 1,230 697 30 739 1,466 837 22 893 1,751 $321 $370 $426 $491 $567 36 37 38 39 40 41 42 Current liabilities =C14*C20 Long-term debt =C7 Equity =B15+C30 Total liabilities and =C37+C38+C3 shareholder's equity EXTERNAL FUNDING REQUIRED=C35-C40 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 Q1a Discuss how the interest expense (row 25) calculation works and whether or not it includes the Scoring: 15 points @ for Q1a,b,c,d - total 60 points short term borrowing on the Cartwright balance sheet. HINT: What is the source of 20 points @ for Q2a,b - total 40 points the data used in the ratio on row 14? Wk2 assignment total 100 points Row 25 calculates interest expense by summing LTD plus CURRENT PORTION OF LTD, multiplying sum by the interest rate. Short-term debt is not included in the calculation, as if short-term debt is zero. points answer The data in row 14, the assumption that forecasts current liabilities, does not include short-term debt. 10 citing (C7+C8)*C9 or equivalent in words Therefore, EFR (EFN-same thing), the result of the forecast, must be interpreted in light of short-term debt being zero. 5 short-term borrowing not in balance sheet forecast 15 Cells C14.G14 generate row 37 current liabilities; that row includes only those current liabilities that move up and down when sales moves up and down, i.e., borrowing is not included. If the numerator in C14.G14 had been 295 instead of 302, the interpretation would have been easier to make. The difference is 7 of current portion of long-term debt (295+7=302) which should not be in the numerator. SOLUTIONS CONTAIN MORE DETAIL That being said, the blue-cell assumptions can be judgments about how the future will look, not necessarily the same ratios from the prior year(s). THAN EXPECTED IN ANSWERS... TO ADVANCE THE UNDERSTANDING The fundament here is to interpret EFR correctly, consistent with the workings of the forecast...that short-term borrowing does not get forecasted as a line item in the 2004-2008 balance sheets...it is included in the EFR. Short-term borrowing must be zeroed in the forecast because there is no assurance that such borrowing will exist in the future. Q1b How much does Cartwright need to borrow and when? Explain by citing specifics from the forecast. He needs to borrow $321 by the end of 2004, increase it to $370 by end of 2005 ($50 more), $321 means $321,000 increase it to $426 by end of 2006 ($56 more), then to $491 for 2007 ($65 more), and finally to $567 ($76 more) by end of 2008. He needs to borrow $567 to complete his 5-year growth plan. EFN end of year change each year 2004 $321 2005 $370 $50 2006 $426 $56 2007 $491 $65 2008 $567 $76 5 knowing that EFR numbers are cumulative, not additive 5 citing specific figures from row 42 5 citing timing 15 He could raise the EFR via equity instead of debt, of a combination of both...but the case says that he bought out Stark to eliminate his equity partner...so we can assume that he wants future financing to be all debt...if the banker is willing. Q1c Does Cartwright have the ability to pay the interest expense? Explain by citing specifics from the forecast. He does have the ability to pay interest if EFR is raised via debt. 2004 2005 2006 EBIT 115 138 165 Interest on LTD 5 4 4 Interest on EFR as de 29 33 38 Total interest 33 37 42 Interest coverage rati 3.4 3.7 4.0 2007 199 3 44 47 4.2 2008 238 2 51 53 4.5 5 citing EBIT from which interest expense is paid 5 citing interest expense on LT and ST debt 5 specific yes or no backed by specific numbers 15 The calculations assume an interest rate of 9%, see row 86 cell formulas. Q1d Does Cartwright have the ability to repay the loan principal? Explain by citing specifics from the forecast. He does not have the ability to repay the principal because EFR increases each year, showing that the loan principal increases, according to Q1b above. numbers same as rows 71-73 A decline in EFR year-to-year implies that loan principal can be repaid. 8 cite increasing EFR with correct interpretation 7 specific yes or no backed by specific numbers 15 60 total on Q1 N A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 B C D ENTER DATA IN BLUE-COLORED CELLS Year Net Sales Growth rate in net sales Cost of goods soldet sales GS&A expenseset sales Long-term debt Current portion long-term debt Interest rate Tax rate Dividend/earnings after tax Current assetset sales Net fixed assets Current liabilities/ net sales Owner's equity (net worth) 2004 $2,694 2004 2005 E F G 2006 2007 2008 H I J K L M N 0.0% 0% 0% 0% 0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% $ 50 $ 43 $ 37 $ 30 $ 23 $ 7 ### $ 7 $ 7 $ 7 0.0% 0.0% 0.0% 0.0% 0.0% 35.0% 35.0% 35.0% 35.0% 35.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% $ 157 $ 160 $ 163 $ 166 $ 168 0.0% 0.0% 0.0% 0.0% 0.0% $348 16 17 INCOME STATEMENT Year Net sales Cost of goods sold Gross profit GSA expense EBIT Interest expense Earnings before tax Tax Earnings after tax Dividends paid Additions to retained earnings Equations Forecast 2004 2004 2005 2006 2007 =B3+(B3*C4) $2,694 $2,694 $2,694 $2,694 =C5*C20 0 0 0 0 =C20-C21 2,694 2,694 2,694 2,694 =C6*C20 0 0 0 0 =C22-C23 2,694 2,694 2,694 2,694 =(C7+C8)*C9 0 0 0 0 =C24-C25 2,694 2,694 2,694 2,694 =C10*C26 943 943 943 943 =C26-C27 1,751 1,751 1,751 1,751 =C11*C28 0 0 0 0 =C28-C29 1,751 1,751 1,751 1,751 BALANCE SHEET Current assets Net fixed assets Total assets =C12*C20 =C13 =C33+C34 18 19 20 21 22 23 24 25 26 27 28 29 30 2008 $2,694 0 2,694 0 2,694 0 2,694 943 1,751 0 1,751 31 32 33 34 35 0 157 157 0 160 160 0 163 163 0 166 166 0 168 168 36 37 38 39 40 41 42 Current liabilities =C14*C20 0 0 0 0 0 Long-term debt =C7 50 43 37 30 22 Equity =B15+C30 2,099 3,850 5,601 7,352 9,104 Total liabilities and =C37+C38+C3 2,149 3,893 5,638 7,382 9,126 shareholder's equity EXTERNAL FUNDING REQUIRED=C35-C40 ($1,992) ($3,733) ($5,475) ($7,216) ($8,958) 43 44 45 46 FROM P 45 IN COHEN FINANCE WORKBOOK: 47 FOR EACH $1 CHANGE IN REVENUE: 48 Assumptions: 49 revenue 2694 1.6% 50 net profit margin 51 CHANGE IN ASSETS (USES OF FUNDS) CHANGE IN LIABILITIES+EQUITY (SOURCES OF FUNDS) cents CURRENT LIABILITIES PAYABLES 256 OTHER ACC 39 CL/SALES 11.0 cents 52 53 CURRENT ASSETS 54 55 RECEIVABLES INVENTORY 317 418 56 CA/SALES 57 58 FIXED ASSETS 59 60 61 27.3 LONG-TERM DEBT PLANT PROPERTY EQUIPMENT 62 FA/SALES 63 0 EQUITY INCR IN RET EARN/S 1.6 0.0 TOTAL FORECASTED SOURCES 12.6 EXTERNAL FINANCING NEEDED 14.7 67 TOTAL FORECASTED USES 27.3 ADJUSTED TOTAL FORECASTED 68 69 70 Q2a 71 Explain how the p 45 table from the Cohen Finance Workbook, shown above starting on row 47, works and its 72 significance to Cartwright's (and most other businesses too) external financing needs problem. 27.3 64 65 66 73 74 75 76 77 The left side of the balance sheet grows faster than the right side. Therefore, EFR (EFN) is needed to balance the balance sheet, to provide the missing sources of funds. points C54 & C55 show Cartwright's Rec and Invn, then D56 shows them as a % of sales (revenue). Each $1 change in revenue requires that amount of change in Rec plus Invn. 78 No change in PPE is shown for Cartwright. 79 Generalizing to all businesses, if PPE is increased, then more USES of funds occurs. 80 81 H54 & H55 show Pay and Other Accruals, then I56 shows them as a % of total revenue. 82 Each $1 change in revenue provides that amount of change in Pay plus Other Accr. 83 LTD does not change...it is a formal decision borrow or not, not generated by a change in revenue. 84 The profit margin in I61 is another source of funds. 85 Total sources of funds are 12.6; total uses oif funds are 27.3; the difference is EFR, 14.7 86 87 this is crucial - it's worth re-reading the Cohen Finance Workbook from page 45 88 89 Q2b 90 Revise the short-form forecast model from Q1, using the 'input cells zeroed' model at the top of this tab. 91 As the banker, assume a lower growth rate in sales, and explain, showing specifics from the revised forecast, 92 how it helps Cartwright solve his external financing needed problem. Enter revised data in the blue cells, using your judgment. 93 94 95 96 97 According to the case, Northrup Bank may offer a maximum credit line of $465,000. According to the Q1 EFR forecast EFR $321 the $465 loan limit would be exceeded between the 3rd and 4th years of the forecast, after 2006. Further, as pointed out in Q1, steadily rising EFR means 98 no ability to repay the loan principal (he can pay only the annual interest). $370 $426 $491 answer 5 left side grows more than right side, or equivalent insight 5 EFR balances source and use 5 revenue growth drives EFR 5 need for outside financing 20 or something related to case SOLUTIONS CONTAIN MORE DETAIL THAN EXPECTED IN ANSWERS... TO ADVANCE THE UNDERSTANDING $567 99 100 Therefore, the banker may be uneasy about Cartwright's growth rate of sales.. 101 the higher the growth rate, the greater is EFR. 102 enter the different growth rates in row 4 to see these results for yourself 103 Lower the growth rate of sales to 10% annually, EFR becomes: 222 219 213 206 198 104 Lower it to 5% annually, EFR becomes: 201 177 150 121 90 105 only sales growth rate changed, all other assumptions same as Q1 106 107 With 10% sales increase, borrowing on row 103 is lower than on row 94 and it decreases from year-to-year, indicating repayment of principal. 108 109 110 111 112 Scoring Rubric: 20 points @ for Q2a,b - total 40 points With 5% sales increase, borrowing on row 104 is lower than on row 103, similarily decreasing. ALL OTHER ASSUMPTIONS ARE HELD CONSTANT FOR THIS EXAMPLE. The essence is to match the growth rate of sales with available financing, not an easy thing to do when the business wants growth and the lender wants its money back. No sales growth, almost 0 2008 loan balance EFR, 0% sales growth 180 137 92 48 4 6 evidence of revision in rows 4-14 above no need to revise all cells growth rate in sales must be cut 7 discussion of revised EFR as lower than in Q1 mentioning specifics 7 lower sales growth, less financing needed 20 40 points total for Q2 100 points total for Wk 2 assignment O INCOME STATEMENT Revenue Cost of sales Gross profit Other operating income Other operating expenses Total cost and expenses Operating profit (EBIT) Interest, finance costs Profit before tax Income tax Net profit after tax Dividends Reinvested in the business BALANCE SHEET ASSETS LIABILITIES AND EQUITY Current assets Current liabilities Cash Trade payables Investments Other accruals Trade receivables Tax liabilities Inventories Short-term loans, leases Non-current assets Non-current liabilities Property, plant & equipment Loans, debt, leases due after 1 year Investment property Retirement benefit obligation Goodwill Deferred tax liabilities Total non-current liabilities WORKING CAPITAL spontaneous change with revenue ?what levels of ca, cl, s-t loans? CAPITAL BUDGETING ?what projects to accept? FINANCING ?what is the debt capacity? COST OF DEBT K-WACC Stockholder's equity (Net worth) Preferred stock Common stock Additional paid-in-capital Retained earnings OPERATING LEVERAGE FINANCIAL LEVERAGE Total assets Total liabilities & equity COST OF EQUITY VALUATION CASH FLOW COST OF CAPITAL ANALYSIS STEPS: FINANCING 1-HISTORICAL RATIOS 2-K-WACC HISTORICAL RATIOS I/S & B/S FORECAST 3-CAPITAL BUDGETING 4-FORECAST & EFN LONG-FORM FORECAST I/S, B/S, & RATIOS 5-EQUITY VALUATION 6-FINANCING CAPITAL BUDGETING OP & CAP NATCF, NPV, IRR, PAYBACK VALUATION ENTERPRISE VALUE USING FREE CASH FLOW MARKET MULTIPLES: P/E, MV/BV, REV, EBIT EFN DEBT EQUITY DEBT EQUITY EBIT CHART income K-WACC risk control mktblty flexblty timing Q1-FCF Valuation 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 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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 Page 16 MARKET MULTIPLES (COMPARABLES) VALUATION OF EQUITY none Market Multiples of Peers Peer A Peer B Price /revenue market multiple of peer company 0.3 Price/EBITDA market multiple of peer company 12.0 Price /Earnings market multiple of peer company 14.0 Mkt Val of Eq/Book Val mkt mult of Equity of peer co 2.4 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 MAKE ENTRIES IN BLUE none Peer C Target company is Cartwright- the one 2694.0 86.0 Use EBIT because EBITDA is not give 44.0 348.0 10.0 Not relevant- no shares outstanding from col B from Col G BxC Target Co Average Aggregate Data Mkt Mult Valuation 2694.0 0.3 808.20 86.0 12.0 1032.00 44.0 14.0 616.00 348.0 2.4 835.20 -$665,000 from previous tab B32 $535,400 from previous tab B72 $808,200 from E19 $1,032,000 from E20 $616,000 from E21 $835,200 from E22 There is no current market price, this is a small business, 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. Q3 - Answer in the box below: Reconcile the FCF Valuation results with the Market Multiples Valuation results. HINT: Use Summary at row Q4 - Answer in the box below: 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. KE ENTRIES IN BLUE-COLORED CELLS none none Average Peer D Peer E Mkt Mult 0.3 12.0 14.0 2.4 adjust function if less than 5 y is Cartwright- the one being valued peers use EBITDA is not given ALREADY ADJUSTED BY BLANKING EMPTY CELLS o shares outstanding C/B55 Per Share Valuation $80.82 See formulas in cells for source of data $103.20 $61.60 $83.52 this is a small business, its shares are not listed or traded OTC ar to Cartwright Lumber that was sold recently to a new owner. t Multiples template, cells B4.B7. T: Use Summary at row 24. imple capitalization formula, \fCase Cartwright Lumber Company, continued from Wk2 Learning Objectives 1 Review calculation of cost of capital (k-wacc). SEE THE FLOW DIAGRAM TAB- YOU ARE NOW WO 2 Understand the foibles of equity valuation. 3 Appreciate that the capital budgeting analysis you did in Wk4 'values' one project standing alone - the Merseyside project. The valuation analysis this week values the whole business - all the projects - using similar methodology based on free cash flow. 4 Realize that market price is not the same as intrinsic value. Reading Questions Cohen Finance Workbook chapter 6: 1 Cost of Capital material starts on p 70 - already assigned. 2 Cost of Capital Template explained on p 75 - already assigned, should be familiar by now 3 Read chapter 6 on equity valuation 4 Pps 99-102 explain how to use FCF Equity Valuation template 5 Pps 102-105 explain how to use Market Multiples template See Q1 and Q2 Tabs for the questions. Scroll down to see the questions. Solutions for Wks 1&2 are included on separate tabs if you want to review them. Recall what you learned about CAPM in MBAD 6233 Wk3. Group work is encouraged...but...when you write your answers in this template, the work must be your ow Doing otherwise violates academic integrity rules. THERE IS NO SINGLE CORRECT ANSWER TO THIS CASE. THE PURPOSE OF THE ASSIGNMENT IS TO LEARN THE PROCESS OF FINANCIAL STATEMENT ANALYSIS AND FORECASTING. PERFECTION IS NOT EXPECTED. THIS IS WORK-IN-PROCESS; NOT FINISHED PRODUCT...I.E., A LEARNING EXPERIENCE. BUT, YOU MUST MAKE A CLEAR RECOMMENDATION BASED ON THE RESULTS OF YOUR ANALYSIS. DIAGRAM TAB- YOU ARE NOW WORKING ON THE BLUE-COLORED ANALYSIS familiar by now emplate, the work must be your own independent work. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 A B C D E Do not let line length overflow a box. Hit ENTER when the number of characters fills the row. F G H I J K L M N O P Q1: ROE, ROA, ROIC are given for Cartwright Lumber and Apex Lumber. Read the Cohen Finance Workbook chapters 1 & 2, concentrating on the how-to's of ratio analysis in chapter 2. If you need it, chapter 1 acts as an accounting refresher. Interest rate Income tax rate Cartwright Lumber 6.0% 17.0% Apex Lumber 8.0% 17.0% Debt Equity TOTAL LIAB+EQUITY 585 348 933 100 833 933 EBIT - Interest expense Earnings before tax - Income tax Earnings after tax 86 35.1 50.9 8.7 42.2 86 8 78 13.3 64.7 Ratio ROE ROA ROIC Fraction Ratio 12.1% 42.2/348 4.5% 42.2/933 7.7% 86*.83/933 Fraction 7.8% 64.7/833 6.9% 64.7/933 7.7% 86*.83/933 RETURN ON EQUITY Numerator Earnings after tax Denominator Equity RETURN ON ASSETS RETURN ON INVESTED CAPITAL Earnings after tax EBIT * (1-Tax rate) Total Liab+Eq Total Liab+Eq 1a: Why is Cartwright ROE higher than Apex ROE? Is it better? Why? Why not? Write answer in box. Parsing the numerator and denominator, for Cartwright earnings after tax is 42.2, lower than Apex 64.7, BUT, Cartwright equity is much lower at 348 than Apex at 833. The numerator and denominator reveal the essence of financial leverage, Cartwright has higher Higher leverage (debt to equity) means higher ROE. No indication of better or worse is implied. Depends on other info. Think of DuPont equation - profitability X efficiency X leverage = ROE .....higher leverage, higher ROE - direct relationship Higher leverage means greater default risk - is it bad? - maybe, maybe not....will be unraveled later in the course...always somewhat subjective. Depends on analysis of business risk (green-coded) and financial risk (red-coded) on IS/BS Model. When EBIT is predictably stable and high enough to cover interest expense, default risk may be lower, justifying higher leverage. Interest expense is increasing, which means that debt is increasing, substantially, increasing financial risk. 1b: Why is Cartwright ROA lower than Apex ROA? What does it tell you about the two companies? Write answer in box. is classified in the balance sheet as short-term borrowing - not included in the ratios on row 32 & 33. No implication that one is better or worse than another. Parsing the numerator and denominator, numerators are the same as in the ROE metric above, denominators are identical, so Apex with higher earnings after tax has higher ROA. 1c: How do the Cartwright & Apex ROICs compare? What does this suggest about the two companies? Write answer in box. The benefit of the ROIC measure of return is that the impact of leverage is removed, both are 7.7% because they have the same EBIT and total liabilities plus equity. If one has higher business risk (higher fixed cost ratio, riskier industry, more volatility in sales year-to-year...comparison weakens. If ratios reflect long-run situation, interpretation strengthens. If ratios reflect transitory events, interpretation weakens. Overall, different ratios measure different things. Look carefully at numerator and denominator before you interpret the meaning of the metric and beware of generalizations. Scoring Rubric a. 9 points for knowing that leverage drives ROE, minus 2 for lack of specificity b. 9 points - same as b, minus 2 as above. c. 9 points for knowing that ROIC removes impact of leverage, minus 2 as above. TOTAL 27 points of 100 points for S1 see formulas in cells. 6 points for citing specifics about the trends 6 points for citing coverage ratio in addition to debt ratio Q Q3 - Answer in the box below: Reconcile the FCF Valuation results with the Market Multiples Valuation results. HINT: Use Summary at row The average of these 4 market multiples gives you an intriisic value of $82.29 per share which is 54% higher than the FCF for s Individual market multiples valuations % change per share A scenario %change per share B scenario Revenue market multiple 222% 51% EBITDA market multiple 255% 93% earnings market multiple 193% 15% book value market multiple 226% 56% T: Use Summary at row 24. % higher than the FCF for scenario B and and 223% higher than the FCF model for A. ange per share B scenario Case Cartwright Lumber Company, continued from Wk2 Learning Objectives 1 Review calculation of cost of capital (k-wacc). SEE THE FLOW DIAGRAM TAB- YOU ARE NOW WO 2 Understand the foibles of equity valuation. 3 Appreciate that the capital budgeting analysis you did in Wk4 'values' one project standing alone - the Merseyside project. The valuation analysis this week values the whole business - all the projects - using similar methodology based on free cash flow. 4 Realize that market price is not the same as intrinsic value. Reading Questions Cohen Finance Workbook chapter 6: 1 Cost of Capital material starts on p 70 - already assigned. 2 Cost of Capital Template explained on p 75 - already assigned, should be familiar by now 3 Read chapter 6 on equity valuation 4 Pps 99-102 explain how to use FCF Equity Valuation template 5 Pps 102-105 explain how to use Market Multiples template See Q1 and Q2 Tabs for the questions. Scroll down to see the questions. Solutions for Wks 1&2 are included on separate tabs if you want to review them. Recall what you learned about CAPM in MBAD 6233 Wk3. Group work is encouraged...but...when you write your answers in this template, the work must be your ow Doing otherwise violates academic integrity rules. THERE IS NO SINGLE CORRECT ANSWER TO THIS CASE. THE PURPOSE OF THE ASSIGNMENT IS TO LEARN THE PROCESS OF FINANCIAL STATEMENT ANALYSIS AND FORECASTING. PERFECTION IS NOT EXPECTED. THIS IS WORK-IN-PROCESS; NOT FINISHED PRODUCT...I.E., A LEARNING EXPERIENCE. BUT, YOU MUST MAKE A CLEAR RECOMMENDATION BASED ON THE RESULTS OF YOUR ANALYSIS. DIAGRAM TAB- YOU ARE NOW WORKING ON THE BLUE-COLORED ANALYSIS familiar by now emplate, the work must be your own independent work. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 A B C D E Do not let line length overflow a box. Hit ENTER when the number of characters fills the row. F G H I J K L M N O P Q1: ROE, ROA, ROIC are given for Cartwright Lumber and Apex Lumber. Read the Cohen Finance Workbook chapters 1 & 2, concentrating on the how-to's of ratio analysis in chapter 2. If you need it, chapter 1 acts as an accounting refresher. Interest rate Income tax rate Cartwright Lumber 6.0% 17.0% Apex Lumber 8.0% 17.0% Debt Equity TOTAL LIAB+EQUITY 585 348 933 100 833 933 EBIT - Interest expense Earnings before tax - Income tax Earnings after tax 86 35.1 50.9 8.7 42.2 86 8 78 13.3 64.7 Ratio ROE ROA ROIC Fraction Ratio 12.1% 42.2/348 4.5% 42.2/933 7.7% 86*.83/933 Fraction 7.8% 64.7/833 6.9% 64.7/933 7.7% 86*.83/933 RETURN ON EQUITY Numerator Earnings after tax Denominator Equity RETURN ON ASSETS RETURN ON INVESTED CAPITAL Earnings after tax EBIT * (1-Tax rate) Total Liab+Eq Total Liab+Eq 1a: Why is Cartwright ROE higher than Apex ROE? Is it better? Why? Why not? Write answer in box. Parsing the numerator and denominator, for Cartwright earnings after tax is 42.2, lower than Apex 64.7, BUT, Cartwright equity is much lower at 348 than Apex at 833. The numerator and denominator reveal the essence of financial leverage, Cartwright has higher Higher leverage (debt to equity) means higher ROE. No indication of better or worse is implied. Depends on other info. Think of DuPont equation - profitability X efficiency X leverage = ROE .....higher leverage, higher ROE - direct relationship Higher leverage means greater default risk - is it bad? - maybe, maybe not....will be unraveled later in the course...always somewhat subjective. Depends on analysis of business risk (green-coded) and financial risk (red-coded) on IS/BS Model. When EBIT is predictably stable and high enough to cover interest expense, default risk may be lower, justifying higher leverage. Interest expense is increasing, which means that debt is increasing, substantially, increasing financial risk. 1b: Why is Cartwright ROA lower than Apex ROA? What does it tell you about the two companies? Write answer in box. is classified in the balance sheet as short-term borrowing - not included in the ratios on row 32 & 33. No implication that one is better or worse than another. Parsing the numerator and denominator, numerators are the same as in the ROE metric above, denominators are identical, so Apex with higher earnings after tax has higher ROA. 1c: How do the Cartwright & Apex ROICs compare? What does this suggest about the two companies? Write answer in box. The benefit of the ROIC measure of return is that the impact of leverage is removed, both are 7.7% because they have the same EBIT and total liabilities plus equity. If one has higher business risk (higher fixed cost ratio, riskier industry, more volatility in sales year-to-year...comparison weakens. If ratios reflect long-run situation, interpretation strengthens. If ratios reflect transitory events, interpretation weakens. Overall, different ratios measure different things. Look carefully at numerator and denominator before you interpret the meaning of the metric and beware of generalizations. Scoring Rubric a. 9 points for knowing that leverage drives ROE, minus 2 for lack of specificity b. 9 points - same as b, minus 2 as above. c. 9 points for knowing that ROIC removes impact of leverage, minus 2 as above. TOTAL 27 points of 100 points for S1 see formulas in cells. 6 points for citing specifics about the trends 6 points for citing coverage ratio in addition to debt ratio Q 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 A E F G H Do not let line length overflow a box. Hit ENTER when the number of characters fills the row. I J L M N O P Read the Cartwright Lumber Company case study. First, read the first and last paragraphs of the case. Discern what the case is about, the issues, and the decision to be made. Next, peruse the case exhibits and preliminarily digest what they tell you. Then, read the assignment questions so you know what you have to do for the assignment. Finally, read the rest of the case. Cohen Finance Workbook chapter 2 goes with this assignment. It explains ratio analysis in detail. Cartwright's financial statements are below for your convenience. Financial ratios are automatically calculated in the panels below the financial statements - at row 180. Examine the cell contents to learn how the ratio calculations work in Excel. Later, when you need to do calculations in Excel, you will know how. Never type-in in calculations done outside of Excel. Wk2 continues with a forecast of Cartwright's financial statements and external financing needs. 1a: Using the ratios below, appraise the trend (2001-03) of Cartwright's liquidity. Cite specific ratios to justify your analysis. Current ratio 1.8 1.6 1.5 -19.4% Quick ratio 0.9 0.7 0.7 -24.0% Days sales in receivables 36.8 40.3 42.9 16.8% Days cost of sales in inventory 71.4 82.8 78.2 9.6% Days cost of sales in payables 37.0 48.8 47.9 29.4% Liquidity is in decline for all ratios above. Quick ratio decline exceeds current ratio decline because days in rec increased more than increase in inventory. Customers are taking longer to pay. Is it Cartwright's choice or customers'? Day payables increased the most - indicating trouble in paying suppliers on time. 1b: Using the ratios below, appraise the trend (2001-03) of Cartwright's leverage. Cite specific ratios to justify your analysis. Long-term debt to total capital 19.2% 15.8% 12.6% -34.4% Long-term debt to equity 23.7% 18.8% 14.4% -39.4% Times interest earned 3.8 3.1 2.6 -32.2% Full burden coverage 3.2 2.5 2.2 -32.9% SCORING RUBRIC always be aware of numerator and denominator and how they change...extent of change in numerator and extent of change in denominator...think analytically Excel HINT: Copy ratios from below row 180 using copy, then Paste Special, VALUES. 6 points for citing general decline 3 points each for citing specific sources of decline TOTAL 12 points minus 2 if trend not discussed calculate %age change in Col H as (ending amount-beginning amount)/beginning amountsee formulas in cells. 6 points for citing specifics about the trends 6 points for citing coverage ratio in addition to debt ratio TOTAL 12 points minus 2 if trend not discussed Interest expense is increasing, which means that debt is increasing, substantially, increasing financial risk. Row 32 and 33 debt ratios show long-term debt decreasing, which is true...but, the debt increase is classified in the balance sheet as short-term borrowing - not included in the ratios on row 32 & 33. 1c: Using the ratios below, appraise the trend (2001-03) of Cartwright's asset use (efficiency). Cite specific ratios to justify your analysis. Fixed asset turnover 13.5 14.4 17.2 Total asset turnover 2.9 2.7 2.9 27.4% 1.1% 8 points for citing general increase 4 points for distinguishing the two ratios TOTAL 12 points minus 2 if trend not discussed Fixed asset turnover is healthy, indicating that fixed assets (growing little) drive increased sales. Total asset turnover changes little, because receivables and inventory are increasing as sales increases. 1d: Using the ratios below, appraise the trend (2001-03) of Cartwright's profitability. Cite specific ratios to justify your analysis. Gross margin (GM) 28.0% 28.6% 27.6% -1.3% 8.3% -10.6% -9.6% 10.1% 0.5 point 58 Operating profit margin (OPM) Return on sales (ROS) 2.9% 1.8% 3.0% 1.7% 3.2% 1.6% 59 60 Return on total assets (ROTA) Return on equity (ROE) 5.2% 11.5% 4.6% 11.2% 4.7% 12.6% 61 62 63 64 65 66 67 68 69 8.5% Return on invested capital (ROIC 7.1% 6.9% 7.7% Small 1.3% gross margin decrease indicates small increase in cost of sales. Good 8.3% increase in operating profit margin indicates costs are well controlled and decreasing in face of rising sales - excellent performance. Big 10.6% drop on sales driven by interest expense (financial), not operations which are sound; same for return on assets decline. ROE increase driven by leverage increase-a negative. Solid ROIC increase driven by solid operating performance. 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 K Q2: FINANCIAL STATEMENT ANALYSIS WITH RATIOS 0.5 point 0.5 point 0.5 point 0.5 point Total 0.5 point 4 points 5 points 12 points distinction between IS and BS ratios ROIC removes impact of leverage minus 2 if trend not discussed 1e: Interpret the DuPont Formula ratios by explaining if its four ratios are a valid substitute for the ratios in Q1a-1d above. Cite specifics. Profitability Efficiency 1.8% 2.9 1.7% 2.7 1.6% 2.9 2.2 2.4 2.7 Leverage -10.6% 1.1% 21.9% 10.1% ROE Check 11.5% 11.2% 12.6% Profitability and efficiency ratios are the same; profitability is ROS and does not reveal GM or OPM. Leverage uses unique numerator & denominator, no coverage ratio included, nothing on liquidity ratios, days in rec,invn,pay. Substantial ROE growth driven by large, significant 21.9% increase in leverage (financial risk) and small insignificant 1.1% increase in efficiency and significant 10.6% decrease in profitability. Note that decrease in profitability is caused by increased interest expense, not operations...because operating profit margin increased 8.3%. 1f: Based on Cartwright's 2001-03 performance, make a qualitative summary judgment about it. Operations excellent, good customer relationships, good supplier relationships in spite of slow payment period. Growth rapid, but requires increasing financing of working capital (receivables and inventory). Increased interest expense on financing drags down profits (difference between EBIT and net profit) and weakens ratios. ROE growth misleading, driven by financial leverage more than operating performance. Use of temporary financing (short term) puts Cartwright under control of bank - no loan - no business. Temporary financing of permanent needs is a dangerous plight for him to be in. 4 points 4 points 4 points Total 12 points no liquidity ratio no coverage ratio otherwise comprehensive 4 points 4 points 4 points 1 points Total 13 points operations - top of IS financing - bottom of IS weak balance sheet connect hi growth to hi debt Q2 total 73 points of 100 for S1 Summary judgment: solid operating performance, underfinanced due to rapid growth increasing receivables and inventory, requiring increased borrowing from bank and suppliers...the segue to Wk 2 forecast. CARTWRIGHT LUMBER COMPANY (000 omitted) INCOME STATEMENTS -1 2002 0 2003 Revenue Cost of sales Gross profit Other operating income Distribution costs Administrative costs Depreciation & amortization exp Other operating costs Restructuring costs Total operating costs Profit from operations (EBIT) Interest, financing expense Income from investments Disposal of operations Profit before tax Income tax Profit after tax Minority interest Other Net profit Dividends Other Reinvested in the business PERIOD JANUARY 1-DECEMBER 31 -2 2001 1697.0 1222.0 475.0 0.0 425.0 0.0 0.0 0.0 0.0 1647.0 50.0 13.0 0.0 0.0 37.0 6.0 31.0 0.0 0.0 31.0 0.0 0.0 31.0 2013.0 1437.0 576.0 0.0 515.0 0.0 0.0 0.0 0.0 1952.0 61.0 20.0 0.0 0.0 41.0 7.0 34.0 0.0 0.0 34.0 0.0 0.0 34.0 2694.0 1950.0 744.0 0.0 658.0 0.0 0.0 0.0 0.0 2608.0 86.0 33.0 0.0 0.0 53.0 9.0 44.0 0.0 0.0 44.0 0.0 0.0 44.0 PER SHARE DATA Market price Extraordinary items per share Earnings per share - primary Earnings per share - fully diluted Dividends per share Price/earnings ratio Common shares outstanding 0.00 #DIV/0! #DIV/0! 0.00 #DIV/0! #DIV/0! 0.00 0.00 #DIV/0! #DIV/0! 0.00 #DIV/0! #DIV/0! 0.00 0.00 #DIV/0! #DIV/0! 0.00 #DIV/0! #DIV/0! 0.00 -1 2002 0 2003 GO TO ROW 222...FIND 2 SUPPLEMENTARY QUESTIONS-ANSWERS ABOUT 'BUSINESS RISK' AND 'FINANCIAL RISK' THIS WILL BE IMPORTANT GOING FORWARD BALANCE SHEETS PERIOD AS OF DECEMBER 31 ASSETS Current assets: Cash & equivalents Investments Trade receivables Inventory Other Total current assets Non-current assets: Property, plant & equipment-gro Accumulated deprec. & amort. Property, plant & equipment-net Investment property Goodwill Other 1 Other 2 Total non-current assets Total assets LIABILITIES AND EQUITY Current liabilities: Trade & other payables Retirement benefit obligation Tax liabilities Leases due in 1 year Loans, debt due in 1 year Other Total current liabilities Non-current liabilities: Retirement benefit obligation Deferred tax liabilities Finance leases due after 1 year Loans, debts due after 1 year Other Total non-current liabilities Total liabilities Stockholder's equity: Preferred stock Common stock Paid-in surplus Other Retained earnings Total equity Minority interest Total liabilities & equity -2 2001 58.0 0.0 171.0 239.0 0.0 468.0 48.0 0.0 222.0 326.0 0.0 596.0 41.0 0.0 317.0 418.0 0.0 776.0 126.0 0.0 126.0 0.0 0.0 0.0 0.0 126.0 594.0 140.0 0.0 140.0 0.0 0.0 0.0 0.0 140.0 736.0 157.0 0.0 157.0 0.0 0.0 0.0 0.0 157.0 933.0 124.0 0.0 0.0 0.0 112 24.0 260.0 192.0 0.0 0.0 0.0 153 30.0 375.0 256.0 0.0 0.0 0.0 240 39.0 535.0 0.0 0.0 0.0 64.0 0.0 64.0 324.0 0.0 0.0 0.0 57.0 0.0 57.0 432.0 0.0 0.0 0.0 50.0 0.0 50.0 585.0 0.0 0.0 0.0 0.0 270.0 270.0 0.0 594.0 0.0 0.0 0.0 0.0 304.0 304.0 0.0 736.0 0.0 0.0 0.0 0.0 348.0 348.0 0.0 933.0 FINANCIAL RATIOS PERIOD YEAR Liquidity Ratios Current ratio Quick ratio Days sales in receivables Days cost of sales in inventory Days cost of sales in payables Leverage Ratios Long-term debt to total capital Long-term debt to equity Times interest earned Full burden coverage Asset-Use (Efficiency) Ratios Fixed asset turnover Total asset turnover -2 2001 -1 2002 0 2003 1.8 0.9 36.8 71.4 37.0 1.6 0.7 40.3 82.8 48.8 1.5 0.7 42.9 78.2 47.9 19.2% 23.7% 3.8 3.2 15.8% 18.8% 3.1 2.5 12.6% 14.4% 2.6 2.2 13.5 2.9 14.4 2.7 17.2 2.9 Profitability Ratios Gross margin Operating profit margin Return on sales Return on total assets Return on equity (ROE) Return on invested capital (ROIC 28.0% 2.9% 1.8% 5.2% 11.5% 7.1% 28.6% 3.0% 1.7% 4.6% 11.2% 6.9% 27.6% 3.2% 1.6% 4.7% 12.6% 7.7% DuPont Formula - ROE Profitability Efficiency Leverage ROE Check 11.5% 1.8% 2.9 2.2 11.5% 11.2% 1.7% 2.7 2.4 11.2% 12.6% 1.6% 2.9 2.7 12.6% Compound Annual Growth Rates #DIV/0! Revenues #DIV/0! Gross profit #DIV/0! Operating profit (EBIT) #DIV/0! Total assets 18.6% 21.3% 22.0% 23.9% 33.8% 29.2% 41.0% 26.8% Appraise Cartwright's 'business risk'. Cartwright's business risk driven by changes in sales and changes in operating expenses, which drive changes in EBIT. BUSINESS RISK is coded GREEN on IS/BS Model, On the operating expense side, business risk is low because of cost control and efficient operations - controllable by linking IS and BS. It is analogous to OPERATING LEVERAGE; Mr. Cartwright. variation in EBIT driven by variations in sales and operating expenses. On the sales side, he has less control, subject to economic forces and competition. His customer and supplier relationsh Always consider degree of fixed cost in interpreting business risk - higher are good. He uses generous credit terms to build sales - but his customers (small builders) likely have cash flow problempercentage of fixed cost to total cost, higher business risk. of their own - so quality of receivables is low - bad debt risk. See Cohen Finance Workbook pps 29-32 Business risk is moderate, not on cost side, but on possibility of sales decline and collection of receivables. Appraise Cartwright's 'financial risk'. it is increasing - and highly risky. Most of his debt (82.8%) is short term debt, not revealed in the ratio analysis. short term debt 240 debt 290 long term debt 50 debt+equit 638 45.5% equity 348 ratio 82.8% st debt to If the bank does not roll over the short-term loan, and Cartwright can't find another lender, he would be forced to liquidate the business to repay the loan, or face bankruptcy. New equity is off the table because Cartwright recently bought out his equity partner - he does not want a partner. FINANCIAL RISK is coded RED on IS/BS Model, linking IS and BS. Interest expense is a fixed cost, the driver behind FINANCIAL LEVERAGE. See Cohen Finance Workbook, pps 109-11 Q A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 B C D E F G H 2005 2006 2007 2008 I J K L M ENTER DATA IN BLUE-COLORED CELLS Year Net Sales Growth rate in net sales Cost of goods soldet sales GS&A expenseset sales Long-term debt Current portion long-term debt Interest rate Tax rate Dividend/earnings after tax Current assetset sales Net fixed assets Current liabilities/ net sales Owner's equity (net worth) 2004 $2,694 2004 33.6% 20% 20% 20% 20% 72.4% 72.4% 72.4% 72.4% 72.4% 24.4% 24.4% 24.4% 24.4% 24.4% $ 50 $ 43 $ 37 $ 30 $ 23 $ 7 ### $ 7 $ 7 $ 7 8.0% 8.0% 8.0% 8.0% 8.0% 35.0% 35.0% 35.0% 35.0% 35.0% 0.0% 0.0% 0.0% 0.0% 0.0% 28.8% 28.8% 28.8% 28.8% 28.8% $ 157 $ 160 $ 163 $ 166 $ 168 11.2% 11.2% 11.2% 11.2% 11.2% $348 16 17 INCOME STATEMENT Year Net sales Cost of goods sold Gross profit GSA expense EBIT Interest expense Earnings before tax Tax Earnings after tax Dividends paid Additions to retained earnings Equations Forecast 2004 2004 2005 2006 2007 =B3+(B3*C4) $3,600 $4,320 $5,184 $6,220 =C5*C20 2,606 3,127 3,752 4,502 =C20-C21 994 1,1