An investor seeks to establish an investment portfolio using the lowest possible initial investment that will generate specific amounts of capital at specific time periods in the future. We call this type of problem the sinking fund problem. Marcel Lapierre is trying to plan for his daughter Chantals university expenses. Based on current projections (it is now the start of year 1), Marcel anticipates that his financial need at the start of each of the following years is as follows (shown below left):

Marcel has several investment choices (assume these are tax free if used for education) at the present time, as also shown above (right). Each choice has a fixed known return on investment and a specified maturity date. Assume that each choice is available for investment at the start of every year. Marcel wants to establish a sinking fund to meet his requirements. Note that at the start of year 1, the entire initial investment is available for investing in the choices. However, in subsequent years, only the amount maturing from a prior investment is available for investment. Remember that this is a multi-year investment problem. Use the following decision variables - you do not need to introduce any additional decision variables.

Let

Ai be the amount (in $) invested in Choice A at the beginning of year i; i=1,2,3,4,5;

Bi be the amount (in $) invested in Choice B at the beginning of year i; i=1,2,3,4;

Ci be the amount (in $) invested in Choice C at the beginning of year i; i=1,2,3; Di be the amount (in $) invested in Choice D at the beginning of year i; i=1,2;

1. (a) Formulate an analytical LP model for this problem.

2. (b) Solve this LP model using Excel Solver to find the optimal solution.

Year 3 Year 4 Year 5 Year 61 $20,000 $22,000 $24,000 $26,000 Choice A B D ROI 5% 13% 28% 40% Maturity 1 year 2 years 3 years 4 years Year 3 Year 4 Year 5 Year 61 $20,000 $22,000 $24,000 $26,000 Choice A B D ROI 5% 13% 28% 40% Maturity 1 year 2 years 3 years 4 years