Explore BrainMass

Optimizing Susan Wong's Personal Budget.

Please look at the attached document for problem details and solution. I need help with formulating the Objective function and constraints for it.

After Susan Wong graduated from State University with a degree in Operations Research, she went to work for a computer systems development firm in the Washington, D.C., area. As a student at State, Susan paid her normal monthly living expenses for apartment rent, food, and entertainment out of a bank account set up by her parents. Each month they would deposit a specific amount of cash into Susan's account. Her parents also paid her gas, telephone, and bank credit card bills, which were sent directly to them. Susan never had to worry about things like health, car, homeowners', and life insurance; utilities; driver's and car licenses; magazine subscriptions; and so on. Thus, while she was used to spending within a specific monthly budget in college, she was unprepared for the irregular monthly liabilities she encountered once she got a job and was on her own.
In some months Susan's bills would be modest and she would spend accordingly, only to be confronted the next month with a large insurance premium, or a bill for property taxes on her condominium, or a large credit card bill, or a bill for a magazine subscription, and so on the next month. Such unexpected expenditures would result in months when she could not balance her checking account; she would have to pay her bills with her bank credit card and then payoff her accumulated debt in installments while incurring high interest charges. By the end of her first year out of school she had hoped to have some money saved to begin an investment program, but instead she found herself in debt.
Frustrated by her predicament, Susan decided to get her financial situation in order. First, she sold the condominium that her parents had helped her purchase and moved into a cheaper apartment. This gave her enough cash to clear her outstanding debts with $3,800 left over to start the new year with. Susan then decided to use some of the operations research she had learned in college to help her develop a budget. Specifically, she decided to develop a linear programming model to help her decide how much she should put aside each month in short-term investments to meet the demands of irregular monthly liabilities and save some money.
First, Susan went through all of her financial records for the year and computed her expected monthly liabilities for the coming year, as shown in the following table:

January $2,750 July $3,050
February $2,860 August $2,300
March $2,335 September $1,975
April $2,120 October $1,670
May $1,205 November $2,710
Jun $1,600 December $2,980

Susan's after-taxes-and-benefits salary is $29,400 per year, which she receives in 12 equal monthly paychecks that are deposited directly into her bank account.
Susan has decided that she will invest any money she doesn't use to meet her liabilities each month in either a one-month, three-month, or seven-month short-term investment vehicles, rather than just leaving the money in an interest-bearing checking account. The yield on one-month investments is 6% per year nominal; on three-month investments the yield is 8% per year nominal; and on a seven-month investment the yield is 12% per year nominal. As part of her investment strategy, any time one of the short-term investments comes due she uses the principal as part of her budget, but she transfers any interest earned to another long-term investment (which she doesn't consider in her budgeting process). For example, if she has $100 left over in January that she invests for three months, in April when the investment matures she uses the $100 she originally invested in her budget, but any interest on the $100 is invested elsewhere. (Thus, the interest is not compounded over the course of the year.)
Susan wants to develop a linear programming model that will maximize her investment return during the year so she can take that money and reinvest it at the end of the year in a longer-term investment program. However, she doesn't have to confine herself to short-term investments that will all mature by the end of the year; she can continue to put money toward the end of the year in investments that won't mature until the following year. Her budgeting process will continue to the next year, where she can take out any surplus left over after December and reinvest it in a long-term program if she wants to.

Develop a linear programming model that to help Susan determine her best investment options to maximum her return. (Hint: You need to create variables that represent monthly investment options. Your objective is to maximize return taking into account that money invested each month is left over after expenses have been paid.)

Note: Need to be solved using Excel Solver.


Solution Summary

Solution using Excel's Solver is presented along with a detailed objective function on a PDF. Also includes Excel attachment.