How to Forecast with Excel in less than 5 minutes : Tutorial & Forecasting Models

hello my friends my name is edward chulom the founder of abc supply channel and in today's video i'm going to show you how to focus very quickly and simply in excel okay so it's very important if you want

to improve your forecasting and excel skills to practice and for that we're gonna use uh the curvy case in the world we have some we have a very challenging situation right now especially in india

and still in the world and we're gonna also forecast the sales cars in the the us we're gonna focus for the next 10 years i'm going to show you how to do it very very quickly so we're going to start with the kovka

so i got this data from the all over line in data.org you can download this excel below the video if you want to practice with me it's very important to practice

and let's start to check just the data from the world let's insert a shot there we go so this is the curry case in the world

it doesn't look great and the first thing you can do before focusing automatically we can just have a quick look on the trend line so just click on the curve

add a trendline just change the color and this trend line basically will give you a trend of uh your your current data and in this case the trend is going up you can have a

look so let's check the trend line you can change the trend line i'm not going to explain the mathematics behind each timeline but you can just have a look on the graphic and the good thing with excel excel is not perfect to focus but

excel is a very good way to visualize data so i encourage you to have a look on the different one exponential doesn't look great logarithmic polynomial power moving average you can change the period

it's just a moving average i recommend to use seven days for this one this is the one that google is using for the covet case if you check the numbers and i like the polynomial one and you can change the order basically

the more you are ordered the more the polynomial will be sensitive with recent data i like the number three etc etc so then my first tip is you can

forecast the trend so let's go back to a linear one and you can see you have forecasts here and you can focus the periods so what are the periods i have they have data every day so my period is days

and if you go back to the forecast i'm gonna forecast the next 30 periods so the next 30 days you can focus also the next 60 days so that gives you an idea oh okay what is the trend

and if you want to focus in excel you can use the equation so this is the equation you can use to forecast this is quite complex i'm going to do probably another video i'm going to show you an

a better way to focus automatically but just to let you know you can use this formula if you want to focus in the future let's have a look at the polynomial much more complex

but this is probably the one i would use if i had to focus uh this way if you if we use the the other three is getting a bit crazy so i think this is the best way

for this trend line so i recommend you to play with the trend line before to have a look so this is we have different case from like india it's very challenging right now we have the us and we have the world you can also

change the period you will see that if you change the period the trend will change as well but my video today is to show that there is a much faster and simpler way to forecast and this

tool is called if you go to data it's called forecast sheets so i'm going to show you in excel right now so you need to select if you select the date it won't work

i'm going to show right now if you do focus sheets you don't have any forecast basically you need to have like an incremental number and the best way to do it is just to have a like the number of days for example of

the number of periods so this is just a one two three etc the end of the table so you select your period number of day and your data you go to data forecast sheets

and taran you have a forecast automatically so that's pretty cool if you never forecast before that's a very good way to start and what is this so i'm gonna i'm going to explain you

slowly click on options the first thing is you can focus how many days you want to focus so we have 467 days i'm gonna forecast for example for the next

100 days so i'm going to focus this is the first parameter when you want a forecast to end then you have the confidence interval so basically what is 95 percent is you have the forecast in the middle

and basically uh excel tell you that we have 95 percent chance then you the reality the the reality of the next anodize would be between this curve

and this curve you can of course change this trend let's change for example to 70 percent if you if you think you're you don't need this um this large accuracy

and then you need to define the seasonality so the season 80 if you have any uh rep repeated cycle so as you can see in this in this curve they found automatically

that there is a seven-day seasonality and why do we have seven days simply because uh people get less copy tests at the end of the week so we have less tests on sunday and on monday and in this case

that's correct but i'm going to show you examples where you need to do it manually because otherwise it won't be a it won't be a great forecast and then you can also include the forecast36 if you want to know what

was used to the focus so then when you have this setup as you want let's just create and you have a forecast in excel in just one minute so that's pretty cool

the good thing as well is you have the data so if you want to use the data if you go down you are you already have the data for the forecast and for the lower confidence bound and the the upper confidence bond

this is pretty cool if you never forecast before this is much better than most of the the focus i can see on the market right now and basically excel is using the forecast ets and what is for casitas for

casitas is exponential triple smoothing that's a good way to start focusing if you never focus before the good way with exponential troopers the thing it uh it will consider the seasonality into your focus

so this is the first example you can also change the labels the design of the graph etc etc and you have the statistics if you want to have a look on the statistic especially the focus accuracy based on

this on the historic i'm going to probably do a video just on how to how to track your focus accuracy so you we have this forecast now let's try to forecast

the case in india and you will see we will have some challenge with this forecast so you go to data focus and you see this focus is a bit weird now

why is it weird so let's go into options the first thing is it doesn't it doesn't find any seasonality so you can set a manual seasonality and in this case we're gonna use as well the seven days it looks a bit better but

it looks a bit weird because we have this trend that we look we can see that the trend in india start to slowing down the number of cases of kovid and the reason why we have this

is because uh the period is maybe too long so the problem is we have the first peak and then we have a second one and because this pair is too long the the model won't see any trend

