This article is aimed to show you two forecasting methods that you can use to forecast in Excel.
If you own or operate a business, this is a common scenario that you are worried about the future performance of your business. In this article, I will show you two techniques that you can use to get some idea about the future performance of your business if the current trend continues.
I have two worksheets in the workbook that you are seeing in the following image. The worksheets are Trend and Forecast. In the image, you are seeing Trend worksheet is open and you see the year 2005 to the year 2024 under the Year column. Under the Sales column, you are seeing sales data for years 2005 to 2015. And you want to get an idea about the sales in the years from 2016 to 2024.
Table of Contents
Forecasting Methods in Excel
You can perform forecast job in Excel in multiple ways. I am naming some here:
- Using FORECAST(…) function: I will show this technique in this article.
- Using TREND(…) function: Read this article to learn more.
- Using LINEST(…) function: Read this article to learn more.
- Adding a Trend Line to your chart: Read this article to learn more.
- Using Auto Fill Handle Tool: This technique will be discussed here.
- Using Analysis ToolPak: Read this article to learn more.
- Even manually: Read this article to learn more.
There might be more methods to forecast in Excel. If you know any, please put the method in the comments box. I will add that when I will update this article.
My motto is to make your job easier. So, I am showing you two easy techniques that are easy and fast to provide you the future performance of your business.
- Using Auto Fill Handle Tool
- And Using Excel’s FORECAST(…) function.
Forecasting Using Auto Fill Handle Tool
At first, I am going to show you how you can use Excel’s Auto Fill Handle tool to forecast future results.
I hope you know how to use Excel’s Fill Handle tool.
But for them who don’t know how to use it, here is the process:
- At first, select the sales data for the years 2005 to 2015.
- When this data range is selected, you will find a little green square box at the bottom right corner of the selection. This square box is actually the fill handle tool. This tool is used to fill cells in a worksheet. That is why it is named as Fill Handle Tool.
- Hover your mouse pointer over the square box and you see the mouse pointer turns into a black and sharp plus sign. Click and hold your mouse pointer and drag down.
- I release the mouse pointer when it reaches the year 2024. The blank cells are filled with new data. This new data is actually the extended series of the old data series.
From the overall data, it is apparent that the sales data has an upward trend. I create an XY chart using this data, you see from the chart that the data is in the uptrend. Though there are some trend breakouts in some places; but the overall trend is up.
Now I shall show you another technique and that is using Excel’s forecast function.
FORECAST(…) Function in Excel
Before showing you how to use the Forecast(…) function in Excel to get future results, at first get introduced to the function. In the image below, you see Forecast(…) function’s Description and Syntax.
Forecasting in Excel Using Excel’s Forecast(…) Function
Now open the Forecast worksheet in the workbook. You will find the data of Distance vs. Amount Spent.
Think that you have a grocery store in one corner of your town. And your hypothesis is: when customers come from longer distance shop more. You see from the table that, the distance is going higher and the amount spent is also increasing. So my hypothesis is supported by this data.
Now you see from the table that you have survey data from people who came to your store from 50 miles away, then the data jumps to 75 miles. So you might want to guess if a customer comes from 60 or 65 miles of distance, how much he might spend in your store.
In this case, you can use Excel’s forecast function. First argument of Forecast function is x, then known y’s and then known x’s. If you create an XY chart using this data, you know that Distance values will be the independent variables, I mean the X values of the chart. And Amount Spent values will be the dependent variables, I mean the Y values of the chart.
The x argument means actually a single value from the Distance column, x can be 20, 25 or anything else; 25 is not in the Distance column, but you can use 25 as x’s value; so you can use any value as x but x will be a single value. As known y’s, values from the Amount Spent column will be used. Then as known_x’s; as this argument you will use all the values from the Distance column, as they are x values or dependent variables.
In Cell E5, I insert the forecast function, as x value, I will use the value of the cell D5, so I select it; now as known_y’s values, I select the cells from B2 to B27. As known-x’s values, I select the Distance values; the formula will be as the following image.
Now press return. The cell will be showing 22.43 (change the cell format to number).
Cell D5 has no value, so it is showing 22.43.
Input some value in cell D5. At first, I input 20, the amount the customer might spend is 45. And from the data table, you see that 20 has two values 50 and 25. At 22 miles you get the amount spent is 40. So, the average can be easily taken as 45.
Then I input 60, and I get 90.15 value. From the zone of data (50, 50, 50 and 75 values in the Distance column), it is possible that if a customer comes from 60 miles of distance, he might spend 90 dollars.
Let’s check another value. Say the customer is coming from 127 miles. 127 is between 125 and 130. I input 127 and I get result 166 approximately. From the data, 125 and 130, you can guess that if the customer is coming from 127 miles of distance, he might spend 166 dollars.
So this is how using Excel’s fill handle tool, you can create a series of future values and using forecast function you can get an idea of the future value for a single data point. I hope in real life, you will able to use these techniques to analyze data.