Optimization Problem Using Excel Solver

3.9 Planning Cash: Each Fall, the treasurer of Trefny's department store does financial planning for the next 6 months, September through February. Because of the holiday season, Trefny's needs large amounts of cash during October, November, and December, whereas a large cash inflow is expected after the first of the year when customers pay off their holiday bills. The following table summarizes the predicted net cash flows (in thousands) from "business-as-usual" operations. Month September October November December January February Surplus $20 30 150 Deficit 30 60 90 The treasurer can draw on three sources of short-term funds to meet the store's needs, although each represents a departure from "business as usual." They are as follows: Accounts Receivable Loans. A local bank will loan Trefny's funds on a month-by-month basis against a pledge on the accounts receivable balance as of the first day of a particular month. The maximum loan is 75% of the balance, and the cost of the loan is 1.5% per month, assessed on the amount borrowed. The predicted balances (in thousands) under "business-as-usual" plans are shown below. Month September October November December January February Balance $70 50 70 110 100 150 Delayed Payment of Purchases. All bills for purchases come due on the first of the month, but payments on all or part of these obligations can be delayed by 1 month. When payments are delayed this way, Trefny's loses the 2% discount it normally receives for prompt payment under "business-as-usual" operations. (Loss of this 2% discount is effectively a financing cost.) The predicted payment schedule (in thousands) without the discount is shown below. Month September October November December January February Payment $80 90 100 60 40 50 Short-Term Loan. A bank is willing to loan Trefny's any amount from $40,000 to $100,000 for 6 months, starting September 1. The principal would be paid back at the end of February, and Trefny's would not be permitted to pay off part of the loan, or add to it, during the 6- month period. The cost of the loan is 1% per month, payable at the end of each month. In any month, excess funds can be transferred to Trefny's short-term investment portfolio, where the funds can earn 0.5% per month. a. Determine a plan for the treasurer that will meet the firm's cash needs at minimum cost. (Assume that all cash flows occur at the beginning of the month.) What is the cost of this plan? Equivalently, what is the maximum amount of funds on hand after February? b. Show the network diagram corresponding to the solution in (a). That is, label each of the ares in the solution and verify that the flows are consistent with the given information