In: Operations Management
The following tables shows 5 year returns of different funds
Mutual Fund 
Annual Return (%) 

Year 1 
Year 2 
Year 3 
Year 4 
Year 5 

Stocks 
12.01 
11.22 
13.47 
45.42 
21.93 
Bonds 
17.64 
4.25 
7.51 
1.33 
7.36 
Aggressive Growth 
32.41 
18.71 
30.09 
41.46 
23.26 
Aggressive Value 
32.36 
20.61 
12.93 
7.06 
5.37 
Moderate Growth 
33.44 
19.40 
6.77 
58.68 
9.02 
Moderate Value 
24.56 
25.32 
6.70 
5.43 
17.31 
Let’s assume the minimum return every year across all the funds is M. This means sum of money invested in stock, bonds, aggressive growth, aggressive value, moderate growth and moderate value in any year will return at least M%.
Keep in mind that the total percentage of money allocated across all the funds in any year will be 100%. This means stocks + bonds + …… moderate value = 100% or 1
The objective here is to maximize return in any year. E.g. in year 1 to maximize return, one can invest all the money in moderate growth, but this will not work because in year 2, moderate value returns higher. Therefore, the objective is to come up with an allocation across all the funds such that the return is maximum in the 5 year period.
The optimal allocation of funds in each of the five years is determined using the spreadsheet model as below:
Formulas:
B11 =SUMPRODUCT(B3:B8,G3:G8) copy to B11:F11
G9 =SUM(G3:G8) copy to G9:K9
G11 =SUM(B11:F11)