Question

An investor has $100,000, which he wishes to invest in income-producing securities and government bonds so as to maximize his annual return. He has selected five possible investments, all of which he considers to have reasonably high yields and stability:

1. Oil company A stock, paying 11% annual dividend.

2. Oil company B stock, paying 7.5% annual dividend.

3. Electric utility A stock, paying 8% annual dividend.

4. Electric utility B stock, paying 6% annual dividend.

5. Government bonds, paying 5% annual interest.

Since he has no plans to sell the stocks in the future, he is not concerned with their selling price. Based on the various risk levels involved, he has made the following decisions:

1. The total investment in oil stocks may not exceed $30,000.

2. The total investment in electric utilities may not exceed $50,000.

3. The investment in oil company A may not exceed $20,000.

4. The investment in electric utility A may not exceed $30,000.

5. The total investment in oil stocks may not exceed the total investment in electric utilities.

6. The investment in oil company A and electric utility A combined may not exceed the investment in government bonds.

Find out how much money to invest in each investment so as to maximize annual return.

Answer must be in excel or Lingo

Answer 1

The boxes in green, are decision variables
The boxes in blue are constrains
The boxes in orange is the objective function

The formulae:

Dec var	Oil A	Oil B	Elec A	Elec B	Gov	Total
Amount in each	20000	2.72848410531878E-12	30000	0	50000	=SUM(B2:F2)	<=	100000
	<=		<=			Total availability constraint
	20000		30000
	constraint 3		constraint 4
		Total oil		Total Elec	Total Elec+Oil
		=SUM(B2:C2)	<=	=SUM(D2:E2)	=SUM(B2:E2)
		<=	constraint 5	<=	<=
		30000		50000	=F2
		constraint 1		constraint 2	constraint 6
Return	0.11	0.075	0.08	0.06	0.05
Total return	=SUMPRODUCT(B2:F2,B11:F11)
Objective func	Maximization

The solution:

Dec var	Oil A	Oil B	Elec A	Elec B	Gov	Total
Amount in each	20000	0	30000	0	50000	100000	<=	100000
	<=		<=			Total availability constraint
	20000		30000
	constraint 3		constraint 4
		Total oil		Total Elec	Total Elec+Oil
		20000	<=	30000	50000
		<=	constraint 5	<=	<=
		30000		50000	50000
		constraint 1		constraint 2	constraint 6
Return	11.0%	7.5%	8.0%	6.0%	5.0%
Total return	7100
Objective func	Maximization