I need help with the attached homework assignment. Any assistance would be greatly appreciated.

Thanks!

Case Cartwright Lumber Company Learning Objectives Following the Wk1 assignment on financial ratio analysis of the historical financial statements and making a judgment about Cartwright's business, Wk2 forecasts the financial statements five years into the future so you can understand the relationship between sales growth and external financing needs (EFN). Reading If your knowledge of accoounting terminology and ratios is sound, good. If not, re-read Chapters 1 & 2. Treat accounting as a foreign language that must be mastered; without it you understand little or nothing. Know the content of Chapters 1 & 2 so you can refer to them when necessary. IT IS VITAL THAT YOU STUDY THE Wk1 SOLUTIONS TEMPLATE BEFORE PROCEEDING WITH THE Wk2 ASSIGNMENT. The 4th & 5th tabs in this Excel file are the Wk1 Assignment Q1 & Q2 Solutions so you don't have to switch back-and-forth between two Excel files. Notice that the GRAY-colored part of the FLOW DIAGRAM is what you are learning about in Wks 1&2 Assignments. Chapter 3 discusses the Wk2 assignment, based on the short-form forecasting explained step-by-step beginning on page 33. Also, pay special attention to the discussion of: a circular reference beginning on page 41 b external financing needed beginning on page 42 c free cash flow definition on page 45 Questions 1 The short-form forecasting model (Q1 tab) shows 2003 as the base year (historical) and five forecast years, 2004-08. The forecast assumptions are entered for you in C4.G15. Show your understanding of the short-form forecasting model by answering the questions in the Q1 boxes. 2 Study the table on p 45 of the Cohen finance book and explanations on the pages following. Then, as the banker, explain to Mr. Cartwright why he must reduce his sales growth rate to reduce his external financing needs, supported by a revised forecast. The Q2 tab includes a working (with the formulas) version of the p 45 table and the short-form forecasting model with zeros in the assumption cells C4,G15, so you can forecast a scenario more to the banker's liking than the scenario in the Q1 & Q2 tab. 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. 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: 1-HISTORICAL RATIOS FINANCING DEBT EQUITY DEBT 2-K-WACC HISTORICAL RATIOS I/S & B/S FORECAST EFN 3-CAPITAL BUDGETING 4-FORECAST & EFN 5-EQUITY VALUATION 6-FINANCING LONG-FORM FORECAST I/S, B/S, & RATIOS EQUITY EBIT CHART income CAPITAL BUDGETING OP & CAP NATCF, NPV, IRR, PAYBACK K-WACC VALUATION ENTERPRISE VALUE USING FREE CASH FLOW MARKET MULTIPLES: P/E, MV/BV, REV, EBIT risk control mktblty flexblty timing 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 12.1% 42.2/348 4.5% 42.2/933 7.7% 86*.83/933 Ratio Fraction 7.8% 64.7/833 6.9% 64.7/933 7.7% 86*.83/933 RETURN ON EQUITY RETURN ON ASSETS RETURN ON INVESTED CAPITAL Numerator Earnings after tax Earnings after tax EBIT * (1-Tax rate) Denominator Equity 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 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 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 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 K L M N O P Q2: FINANCIAL STATEMENT ANALYSIS WITH RATIOS 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. 27.4% Fixed asset turnover 13.5 14.4 17.2 1.1% Total asset turnover 2.9 2.7 2.9 8 points for citing general increase Fixed asset turnover is healthy, indicating that fixed assets (growing little) drive increased sales. 4 points for distinguishing the two ratios Total asset turnover changes little, because receivables and inventory are increasing as sales increases. TOTAL 12 points minus 2 if trend not discussed 1d: Using the ratios below, appraise the trend (2001-03) of Cartwright's profitability. Cite specific ratios to justify your analysis. -1.3% Gross margin (GM) 28.0% 28.6% 27.6% 8.3% Operating profit margin (OPM) 2.9% 3.0% 3.2% -10.6% Return on sales (ROS) 1.8% 1.7% 1.6% -9.6% Return on total assets (ROTA) 5.2% 4.6% 4.7% 10.1% Return on equity (ROE) 11.5% 11.2% 12.6% 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. 0.5 point 0.5 point 0.5 point 0.5 point 0.5 point 0.5 point 4 points 5 points Total 12 points 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. -10.6% Profitability 1.8% 1.7% 1.6% 1.1% Efficiency 2.9 2.7 2.9 21.9% Leverage 2.2 2.4 2.7 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. 4 points Leverage uses unique numerator & denominator, no coverage ratio included, nothing on liquidity ratios, days in rec,invn,pay. 4 points Substantial ROE growth driven by large, significant 21.9% increase in leverage (financial risk) and small insignificant 4 points 1.1% increase in efficiency and significant 10.6% decrease in profitability. Total 12 points 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 1 points Total 13 points distinction between IS and BS ratios ROIC removes impact of leverage minus 2 if trend not discussed no liquidity ratio no coverage ratio otherwise comprehensive 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 PERIOD JANUARY 1-DECEMBER 31 -2 2001 -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 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 GO TO ROW 222...FIND 2 SUPPLEMENTARY QUESTIONS-ANSWERS ABOUT 'BUSINESS RISK' AND 'FINANCIAL RISK' THIS WILL BE IMPORTANT GOING FORWARD 0 2003 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-gros 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 -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% #DIV/0! #DIV/0! #DIV/0! #DIV/0! 18.6% 21.3% 22.0% 23.9% 33.8% 29.2% 41.0% 26.8% 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 Compound Annual Growth Rates Revenues Gross profit Operating profit (EBIT) Total assets 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 relationship 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 problems percentage 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 d 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 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) B C ENTER DATA IN BLUE-COLORED CELLS 2004 $2,694 2004 D E F G 2005 2006 2007 2008 33.6% 20% 20% 72.4% 72.4% 72.4% 24.4% 24.4% 24.4% $ 50 $ 43 $ 37 $ 7 ### $ 7 8.0% 8.0% 8.0% 35.0% 35.0% 35.0% 0.0% 0.0% 0.0% 28.8% 28.8% 28.8% $ 157 $ 160 $ 163 11.2% 11.2% 11.2% 20% 20% 72.4% 72.4% 24.4% 24.4% $ 30 $ 23 $ 7 $ 7 8.0% 8.0% 35.0% 35.0% 0.0% 0.0% 28.8% 28.8% $ 166 $ 168 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 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 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 Q1a Discuss how the interest expense (row 25) calculation works and whether or not it includes the short term borrowing on the Cartwright balance sheet. HINT: What is the source of the data used in the ratio on row 14? Q1b How much does Cartwright need to borrow and when? Explain by citing specifics from the forecast. Q1c Does Cartwright have the ability to pay the interest expense? Explain by citing specifics from the forecast. Q1d Does Cartwright have the ability to repay the loan principal? Explain by citing specifics from the forecast. H I J A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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) B C ENTER DATA IN BLUE-COLORED CELLS 2004 $2,694 2004 D E F G H 2005 2006 2007 I J K 2008 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 =B3+(B3*C4) $2,694 $2,694 $2,694 =C5*C20 0 0 0 =C20-C21 2,694 2,694 2,694 =C6*C20 0 0 0 =C22-C23 2,694 2,694 2,694 =(C7+C8)*C9 0 0 0 =C24-C25 2,694 2,694 2,694 =C10*C26 943 943 943 =C26-C27 1,751 1,751 1,751 =C11*C28 0 0 0 =C28-C29 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 2007 $2,694 0 2,694 0 2,694 0 2,694 943 1,751 0 1,751 2008 $2,694 0 2,694 0 2,694 0 2,694 943 1,751 0 1,751 0 166 166 0 168 168 31 32 33 34 35 0 157 157 0 160 160 0 163 163 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: Assumptions: revenue 50 net profit margin 48 49 51 52 53 54 55 56 57 58 59 60 61 62 63 2694 1.6% CHANGE IN ASSETS (USES OF FUNDS) cents CURRENT ASSETS RECEIVABLES INVENTORY CA/SALES 317 418 27.3 FIXED ASSETS LONG-TERM DEBT PLANT PROPERTY EQUIPMENT FA/SALES CHANGE IN LIABILITIES+EQUITY (SOURCES OF FUNDS) cents CURRENT LIABILITIES PAYABLES 256 OTHER ACC 39 CL/SALES 11.0 0 EQUITY INCR IN RET EARN/S 1.6 0.0 TOTAL FORECASTED SOURCES 12.6 EXTERNAL FINANCING NEEDED 14.7 ADJUSTED TOTAL FORECASTED 27.3 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 108 109 110 111 112 TOTAL FORECASTED USES 27.3 Q2a Explain how the p 45 table from the Cohen Finance Workbook, shown above starting on row 47, works and its significance to Cartwright's (and most other businesses too) external financing needs problem. Q2b Revise the short-form forecast model from Q1, using the 'input cells zeroed' model at the top of this tab. As the banker, assume a lower growth rate in sales, and explain, showing specifics from the revised forecast, how it helps Cartwright solve his external financing needed problem. Enter revised data in the blue cells, using your judgment