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.