please see attached images.

We have already entered the base case data for each model in this file, and the models have performed the analysis for preceding parts of the problem. You will need to enter the data for each of the remaining parts of the problem--we indicate in each problem the parts that should be done using the spreadsheet. However, there are several points worth noting before you go into a model: 1. The input data are entered in specified cells in the INPUT DATA section. When you change an input item, the model automatically recalculates the values of appropriate output data items, unless you are told otherwise. If the values do not change automatically, press the F9 key to recompute the values. 2. The key output data are displayed to the right of the INPUT DATA section or immediately below it. This placement permits you to change an input and instantly see how that change affects the output of the model. This is extremely useful in sensitivity analysis. 3. Input data items that you can change are distinguished from the ones you should not change. The items that you can change are highlighted in color (blue) whereas the other items are printed in black. 4. All percentages must be entered as decimals. Dollars and other numbers must be entered without dollar signs or commas. 5. Instructions and comments concerning specific models accompany each model. Graphs associated with the model are included in another worksheet that can be accessed by clicking on the worksheet labeled GRAPH at the bottom of the spreadsheet. Chapter 8 Spreadsheet Problem Solutions (C08) Realized Rates of Return 1. There are a number of instructions with which you should be familiar to use these computerized models. These instructions appear in a separate worksheet labeled INSTRUCTIONS. If you have not already done so, you should read these instructions now. To read these instructions, click on the worksheet labeled INSTRUCTIONS. 2. A graph which shows the return and standard deviation of each stock and the portfolio will be displayed if you click the worsheet labeled GRAPH at the bottom of this spreadsheet. To return to this worksheet, click on the worksheet labeled C08 at the bottom of the GRAPH worksheet. INPI' TA. Perc Use the model in the attached spreadsheet to solve the problem. Module 3 Problem Spreadsheet Stock A and Stock B produced the following returns during the past five years (Year-1 is one year ago, Year-2 is two years ago, and so forth): Stock A and Stock B Returns A. Calculate the average rate of return for each stock during the past five years. B. Assume someone held a portfolio consisting of 50 percent Stock A and 50 percent Stock B. (1) What would have been the realized rate of return on the portfolio in each year for the past five years? (2) What would have been the average return on the portfolio during this period? C. Calculate the standard deviation of returns for each stock and for the portfolio. D. Calculate the coefficient of variation for each stock and for the portfolio. If you are a riskaverse investor, would you prefer to hold Stock A, Stock B, or the portfolio? Why? E. Assume a third stock, Stock C, is available for inclusion in the portfolio. Stock C produced the following returns during the past five years: Input these values and calculate the average return, standard deviation, and coefficient of variation for Stock C. F. Assume the portfolio now consists of 33.33 percent Stock A,33.33 percent Stock B, and 33.34 percent Stock C. How does this composition affect the portfolio return, standard deviation, and coefficient of variation versus when 50 percent was invested in A and in B ? G. Make some other changes in the portfolio, making sure that the percentages sum to 100 percent. For example, enter 25 percent for Stock A, 25 percent for Stock B, and 50 percent for Stock C. Notice that rP remains constant and that P changes. Why do these results occur? H. In part b, you should see that the standard deviation of the portfolio decreased only slightly because Stocks A and B were highly positively correlated with each other. The addition of Stock C causes the standard deviation of the portfolio to decline dramatically, even though C=A=B. What does this change indicate about the correlation between Stock C and Stocks A and B ? I. Would you prefer to hold a portfolio consisting only of Stocks A and B or a portfolio that also includes Stock C ? If others react similarly, how might this fact affect the stocks' prices and rates of return