Forecasting Techniques: Trend and Seasonality-Corrected (Winter's Method)

hi everyone in this video we are going to focus on the trend and seasonality corrected exponential somatic method if we're going through an example of this technique I want to briefly talk about forecasting in general so in any observed demand we have two components systematic component and random component systematic component may include the level trend and or seasonality so to detect this we examine the past data and see if there's any trend and over seasonality so you basically plot the demand versus period graph and see what kind of behavior your data has so in our example suppose that we are operating a cafe at RPI campus and we want to forecast the demand for iced coffee so the table on the Left shows us the demand for iced coffee over 16 weeks so these are randomly generated fictitious values so we have the data for 16 weeks and now I want to see what kind of behavior this data set has so to do that I go to insert section find my graph and select this first one and right click ends I select data add and my y-axis is going to be my demand values say okay and update your x-axis is your period or beats in this case say ok and I got my graph so in this graph what we observe is there is some fluctuation so if the demand goes up goes down goes up goes down and so on so in addition to this seasonality we also have an increasing trend so even though these fluctuations occur we still have some sort of increase if you look at this pattern over here so this means our systematic component in this case is level plus trend times seasonal factors so we have level and season on the in our systematic informant so the question now is so if you look at this summary here so the question is what kind of forecasting method can be applied to this and the answer is winters model which is also known as trend and season of the corrected expression smoothing so you may ask me why not halts method because that doesn't capture seasonality so we have the seasonality and winter models chapters that and that for this reason we are going to use the formulas that will capture winters model so I have the screenshot shots of those um formulas and very basically going to see how we can apply them so the first step is to decision wise the data that we have since it is the since it is seasonal and it is not linear we cannot apply a linear regression model to it and the reason why we need linear regression is to initialize level and track values if you remember Holtz method to find the initial level and trend values we fit a linear regression model so we are going to use the same idea but first we need to DC's analyze our data so that we can apply the linear regression model so the seasonal eyes we have this formula if P is even we are going to use this formula if P is odd we are going to use this formula so what is P P is the number of periods after which the seasonal cycle repeats so how often my cycle repeats if you look at these so 1 5 9 13 so this cycle repeats in every four periods therefore my p value is 4 p is also called seasonality periodicity so my period value is 4 I'm going to type it over here so and since 4 is even we are going to use this formula over here so basically you are going to plug in your T value whatever period you're going to calculate it for and P is for you're going to plug it in and your summation and you have your DNP values so these highlighted cells are going to be my decision alized demand values i have the first two and last two cells blank because we have four of these in our cycles so start with d3 so the formula is b3 demand of period 1 + B 7 demand of period 5 and I'm going to need parentheses at beginning plus some of the ones in between times 2 because there's this tail over there close the parentheses divided by 2 times P okay since I'm going to drag this down I need to stabilize my p value and I put dollar signs in between B and 25 n to the beginning of be okay I calculate my decision lies demand for period day now I'm going to drag it down and compute all of mine this instantize demand values to verify why I stopped here you can check this computation so be 14 and be 18 so be 18 is the last cell so this 18 is love cell that I have a data in therefore I can't go any further and this is very important stuff you can verify your this in season wise demand in this way as well okay so we computed or this is nice demand and now we are going to fit a linear regression model to it but to do that go to data data analysis regression okay and say you're set your Y range so my Y range is the decision wise data and X range is the period corresponding to that and output range basically go ahead and select a range okay and okay so my range is this is a nice demand and X range is the week's corresponding to that and now I'm going to say okay and get my linear regression model okay so I have my solutions or intercept corresponds to my level initialized value and X variable is going to represent my initial trend value so copy them and paste here by using transpose so okay I find my initial level and trend values now I can find my decision lies demand for each period so this step is after applying the regression model and this is the formula that I am going to use so level plus trend times period so level plus trend times my week so I have to stabilize these values because I'm going to drag down and I want them to be constant and we compare our all this is a nice demand values the next step is to initialize seasonal factor values so initial values of these seasonal factors are computed by actual demand divided by estimated demand so the actual demand is in the cell divided by this is analyzed estimated demand drag it down you find your seasonal factors next thing by using these initial values I'm going to find my actual seasonal factors so we have a formula over here so this one is going to be used for the first four of them and the reason is that we have four of these periods in each cycle so si is going to be used going to be computed by this formula and you see there is an R the other year our is going to be found by the total number of periods in our time Verizon divided by P so 16 weeks we have divided by P equals 4 is 4 so R is 4 you're basically going to plug in your values and find these first 4 physic seasonal factors and to save time I'm going to go ahead and compute them so it is basically going to correspond to s 1 plus s 5 plus this 1 plus this last one divided by 4 because R is 4 and now easily drag it down and find your all seasonal factor relish ok so we got our seasonal factors and now we are ready to compute the rest of them and to do that we are going to use the formula over here so s is a bit by a smoothing constant very like to divide the demand by level and 1 minus 2 money constant times s from the initial period in this first computation so but before going through that I ought to also complete my level values so level value is going to be my alpha times demand in the corresponding period the cell divided by s okay close the parentheses plus 1 minus my alpha value close the parentheses times level from previous period plus trend from Chris period so I am going to stabilize these constant values so that it stays the same and drag it down ok for trend I am going to use beta so beta is in the cell times level from current period the cell - level from previous period because the parentheses plus one minus beta times trend from previous period okay so again stabilize these constant smoothing constant and then drag it down okay so you see some of these gave me an error and the reason why is that these values depend on the seasonal factors over here and since I didn't compute yet compute them yet I got this error and as soon as I complete them I'm going to get some values over here so let's compute s 5 so s 5 is going to be computed by this so if you look at this formula T equals 0 P is 4 so this index is 5 ok as 5 is what I am trying to compute so gamma times the t + 1 T is 0 which means d 1 divided by L 1 so I'm going to go to the first period plus 1 minus gamma times s 1 so I'm going to use the seasonal factor from first period okay so gamma put dollar sign to stabilize times demand divided by level plus 1 minus beta stabilize it times s from the first period okay computer and now simply drag it down okay I completed all of my seasonal factor rails using this from after the fifth period because my p-value is 4 and 4 plus 1 we have to go to fifth period to compute these similar seasonal factors and since level and trend depends depend on seasonal factor values then as soon as I computed their values I could compute level and trend values as well so I have my seasonal factors level and trend videos now it is simple to forecast my demand so forecast is level from previous period plus trend from previous period times seasonal factor it's a period one level times trend times seasonal factor right drag it down and you got all of your forecasted values and you may ask me what about period 6017 sorry so 4:17 you are going to use this formula so this is going to give you the forecast for how many periods you are ahead of your last period so this one is computed by you're going to stabilize your lost level and trend values okay so go ahead and put a dollar sign in between them and how many kids am i ahead of my last period one because I am computing period 17 but my last period is 16 so one period ahead and multiply it by s so that is going to be this one okay drag it down the only change you need to do is to update this value zone change it to two because we are two periods ahead good drag it down update this drag it down update this three and change it to four because we have four periods a hazard another way to verify periods 20 is calculated 20-16 for good so I completed my future forecast values as well so now the last step in my forecasting technique is to compute the error measurements so ever is the difference between the forecast value and actual demand all right nice so for MSE mean square error I'm going to need the square of each error value cell multiply it by itself drag down for M ad I am going to need my absolute value of error so for each cell compute the absolute value of the error term and last one is for M AP mean absolute percentage error and absolute value divided by demand okay write down and now I'm ready to complete my MSC ma B and M a PE values so MSE is basically the average of my error squared values ma D is the average of the absolute error values and ami PE is the average of these values so I computed all of my error measurements now since I used randomly generated alpha beta and gamma values now I am going to find the best combination of these values so what are the best alpha beta gamma values to find it I am going to use Excel solver so go to data solver and okay so choose one of these Android measurements in this case I'm going to choose ms e so I want to minimize my MSE value objective is to minimize and which are my decision so my decision variables are alpha beta and gamma because I am going to select those values to minimize my MSE value my constraint is alpha beta and gamma must be between 0 and 1 therefore my right hand side is 1 because these three must be less than equal to 1 okay okay and choose your solving method my solving method is going to be GRG nonlinear because my objective is nonlinear in this case since it includes the square of the error terms so so and you're going to get your solution so we found the solution all constrains an optimal cones are satisfied which is good and say ok ok so Excel solver tells me your best combination of alpha beta gamma is given here and every term is now MSE is now 1.3 so compare it with the initial values let's change them back now it is 2 point there are so see as we picked some other alpha beta gamma values we increased our MSE value but Excel solver found out the best one that will give us the minimum MSE value so this is how you find your best alpha beta gamma ray list so this ends this video thanks for watching

Channel: Supply Chain Tutorials

Views: 90,862

Keywords:

Id: gYYpS-LafcQ

Length: 18min 26sec (1106 seconds)

Published: Tue Oct 13 2015