Speaker 1: Hi folks, I am Rishabh and in today's video, we will talk about Forecasting in Excel Forecasting is a very important technique If you are looking for a job in the field of Data Analyst Then you should know a skill called Forecasting Forecasting is so powerful that you can predict the future with it First, let's talk about what is Forecasting You can see on the screen If we talk about the definition of Forecasting It is a technique of predicting the future based on results of previous data In simple words, you can predict the future based on historical data In this video, we will learn how to forecast using three methods. The first one will be formula-based, which is very easy. The second one will be done with the help of data visualization, i.e. using a chart. And the last one will be done using a dedicated option, which is called forecast analysis. So, we are in Excel. As you can see, we have two columns. One is date and the other is views. So, this is the data of my YouTube channel. Almost 80-90% will be correct. I have made some changes. So, here we will predict views. The data I have here is as of 14th January, 2023. and after this we will also predict some values and then compare it with actual values so here you can see I have second tab which is called data visualization here you can see I have 15th means data after 14th and one week's data and some values of that so with this we will compare our predicted values or forecasted values ok so first of all I will tell you a basic method that how to predict this so for that what we do first of all I bring a date here Suppose I want to predict what will be my views for 15th So there is a simple formula for this I write forecast So here you can see there are many formulas for forecast One is forecast.ets It has many variations So its full form of ets is exponential smoothing And this is a kind of algorithm And different formulas are made on the basis of it So we will use simple variation, simple forecast Which you will get at the end Now as soon as I select forecast Now the first variable is asking x So, I have to find the value of X in 15th January, 2023 So, I have selected that column or that cell After that I will put a comma Now it is asking for known Ys So, you have to find the value of X and it is asking for known Y So, the column on the right will be Y Because X is my date or time So, I will select all these values Because it didn't ask for Y, it asked for Y's, so it needs all Y's So I am selecting all of them, which is from B3 to B230 Now I will put a comma Now the last variable is asking for known Xs Now we need the value of X, we have X as a date So I will select all the values of X Except 15, you don't have to select 15 You have to select up to 14th Gen After this, you will close the bracket and press enter So the value it is predicting is 1750 For 15th Gen So you can predict a value in such an easy way It's very easy, just write a formula and you will get the value So here I am removing the decimal, for that I am going here And I have removed the decimal from here So I have to forecast a week's data, so I will extend this to 21st So my 21st will come here And here similarly you don't have to do anything, you have to go to the right side And here you will get a small plus sign and you have to double click it So you can see how easily we have predicted the value for 7 days So I am highlighting this in a different color for now So I will go here and highlight it like this and I am copying these values in the data validation tab and I am giving the name of that column as formula so that we know that these values came with the help of formula so here you can see that it has predicted to a large extent like it said 1750 but my actual is 1671 which is less second it said 1753 and actual is 1783 which is almost close it has predicted quite well now let's go to our method no.2 before that I will delete these values from here So here I am changing it to CTRL-SHIFT-UP Ok So if you liked this video then please like this video and subscribe to the channel The second method is very easy In that you have to select both the columns All of them CTRL-SHIFT-DOWN-ARROW will select all of them I will go to Insert tab Here I will go to Line Chart And I will take a simple chart So you can see I have a simple line chart Here you can see a plus option Here you can see an option for trendline I will click on arrow Now I have different options for trendline So I will choose linear So you can see a blue dotted line I will change the color of line to help you Format And I will change the color to red If it is still not visible I will increase the width Ok I hope now you can see Before that I will explain what is dotted line So, this line you can see is my algorithm or a model which has made this line And it must be based on some calculation So, if I click on the model that I have selected So, you can see that I have selected a linear model So, suppose this is a linear regression model So, there will be an equation y equals to mx plus c And you can see the equation on the screen which I have just told you y equals to mx plus c So, y is your outcome It is the predicted or forecast value x is the time M is the time, M is the slope, and C is the intercept Based on the different values of X and Y, it will calculate the values of M and C Then I told it that the value of Y is the time So I told it that it needs the data of 15th January So it will write 15 instead of Y and calculate the value of X Because it has already calculated the values of M and C from my historical data So this is how your model works So if you are getting confused after seeing the equation I'll tell you in simple terms that you have to draw a line or select an option from here which is right in the center of all the points that you can see It means that it is passing the curve that you are drawing accurately You need a line that fits best in this curve So, if I look at it linearly, it seems fine to me But if you see, some points are missing here, this whole point is missing The upper values are not getting covered You can see this whole section This is also not getting covered and the line is going from the bottom So, I have a little doubt but it will work Apart from this, you can see the second model If I check the exponential Exponentially, it has become very bad Because the whole values are going up and the line is going down And here also a lot of values are left So, there is a different option here The logarithmic looks like linear Or it looks worse than that, you can see there is a lot of gap here Other than this you have polynomial, power and moving average So you have different techniques So let me tell you one thing If you don't know which line to select So I will tell you the easiest way Scroll to the end Here you will get an option display R squared value If I click this Here is a value I will bring it up Let's correct its color So, this is the value of r square So, the confusion is that Which technique should we choose Exponential, Linear or Logarithmic Because almost all lines are same So, for that case, let's see the value of r Because the model also does the same It tells the value of r That what is the performance of your model Means how good prediction can be done This model can forecast So, the better value of r The value of r is always between 0 and 1 So, the more value is near 1 the better your model will be so one ideal case is like in my case 68% which is actual data so as soon as you bring R value here R square value after that if you select different techniques then this value will change and the technique which will give you highest value will give you good forecast result so before that let me tell you what is R square so you can see on the screen how can we see the performance of forecasting model or technique I am showing you the R-square, this is for Linear Regression Model Here we also call R-square as Coefficient of Determination and if I talk about its definition How well the fitted line, regression line fits the data The one which fits the best will give you the best forecast value So this R-square is the proportion of variation that is explained by linear model Means it predicted that the forecast value is 100 but actually it is 90 So the gap of 10 is calculated by R-square You can understand this at a high level and the same thing is written below that its value lies between 0 and 1 the more value is near 1 the better your model performance will be this formula is also given below so you can see how to calculate r square let's go to excel again now we have the value of r square so now we will understand a lot of things so first of all I select linear so you can see the output of r square is 0.68 which is correct if I take exponential then it is 0.66 it means exponential is not for my data if we see visually there is a lot of gap between line and points it should have passed from the center it is already bad and according to r square value also if you see logarithmic it is same 0.68 if you see polynomial it is also 0.68 but in polynomial you have many options in which you can change the order of polynomial you can see on that basis as I show you as I do 3, you can see the performance of my model How much it has improved from 68% to 78% So if I have to select a technique to predict my data If I want to know how many views I will get per day on YouTube channel in February or March Then I will take the help of this model Which is a polynomial and it is of 3rd order 3rd order means it will have an x cube So if you want to see which model is being used in this Which equation is being used There is a display equation written on R2 As soon as I click on it, the equation will be shown to you So you can see that I am using x3 here because it is a third order polynomial It is x2 So this calculation is happening in the backend, you don't have to worry about it But I am telling you that Actually what is the use of this 3 here And if I would have selected linear, then you can see what is coming here Here it is simple, y equals to mx plus e Which I have shown you in the example Same thing is here, so this calculation is happening in the backend, you don't have to calculate it Okay Now let's do one thing For my case, the best polynomial is here So I will select that, I will remove this or else you will get scared of the equation and I want the polynomial of 3rd order so I will go here and increase it to 3 which is 78% ok now if I want to forecast here you will see an option of forecast and it is written forward I want to forecast for 7 days so I will do 7 so you can see here it is forecasting for 7 days it means after 7 days my views will be somewhere around and this line is of 2500 so around 2400 23-2400 if i see 21st around 2400 you can see how accurate output it is the actual data i have in data validation sheet you can see around 21st 2400 views came on my channel and this model is predicting the same that's why it is very important to select the model and in the same way you can forecast as much data as you want Again, keep one thing in mind The more accurate historical data you have And the more historical data you have The better your forecast value will be So, I have taken 7 days Now, let's see How much will be my per day views in 3 months So, it is saying My per day views will be somewhere around 2300 Sorry, it is showing in linear I don't want linear I want Exponential So it is telling that per day somewhere around 6 to 7 So somewhere around 6800 will come Which is a very good view If I stay consistent on my YouTube channel So it is very interesting for me to do all these things I get to know how much growth will happen Now let's go to our last method Which is very actual, which we also use in office So what we will do for that First of all we will go back to our data After that You will select both the columns Then you will go to the data column In the data tab, you will find In this data tab, you will find an option Forecast In that, you will find a forecast sheet Click on it As soon as you click on it, an option will open In that, it is showing Three values are forecasted So this is your basic If I create it directly here, it will give you the forecast value But you can play with some parameters So here you have an option Click on it You can see the end date, which is 12th March, 2023 I can change this according to how many days you want to forecast There are some options for this For example, the forecast is starting from this date From where my raw data is Here comes a very important parameter Confidence interval In simple language, how confident you are How much load you can take on that data That on the basis of this data, the prediction of my feature will be correct The 95% confidence level, I am reducing it to 85% I think it will do a good prediction in 85% Other than this, if there is seasonality in the data It automatically detects it because there are calculations in the background on the basis of the same equation Other than this, if you have any forecast statistics, you can also include it here And here the time range that we selected, which is my date And the values range that you can see, this is my views How many videos have views, that is the data Other than this, if there is any missing data in between For example, if you don't get any data For that, you will calculate the interpolation and fill it Or if you want to leave it as 0, you can do that And lastly, for data aggregation, you will take the average and leave it as it is Before creating, I am changing the date Because I want the forecast for 7 days You can select it manually, but I want the forecast for 21st Ok, 21st January 2023, I will click on create So, this is my forecast I don't need it, I will remove it So what we have seen outside is what is shown in the chart So you won't understand much in the chart, let's see the actual data So this will create a new sheet for you Like you will click on forecast create And here you will get 3 new columns I had the date and views Which are the 3 new columns? 1st will be forecast 2nd is lower confidence forecast And 3rd is upper confidence forecast If you can understand normally, it has forecasted normally in this column The confidence is low that you can do minimum then it will come in lower confidence and upper confidence means if you do well then you can do till upper Now let's go to the last which I predicted for 7th date I have this data So these are your 3 methods with which you can forecast in Microsoft Excel I hope you learned something See you in the next video Bye Bye