0:05

All right, so

Â the problem that we're presented with today is an inventory management problem.

Â And if you look at the Word document that's posted on the course website,

Â part one, the business problem,

Â you'll see that we have data provided by Bob's Books and Beyond.

Â Some historic data on the level of demand that they've experienced selling

Â calendars.

Â The context of this particular problem, a bookstore sells calendars,

Â or purchases calendars, for $7.50 per unit, sells them for

Â $10.00, makes a profit of $2.50 for each calendar.

Â But if they don't sell those calendars,

Â they can return them and get a credit of $2.50.

Â So the challenge that we have in this case is we, as the retailer,

Â want to figure out how many calendars should

Â we order based on what we expect the level of demand to be.

Â Now, as you'll see on the worksheet as well as the Word document,

Â we have predicted levels of demands.

Â Let me highlight that for you in cells F2 through G7,

Â that's the historic level of demand.

Â So you'll notice that there's a 0.3 or 30% probability of demand being 100 units,

Â a 20% probability of demand being 150 units.

Â 30% chance of getting demand of 200 units, 15% chance of demand

Â of 250 units, and 5% chance of getting demand of 300 units.

Â 1:33

Now, that means that demand may be as high as 300 units,

Â be we're not going to know exactly what the level of demand is

Â until after we place the order for the calendars.

Â So we need to make a decision based on the historic level of demand,

Â how many calendars should we order.

Â So the order quantity,

Â that's our decision variable that we're going to enter into cell B7.

Â Now the technique that we're going to be using today is called Monte Carlo

Â simulation or the Monte Carlo method.

Â And what we're going to do is essentially run a number of replications or

Â simulations where we don't know for

Â any particular scenario which of those is going to become true.

Â So if we run 10,000 or a million different scenarios with differing levels of demand,

Â where demand is based on that historic distribution that we looked at previously,

Â in each individual scenario we assume that we know what the level of demand is.

Â But in reality, we don't know which of those million simulations is going to

Â actually come true.

Â So when we take our average, when we take in expectations,

Â we're going to look over all of the different scenarios and look at,

Â in what fraction of the scenarios do we profit under different order conditions?

Â So under different values of our decision variable based on a given level of demand.

Â So we're going to place an order for a specified amount.

Â We're going to simulate out different hypothetical levels of demand

Â that could happen.

Â And we're going to calculate the expected profit across a number of

Â different simulations.

Â Then we can change what the order quantity is.

Â So if we order 200 calendars, well if demand is only 100 units,

Â we've ordered 100 too many calendars.

Â If demand is 300 units, we've ordered too few calendars.

Â So through the Monte Carlo method, what we're doing is creating

Â 3:33

a number of different hypothetical worlds, where we have different levels of demand.

Â We calculate what profit is based on the decision that we've made.

Â And then we can look at, based on that order quantity, what's the ideal

Â order quantity looking across all of those hypothetical outcomes that we might have,

Â all right?

Â So to walk you through the worksheet that we're going to be using, the goal that

Â we're ultimately going to have is based on the order quantity that we input.

Â Whether it's 100 units, 125 units, 150 units, we want to calculate out,

Â what's our expected profit when we order that many calendars?

Â And if we scroll down the worksheet slightly, we're also going to

Â retrieve from that table, what's that optimal order quantity?

Â But I've set up the problem for you, and you see starting in row 26 and

Â 27, that's where we're going to conduct our simulations.

Â So the approach that we're going to take is we're going to simulate

Â 4:34

a random number and use that random number to simulate a particular level of demand.

Â We're going to calculate out the revenue based on the level of demand and

Â the order quantity.

Â How much did it cost us based on the order quantity,

Â what the refund amount is going to be, and what's our total profit going to be?

Â Now that's for one replication.

Â Well, if you scroll down column A, you'll see we're going to do this for

Â a number of different replications.

Â In fact, we're going to do it for 5000 replications.

Â 5:04

So a couple of different Excel features that we're going to be looking at today.

Â We're going to use the random number

Â feature to generate different numbers of demand.

Â We're also going to look at reference commands, vlookup,

Â index, and match, as a means of retrieving a particular level

Â of demand associated with the random number.

