Question

Larry Fredendall is trying to plan for his daughter Susan’s college expenses. Based on current projections (it is now the start of year 1), Larry anticipates that his financial needs at the start of each of the following years is as shown in the table below:

Year	3	4	5	6
$ Needed	$20,000	$22,000	$24,000	$26,000

Larry has several investment choices to choose from at the present time, as listed in the table below.   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 and also assume that returns are tax free if used for education.

Choice	ROI	Maturity
A	5%	1 year
B	13%	2 years
C	28%	3 years
D	40%	4 years

Because choices C and D are relatively risky choices, Larry wants no more than 20% of his total investment in those two choices at any point in time.

Larry 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.

Formulate as a linear programming problem and solve.

Answer 1

FORMULATING THE PROBLEM

Decision Variables: Note that in defining these variables, we need to consider only those investments that will mature by the end of year 5, at the latest, because there is no requirement after 6 years:

A1 = $ amount invested in choice A at the start of year 1

B1 = $ amount invested in choice B at the start of year 1

C1 = $ amount invested in choice C at the start of year 1

D1 = $ amount invested in choice D at the start of year 1

A2 = $ amount invested in choice A at the start of year 2

B2 = $ amount invested in choice B at the start of year 2

C2 = $ amount invested in choice C at the start of year 2

D2 = $ amount invested in choice D at the start of year 2

A3 = $ amount invested in choice A at the start of year 3

B3 = $ amount invested in choice B at the start of year 3

C3 = $ amount invested in choice C at the start of year 3

A4 = $ amount invested in choice A at the start of year 4

B4 = $ amount invested in choice B at the start of year 4

A5 = $ amount invested in choice A at the start of year 5

Objective Function: The objective is to minimize the initial investment and can be expressed as

Minimize A1 + B1 + C1 + D1

As in the multiperiod production scheduling problem, we need to write balance constraints for each period (year). These constraints recognize the relationship between the investment decisions made in any given year and the investment decisions made in all prior years. Specifically, we need to ensure that the amount used for investment at the start of a given year is restricted to the amount maturing at the end of the previous year less any payments made for Susan’s education that year. This relationship can be modelled as

(Amount invested at start of year t + Amount paid for education at start of year t = Amount maturing at end of year t-1)

At the start of year 2, the total amount maturing is 1.05A1 (investment in choice A in year 1 plus 5% interest). The constraint at the start of year 2 can therefore be written as

A2 + B2 + C2 + D2 = 1.05*A1              (year 2 cash flow)

Constraints at the start of years 3 through 6 are as follows and also include the amounts payable for Susan’s education each year:

A3 + B3 + C3 + 20,000 = 1.13*B1 + 1.05*A2                           (year 3 cash flow)

A4 + B4 + 22,000 = 1.28C1 + 1.13B2 + 1.05A3                       (year 4 cash flow)

A5 + 24,000 = 1.4D1 + 1.28C2 + 1.13B3 + 1.05A4                (year 5 cash flow)

26,000 = 1.4D2 + 1.28C3 + 1.13B4 + 1.05A5                          (year 6 cash flow)

These five constraints address the cash flow issues. However, they do not account for Larry’s risk preference with regard to investments in choices C and D in any given year. To satisfy these requirements, we need to ensure that total investment in choices C and D in any year is no more than 20% of the total investment in all choices that year. In keeping track of these investments, it is important to also account for investments in prior years that may have still not matured. At the start of year 1, this constraint can be written as

C1 + D1 ≤ 0.2*(A1 + B1 + C1 + D1)                             (year 1 risk)

In writing this constraint at the start of year 2, we must take into account the fact that investments B1, C1, and D1 have still not matured. Therefore,

C1 + D1 + C2 + D2 ≤ 0.2*(B1 + C1 + D1 + A2 + B2 + C2 + D2)                            (1year 2 risk)

Constraints at the start of years 3 through 5 are as follows. Note that there is no constraint necessary at the start of year 6 because there are no investments that year:

C1 + D1 + C2 + D2 + C3 ≤ 0.2*(C1 + D1 + B2 + C2 + D2 + A3 + B3 + C3)        (year 3 risk)

D1 + C2 + D2 + C3 ≤ 0.2*(D1 + C2 + D2 + B3 + C3 + A4 + B4)                            (year 4 risk)

D2 + C3 ≤ 0.2*(D2 + C3 + B4 + A5)                                                                             (year 5 risk)

Finally, we have the nonnegativity constraints:

All variables ≥ 0

SOLVING THE PROBLEM AND INTERPRETING THE RESULTS

The problem can be solved using Excel Solver and it will produce following results:

1.05*A1 - A2 - B2 - C2 - D2 = 0                                                    (year 2 cash flow)

1.13*B1 + 1.05*A2 - A3 - B3 - C3 = 20,000                             (year 3 cash flow)

1.28*C1 + 1.13*B2 + 1.05*A3 - A4 - B4 = 22,000                 (year 4 cash flow)

1.4*D1 + 1.28*C2 + 1.13*B3 + 1.05*A4 - A5 = 24,000       (year 5 cash flow)

1.4*D2 + 1.28*C3 + 1.13*B4 + 1.05*A5 = 26,000                (year 6 cash flow)

The optimal solution requires Larry to invest a total of $73,314.71 at the start of year 1, putting $61,064.11 in choice B, $3,804.66 in choice C, and $8,445.95 in choice D. There is no money maturing for investment at the start of year 2. At the start of year 3, using the maturing amounts, Larry should pay off $20,000 for Susan’s education, invest $38,227.50 in choice A, and invest $10,774.93 in choice B. At the start of year 4, Larry should use the maturing amounts to pay off $22,000 for Susan’s education and invest $23,008.85 in choice B. The investments in place at that time will generate $24,000 at the start of year 5 and $26,000 at the start of year 6, meeting Larry’s requirements in those years.