Reference no: EM131662434

An investment firm has $1 million to invest in stocks, bonds, certificates of deposit, and real estate. The firm wishes to determine the mix of investments that will maximize the cash value at the end of 6 years. Opportunities to invest in stocks and bonds will be available at the beginning of each of the next 6 years. Each dollar invested in stocks will return $1.20 (a profit of $0.20) 2 years later; the return can be immediately reinvested in any alternative. Each dollar invested in bonds will return $1.40 3 years later; the return can be reinvested immediately. Opportunities to invest in certificates of deposit will be available only once, at the beginning of the second year. Each dollar invested in certificates will return $1.80 four years later. Opportunities to invest in real estate will be available at the beginning of the fifth and sixth years. Each dollar invested will return $1.10 one year later. To minimize risk, the firm has decided to diversify its investments. The total amount invested in stocks cannot exceed 30% of total investments, and at least 25% of total investments must be in certificates of deposit. The firm’s management wishes to determine the optimal mix of investments in the various alternatives that will maximize the amount of cash at the end of the sixth year.

Please formulate a linear model and solve using Excel Solver. Please show all Excel screenshots and formulas used in the cells.