Â And we're going to use the one-way data table to conduct what if scenarios.

Â 5:33

All right, so

Â on page two you'll see the general approach that we're going to be taking.

Â So we assume a given order quantity, and that's the one piece that we actually get

Â to control because demand is the random variable,

Â we don't get to control what that's going to be.

Â Now, what we know is, historically,

Â we know what the distribution of demand has looked like.

Â 5:53

We know that there's a 30% chance of a demand being 100 units,

Â a 20% chance of demand being 150 units.

Â And so we're going to use these historic probabilities in terms of

Â simulating different levels of demand.

Â Based on the level of demands in a given simulation,

Â we calculate out the profit based on the demands and the order quantity.

Â We calculate out that profit for

Â each of the 5,000 different scenarios that we're simulating.

Â And then, the expected profit, that's going to be

Â the average profit across those 5,000 different simulations.

Â 6:32

If we jump into the specific steps that we're going to be taking to fill out this

Â worksheet, let's start by inputting a given order quantity,

Â just as a placeholder for the time being.

Â So in cell B7, you're going to put 200 units.

Â And I'm going to follow the steps that are outlined starting on

Â page three of the handout.

Â Now, what we want to do is take the 200 units as our order quantity,

Â and then simulate out a given level of demand.

Â Use the order quantity and the simulated level of demand to figure out,

Â have we ordered the right number, have we ordered too much,

Â have we ordered to little, all right?

Â So we're going to scroll down just to start filling out this table.

Â And you'll see we're first going to use in cell B28,

Â we're going to type =rand().

Â And this is the command to simulate out a random number between 0 and 1.

Â All right, now this is just an intermediary step for us.

Â We're going to use the random number that we've simulated to match that with

Â a particular level of demand.

Â And where that's going to come from is the demand table up top.

Â So if we just scroll back up for a quick second to take a look, you'll notice that

Â I already calculated out a column for us in column E, the cumulative probability.

Â Now what this is Is the cumulative probability of demand being less than

Â a particular level.

Â And so, we never have demand less than 100 units, that's why it's 0.

Â What's the probability of demand being less than 150 units?

Â 8:17

Well, the only possibility is that demand is 100 units, and so it's the 0.3, so 30%.

Â What's the probability of demand being less than 200 units?

Â Well, it's the probability of getting 100 units of demand or 150 units of demand.

Â That adds up to 0.5, and we can continue to populate out this table.

Â The probability of demand being less than 300 units.

Â It's the probabilities associated with demand being 100 units

Â 8:45

through 250 units, and that gives us the 0.95.

Â Now that we have that cumulative probability,

Â what we're going to do is try to find the nearest match,

Â rounding down for the random number that we generate.

Â And so, if we generate a random number between 0 and

Â 0.3, it's going to round down and say, find for me

Â the demand associated with the cumulative probability of 0, it's 100 units.

Â Let's say we simulated a random number that's between 0.3 and 0.5,

Â it's going to round down to 0.3 and match that with demand of 150 units.

Â If it's between 0.5 and 0.8, match that with a demand of 200 units.

Â And the nice thing about this, if we look at this is between 0 and 0.3.

Â Well, all the random numbers that we're drawing are between 0 and

Â 1, they follow a uniform distribution.

Â So what are the chances of a number falling between 0 and 0.3?

Â Well, it's a 30% chance, so

Â there's a 30% chance that we're going to simulate demand of 100 units.

Â The difference between the cumulative probability of 0.3 and 0.5, that's 20%,

Â and that's the probability of getting demand of 150 units.

Â 10:20

So, let's go through the step by step in the demand column in cell C28,

Â it's going to be, we're going to use = and this is step four.

Â We're type in vlookup and

Â this is a common reference command that we're going to come back to.

Â So, just to look out what the arguments are that go into this command,

Â we first tell Excel what's the value that we're going to look up.

Â In this case, it's the random number that we generated.

Â We then tell Excel where should I look up this value,

Â where is the table you want me to look up this value?

Â And then what column in that table, when I find the closest match,

Â what column do you want me to extract and

Â do I look for an approximate match or an exact match?

Â All right, so the look up value is our random number B28,

Â where are we looking this up?

