The CFO for Eagle Beach Wear and Gift Shop is in the process of planning for the company’s cash flows for the next 6 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 1 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 (i.e., minimal net financing costs) for this 6-month period. What is the optimal solution?