0:05

So what we're going to be working through in this particular exercise is

Â an ordering decision that we have to make based on historic demand.

Â How much quantity should we order to have on hand for a particular retailer?

Â And so there's a Word document up on the course website.

Â And the approach we're going to take is, as you can see on this spreadsheet,

Â we have historic data.

Â So we have the first column, column A gives us a month indicator.

Â Month 1 through 12 corresponding to January through December, we have monthly

Â sales information, a measure of how much advertising had been conducted.

Â And what I've also created are monthly indicator variables.

Â And what we're looking to capture are differences in the baseline.

Â So, are January sales higher than December?

Â Are February sales higher than December?

Â Are March sales higher than December, and so forth.

Â So do we see variation month to month.

Â And by creating monthly dummy variables where it takes on the value of one if it's

Â a particular month and zero otherwise, what that allows us to do is capture

Â differences that can exist month to month or season to season in a flexible manner.

Â Now, you'll notice the dummy variables that we've created, we have one each for

Â January through November.

Â And what we've done is we've omitted December.

Â Whenever you're creating dummy variables to put into a regression analysis,

Â we're taking 12 months, we're representing them with 11 different dummy variables.

Â All of the coefficients that we're going to

Â estimate with the regression are going to be relative to the baseline.

Â In this case, we've omitted the December variable.

Â And so we're going to interpret the coefficients for

Â January through November as sales being higher or

Â lower in that particular month, relative to the baseline sales in December.

Â That baseline is going to be reflected in the intercept term.

Â 2:03

So we're going to run a linear regression model, where we expect

Â variation month to month, and we expect advertising to have an impact on sales.

Â Now, if we go onto the data ribbon,

Â what you should see on the far right is the data analysis tab.

Â 2:33

and click on Add-Ins.

Â And what you'll see is toward the bottom of this screen,

Â there's going to be Manage Excel Add-Ins.

Â If we go to that, we want to make sure we've checked off the Analysis ToolPak for

Â this exercise.

Â But while you're in there, make sure you have the solver add-in enabled also.

Â We'll be using that throughout the course.

Â 3:04

Now, in the y range, we're going to select our y variable, the dependent variable,

Â in our case, it's going to be sales, and we're going to click on the range button.

Â I'm going to highlight range B1, and I'm going to hold down Ctr+Shift and

Â the down key.

Â It'll take me to the bottom.

Â And what we're going to try to do is we're going to use sales through

Â September of our final year, and we're going to use that to make forecasts for

Â October, November, and December.

Â So we're highlighting B1 through B46 as

Â our X range, I'm going to go back to the top of the spreadsheet.

Â We're going to highlight C1 through N46, all right.

Â So that's giving us the advertising variable

Â as well as the dummy variables per month.

Â We're going to check off the box that says labels.

Â That's indicating that we have variable names

Â in the first row of the range that we've selected.

Â And then for the output,

Â 4:30

So this is how your dialog box should look.

Â The y range, B1 though B46, the x range, C1 through N46,

Â top row does contain labels, and the output range is Q1.

Â And just to note, the reason why we're not using column A in our analysis,

Â its months are represented there 1 through 12.

Â If we were to just insert that as our variable,

Â what we'd be saying is that we expect a systematic linear pattern.

Â That is, we expect February to be greater than January, we

Â expect March to be greater than February, we expect April to be greater than March,

Â because of the numerical values that represent the different months.

Â Months are a nominal variable or you can think of it as a categorical variable,

Â and so we need to create the dummy variables.

Â We can't just use those numerical labels.

Â 5:24

All right, we're going to click on OK.

Â Notice the warning message that it's giving us because we have

Â a text in cell Q1, we're going to be okay overriding that.

Â And we have our regression results.

Â And so, taking a look at our regression output, the r squared value telling us

Â that the monthly dummy variables coupled with advertising are accounting for

Â 78% of the variation in sales that we're observing.

Â We do have a significant f test telling us that the regression line

Â as a whole is a significant predictor of sales.

Â And then if we look at our individual coefficients,

Â we have intercept value amount, the coefficient for the advertising amount,

Â as well as the coefficients for those dummy variables for

Â each of the monthly effects, standard errors, t statistics and P-values.

Â The P-value's telling us, do we have significance at a particular level?

Â Now, you'll notice, for example, advertising,

Â we do have a significant coefficient.

Â That's a very, very small number.

Â And you might be inclined to say that April is not a significant effect,

Â or August is not a significant effect,

Â October is not a significant effect, November is not a significant effect.

Â And while that's true, keep in mind what the significant effect means in this case.

Â 6:48

These coefficients are differences compared to sales in December.

Â So we expect 132 fewer units sold in January compared to December.

Â 131 fewer units sold in February compared to December.

