3. You recently received $1,000,000 because you were the 1,000,000th person to purchase a 20-ounce Sprite Zero. You want to invest the $1,000,000 by constructing a portfolio of securities. After looking at the historical returns produced by the three classes of assets in the authors' analysis, you decided to begin to construct your portfolio by allocating the $1,000,000 to two of the assets in the analysis. You decide on the following allocation scheme: 80% to common stocks and 20% to treasury bills. You want to estimate your portfolio's expected return and standard deviation based on your asset allocation scheme. You want to analyze the assets carefully to generate probability distributions of their expected returns. To satisfy your immediate curiosity, you decide to use the historical values relative to the securities from the above table to compute a very rough, preliminary estimate of your portfolio's expected return and standard deviation, Compute your rough estimates of the expected return from your portfolio and its standard deviation. Amount Expected Return on Portfolio (3 points) Portfolio's Standard Deviation (3 points) SUPPORTING COMPUTATIONS REQUIRED: d) You subsequently analyze the common stock class. You identify three possible scenarios that could affect their expected returns and estimate the probabilities of each scenario as: Scenario Best-case Optimistic-case Most-likely case Pessimistic-case Worst-case Expected Return Probability 45% 0.05 30% 0.15 16% 0.60 -8% 0.15 -25% 0.05 Create an Excel worksheet to compute the following values for the common stock class: Bridgewater State University Take-Home Problem Set #1 ACFI 490 Investments Page 11 of 13 Summer 2021 Expected Return on the Common Stock (4 points) Standard Deviation of the Expected Returns (6 points) EXCEL WORKSHEET REQUIRED: c) Use the above amounts and the historical T-bill rate to compute the expected return and standard deviation of your portfolio. (6 points) Amount Expected Return on Portfolio (3 points) Portfolio's Standard Deviation (3 points) SUPPORTING COMPUTATIONS REQUIRED: 3. You recently received $1,000,000 because you were the 1,000,000th person to purchase a 20-ounce Sprite Zero. You want to invest the $1,000,000 by constructing a portfolio of securities. After looking at the historical returns produced by the three classes of assets in the authors' analysis, you decided to begin to construct your portfolio by allocating the $1,000,000 to two of the assets in the analysis. You decide on the following allocation scheme: 80% to common stocks and 20% to treasury bills. You want to estimate your portfolio's expected return and standard deviation based on your asset allocation scheme. You want to analyze the assets carefully to generate probability distributions of their expected returns. To satisfy your immediate curiosity, you decide to use the historical values relative to the securities from the above table to compute a very rough, preliminary estimate of your portfolio's expected return and standard deviation, Compute your rough estimates of the expected return from your portfolio and its standard deviation. Amount Expected Return on Portfolio (3 points) Portfolio's Standard Deviation (3 points) SUPPORTING COMPUTATIONS REQUIRED: d) You subsequently analyze the common stock class. You identify three possible scenarios that could affect their expected returns and estimate the probabilities of each scenario as: Scenario Best-case Optimistic-case Most-likely case Pessimistic-case Worst-case Expected Return Probability 45% 0.05 30% 0.15 16% 0.60 -8% 0.15 -25% 0.05 Create an Excel worksheet to compute the following values for the common stock class: Bridgewater State University Take-Home Problem Set #1 ACFI 490 Investments Page 11 of 13 Summer 2021 Expected Return on the Common Stock (4 points) Standard Deviation of the Expected Returns (6 points) EXCEL WORKSHEET REQUIRED: c) Use the above amounts and the historical T-bill rate to compute the expected return and standard deviation of your portfolio. (6 points) Amount Expected Return on Portfolio (3 points) Portfolio's Standard Deviation (3 points) SUPPORTING COMPUTATIONS REQUIRED