Operations Management using Excel: Seasonality and Trend Forecasting

In this video I explain how to create a forecasting model in Excel for a data set that involves both trend and seasonality. I chose not to de-seasonalize data set before finding the trend parameters.
Dataset URL: Copy and Paste it into Excel or work in Google Sheets
docs.google.co...
I posted a follow up video in which you can handle multiple forecasts at the same time for multiple SKUs (Stock Keeping Units).
• Seasonality and Trend ...
An alternative method could be to use a multiple linear regression model. And I think the regression model is more practical. Yes, I have recorded a video for that as well. The link for the video is • Seasonality and Trend ...
These videos are created for the students at The College at Brockport, State University of New York. However anyone can benefit from them as they should work for any Operations Management class.

[@goforward12ify]

THIS VIDEO IS SO UNDERRATED, IT SHOULD BE ON THE TOP OF THE PAGES

[@arifrayhan1126]

exactly

[@AnkitPatel234]

Exactly. Love from India.

[@OscarFromIndia]

I agree

[@Rajdeepdey943]

Absolutely

[@zazisosibo6015]

Nothing but the truth there.I passed an interview practical with this video.

[@fabianl9465]

This video is literally a must for everyone that works as a planner. It’s one of the best educational videos out there

[@fsociety_]

Hy, did this help you in planning in real life? I am a Production Planner myself and I want to know which forecast method people normally use in forecasting demand in real life scenarios.

[@randominternetviewer166]

​@@fsociety_ It depends on the data. This one he presented works well if the data has obvious repeatable patterns that repeat every year, like the one shown in the chart. If it doesnt have that, this method will easily fail.

[@jamesjennings9438]

This is absolutely an AWESOME technique! The seasonal adjustment is exactly what I needed and I put this into actual use -- Thank you for helping me look good to my boss!

[@97NRBhgfdkjsvfdkjl]

Wow, incredibly helpful. I learned more from this video than I've learned from the actual class all semester.

[@bensmith6987]

This is much clear explanation than my prof who has 30 years of experience. Thank you so much.

[@zorkminer]

I don't comment much, but this video is amazing. I'm impressed that a video so short could be so informative.

[@mcanbolat]

Thank you!

[@rounaksinghbuttar9083]

God you're a genius. No one else has explained forecasting using Excel for Production and Operations Management as well as you sir.

[@mcanbolat]

Thank you!

[@godfreyharris4582]

Dr. Mustafa is a Genous. He makes complex work easy to do.

[@peterdoherty4558]

It was great when you taught me years ago and it's even better now that I have some real world experience. Fantastic!

[@mcanbolat]

Thank you Peter! Nice to hear from you!

[@selimcanpolat8664]

just predicted my future bills on excel w/ this method, thanks a lot!

[@matiuszhu8967]

Thank god i found your video man. You don't know how much I needed this before my interview. LIFESAVERRRRR

[@drinkbeerandchillwithmzohm4507]

You should get an award for this video sir 🙌🏿🙌🏿🙌🏿

[@Kentucky-ev7bd]

Very nice!! Love it!! The only thing I would say add is a quick explanation of key terms. Slope, intercept, seasonality.

[@daniel_ptt]

Sir, you are the man of career development, love your videos

[@shubhamraje6122]

Very well depicted. Follows all baby steps. Must watch for a newbie learner!

[@MohaAlraed]

thanks brother i will start my new job as an executive manager for a small factory and this is going to help a lot

[@MohaAlraed]

few question what if there are "controllable" factors that will effect the demand, for example, huge random advertising, or getting new businesses to order the products in large number I mean it will not be just seasonal, is there a way to determine the future demand? I have one in mind but not sure if it's scientific.

[@danelleduardorapozoramirez1030]

@@MohaAlraed my take is that since those events weren't seasonal you shouldn't take it into account when doing your forecast.

[@mcanbolat]

