*** Please use excel solver to solve the problems and provide excel formulas***

1. Linear Programming

John Smith, an individual investor, has $70,000 to divide among several investments. The investment options are municipal bonds with an 8.5% annual return, certificates of deposit with a 5% return, treasury bills with a 6.5% return and a growth stock fund with a 13% annual return. The investments are all evaluated after 1 year. John wants to maximize his returns but he wants to ensure a level of diversification which he specifies below:

At least 15% of the total investment should be in municipal bonds

The amount invested in certificates of deposit should not exceed the amount invested in the other three alternatives. (Cumulatively, NOT individually)

At least 35% of the investment should be in treasury bills and certificates of deposit.

To be safe, more should be invested collectively in CDs and treasury bills than in municipal bonds and the growth stock fund (collectively), by a ratio of at least 1.2 to 1.

a. What is the best investment mix for John to maximize his returns?

b. Suppose John wants his portfolio to be reasonably well-balanced and chooses to do this by maximizing the minimum investment in each category. What would the optimal mix be in that case?

c. John believes that his allocations should focus primarily on maximizing returns [objective in (a)]. But he would also like to have some level of equitable allocation [objective in (b)]. He decides to pursue a strategy of giving 95% weight to objective (a) and only 5% weight to objective (b). What will the optimal allocation amounts be with this (hybrid) strategy?