for going down for the next few a few weeks and few months so what we're gonna do is because i i don't like this these forecasts we're gonna change the period to avoid this first peak and to to give

less attitude to them to the forecast model to see if we can have something better so we're going to start from this point and see if it's better so let's cancel so i create another sheet only with the data

for 2021 from first of john and we're going to see if it's better or not so we go to data focus sheets and you see now it looks a bit better

so let's go into options let's set the signality to seven days and now it looks much much better so you see that the selection of the period in the past is very important when you focus and that's why you need

to practice and it's good to have this graph too to see if it's relevant or not the last thing you have to be careful is you've seen in this confidence interval we have negative value for this for this one and it doesn't make sense for me so you can

change the interval let's switch to for example 50 and now we have a much better forecast this is something that looks possible of course we only have historic data we don't have the vaccination

forecasts or different focus but that's a good way to start forecasting and you can use this data for example if you work if you supply the like products for the kovid supply this

is i think this is a quite relevant forecast that you can update every day or every week based on your recent historic so you can also change the design of this

i like to change also the colors so this is the best seller so let's put it in green hopefully this is what's going to happen in india this is the worst one and this is the

middle one and you can also change i like to say best scenario and worst

scenario there we go so now we have a forecast for the case in india so i recommend you to play with this data for example you could maybe try to forecast the curvy case in

the us to see um what you get from 18 months per head and for six months period and you can also play with the 70 be very careful with the seasonality

okay so this is for the first example now i'm gonna show you the second one so the second one now this is for the price okay so now let's have a look on the sales car in the u.s so i got these that are

since 1976 that's pretty cool you can download this excel if you want to play with me and we have the monthly sales and we i just did a graph to see okay what is the trend if we check the linear

trend we can see that we have we are slightly growing growing up for the sales cars in the us but we had some massive crisis the old shock around the 80s we had the sub crime crisis and we

had of course the curvy crisis in 2020 we can see that the cells are much better right now but it's probably just the compensation from the the crisis we had the

the last year so basically we have this trend this line is you see it's 0.0063 times x so it's slightly growing if we if we base this

trend on the on the last 40 or 50 years we can have a look on the train line and then we're gonna forecast so i don't think it's an exponential logarithmic polynomial

i like the polynomial probably the number two i don't think you see when you use the polynomial number three so i'm using the next 50 year period so the next 15 months we can

use 100 months because of the last uh the recent month with very strong cells the polynomial is maybe too reactive on the recent path so i'm going to use the polynomial 2 and this is i think the

best way to focus but let's use the same tool i was using before so let's select the data

we go to forecast sheets and then you can see that this focus is going a bit crazy not crazy but because of the recent story we have this trend i think the sun

is a bit too high so let's go into options you can reduce the interval if you want less forecast in the future so let's move to maybe maybe 700 so it's

the number of months so this is the number of months i have and this is the forecast i want to see i'm going to reduce my beauty interval to 60 and then we have the seasonalities so

xa will detect that we have a seasonality every 181 months so this is basically i think the average of the the crisis we can face in a

since 1970 and 76 i'm going to change the seasonality i'm going to check for the similarity manually i'm going to say this is a 12-month seasonality we sell more car before summer than in winter

so i'm going to change to 12 months we have a seasonality every year on selling car and this is it looks much more relevant for me we're going to include the sales forecast and we're going to create

the forecast and there we go once again in three clicks we have a forecast for the sky in the us i will probably change again the the the

lower and upper confidence limits so i'm going to go back to the data and do it again focus sheets version

700 let's put 50 i prefer the 51 maybe 60.

i don't think that it's gonna go that crazy for for the upper limits manually 12 i'm going to go to 30

yeah i think that's pretty good including forecasts and ebola we have the forecast only a few clicks you can change the

design and i think it's good also to have a base case and a worst case because in demand planning in supply chain and forecasting most of the time we only have one focus but i like this idea to

have like what is your the best scenario what is the worst scenario this is really important if you work for example for the snlp process to give okay what what is what what happened if if the worst happen in

terms of profits in terms of inventory and any time of sales and this is very useful to have this kind of different forecast between like what is the most probabilistic focus and what is the the best and worst scenario so you can change this of

course you can change the design so that's it for the stage focus you can also change the layout so i compared these forecasts with this seasonality with

181 periods and the one with 12 pairs so 12 months you can change the colors you can also change the year so you can use my excel if you want a more beautiful graph so i hope you enjoyed this video the

goal was not to become a forecasting expert in 10 minutes but it was to give you the curiosity and the appetite to start practicing to learn and maybe to go further you're gonna start review with this

excel function and then you're gonna add more data more made me a mathematical model and maybe one day you're gonna start doing machine learning in python so let me know in the comments what do you think about this forecasting tool in

excel are you going to use it please leave me a comment give me a like and i'm going to show you on the next videos how to improve your focus accuracy or to track your performance your kpis or to create

dashboard and many different tools to improve your performance in your supply chain thank you guys and

i'll see you later you