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 Prepayment 30 50 80 100 150 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 monththe 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) June 200
11 | P a g e (Feb) x2+y2+d2-1.01x1+1.003z1-z2 = 100 (2pts) (March) x3+y3+d3-d1-1.01x2+1.003z2-z3 = -200 (2pts) (April) x4+d4-d2-1.02y1-1.01x3+1.003z3-z4 = 200 (2pts) (May) x5-d3-1.02y2-1.01x4+1.003z4-z5=-50 (2pts) (June) -d4-1.02y3-1.01x5+1.003z5-v = -300 (2pts) x1<=100, x2<=100, x3<=100,x4<=100,x5<=100, (1 pt) (ratio-Feb) d2-0.5z1<=0 (since d2/z1<=0.5) (2pts) (ratio-Mar) d3-0.5z2<=0 (since d3/z2<=0.5) (2pts) (ratio-Apr) d4-0.5z3<=0 (since d4/z3<=0.5) (2pts) d1<=30, d2<=50, d3<=80, d4<=100 (2pts) x1,…,x5,y1,..,y3,z1,…,z5,d1,..,d4>=0 (1 pt) NOTE: The problem could be formulated without the V decision variable, where the constraint for June becomes the objective function. Excel: (total marks: 10) Excel without any report, even with correct formulation (receive no more than 4pts) With all the reports but the solution does not make much sense (receive no more than 4pts) With all the reports and the solution makes sense (receive at least 6 pts) Below you will find the Excel spreadsheet formulation. Please note that we have two ways to formulate the problem (option 1 and option 2). Both leads to the same value for the objective function: check option 1 and option 2 (after adding the 300).
Want to read all 18 pages?
You've reached the end of your free preview.
Want to read all 18 pages?
- Fall '05
- LHS, Konrad Zuse, Jan LHS