Susan Wong's Personal Budgeting Model Susan Wong wants to develop a linear programming model for her budget. The objective is to maximize her short-term investments during the year so she can take the money and reinvest at the end of the year in a longer-term investment program. Susan has $3000 in her bank account at the beginning of this year. Her after-taxes-and-benefits salary is $29400 per year which she receives in 12 equal monthly paychecks ($2450/month) at the end of each month. Susan has computed her expected monthly liabilities for this year, as shown in the following table. Month January February March April May June Bills ($) 2860 2750 2550 2120 1205 1600 Month July August September October November December Bills ($) 3050 2300 1975 1670 2710 2980 Susan has decided that she will invest any money she doesn't use to meet her liability each month in either 1-month, 3-month or 7-month short-term investment vehicles. The yield on a 1-month investment is 6% per year nominal (0.5%/month). The yield on a 3-month investment is 8% per year nominal (equivalent to 2% for 3 months). On a 7-month investment, the yield is 12% per year nominal. These are the assumptions for the linear programming model. All her bills come due at the end of the month. She receives her monthly salary at the end of the month. She puts aside money for short-term investments at the end of the month. She does not have to confine herself to short-term investments that will all mature by the end of the year. At the end of the December, she would not invest the balance in short-term investments. She would transfer the December balance to longer-term investment. There are two possible strategies to handle the matured short-term investments. Develop an LP model for each strategy and answer the questions. Strategy I She uses the principal of the matured short-term investment as part of her budget and transfers the earned interest to another long-term investment. For example, she has put aside $100 in January for a 3-month investment. In April, when the investment matures, she receives $102 (principal plus interest). She uses the $100 she originally invested back to her budget for April, but $2 interest is invested elsewhere. a. Based on this strategy, develop a linear programming model to determine how much she should put aside each month in short-term investments to maximize her short-term investment returns. Solve the model. b. If she decides she doesn't want to include all her original $3000 in her budget at the beginning of the year, but instead she wants to invest some of it directly in alternative longer-term investments, what is the minimum she would need from the $3000 to develop a feasible budget? c. If she decides to save money by cutting expenses, which month to cut expense would give her the best return? Strategy II She uses the entire matured short-term investment (i.e. principal plus the interest) as part of her budget. For example, if she puts aside $100 in January for a 3-month investment. In April, when the investment matures, she receives $102 (principal plus interest). She would use the entire $102 back in her April budget. a. Based on this strategy, develop a linear programming model to determine how much she should put aside each month in short-term investments to maximize her short-term investment returns. Solve the model. b. Which strategy is better for her? There are two deliverables for this case study, a short write-up of the project and the spreadsheet showing your work. Write-up Your write-up should introduce your solution to the project by describing the problem. Identify what type of problem this is. For example, you should note if the problem is a maximization or minimization problem, as well as identify the resources that constrain the solution. Identify each variable and explain the criteria involved in setting up the model. This should be encapsulated in one (1) or two (2) succinct paragraphs. After the introductory paragraph, write out (explain) the L.P. model for the problem, including the explanation of the objective function and all constraints. Then, you should present the optimal solution, based on your work in Excel. Explain what the results mean. Finally, write a paragraph addressing the part of the problem pertaining to sensitivity analysis and shadow price. Excel As previously noted, please set up your problem in Excel and find the solution using Solver. Clearly label the cells in your spreadsheet. You will turn in the entire spreadsheet, showing the setup of the model, and the results. Microsoft Excel 16.0 Answer Report Worksheet: [Susan Wong Template Winter 2017.xlsx]Strategy 1 Report Created: 2/26/2017 11:03:52 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.032 Seconds. Iterations: 13 Subproblems: 0 Solver Options Max Time 100 sec, Iterations 100, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 5%, Assume NonNegative Objective Cell (Max) Cell Name $C$19 Return 3 month Investment Original Value 634.65 Final Value 634.65 Variable Cells Cell Name $B$3 1 month Investment $C$3 3 month Investment $D$3 7 month Investment $B$4 1 month Investment $C$4 3 month Investment $D$4 7 month Investment $B$5 1 month Investment $C$5 3 month Investment $D$5 7 month Investment $B$6 1 month Investment $C$6 3 month Investment $D$6 7 month Investment $B$7 1 month Investment $C$7 3 month Investment $D$7 7 month Investment $B$8 1 month Investment $C$8 3 month Investment $D$8 7 month Investment $B$9 1 month Investment $C$9 3 month Investment $D$9 7 month Investment $B$10 1 month Investment $C$10 3 month Investment $D$10 7 month Investment $B$11 1 month Investment Original Value 400 2190 0 100 0 0 0 0 0 0 0 2520 0 715 530 600 250 0 0 0 0 0 865 0 725 Final Value Integer 400 Contin 2190 Contin 0 Contin 100 Contin 0 Contin 0 Contin 0 Contin 0 Contin 0 Contin 0 Contin 0 Contin 2520 Contin 0 Contin 715 Contin 530 Contin 600 Contin 250 Contin 0 Contin 0 Contin 0 Contin 0 Contin 0 Contin 865 Contin 0 Contin 725 Contin $C$11 3 month Investment $D$11 7 month Investment $B$12 1 month Investment $C$12 3 month Investment $D$12 7 month Investment $B$13 1 month Investment $C$13 3 month Investment $D$13 7 month Investment Constraints Cell $J$3 Net $J$4 Net $J$5 Net $J$6 Net $J$7 Net $J$8 Net $J$9 Net $J$10 Net $J$11 Net $J$12 Net $J$13 Net $J$14 Net Name 0 0 1505 0 0 0 0 4630 0 Contin 0 Contin 1505 Contin 0 Contin 0 Contin 0 Contin 0 Contin 4630 Contin Cell Value Formula 2860 $J$3>=$K$3 2750 $J$4>=$K$4 2550 $J$5>=$K$5 2120 $J$6>=$K$6 1205 $J$7>=$K$7 1600 $J$8>=$K$8 3050 $J$9>=$K$9 2300 $J$10>=$K$10 1975 $J$11>=$K$11 1670 $J$12>=$K$12 2710 $J$13>=$K$13 2980 $J$14>=$K$14 Status Slack Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Microsoft Excel 16.0 Sensitivity Report Worksheet: [Susan Wong Template Winter 2017.xlsx]Strategy 1 Report Created: 2/26/2017 11:03:53 AM Variable Cells Cell $B$3 $C$3 $D$3 $B$4 $C$4 $D$4 $B$5 $C$5 $D$5 $B$6 $C$6 $D$6 $B$7 $C$7 $D$7 $B$8 $C$8 $D$8 $B$9 $C$9 $D$9 $B$10 $C$10 $D$10 $B$11 $C$11 $D$11 $B$12 $C$12 $D$12 $B$13 $C$13 $D$13 Name 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 400 0 0.005 0.005 1.00000E+030 2190 0 0.02 1.00000E+030 0 0 0 0.07 0 1.00000E+030 100 0 0.005 0.005 1.00000E+030 0 -0.025 0.02 0.025 1.00000E+030 0 -0.005 0.07 0.005 1.00000E+030 0 -0.005 0.005 0.005 1.00000E+030 0 -0.03 0.02 0.03 1.00000E+030 0 -0.005 0.07 0.005 1.00000E+030 0 -0.025 0.005 0.025 1.00000E+030 0 -0.025 0.02 0.025 1.00000E+030 2520 0 0.07 1.00000E+030 0 0 -0.005 0.005 0.005 1.00000E+030 715 0 0.02 0.02 0.005 530 0 0.07 0.04 0.02 600 0 0.005 1.11022E-016 0.025 250 0 0.02 0.005 1.11022E-016 0 -0.03 0.07 0.03 1.00000E+030 0 -1.11022E-016 0.005 1.11022E-016 1.00000E+030 0 -1.11022E-016 0.02 1.11022E-016 1.00000E+030 0 -0.025 0.07 0.025 1.00000E+030 0 -0.005 0.005 0.005 1.00000E+030 865 0 0.02 0 0.005 0 -0.02 0.07 0.02 1.00000E+030 725 0 0.005 0.005 1.11022E-016 0 -0.02 0.02 0.02 1.00000E+030 0 -0.01 0.07 0.01 1.00000E+030 1505 0 0.005 0.005 1.11022E-016 0 -0.055 0.02 0.055 1.00000E+030 0 -0.005 0.07 0.005 1.00000E+030 0 -0.025 0.005 0.025 1.00000E+030 0 -0.05 0.02 0.05 1.00000E+030 4630 0 0.07 1.00000E+030 0.005 Constraints Cell Name Final Value Shadow Price Constraint R.H. Side Allowable Increase Allowable Decrease $J$3 $J$4 $J$5 $J$6 $J$7 $J$8 $J$9 $J$10 $J$11 $J$12 $J$13 $J$14 Net Net Net Net Net Net Net Net Net Net Net Net 2860 2750 2550 2120 1205 1600 3050 2300 1975 1670 2710 2980 -0.16 -0.155 -0.15 -0.14 -0.11 -0.1 -0.095 -0.09 -0.08 -0.075 -0.07 -0.04 2860 2750 2550 2120 1205 1600 3050 2300 1975 1670 2710 2980 2190 1.00000E+030 2190 400 2190 100 2520 1.00000E+030 715 1.00000E+030 250 1.00000E+030 250 600 865 1.00000E+030 725 1.00000E+030 1505 1.00000E+030 4630 1.00000E+030 715 530 1 month Month Investment 1 2 3 4 5 6 7 8 9 10 11 12 3 month Investment 400 100 0 0 0 600 0 0 725 1505 0 Interests 16.65 Return 7 month Investment 2190 0 0 0 715 250 0 865 0 0 0 80.4 634.65 Matured 1 month Investment 0 0 400 0 100 2520 0 530 0 00 0 600 00 00 0 725 4630 1505 0 537.6 Matured 3 Matured7 month month Investment Investment Extra Income 3000 2190 0 0 0 715 250 0 865 0 0 0 0 2520 530 Salary 2450 2860 2450 2750 2450 2550 2450 2120 2450 1205 2450 1600 2450 3050 2450 2300 2450 1975 2450 1670 2450 2710 2450 2980 Net expense 2860 2750 2550 2120 1205 1600 3050 2300 1975 1670 2710 2980 Microsoft Excel 16.0 Answer Report Worksheet: [Susan Wong Template Winter 2017_Hurd.xlsx]Strategy 2 Report Created: 2/26/2017 1:39:36 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.063 Seconds. Iterations: 13 Subproblems: 0 Solver Options Max Time 100 sec, Iterations 100, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 5%, Assume NonNegative Objective Cell (Max) Cell Name $C$19 Return 3 month Investment Original Value Final Value 0 659.25765813 Variable Cells Cell Name $B$3 1 month Investment $C$3 3 month Investment $D$3 7 month Investment $B$4 1 month Investment $C$4 3 month Investment $D$4 7 month Investment $B$5 1 month Investment $C$5 3 month Investment $D$5 7 month Investment $B$6 1 month Investment $C$6 3 month Investment $D$6 7 month Investment $B$7 1 month Investment $C$7 3 month Investment $D$7 7 month Investment $B$8 1 month Investment $C$8 3 month Investment $D$8 7 month Investment $B$9 1 month Investment $C$9 3 month Investment $D$9 7 month Investment $B$10 1 month Investment $C$10 3 month Investment $D$10 7 month Investment $B$11 1 month Investment Original Value 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Final Value Integer 397.514913 Contin 2192.485087 Contin 0 Contin 99.502487562 Contin 0 Contin 0 Contin 0 Contin 0 Contin 0 Contin 0 Contin 0 Contin 2566.3347887 Contin 0 Contin 749.6728972 Contin 495.3271028 Contin 597.01492537 Contin 252.98507463 Contin 0 Contin 0 Contin 0 Contin 0 Contin 0 Contin 914.66635514 Contin 0 Contin 733.04477612 Contin $C$11 3 month Investment $D$11 7 month Investment $B$12 1 month Investment $C$12 3 month Investment $D$12 7 month Investment $B$13 1 month Investment $C$13 3 month Investment $D$13 7 month Investment Constraints Cell $J$3 Net $J$4 Net $J$5 Net $J$6 Net $J$7 Net $J$8 Net $J$9 Net $J$10 Net $J$11 Net $J$12 Net $J$13 Net $J$14 Net Name 0 0 Contin 0 0 Contin 0 1516.71 Contin 0 0 Contin 0 0 Contin 0 0 Contin 0 0 Contin 0 4943.2314562 Contin Cell Value Formula 2860 $J$3>=$K$3 2750 $J$4>=$K$4 2550 $J$5>=$K$5 2120 $J$6>=$K$6 1205 $J$7>=$K$7 1600 $J$8>=$K$8 3050 $J$9>=$K$9 2300 $J$10>=$K$10 1975 $J$11>=$K$11 1670 $J$12>=$K$12 2710 $J$13>=$K$13 2980 $J$14>=$K$14 Status Slack Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Binding 0 Microsoft Excel 16.0 Sensitivity Report Worksheet: [Susan Wong Template Winter 2017_Hurd.xlsx]Strategy 2 Report Created: 2/26/2017 1:39:37 PM Variable Cells Cell $B$3 $C$3 $D$3 $B$4 $C$4 $D$4 $B$5 $C$5 $D$5 $B$6 $C$6 $D$6 $B$7 $C$7 $D$7 $B$8 $C$8 $D$8 $B$9 $C$9 $D$9 $B$10 $C$10 $D$10 $B$11 $C$11 $D$11 $B$12 $C$12 $D$12 $B$13 $C$13 $D$13 Name 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment 1 month Investment 3 month Investment 7 month Investment Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 397.514913 0 0.005 0.0056384894 1.00000E+030 2192.485087 0 0.02 1.00000E+030 0 0 0 0.07 0 1.00000E+030 99.502487562 0 0.005 0.0056104372 1.00000E+030 0 -0.0264954997 0.02 0.0264954997 1.00000E+030 0 -0.0056104372 0.07 0.0056104372 1.00000E+030 0 -0.0055825246 0.005 0.0055825246 1.00000E+030 0 -0.0318189139 0.02 0.0318189139 1.00000E+030 0 -0.0055825246 0.07 0.0055825246 1.00000E+030 0 -0.02610586 0.005 0.02610586 1.00000E+030 0 -0.02610586 0.02 0.02610586 1.00000E+030 2566.3347887 0 0.07 1.00000E+030 0 0 -0.0053750086 0.005 0.0053750086 1.00000E+030 749.6728972 0 0.02 0.0204755515 0.0053750086 495.3271028 0 0.07 0.043228 0.0204755515 597.01492537 0 0.005 0 0.0257219503 252.98507463 0 0.02 0.0053482672 0 0 -0.032341285 0.07 0.032341285 1.00000E+030 0 0 0.005 0 1.00000E+030 0 0 0.02 0 1.00000E+030 0 -0.026857 0.07 0.026857 1.00000E+030 0 -0.0052696163 0.005 0.0052696163 1.00000E+030 914.66635514 0 0.02 0 0.0052696163 0 -0.0214 0.07 0.0214 1.00000E+030 733.04477612 0 0.005 0.0052433993 0 0 -0.01951875 0.02 0.01951875 1.00000E+030 0 -0.01072675 0.07 0.01072675 1.00000E+030 1516.71 0 0.005 0 0 0 -0.05535 0.02 0.05535 1.00000E+030 0 -0.00535 0.07 0.00535 1.00000E+030 0 -0.024398 0.005 0.024398 1.00000E+030 0 -0.05 0.02 0.05 1.00000E+030 4943.2314562 0 0.07 0 0.0053233831 Constraints Cell Name Final Value Shadow Price Constraint R.H. Side Allowable Increase Allowable Decrease $J$3 $J$4 $J$5 $J$6 $J$7 $J$8 $J$9 $J$10 $J$11 $J$12 $J$13 $J$14 Net Net Net Net Net Net Net Net Net Net Net Net 2860 -0.167798 2750 -0.1619880597 2550 -0.1562070246 2120 -0.1449 1205 -0.113228 1600 -0.102341285 3050 -0.096857 2300 -0.0914 1975 -0.08072675 1670 -0.07535 2710 -0.07 2980 -0.0404 2860 2192.485087 1.00000E+030 2750 2203.4475124 399.50248756 2550 2214.46475 100 2120 2566.3347887 1.00000E+030 1205 749.6728972 1.00000E+030 1600 252.98507463 1.00000E+030 3050 254.25 600 2300 914.66635514 1.00000E+030 1975 733.04477612 1.00000E+030 1670 1516.71 1.00000E+030 2710 4943.2314562 1.00000E+030 2980 802.15 530 1 month Month Investment 1 397.514913 2 99.502487562 3 0 4 0 5 0 6 597.01492537 7 0 8 0 9 733.04477612 10 1516.71 11 0 12 3 month Investment 7 month Investment Matured 3 Matured7 month month Investment Investment 2192.485087 0 0 0 399.50248756 0 0 100 2236.33479 0 2566.3347887 0 0 749.6728972 495.3271028 0 0 252.98507463 00 0 0 0 600 0 764.666355 914.66635514 0 258.044776 0 00 0 0 0 736.71 0 4943.2314562 1524.29355 932.959682 0 0 Interests 16.71893551 82.196188279 560.34253434 Return Matured 1 month Investment 659.25765813 Extra Income 3000 0 0 0 2745.978224 530 Salary 2450 2860 2450 2750 2450 2550 2450 2120 2450 1205 2450 1600 2450 3050 2450 2300 2450 1975 2450 1670 2450 2710 2450 2980 Net expense 2860 2750 2550 2120 1205 1600 3050 2300 1975 1670 2710 2980