Couple of people criticized that I didn't de-seasonalize before finding the trend. No one can prove that the model will be superior if we de-seasonalize. Below is a link with a small simulation with random dataset. You can refresh it in Excel using F9 or you can enter a value in an empty cell to refresh in Google. I calculated the MAPE for both deseasonalized and not deseasonalized models. I have shown empirically that there is no obvious benefit of removing seasonality before finding the trend parameters. docs.google.com/spreadsheets/d/1YVjz1IZT8Hd489ZCifAIM5yTiuyk0qdcoCrdWIbBruk/edit?usp=sharing

[@lalu225]

Thank you very much, your video is very useful. Also, I tested the excel sheet that simulates the difference between the two trending approaches and it seems like the the differences in error is indeed random. Do you know if there are any papers or studies suggesting otherwise? Where did people get this impression from, that one needs to de-seasonalise before finding the trend? I'd be interested if this is true over say 100k trials - I'll attempt to run a python script to test it and check the distribution of outcomes

[@mcanbolat]

lalu225 I haven’t looked at the literature but if someone proves that there is no benefit it may get published. It would be interesting to try this on a large number of datasets and report empirical output.

[@helloh5727]

Thabks for the videos man, I am an operations and supply chain management major and your videos are amazing.

[@ammaralolaqi7627]

Thank you a lot Dr. Mustafa for this great video. I have 2 queries: - is there a name for this forecasting method ? - What about if you had a data set for one year only, 12 months ? (Means January do not repeat etc...). Anything will be applied differently then ?

[@kellyzimmermann3391]

This has helped me so much!! Thank YOU Mustafa!

[@haveyoumetpia]

I got everything that I need. Well explained. Thank you to the creator of this video. New subscriber here 🤗

[@PradeepRamesh-ew7uh]

Simple to understand and Great video

[@vishalkulkarni9227]

One of the great video regarding forecasting. 👏 Thank you so much need more video like this. Thank you so much.

[@josephmastropietro5810]

Incredibly helpful. Great walkthrough, I was able to replicate the process with my data and drive some great discussion.

[@dianap.carlos4934]

Excellent video. Thank you very much for your help Dr. Canbolat.

[@chrisdireduryan]

This video is so educating it made me post a comment. Couldn't be better. Thanks a million

[@mcanbolat]

Thank you!

[@anihusein6168]

Dear Mustafa, you are great.

[@danelleduardorapozoramirez1030]

Amazing video, i was looking for something like this for a while. Subscribed.

[@ahammadgalib5810]

Awesome video, easy explanation, great presentation. Thank you very much. 👍

[@woodypham6474]

I hope that you will keep doing forecast for different kind of business sales. That would be great

[@mahadev2023]

Thank you Dr. Mustafa. You described very well.

[@Chanheegogo]

Thank you so much for sharing this technique. Very clear and simple to understand for even beginner for demand planning Thank you so much. I will try to utilize this method in my workplace

[@iPururin]

Straightforward and practical presentation. Thank you.

[@azizony123]

Awesome Mustafa, very well explained........

[@debanshumajumdar9790]

Dear Mustafa sir, the whole KZbin community, and the whole world are blessed to have you here. You answered all the questions that were coming into my mind as the video progressed. It was like you were reading my mind and answering all the questions that I have. Thank you sir

[@mcanbolat]

Thank you!

[@OlugunnaAdeifeAlabi]

I hardly comment on videos here but this is wow!!!!!!

[@Rabixter]

Nice one, I liked the seasonality index idea. It's very simple but still gives a decent accuracy.

[@madinat.3863]

you're the best! thanks for explaining

[@ankeshsingh2576]

This is a hidden treasure. I am so glad I found this channel. Could you possibly explain if the seasonality index we have used so far is what we call exponential smoothing ?/

[@mcanbolat]

Thanks. They are quite different. I have a video on exponential smoothing.

[@diegocanales6783]

Thanks, so muchh the video was really clear and the document help a lot to put It in practise. thanks a lot again.

