 In the last video we developed a regression model that captured the seasonality, these peaks and valleys of the data, but it didn't capture the trend. Do you recall on this plot of the actual sales data in blue and the forecast in orange? Down here at the beginning of the time series the actual is below the forecast and at the end of the time series the actual is above the forecast. So we want to capture that trend. This is the worksheet that we had before we ran the regression in which we have our dummy variables, the 11 dummy variables that captured the seasonality. Now we want to get the trend and the trend is tied to the months, the periods, and the easiest way to get this is just to copy this column over here on this side. And the reason we need it there is that if you recall the data analysis regression tool needs all of the x variables and adjacent columns and the y variable here would be separating it. So now we have our data transformed. Let's go ahead and go to data, data analysis, bring up the dialog box. We want regression and we want again our y variables, get those all selected, and our x variables, move this that away. This time we need all of the dummy variables and the month column, the period column to get the trend. We have that. We want our output. I'm going to put it over here just one more cell over and again we don't need the residuals or anything. We're just going to click okay and move over here. I've expanded the regression output and gone ahead and converted those scientific formats into decimals for our p-value. So again the ANOVA is significant and if we go down our various coefficients we see that again we've got August out there that is not statistically significant. But the overall model, we look at the adjusted R square is 0.962. So that's increased quite a bit. If you recall the adjusted R square for the previous model was 0.903. So this is a pretty good model. One thing you might consider doing to see if you could get better would be to rerun the regression, getting rid of the month of August and see if our adjusted R square improves or not. Before we do that let's calculate our forecasted values to see how the plot looks. If you recall we'll just put our cursor in that cell hit an equal and we'll start with the intercept. Use the function f4 key to lock it down plus the November coefficient function f4 to lock that down times November plus December coefficient f4 to lock it down times December plus the last step of the model is to add in the month coefficient lock that down times the month and then I can hit enter and I know that shows March is 71 that's just because the formatting was carried over. I'm going to click on accounting to get it into something that looks more realistic and of course then we can drag this down to get our forecast and now we'll plot that. I've created my scatter plot. Recall this is the plot that captured the seasonality and you can see the gaps there. I'm just going to click over here on the new plot and you can see that the forecast is much much much closer to the actual and I'll just click that back and you can see that we've captured both the seasonality and the trend using this method.