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. 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 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 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. 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. 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. 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. 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? 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 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. Between 0.5 and 0.8 is a 30% chance, that's a probability of getting demand of 200 units. If we look at what's the probability of being between 0.95 and 1, only a 5% chance. And that corresponds to the probability of getting a demand of 300 units. 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, F7 and you'll see that Excel has replaced that with the label demand table. That was the name I've already assigned to this table. And what column do we want to extract? Well we're going to look for the match of the random number in the first column and we want to extract the value that's in the second column. And then the last piece do we want to allow for approximate matches? And yes we do in this case, so we're going to type the statement true. So look up the random number generated in cell B28, find it in this demand table. We find the closest match in the first column, then we pull the corresponding value from the second column, and we are allowing for approximate matches. 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. 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. So that's one way of writing out that formula. Another way that we could write out this formula is we could do this using an if statement. All right, so for example I could write this as B3 multiplied by an if statement that says, if, Demand is less than, 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. So either of these approaches could be used for specifying the revenue model, all right? So let's copy this formula down and take a look at it to make sure that everything's working. 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. 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, And I was able to sell C28 units, all right. 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. So I get a 0 as my refund. 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. 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. So the minimum of G28 through G5027 and then we can look at the maximum. So what's the best and worst case scenarios for us when it comes to profit? So we might make profit as high as 500 units, but we might lose as much as $250 when we order 200 units. Well, what about ordering 225 units? What happens to our expected profit? 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. 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. And we're going to treat this as a Data Table. And our column input. 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. 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? Well, to do that, we're going to use the index and match features. So, let's build up this command in sequence. If you look at step eight, here's what we're going to be looking at. So. 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. And where do we want to look it up? Well, we want to look it up in the same table. And what kind of match are we looking for? We're looking for an exact match. Now, this is going to return for us the row corresponding to where this value exists. 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. 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, And so, our optimal order quantity is 150 units. Now, if we wanted to, we could plot this out and see where on our curve, where on the curve do we get the maximum profit? In fact, let's take a look at that. And so, if we highlight these values under insert, we're going to put in a line chart. And I'm going to delete the series one, that's the straight line, And if we go and right click on that line to select the data, we'll format this a little bit, our horizontal axis we want the labels to be. E13 through E21. And so, I've just replaced those values, and we'll get rid of that Series2 label. So, just hit delete. This is, just title this, Expected Profit by Order Quantity. 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. And if we think about what's going on here is what is the level? Where do we have the bulk of the demand? 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? 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. 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. 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.
