0:00

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.

Â 1:33

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.

Â 2:34

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.

Â 4:24

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.

Â 5:13

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.

Â 5:36

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.

Â 6:37

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.

Â 7:11

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.

Â 9:00

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.

Â 9:36

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.

Â 10:06

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.

Â 10:49

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

Â 11:33

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.

Â 12:39

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.

Â 14:04

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.

Â 15:05

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.

Â 16:07

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.

Â 19:13

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.

Â 20:03

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.

Â 20:39

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.

Â 21:41

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.

Â 23:39

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.

Â