Â Because we do not have a significant P value for

Â the November coefficient, or for the October coefficient.

Â What we're saying is that there's not a significant difference in the sales

Â volume between October and December or November and December.

Â So that's the interpretation that we're going to have there.

Â But taking the monthly dummy variables as a whole,

Â 7:41

All right, so we've run our regression analysis.

Â The next thing that we need to do is to use this equation to

Â come up with our predictions.

Â And then we're going to take a look at how good a job are we doing at forecasting

Â sales.

Â So on the second page of the instructions you can see what the equation looks like.

Â And we're going to take the intercept value, we're going to add to that

Â the advertising coefficient, multiplied by the advertising amount.

Â And then we're going to add in the appropriate dummy variable.

Â Now the Word document that's up on the website,

Â we can do this in a very manual way.

Â And I'll show you what that starts to look like.

Â And we're going to look at, it's going to be the intercept and make sure that we put

Â in our absolute references when we're referring to the coefficients.

Â Plus our advertising coefficient multiplied by the advertising amount and

Â we can gradually enter these terms and you can copy and

Â paste from the Word document directly into this cell.

Â That'll save you some time.

Â I'm going to show you another way that we can save some time when we're doing these

Â predictions, is to use the sum product command.

Â 9:16

Of the transpose of the advertising.

Â Now notice our values are in a row,

Â what the transpose command is going to do is turn that into a column.

Â Now I'm going to take the sum product of that with the coefficient amounts.

Â And on my coefficient amounts I'm going to make sure we put dollar signs.

Â And we're going to hope that this command works now.

Â Because the transpose command is an array function,

Â I'm going to hold down control shift and hit enter.

Â 9:54

And so what that's done is that's saved me the trouble of having to copy out that

Â entire equation.

Â Again, you can use this particular command or

Â you can copy the command directly from the Word document into Excel.

Â We're going to hope that the shortcut that I've used allows me to copy this formula

Â down the columns and it does.

Â 10:29

And eyeballing it, it doesn't look like we're doing a bad job.

Â We'll take a look at it graphically, but

Â before we do that, let's jump down to the bottom of the table.

Â If you just hold the Control button down and hit the down key.

Â How good a job did we do in the last three months?

Â So rows 47, 48 and 49.

Â We did not use this data when we were estimating our regression model.

Â So these are forecasts based on the amount of advertising that was done.

Â And it seems that we do a generally good job.

Â All right.

Â Now if we want to look at things graphically, under the insert tab,

Â let's put in line chart.

Â 11:11

And let's highlight the two columns first.

Â I'm going to highlight column B by clicking on,

Â then I'm going to hold down Ctrl, click on column O and let's see if that

Â helps Excel figure out what we're trying to do and there we go.

Â Let's get rid of that title that doesn't tell us anything.

Â Just good habit to get into as far as building these charts.

Â Let's put some axes in here so that we know what we're looking at.

Â 12:30

All right.

Â And so we see from our regression results

Â that the seasonal variables we do pick up those dips and those spikes,

Â and even in our forecasting period which not use to estimate the model.

Â We do a pretty good job, right?

Â So we've built a forecasting model.

Â 12:47

Now the next thing that we want to do is figure out what should be

Â the appropriate amount for us to order.

Â So we're going to user our predictive model that we've developed.

Â And you can see after the graph on page two, how do we incorporate this and

Â recognize the extent of uncertainty that our model has in it in

Â order to inform how much we should be ordering?

Â So we want to understand what profit looks like under different levels of order

Â quantity.

Â Now we're going to do this for the month of October,

Â that was the first month of our forecasting period.

Â We're also going to do with this using the same procedure for November and

Â December as well.

Â 13:29

And so the procedure that we're going to follow is we're going to simulate

Â a level of demand for a specified order quantity and that level of demand.

Â We're going to calculate what our revenue costs and profit are and

Â we're going to repeat that simulation procedure, do it 10,000 times.

Â Once we've done that account for

Â the variation, we're going to look at the expected profit or the average profit for

Â a given order quantity and the different percentiles.

Â So let's take a look at the worksheet that we're going to be using.

Â 14:10

Then we'll come up with our formula for what the revenue is going to be.

Â And that revenue is going to take into account what was demand as well as how

Â much as did we order.

Â We'll take into account the costs, that's based entirely on how much we ordered.

Â And then calculate profit.

Â We'll repeat that procedure 10,000 times.

Â And then we're going to summarize that information over in this region.

Â So order quantity for October, November, December.

Â That's what we're specifying.

Â Those are our decision variables.

Â And then we'll summarize the results in these columns.

Â 14:59

Now when we came up with predictions, we come up with our regression equation,

Â that's giving us a point estimate.

