Hi, I'm Sergey Seven. Welcome to session three, the last session of the first week of our course. In this session, we'll discuss the notions of risk and reward. We will look at an investment problem that chooses a simple score to describe the risk associated with alternative investment vehicles. The simple treatment of risk will prepare us for dealing with more complex risk measures in the coming weeks. So far in Week 1, we have looked at the general structure of optimization models, and at how the spreadsheet versions of such models can be set up and solved. One important assumption we have relied upon so far is the assumption that for the decision maker, there's no uncertainty around the future impact of any potential action. In this last session of Week 1, we will largely continue to rely on this assumption, but we will also start looking at the notions of reward and risk. In particular, we will look at an investment example where we use a simple risk score to mimic the future uncertainly. This example will prepare us for a more systematic discussion of uncertainty and risk in the future weeks. So, here is an example we're going to use in the session. We're looking at a financial company that needs to allocate $125 million among 4 groups of investment vehicles for the next year. One characteristic of these groups of financial products that guides company's' decision is the expected annual return values, with government bonds having the lowest values, and consumer loans the highest. Another characteristic is a quality score that a company uses internally to describe the quality quote unquote of a particular group of financial products. The quality score communicates the likelihood that the actual returns next year, will deviate from the expected values in an undesirable way because of, say, a default. So here are the quality scores for the groups of financial products the company considers. Note that the higher is the quality of the product, the lower is the expected return. Expected returns and quality scores are not the only factors that influence the investment decision that the company faces. Here are the additional requirements that the investment portfolio must meet. First, the entire investment budget must be used. Second, the average quality of the portfolio must be at least 2.5. Third, there are minimum and maximum amounts that can be allocated to any particular group of financial products. Finally, government bonds must account for at least 25% of the overall bond component of the portfolio. Okay, as usual, we start by creating an algebraic model that expresses the problem in a structured form, using decision variables, objective function, and constraints. Decision variables in this example are investment amounts for each group of financial products. The objective that the company wants to maximize is the expected return on its investment over the next year. And the requirements become a set of constraints. Let's start with the decisions variables. We have four product groups, so we have four decision variables, how much to invest in government bonds, municipal bonds, corporate bonds, and consumer loans. Now, the objective function. In order to calculate the total return on investment described by any combination of decision variables, we should multiply each decision by the corresponding return value and add the results. Finally, the constraints. The first is the budget constraint. In this case, we must invest the entire amount. So the way to express this constraint is to equate the sum of the decision variables to the investment amount. The next constraint, limits from below, the average quality score of the investment portfolio. We express this constraint in algebraic form, by requiring that the sum of scores of the products participate in the portfolio, weighted by the fraction of the total investment allocated to each group is at least 2.5. Now, the minimum and the maximum investment constraints. On the maximum side, no group can get more than 50% of 125 million, which is 62.5 million. So here's how the minimum and the maximum investment constraints can be expressed. They're simply the lower and the upper bounds on the values of our decisions variables. Finally, the constraint on the minimum fraction of government bonds. The way we express it is by calculating the fraction that the investment in government bonds makes in the total bond investment and, requiring that this fraction is at least 25 percent. So here's our complete model. Decision variables, objective function, and constraint. Note that we have added as usual non-negativity constraint on investment amounts. Next, let's go to the Excel template we will use to set up and solve this model. So this is our Excel template epsilon, delta, capital underscore zero posted on the course site. As usual, we need to specify to the Excel and to the solver where our decisions variables are, where our objective function is, and what constraints we are facing. We start with the decision variables. We have four decision variables. We're presenting the investment amounts for four financial product groups. On the spreadsheet, I will use the cells B14 through E14 for this variables. For example, cell B14 will represent the investment amount in government bonds. I will put some initial values into the cells. For example, I'm going to invest 50 million in the government bonds and 25 million into each of other product groups. I will also change the color and the font in the decision variable cells to make them stand out. Now it is time for the objective function. The objective function we're maximizing in this case is the total expected return of the portfolio. On the spreadsheet, the objective function value would be placed in the cell G6. So we need to be able to calculate the objective function value for any combination of decision variables. The total expected return for the investments reflected in the cells B14 through E14 is given by the sum product of the investment amounts and their returns expressed as percentages. So let's this formula in G6, SUMPRODUCT of our investment amounts and expected returns, and let's not forget that those expected returns are percentages. Okay, again, as before, we should make objective function bold and red. Now the constraints. First, the investment amount. We will use the cell G14 to calculate the sum of investment amounts. And in E14, we have an investment budget that we have to allocate. The next constraint we want to put in is the constraint on the average quality of the portfolio. What we're going to do first is we calculate the fractions of the total investment amount that we'll put into each product group. We're going to put the values of those fractions into the cells B16 through E16. For example, in B16 we're calculating the fraction that goes into the government bonds. That's B14, currently 50 million, divided by I14, which has 125 million. And we would like to actually enter the address of the cell I14 because we'd like to copy and paste this formula into the other cells. So changing the addresses from relative to absolute allows us to instruct the Excel not to change the address of the cell when the formula is copied into other cells. So if you now copy the formula from B16 into C, D, and E16, we're going to get the correct formulas. Let's check. For example, this is the formula in the cell E16. That's 25 million divided by 125 million. That's 20% as a fraction. Okay. Let's just return. The borders to their past state. Now we can use the investment fractions to calculate the average portfolio quality. We'll put this calculation into the cell B18. So what we need to do is we need to calculate the sum product of the quality's course weighted by the investment fractions. Let's have a look at the formula. So the sum product of the investment fractions and the quality score of each investment. Now we want to make sure that the average portfolio quality is greater or equal to 2.5. Okay, finally, we need to make sure that the fraction allocated to government bonds is at least 25 percent of the entire sum invested in government, municipal, and corporate bonds. So, the way we do it, is we just calculate the fraction directly here. So we divide the government bond amount by the sum of three bond investments. Okay, we're ready to call solver. Data, solver, three elements of the decision model. The objective function, that's our G6. I want to maximize the expected return. Our decision variables are in the cells B14 through E14. The constraints are we start with the total investment budget constraint, G14 = I14. Then, we move to the risk score requirement. The average risk score has to be at least 2.5. And finally, the fraction allocated to government bonds must be at least 25% of all allocations to bond financial products. Of course, we should not forget the minimum and maximum investment amounts. On the minimum side, we force all four decision variables to be greater or equal than the minimum value which is 20 million. And on the maximum fraction, we can just write it like this. So the fraction for each investment group cannot be more than 50%. That's slightly different from how we express this constraint in the slides, but the optimal solution, of course, will be exactly the same because this is an equivalent way of writing this constraint. Okay, so now we click Solve The solver tells us that solution has been found. And here it is. Here again is the optimal solution. In the previous session, we have talked about how important it is to not limit the use of solver to simply calculating the optimal decision for a particular combination of problem parameters. It is useful to also investigate how the optimal solution changes with the values of problem parameters. Often this can generate import insights into the nature of your model. For example, what if the company's willing to tolerate lower quality of the portfolio, and therefore, higher risk? How much more in terms of the expected return can the company obtain? Here's what happens if we resolve our model for the values of the required portfolio quality ranging from 2 to 3.5. Here we've plotted the optimal expected return as a function of the minimum portfolio quality. These results are stored in a sheet called Analysis in the epsilon delta capital solution file. If you look at these results, you see the trade-off between what we can call risk, that's the opposite of portfolio quality, and reward that is expected return. And the trade-off is that if the company wants higher reward, it should be willing to tolerate higher degree of risk. The blue points on the graph depict the best possible level of reward that can be achieved for a given level of risk. In a similar way, a composition of the optimal portfolio is influenced by the company's risk tolerance level. Across a wide range of minimum portfolio quality levels, two of the four product groups, municipal and corporate bonds, do not really offer attractive combination of risk and reward features for this company. Government bonds and consumer loans dominate the portfolio composition, and as the risk tolerance increases so does the fraction of consumer loans in the optimal portfolio. We have completed the first week of our course. So far, we have looked at the optimization approach designed to find the best way forward in business environments with low levels of uncertainty. As the level of control that a decisionmaker exercises in his or her environment decreases, the need to explicitly manage potential exposure to risk becomes more and more prominent. In week two, we'll take a closer look at a particular way of modeling future uncertainty, called scenario approach. And we'll see how the optimization tool kit can be adapted to making decisions in highly uncertain settings. See you next week.
