Report to Management Format (20 points)

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.

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? (3 points)

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. (3 points)

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. (3 points)

January February March -100 April May June 50 200 -200 300 Net Cash-150 Flow January February March -100 April May June 50 200 -200 300 Net Cash-150 Flow