MUST BE SOLVED IN SOLVER- Excel must include "Goal weight"

1-Theo Harris earns $55000 a year and has $9000 to invest in a portfolio. His investment alternatives and their expected returns are shown in the table at the top of the next column.

Investment

Description

Expected return

A

IRA (retirement)

3.5%

B

Employer's retirement plan

4.5%

C

Deferred income (retirement)

8%

D

Unity mutual fund

7%

E

Liberty mutual fund

7.5%

F

Money market

5.5%

Theo's investment goals are as follows and can be ranked according to the weights shown in parenthesis. Which investments should be included in Theo's portfolio, and how much should he invest in each?

Goal 1: (25) Invest all funds available.

Goal 2: (20) Maximize the total annual return in dollars, with a target of $1000.

Goal 3: (15) Invest at least 3% of salary in employer's retirement plan.

Goal 4: (15) Invest at least 10% of the total investment in the money market.

Goal 5: (10) Invest at most 25% of the total investment in retirement plans.

Goal 6: (10) Invest at least 50% of the total investment in non-retirement plans.

Goal 7: (5) Invest at most 50% of the total investment in mutual funds.