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. 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. Now before we click on that, if that's not yet installed on the computer that you're using, you can click on the File tab under Options, 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. All right, so if we click on the data ribbon, click on data analysis, and we're going to scroll down to the regression tool. 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, what we're going to do is I'm going to put the output on this same worksheet. All right, so I'm going to click on the output range rather than building a new worksheet. And I'm going to put in cell Q1, where it says insert regression output. 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. 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. 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, we do observe significant variation month to month as long as at least one of our dummy variables has a significant coefficient, which many of them do. 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. And we're going to hope that this works coupled with the transpose command. But we're going to take the sum product, 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. 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. All right. So we've now populated the prediction column. And what we want to look at is how good a job are we actually doing? So column O has our predictions. Column B has actual sales. 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. 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. So under the chart tools design, we're going to add axes titles. Let's make sure we've got our axis or the horizontal axis title, this is just a monthly indicator. And then we want to go back and do that. And we want to put in our sales on the vertical. Let's call this unit sales. 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. 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. 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. All right. And so the way that this has been set-up is we're going to stimulate out what the level of demand is in this first region. We'll do that for the month of October. 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. So let's start by simulating what the demand is going to look like in a given month. 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. 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. And what's the standard deviation? Well, that's going to be based on our regression results also. It's going to be based on that Standard Error. So cell R7, an Absolute Reference. So, we're going to draw a number between 0 and 1, and we're going to find a particular value that that cumulative probability corresponds to coming from a normal distribution centered at our prediction and having the standard deviation equal to our standard error. 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. 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. 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. 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. And so, how much do we make on the units that we sell it's 15 times the minimum of my October order quantity And my demand. So I get $15 whenever I sell a unit. And then you'll see that I'm using an if statement saying that if I've ordered too many units, I get back $5 for each excess unit. All right, so we'll use the if statement so if, P1 exceeds demand, I get back $5 for each excess unit. And what if I don't have units left over? Well then it's 0. I don't have any refund amounts. 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. 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. So that's our cost equation for the month of October. And since we've used absolute references here, I'm just going to copy and paste that formula into the next two cells. And let's just update that formula. We're going to change P1 for all of these. We're going to change that to P2. So I'm just doing that by dragging all of those boxes. You could also do it by just retyping everything in your equation. 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? 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, and you can copy this directly from the instructions. Inclusive versus exclusive doesn't make much of a difference in our case. 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. And let's go back and modify those dollar signs. 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. 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. Let's just drag this down for October and November, and make the modifications that we need to. 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. Let's just make sure that we're always drawing from the appropriate region. 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. 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.
