 Hello everyone, welcome to the session of time series analysis. In the previous session we have discussed different type of exposure smoothing models and also we have discussed the seasonality and how to use seasonal index to calculate the seasonal average of the quarters or average of the priors or weights of the priors and then how to make forecast using seasonal index or seasonality process. We have also discussed different type of exposure smoothing models like simple exposure smoothing model, like in then hold model and then winter hold model also. In the hold model we have discussed the trend part that means if the data if the time series data has the trend in that case you can use the hold model we have discussed detail about it. Then we have discussed if the data has a trend and seasonality together that is called you know winter hold method we have discussed that also where you segregate the data into three aspects level base value and then you calculate the like trend part and thus index seasonal index part and then if you integrate all three you get the winter's hold model which is extension of hold model we have discussed detail about that. Also remember in the seasonal index calculation process or simple if the data are having a simple seasonality not the trend we have discussed the quarter leverage method or simple average method or say normalization method. So, all aspects we have covered if the data has seasonality or trend or say trend and seasonality together. Today we are going to study a different model which is alternative to winter method that is called multiplicative decomposition method. It is the same like winter's method, but in industry it is quite popular to understand for a lemon person even people from HR background IT background can also understand this how the multiplicative decomposition method works. But in winter method you have a many components right three different series you want to calculate and then you have to integrate all three like level trend and seasonality and you have to add them level and trend and then you have to multiply the index for each quarter and then you have to forecast that which is bit complex, but we have understood that we have discussed that. Today we are going to study a lemon way of understanding the time series data analysis through decomposition multiplicative decomposition method and also it is quite stronger like winter method and it will also give a quite accurate forecast through this decomposition method. Let us see how this multiplicative decomposition method works as alternative to winter's method and why it is so popular in industry we should study today. In general in multiplicative decomposition method same logic like winter method what they do they calculate the different components like you know the decompose the entire time series the behavior of the data they analyze effectively and they decompose the data into major two components that is called the trend part and the seasonality part. They first calculate the seasonal index the seasonality component seasonal component and the trend part also then they again they collate them and they come up with a further forecast which is called you know trend line analysis after desalination and again multiply by the index how the process works I will explain that, but also remember one thing that you know generally people talk this decomposition method or people explain this decomposition method in many way look at here one like popular definition I have bought here like multiplicative decomposition method of time series is a forecasting method that decompose the time series into into its component like trend, seasonality and the residual parts also like in the error part residual components part also and then they first calculate the trend part and then like independently they decompose and the one by one they calculate and then they again they put together and they finalize the multiplication of all three and then come up with the forecast that the seasonal part and the residual part today we will not focus on the residual part we will focus majorly on trend and seasonal analysis and a new way of understanding or a layman way of understanding the decomposition method of time series data where both seasonality and trend will exist look at this particular figure components of time series session we have bought I have shown you this figure right I have bought the same figure because this is the ultimate graph where it takes care of both the trend part as well as the seasonal part so both seasonality and trend for the timing randomness we will not consider suppose we will consider simple seasonality and and say you know trend part together look at over a period of time the data has a seasonality and trend it is not only seasonal it is not only seasonal data is having a trend also look at data is having a trend also so therefore simple average or simple average or say normalization method cannot be used here but if you have only seasonality no up trend down trend then quarterly average method is very popular but this method is much more powerful than actually simple seasonal index calculation or quarterly average method simple average method so let us see how it works and what are the process involved or the steps involved under you know multiplicative decomposition method and with a numerical illustration we will try to understand the process and the calculation of this decomposition method there is another process called additive decomposition method we are not discussing that we will be restricting our discussion only on multiplicative decomposition method so there are five major steps in this decomposition method or multiplicative decomposition method one is that first calculate the major steps is that calculate the index like quarterly average or simple average you need to find the index so that index is here calculate the seasonal index but before that here in quarterly average what you do you take the all quarter data and take the average and calculate the global average and divide this to quarter average by the global average right the overall average you get the index but here you do not do that you can follow that of that process also to get the seasonal index here we will use a different approach that is called center moving average using that see not only on not only the quarterly average data and then they then total of them by the total global average or overall average we will not follow that process of calculating the index here we will follow a different method called center moving average there is a merit I will discuss that in the in excel so we will calculate the center moving average simple moving average you know using that concepts we will calculate the center moving average using the center moving average we will calculate the seasonal index so then step 1 step 2 are over once you get the seasonal index that component that seasonal component part are done now right then what you have to do you have to decolonize the data look at this this data to some extent seasonal data so you have to decolonize the data into a trend line format so seasonality has to be removed by dividing the data actual data by the index so then you will get a decolonized data this decolonized data which you will find say in step 3 once you get the decolonized data using this decolonized data you use a trend line or regression line there you will find the trend of the data as per the pattern of how the data has behaved in the past accordingly we will get the forecast but in a trend line manner because you are using simple regression line or trend line analysis so you will get it but that is the forecast based on your decolonized data because the seasonality you have removed seasonality you have removed right so you have bought a decolonized data how to do that I will tell you and then you have prepared a decolonized data by removing the seasonal index by using the seasonal index or removing the seasonality and then you use the trend line you get the forecast that forecast is nothing but your forecast using the trend line analysis or using decolonized data and then at again this forecast cannot be the final forecast you have to multiply with the index again with the index again so that you get again exact pattern or seasonal pattern of the data that means in a simple manner I can tell you that suppose you have a data like this you have a data like this and suppose this data has a seasonality and trend and you want to make forecast for the next say next year so in that case your first you calculate the decolonized data so bring this particular peak into here bring this down here decolonize the data this steps I will discuss in a Excel don't worry and then this this decolonized down all down you bring it up all up you bring it down by dividing the index effectively you found a decolonized line so you can see that let me put a different color you will get to know look at this here you found different type of point look at the yellow points here you are actually getting decolonized data you got the decolonized data so this data you store this this is nothing but your decolonized time series look at this this is nothing but a decolonized time series so now what you do using decolonized this data and the trend line analysis you make the final forecast for this particular year that you want to calculate right for this particular year you want to calculate and then what you do this is not your final forecast again you multiply the index for each quarter you index and then you will get the decolonized data again say like this the way you have got the previous data your forecast also will come like this this is the final forecast so how this adjustment or decolonization and again trend line and the adjustment final adjustment are done let us illustrate that through example of time series data so here is the one data sets I have bought for the illustration purpose if you look at the data pattern here so suppose I have we have 3 years data year 1 year 2 year 3 right and say quarter wise data I can show you the monthly data also which follow seasonality and trend together but let us focus on the quarterly basis data so you have the 4 quarter data every year and there for these quarters I have mentioned for our understanding as a 1 2 3 4 5 6 7 8 9 10 11 club suppose 3 years data you have you might have 4 years 5 years data that will be easier for you to do the calculation here suppose minimum 3 years data you have and with this data will make the forecast for the final year right for the 4th year will make the forecast using the decomposition method remember here we are not using the winter's hold method we are using a alternative method which is very popular in industry that is called the multiplicative decomposition method so now look at the data pattern first you need to understand you have to draw the graph of the data whether this data really has a seasonality or not or the data has a trend or not I have discussed that in a previous many sessions about that first you have to understand the data behavior here also suppose if you look at this particular data you see for every year you know in first quarter say that means April May June this first quarter the sales are quite low fifth that means again first quarter and then here look at here sales are quite low so every year this first quarter sales are you know quarter 1 quarter 1 quarter 1 sales are quite low but if you look at the data and third a third quarter look at the third quarter data quarter 3 if you see look at quarter 3 look at quarter 3 they have a quite high sale so that means in every year the the third quarter is coming out to be a high sale and first quarter are all having low sale so suppose this is the data pattern and based on this data and the graph we could realize that the data has a seasonality and also first confirmation that data has a seasonality so seasonality we understood the data involved seasonality now think about that whether the data has a trend or not if the data does not have trend then only to use the decomposition method you can go back to the you know simple quarter average method or say simple average or normalization process and we can make the forecast which is very good but now we are seeing the data and we want to see whether there is a trend also not uptrend or downtrend look at the data it started with 108 within 3 years it is a data with 168 and on an average if you see the data pattern they have a uptrend also look at the data even if you see the graph you know it is to some extent it has a uptrend also in another graph the uptrend data are there or not but here we could see the data has a uptrend also so this confirms that the data has a seasonality and data has a trend together so both when the data has both seasonality and trend you cannot use we cannot do you say hold model you cannot use a you know simple quarter average method for to bring the better accuracy you cannot use explicit method you cannot be moving average model also not a single model will come up with the best accuracy with better forecast so the only option is that either follow inter method or this decomposition method so let us go to illustrate the decomposition method in order to illustrate that I will go to excel directly and this five steps that I have mentioned here this five steps I will elaborate through numerical examples now let us go to the excel and let us illustrate the multiplicative decomposition method through this particular numerical examples I have done all the you know illustration here I believe the excel sheet is visible to you look at the data here the same data that we have illustrated this for you know inter method also but since it is a alternative to inter method the decomposition multiplicative decomposition method so we will take the same data and we will find the results so here quarter wise data are been given here now look at here same data so first step what is that let me delete this forecast value I will not put the forecast value here now and the end will get this forecast value now first step is the you know centre moving average so how we have calculated centre moving average look at the first step so take the average of first four quarter look at the average of first four quarter you drag to the data now in the next column what do you do take the average drop the first period like moving average drop the first period take the next four period and take the average effectively you are including the first period of the next year look at here so you taken the average of that. Now, if you take the average of this value 131 and not 133, these two simple average, you will get the center moving average for third period. So, this is the average value for third period, but the center moving average, two times you have taken the average and then you have taken the average of them. So, this is called the center moving average. What actually you have done? This process can be illustrated in another manner. In column number G, I have done it effectively since you have the data to some extent uptrend and as well as the seasonality also. So, you are trying to integrate the impact of the data in a sequential manner. So, here you can see the center moving average for the third period. From third period onwards, you have to start. What you do? You take the actual value of third period, look at the C4, actual value of third period, then actual value of the preceding period, actual value of the succeeding period as it is, then 50% of the first quarter and 50% of the fifth quarter. Fifth quarter means again first quarter. So, effectively you have taken all quarter data, but you are taking, by total by 4, you are taking the centered average for third period. That is the center point of the data from 1 to 5. That is the center point of data from 1 to 5. So, center point among them is the 3. So, third period, so third period onwards you have to start. For example, if you take the monthly data, if you have 12 periods data, your first period of center moving average will start from July month onwards. Because that is the middle point of the data. Initially, you will have the data and preceding data and the succeeding data and July will be the center point. From there, you have to draw your center moving average. So, whatever the process you can follow, not a matter whether quarterly or monthly as it is. So, this first average, first simple average, first fourth period and second fourth period and then you take the average of them, you will get the average of average, you get the center moving average or you can follow this calculation also like 50% of the first and the 50% of the fifth and middle three as it is by 4. So, all will be the same. So, this way, we have calculated the center moving average first step. Now, here if you see what happened, look at suppose you know either F or G, anyone you can follow suppose G. So, here you can see the average value for third quarter. Here we found, again here we also found the average of third quarter. This is third quarter. Actually, third means seventh. Seventh is nothing but the third, right? Seventh, three, seven and eleven are of same, but based on the three years data only, we can get only two representative of third quarter of every year. So, third and seventh information we found. For seventh period, you had actually 159, but the average center moving average you found here is 141, not 159, you found the center moving average 141. So, two data you have, right? So, it is the two references. From that, you will get the index. Objective is to get the index. Look at there, the blue one. Objective is to get the seasonal index. That you are again trying to find, right? Through center moving average process. Simple quarter average also you can follow, you may get to get the index also, but here people follow the center moving average because they try to find the interlink among the data, right? Correlation among the data through the center moving average. But you, it is not mandatory that you have to follow that. You can follow simple, you know, quarter average data also to get your index. Ultimately, the goal is to get the index. So, then you can desalinate the data, third steps, and then you can get the trail line, and you get the forecast through trail line through regression, and then again you multiply the index, and then you get back your final forecast. Like again, you go back to the zigzag pattern. So, now here you see, so this data you found and actual data for third period is 150. You divide the actual by the average data, center moving average data, you will get the index. Here in column number H, we found the index actual by the average data, right? So, we found the index, like, you know, in winter method, we have used similar, like, actual by the average of that period or that year, we got the initial value of index. So, here also we found the index, right, signal index. But here, you are preparing some center moving average process to get your index. So, now, you got one index. Again for seventh quarter, seventh period, you can get one more index, right? Like 159, by the center moving average of that period, 141, you will get another index. So, which one to take? 1.13 or 1.12. Both are having more than one. That is because third quarter, for this data, third quarter of every year has a higher cell. So, we are getting index more than one, not a matter, but which one to take? You take the average of then again. So, here we have taken the average of these two as the final index for that particular quarter. So, now that is fixed. That will be even for fourth year, fifth year, sixth year, third quarter index will be this one. It is fixed now. For every year, you know, index is fixed now. So, in third year, 11th quarter, the index will be fixed. Look at 1.13. I have kept 1.13 here also as a representative for 11th quarter index. So, index for third quarter is finalized now. Similarly, based on these three years data, if you have more data, you may get more index, dummy index, and you can take the average of them, which will be more appropriate perhaps. Now, similarly for second quarter, now for fourth quarter, you have got one index here, using center moving average, and you got one more index here also for fourth quarter here. Look at here. This is also fourth quarter index. So, two index you found, take the average of them, you will get the index for fourth quarter. Every year, fourth quarter index will get the average of them. So, this way we found all four index for all four quarter. Now, this data you have to cross verify that some of them is four or not. If not, then out of four, how much you have to do? Because total index cannot be for quarterly data. If it is monthly, then total should be 12. I have discussed that in the seniority chapter. So, now here out of four, how much I have done that calculation. In case it is not, how to do that? Look at here. Actual by total sum multiplied by four, out of four. Suppose, here we are getting almost same. So, we have not changed anything, same data. Now, we have pasted in column number K as per their corresponding quarter. So, look at here. This column first row, like here quarter one, 108 is the actual sales. What is the index for that? Index is not 1.13. Index is this one. Look at here. Fifth quarter means first quarter. So, index is 0.84. So, 84 percent. So, we have kept the index here for that quarter. We have distributed this index as per their corresponding quarter. So, here 0.96 is the second quarter. Then 1.13 is the third quarter and 1.05 is the fourth quarter. Now, these are the four quarter index. Now, we have finalized that we have copied and we have pasted for everyone. We have pasted for everyone. We have pasted for everyone. Here also, we have pasted because we will be using this data. Now, we have pasted this data for everybody now. So, index calculation are done now for every year. For fourth quarter also, we have stored it. Now, what we will do? We will get the desalinated data. We have to desalinate the data by dividing the actual data by the index. Let us understand. You divide the actual sales data column number C by the index. How can you do it? So, you take that actual data by index. You will get the desalinated data. Remember, in the PPD, I have also explained that you have a data like this. Now, you have to get this desalinated data. So, what do you have to do? This data should be divided, actual data divided by the index. This data will come here. This data will come here. This data will come here. This data will come here. So, you are getting a desalinated data. That data has to be desalinated by dividing the index. Like in winter also, we have done it. So, same way, you have got the desalinated data of each quarter data. Look at 108 has gone up. 108. Suppose this is 108. It has gone up to 127. But here, if you can see, it was 150, say, it has come down to 132. So, this way we have, but trend is there, uptrend is there, but we have desalinated the data. So, look at here. How come 132 actual data by the corresponding index? So, you got the desalinated data. Now, this data will copy along with the quarter data, quarter representative. Then we will use the trend line or regression line to get the forecast for the fourth year. For the fourth year, we will get the forecast here on the trend line through regression line. But that is not the best. Again, you have to multiply the index to get your exact pattern. But for the time being, we are getting the trend line. So, what do you have to do? You copy this data, copy this data and the quarter data and go to a new cell, new seed. I have here, I will show you. Suppose here you can see, look at here. So, this data we have pasted here. You can paste also. Look at this data we have pasted here and the corresponding quarter. Now, we will calculate the index for the next quarter, next fourth quarter. So, the representative as X are 13, 14, 15, 16. We will calculate. But using this data, we will use the regression analysis or trend line analysis. Then we get the intercept and slope. Let us go to data and go to data analysis. Come to regression analysis here and then click OK. So, here you select the Y range. Y range is nothing but the sales data, right? The trend line data. Look at the graph here, this trend line data, right? Then X range is nothing but your representative of the quarters, 12 quarter data you had. And then level you have to select because quarter and sales information you have also put the first row output cell. Suppose you already have here. So, I will paste here for your information. I will paste in this particular cell, right? To replicate the iteration or the calculation. Now, we found the forecast here. Look at the same data. Look at the intercept and slope 124 and 2.34. Same also we found here. Look at 124 and 2.34. Look at here. So, now I can skip this. I will come back here because I have put a color here. To save the time, I can show you from here now. Now, we got the intercept and slope. What will be the forecast? Y calc 2M. Look at here. Y calc 2Mx plus C is the general trend or A plus Bx. So, this way we have calculated Y calc 2.34 into X plus 124 is the intercept. And you may get the forecast like this. Look at here. Forecast for each data. Look at 13 multiplied by the index. A 13 is the X. Or I can show you one calculation here also. It would be just one calculation I will show you. Equals to M. This is M. Look at the formula here. M into X. What is X? Represents it is 13, right? Next year trend line you are making forecast. Y calc 2Mx plus C. C is how much? Intercept. 124. You got 155. Similarly, for second period or say 14 period, we will get M dot X. X is nothing but 14 plus C. C is the intercept. This way you will get the trend line forecast. So, this forecast we found. Suppose here we found this forecast, right? Let us copy them. Copy them. You can calculate here also. Just come back to the data where it was. We were here, right? So, just paste this data here. Look at here. Paste this data as a value. Paste this data here. So, this is the trend line forecast. This is not the forecast. Look at here the trend line. The trend line forecast we have made here as per the line, as per the decentralized data. This is not the final forecast. What is the last step? Look at the using the decentralized trend line to identify the trend. So, trend we found now. Fourth step are done now. Now adjust the seasonal index with the trend line data to get your final forecast. So, what will be your final forecast? You already have the indexes, right? So, that use these indices and get your forecast. Suppose here you can write in other cell also to get the forecast. So, forecast value will be final forecast. The trend line data, look at suppose this value multiplied by the index. It may go down or go up depending on the weightage. So, this 155 probably it will go down because the weightage of that index is less. Thus quarter is less than 1 multiplied by the index. That is it. Look at the final forecast 131 is the forecast for first quarter of fourth year that is 13 quarter. So, which is you know to some extent look at 108, 116, then 123, 131. It is up trend, but it is less. Now if you drag this, the calculation with the index multiplication will get the forecast for all four quarter. Look at here. Forecast for all four quarter. This is the final forecast. So, effectively you will get forecast like you know similar pattern of say you know say like this, like this. I will show you, I will show you the graph. So, here you can see the final forecast for all four you know period. So, this is confirms that you know your seasonality is also maintained. Look at third quarter value 180 which is higher than everybody and fourth quarter is again down and first quarter is down. It is following the trend, it is following the seasonality also. So, you will get the forecast like this. This is what the decomposition method is. It is easier to understand what you have done to summarize. You have taken the center moving average only and then you have calculated the dummy index, but you got a repeated dummy index. So, you take the average of them, you will get the final index. You can follow quarterly average also, but traditional process says that center moving average need to be followed because you have to take the interrelationship among the quarter. So, every time you are taking some initial value and the older value and the forthcoming value and you are taking the average and the average of average you are getting the center moving average which will start from the third period, but for monthly data you have to start from the July period. So, now you got the seasonal index and then that means this step is done now. This seasonal index and the actual data you get your decelerized data, third steps. So, you get the decelerized data, now trend line data, look at the trend line data, then you use the regression line or trend line, you get the forecast using simple vicals to mx plus c trend line analysis. You get the forecast of trend line, that is not the final, you have to multiply the corresponding weightage that is index. Again you multiply, you get the exact forecast which is nothing but this forecast. Easier, much easier than you know winter method, but I have tried to illustrate both winter method and you know hold method. If you look at the hold method here, look at the graph of hold method. Look at here, I can delete this, look at the graph of hold method, it is also very good right, it is also very good. Look at here the forecast, it is also following the similar trend, look at here, it has gone down and then up the winter method forecast for the same data. Maybe output may not be the same, here you can see the RMSE value is here, but if you look at the you know multiplicative method, the RMSE what is, for the RMSE I have also calculated here the RMSE, let me show you the RMSE calculation. The multiplicative method error, if you unhide it, look at here I have let me delete all this data. You can see the forecast using multiplicative method and the corresponding regression line. Like if you draw the regression line from the older data also, it may not be the same like the trail line data, because you are now multiplying this, we are getting this value by y equals to mx plus c calculations, it may not be the same with the decolonized data. But you got the regression forecast, forecast with the trail line and then again you multiply the index, the index you multiply, you will get the actual forecast as per your forecast strength, as per your decomposition method process. So, column number n is your final forecast, let me put a color, column number n is your nothing but your final forecast, as per the say decomposition method. So, this if you take that and the corresponding error if you take and the square of them and the RMSE, how much RMSE you are getting? 1.56, look at the RMSE of decomposition method 1.56. I am not saying that this is the best always than you know winter method, but it has a good merit also. Look at here for this particular data, your RMSE is lower than you know winter method. Look at the winter method, RMSE 2.66. We have optimized this, whatever the value you take alpha bit of, now here you can see the RMSE here it is for a whole to model for the same data 2.66, but for decomposition method it is 1.56. So, definitely you will recommend this forecast for this particular data sets throughout decomposition method rather than you know winter method, but both has a merit in industry whichever people want. You can now illustrate, you can carry forward that particular model and you can illustrate the data and you can make the forecast for the most complicated data sets that is called to some extent signality trained put together and you can make the forecast. So, this is what two method in the previous session we have discussed detail of winter method and this session we have discussed the alternative to winter method that is called multiplicative decomposition method. I believe it is clear to everybody. Now, one more examples I will have to pass to you, you can practice that at home. Suppose, data are following monthly seasonality, suppose. So, in that case you do not have 4 quarter, you have 12 periods say for every year. Suppose, you have a 3 years data and then monthly data you have in that case if you follow decomposition method, if you follow say you know multiplicative decomposition method you have to calculate 2017 forecast then how to start with. You have to calculate the centre moving average in that case from which month you should start your CMA calculation centre moving average calculation to get the index for that particular month first. In quarter lever you have started from third quarter right, but here you have to start from seventh month because you know you have to take equal weightages of seventh adjectives and then past say 1, 2, 3, 4, 5, 6 months and the following 6 months, 5 months in the back, 5 months in the subsequent periods or say months and then 50% of January and 50% of next year January. You divide by 12, you will get the index for July. You will get the index for July index. Once you get that July, you drag it, you will get the index for August, September onwards. So, for monthly data you have to start your CMA calculation centre moving average calculation from July onwards, but for quarter leverage data you have to start from the third quarter. The excel you have shown you can do that similar calculation process for same procedure you can follow for monthly data also. So, from July onwards you start your index calculations and then you drag that for all the data, all the periods been given and you calculate the index. So, once you get the CMA centre moving average and then you get couple of index you will get using that average you will get the final index and then divide the monthly data and what you have to do you know, you have to start say you know your month and the data set. So, in that case this all this will become 1, 2 to up to say 12, this is nothing but 2014. Then for 2015 it will start from 13 to dot, dot, dot, dot say 24. Then for 2016 it will be 25 to say 36. So, year 1 data you have, then year 2 data you have, then year 3 data you have to rewrite in your excel, all this data here, all this data here. So, once you have taken this then you calculate CMA same way the in the excel for process that I have shown you like execution process. Then you calculate the CMA and then the index and then the decentralized data and then the trail line and then again the final forecast using trail line and then multiply the index you will get the forecast for the year 2017. But how many data you will get forecast 12 months data for 2017 like say 37 will be the first month of 2017, then up to 48. So, this forecast you have to calculate for this particular representative or month say 37, 38 up to 48, total 12 months forecast you can do also. You can practice this data for monthly average monthly data using decomposition method and you may get to know how much will be the forecast for 2017 also, but the procedure will remain same. So, this is what the decomposition method which is very you know easy to understand and popular in the industry also. So, now with that let us conclude the session of multiplicative decomposition method and the comparison of winter method and decomposition method. I believe it is clear to everybody.