How to Forecast in Excel Based on Historical Data (4 Suitable Methods)

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.

Read More: How to Calculate Forecast Accuracy Percentage in Excel

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.

📌 Steps:

• 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.

📌 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.

📌 Steps:

• 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.

Read More: How to Forecast Sales Using Historical Data in Excel

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.

📌 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.

Conclusion

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.

Related Articles

• How to Forecast Revenue in Excel
• How to Forecast Sales in Excel
• How to Forecast Revenue Growth in Excel
• How to Forecast Sales Growth Rate in Excel

<< Go Back to Forecasting in Excel |Excel for Finance | Learn Excel