You currently collect your credit sales in the following pattern: 30% one month after the sale, 50% two months after the sale, and 20% three months after the sale. You have determined that if you offer a 2% cash discount, you can get 50% of each months sales to be cash sales. The remainder of the months sales would still be collected in the same existing collection pattern. This cash discount program will begin January 1. Compare the interest expense savings over the 6-month period to the cost of the new policy. Should you offer the discount? All other conditions and assumptions remain the same. To account for the timing impacts, you have to redo the cash budget. Please show excel calculation.

CASH-BUDGET JAN FEB MAR APR MAY JUNE (1) Cash Receipts: Total Collections $57,000 $55,400 $62,500 $75,900 $89,000 $100,600 (2) Cash Disbursements: Purchases 56,250 66,000 75,000 82,500 75,000 60,000 3,000 10,000 7,000 8,000 6,000 Wages and Salaries Rent 4,000 4,000 4,000 4,000 4,000 4,000 4,000 Other Expenses 1,000 500 1,200 1,500 1,500 1,200 Interest Expense 600 7,500 (Existing Debt) Taxes 4,460 5,200 Purchases of Equipment 14,000 Loan Repayment 12,000 Total Disbursements 64,250 94,500 91,660 96,000 99,100 81,900 (3) Cash Flow: (=Receipts-Disbursents) (7,250) (39,100) (29,160) (20,100) (10,100) 18,700 (4) Plus: Beginning Cash Balance 20,000 12,750 10,000 10,000 10,000 10,000 Net: Excess (or Deficit) 12,750 (26,350) (19,160) (10,100) (100) 28,700 Less: Interest on Short-term Borrowing (@ 1% /mo.): 364 659 866 976 (5) Equals: Ending Cash Balance without borrowing: 12,750 (26,350) (19,524) (10,759) (966) 27,724 (6) Desired Cash Balance: 10,000 10,000 10,000 10,000 10,000 10,000 (7) Surplus/Fin. Needed* 2,750 (36,350) (29,524) (20,759) (10,966) 17,724 Cumulative borrowing 0 36,350 65,874 86,633 97,599 79,875** * The Financing Needed is the amount of financing required to raise the firm's ending cash balance to the $10,000 desired cash balance. A positive figure for Financing Needed means the firm has excess cash that can retire prior short-term borrowing or be used for investing; in this example, the excess cash in June repaid borrowing. ** This figure results in accrued interest (interest obligated for the month of June but not yet paid) of $799. Purchases and Payments: Sales Purchases NOV DEC JAN FEB MAR APR MAY JUNE $62,000 $50,000 $60,000 $75,000 $88,000 $100,000 $110,000 $100,000 66,000 66,000 Purchases 56,250 66,000 75,000 82,500 75,000 60,000* 56,250* Payments 56,250 66,000 75,000 82,500 75,000 60,000 *These numbers are based on forecast sales of $80,000 for July and $75,000 for August. (75% future 2 months) (post, 1 month) Payments OCT $55,000 Cash Receipts from Sales: Sales Collections of December Sales: First month (30%) Second month (50%) Third month (20%) NOV DEC JAN FEB MAR APR MAY JUNE $62,000 $50,000 $60,000 $75,000 $88,000 $100,000 $110,000 $100,000 15,000 25,000 10,000 OCT NOV DEC JAN FEB MAR APR MAY JUNE* $55,000 $62,000 $50,000 $60,000 $75,000 $88,000 $100,000 $110,000 $100,000 First month (30%) 15,000 18,000 22,500 26,400 30,000 33,000 Second month (50%) 31,000 25,000 30,000 37,500 44,000 50,000 Third month (20%) 11,000 12,400 10,000 12,000 15,000 17,600 Total Collections 57,000 55,400 62,500 75,900 89,000 100,600 * At the end of June, the firm will have accounts receivable of (.20) (100,000) from April, (.70)(110,000) from May, and 100,000 from June for a total of $197,000. Sales Collections: OCT $55,000 CASH-BUDGET JAN FEB MAR APR MAY JUNE (1) Cash Receipts: Total Collections $57,000 $55,400 $62,500 $75,900 $89,000 $100,600 (2) Cash Disbursements: Purchases 56,250 66,000 75,000 82,500 75,000 60,000 3,000 10,000 7,000 8,000 6,000 Wages and Salaries Rent 4,000 4,000 4,000 4,000 4,000 4,000 4,000 Other Expenses 1,000 500 1,200 1,500 1,500 1,200 Interest Expense 600 7,500 (Existing Debt) Taxes 4,460 5,200 Purchases of Equipment 14,000 Loan Repayment 12,000 Total Disbursements 64,250 94,500 91,660 96,000 99,100 81,900 (3) Cash Flow: (=Receipts-Disbursents) (7,250) (39,100) (29,160) (20,100) (10,100) 18,700 (4) Plus: Beginning Cash Balance 20,000 12,750 10,000 10,000 10,000 10,000 Net: Excess (or Deficit) 12,750 (26,350) (19,160) (10,100) (100) 28,700 Less: Interest on Short-term Borrowing (@ 1% /mo.): 364 659 866 976 (5) Equals: Ending Cash Balance without borrowing: 12,750 (26,350) (19,524) (10,759) (966) 27,724 (6) Desired Cash Balance: 10,000 10,000 10,000 10,000 10,000 10,000 (7) Surplus/Fin. Needed* 2,750 (36,350) (29,524) (20,759) (10,966) 17,724 Cumulative borrowing 0 36,350 65,874 86,633 97,599 79,875** * The Financing Needed is the amount of financing required to raise the firm's ending cash balance to the $10,000 desired cash balance. A positive figure for Financing Needed means the firm has excess cash that can retire prior short-term borrowing or be used for investing; in this example, the excess cash in June repaid borrowing. ** This figure results in accrued interest (interest obligated for the month of June but not yet paid) of $799. Purchases and Payments: Sales Purchases NOV DEC JAN FEB MAR APR MAY JUNE $62,000 $50,000 $60,000 $75,000 $88,000 $100,000 $110,000 $100,000 66,000 66,000 Purchases 56,250 66,000 75,000 82,500 75,000 60,000* 56,250* Payments 56,250 66,000 75,000 82,500 75,000 60,000 *These numbers are based on forecast sales of $80,000 for July and $75,000 for August. (75% future 2 months) (post, 1 month) Payments OCT $55,000 Cash Receipts from Sales: Sales Collections of December Sales: First month (30%) Second month (50%) Third month (20%) NOV DEC JAN FEB MAR APR MAY JUNE $62,000 $50,000 $60,000 $75,000 $88,000 $100,000 $110,000 $100,000 15,000 25,000 10,000 OCT NOV DEC JAN FEB MAR APR MAY JUNE* $55,000 $62,000 $50,000 $60,000 $75,000 $88,000 $100,000 $110,000 $100,000 First month (30%) 15,000 18,000 22,500 26,400 30,000 33,000 Second month (50%) 31,000 25,000 30,000 37,500 44,000 50,000 Third month (20%) 11,000 12,400 10,000 12,000 15,000 17,600 Total Collections 57,000 55,400 62,500 75,900 89,000 100,600 * At the end of June, the firm will have accounts receivable of (.20) (100,000) from April, (.70)(110,000) from May, and 100,000 from June for a total of $197,000. Sales Collections: OCT $55,000