Susan Murphy, the financial manager at Murphy, Inc. is attempting to forecast the company's short-term borrowings and investments for the next 12 months. She estimates the term structure of interest rates in Table 1. The Balance Sheet as of August 31, 2020 is in Table 2. Murphy terms of sale are net 30. However, her collections are 0% during the month of the sale, 60% during the month following the sale and 40% in the second month following the sale. Murphy purchases inventory equal to the following month's cost of sales. Purchases have terms of net 30; she pays suppliers on the net date. General and administrative costs are $28,000 per month. Murphy pays quarterly taxes in March, June, September and December. Depreciation is $2,800 per month. Murphy's' gross profit margin is 34%. As part of a loan covenant, Murphy's' bank requires a minimum ending cash balance of $8,000. Table 3 contains the marketing department's sales forecast. Murphy's' has two short-term investments: 30-day and 90-day CDs. Financing sources are 30-day, 90-day and long-term loans. Susan will borrow long-term only in September. For forecasting purposes, Susan does not include investments or borrowings beyond August of 2021. Susan's forecasting model assumes that cash flows occur at the end of the month. Thus, Murphy begins earning/paying interest in the month following the investment/borrowing (Murphy earns and pays interest on a monthly basis). Murphys' tax rate is 21%. In August, Murphy refinanced all debt and rolled over all C/Ds at the August rates. Answer the following questions: 1. Develop a model to forecast Murphy's' income statement, balance sheet, and cash budget for each month from September 2020 through August 2021. At this point, do not consider an investing/borrowing strategy. That is, assume that Susan does not schedule any borrowings or investments. How do Murphy's monthly ending cash balances compare to the bank's required minimum? 2. Determine a financing strategy that allows Murphy to maximize her net interest income over the planning period. 3. Determine a financing strategy that is consistent with conservative working capital management (no short-term financing). 4. Susan does not aggressively enforce her terms of sale. She believes her sales are higher because of her soft enforcement. She estimates that the sales forecast in Table 3 would be 3% lower each month if she strictly enforced Murphys' terms of sale (net 30). How much would Jones' interest costs change if she strictly enforces the company's terms of sale? 5. Evaluate the strategies from questions 2, 3 and 4 above. Which would you recommend? Why? Table 1. Term Structure of Interest Rates LTD N/P90 6.84% AUG 7.81% C/D30 C/D90 N/P30 2.29% 3.22% 5.98% 2.24% 2.99% 6.04% 2.24% 2.99% 6.10% SEP OCT NOV 6.15% 2.19% 2.13% 2.88% 2.83% DEC 6.13% JAN 6.11% FEB 2.13% 2.79% 2.13% 2.78% 2.12% 2.77% 6.04% 6.84% 7.80% 6.84% 7.80% 6.84% 7.79% 6.82% 7.71% 6.75% 7.66% 6.64% 7.66% 6.57% 7.66% 6.40% 7.66% 6.05% 7.66% 5.92% 7.66% 5.83% 7.66% MAR 6.00% APR 5.95% MAY JUN 2.10% 2.72% 2.06% 2.71% 2.01% 2.68% 1.92% 2.63% 1.88% 2.54% 5.93% 5.86% JUL 5.79% 5.62% AUG 5.79% 7.66% Table 2. August Balance Sheet. Cash CD (30) CD (90) Receivables Inventory Current Assets Gross PPE Depreciation Net PPE Total Assets 8,000 6,000 14,000 452,540 109,296 589,836 522,000 52,000 470,000 1,059,836 166,163 0 Trade Payables Taxes Payable N/P (30) N/P (90) Current Liabilities L/T Debt Equity Total Liabilities & Equity 0 92,000 258,163 430,000 371,673 1,059,836 Table 3. Sales Forecast JUL AUG SEP OCT NOV DEC JAN FEB MAR APR MAY JUN JUL AUG SEP 207,000 186,300 165,600 372,600 621,000 414,000 331,200 248,400 248,400 182,160 428,490 745,200 517,500 430,560 335,340 * Actual sales, all others forecasted Susan Murphy, the financial manager at Murphy, Inc. is attempting to forecast the company's short-term borrowings and investments for the next 12 months. She estimates the term structure of interest rates in Table 1. The Balance Sheet as of August 31, 2020 is in Table 2. Murphy terms of sale are net 30. However, her collections are 0% during the month of the sale, 60% during the month following the sale and 40% in the second month following the sale. Murphy purchases inventory equal to the following month's cost of sales. Purchases have terms of net 30; she pays suppliers on the net date. General and administrative costs are $28,000 per month. Murphy pays quarterly taxes in March, June, September and December. Depreciation is $2,800 per month. Murphy's' gross profit margin is 34%. As part of a loan covenant, Murphy's' bank requires a minimum ending cash balance of $8,000. Table 3 contains the marketing department's sales forecast. Murphy's' has two short-term investments: 30-day and 90-day CDs. Financing sources are 30-day, 90-day and long-term loans. Susan will borrow long-term only in September. For forecasting purposes, Susan does not include investments or borrowings beyond August of 2021. Susan's forecasting model assumes that cash flows occur at the end of the month. Thus, Murphy begins earning/paying interest in the month following the investment/borrowing (Murphy earns and pays interest on a monthly basis). Murphys' tax rate is 21%. In August, Murphy refinanced all debt and rolled over all C/Ds at the August rates. Answer the following questions: 1. Develop a model to forecast Murphy's' income statement, balance sheet, and cash budget for each month from September 2020 through August 2021. At this point, do not consider an investing/borrowing strategy. That is, assume that Susan does not schedule any borrowings or investments. How do Murphy's monthly ending cash balances compare to the bank's required minimum? 2. Determine a financing strategy that allows Murphy to maximize her net interest income over the planning period. 3. Determine a financing strategy that is consistent with conservative working capital management (no short-term financing). 4. Susan does not aggressively enforce her terms of sale. She believes her sales are higher because of her soft enforcement. She estimates that the sales forecast in Table 3 would be 3% lower each month if she strictly enforced Murphys' terms of sale (net 30). How much would Jones' interest costs change if she strictly enforces the company's terms of sale? 5. Evaluate the strategies from questions 2, 3 and 4 above. Which would you recommend? Why? Table 1. Term Structure of Interest Rates LTD N/P90 6.84% AUG 7.81% C/D30 C/D90 N/P30 2.29% 3.22% 5.98% 2.24% 2.99% 6.04% 2.24% 2.99% 6.10% SEP OCT NOV 6.15% 2.19% 2.13% 2.88% 2.83% DEC 6.13% JAN 6.11% FEB 2.13% 2.79% 2.13% 2.78% 2.12% 2.77% 6.04% 6.84% 7.80% 6.84% 7.80% 6.84% 7.79% 6.82% 7.71% 6.75% 7.66% 6.64% 7.66% 6.57% 7.66% 6.40% 7.66% 6.05% 7.66% 5.92% 7.66% 5.83% 7.66% MAR 6.00% APR 5.95% MAY JUN 2.10% 2.72% 2.06% 2.71% 2.01% 2.68% 1.92% 2.63% 1.88% 2.54% 5.93% 5.86% JUL 5.79% 5.62% AUG 5.79% 7.66% Table 2. August Balance Sheet. Cash CD (30) CD (90) Receivables Inventory Current Assets Gross PPE Depreciation Net PPE Total Assets 8,000 6,000 14,000 452,540 109,296 589,836 522,000 52,000 470,000 1,059,836 166,163 0 Trade Payables Taxes Payable N/P (30) N/P (90) Current Liabilities L/T Debt Equity Total Liabilities & Equity 0 92,000 258,163 430,000 371,673 1,059,836 Table 3. Sales Forecast JUL AUG SEP OCT NOV DEC JAN FEB MAR APR MAY JUN JUL AUG SEP 207,000 186,300 165,600 372,600 621,000 414,000 331,200 248,400 248,400 182,160 428,490 745,200 517,500 430,560 335,340 * Actual sales, all others forecasted