Speaker 1: Hi everyone, welcome. This is a follow-up video to one of my earlier videos that I posted in YouTube, forecasting using Excel for data with seasonality and trend. In that video, we only covered an example with one data set only, so demand was provided and the seasonality was in months, and then we were able to kind of provide the forecast values for this single data set. And in the comments, some of you guys said that, hey, what if we have multiple products? Like, if this is going to be done for a company, they may have not just one product, but maybe multiples of stock keeping units, a lot of different products. And how do we do that? The transition is easy. So first, I thought that you might have to write a macro, but you don't need to do macro. You could just simply put the formula in there and then just find your forecast values for hundreds of different SKUs right away. So how do we do that? Let's just go and just take this example. I posted this data set online in my Google Sheet, and the link is down below. And what we have is we have 100 stock keeping units, so we could have more. And now we are going to find the forecast. So let's start with the forecast for January 2020. We are assuming that we are right now, right before January 2020, right before the nightmare started about the COVID-19 and everything. But we are assuming that this 2020 was going to be an excellent year for us. You know, it is 2020. It's just a nice number, but it didn't happen like that. Okay, anyhow, so what's going to happen now is I'm just going to put my formula first. I'm going to find the seasonality index for January using the earlier data sets. And I will just use my formula here, and my formula is going to be, hey, just find the average for average if, just take this range, just look at all those months, and then this is the range for my criteria. And if those match January, then average the values in there. Okay, so this will find the average of January in the data set here, so for this stock keeping unit number one. And then I'm going to, if I divide it by average of all of these values, okay, average of my demand from 2016 until 2019, then I will find my seasonality index for this month. Then I'm going to use the function trend. Now this is going to capture the trend of this data set. If there is a trend, then it will capture. If there's no trend, the trend is going to be very minimal, but that's fine. We will just take the trend function, this will just use the linear regression model in there, and it records the known y's. The y's are my dependent variable, my demand here, okay. And then I'm going to put a comma, and my x's are the independent variables, and those are the time periods. So those are the t from 1 to 48. And then the new x is the trend number, the period number 49. So once I have that, then I'm done. And what happens here is it multiplies, it finds first the value, the trend adjusted forecast here, and using the trend, just the trend forecast, and then it is going to adjust that by seasonality, okay. So it is going to provide me directly what my forecast is going to be for January. And if you do not understand this, I would recommend that just go ahead and I'll take a look at the earlier video that I posted, you know, I mentioned at the beginning of this video. So what I have to do now is, in order to copy and paste this, I have to be just using my dollar signs. And for C2C49, these are my months, and I want to use those all the time there. I'm just going to put my both dollar signs in there. I will not change that. And for C51, this is my period. I want to use period 50, 51, 52, but I don't want to move this column to the right, so I will only put a dollar sign in front of C. And for D2D49, and I want to move those to the right, but I don't want to move those two down, so I will just put the dollar signs here. And here, D2D49, the same thing. And then the trend is the same thing, D2D49. And A2A49 is going to stay there, all together. Okay, both dollar signs. And then A51 is again, it is going to go down, but not to the right, so dollar sign in front of A. So if everything is good, I could just click on it, and then it is showing me my forecast value for January 2020 for this stock keeping unit. So let me just see if this is working fine. I could just try like copying a bit. And yes, it is taking, for example, this data set, like the stock keeping unit number three, and it is using for May, and May is the 53rd period, and then it is just calculating the forecast for that. So what I could do is now I could just drag this to, let's just delete these, and then drag this to the right, okay, for all stock keeping units. And then we could just double click, and we are all set. So we are able to find a forecast for for 2020 for each stock keeping unit. And if we have done that in before January, and hoping that the things are going to be just repeating the history, we would be in big trouble. That is why forecasting is always wrong, but sometimes it is more wrong than other times. So here, so here, this is just expecting that the history is going to repeat, but sometimes it does not, right? That's the whole idea. Anyhow, we were kind of able to manage to find a forecast for multiple stock keeping units at the same time. I want to just tell you something here. In Excel, it's not very easy to kind of create this, the whole line chart with different colors, like two colors. For example, if I just keep stock keeping unit number one, and insert a line chart, let's see, you know, it is just showing me the two different lines. Let's try to see it. Click OK. So here, after this point, this is my forecast. Before this point, this is my historical values. So if I try to select data, it puts all the data set into a single series. If I try to separate them, it only starts from the beginning. So what you need to do is you need to put them in two different columns, and then if you want to have a line chart with two different colors. Otherwise, it is not possible to kind of show it in two different colors. But if you don't care about that, you could just, you know, insert a line chart, and here it is. And this is the first stock keeping unit. And then I could just move to the next one, here it is, and move to the third one, and move to the fourth one, and I am able to kind of find all my forecast values are shown here, and all my historical values are shown there. Thanks for watching, and if you have any questions, let me know. Bye for now.