Excel has awesome tools and functions to predict future values. It has a Forecast button, introduced in Excel 2016 version, FORECAST, and other functions both for linear and exponential data. In this article, we will see how to use these Excel tools to forecast future values based on historical data.
What Is Forecasting?
Formally, Forecasting is an approach that uses historical data as inputs to generate educated predictions about future trends. Forecasting is often used by businesses to decide how to allocate their resources or plan for anticipated expenses in the future.
Suppose you are planning to start a business with a certain product. I am not a businessman, but I guess one of the first things you want to know about the product will be its current and future demand in the market. So, there is a matter of prediction, estimation, educated guess, or “foretelling” the future. If you have adequate data that somehow follow a trend, you can reach close enough to a perfect projection.
However, you cannot predict with 100% accuracy no matter how much past and present data you have and how perfectly you’ve identified the seasonality. So before making any final decision, you have to double-check the results and consider other factors as well.
How to Forecast in Excel Based on Historical Data: 4 Methods
In this article, we have taken the price data of Crude Oil (Petroleum) from the World Bank’s website, for the last 10 years (April 2012 to March 2022). The following picture shows the list partially.
1. Use ‘Forecast Sheet’ Button in Excel 2016, 2019, 2021 and 365
The Forecast Sheet tool was first introduced in Excel 2016, which makes time series forecasting a cinch. Simply organize the source data accurately, and Excel will take care of the rest. You have to follow just two simple steps.
📌 Step 1: Arrange Data with Time Series and Corresponding Values
- First, set the time values in the left column in ascending order. Arrange the time data at a regular interval, i.e., daily. Weekly, Monthly, or yearly basis.
- Then set the corresponding prices in the right column.
📌 Step 2: Create Forecast Worksheet
- Now, go to the Data tab. Then click on the Forecast Sheet button from the Forecast group.
The Create Forecast Worksheet window will open up.
- Now, choose the graph type from the window.
- You can also choose the end date of the forecast.
- Finally, press the Create button. You are done!
Now a new worksheet will be opened in Excel. This new sheet contains our present data together with the anticipated values. There’s a graph too that represents the original and predicted data visually.
Customizing Forecast Graph:
You can customize the Forecast graph in the following ways. Look at the following image. Excel provides us with many customizing options here.
1. Chart Type
There are two options here, Create a column chart and Create a line chart. Use either of these which you find visually more comfortable.
2. Forecast End
Set here the timing when you want to end the forecast.
3. Forecast Start
Set the forecast starting date with this.
4. Confidence Interval
Its default value is 95%. The less it is, the more confidence in predicted values it indicates. You can mark or unmark this checkbox depending on the necessity of showing the accuracy level of your forecast.
Excel tries to find out seasonality in your historical data if you select the ‘Detect automatically’ option. You can also set it manually by putting a suitable value.
6. Timeline Range
Excel automatically sets it when you select any cell within the data. Besides, you can change it from here as per your need.
7. Values Range
You can edit this range in a similar fashion.
8. Fill Missing Points Using
You can choose either interpolation, or you can set the missing points as zeros. Excel can interpolate the missing data (if you choose to) if it is less than 30% of the total data.
9. Aggregate Duplicates Using
Choose the suitable calculation method (Average, Median, Min, Max, Sum, CountA) when you have multiple values at the same timestamp.
10. Include Forecast Statistics
You can add a table with info on smoothing coefficients and error metrics by selecting this checkbox.
2. Use Excel Functions to Forecast Based on Previous Data
You can also apply Excel functions such as FORECAST, TREND, and GROWTH, to forecast based on previous records. Let’s see them one by one.
2.1 Utilize FORECAST Function
MS Excel 2016 replaces the FORECAST function with FORECAST.LINEAR function. So, we will use the more updated one (for future compatibility issues with Excel).
Syntax of FORECAST.LINEAR Function:
=FORECAST.LINEAR(x, knows_ys, known_xs)
Here, x stands for the target date, known_xs stands for the timeline, and known_ys stands for known values.
- First off, insert the following formula in cell C17.
- Then hit ENTER.
2.2 Apply TREND Function
MS Excel also helps with the TREND function to forecast based on historical data. This function applies the Least Square method to foretell future values.
Syntax of TREND Function:
=TREND(known_ys, [known_xs], [new_xs], [const])
In the case of the following dataset, enter the formula below in cell C125 to get forecast values for April, May, and June 2022. Then hit ENTER.
2.3 Utilize GROWTH Function
The GROWTH function works using exponential relation while the TREND function (used in the previous method) works with linear relation. Other than that, both are identical with respect to arguments and application.
So, to get forecast values for April, May, and June 2022, insert the following formula in cell C125. Then hit ENTER.
Read More: How to Forecast Growth Rate in Excel
3. Use Moving Average and Exponential Smoothing Tools
There are two types of Moving Averages used in statistics. Simple and Exponential Moving Averages. We can use them also to foretell future values.
3.1 Use Simple Moving Average
To apply the Moving Average technique, let’s add a new column named Moving Average.
Now apply the following steps.
- First of all, go to the Data tab and click on the Data Analysis button.
- Then select the Moving Average option from the list and press OK.
- The Moving Average window appears.
- Now, select Input Range as C5:C20, put Interval as 3, Output Range as D5:D20 and mark Chart Output checkbox.
- After that, Press OK.
You can see the forecast value for April 2022 in cell D20.
Besides, the following image is a graphical representation of forecast results.
3.2 Apply Exponential Smoothing
For more accurate results, you can use the Exponential Smoothing Technique. The procedure of applying this in Excel is quite similar to that of Simple Moving Average. Let’s see.
- First, go to the Data tab >> click on the Data Analysis button >> select Exponential Smoothing from the list.
- Then press OK.
The Exponential Smoothing window will pop up.
- Now Set Input Range as C5:C20 (or as per your data), Damping factor as 0.3, and Output Range as D5:D20; mark the Chart Output checkbox.
- Then press the OK button.
After pressing OK, you will get the result in cell D20.
And look at the following graph to understand the forecast visually.
4. Apply Fill Handle Tool to Forecast Based on Historical Data
If your data follow a linear trend (increasing or decreasing), you can apply the Fill Handle tool to get a quick forecast. Here is our data and corresponding graph.
These suggest that the data has a linear trend.
Assuming that we want to forecast for April, May, and June 2022. Follow the following quick steps.
- First, select the values C5:C16 and hover your mouse cursor in the bottom right corner of cell C16. The fill handle tool will appear.
- Now drag it till cell C19.
The following graph shows the results visually. It’s noticeable that Excel ignores the irregular values (e.g. sudden hike in Mar-22), and considers the values that are more regular.
Read More: How to Forecast Call Volume in Excel
How Accurately Can Excel Forecast?
A question may arise in mind, How Much Accurate Are Excel Forecast Techniques? The answer is not simple. Because forecasting depends on many factors. For example, when we were working on this article, the Ukraine-Russia war began, and Crude oil prices rose suddenly much higher than it was expected.
So, it depends on you, i.e., how perfectly you are setting up the data for forecasting. However, we can suggest you a way to check the perfectness of the methods.
Here, we have data for April 2012 to March 2022. If we forecast for the last several months and compare the results with the known values, we will know how firmly we can depend on it.
Download Practice Workbook
You can download the following workbook for your own exercise. We have used some authentic data here and cross-checked if the results produced by the methods in this article match the real values.
In this article, we have discussed 4 methods to forecast in Excel based on historical data. If you have any questions regarding them, please let us know in the comment box.