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.