Reference no: EM132102438

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 Janurary Feburary 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. 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 Janurary Feburary 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 >= 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. Expert Answer