Â We're going to look this up in the table created by cells E3 through,

Â 12:08

All right, so in this case, see, now our random number has refreshed to a 0.27, so

Â that's less than 0.3, so that's being associated with a demand of 100 units.

Â Just so you can see some different values,

Â what I'm going to do is I'm going to highlight cells B28 and C28.

Â And I'm going to copy these formulas down the table.

Â And a shortcut for doing that is if I move my mouse cursor to the lower

Â right hand corner of the highlighted selection and then double-click.

Â It's going to copy those formulas all the way down.

Â So long as I have something that's already filled out in the adjacent column.

Â And so let's take a look here.

Â So when the demand is, or the random number is 0.83,

Â that's associated with 250 units of demand.

Â A random number of 0.41, so between 0.3 and 0.5.

Â That gets me my demand of 150 units.

Â 0.999 as my random number gets me demand of 300 units.

Â All right, so we've now simulated out the exact value of demand,

Â in each of our different replications.

Â We're going to calculate out what's the revenue associated with

Â a particular order quantity and level of demand.

Â What are our costs?

Â What's the refund amount and the profit?

Â So let's start by filling out the revenue.

Â All right, well the revenue depends on two things.

Â It depends both on the order quantity and it depends on the level of demand.

Â We control the order quantity, we don't get to control the level of demand.

Â So the revenue it's going to be how much we sell each calendar for, so that's B3.

Â And I want to make sure to hit F4,

Â I want dollar signs to show up around that reference.

Â That's going to make it an absolute reference.

Â So for, that's the price we get per calendar sold.

Â Then we're going to multiply that by how many calendars we sell.

Â Well that's going to be the minimum of what

Â the level of demand is, and the order quantity.

Â 14:27

And for B7, I'm again going to hit F4 and put dollar signs around that.

Â So it's our price multiplied by whichever is smaller.

Â The order quantity or the level of demand.

Â So if I only order 200 calendars I can only sell 200 calendars so

Â long as demand is at least 200 units.

Â But if demand is less than 200 units,

Â less than my order quantity I only get to sell as much as my order quantity.

Â 15:30

My order quantity, Again make

Â sure we get our absolute references with the dollar signs by hitting F4.

Â So if demand is less than the order quantity, the quantity sold

Â is only going to be equal to the demand.

Â But what if that if statement isn't true?

Â What if demand is equal to or exceeds the order quantity?

Â Well in that case, then I'm going to get My order quantity.

Â 16:19

So my order quantity as our placeholder was 200 units.

Â If demand is 250 calendars, well,

Â I've only got 200 calendars to sell at $10 a piece.

Â So I get revenue of 2,000.

Â If demand is 100 units, even though I have 200 calendars I'm only

Â able to sell 100 of them, so I get my revenue of $1,000.

Â So that's our revenue piece.

Â Let's write out the cost piece, and

Â that cost piece is going to depend on my order quantity and the price per unit.

Â So the cost is going to be my order quantity,

Â B7, again hit F4 to make sure you get that absolute reference.

Â So that when we copy this formula, it always refers back to cell B7.

Â So it's how much I order, multiplied by my cost per unit.

Â 17:10

In both of these cases, we're going to put those absolute references in place.

Â So B7 multiplied by B2.

Â And we can copy that formula down our spreadsheet.

Â Now notice in this case, keep in mind that whereas revenue depends on both the order

Â quantity and the level of the demand, the cost to purchase that quantity

Â only depends on how much we choose to order.

Â Cost doesn't depend on the level of demand.

Â All right, the last piece is the refund amount.

Â So when do we have a refund?

Â Well, we have a refund when we've ordered too many calendars.

Â All right, so we're going to use an if statement here, =if.

Â So if the demand is less than my order quantity,

Â so if C2 is less than B7, and hit F4 for

Â the dollar signs, all right, when that's the case.

Â So I ordered too many calendars.

Â Well, how many units were, what's our refund amount going to be?

Â Well, my refund amount is going to be the amount of

Â the refund per calendar, so that's B4.

Â And how many units am I getting as a refund?

Â Well, it's the difference between how much I ordered and how much I was able to sell.

Â So I ordered B7 units,

