QUESTIONS 1. Complete the 1992 columns of Tables 3 through 6, disregarding for now the projected data in the 1993 and 1994 columns. If you are using the spreadsheet model, use it to complete the tables. Be sure you understand all the numbers, as it would be most embarrassing and harmful to your career) if you were asked how you got a particular number and you could not give a meaningful response. 2. Based on the information in the case and on the results of your calculations in Question 1, prepare a list of Mark X's strengths and weaknesses. In essence, you should look at the common size statements and each group of key ratios (for example, the liquidity ratios) and see what those ratios indicate about the company's operations and financial condition. As a part of your answer, use the extended Du Pont equation to highlight the key relationships 3. Recognizing that you might want to revise your opinion later, does it appear, based on your analysis to this point that the bank should lend the requested money to Mark X? Explain. 4. Now complete the tables to develop pro forma financial statements for 1993 and 1994. For these calculations, assume that the bank is willing to maintain the present credit lines and to grant an additional $6,375,000 of short-term credit effective January 1, 1993. In the analysis, take account of the amounts of inventory and accounts receivable that would be carried il inventory utilization (based on the cost of goods sold) and days sales outstanding were set at industry-average levels. Also, assume in your forecast that all of Mark X's plans and predictions concerning sales and expenses materialize, and that the firm pays no cash dividends during the forecast period. Finally, in your calculations use the cash and marketable securities account as the residual balancing figure, In responding to Questions 5 through 8. no spreadsheet model modifications are required. Answers should be based solely on the data contained in the financial statements completed in response to Question 4. funds in excess of this amount will be invested in marketable securities, which on average will earn 7 percent interest. Based on your forecasted financial statements, will Mark X be able to invest in marketable securities in 1993 and 1994? If so, what is the amount of excess funds Mark X should invest in marketable securities? Do your financial forecasts reveal any developing conditions that should be corrected? 6. Based on the forecasts developed earlier, would Mark X be able to retire all of the outstanding short-term loans by December 31, 1993? In answering this question, assume that the firm will, if possible, repay the loans at a constant rate throughout the year. Therefore, on average, the amount of short-term loans outstanding will be half of the beginning of year amount. 7. If the bank decides to withdraw the entire line of credit and to demand immediate repayment of the two existing loans, what alternatives would be available to Mark X? 8. Under what circumstances might the validity of comparative ratio analysis be questionable? Answer this question in general, not just for Mark X, but use Mark X data to illustrate your points. 9. Revise your pro forma financial statements for 1993 and 1994 assuming both of the following conditions: a. Short-term loans will be repaid when sufficient cash is available to do so without reducing the liquidity of the firm below the minimum requirements set by the bank and when the company is able to maintain at least the minimum cash balance (5 percent). b. Mark X will reinstate a 25 percent cash dividend in the year that all short-term loans and credit lines have been fully cleaned up (paid in full). 10. It is apparent that Mark X's future and that of the bank loan) is critically dependent on the company's performance in 1993 and 1994. Therefore, it would be useful if you could, as part of your consulting report, inform management and the bank-as to how sensitive the results are to such things as the sales growth rate, the cost of goods sold percentage, and the administrative expense ratio. If the results would still look fairly good even if those factors were not as favorable as initially forecasted, the bank would have greater confidence in extending the requested credit. On the other hand, if even tiny changes in these variables would lead to a continuation of the past downward trend, then the bank should be leery. If you are using the spreadsheet model, do some sensitivity analyses (using data tables) to shed light on this issue. (Hint: See the bottom part of the model labeled "SENSITIVITY ANALYSES" for some ideas.) If you do not have access to the model, describe how one would go about a sensitivity (or scenario) analysis, but do not quantify your answer. 11. On the basis of your analyses, do you think Karen should recommend that the bank extend the existing short and long-term loans and grant the additional $6,375,000 loan, or that the bank demand immediate repayment of all existing loans? If she does recommend continuing to support the company, what conditions (for example, collateral, guarantees, or other safeguards) might the bank impose to help protect against losses should Mark X's plans go awry? Table 1 Historical and Pro Forma Balance Sheets for Years Ended December 31 (In Thousands of Dollars) 1992 Pro Forma 1993 1994 1990 1991 Assets: Cash and marketable securities $ 5,149 $ 4,004 Accounts receivable 17,098 18,462 Inventory 18,934 33,029 Current assets $ 41,181 $ 55,495 Land, buildings, plant, and equipment $ 17,761 $ 20,100 Accumulated depreciation (2.996) (4.654) Net fixed assets $ 14,765 $ 15,446 Total assets $ 55,946 $ 70,941 $ 3,906 29,357 46,659 $79,922 $ 22.874 (6,694) $ 16,180 $ 96,102 $ 29,249 19.117) $ 20.132 $ 30,126 (10.940) $ 19.186 $ 16,795 11.626 Liabilities and Equity: Short-term bank loans Accounts payable Accruals Current liabilities Long-term bank loans Mortgage Long-term debt Total liabilities Common stock Retained earnings Total equity Total liabilities and equity $ 3,188 $ 5,100 $ 18,233 6,764 10,506 19,998 15,995 3,443 5.100 7.331 9,301 $ 13,395 $ 20,706 $ 45,562 $ 6,375 $ 9,563 S 9,563 $ 9,563 2.869 2.6012.340 2.104 $ 9,244 $ 12.164 $11.903 $11.66 $ 22,639 $ 32,870 $57,465 $ 23,269 $ 23,269 $ 23,269 $ 23,269 10.038 14.802 15,368 $33,307 $ 38,071 $ 38,637 $ 55,946 $ 70,941 $ 96,102 $ 9,563 1.894 $11.457 Notes: a. 3,500,000 shares of common stock were outstanding throughout the period 1990 through 1992 b. Market price of shares: 1990 - $17.78; 1991 - $9.71: 1992 - $3.67 c. Price/eamings (P/E) ratios: 1990-6.61: 1991 -5.35; 1992-17.0. The 1992 P/E ratio is high because of the depressed earnings that year. d. Assume that all changes in interest-bearing loans and gross fixed assets occur at the start of the relevant years. e. The mortgage loan is secured by a first-mortgage bond on land and buildings. f. Pro Forma Inventory is based on industry standards and anticipated cost of goods sold. Table 2 Historical and Pro Forma Income Statements for Years Ended December 31 (In Thousands of Dollars) Pro Forma 1993 1994 520 3.768 Net sales Cost of goods sold Gross profit Administrative and selling expenses Depreciation Miscellaneous expenses Total operating expenses EBIT Interest on short-term loans Interest on long-term loans Interest on mortgage Total interest Before-tax earnings Taxes Net income Dividends on stock Additions to retained earnings 1990 $170,998 137.684 $ 33,314 $ 12,790 1,594 2.027 $ 16,411 $ 16,903 $ 319 638 258 $ 1.215 $ 15,688 6,275 $ 9.413 $ 2.353 $ 7,060 1991 1992 $184,658 $195,732 151.761 166,837 S 32,897 $ 28,895 S 15,345 $ 16,881 1,658 2,040 3,557 5.725 S 20,560 $ 24.646 S 12,337 $ 4.249 $ 561 $ 1,823 956 956 234 211 $1.751 $ 2.990 S 10,586 $ 1.259 4.234 $ 6,352 $ 755 $ 1.588 $ 189 $ 4,764 $ 567 $ 14.263 S 2,953 $ 2,953 956 170 S4008 $ 4,079 $21.953 8.781 504 Notes: a. Earnings per share (EPS): 1990 - $2.69: 1991 - 51.81: 1992-$0.22. b. Interest rates on borrowed funds: Short-term loan: 1990 - 10%; 1991-11%; 1992-10%. Long-term loan: 10% for each year. Mortgage: 9% for each year. c. For purposes of this case, assume that expenses other than depreciation and interest are totally variable with sales. Table 3 Common Size Balance Sheets for Years Ended December 31 (all numbers are in percentages) 1990 1991 1992 4.06 48.55 Assets: Cash and marketable securities Accounts receivable Inventory Current assets Land, buildings, plant, and equipment Accumulated depreciation Net fixed assets Total assets 9.20 30.56 33.84 73.61 31.75 (5.36) 5.64 26.02 46.56 78.23 28.33 (6.56) 21.77 100.00 2639 6.97) 16.84 100.00 100.00 5.70 12.09 6.15 23.94 11.39 7.19 14.81 7.19 29.19 13.48 18.97 20.81 7.63 47.41 Liabilities and Equity: Short-term bank loans Accounts payable Accruals Current liabilities Long-term bank loans Mortgage Long-term debt Total liabilities Common stock Retained earnings Total equity Total liabilities and equity 5.13 3.67 2.43 59.80 24.21 16.52 40.47 41.59 17.94 59.53 100.00 17.15 46.33 32.80 20.86 53.67 100.00 100.00 Table 4 Common Size Income Statements for Years Ended December 31 (all numbers are in percentages) 1992 100.00 8.62 9 Net sales Cost of goods sold Gross profit Administrative and selling expenses Depreciation Miscellaneous expenses Total operating expenses EBIT Interest on short-term loans Interest on long-term loans Interest on mortgage Total interest Before-tax earnings Taxes Net income 1990 100.00 80.52 19.48 7.48 0.93 1.19 9.60 9.88 0.19 0.37 0.15 0.71 9.17 3.67 5.50 1991 100.00 82.18 17.82 8.31 0.90 1.93 11.13 6.68 0.30 0.52 0.13 0.95 5.73 2.29 3.44 2.17 xx 0.11 1.53 0.64 x 0.39 Dividends on stock Additions to retained earnings 1.38 4.13 0.86 2.58 0.10 0.29 Table 5 Statement of Cash Flows for Years Ended December 31 (In Thousands of Dollars) 1991 1992 $195,732 Cash Flow from Operations: Sales Increase in receivables Cash sales Cost of goods sold Increase in inventories Increase in accounts payable Increase in accruals Cash cost of goods Cash margin Administrative and selling expenses Miscellaneous expenses Taxes Net CF from operations S X (166,837) (13,630) 9,492 $184,658 (1,364) $183.294 (151,761) (14,095) 3,742 1,657 ($160,457) $ 22,837 (15,345) (3,557) (4.234) ($ 299) ($ 16,881) (5,725) (504) Cash Flow from Fixed Asset Investment: Investment in fixed assets ($ 2,339) ($ 2,774) $ 13,133 Cash Flow from Financing Activities: Increase in short-term debt Increase in long-term debt Repayment of mortgage Interest expense Common dividends Net cash flow from financing activities Increase (decrease) in cash and marketable securities $ 1.912 3,188 (268) (1,751) (1,588) $ 1,493 (261) (2.990) (189) $ 9,693 ($ 1,145) Table 6 Historical and Pro Forma Ratio Analysis for Years Ended December 31 Pro Forma Industry 1990 1991 1992 1993 1994 Average Liquidity Ratios: Current ratio Quick ratio 3.07 1.66 2.68 1.08 X 0.73 X 1.10 X X 2.50 1.00 Debt Management Ratios: Debt ratio TIE coverage 40.47% 46.33% 13.917.05 X 1.42 x 52.69% X 6.38 50.00% 7.70 5.70 Asset Management Ratios: Inventory turnover (cost) Inventory turnover (sales) Fixed asset turnover Total asset turnover Days sales outstanding (ACP) 7.27 9.03 11.58 3.06 36.00 4.593.58 5.59 4.19 11.96 12.10 2.60 X 35.99 53.99 5.70 X 10.69 2.03 x 5.70 7.00 12.00 3.00 32.00 32.00 Profitability Ratios: Profit margin Gross profit margin Return on total assets ROE 5.50% 19.48% 16.82% 28.26% 3.44% 17.82% 8.95% 16.68% 0.39% X X 14.76% 17.50% 20,00% X 5.74% 10.76% 1.96% X X 2.90% 18.00% 8.80% 17.50% Other Ratios: Altman Z factor Payout ratio 5.08 6.55 25.00% 4.68 X 25.00% 25.00% X 0.00% 4.65 20.00% Notes: a. Uses cost of goods sold as the numerator. b. Uses net sales as the numerator. C. Assume a 360-day year. d. Altman's function is calculated as Z= 0.012x + 0.014x + 0.033x, +0.006X, +0.999X Here x = net working capital/total assets. X = retained earnings/total assets X = EBIT/total assets X = market value of common and preferred stock/book value of all debt. X = sales/total assets. The "Altman Z score range of 1.81-2.99 represents the so-called "zone of ignorance." Note that the first four variables are expressed as percentages. Refer to Appendix 26A of Eugene F. Brigham and Louis C. Gapenski, Intermediate Financial Management (Hinsdale, Ill: Dryden Press, 1993), for details