Report to Management Format. The problem of short-term financing is known to be very common to corporations. They often have some cash commitments, either to pay or to receive, in the short run. It is critical for them to figure out what would be the most economic way to fulfill the commitments. In particular, the company Analytics-C located in Ottawa has the following short-term cash commitments starting this year.
Month January February March April May June
Net Cash Flow -150 -100 200 -200 50 300
In the above table, a negative (or positive) amount means that the company would have to pay (or receive) the specified amount of cash at the start of that month. The amounts are stated in thousands of dollars.
After consulting with some financial specialists, the management team of Analytics-C has identified two sources of funds they may use to finance the cash-flow requirements. The first is to borrow money using a line of credit, which is available up to $100K per month at an interest rate 1% per month. The second is to borrow money by issuing 90- day commercial paper, which can only be done in the first three months and would bear a total interest of 2% for the 3-month period. For example, if the company decides to borrow X amount by issuing 90-day commercial paper in January, they would need to pay back in April the borrowed amount, i.e. $X, plus 2% of that, i.e. 2%*X. If they borrow in February, they pay back in May and so on so forth. In addition, at the end of each month, if the company finds any excess fund, i.e. the cash left after fulfilling the cash requirement of that month, they can re-invest that amount at an interest rate of 0.3% per month.
Task 1:
You are hired fairly recently and assigned to help determine how the company should schedule their short-term financing. The management team asks you to develop a financing plan that will maximize the cash amount at the end of June.
Report how to solve this problem by formulating a Linear Programming (LP) model. In addition to following the “report to management” format, you should provide all the details, which includes but are not limited to the LP algebraic model formulation (i.e. decision variables, objective function, and constraints with detailed explanations). The Excel sheets including your LP spreadsheet model formulation, answer report and sensitivity report. (29 points)
Moreover, the management team has the following questions for you, and you should provide your answers based on the Excel sensitivity report.
a) There is a 6-month loan available only at the beginning of January that requires a total interest of 1%, i.e. every dollar borrowed in January has to be paid back at the end of June in an amount of 1.01 dollar. Would you recommend the firm to borrow the money or not?
b) Recently, the management team was approached by another bank that can also provide the service of line of credit. This bank proposed that if the company switched to its service, it could increase the line of credit up to $200K. Thus doubling the amount that the company currently has. The management team is quite satisfied with the current bank overall and wonder if it is worthy to make the switch. Provide your recommendation with detailed justification.
c) The management team foresees that there is a need to transfer out $40K sometime between January and June. The team wonders which month it should make the transfer so that it has the minimum impact on the cash amount at the end of June. Which month would you recommend and how does that change the final cash amount? Provide detailed answers and analysis based on the sensitivity report.
Task 2:
The management team recently had a meeting with the accountant and learned that they can expect to receive some cash prepaid by the customers in each month. The schedule of the pre-payments is detailed in the following table.
Month January February March April May June
Prepayment 30 50 80 100 150 200
The management team wonders if it can use some of these prepayments to improve its short-term financing schedule of Task 1. Every dollar the management team draws from this prepayment account has to be returned to the account in the second following month (i.e. the amount drawn in January must be returned in March, the amount drawn in February must be returned in April, etc.). However, for risk management purpose, the amount of money drawn in each month has to be regulated. In particular, the team needs to ensure that starting from February, the following ratio the amount of money drawn in each month the amount of cash left from the previous month cannot exceed 50% in each month.
You are asked to recommend a new financing plan that incorporates the use of the prepayment account so as to maximize the cash amount at the end of June. Note that all the information in Task 1 applies here. Report how to solve this problem by formulating a Linear Programming (LP) model, and similar to Task 1 you should provide all details of the model algebraic formulation, LP spreadsheet formulation, answer report and sensitivity report. (36 points)
In addition, the team leader has the following questions for you, and you should provide your answers based on the Excel sensitivity report.
a) The accountant cautions that the prepayment in March may drop by 40K. How would this drop affect the financing plan? Provide detailed answers and analysis.
b) The management team argues with the regulators that the ratio requirement has a direct impact on their financing plan. Can you verify if this claim is true?