Determine the optimal amount for Pierre to invest in each option each year. What percentage return should Pierre expect to make on his initial investment?

Create a sensitivity report and use it to determine what will happen if the maximum investment in investment A in year 1 is changed to $4 million.

Create a sensitivity report and use it to determine what will happen if the maximum investment in investment E is changed to $2 million.

****PLEASE USE EXCEL SOLVER AND SHOW FORMULAS *****

Part 1 The first item on Pierre's agenda is to devise a five-year investment plan. He has set aside $5 million to initially place into this portfolio, and will not add any other funds later. Any investment proceeds realized during the five-year window will be reinvested. The goal is to generate as much money as possible by the end of the fifth year. The proceeds will go towards his senate campaign. For brevity, we will call the investments A, B, C, D, and E. The below table shows the years that each investment can be invested in, the duration of time until the investments mature, and the maximum amount that can be invested in each investment (per period). For example, investment C can be invested in up to two times: at the beginning of year 1 or at the beginning of year 3. Each time the maximum investment amount is $3 million (so, in total, $6 million could be placed in this investment). Available Years Max Amount per Investment Investment Duration A 1 2 2 3 B c 2 1,2,4,5 2,4 1,3 1,2,3 2 3 D 3 1 E 5 The returns from the investments are shown below. These returns are realized when an investment matures (i.e., they are not all annual returns). Some investments have variable returns that are based on the year in which the investment was made, while others return the same amount no matter when the investment was made. Also, we should note that there is an option to invest in cash every year. There is no maximum amount for cash investments. Year B D E Cash 1 5% 9% 2.0% 4.0% 8% 12% 9% 9% 6% 2 3 4 5 1.5% 1.5% 1.5% 1.5% 1.5% 8% 3.0% 2.0% For example, if $1 million is invested in C at the beginning of year 1, Pierre will realize a 5% return at the end of year 2/beginning of year 3 (because the duration of investment C is 2 years). In addition, Pierre would receive his $1 million principle back, so in total he would have $1 million +0.05*$1 million = $1.05 Million available for reinvestment at the beginning of year 3. Part 1 The first item on Pierre's agenda is to devise a five-year investment plan. He has set aside $5 million to initially place into this portfolio, and will not add any other funds later. Any investment proceeds realized during the five-year window will be reinvested. The goal is to generate as much money as possible by the end of the fifth year. The proceeds will go towards his senate campaign. For brevity, we will call the investments A, B, C, D, and E. The below table shows the years that each investment can be invested in, the duration of time until the investments mature, and the maximum amount that can be invested in each investment (per period). For example, investment C can be invested in up to two times: at the beginning of year 1 or at the beginning of year 3. Each time the maximum investment amount is $3 million (so, in total, $6 million could be placed in this investment). Available Years Max Amount per Investment Investment Duration A 1 2 2 3 B c 2 1,2,4,5 2,4 1,3 1,2,3 2 3 D 3 1 E 5 The returns from the investments are shown below. These returns are realized when an investment matures (i.e., they are not all annual returns). Some investments have variable returns that are based on the year in which the investment was made, while others return the same amount no matter when the investment was made. Also, we should note that there is an option to invest in cash every year. There is no maximum amount for cash investments. Year B D E Cash 1 5% 9% 2.0% 4.0% 8% 12% 9% 9% 6% 2 3 4 5 1.5% 1.5% 1.5% 1.5% 1.5% 8% 3.0% 2.0% For example, if $1 million is invested in C at the beginning of year 1, Pierre will realize a 5% return at the end of year 2/beginning of year 3 (because the duration of investment C is 2 years). In addition, Pierre would receive his $1 million principle back, so in total he would have $1 million +0.05*$1 million = $1.05 Million available for reinvestment at the beginning of year 3