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

All of the $10,000 must be invested.

Questions:

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

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

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

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

e. 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. In cells G12:G19, write the type of inequality associated with each constraint using either "<=," ">=," or "=."

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

Please provide answers in excel format with clear instructions for a better understanding