Question 3 (25 points) Minimum Balance Year 1 2 3 4 5 6 Cash Flow Needs and Yields at Full Participation Cash Flow from Project A Project B Project C Other Projects -6 -8 -5 8 -8 -10 -7 10 -10 -12 -10 6 32 -12 -10 8 0 55 -5 10 0 0 50 12 5 10 10 20 30 50 Lower Bound Participation Rate Upper Bound 0.20 1/3 0.80 0.20 1/3 0.80 0.20 1/3 0.80 Suggested initial setting You are advising a client organization to find the best combination of investments in three projects over the next six years. Partial investments (referred here as participation rates) are allowed, between a lower and upper bound set for safety. The client organization has an initial cash reserve of 12 [million $] for these investments. The cash flow needs of the projects at full participation are shown by negative numbers in the table above, see columns 2 to 4: the positive numbers indicate cash flow resulting from a completed project at 100% participation. There is an additional cash flow stream resulting from other projects, see column 5. At the end of each year the client organization wants to have a minimum cash balance shown by column 6. The goal of your client is to maximize their ending cash position in Year 6. All financial figures are expressed in [million $] units. Develop a spreadsheet model to formulate and solve this problem. Question 3 (25 points) Minimum Balance Year 1 2 3 4 5 6 Cash Flow Needs and Yields at Full Participation Cash Flow from Project A Project B Project C Other Projects -6 -8 -5 8 -8 -10 -7 10 -10 -12 -10 6 32 -12 -10 8 0 55 -5 10 0 0 50 12 5 10 10 20 30 50 Lower Bound Participation Rate Upper Bound 0.20 1/3 0.80 0.20 1/3 0.80 0.20 1/3 0.80 Suggested initial setting You are advising a client organization to find the best combination of investments in three projects over the next six years. Partial investments (referred here as participation rates) are allowed, between a lower and upper bound set for safety. The client organization has an initial cash reserve of 12 [million $] for these investments. The cash flow needs of the projects at full participation are shown by negative numbers in the table above, see columns 2 to 4: the positive numbers indicate cash flow resulting from a completed project at 100% participation. There is an additional cash flow stream resulting from other projects, see column 5. At the end of each year the client organization wants to have a minimum cash balance shown by column 6. The goal of your client is to maximize their ending cash position in Year 6. All financial figures are expressed in [million $] units. Develop a spreadsheet model to formulate and solve this
