#7. EPC uses a monthly cash budget for the coming year. The company sells on terms of 2/10, net 60. 20% of customers pay in the 1st month and take discounts, 70% pay on time in the 2nd month, and 10% pay in the 3rd month (no bad debts). Purchases are 60% of next month's sales, and payments for purchases are made the month after the purchase. Monthly gross sales are shown on the cash budget, and sales are adjusted for the 2% cash discount. The nominal cost of non-discount credit is 14.9%. Please complete the firm's cash budget table (on the next page) for the third quarter (July September) of fiscal year 2019 and estimate the maximum loan amount required for the quarter. Dollar amounts are in million $. (10 points) May June July October $200 $250 $300 $350 August September $400 $500 0% 0% $400 0% 0% 0% $250 0% $350 $200 $300 $500 Month Panel A: Forecasted gross sales Adjustment: % deviation from forecast Adjusted gross sales forecast Panel B: Collections on sales During sales' month: During 2nd month: Due in 3rd month: Total collections Panel C: Purchases: 60% of next month's sales Panel D: Payments Pmt for last month's purchases (30 days of credit) Wages and salaries Lease payments Other payments interest on LT bonds, dividends, etc.) Taxes Payment for plant construction Total payments Panel E: Net cash flows Assumed excess cash on hand at start of forecast period Net cash flow (NCF): Total collections - Total payments Cumulative NCF: Prior month cum plus this month's NCF Panel F: Net cash position before financing or investing Target cash balance Surplus cash or loan needed: Cum NCF Target cash $30 $40 $50 $30 $30 $30 $30 $30 $30 $30 $100 SO $10 $10 $10 #7. EPC uses a monthly cash budget for the coming year. The company sells on terms of 2/10, net 60. 20% of customers pay in the 1st month and take discounts, 70% pay on time in the 2nd month, and 10% pay in the 3rd month (no bad debts). Purchases are 60% of next month's sales, and payments for purchases are made the month after the purchase. Monthly gross sales are shown on the cash budget, and sales are adjusted for the 2% cash discount. The nominal cost of non-discount credit is 14.9%. Please complete the firm's cash budget table (on the next page) for the third quarter (July September) of fiscal year 2019 and estimate the maximum loan amount required for the quarter. Dollar amounts are in million $. (10 points) May June July October $200 $250 $300 $350 August September $400 $500 0% 0% $400 0% 0% 0% $250 0% $350 $200 $300 $500 Month Panel A: Forecasted gross sales Adjustment: % deviation from forecast Adjusted gross sales forecast Panel B: Collections on sales During sales' month: During 2nd month: Due in 3rd month: Total collections Panel C: Purchases: 60% of next month's sales Panel D: Payments Pmt for last month's purchases (30 days of credit) Wages and salaries Lease payments Other payments interest on LT bonds, dividends, etc.) Taxes Payment for plant construction Total payments Panel E: Net cash flows Assumed excess cash on hand at start of forecast period Net cash flow (NCF): Total collections - Total payments Cumulative NCF: Prior month cum plus this month's NCF Panel F: Net cash position before financing or investing Target cash balance Surplus cash or loan needed: Cum NCF Target cash $30 $40 $50 $30 $30 $30 $30 $30 $30 $30 $100 SO $10 $10 $10