Â The problem is that those point estimates really are just the center of a normal

Â distribution.

Â And the standard deviation around that normal distribution,

Â well that's reported in our regression statistics.

Â So in our case, there's the mean estimate,

Â our prediction, is giving us the mean of the normal distribution.

Â And then how much uncertainty do we have around that?

Â What is the standard deviation around that?

Â In our case it's going to be 42.47.

Â It's given by that standard error.

Â So what we'd like to do, rather than just plug in the prediction value and

Â assuming that's what we get every single time,

Â let's recognize that sometimes it's going to be higher.

Â Sometimes it's going to be lower.

Â So what we're going to do is we're going to draw from a normal distribution

Â centered at our prediction.

Â So in our case for the month of October.

Â Our prediction is row 47, that's 209.22998.

Â That is the center of our normal distribution but sometimes demands

Â going to be higher than that, sometimes demand is going to be lower than that.

Â So, we are going to draw a random variable from a normal distribution to

Â simulate a level of demand.

Â Now, unfortunately for us, Excel it does not have a built-in tool to simulate

Â a normal random variable in a convenient way.

Â So what we're going to do is, we are going to use a little trick.

Â It's very similar to what we had done earlier in the semester.

Â We're going to use the norm inverse command, and what the norm inverse

Â command is going to do is say, give me a value between 0 and 1.

Â Well we can simulate a value between 0 and 1 using the RAND command.

Â So, using the RAND command, we're going to draw a number between 0 and 1.

Â 16:54

We're going to specify that we want Excel to return to us a number

Â corresponding to that cumulative probability be 0 and

Â 1 coming from a particular normal distribution.

Â And that normal distribution it's going to have the mean based on our prediction and

Â put an Absolute Reference there.

Â 17:52

We can repeat that same procedure for

Â predicting November and for predicting or simulating November and December demand.

Â And so, what I'm going to do since I use absolute references,

Â I'm just going to drag that formula over, but I've gotta go back and

Â change where we specify the mean of the normal distribution.

Â 18:20

And so, we've gotta go down one cell.

Â So it's not O47, it's going to be P47 for November and

Â it's going to be Q47 for the month of December.

Â And let's just go back into our data to make sure that we've got

Â those rows appropriately.

Â 18:46

And so, my mistake there.

Â We're staying in column O, so we're going to be changing it from 47 to 48.

Â So we want to be referring to O48 for November and O49 for December.

Â So let's go back and undo those two changes.

Â So, the month of November is going to read as drawing from

Â the cell O48 as the mean of the normal distribution.

Â Month of December is going to read from O49.

Â 19:21

Right, and so, we're now simulating a level of demand for October, November and

Â December.

Â What about the revenue?

Â Well, the revenue is going to depend on how many units we actually sell and

Â then if the units are not sold what's the refund amount that we're getting.

Â So, you can see the command that we've specified already.

Â So for each unit that we sell, now the number of units that we sell,

Â it's the minimum of our demand and how much we order.

Â So let's start out by placing in some placeholder values for our demand.

Â I'm going to put in 200 units for each month.

Â 21:12

So that's our equation for revenue in the month of October.

Â Now, I'm going to highlight this formula through the formula bar and

Â copy it manually into November and into December.

Â And let's make the appropriate changes here.

Â And so if I click in my formula bar, you see,

Â I don't want to be referring to the month of October for November revenue.

Â I always want to be referring to my demand in November.

Â And similarly, my order quantity, I can simply click and

Â drag those reference to the appropriate cells.

Â And so now, for the month of November,

Â we're referring to, in this case, it's cell C3 for

Â the demand and P2 for the order quantity.

Â And we're going to make similar modifications when we're looking at

Â the month of December.

Â Our demand should be coming from cell D3,

Â and our order quantity should be coming from cell P3.

Â 22:23

Okay, right, so now we've got our revenue figures.

Â What does our cost calculation look like?

Â Well, in this particular example that we're working through

Â as we have discussed in the PowerPoint.

Â We're going to assume that the pricing depends on how much we order.

Â And so, if I order 1 to 100 units, I'm paying $12 per unit.

Â If I order Between 100 and

Â 200 units, well then my pricing goes down to $10 on the second batch of 100 units.

Â And if I'm ordering above that,

Â my pricing goes down to $8 per unit for

Â all units above 200.

Â And so, there are a couple of different ways that

Â we could create this cost function.

Â The way that it's described in our instructions,

Â we're going to use an if command.

Â So if our order quantity, P1,

Â and then we'll again make the changes for

Â November and December as need be.

Â So if we're ordering less than 101 units,

Â we're paying $12 for each of those units.

Â If not, if we're ordering.

Â So, the if statement tells us, if it's not less than 101.

