I need help with solutions to the attached excel file. Both questions 1 and 2

Case Cartwright Lumber Company Download the case as part of Coursepack - see "Link to Buy Cases" in Bb Main Course Menu. Learning Objectives This case develops your financial statement analysis skills using ratios. In Wks 1&2 you will also deal with the use of accounts receivable (credit policy) as a marketing tool, the relationship between accounts receivable and debt on the balance sheet, and short-term vs. long-term borrowing. IS-BS MODEL AND FLOW DIAGRAM: see the 2nd and 3rd tabs of this Excel file PRINT A COLOR COPY OF BOTH THE IS-BS MODEL AND THE FLOW DIAGRAM. BOTH ARE REFERRED TO CONSTANTLY. IF NOT COLOR, THEN BLACK AND WHITE. THEY ARE EXPLAINED IN THE COHEN FINANCE WORKBOOK THE IS-BS MODEL LOOKS SIMPLE ENOUGH, AN ABBREVIATED SET OF FINANCIAL STATEMENTS. EVERYTHING YOU DO IN THIS COURSE IS BUILT AROUND THIS MODEL, COLOR-CODED TO ILLUSTRATE THE ELEMENTS OF FINANCIAL ANALYSIS. KEEP A COPY BY YOUR SIDE AS YOU WORK. THE FLOW DIAGRAM SHOWS EACH OF THE SPREADSHEETS YOU WILL WORK ON, AGAIN, ILLUSTRATING THE ELEMENTS OF FINANCIAL ANALYSIS AND HOW THEY ARE LINKED. Reading The Cohen Finance Workbook replaces weekly 'files' used in MBAD 6233; the chapters are the 'files'. Chapter 1, Cohen Finance Workbook, for review of accounting Chapter 2, Cohen Finance Workbook, for details on financial ratio analysis. Questions 1 See Tab Q1 Answer the questions on the tab, parts 1a, 1b, 1c. Keep your answers within the boxes provided. 2 See Tab Q2 Perform a full ratio analysis of Cartwright Lumber Company, answering Q2a-2f. Ratios are already calculated for you. Use the ratio speadsheet to learn how the ratios are calculated. Notice the formula in the cell where each ratio appears, then learn how to create those formulas on your own so you can do calculations in Excel spreadsheets. SAVE THE FILE AS: 'yournameWk1assign' and submit via Bb. THERE IS NO SINGLE CORRECT ANSWER TO THIS CASE. THE PURPOSE OF THE ASSIGNMENT IS TO LEARN THE PROCESS OF FINANCIAL STATEMENT ANALYSIS (S1) AND FORECASTING (S2). 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. DO NOT SHRINK FROM THAT RESPONSIBILITY! Observe spreadhseet conventions: Please notice how spreadsheet style conventions are observed in this box: Text does not run outside the box. Please get in the habit of writing your text in Excel this way, by hitting the ENTER key when your text fills the width of the box. Do not write in Word, then copy your text into Excel. Copyright (C) 2011 by Neil G. Cohen. All rights reserved. Please save with 125% zoom and 12 pt. font. 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 A B C D E F Do not let line length overflow a box. Hit ENTER when the number of characters fills the row. G H Q1: ROE, ROA, ROIC are given for Cartwright Lumber and Apex Lumber. Read the Cohen Finance Book 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 ROE ROA ROIC Ratio 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) 1a: Why is Cartwright ROE higher than Apex ROE? Is it better? Why? Why not? Write answer in box. 1b: Why is Cartwright ROA lower than Apex ROA? What does it tell you about the two companies? Write answer in box. 1c: How do the Cartwright & Apex ROICs compare? What does this suggest about the two companies? Write answer in box. Denominator Equity Total Liab+Eq Total Liab+Eq 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 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 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. Read the assignment questions so you know what you have to do for the assignment. Then, read the rest of the case. Cohen Finance Workbook chapter 2 goes with this assignment. Read as necessary. Cartwright's financial statements are below for your convenience. Financial ratios are automatically calculated in the panels below the financial statements. Examine the cell contents to learn how the ratio calculations work in Excel. You are expected to perform calculations, when necessary, in 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. 1b: Using the ratios below, appraise the trend (2001-03) of Cartwright's leverage. Cite specific ratios to justify your analysis. 1c: Using the ratios below, appraise the trend (2001-03) of Cartwright's asset use (efficiency). Cite specific ratios to justify your analysis. 1d: Using the ratios below, appraise the trend (2001-03) of Cartwright's profitability. Cite specific ratios to justify your analysis. 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. 1f: Based on Cartwright's 2001-03 performance, make a qualitiative summary judgment about it. 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 expense 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 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-gross 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 -1 2002 0 2003 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 M N O