[@valentinapatino8215]

Thank you so much for sharing your knowledge! An amazing technique

[@qianwu1873]

This is exactly what I was looking for!! Super helpful for my upcoming interview.

[@aloy5678]

Excellent Video , Thank you so much Mustafa

[@drmilk1100]

best video for forecasting!!!

[@y020380]

This was extremely helpful, THANKYOU!

[@varunsurya6811]

I loved this method. Superb.

[@arabbitinsolar2649]

Thanks, your video helps me a lot. If you can add some equations attached in the sheet, that‘s will much better. But, anyway, it is an amazing video!

[@elenamohanty517]

Hi Mustafa, this was great! thanks a lot for your video!

[@southbayeric]

Great example my friend. Very useful!

[@kamsun508]

dude, it is a amazing video , very helpful. thank you a lot

[@jugalpaleja6431]

Super helpful video, I am requesting you make more video other forecast methods or help with link if it's already available somewhere. Thanks a Ton Mustafa

[@josemanuelslatercarrasco617]

Thanks for the video. I think In order to improve the forecast, we should deseasonalize the demand first and then apply a Linear regression to obtain the trend, instead of just doing the regression on original demand

[@mcanbolat]

Please see my other comment for that. I know that what you described is a common process but empirically there is no apparent benefit. There is a link to the empirical "evidence" in my comment. I am thinking about writing a research note on that.

[@josemanuelslatercarrasco617]

@@mcanbolat Srry I didnt read the description. Thanks. Seems pretty interesting the low benefit of deseasonalize before regression empiricaly.

[@Simon-pt4ox]

perfect video! thank you very much for this!

[@aminualiyu555]

Thank you, so much for this insight.

[@manishsrivastav970]

It's really going to help me in my professional career. Can you please make a video over OTB planning.?

[@navalksa]

this should be on top

[@sebastianmakaruk7339]

Great video. Thanks so much! :)

[@linaf6299]

Thank you Mustafa.. very clear & helpful!!

[@andrewhampaslupa2304]

subscribed after watching this..

[@mohammaddanish1670]

Great 👍🏻 explained well

[@frustrategirl]

in a textile industry, a machine fail at 5 per hour and cost of non productive machine is 400 per hour. A repairman charges 400 per hour and repair at 6 per hour. What will be the total queuing cost per hour? Assume M/M/1 queuing system. Please solve this question

[@johntimothyvillacorte9115]

Awesome!! 👍🏼

[@IAKhan-km4ph]

very nice. plz discuss ARIMA and Seasonal arima

[@henrikmanukyan3152]

In case it is not that obvious, one may find it useful to use anova for finding the exact seasonality period (that so called time lag)

[@divinekofigavor1546]

Very useful for for budgeting. Thank you for this great video.

[@jbyxxx]

Thanks for such detailed explanation. I was looking for something else but now know something new :)

[@ashupashu5559]

Nice video Mr. Canbolat.

[@jieyeye7302]

Many thanks! Really good explained!

[@bilaltelkok4792]

Tesekkur ederiz.

[@j.christine8407]

This is really helpful. However, I am trying to do this for several skus (30) do you know the best way for me to do that?

[@mcanbolat]

I already posted a video on that. Please see the descriptions of the video.

[@j.christine8407]

@@mcanbolat thank you!

[@siddhantchaudhari7891]

In the last part, when I drag the LT froecast and Seasonal Forecast column, it isn't doing anything. Any suggestions? Need help urgently

[@SuperAchraf91]

Well explained thank you so much Mustafa

[@AdamTauro]

Thank you Mustafa! Very helpful!

[@thanhthaovothi3576]

Love your video so muchhhh!

[@mcanbolat]

Thank you!

[@SiddharthaMaz]

Thank you for such an informative video. Can this forecast be made even more accurate?

[@dalex7777]

This is GOLD. Thank you so much +MustafaCanbolat!

[@AtomTiger3460]

