 This analysis we have done for monthly basis we illustrate that for quarterly basis in excel so that you may get to know all the steps of calculations in an effective manner. So let us see same process actually suppose you have a quarterly data for say last say 1, 2, 3, 4, 5, 6, 7, 8 years data you have and quarterly data are been given to you now. So you have to make forecast for the next year 2007 say 2008 say 7 only. So what is the steps now as per the previous examples what you have to do you have to calculate the quarterly average first, quarterly average first, quarterly average for each quarter same way like monthly average you have calories and write it and I have removed take the transpose of the I have taken the transpose of the data now quarterly I have kept in this column manner. So take the quarterly average of the data then this is quarterly average this is not the final index the third step the index is that you know what you do then you take the overall average overall average of the data. So you will get some value suppose here you will get some value you divide each quarterly data by this particular value overall average you will get the index index for each quarter this is the up to third steps. So you have calculated look at the average of each quarter step 1 then the overall average by taking all the data put together how many data you have now say 8 into 4 32 data you have now. So total 32 quarter you take the average of them you will get whatever the value you will get quarter average divided that by global average or overall average you will get the index it is 91 percent here it is 1.29 30 percent extra here it is 8 percent extra here it is 30 percent down look at quarter 4 data if you look at the quarter 4 data the sales are low but quarter if you look at the quarter 2 data sales are high suppose it is a agro product or fertilizer product and Mansoons they have a high sales where quarter 2 belongs to Mansoons season so they have a high sales and in September also to some extent you know processor going on for the agriculture sector so you know they need more fertilizer on the agrochemical sector so you know in quarter 2 and quarter 3 you will find good amount of sales suppose. So therefore index are high but interestingly here in the previous case you had 12 period right like January to December and that is been repeated every year because data are been given monthly basis so total 12 index you calculated right and the sum of the all index should be 12 but here you have 4 quarter now so here the sum of total 4 index you will have to calculate based on the previous data or past data but the sum of them sum of them should be 4 now in earlier for 12 months data you had a total 12 the sum of the index should be 12 but here it is 4 if not 4 how to do calculations I will show you in excel the proportionate part suppose here sum of them is 4 you got the index and 5 1 9% less in January quarter was a first quarter April quarter and 30% extra and this we found the index say this is what third steps you got the index what is the next next is the make the forecast for the annual year forthcoming year that we are going to calculate what is the next year 2007 right say 2007 question was given to us calculate 2007 and 8 look at the 2007 annual forecast I have written as 98 and 2008 annual forecast 100 how come we found using regression line or the train line I will show you the calculations we are following the quarterly average or simple average method let us see so what you have to next step is the annual forecast so what you do 2007 take the annual data of each year annual data of each year you take the sum you will get the annual sales or whatever or demand say for the 2019-99 then 2002 2001 this way you can calculate the sum of each year annual sales and this is what your annual data sets for each year use the regression line say 2009 say 1999 you can predict as 1 2 you can consider as a representative say 3 4 like this way and then you can make the forecast this is a Y and this is suppose X so you use them as a independent or dependent will use the regression line or train line will get the forecast for the 2007 2007 will forecast you will get that would be your annual sales of 2007 but that is not the final what you have to do you have to divide that by 4 because 4 quarter are there so you will get the you know the annual state line prediction 2007 state line prediction right as 98 by 4 then 98 by 4 then 98 by 4 then 98 by 4 so this is what the steady forecast or vision lights forecast but that is not the final you have to multiply the index again so it will be like this it will fall down because not 100% you are utilizing it is below than 100% so it will be like here then it will go up then it will remain between like this and then it will again fall down so this is the pattern of the data because this particular second quarter it has a 30% extra in first quarter it has a 10% 9% less so the forecast will be like this the what about the forecast we are getting multiply the corresponding index same way we will get the forecast for the next quarter here is the calculation you can see so you can see the index the average quarterly data we have calculated here and the global average we have calculated here whatever you can take I will show you next cell then index we have calculated and then using regression we got the 2007 forecast 2008 forecast we will get 100 and then if you divide that by 4 you are getting equal prediction like distribution but this is to some extent as I mentioned it is just decelerized prediction on the straight line but that is not the final predictions what you have to do you have to make the forecast as per the index the weightage so multiply the weightage multiply the weightage we will get the index like this this is what the forecast for quarterly data let's illustrate this using Excel now let's go to Excel so we have come to the Excel sheet now so this quarterly data monthly data we have already understood quarterly data also understood but I will illustrate only this quarterly data analysis for your detail information so let us see the data set I believe the Excel is visible to everyone so here you can see the you know all 8 years data are been kept whatever the data I have shown in PPT that I have kept here in Excel now firstly with the average of each quarter, so I have taken the average of each quarter look at here average of each quarter, average of each quarter, average of each quarter step one then step two global average. Average of all otherwise we can take average of all whatever you can take all them we can calculate all them average of all this data also you can calculate all will be almost same this is what your calculation of the overall average data, right? The average forecast of the overall data. So, we found that then what is the next step, third step index? We divide the quarter average by the global average. So, you will get the index, correct? Index for each quarter we found it. Look at here, 29 percent extra here, first quarter 9 percent less, 91 percent. So, 9 percent less, the sales are 9 percent less as compared to second quarter. So, this is what I have shown you, but if you look at this index now, this part I am going to explain in excel now, which I have not discussed in the PPT, but here you can see the index that we have calculated the four quarter, the sum of them should be 4. This is my data, because four quarter you have 4, for the monthly data some of them should be 12, I have already mentioned that. In case if you have a data say missing data or some outlay or some calculation mistakes you have done or some you know analysis are not able to do because of you know, non-affiliate of data or you have put some interpolated data or average data. And in case in case you found that your average total sum of the index for each four quarter are not 4, what you do? You calculate the proportionate value. Suppose this value you are getting say 3.75, suppose you found, suppose you will not get most of the cases you will get 4 or say 4. something, suppose you found this, then in that case what will be your actual index now? So, what do you do? You take this say 0.9157 out of what? 3.75 into 4. So, out of 4 how much you calculate that? That is a new prediction, new index for your final index for that particular quarter. Similarly, suppose here you got 1.29, 1.29 by say 3.75 or whatever higher the not equal to 4, divide that and then multiply by 4. Out of 4 how much that proportionate value you have to calculate? That is your final index. Suppose here we found in the previous stage itself, in the first stage itself we found that you know total sum is 4. So, what we need to do is all this calculation are not required now. Suppose we found the index, so step 3 is done. Now, we have to calculate the forecast for 2007. So, you have taken this entire data. What is that? These are the sum of total sales of that particular year, sum of total sales of that particular year. Correct. So, you have taken this data, just copy this data and go to a new sale. Suppose a new seat and suppose paste here, special value what is this? This is the annual sales. And this is the years. So, first was 1999 and then second was 2000. So, this way you rather than 99, 2000, I am for my simplicity, I am writing this as my representative of years. 8 means 2006, 7 means 2000 say you know 5. So, this I have calculated now. So, this is my y, this is my x. So, what we will write? I will consider this as a x for my understanding and this is my y. Now, I will put the train line and regression, I will calculate 2007 forecast here. Effectively, my objective is to calculate the 2007 forecast using the data of annual sales. So, I will represent 2007 as 9, but I will see that later. What I will do? I will go to data analysis and then I will go to regression, it is here, go to regression and then select your y range, this is your y range. Then go to input data, select your x range up to this, select level because first rule is considered as a level, sales and select a sale where you want to make your forecast. Now, the forecast value has come now because the intercept and the slope. Then we put the color here. So, this is your intercept and slope. Now, what you have to do? You have to calculate 2007 forecast. What is the forecast value? y equals to mx plus c or a plus bx. So, this formula will follow train line formula and then we will calculate the forecast for 2007. What will be the value now? y equals to m. m is the slope. So, slope into x. What is x? 2007, but here we have taken 9 as a represent of 2007. x plus c, what is c? The intercept is 80. We found the forecast as a 98. So, this 98 I had written in the PPT. So, this is the 2007 forecast. Now, I am going to calculate 2008 forecast. So, 80 represent it will be 10 sale and then what you do? m into x plus c. You will get the annual forecast for 2008. Look at 100. So, this data I have written in my PPT from here. So, this way you can calculate the annual prediction step 4 now. Come back, look at this analysis I have done here. You can get to know how I have calculated all this. You know R square values, standard error also if you want to see, you can see all these things. So, now we have found all this prediction for 2007 and 2008. These two data we will copy and we will go to the master 5. So, come here. So, look at here now. So, 2007, let us understand 2007 only. So, annual sales we found as a from the regression line as a 98, but this is the annual sales of 2007. What you have to do? Now, you have to divide that by 4 because quarterly data unit. So, actually what happens? 2007 say, this previous data sets you have used and you have made the forecast. What is your forecast? It is 98 by 4. So, it is coming to be 24.5, 24.5, 24.5, 24.5 for 4 quarter. But this is the additionalized prediction. You cannot take that as the final forecast. You have to multiply with the index. You multiply with the index, it will come down to 22.44. So, 22.44. Here, look at here. So, you are multiplying now. Look at this. Index into the on an average quarterly forecast. So, 24.5 into 0.91. We are getting lower than that on an average. So, how much additionalized data lying? So, 22.43 is the final forecast for 2000, first quarter of 2007. Similarly, second quarter has the highest index, 30 percent extra, 29 percent extra. So, you can get the forecast is above than 24.5. It is coming to almost 30. How much? 31.79. So, this 31.79 will go higher. Say, suppose here, the effect of your forecast are coming like this. Then third quarter, how much? 8 percent extra. So, 26.56. So, here it will be like this. Then fourth quarter, 30 percent less. So, 70 percent weightage only, 30 percent less. So, it will fall down further here. So, your forecast will be like this. Look at this. This will be the forecast for every year. Even the previous data, if you draw the graph, it will be like this. But now, for 2007 forecast, you found this is what the forecast throw, simple average or quarter average method. I believe it is clear to everybody. Now, for 2008, if you want to calculate, you already got the prediction for annual prediction, but 2008 at 100 divided by 4. So, you will get 25 for each quarter. But that is not the final forecast. You multiply the index that you calculate on index. Let me write down index. This index, you know, this index row, you multiply the corresponding forecast on an average. So, you will get, look at this. On an average, 100 divided by 4 multiplied by index. And if you drag it, you will get the forecast for 2008 also. You are getting forecast through quarterly average data or method or simple average method. And you have understood the process of seasonality index and the corresponding forecast if the data has a seasonality. So, this is all about, you know, simple average method or, you know, quarter average method. Now, we will extend our discussions to normalization method. This is another method. Sometimes industry follows this also. It is quite easy to understand. And it has only two, three steps to, you know, to capture the process of normalization method. But it is different than the quarter average method. In quarterly average method, you divide, get the index for each quarter. Here also, you have to calculate the index. But the process is different by normalizing the matrix. There, you do not normalize the matrix. You take the quarter average or monthly average and divide by the global average. That index is your index, right? And then, same process. You are taking the annual data and then you are dividing by the quarter, number of quarter and then multiply the index. These are the quarterly summary, summary of quarter average. But in normalization method, what do you do? Let us understand the process of normalization method. It is easy. Then, here then quarter average also. But both has a merit. Whatever you want, you can follow. Let us understand the normalization method process. So, first, you take the, you know, the total sales of, say, you know, first year. Then, total sales of second year. Then, total sales of third year. Once you get that, you, what do you do? You divide the, you know, quarter one data by the total sales. You will get the weightage for that quarter. Like, 53 is total by, say, 150, suppose. So, total annual, first year sales is, say, 150. Let me see how much is the total annual sales. Look at here. Total annual sales is how much? 157. So, it is nothing but the sum of all four quarter. So, first year annual sales you found. Second year how much? 173. Third year how much? 189. So, these three years annual sales you found. Now, you calculate the weightage for each quarter. So, 53 by 153. So, you will get 53 by 157. You will get the index for, you know, for first quarter, 34 percent. Then, second quarter, 14 percent, because sales is only 22. So, 14 percent. Then, third quarter, 24 percent and 29 percent. Total should be, you know, on an average one. Because, out of 157 you are distributing. So, weightage should be one. So, but we will discuss that later. So, now, suppose you got the weightage, normalized value for each quarter. Similarly, for second year, you do like 58 by 158 by 173. You will get the index for that. And similarly, for all the other quarter, you calculate it. In Excel, I will show you, you get the calculation, the index normalized value for each quarter. Now, what you do? Next step is that, you know, you take the average of normalized data. So, weightage, that is the final weightage. For each, first year you have calculated the weightage for each. As per the sales of the annual data, you have calculated the weightage for that. For second year also, you have calculated the weightages. As per the sales of the second year, total sales. So, for third year also, you have calculated. But now, you are getting on an average, what is the total sales? So, value fluctuation you are removing and you are getting on an average sales for that particular quarter, the weightage. Or you can say the index now. So, here you had 34, 34 say 33, you are getting 30.337 as the index. That is the index for quarter 1. Q1 index you found. So, for Q2, it is 14%. For Q3, it is 23%. For Q4, it is 23%. Total should be 1. There you had a 4, but here it is 1. Because out of annual data, you are calculating the weightage you are calculating. The weightage, the proportionate value you are calculating out of annual data. So, therefore, here, but that you calculate the quarterly, out of quarter. Like you know quarterly data, average by the total global quarter average. So, therefore, you found the quarterly data. But here you are calculating out of, like you know, therefore, total sum was 4 that time. Because this is also quarterly average, overall average is quarterly and you are dividing the quarterly average data by the global average or overall average. So, therefore, whatever the index you are getting in quarterly average method, that index is out of 1 say. So, 1 plus 1 plus 1 plus 1, total 4 or on a fluctuation if you put together, it will be 4. Because 4 quarter you are calculating separately. But here on an overall basis, annual basis you are calculating. So, out of annual sales, how much weightage you are calculating? So, therefore, here total index of sum should be, sum of the all indexes should be 1 only. Remember the differences. So, you found the index for each quarter now, based on the annual sales data. So, this is the next step now. The next step is that similarly, like the previous quarterly method or simple average method, take the annual sales. Let me delete this now, erase this. Now, you have the annual sales for each year. You calculate the trail line or regression line. We will get the forecast for say, year 4. We will get the annual forecast for year 4. Multiply that with the index. That is it. Look at here. So, let me delete this. You will get a better clarity. What you do? You go to the next. Look at this. So, total sum should be 1. As I mentioned here, let us write down here. Now, what you do? Using regression line or trail line using this data, we get the forecast for the next year. Simple, similar like first step is same for everybody. You get it. Then you do not have to divide this by 4. Because as it is annual sales and this you have index you have found based on the annual sales. So, out of 1, how much that you have already calculated? Directly multiply this with the index. Directly you multiply this with the index of annual predictions. You will get the forecast for each quarter. This is what simple process we call as a normalization method. Because you are normalizing a matrix with the data, with the annual sales and you are getting the index accordingly. So, therefore, these process are called the normalization method and it is easier than quarter leverage. But whatever you want, you can follow. Let me illustrate this in Excel also quickly. So, you will get to know because the matrix normalization method here. Same annual data, let me increase the font size. I believe it is visible to everybody now. So, here initial data we have. So, annual sales you have calculated first step. Then divide the quarter data by annual sales. Look at divide the annual quarterly, each quarterly data by the annual sales or respective sales. So, you get the index for that. So, this is the intermediate index. This is the normalization which has been done. Total final advantage you have to calculate by taking the average of the all quarter for easier. Because interrelationship has to be brought into the system. Individually you cannot make a prediction. So, now, because data pattern has to follow and how the index for each quarter cannot be individually calculated. It has to be calculated based on the taking into account of the all the data. So, I will discuss that also again at the end to summarize the entered process. So, now suppose this is what your index for that particular quarter intermediate and then for second quarter you can see it is total sales of that quarter by the total annual sales. This way you can calculate the index for each quarter. This is the normalized data. And then second year also, then third year also and then if you take the average of all three, you will get the final weightage for that particular quarter. So, here it will be say W1, W2, W3, W4 or you can say index quarter 1, quarter 1 index, Q1 index, Q2 index, like this way Q3 index and Q4 index. So, this for index you calculated as a step 3 now as per your understanding whatever. So, you store it. The next same as it is you calculate the annual forecast. What do you have to do? You take this annual data, take this annual data, look at here annual data and you represent them as a you know 1, 2, 3 whatever and you make the forecast using regression line. You got the intercept on slope, take it and then you may calculate the forecast for the next year. How much it will be? 16 into mx. x is how much? 4 plus c, c is 141. We will get the forecast for the next year as 205. So, this is what the forecast for the next year. We have calculated here, look at here, look at the forecast for the fourth year. But now that forecast, you have to multiply the index. So, that forecast you have to multiply the index. So, this is the index that we have calculated here and the forecast here. If you multiply them, you will get the forecast for the next year quarter 1. Quarter 2 data of next year is 28, quarter 3 of next year is like 23% of 205 and 29% of 205. So, this is the final forecast for the next year. This is called the matrix normalization method and quite easy to understand. Whatever the method you want, you can follow. But both methods I have discussed and if you come back to it now, this summary of the excel I have kept here and you can see the forecast for the next year. Look at this. Quarterly data, quarterly forecast. All the difference here is that, you know, it is the index are been calculated out of 1 and there it was calculated out of 4. But one more thing let me explain here also and in the normalization simple average method, quarter average method, that suppose you may say that Sir, I will calculate the index for each quarter and then the next year round forecast I will get and I will multiply that. I will not take the average of that. You cannot do that. Like, you know, this index you can 0.34. You can multiply this with this here also. You can do it or annual sales like, you know, you can take this and this or corresponding forecast you can do it. But that is not the best way to make the forecast. What do you have to do? You have to take all quarter data and then the average and then you have to find the interrelationship. So, today we have discussed two methods. One is the, you know, simple average method or quarterly average method and the second one is the normalization method. Let me go back to that particular sheet. Today we have discussed the simple average method to calculate the seasonality and the signal index and make the forecast and also we have discussed normalization method. So, these two are the most popular method we have discussed and we have also illustrated in excel. In the next session, we will discuss if the data has a seasonality and print both. That means, if the data follow seasonality, only seasonality we have discussed today, right, how to make the forecast for the forthcoming year. But if the data has a seasonality and print together, seasonality and print, look at the this data, seasonality as well as the uptrend set. In that case, the process that we have followed today may not be the best in terms of accuracy or the reliability or the measure of accuracy calculation. So, what do you have to do? You have to upgrade your model from simple quarter leverage or say normalization method to a better model who can capture the seasonality and print together. There are very popular method. One is the decomposition method, another the interhold method, another the Arima model. All three are popular to capture the seasonality and print together. In the forthcoming sessions, one by one, we will discuss all three models when the data has seasonality and print both and how can we make forecast for the future using this type of combination of the data pattern. So, let us complete this today's session with this simple understanding of seasonality and the index calculation and the forecast and we will extend the concept with print put together is allowed with seasonality in the coming session. Thank you.