Speaker 1: Hello, everyone, and welcome to the data analytics channel. Today we will solve a very practical business problem, we will forecast the sales in a scenario that has both a seasonality and a trend. And for this, we will use the Excel statistical package, namely the regression model. I recommend before going to more advanced tools like Python and R to have a solid foundation in Excel statistical package. Our scenario is very simple. We have the sales for three years. And in this graph, we see that the sales have seasonality, but also a trend, an upward trend each year. So what we have to do is to predict the sales for the next year from January 18 to December 18. For this, we will use the regression model with the dummy variables for each month. What I want to say is that some things maybe will not be very clear to you in this tutorial. Please check them online. Because I don't want to make this tutorial very long. So for your development in analytics, you will need to go online and check for example, what is the dummy variable model? Okay. Maybe what is the regression model? Okay, we have here the equation of the regression model. Okay. So we start building the model, I have added here the month column. And now we have to create the dummy variables. First, we will create a period column. Okay. So each period going to December 18. And then the dummy variables, we select only 11 month. This is the model one of the season from 12 seasons, one is excluded. And for example, if you have on quarterly basis, you only select three quarters for the dummy variables model. Okay, so we transpose them. And then what we do, we want to if this equal this, we put one otherwise zero. And now, if we want to remain constant, the column we freeze B. And here we have to have constant the row we freeze one. Okay. So we go like this, and like this. And you see, we only have one at the indexation of this and this. Perfect, we are now ready to go to the regression model, we have to go to data. And here we have the tab data analysis. If you don't have it here, you have to go to file options, add these Excel IDs, and you select the data analysis tool pack. Okay, I have it here already. So I will choose regression. Okay. And now y range is the sales. Okay. And input x range is this. Okay, we have the labels, the name of the columns, and we want to be in a new worksheet. We select the residual, let's name the new sheet regression output. And we click OK. Perfect. We have the regression model. So what do we have here, we see that adjusted air square is 95%, which is a very good estimation, it covers 95% of the situations, the significance is very, very low, which is very good. Okay, the p value is below 0.05. And we have here the coefficients. The p value is significant for all except for the month of March, and October, which is over 0.05. Again, this p value significance, please check it online if it is not very clear to you. These are very important statistical concepts. And you need to have the basis to understand it. So we now are ready to have the estimation. Okay, here we have the predicted sales. Okay, we have the we can copy them. And you see, here is the difference between our prediction and the real sales. Okay. And these are the residuals. The estimation is quite good. And this shows that we will also have a very good estimation starting from January 18. So now, we will create the regression model. Practically, we have to recreate this equation. Okay. So what we do, we will enter here, a column of forecast. So you will see that with the formula with this formula, we will recreate these values that Excel calculated automatically in the regression model. So how, how we do it, the equation is like this first is the intercept. So we have plus this the intercept plus. And now we have to multiply each coefficient from here, starting with time, and then the 11 months with the values from here. For this, we have a matrix multiplication. So we will use the m mouth formula in Excel, which is a matrix multiplication. And what it it is doing, it multiplies the values from here. Okay. With the values from here. Okay. And we will have to freeze here. Okay. So you see, we have the same value we have the same value that Excel calculated for us. And now we go. Sorry, I have to I have to see why we don't have this values correct. Ah, okay, we have to freeze also the intercept. Okay, this was the problem. So now you see, we have the same values. And now, what we do, we just continue. Okay, with the formula. And now we have the we have the forecast. And very quickly, we will visualize it. Insert recommended charts, or charts, templates. Okay. Perfect. And now we see that in orange is our forecast in blue is the sales, the real sales, we see that our forecast is very good, it follows very correctly, the sales, the real sales, and the forecast is very correctly done. So, in short, this is the dummy variables model with seasonality and trend, you can use it for different seasonality. For example, you can use it for quarters. Or you can use it for weeks, for example, or for days, the system is the same. Thank you very much for watching this video. If you have liked it, please subscribe and give it a like.