Can someone help me setup this and explain the logic please?

Tata Corp is trying to complete its investment plans for the next five years. Currently Tata Corp has $3.9 million available for investment. Furthermore, Tata Corp expects income streams from other business ventures over the next 4 years of $2.5 million, $1.3 million, $2.2 million, 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.)

Cash Available for Investment ($millions)

Now 3.9

One Year from Now 2.5

Two Years from Now 1.3

Three Years from Now 2.2

Four Years from Now 1.6

There are three development projects that Tata Corp is considering. Hotel, Mall and Office.

Office is to be built starting in 2 years after smaller buildings are demolished.

If Tata Corp 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) Hotel Mall Office

Now 5.2 4.6 0

One Year from Now 2.2 3.1 0.2

Two Years from Now 1.6 2.8 2.8

Three Years from Now 2.2 2.2 2.8

Four Years from Now 2.6 2.4 2.6

----------------------------------------------------------------------

Value Five Years from Now 14 12 16

Assume that Tata Corp may participate either fully, fractionally (with a partner), or not at all in any or all of the three projects. If Tata Corp participates in a project fractionally at less than 100%, all the cash flows, and the final value of that project for Tata Corp are reduced proportionally. For example, if Tata Corp participates at 50% in the Hotel, the cash flows would be $2.6 million (now); $1.1 million (one year from now); +$0.8 million (two years from now); +$1.1 million (three years from now); and +$1.3 million (four years from now); the estimated value to Tata Corp five years from now would be $7 million. Tata Corp can borrow money at 3% 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.03 million is due three years from now). A new loan can be taken out in any year. By company policy, Tata Corp keeps all surplus cash in a savings account and must maintain a minimum balance of $700 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 4 years from now). Assume no interest earned now (it is already included in the $3.9 million available now). Tata Corp 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.