(Business Analytics) Problem 1) Maureen Laird is the chief financial officer for the Alva Electric Co., a major public utility in the Midwest. The company has scheduled the construction of new hydroelectric plants 5, 10, and 20 years from now to meet the needs ofthe growing population in the region served by the company. To cover the construction costs, Maureen needs to invest some of the company’s money now to meet these future cash flow needs. Maureen may purchase only three kinds of financial assets, each of which costs $1 million per unit. Fractional units may be purchased. The assets produce income 5, 10, and 20 years from now, and that income is needed to cover minimum cash flow requirements in those years, as shown in the following table. Income Per Unit of Assets Year Asset 1 Asset 2 Asset 3 Minimum Cash Flow Required 5 $8 Million $2 Million $2 Million $1.6 Billion 10 $2 Million $4 Million $4 Million $400 Million 20 0 $6 Million $8 Million $1.2 Billion Maureen wishes to determine the mix of investments in these assets that will cover the cash flow requirements while minimizing the total amount invested. a.Formulate a linear programming model for this problem on a spreadsheet. b. Use the spreadsheet to check the possibility of purchasing 100 units of asset 1, 100 units of asset 2, and 200 units of asset 3. How much cash flow would this mix of investments generate 5, 10, and 20 years from now? What would be the total amount invested? c. Take a few minutes to use a trial-and-error approach with the spreadsheet to develop your best guess for the optimal solution. What is the total amount invested for your solution?

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...

See similar textbooks

Related questions

Question

(Business Analytics)

Problem 1)

Maureen Laird is the chief financial officer for the Alva Electric Co., a major public utility in the Midwest. The company has scheduled the construction of new hydroelectric plants 5, 10, and 20 years from now to meet the needs ofthe growing population in the region served by the company. To cover the construction costs, Maureen needs to invest some of the company’s money now to meet these future cash flow needs. Maureen may purchase only three kinds of financial assets, each of which costs $1 million per unit. Fractional units may be purchased. The assets produce income 5, 10, and 20 years from now, and that income is needed to cover minimum cash flow requirements in those years, as shown in the following table.

Income Per Unit of Assets
Year	Asset 1	Asset 2	Asset 3	Minimum Cash Flow Required
5	$8 Million	$2 Million	$2 Million	$1.6 Billion
10	$2 Million	$4 Million	$4 Million	$400 Million
20	0	$6 Million	$8 Million	$1.2 Billion

Maureen wishes to determine the mix of investments in these assets that will cover the cash flow requirements while minimizing the total amount invested.

a.Formulate a linear programming model for this problem on a spreadsheet.

b. Use the spreadsheet to check the possibility of purchasing 100 units of asset 1, 100 units of asset 2, and 200 units of asset 3. How much cash flow would this mix of investments generate 5, 10, and 20 years from now? What would be the total amount invested?

c. Take a few minutes to use a trial-and-error approach with the spreadsheet to develop your best guess for the optimal solution. What is the total amount invested for your solution?

Expert Solution