How to do Forecasting in Excel?
Forecasting is the method used in statistics to find relationships and trends that can project the future trend based on the previous period data while providing all these inputs we do make some sort of assumptions. Forecasting technique in excel is used to estimate budgets and sales revenue and expenses in the near future. However, Forecasting doesn’t tell the clear picture or accurate numbers rather it shows how the future would look like, what needs to be done to achieve those goals and what is the plan of action required. Forecasting in excel can be made in multiple ways, we will show each every method in this article.
Method #1 – Using Excel FORECAST Formula
In excel, we have many built-in functions and FORECAST is one of the function to predict the near future values.
For an example look at the below data.
In the above sales table, we have six months of revenue data, based on these six months revenue we need to forecast for the next 6 months.
Let’s do this by using excel FORECAST function, open this function in C8 cell.
The first argument in FORECAST function is “x” for this we need to select what is the next period i.e. next month number. In this example select B8 cell.
Next argument is “Known Y’s” this is nothing but what is the revenue already achieved so far in the previous 6 months, select the data from C2 to C7 and make it an absolute reference.
Ok, we are done close the bracket and hit enter key to get the result.
So, our excel FORECAST function has predicted the next six months as shown above.
Method #2 – Using Moving Averages to Forecast in Excel
In order to smooth the trend from the data we can use moving averages method, this is a simple method forecast the things. This method takes the average of a specific set of values.
Below is the data we are going to use for this example.
From this data lets say you want to smooth out the trend daily changes in sales for every three days. For this first, we need to find the average for the first 3 days. In the next column apply AVERAGE function as shown below.
As per moving average for every three days, we have got the forecasting trending numbers.
This is one way of adding moving averages now we will see how to add moving average without calculating anything but just by inserting the chart.
For the old data, insert line chart.
Now click on the PLUS icon of the chart to see chart elements.
When you click on the PLUS icon you will see a variety of options, from this, choose “Trend Line”.
Now Format Trend Line options open up on the right side of the chart.
From this window, you need to select “Moving Average” for a period of “3 Days”.
Now in the chart, we can see the “Moving Average” trend line appears.
As you can see above “dotted line” is the moving average trend line.
Things to Remember
- There are many different ways of forecasting the data, you need prior experience of the same to implement in excel.
- FORECAST is the popular formula in excel to forecast things in excel.
This has been a guide to Forecasting in Excel. Here we discuss how to do forecasting in excel along with methods and downloadable excel template. You may also look at these useful functions in excel –