EXCEL Assignemnt
Description Your boss was pleased with your last project and now wants you to perform a more extensive analysis by considering the Sharpe Ratio and is looking for a spreadsheet in which she can enter return information about two stocks. She then wants to see information about the portfolio of stocks which has the highest Sharpe ratio, she wants to know what proportion of risky portfolio and risk-free asset is required to achieve a given return, and she wants a chart that includes: The efficient frontier The capital markets line The tangency portfolio highlighted and A portfolio with a specific return highlighted. Step 1-Set up the Inputs In a new spreadsheet name a tab Two Stock Portfolio. All input and output on the spreadsheet should be on this tab. The key inputs are: Risk-free rate. The return on a risk-free investment. In this US this is usually assumed to be US Treasuries First and second stock expected returns First and second stock standard deviation of the return:s The correlation between the two stock returns Target Return. This is the return for the target portfolio. It should use data validation to restrict its input to be greater than or equal to the risk-free return You may want to name these inputs so that your formulas are easier to read. The input range should look something like: Portfolio Information Risk Free Rate First Stock Return Second Stock Return First Stock Standard Deviation Second Stock Standard Deviation Stock Correlation Target Return 6.0000% 7.0000% 10.0000%, 30000910 7.5000% Step 2- The Risky Portfolio As output you need to find the create an output range that looks something like: Risky Portfolio Statistics Risky Weight 1 Risky Weight 2 Expected Return Variance Standard Deviation Sharpe Ratio 4.7059% 95.2941% 9.8588% 3.7181% 19.2823% 20.0122% You will need to run the solver to maximize the Sharpe ratio by changing the first stock's risky weight. The second stock's weight should be the formula 1-weight1. As the user of the spreadsheet needs to use the solver to find the maximum Sharpe ratio you need to give them instructions on how to do so. I use the explanatory text style to give instructions as seen below Portfolio Information Risk Free Rate First Stock Return Second Stock Return First Stock Standard Deviation Second Stock Standard Deviation Stock Correlation Target Return After setting up your inputs you need to run the solver to maximize the Sharpe 10.0000%) Ratio by changing the weight of the first 15.000096 stock in the risky portfolio. The solver can be run by selecting the Data ribbon and clicking the solver button. You will need to press the Solve button as the solver is 7.0000% 30.0000% already set up. 7.5000% If you have already run your solver then when you save the spreadsheet it stores the solver setup which means that I can change the data and run the solver without much work. You should ensure that this works by saving the spreadsheet, closing it, opening it and running the solver. Step 3- The Minimum Variance Portfolio In this step you are going to create a minimum variance portfolio that matches the target return. You know that the minimum variance portfolio consists of some proportion of the risk-free asset and some proportion of the risky portfolio you just computed. You can solve for the weight of the risk-free asset using the portfolio return equation Rportfolio wR(1- w)R2 Step 4 The Chart The chart is going to have standard deviation on the x-axis and expected return on the y-axis Create a separate tab for your chart calculations. On the chart should be: The efficient frontier. This should be the same as your last excel assignment. The capital markets line. As it is a straight line you can create it using two points. Include 100% invested in the risk-free investment (0% in the risky portfolio) as one point and- 100% invested in the risk-free investment (200% in the risky portfolio). Use the X-Y scatterplot as your chart type but connect the data points with lines. On the chart you want to include the tangency portfolio and the target portfolio. To do this as two data series to the chart containing a single point. You will want to increase the marker size and possibly change its color to make it stand out. You will also want a data label for both to identify them correctly. You will want a chart legend as well but will want to delete the legend for the tangency portfolio and target portfolio. The chart should look something like this Make sure you have included a sensible title and axis titles. Two Stock Portfolio - Efficient Frontier and Capital Markets Line 16.0000% 14.0000% 12.0000% Tangency Port 10.0000% Target Portfolio 80000% 6.0000% 4.0000% 2.0000% 0.0000% 45.0000% 0.0000% 5.0000% 10.0000% 15.0000% 20.0000% 25.0000% 300000% 35.0000% 40.0000% Standard Deviation of Returns Efficient FrontierCapital Market Line Description Your boss was pleased with your last project and now wants you to perform a more extensive analysis by considering the Sharpe Ratio and is looking for a spreadsheet in which she can enter return information about two stocks. She then wants to see information about the portfolio of stocks which has the highest Sharpe ratio, she wants to know what proportion of risky portfolio and risk-free asset is required to achieve a given return, and she wants a chart that includes: The efficient frontier The capital markets line The tangency portfolio highlighted and A portfolio with a specific return highlighted. Step 1-Set up the Inputs In a new spreadsheet name a tab Two Stock Portfolio. All input and output on the spreadsheet should be on this tab. The key inputs are: Risk-free rate. The return on a risk-free investment. In this US this is usually assumed to be US Treasuries First and second stock expected returns First and second stock standard deviation of the return:s The correlation between the two stock returns Target Return. This is the return for the target portfolio. It should use data validation to restrict its input to be greater than or equal to the risk-free return You may want to name these inputs so that your formulas are easier to read. The input range should look something like: Portfolio Information Risk Free Rate First Stock Return Second Stock Return First Stock Standard Deviation Second Stock Standard Deviation Stock Correlation Target Return 6.0000% 7.0000% 10.0000%, 30000910 7.5000% Step 2- The Risky Portfolio As output you need to find the create an output range that looks something like: Risky Portfolio Statistics Risky Weight 1 Risky Weight 2 Expected Return Variance Standard Deviation Sharpe Ratio 4.7059% 95.2941% 9.8588% 3.7181% 19.2823% 20.0122% You will need to run the solver to maximize the Sharpe ratio by changing the first stock's risky weight. The second stock's weight should be the formula 1-weight1. As the user of the spreadsheet needs to use the solver to find the maximum Sharpe ratio you need to give them instructions on how to do so. I use the explanatory text style to give instructions as seen below Portfolio Information Risk Free Rate First Stock Return Second Stock Return First Stock Standard Deviation Second Stock Standard Deviation Stock Correlation Target Return After setting up your inputs you need to run the solver to maximize the Sharpe 10.0000%) Ratio by changing the weight of the first 15.000096 stock in the risky portfolio. The solver can be run by selecting the Data ribbon and clicking the solver button. You will need to press the Solve button as the solver is 7.0000% 30.0000% already set up. 7.5000% If you have already run your solver then when you save the spreadsheet it stores the solver setup which means that I can change the data and run the solver without much work. You should ensure that this works by saving the spreadsheet, closing it, opening it and running the solver. Step 3- The Minimum Variance Portfolio In this step you are going to create a minimum variance portfolio that matches the target return. You know that the minimum variance portfolio consists of some proportion of the risk-free asset and some proportion of the risky portfolio you just computed. You can solve for the weight of the risk-free asset using the portfolio return equation Rportfolio wR(1- w)R2 Step 4 The Chart The chart is going to have standard deviation on the x-axis and expected return on the y-axis Create a separate tab for your chart calculations. On the chart should be: The efficient frontier. This should be the same as your last excel assignment. The capital markets line. As it is a straight line you can create it using two points. Include 100% invested in the risk-free investment (0% in the risky portfolio) as one point and- 100% invested in the risk-free investment (200% in the risky portfolio). Use the X-Y scatterplot as your chart type but connect the data points with lines. On the chart you want to include the tangency portfolio and the target portfolio. To do this as two data series to the chart containing a single point. You will want to increase the marker size and possibly change its color to make it stand out. You will also want a data label for both to identify them correctly. You will want a chart legend as well but will want to delete the legend for the tangency portfolio and target portfolio. The chart should look something like this Make sure you have included a sensible title and axis titles. Two Stock Portfolio - Efficient Frontier and Capital Markets Line 16.0000% 14.0000% 12.0000% Tangency Port 10.0000% Target Portfolio 80000% 6.0000% 4.0000% 2.0000% 0.0000% 45.0000% 0.0000% 5.0000% 10.0000% 15.0000% 20.0000% 25.0000% 300000% 35.0000% 40.0000% Standard Deviation of Returns Efficient FrontierCapital Market Line