Â 18:46

So if demand is less than the order quantity,

Â I get the amount of the refund per unit multiplied by the number of unsold units.

Â My order quantity minus demand.

Â And if demand is not less than the order quantity, well,

Â then I don't have any refund amounts.

Â 19:13

All right, so again, let's take a look at this.

Â If I have a demand of 100 units, and I order 200 units,

Â I have 100 units left over.

Â I get a refund of $250.

Â All right, now if I have demand of 200 units and

Â I order 200 units, I get no refund.

Â If I have demand of 250 units and I only order 200 units, I also get no refund.

Â If I have demand of 150 units, I've got 50 units that

Â don't get sold at 2.50 a piece for the refund, I got a refund of $125.

Â All right, so we have a revenue, the cost, and the refund amount.

Â The profit is just going to be based on revenue

Â minus the cost plus my refund amount.

Â And let's copy that formula all the way down, all right?

Â So when I order 200 units, you see a lot of scenarios where we generate a profit,

Â but we do have some scenarios, so in this case,

Â when demand is only 100 units, where we end up coming up short.

Â 20:21

Now, just so that we can take a look at this,

Â what we can do is in the summary measures section, let's summarize this.

Â So the expected profit, that's going to be

Â the average of cells G28 through G5027,

Â and so our expected profit is positive.

Â If we wanted to get a sense for how much variation there is, we can use the stdev

Â command, Of the same range.

Â So what's the standard deviation of that column?

Â We could also calculate out the minimum and

Â maximum profit across those 5,000 simulations.

Â 21:43

Expected profit drops, but there is a higher maximum profit but

Â a lower minimum profit.

Â What if we go the other way?

Â What if it was only 175 units?

Â So we could play around with the order quantity and

Â look at what kind of impact it's going to have on our summary measures.

Â And what's happening with these summary measures is every time we make a change to

Â this worksheet, it's recalculating for us all 5,000 scenarios that we're running.

Â Now we could do this for different levels of demand, one at a time or

Â different levels of the order quantity one at a time, copy and paste the results,

Â or we could try to get Excel to do the heavy lifting for us, all right.

Â And that's what we're going to do in the one-way what-if analysis.

Â So we're up to step seven on the work through, and

Â what we're going to do is, we're going to get Excel to, one at a time, enter all of

Â these different order quantities, 100 through 300 in increments of 25 units.

Â Excel's going to take each of those values, plug it into cell B7,

Â run our 5,000 simulations, and

Â calculate out the expected profit and record that value.

Â So in this case, it might not look like it's saving us too much time, but

Â keep in mind that we're only considering a handful of scenarios, or

Â a handful of different order quantities.

Â If we wanted to go one unit at a time, or if we have a wider range of options,

Â having the computer do this for us is going to save us a lot of time.

Â All right, so let's go through the steps that we have to follow.

Â In cell F12, we're going to type = b11.

Â And so what we're doing is, that's the value that is going to be recorded.

Â So we're going to take this 100 as my order quantity,

Â the computer's going to plug it into B7.

Â It's going to run our simulation analysis, it's going to update what's in B11,

Â the expected profit, and we're going to record that value in cell F13.

Â Then it's going to move on to order quantity 125, plug that into B7,

Â update the scenarios, calculate expected profit, and we'll record that value.

Â So that's what the what-if analysis is doing for us.

Â 23:58

All right, so we're going to highlight cells B12 through F21.

Â And so, notice that we're highlighting in the first column,

Â it's the different values that we want plugged in.

Â And we have to go as high as the cell that includes the value that's going to be

Â copied in.

Â All right, then we're going to go under the data ribbon.

Â And we're going to go under the What If Analysis.

Â 24:49

So, we're going to allow that to be cell B7.

Â All right, so the column input,

Â what it's doing is saying take values in this first column and

Â plug that into cell B7, all right, and we're done.

Â All right, so, saves us a bit of time from doing this manually,

Â so, when I order a 100 units, I expect a profit of 250.

Â When I order 125 units, my expected profit is 255.

Â And we could do this manual, and you'll see some variation around these

Â exact numbers, because it's based on the set of 5,000 simulations that we've done.

