Welcome to session three of the second week of our course on modeling risk and realities. In this session, we'll look at optimizing the allocation of a fixed budget among the two stocks, using the expected return as a measure of reward, and the standard deviation of the return as a measure of risk. We'll formulate an algebraic model, implement it in Excel, and investigate how the optimal budget allocation depends on the risk tolerance of the decision maker. In the first two sessions of this week, we have looked at how to use scenarios to model uncertainty and how to calculate the reward and risk measures associated with the probability distribution described by scenarios. We have also discussed the notion of correlation between random variables, and in particular, the connection between correlation and the reduction of risk. In the last session of this week, we'll put these concepts together in our search for the optimal decisions in uncertain settings. In the last session, we have looked at simple examples of portfolios of two stocks. Now, we will look at a related problem in a more systematic fashion. Similar to the last session's example, consider an investor that would like to design an optimal way of allocating a sum of $100,000 today among two hypothetical stocks, A and B, with a goal of maximizing the expected profit from this investment tomorrow. In our analysis, we will use 20 scenarios for the possible values of pairs of returns on stocks A and B. In particular, we will assume that all scenarios are equally likely so that each scenario will be realized tomorrow with a probability of 1/20 or 5%. The return scenarios are stored in the Excel file Two Stocks, posted on course site. Let's have a look. We're looking at the sheet called Scenarios of the file Two Stocks. The sheet contains the data for the return scenarios, a number of calculations, as well as the set up for the portfolio optimization model. We will look at the model later. Now, we will look at the scenarios data and check how the expected values and the standard deviations for the returns on two stocks, as well as the correlation between the returns, are calculated. Cells B5 through D24, Contain the return values and the probabilities for each of 20 scenarios. For example, under Scenario 1, the return on Stock A in shown in cell B5, the return on Stock B in the cell C5, and the probability for the scenario, 0.05, is stored in the cell D5. In our analysis, we will use equal probability scenarios, but all the formulas we put in the cells and use to calculate various quantities are general, and can be used for any set of portfolio probabilities as long as those probabilities add up to one. In the cells, B26 through C28, we calculate the expected values, the variances, and standard deviations for the returns on two stocks. For example, the cell B26 contains the formula that calculates the expected return on Stock A. This formula is the sum product of the scenario probability values from the cells D5 through D24 and the corresponding return values for Stock A from the cells B5 through B24. As you can see, the value of the expected return for Stock A is about 0.35%. So B27 calculates the variance of the returns on Stock A. The formula in this cell is the sum product of scenario probability values from the cells D5 through D24 and the squared differences between the scenario return values and the expected return. With those squared differences, or squared deviations, calculated in the cells F5 through F24. Looking, for example, at the cell F5. We see that indeed it calculates the square of the difference between the return on Stock A in Scenario 1 and the expected value of the return on Stock A. Cell B28 contains the standard deviation value for the returns on Stock A, which is the square root of the variance calculated in the cell B27. In a similar way, the expected return, the variance and the standard deviation of the returns for Stock B are calculated in the cells C26 through C28. Finally, the correlation between the returns in Stock A and Stock B is calculated in the cell C31. Note that the formula in the cell C31 uses the expected value of the product of the returns calculated in the cell C30. So here's the summary of the calculated values. As you can see, at the first look, Stock B seems like an unattractive choice. It has lower expected return and higher standard deviations of the returns. But there is hope, and the hope is that the Stock B can still be a useful addition to the portfolio since it is negatively correlated with Stock A. So let's express our model using algebra. We need to specify three components of the model, decisions, objective, and constraints. Let's start with decisions. We need to decide how much to invest into each of the two stocks, so we have two decision variables, XA and XB. What about the objective? Well, the investor would like to put money in today to get as much profit as possible tomorrow. But the profit tomorrow is random since the returns on our stocks are random. So we'll look at the measures of reward and risk. In particular, we will use expected profit as a reward, and the standard deviation of the profit as a measure of risk to be controlled. In our modelling, we will use several different values of the risk tolerance level to see how they affect the results. So to put it all together, we want to select the investment amounts to maximize the expected profit, while making sure that the standard deviation of the portfolio does not exceed the risk tolerance level selected by the investor. And that the investment amounts add up to $100,000, and that the investment amounts are a non-negative. In this context, the non-negativity constraints mean that we do not allow shorting the stocks in our model. We will use Solver to find the best portfolio. So let's go back to the Two Stocks Excel file to see how our model is set up. So here's the section of the Excel file Two Stocks that illustrates the setup of our model in the spreadsheet format. First, we will look at the decision variables. Cells F31 and G31 contain the values for the investment amounts into stocks A and B, respectively. Next, the objective function. In our model, the objective to be maximized is the expected profit value. Cell G26 contains the formula that calculates the expected profit for any combination of decision variables, that is, for any portfolio containing stocks A and B. The expected profit value in the cell G26 is calculated as a sum product of the scenario probabilities from the cells D5 through D24 and the profit values that the portfolio with investment amounts from the decision variables F31 and G31 earns in each scenario. Those profit values are stored in the cells J5 through J24. For example, cell J5, Calculates the profit earned in Scenario 1 by the portfolio expressed by cells F31 and G31. This profit is a sum product of the returns of stocks A and B in Scenario 1 and the investment amounts in the cells F31 and G31. Profit portfolio values in the cells G5 through G24 are also used to calculate the variance, as well as the standard deviation of the profit value. The variance of the profit is stored in the cell G27. The variance is calculated as a sum product of the scenario probabilities from the cells D5 through D24, and the squared deviations in scenario profit values from the expected profit. Those values are stored in the cells K5 through K24. The standard deviation of profit is calculated in the cell G28, by taking square root of the variance in the cell G27. So we have the decision variable cells and objective function cell. Now we need to specify the constraints. The first constraint limits the value of the standard deviation of the profit as the risk measure. In particular, we will instruct the Solver to make sure that the value of the profit standard deviation does not exceed the selected level. We have put in the trial values of 50,000 into each of the decision variable cells. For such portfolio, the standard deviation of the profit is calculated in the cell G27 and is equal to something about $1,446. Suppose that the investor would like to limit the standard deviation to a lower value, say $1,400. We put this limit in the cell I28 and we will tell the Solver to make sure that the value of the standard deviation of the profit in the cell G28 does not exceed the limit in I28. The next constraint must ensure that the sum of the investment amounts for the two stocks is $100,000. In the cell I31, we add the values of two investment amounts, And we will equate it to the total investment budget in the cell K31. Now we're ready to find the best portfolio. We call the Solver, Data > Solver. We make sure that the decision variable's objective function and constraints are all specified, including non-negativity constraints. And click on Solve. The optimal portfolio, in this case, allocates about 80% of the total investment to Stock A and about 20% to Stock B. Using this spreadsheet model implementation, we can optimize our two stock portfolio for different values of investor's risk tolerance. We have used the risk tolerance values between around $1,310, which is the lowest possible standard deviation value that can be achieved in a portfolio comprised of these two stocks, to about $1,900, that's where 100% of the portfolio is allocated to the stock with the highest expected return, Stock A. For each risk tolerance value plotted on the horizontal axis, we have used Solver to find the optimal portfolio and the corresponding expected profit value, plotted on the vertical axis. Finally, we have investigated how the optimal portfolio composition changes as the risk tolerance level increases. As we can see for lower values of risk, the optimal portfolio requires a substantial presence of Stock B. As the risk requirement is relaxed, portfolio gradually shifts towards Stock A, with stock A's fraction reaching 100% when the standard deviation of the profit value is allowed to be as high as approximately $1,850. Week two of our course is behind us. This week, we have looked at how scenario approach to modeling future and certain outcomes can be combined with optimization techniques in search for the best decision. Next week, you will have an opportunity to look at other methods of using historical data for modeling future outcomes.
