The Poughkeepsie Development Corporation (PDC) is trying to complete its investment plans for the next five years. Currently PDC has $4.9 million available for investment. Furthermore, PDC expects income streams from other business ventures over the next 4 years of $3.8 million, $2.6 million, $3.6 million, and then $3.0 million. These data are summarized in the table below. (For simplicity of modeling, you may assume that these income streams occur at one year intervals.) Cash Available for Investment ($millions) Now One Year from Now Two Years from Now Three Years from Now Four Years from Now 4.90 3.80 2.60 3.60 3.00 There are three development projects that PDC is considering. The first is the Poughkeepsie Vacation Resort on the picturesque banks of the Hudson River. A second project, the Hudson River Mall, would be a shopping complex adjacent to the Poughkeepsie Vacation Resort. The third project, the Poughkeepsie Office Building (or POB), is a 6- story office building to be built in downtown Poughkeepsie starting in two years, after the demolition of several smaller buildings. If PDC participates fully in these projects, each would have the projected cash flow streams over the next four years as indicated in the following table. (Again, for simplicity, you may assume these cash flows occur at one year intervals.) The estimated value of each project five years from now is also indicated. Cash Flow ($millions) Vacation Resort Hudson River Mall Office (POB) Now One Year from Now Two Years from Now -6.00 -5.80 0.00 -4.40 -2.80 -0.10 3.60 2.20 -2.50 Three Years from Now 3.00 2.80 -1.80 Four Years from Now 3.20 3.20 -2.20 Value Five Years from Now ($millions) 14.00 13.00 15.00 Assume that PDC may participate either fully, fractionally (with a partner), or not at all in any or all of the three projects. If PDC participates in a project fractionally at less than 100%, all the cash flows, and the final value of that project for PDC are reduced proportionally. For example, if PDC participates at 50% in the Poughkeepsie Vacation Resort, the cash flows would be -$3.0 million (now); -$2.2 million (one year from now); +$1.8 million (two years from now); +$1.5 million (three years from now); and +$1.6 million (four years from now); the estimated value to PDC five years from now would be $7 million. PDC can borrow money at 5% interest per year. At most $4 million can be borrowed in any one year. The loan must be paid back the following year with interest (e.g., if $1 million is borrowed two years from now, $1.05 million is due three years from now). A new loan can be taken out in any year. By company policy, PDC keeps all surplus cash in a savings account and must maintain a minimum balance of $800 thousand at all times. The savings account earns 1% interest annually (e.g., if there is a $1 million ending balance in the savings account 3 years from now, then $1.01 million would be available years from now). Assume no interest earned "now" (it is already included in the $4.9 million available now). PDC would like to know how much to participate in each project and how much to borrow each year so as to maximize their net worth five years from now (the value of their development projects plus any surplus funds and savings interest minus any loan principal and interest due). Assume no loan is taken five years from now (it would have no effect on net worth, since the cash asset would be offset by the loan liability). To simplify the modeling, you may assume that all cash flows occur simultaneously at one year intervals (now, one year from now, etc.). Set up and solve a linear programming spreadsheet model for this problem. A B 10 11 12 789012 C D E Savings Interest 1.00% Loan Interest 5.00% FL G H I J K L M Cash Flow ($millions) (please show two decimal places of precision) Cash Flow ($millions) Resort Mall Office Other Income Beginning Balance Various Cash Flows Ending (add or delete columns as needed) Balance 13 Now -6.00 -5.80 0.00 4.90 14 1 Year from Now -4.40 -2.80 -0.10 3.80 15 2 Years from Now 3.60 2.20 -2.50 2.60 16 69 3 Years from Now 3.00 2.80 -1.80 3.60 17 4 Years from Now 3.20 3.20 -2.20 3.00 18 19 20 21 8661 Value 5 Years from Now 14.00 13.00 15.00