View the step-by-step solution to:

The situation. Peter, portfolio manager for a small investment firm, is responsible for wisely investing 10 million dollars. His goal is to maximize...

LP Excel Assignment
In this assignment, you will set up the formulation of Peter’s Portfolio Planning Problem (given below) in an Excel worksheet and then use Excel’s Solver add-in to optimize his linear program. The situation . Peter, portfolio manager for a small investment firm, is responsible for wisely investing 10 million dollars. His goal is to maximize the total expected return from these investments over the next year. Four investment options are available, each having an annual mean (expected) rate of return and a standard deviation in the rate of return, which can be thought of as a risk factor. Two of the options also have a maximum allowable level dictated by the firm, as shown in the table below. Investment Expected Rate Std. Dev. in Rate Maximum Investment Option of Return (in %) of Return (in %) Level (in Million $) Treasury Bills 4.0 3.0 4.0 Common Stocks 8.0 10.0 3.5 Money Markets 3.0 2.5 (No Limit) Government Bonds 2.0 0.0 (No Limit) To reduce risk, the firm wants Peter to invest at least 4.5 million dollars in a combination of Treasury Bills and Government Bonds - see constraint [2]. Furthermore, the firm requires that the amount of money invested in a combination of Money Markets and Government Bonds be at least twice the amount invested in Common Stocks - see constraint [3]. Last, Peter wants to limit the total standard deviation in the rate of return to be no more than half a million dollars - see constraint [4]. LP Formulation : Peter’s problem has 4 decision variables and 10 constraints: 1* TB = millions of dollars to invest in Treasury Bills 2* CS = millions of dollars to invest in Common Stocks 3* MM = millions of dollars to invest in Money Markets 4* GB = millions of dollars to invest in Government Bonds Maximize .04TB + .08CS + .03MM + .02GB = Z subject to: TB + CS + MM + GB 10 [1] TB + GB 4.5 [2] -2CS + MM + GB 0 [3] .03TB + .10CS + .025MM + 0.5 [4] TB 4.0 [5] CS 3.5 [6] TB 0 [7] CS 0 [8] MM 0 [9] GB 0 [10] Notes: I recommend that you first practice by solving the ABC problem that we’ve discussed in class. This LP has 2 more variables (columns) & several more constraints (rows) than the ABC problem. Some numbers (“coefficients”) in the constraints are 0 or 1, e.g., constraint [2] can be written as 1TB + 0CS + 0MM + 1GB 4.5, so in Excel, enter the coefficients 1 0 0 1 and 4.5 in the cells. In the Solver Parameters dialog: (1) be especially careful when you specify the Set Objective, Changing Variable, and Constraints cells; (2) check the box that says “Make Unconstrained Variables Non-Negative” to take care of constraints [7]-[10]; and (3) choose “Simplex LP” from the “Select a Solving Method” menu.
Background image of page 1

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question