Â All right, well, if it's not less than 101, if it's less than 201.

Â So if I'm ordering up to 200 units, I'm paying $12 per unit for

Â the first 100 units.

Â So I'm paying $1,200 plus

Â $10 per unit multiplied by the number

Â of units in excess of 100.

Â And if I'm not less than 201 units,

Â it means that I'm ordering more than 200 units.

Â So I pay $1,200, that covers the $12 for

Â the first 100 units plus $10 a piece for

Â the next 100 units, so that's $1,000,

Â plus $8 per unit for each unit in excess of 200.

Â 25:48

Now we have cost for the month of December.

Â Now, profit, that's going to be an easy one for us.

Â That's just revenue minus our cost.

Â And we're going to drag that formula over to columns L and M.

Â All right.

Â And we're done for this particular iteration of the Monte Carlo simulation.

Â Now, I'm going to highlight from B3 all the way over to M3.

Â And then let's just double click once we get that crosshair in the lower right

Â corner of that selection.

Â And that'll copy down the formula for 10,000 iterations,

Â because I've already created that counter in column A, all right?

Â 26:30

So, all that's left for us to do is to calculate the percentiles and

Â the percentage profits, all right?

Â Well, for the average, what's our expected profit?

Â We'll just use the average command for each month.

Â So for October, it's the average of K3 through K10,002.

Â November's going to be the average of

Â L3 through L10,002.

Â And December, these are going to be the average

Â of M3 through M10,002, all right.

Â Well, that gives us the expectation,

Â and we can change the order quantities to see how profit might fluctuate.

Â So if we ordered a little bit more in the month of December, we can see that

Â our expected profit in the month of December is going to go up considerably.

Â If we go up even higher, if we order 500, now it goes the other way.

Â So that optimal order quantity might be somewhere in the middle.

Â All right, but how much uncertainty do we have in our profit?

Â Well, that's where the Monte Carlo simulation helps us out,

Â because we've got 10,000 iterations.

Â All we have to do is rank order those iterations to report what's the two and

Â a half percentile, what's the 12 and a half percentile, the 25th percentile,

Â and so forth.

Â And so to do that, what we're going to use is the percentile command,

Â 28:21

But we've got the percentile command,

Â what it's asking us to highlight first is what's the region that we're looking at.

Â So what are the cells we're looking at.

Â And we're going to put an absolute reference around that.

Â And then the next thing that it asks us for is,

Â what is the percentile that we're interested in?

Â And we've put those percentiles in row P, so we're just going to reference that.

Â And in this case, we're going to put the dollar sign so

Â that we're always drawing from row five.

Â That's going to allow us to drag this formula across and

Â down without having to make any changes to it.

Â 29:16

So we're good right now for October.

Â As we drag this across, we're always going to look at column K, and

Â we're going to change the percentile that we calculate, all right.

Â And so you can see in the month of October, ordering 200 units,

Â now I've gotta copy this cell and move it over here, and that's fine.

Â That we do have some downside.

Â There is a chance that we're going to have very low profit ordering 200 units.

Â 29:50

But the range if we want to take a 95% confidence interval,

Â it's going to be somewhere between $50 and $800.

Â We want to take the interquartile range.

Â So the middle 50%, between $600 and $800.

Â Let's do that same command.

Â 30:15

For November, if I click in that formula bar,

Â I can then just drag the highlighted region.

Â For December, I can do the same thing, I click in my formula bar and

Â I can move the highlighted region over to the month of December.

Â And that will allow me to have the formulas that I

Â can then use to just fill in the rest of this spreadsheet.

Â 30:54

All right.

Â And so what we've constructed here in the average column, we have what's our

Â expectation, but we now also have a sense for how much uncertainty do we have.

Â So when ordering 200 units in December,

Â I'm pretty sure that I can get $800 in profit.

Â But there is a chance that it's going to be below that.

Â And that's what's bringing down our average in October or in November and

Â December.

Â Whereas if we look at the month of October,

Â looking at the average, it's a little bit lower.

Â So maybe the order quantities for each month should be different.

Â If we order a little bit more in December,

Â we're pretty comfortable that that's going to be the right decision for us.

Â And so we can now use the sheet with an understanding of, how does our order

Â quantity affect what expected profit is going to be, but also, how does

Â it impact the amount of an uncertainty that we have in that profit equation.

Â 32:02

And so, this tool, we can add some bells and whistles to it.

Â We can make changes to order quantity.

Â We can put in some spinner tools to make this a little bit more interactive.

Â But we don't want to just be thinking on terms of the expectation.

Â We also want to have a sense for how much uncertainty do we have.

Â And so that could be reported as a 95% interval,

Â 90% interval interquartile range or metrics like that.

Â