Aberdeen Construction Corporation.

The Aberdeen Construction Corporation (ACC) is trying to complete its investment plans for the next five years. Currently ACC has $5.2 million available for investment. Furthermore, ACC expects income streams from other business ventures over the next four years of $2.6, $1.8, $2.6, and then $1.6 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.)

There are three development projects that ACC is considering. The first is the Aberdeen Vacation Resort on the picturesque banks of Grays Harbor. A second project, the Northshore Mall, would be a shopping complex adjacent to Grays Harbor. The third project, the Aberdeen Office Building, is a 4-story office building to be built in downtown Aberdeen starting in two years, after the demolition of several smaller buildings. If ACC 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, assume these cash flows occur at one year intervals.) The estimated value of each project five years from now is also indicated.

Assume that ACC may participate either fully, fractionally (with a partner), or not at all in any or all of the three projects. If ACC participates in a project fractionally at less than 100%, all the cash flows, and the final value of that project for ACC are reduced proportionally. For example, if ACC participates at 50% in the Aberdeen Vacation Resort, the cash flows would be $2.1 million (now); $1.6 million (one year from now); +$1.2 million (two years from now); +$0.9 million (three years from now); and +$1.2 million (four years from now). The estimated value to ACC five years from now would be $4 million. ACC can borrow money at 4% interest per year. At most $3 million can be borrowed in any given year. The loan must be paid back the following year with interest (e.g., if $1 million is borrowed two years from now, $1.04 million is due three years from now). By company policy, ACC must maintain a minimum balance of $1 million. ACC invests any and all remaining surplus funds (including the $1 million minimum balance) in a money market fund that earns a 1% return per year (e.g., if $2 million in surplus funds remain 3 years from now, then $2.02 million would be available 4 years from now). Assume no interest earned now (it is already included in the $5.2 million available now). ACC 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 five years from now after accounting for all cash flows through the five years). 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, 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. (when building your model please follow the data structure outlined in columns I through M of the data spreadsheet. Calculate beginning and ending balances at each year, with all cash flows calculated in separate columns in between; add or delete intermediate columns as needed, and label all columns.)

Cash Available for Investment ($millions) Now 5.2 One Year from Now 2.6 Two Years from Now 1.8 Three Years from Now 2.6 Four Years from Now 1.6 Cash Flow (Smillions) Now One Year from Now Two Years from Now Three Years from Now Four Years from Now Value Five Years from Now (Smillions) Vacation Resort -4.2 -3.2 2.4 1.8 2.4 8 Northshore Mall -5.4 -2.4 1.8 2.4 2.8 10 Office 0 -0.3 -2.5 -1.1 -1.3 9 A B E F G I J K L M 1 Aberdeen Construction Corporation 1% Savings Interest Rate Loan Interest Rate Cash Flow ($millions) (please show two decimal places of precision) 4% Beginning Balance Various Cash Flows (add or delete columns as needed) Ending Balance 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Cash Flow ($millions) Now 1 Year from Now 2 Years from Now 3 Years from Now 4 Years from Now Resort -4.20 -3.20 2.40 1.80 2.40 Mall -5.40 -2.40 1.80 2.40 2.80 Office 0.00 -0.30 -2.50 -1.10 -1.30 Cash Available 5.20 2.60 1.80 2.60 1.60 Value 5 Years from Now 8.00 10.00 9.00 Cash Available for Investment ($millions) Now 5.2 One Year from Now 2.6 Two Years from Now 1.8 Three Years from Now 2.6 Four Years from Now 1.6 Cash Flow (Smillions) Now One Year from Now Two Years from Now Three Years from Now Four Years from Now Value Five Years from Now (Smillions) Vacation Resort -4.2 -3.2 2.4 1.8 2.4 8 Northshore Mall -5.4 -2.4 1.8 2.4 2.8 10 Office 0 -0.3 -2.5 -1.1 -1.3 9 A B E F G I J K L M 1 Aberdeen Construction Corporation 1% Savings Interest Rate Loan Interest Rate Cash Flow ($millions) (please show two decimal places of precision) 4% Beginning Balance Various Cash Flows (add or delete columns as needed) Ending Balance 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Cash Flow ($millions) Now 1 Year from Now 2 Years from Now 3 Years from Now 4 Years from Now Resort -4.20 -3.20 2.40 1.80 2.40 Mall -5.40 -2.40 1.80 2.40 2.80 Office 0.00 -0.30 -2.50 -1.10 -1.30 Cash Available 5.20 2.60 1.80 2.60 1.60 Value 5 Years from Now 8.00 10.00 9.00