Problem 3

As a 401K retirement plan participant, you have to make personal decisions regarding your own investment objectives. Suppose you have $10,000 to divide among four investment alternatives in your 401K retirement plan. The investment alternatives and historical returns are as follows;

? Real Estate (RE) = 8%

? CD = 4% ? Mutual Fund (MF) = 6%

? Bonds = 7% You will follow the investment allocation guidelines below.

? More money should be in real estate than in CDs, at a rate of at least 2 to 1. [Hint: That means that RE > 2*CD]

? At least 50% ($5,000) must be in mutual funds and bonds combined.

? No more than 20% ($2,000) can be in bonds alone.

? At least 2.5% ($250) must be invested in each alternative.

? All of the $10,000 must be invested.

Questions:

a. (3 Points) In cell B3, use the Excel's built-in function named "SUMPRODUCT" to write a formula for the objective function. Be sure to reference the appropriate cells.

b. (4 Points) In cells B9:E9, enter the investment return coefficients for the objective function. Make sure the coefficients are input as proportions, not percentages.

c. (8 Points) In cells B12:E19, enter the constraint coefficients for each of the eight constraints.

d. (4 Points) In cells H12:H19, enter the appropriate constraint right-hand side values.

e. (8 Points) In cells F12:F19, use the "SUMPRODUCT" function to write a formula for the constraint left-hand side values. Be sure to reference the appropriate cells.

f. (5 Points) In cells G12:G19, write the type of inequality associated with each constraint using either "=," or "=."

h. (7 points) Setup Excel Solver to use it to solve the model for the maximum return. Keep the optimal solution returned by Solver in the model.

A1 - fx Problem 3 A B C D E F G H 1 Problem 3 2 3 Max Return 4 5 X1 RE X2 CD X3 MF X4 Bonds 6 Decision Variables 7 8 X1 RE X2 CD X3 MF X4 Bonds Return Coefficients 10 11 Constraint Constraint Constraint Coefficients X1 RE X2 CD X3 MF X4 Bonds left-hand side 12 right-hand side RE to CD Ratio 13 MF + Bonds Combined 14 Bonds