Garden State Container Corporation Case

1.Complete the 1990, 1991 and 1992 columns of Tables 3 through 6 in the Excel file, disregarding for now the projected data in the 1993 and 1994 columns. You should enter formula in the appropriate cells for calculations. Keep in mind that numbers in brackets represent negatives or values that will be subtracted. Underlined cells usually represent either total or a net value. 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. Make sure to enter the appropriate formula in each cell. You will need this for future analysis. 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 on January 1, 1993. No short-term loans will be repaid in 1993 and 1994. In the analysis, take account of the amounts of inventory and accounts receivable that would be carried if target inventory turnover (based on the cost of goods sold) and days sales outstanding (Average Collection Period) were set at industry-average levels (that is, assume these ratios are equal to industry averages for each year and use them to compute Inventories and Accounts Receivable forecasts. Assume forecasted Inventories are constant throughout each year and equal to the average year value). Also, assume in your forecast that all of Mark X's plans and predictions concerning sales and expenses materialize (see the Case description), and that the firm pays no cash dividends during the forecast period. That is, read carefully the assumptions regarding Sales increase, cost of goods sold as percentage of sales, etc. Enter each assumption in the Input Table in the Excel file. Use the appropriate cells of the Input Table in your calculations/formulas. Finally, use the cash and marketable securities account as the residual balancing figure. That is, compute all other missing values first, then compute Cash and Marketable Securities so that the Balance Sheet is balanced. Keep in mind that Additions to Retained Earnings value from the Income Statement is used in the Balance Sheet (current year Retained Earnings = Retained Earnings of the previous year + Additions to Retained Earnings). 5.Assume Mark X has determined that its optimal cash balance is 5 percent of sales and that 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 cash (over and above the optimal cash balance) Mark X should invest in marketable securities? What does this mean for the company and the bank? 6.Based on the forecasts developed earlier, would Mark X be able to retire some/all of the outstanding short-term loans by December 31, 1993? Assume desired cash balance is 5 percent of sales. 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? 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 (Z score and Current Ratio) are to the sales growth rate. If the results would still look fairly good even if sales 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 this variable would lead to a continuation of the past downward trend, then the bank should be leery. Fill in the last table in the Excel file. For example, the top left cell in the first panel should contain the 1994 Z score when only 40% of forecasted Sales where realized in 1993 and 40% in 1994. You can copy your excel tables to a new sheet, change the Sales appropriately for 1993 and 1994, your Z score will adjust accordingly. 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