Calculate and visualize expected profit - Microsoft Excel Tutorial

- [Instructor] In the previous movie, we calculated the expected profit or loss for a number of order quantity and demand scenarios. We're assuming that we're buying t-shirts for a conference and we will only be able to sell them at the conference. If we don't sell them then, we won't be able to get any money for them. And this movie is calculate the total expected profit or loss for each of our order quantity scenarios. My sample file is 03_04_CalculateProfits. And you can find that in the chapter three folder of the exercise files collection. To calculate the expected profit for an order quantity scenario, which again are in the columns, starting in column D, we need to add up the expected values for each individual demand scenario. So for example, at a demand level of 200, which has a .0038 probability of happening, or .38%, that demand level, we would lose a total of $3,500, but the probability is only .38%. So we would multiply .0038 by minus 3,500 to get the expected value. We would then do the same for the 400 level, 600, and so on. The sum of all of those expected values is the total expected profit or loss for that order quantity. To calculate that value, we'll use a sum product function. So I'll click in cell D21, type an equal sign. And the function we'll use is SUMPRODUCT. And this multiplies a couple of arrays together, element wise. So element one of one array, such as C7, by the first element in the second array, in this case D7, and then adds all of the individual multiplications or products together. So we will select as our first array, sells C7 through C20. I don't want that to change, the probabilities will stay the same for each demand scenario. So I'll press F4, then a comma, and then the array will multiply it by, for column D is D7 through D20. I'll leave those references relative so that they can change when we copy the formula over. Type array parentheses and press tab, and we get the total of 12,163. Now click cell D21 and drag over to the right. And I got some interesting formatting with borders, which I can clean them up in a second, but I see that the largest total is in cell I21, 19,176. I'll fix the borders around my range D21 through M21. So I'll select that range. And then I'll go up to the borders tool, click its down arrow, I'm on the home tab of the ribbon, and click no border to get rid of those. And then I'll click outside borders, and we can see that the values are now easy to read. These are the values that we're focusing our decision on. So I will change the fill color and the range D21 through M21. So I have selected those cells. And then on the home tab of the ribbon, I'll click the fill color controls down arrow, and I'll select the light green, just so it stands out a little bit. And now the cells stand out and we'll note that our optimum order level is 2000 with a maximum value of 19,176. However, note that our expected profit for buying 2200 shirts is only, it looks like $22 less. So if you're willing to invest in 200 extra shirts, you might make 200 people that much happier when they come to your conference. If you would like to see how this relationship appears graphically, you can create a chart. To do that, go ahead and select the order quantities. And those are in cells D5 through M5. Then hold down the control key and select the total profits. And those are in D21 through M21. And then on the insert tab of the ribbon, go to the charts group, click the "Insert Scatter (X, Y) or Bubble Chart" button, and select one of the scatter chart types. I prefer the second, which has smooth lines and markers. I'll go ahead and click that, and you see the chart. I won't format the chart, but I will make it a little bit larger, so it's easier to see, and you can see the total expected profit, which is on the vertical axis, changes in relation to the number of items that you're buying, starting with 1000 and going up to 2800. And it looks like the maximum is right around 2000. So we have a maximum of 19,176, and to the right 2200, that's 19,154. So once again, as you can see, here visually, and also in the data table, that there's not a lot of difference in expected profit between 2000 and 2200 shirts.