Reference no: EM131227861

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	Bills ($)	Month	Bills ($)
January	2860	July	3050
February	2750	August	2300
March	2550	September	1975
April	2120	October	1670
May	1205	November	2710
June	1600	December	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.

1. 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.

2. 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?

3. 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.

1. 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.

2. 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.