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. Month 1 month Investment 3 month Investment Matured 1 month Investment 7 month Investment 1 2 3 4 5 6 7 8 9 10 11 12 Matured 3 Matured7 month month Investment Investment 3000 0 0 Interests 0 Return 0 0 Extra Income Salary 2450 5450 2450 2450 2450 2450 2450 2450 2450 2450 2450 2450 2450 Net expense 2860 2750 2550 2120 1205 1600 3050 2300 1975 1670 2710 2980 Month 1 month Investment 3 month Investment 1 2 3 4 5 6 7 8 9 10 11 12 Matured 1 month Investment 7 month Investment Matured 3 Matured7 month month Investment Investment 3000 0 0 Interests 0 Return 0 Extra Income Salary 2450 5450 2450 2450 2450 2450 2450 2450 2450 2450 2450 2450 2450 Net expense 2860 2750 2550 2120 1205 1600 3050 2300 1975 1670 2710 2980