The CFO for Eagle’s Beach Wear and Gift Shop is in the process of planning for the company’s cash flows for the next six months. The following table summarizes the expected accounts receivables and planned payments for each of these months (in $100,000s)

The company currently has a beginning cash balance of $40,000 and desires to maintain a balance of at least $25,000 in cash at the end of each month. To accomplish this, the company has a number of ways of obtaining short-term funds:

1. Delay Payments. In any month, the company’s suppliers permit it to delay any or all payments for one month. However, for this consideration, the company forfeits a 2% discount that normally applies when payments are made on time. (Loss of this 2% discount is, in effect, a financing cost.)

2. Borrow Against Accounts Receivables. In any month, the company’s bank will loan it up to 75% of the accounts receivable balances due that month. These loans must be repaid in the following month and incur an interest charge of 1.5%.

3. Short-Term Loan. At the beginning of January, the company’s bank will also give it a 6-month loan to be repaid in a lump sum at the end of June. Interest on this loan is 1% per month and is payable at the end of each month. Assume the company earns 0.5% interest each month on cash held at the beginning of the month. Create a spreadsheet model the company can use to determine the least costly cash management plan (that is, minimal net financing costs) for this sixmonth period. What is the optimal solution?