Hi, I'm Noah Gantz, and we're starting session two of Week 4 in our operations analytics course. In Session 1, we introduce decision trees as a method of analyzing ideas choice of a supplier for it's Krusbar tent. The demand model we used in the example was simple, however and we'd like to enhance it to capture a more complex pattern of demand. An extended demand model will be difficult to evaluate using only a decision tree. But it will be easy to formulate and evaluate using the simulation tool that Sir introduced in Week 3. Welcome to Week 4 session 2. In this session we'll use simulation as a tool to compliment decision trees, and we'll introduce how they're used in the context of idea supplier selection problem. To get started let's remember what ideas selection to problem was. There were two possible suppliers one supplier S from Sweden and another supplier P from Poland. Supplier S had 5,000 units of capacity, and if IDEA worked a contract with supplier S, it would order 5,000 units. The fixed up front charge from supplier S was 0, and the unit cost per tent from supplier S was 120 euros. If IDEA were to contract with supplier P, it would have to order 10,000 units. Supplier P would charge IDEA a 50,000 euro upfront charge. And the unit cost from supplier P would be 100 euros. Remember also that at the 150 euro selling price, IDEA had a two point demand distribution. There'd be a 50% chance that demand would be strong, in which case it could sell 10,000 units. And there would be a 50 % chance that demand would be weak, in which case IDEA could only sell 5,000 units. But remember, demand may be more complex, and we said that we would introduce a more complex demand model. That's exactly what we're gonna do right now. Let's assume, for example, that demand is uniformly distributed. What does that mean? Well a uniform distribution is defined by a minimum outcome and a maximum outcome. Any outcome in between the given minimum and maximum is equally likely. And I am gonna show you that in the moment. If you're interested in uniform distributions or any other kind of distribution such as a normal distribution, you can look it up in Analytic Text or Statistics book. One that we have is by J.R. Evans. Here's the more complex demand model. As before, there will be a 50 -50% chance that demand is weak or strong. If demand is weak, then it'll be uniformly distributed between 2,000 and 8,000 units. And you can see the chart at the upper right of the screen shows that uniform distribution. If you look at the horizontal axis, you can see that the bar stretches from the 2000 unit minimum to the 8000 unit maximum. And if you look at the height of the bar, you can see it's completely even. The height of the bar represents how likely it is, when we sample from the distribution, to get a demand with that number. And because the height is even all the way across, that means that it's equally likely that any number between 2,000 and 8,000 units will be selected. On the other hand, if demand is strong, it's also uniformly distributed, but this time with a different range. If demand's strong it'll be uniformly distributed between 6,000 and 14,000 units. And again in the chart at the lower right of this screen you can see a uniform distribution that stretches from a 6,000 unit minimum to a 14,000 unit maximum. Again the height of the uniform distribution is even all the way across and that means that when we sample from it, it will be equally likely to get any value between 6000 and 14,000 units. How can we represent this in a tree? Well, let's take a look. Consider the choice of supplier S. What happens if IDEA chooses supplier S and the market is weak? Well first of all IDEA decides to contract with supplier S, and it pays a fixed cost of 0. Remember supplier S charges IDEA a 0 fixed cost. Then we find out that the market is weak. And that happens with probability 0.5, or a 50% chance. In this case IDEA's going to order 5,000 units from Supplier S. And this time, rather than writing the number 5,000 we're gonna call the order quantity Q. That's gonna help us later on when we look at Supplier P. If IDEA orders from Supplier S, it's going to pay 120 euros per unit times Q units for a total unicost of 120 euros times Q. Remember the demand is uniformly distributed anywhere between 2,000 and 8,000 units when the market is weak. I'm gonna call the demand, which we don't know ahead of time, D, so that we can make a general calculation. So pictorially, we have another event given that the market is weak, and that's what the demand outcome is. If we try to draw this on a decision tree, you'd see there'd be many, many, many branches from 2,000 all the way up to 8,000 units. And that can quickly become unmanageable. So instead, we're going to use simulation. And here's how we're gonna do it. We're going to say if demand is less than the quantity ordered, well then we know the revenue is going to be 150 euros per unit times the demand. So, for example, if IDEA ordered 5000 and demand was 2000, IDEA would only earn revenue on 2000 units the demand. On the other hand if demand were greater than Q then the revenue would be 150 euros per unit times the order quantity Q. Again if IDEA ordered 5000 and demand were 7000, IDEA would only earn revenue on the 5000 units that it ordered. We can put those two equations together and write a general equation that's good for any D. For any D, we can say that idea earns a revenue of 150 euros per unit, times the quantity sold, which is just the minimum of D, the demand, and Q, the order quantity. And there's a nice little Excel function that will do that for us. Now that we've defined all of the cash flows associated with the fixed cost, the unit order cost, and the revenues, we can define the outcomes for IDEA when it orders from Supplier S and the market is weak. We're going to add them together, just as we always do, to get a general formula for the profit. And you can see that at the bottom of the screen as well. Profit equals -120 euros times the order quantity, that's the cost. Plus 150 euros times the minimum of demand and Q, that's the quantity sold. In our case, we're gonna set Q to be 5000 for supplier S, and then we're going to simulate D as a uniform distribution to estimate IDEAS expected profit. And, you know how to simulate. You saw that last week when you worked with. So, let's build a spreadsheet that will let us simulate, IDEAS average profits, when it orders from supplier S and the market is weak. I've laid out a template with the labels shown, so we can just fill it in with numbers and formulas. We'll start at the top. If IDEA orders from supplier S, the order quantity is 5,000. If IDEA orders from supplier S, it pays no fixed cost. Whenever IDEA sells tenths The unit revenue is 150 Euros and the unit cost from supplier S is 120 Euros. The next data we need to fill in are the demand samples. And you'll remember, from last week, that we can use Excel's random number generator to generate them. We'll go up to the Data tab. And then, the data analysis menu item. And finally, random number generator. And click OK. That might be small, but I'll show in a moment a bigger picture of it. What we're going to do is we're going to tell the random number generator that we'd like to generate samples from one random variable. We'd like to generate 10 samples. We'd like the random variable to be uniformly distributed, with a minimum of 2,000 units, and a maximum of 8,000 units. We'll use the random seed 1234 to generate the random numbers. Remember if you were to use exactly the same software I'm using, the same version of Excel, the same operating system, and you typed in 1234, you would get the same set of random numbers. But if you don't get exactly the same set don't worry, it could be just a subtle difference in your software. The last thing that we'll do is we'll select on output range for Excel to put the random numbers. And to do that we're going to select the cell that is at the upper left of the range, that's cell B10. We click OK. And we've generated ten random numbers. I'll come back to those in a minute, but first I wanted to show you that random number generator dialog box. Here's a bigger picture. 1 Random Variable, 10 Samples, Uniform Distribution, Lower Bound of 2000, an Upper Bound of 8000, Random Seed of 1234, top of the Output Range is Cell B10. Okay. Let's go back to the Excel output. And I wanna point out that the random numbers that Excel has generated in column B are fractional quantities. That's the definition of the uniform distribution. It will generate random numbers. Equally likely anywhere between 2000 and 8000, including fractional quantities. Now, if you wanted to use a different random variable that perhaps took on only integer quantities, you could do that. But, for now for convenience, we're going to stick with the uniform distribution. And, I'll come back to that a little bit later when we get back to our PowerPoint slides. Having done that, let me clean these cells up a little bit. By reducing the number of digits. Okay, so now we have all of the data that we need. We have the order quantity, the fixed cost, the price, the unit cost, and the demand samples. And we can generate, for example, for sample 1, our profits. We'll calculate the revenue to be the 150 Euro price times the sales, which is just the minimum of the order quantity And the actual demand. We can calculate the fixed cost to be 0 in this case. We can calculate the variable cost to be the unit price of 120 times the quantity ordered or 5000. And the profit is simply the revenue minus the fixed cost, minus the variable cost. Lets take a look at our results for the first sample. In the first sample, the demand was for about 2,745 units. And the revenue is about 411,700 Euros. That's because the demand in this case is lower than the order quantity of 5,000. So IDEA can't sell more units than are demanded. And the revenue is simply the demand times the price. The fixed cost is 0. And the variable cost is 600,000. And you can see when the demand is low, IDEA loses about 188,000 euros. Having generated the profit for one sample, we can simply copy and paste our formulas to generate the results for the other samples. Now you can see that the fixed cost never changes. The variable cost never changes. It's the revenue that's changing with the demand. When demand is lower than 5,000 units the order quantity, the revenue depends on the demand. When demand is greater than 5,000 units, for example, in this sample 5, then the revenue is driven by the order quantity. Here you can see that the demand is 6222 units and the revenue is 5000 times 150 or 750,000 Euros. Okay. So we've generated ten samples of demand. We've calculated 10 different profits. And now we can calculate the average of those profits. Here we can see if IDEA orders 5,000 units from supplier S, and has these 10 different demand samples. The average profit is actually a loss of about 28,300 Euros. If we're interested, we could also calculate the standard deviation of the profits. And here, you can see it's almost 159,000 Euros. So, what have we done? We had an excel template, and we built a simulation that calculated the averages profits for IDEA from these ten demand samples. Of course, ten demand samples are quite small. And we'd like to be able to generate a larger simulation, that's much bigger than what we can show you for this spreadsheet. But what we've done is built larger simulations and taken screenshots of them. We'll come back to those in the PowerPoint deck. Before we move on, I wanna note a couple of important points on the simulation. Remember that we used Excel's Random Number Generator to generate ten samples of one random variable for demand. The random variable was uniformly distributed from 2,000 to 8,000 units. And we use the random seed 1234. And you can see all of those parameters in the dialogue box for the random number generator, in the upper right of the screen. The second thing I wanna remind you about, is that our samples from the uniform distribution include fractional quantities. That's by definition of the uniform distribution. And for simplicity, we've used it in our example. There are other distributions that ensure that samples are whole numbers, and if you wanna learn more about distributions, you can look at a book on statistics or business analytics, for example, the one we list here. The spreadsheet IDEA.xlsx has several worksheets, each with a different simulation. Okay, let's go back to our simulation results. We can rerun the simulation of supplier S, and a weak market with 1000 samples, to get a better estimate of IDEA's average or expected profits and we've done just that. You can take a look in the Excel file to see the actual spreadsheet. Here, you can see that our estimate of the average profits now 42,405 Euros. You can see that in the lower right of the screen. So, what we've done is we've used simulation to roll back the decision tree for this complex event. Remember, first IDEA decided to contract with supplier S then there was an event that the market was weak. Then there was another event that the actual demand was somewhere between 2,000 units and 8,000 units. And we had a general formula for any given Q, where we plugged in Q of 5,000, and also based on a random D. What we did was simulation is we rolled that last node back and estimated the expected value of that event node as being 42,405 Euros, and those are our simulation results that we can use to compliment the rest of the decision tree. We can do the same thing for the other three important events nodes in IDEAs decision tree. That is supplier S with a strong market, supplier P with a weak market, and supplier P with a strong market. So let's go ahead and take a look and see what those simulations look like. Here are the results for supplier S in a strong market. The spreadsheet looks nearly identical, but you can see the results of the simulation in column B for the demand have numbers that look quite different, because they are uniformly distributed between 6,000 and 10,000. In every single case you can see in column F, the profit is 150,000 euros. And that's because IDEA only could order 5,000 units from supplier S. So no matter what demand was, anywhere between 6,000 and 10,000 units, IDEA would always sell 5,000 units and make a profit of 150,000 euros. The average profits 150,000 and the standard deviation which is the variation around the average is 0 because every single times is 150,000. The next spread sheet I'll show you is when IDEA supplier P and has a weak market. Here we need to look carefully at column B you can see the order quantity is now 10,000 in cell B3, you can see the fixed cost is now 50,000 in cell B4. And you can see the unit cost is now down to 100 in cell B6. The sample demand numbers in the rest of column D are exactly the same 1,000 numbers that we had before for supplier S in a weak market. Because we've used the same random seed, one two three four. Again the rest of the spreadsheet's exactly the same, and with those demand numbers we can calculate another 1,000 profits and calculate the average and we see that the average profit If IDEA chooses supplier P and the market is weak. The estimated profit for IDEA if it chooses supplier P and the market is weak is 293,391 Euros of loss and the standard deviation is also quite large. The last simulation I'll show you is when IDEA chooses supplier P and the market is strong. Again, the order quantity is 10,000, the fixed cost is 50,000, and the unit cost is 100 Euros per unit. Then demand samples, which you can see in the rest of column B are exactly the same as those when IDEA chose supplier s and the market was strong because we've used the same random seed. 1,2,3,4. Again, if we look in column F we have a 1,000 profits, and when we calculate the average we see our estimate of the expected profits for IDEA when it chooses supplier S and the market is strong, is around 306,500 Euros. So now we've got four estimates of expected values. And let's take a look at them in the decision tree. Here's what it looks like. Remember, looking from the left to the right, the first node is a decision node for IDEA. The first choice that IDEA could make would be to contract with no one, in which case its average profits would be 0. The second choice would be for IDEA to contract with supplier S. In that case there are two market outcomes. There'd be a weak market and a strong market, each of course of probability .5. From the simulation we can see that we estimated IDEA's expected profit for a weak market for supplier S as 42,405 Euros. We also see that IDEA's expected profit with a strong market in supplier s are 150,000 Euros. Finally if IDEA chooses supplier P and the market is weak our estimate of ideas expected profit from this simulation is -293,391 Euros. Whereas, if IDEA chooses Supplier P and the market is strong, our estimate of the expected profit is 306,540 Euros. Now we've got a decision tree in the familiar form. And we can analyze the expected value just as we always have. First we'll analyze Supplier S. Here we can calculate the expected value of choosing supplier S as 0.5 times the outcome when the market is weak, plus 0.5 times the outcome when the market is strong. Or an expected value of around 96,200 Euros. At the bottom we can calculate the expected value of contracting with supplier P. Again, we use the familiar calculation of multiplying the probabilities with the values given those outcomes and we can calculate the expected value to be around 6,575 Euros. We'll substitute those expected values for the event notes. Finally we're back to deciding with whom IDEA should contract. And if we compare the three decision notes, we can see contracting with no one brings an expected value of 0, contracting with Supplier S brings in an estimated expected value of around 96,000 euros, and contracting with Supplier P brings in an estimated expected value of about 6,575 Euros. Contracting with supplier S clearly maximizes the IDEA's expected value. We're ging to choose supplier S. We see though that when we choose supplier S expected profits drop by about a third from the demand model that was much simpler, that had only 5,000 units or 10,000 units demand. Before IDEA's expected profits were 150,000, and now they're only around 96,000 Euros. Even more dramatic are IDEA's expected profits for contracting with supplier P, they've dropped by more than 90% and the simpler demand model, the expected value was 75,000 Euros and now the expected value is around 6,600 Euros. To maximize expected profit IDEA would still contract with supplier S. So that's it for session two of week four. How did IDEA's problem change from last session to this one? Well, a lot of the overall problem structure did not change. First, IDEA had to decide on a supplier, either supplier S, supplier P, or no one. The fixed costs and the order quantities were the same as before. Then IDEA saw if the market were weak or strong, the same 50/50 probabilities as before. What did change were the outcomes for the weak and strong markets. They became more complex. In the initial model, when the outcome was weak or strong, we just got numbers of 5,000 or 10,000, and in either case we could simply calculate IDEA's profits. In the new demand model, which is more complex. If demand were weak it would be uniformly distributed, and if demand were strong it would be uniformly distributed. In either case, demand was still random and we needed to use simulation to estimate IDEA's expected profits. Simulation let us evaluate the outcomes of this more complex event. In the next session, you can see how optimization in a complementary way to help evaluate more complex decisions. In this session, we added some nuance to the model of demand for tent. Even after learning whether the market would be strong or weak, there remains some uncertainty about the exact number of tents that could be sold. We used uniform distributions to model that residual uncertainty. And at the event nodes with these uniform distributions we used simulation to estimate expected values. So simulation was helpful to evaluate event nodes whose distribution were more complex. Next time we'll see how we can use optimization to evaluate complex decision nodes that have many potential choices.
