Raw Preview of Attachment:(refer to the detailed question and attachment below)
College Expenses Example 3.5: Planning for CollegeTwo parents want to provide for their daughter's college education with some money they have recently inherited. They would like to set aside part of the inheritance in an account that would cover the needs of their daughter's college education, which begins four years from now. They estimate that first-year college expenses will come to $24,000 and increase $2000 per year during each of the remaining three years of college. The following investments are available. Open table as spreadsheetInvestmentAvailableMaturesReturn atmaturityAEvery year1 year6%B1, 3, 5, 72 years14%C1,43 years18%D17 years65%The parents would like to determine an investment plan that provides the necessary funds to cover college expenses with the smallest initial investment.Investment and funds-flow problems of this sort lend themselves to network modeling. In this type of problem, nodes represent points in time at which funds flow could occur. We can imagine tracking the balance in a bank account, with funds flowing in and out depending on our decisions. In Example 3.5, we include a node for now (time zero), and nodes for the end of years 1 through 7. Tracking time for this purpose, the end of year 3 and the start of year 4 are in effect the same point in time. To construct a typical start-of-year node, we first list the potential inflows and outflows that can occur.InflowsInitial investmentAppreciation of investment A from 1 year agoAppreciation of investment B from 2 years agoAppreciation of investment C from 3 years agoAppreciation of investment D from 7 years agoOutflowsExpense payment for the coming yearInvestment in A for the coming yearInvestment in B for the coming 2 yearsInvestment in C for the coming 3 yearsInvestment in D for the coming 7 yearsNot all of these inflows and outflows apply at every point in time, but if we sketch the eight nodes and the flows that do apply, we come up with a diagram such as the one shown as Figure 3.13. In this diagram, A1 represents the amount allocated to Investment A at time zero, A2 represents the amount allocated to Investment A at the start of year 2, and so on. The initial investment in the account is shown as I0, and the expense payments are labeled with their numerical values. The diagram shows the different nodes as independent elements, which is all we really need; however, Figure 3.14 shows a tidier diagram in which the nodes are connected in a single flow network. We do not need the variable B7 in the model. A 2-year investment starting in year 7 would extend beyond the 8-year horizon, so this option is omitted. However, the variable A8 does appear in the model. We can think of A8 as representing the final value in the account. Perhaps it is intuitive that, if we are trying to minimize the initial investment, there is no reason to have money in the account in the end. Still, to verify this intuition, we can include A8 in the model, anticipating that we will find A8 = 0 in the optimal solution.The next step is to convert the diagram into a linear programming model. For this purpose, the flows on the diagram become decision variables. Then, each node gives rise to a balance equation, as listed below.Figure 3.15 shows these EQ constraints as part of the spreadsheet model. A systematic pattern is formed by the coefficients in the columns of the constraint equations. Each column has two nonzero coefficients: a positive coefficient (of 1), corresponding to the time the investment is made, and a negative coefficient (reflecting the appreciation rate), corresponding to the time the investment matures. The only exceptions are 10 and A8, which essentially represent flows into and out of the network. In other funds-flow models, the column coefficients portray the investment-and-return profile on a per-unit basis for each of the variables. Following our sign convention, the right-hand-side constants show the profile of flows in and out of the system over the various time periods. In this case, the constants in the last four constraints are negative, reflecting required outflows from the investment account in the last four years of the plan. Figure 3.15: Spreadsheet model for Example 3.5The objective function in this example is simply the initial size of the investment account, represented by the variable I0, which we want to minimize. Thus, we can depart from the standard form (which uses the SUMPRODUCT function as an objective) and designate the objective function simply by referencing cell B5. The model specification is as follows.Objective:B8 (minimize)Variables:B5:P5Constraints:Q10:Q17 = S10:S17When we minimize I0, Solver provides the optimal solution shown in Figure 3.15, calling for an initial investment of about $74,422. Compare this with the nominal value of the college expenses, which sum to $108,000. The lower figure for the initial investment testifies to the power of compound interest. In our example, the parents can use linear programming to take advantage of interest rate patterns and minimize the investment they need to make in order to cover the prospective costs of their daughter's college education.The nature of the optimal solution may not be too surprising. First, the return of18 percent on the three-year investment is dominated by the return of 6 percent on the one-year investment A, due to compounding. Thus, we should not expect to see any use of the three-year instruments in the optimal solution. The return on the one-year investment is dominated, in turn, by the return on the two-year investment and the return on the seven-year investment. Thus, we should expect to see substantial use of those two instruments. The use of the one-year investment is dictated by timing: Because it is the only investment maturing at the end of year 5, it becomes the vehicle to meet the $26,000 requirement. Prior to that, the solution uses B3 (funded in turn by B1) to cover the first year of expenses and to fund A5. Then B5 covers the third year of expenses, funded by B3. Finally, D1 covers the fourth year of expenses. As expected, the account should be empty at the end of the planning horizon.The network diagram provides another perspective on this solution structure. In Figure 3.16, we show the network diagram with only the positive flows displayed. The diagram shows clearly that the only vehicle in the optimal solution for meeting the $30,000 requirement at node 7 is the investment in D1. Therefore, the size of the initial investment in D1 must be 30,000/1.65 = 18,182. Working backwards, we can also see that the size of B5 is dictated by the $28,000 requirement, and A5 is dictated by the $26,000 requirement. Once A5 and B5 are determined, they, together with the $24,000 requirement, dictate the size of B3. In turn, B3 dictates the size of B1. The diagram systematically conveys the detailed pattern in the optimal solution.Box 3.2: Characteristics of General Network Models for Funds FlowDecision variablesArcs correspond to sources and uses of funds.Make investments; pay off debts owed.Objective functionReference a single variable.Minimize initial investment or maximize final value.ConstraintsNodes correspond to points in time.A balance equation corresponds to each node.For investments, the column depicts the pattern of principal and returns.For loans, the column depicts the pattern of principal and interest payments.RHS constants describe external flows to and from the network.Add lower-bound or upper-bound constraints as needed. Figure 3.16: Flow diagram with optimal flows for Example 3.5Box 3.2 summarizes the important features of network models for funds flow problems. In a multiperiod investment model, flows expand as they travel along arcs. Matter is not conserved as it flows, so we lose the conservation of matter that holds implicitly for flows between nodes in special networks. However, balance equations still apply at each node, in the sense that the total flow out of a node always equals the total flow in. The flows are still denominated in currency wherever they appear in the network. (Money is not converted into product, for example.) By contrast, we look next at a class of network models in which the flows are transformed.
Solution: 3.8. College Expenses Revisited Revisit the college expense planning network