What a godly video *praise*

[@utkucansa]

Sevgili Hocam, teşekkürler, saygılar

[@franckkarinamaryo1336]

Thank you very much Dr

[@arunkumar-ws1pk]

Dear sir, I want to learn more on demand forecast, please let me know if possible paid classes

[@janekim5571]

Hi sometimes I get negative forecasting how do I fix this?

[@rohanchadha3506]

Great video!

[@jacksambrano25]

GREAT JOB - THANKS :)

[@sawstem9505]

Great work

[@littlecreator4838]

Hi Mustafa. Is it a linear regression model.ols let me know is it a better model than holt winter model which is used for data with trend and seasonality

[@nikkip683]

What if your sales are rooms rather than widgets? In other words, the census cannot go above a certain threshold. How can I forecast seasonality such that census dips in summer and peaks in winter but the net swing is zero census growth?

[@mcanbolat]

If there is no upwards trend, forecast will not bring any future value exceeding the threshold, so it may still work. But there are models on constrained forecasting which involve transformation of data.

[@ambarilhamchannel]

How can I forecast with sinusoidal forecasting. For example if we have 120 in January, 150 in Feb, 180 in March, ... 500 in July, 600 in Aug, 500 in Sep, 400 in Oct, 350 in Nov, 250 in December, 180 in January next year, 250 in Feb next and so on for the next Month usually have Demand like sinusoidal forcast? Can u let me know? Thanks

[@mcanbolat]

That is possible, you need to set up a sinusodial function with parameters and optimize the parameters to fit the line using Excel Solver.

[@frustrategirl]

how have you done for 2017? please explain

[@jackthebeanstalkdelohio5146]

For monthly seasonal pattern, how do an analyst calculate the average price change? Do they total all the gains, and get the average from that? Or do they total all the losses....... I am not sure. What is it if u don't mind me asking? Thx.

[@mcanbolat]

Hi the average is change is always zero within that year. Each month deviates from the average either on the negative side or the positive side.

[@eduardocastilleja6665]

Great tutorial.

[@hoshanghalem3369]

Hi Mustafa does model capture the irregular pattern as well? I have recognised that it called multiplicative method. Correct?

[@mcanbolat]

Hi the multiplicative method works if the effect of seasonality gets larger and larger (or smaller) over time. Irregular patterns are different. Unfortunately there is no one-model-fixes-all solution. It is always data specific.

[@hoshanghalem3369]

@@mcanbolat thanks a lot. What would be the formula in excel for capture the irregular pattern? And does this model capture up and down of covid 19? Thanks alot for your help

[@jonathanmejia9151]

Great video - one question, when calculating the linear trend, why multiply the sum of the intercept and slope against the period?

[@mcanbolat]

The formula is Forecast = intercept + slope*time period This assumes that the demand has a linear relationship with the time period (over time it increases or decreases). Then we adjust it by the seasonality.

[@christshim7833]

Can WE Say that the method used is the Winters method ? Because i similar exercice in which they asked to find the forecast with Winters method but they didnt give me the alpha, bêta and gamma

[@mcanbolat]

No, it is different. You can use the Excel solver to minimize your error and find the best parameter values. Check my Holts video as an idea.

[@christshim7833]

@@mcanbolat Can you send me the link please

[@UpamaPaudyal]

what is the name of the model that is used here ?

[@adhilrambridge2233]

I was not aware of the last step of needing to drag the data down. I thought F2 will be your final forecast.

[@yordanmihaylov5986]

Perfect video , very helpful

[@AzyTabz]

Thank you very much Mustafa

[@utkuozver5026]

Teşekkürler, faydalı oldu.

[@yassineerrachid3561]

Thank you so much 🙏🏻

[@peiyuanshao6248]

Thank you! That's pretty helpful.

[@akshayemathpati2928]

Am getting div/0 error what should i do

[@magodanaveilrheav.9116]

What does it mean by LT Forecast??