Â 25:36

Now, what we'd like to do is to find out what's the ideal order quantity?

Â Now again, since this is a small set of values that we tested,

Â we could eyeball this and say it looks like the optimal order quantity,

Â whatever corresponds to 263.48, and that would be 150 units.

Â Well, how could we use Excel to retrieve that value for us so

Â that we don't have to do that manually?

Â 26:19

What the match command is going to do for us is find where the match command,

Â we're going to tell it let's find the highest value.

Â And what row does that correspond to?

Â The index command, we're going to say,

Â find in a given set of values that particular role, right?

Â So, let's start by taking a look at what the match command does, right?

Â So, what the match command says is what value do you want to look up,

Â and where do you want to look it up?

Â All right, well, the value we want to look up is what's our highest profit?

Â So, the lookup value's going to be the maximum of this table.

Â All right.

Â 27:25

And so, you see it returns a three first.

Â And what that means is, recall the column that we were looking at,

Â it's the third row.

Â So 1, 2, 3.

Â That's where it had the maximum value.

Â Now, we want to take that three, and say, all right, well, find for

Â us in the order quantity column, the third value,

Â row 123 corresponds to 150, and that's what the index command does for us.

Â All right, so I'm going to build the index command around this.

Â 28:00

So, I'm going to type index.

Â And the index command asks for the array, so

Â that's that first column, the order quantity column, E3 through E21.

Â And then it asks for what's the row number?

Â Well, the row number is what the match command spits out for us,

Â 29:39

And so, we see that as we move from a 100 to a 150 units,

Â we do get an increase in expected profit.

Â But we don't see much of one, and then once we order more than 150 units,

Â then things start to go south for us.

Â So, in this particular example, seems like there's a lot of downside.

Â 30:08

It looks like the bulk of the demand, 50% of the demand is 150 units or less.

Â There's going passed 200 units.

Â If you order more than 250 units,

Â there's only a 20% chance of the demand actually being that high.

Â And so, we've got to balance things.

Â Where, if we order too much, we end up paying a penalty for that.

Â The refund gives us a little bit of credit,

Â but we're essentially throwing away five dollars per unsold unit.

Â And so, we might take a look at well, how might our decision change if we change

Â the refund amount, if we change our cost, if we change the pricing structure?

Â Right now, we're not manipulating those things.

Â But perhaps demand is going to be a function of price.

Â So, that's where some of the techniques we'll look at

Â later in the semester will come into play to say, what if I change my price?

Â Yes, that's probably going to bring down my level of demand a bit,

Â but where's my profit maximizing price going to be?

Â So, depending on if we can get our costs down, if we can increase the price,

Â maybe if we can get a slightly higher refund.

Â And if we change that refund amount, we're going to see these numbers change a bit.

Â Because the lower that refund amount, the less credit I get back,

Â I'm probably going to err on the side of ordering fewer calendars.

Â The higher the refund amount, well, then there's less risk that I'm taking on.

Â If we move this, if we move our chart to the side,

Â just as a hypothetical, what if we increase the refund amount to $5.00?

Â 31:48

What happens to our expected order quantity?

Â Now, higher refund amount,

Â I actually maximized my profit at 200 units, instead of the 150 units.

Â So, if we can get him to buy those calendars back for a higher amount,

Â I'm taking on less risk.

Â I'm better off doing that.

Â Well, what if the refund amount were 0?

Â All right.

Â If the refund amount is zero,

Â now my profit maximization occurs only at 100 units.

Â Say, doesn't matter how much upside there is.

Â I'm better off just ordering the 100 units, and

Â not running the risk of having too much unsold inventory.

Â 32:27

All right, so, we can manipulate cost, price, refund amount and

Â see what kind of impact that ultimately has all the profit.

Â But the only piece we get to control here is our order quantity.

Â So, there's uncertainty in the level of demand, and

Â that's what the Monte Carlo simulation is helping us take into account.

Â 32:48

In the next Excel exercise that we're going to look at is,

Â what if we have a different way of characterizing this uncertainty?

Â We're going to use specific functions,

Â specific distributions to characterize that uncertainty.

Â And in that case, we're going to do it in the context of airline over bookings.

Â