You take the absolute value of (Forecast-Actual) and divide by the larger of the forecasts or actuals. To calculate forecast accuracy using my formula, you follow.
The tutorial explains how to use Excel FORECAST and other related functions with formula examples. In Microsoft Excel, there are several functions that can help you create linear and exponential smoothing forecasts based on historical data such as sales, budgets, cash flows, stock prices, and the like. The main focus of this tutorial will be on the two main forecasting functions, but we will touch briefly on other functions as well to help you understand their purpose and basic uses. Excel forecasting functions In the recent versions of Excel, there exist six different forecasting functions. The two functions do linear forecasts:. FORECAST - predicts future values by using linear regression; a legacy function for backwards compatibility with Excel 2013 and earlier. LINEAR - identical to the FORECAST function; part of the new suite of forecasting functions in Excel 2016 and Excel 2019.
The four ETS functions are purposed for exponential smoothing forecasts. These functions are only available in Excel for Office 365, Excel 2019, and Excel 2016. ETS - predicts future values based on the exponential smoothing algorithm. ETS.CONFINT - calculates the confidence interval. ETS.SEASONALITY - calculates the length of a seasonal or other recurring pattern.
ETS.STAT - returns statistical values for time series forecasting. Excel FORECAST function The FORECAST function in Excel is used to predict a future value by using linear regression. In other words, FORECAST projects a future value along a line of best fit based on historical data. The syntax of the FORECAST function is as follows. FORECAST.LINEAR(x, knowny's, knownx's) This function is available in Excel for Office 365, Excel 2019, and Excel 2016. How FORECAST and FORECAST.LINEAR calculate future values Both functions calculate a future y-value by using the linear regression equation: y = a + bx Where the a constant (intercept) is: And the b coefficient (slope of the line) is: The values of x̄ and ȳ are the sample means (averages) of the known x-values and y-values.
Excel FORECAST function not working: If your FORECAST formula returns an error, this is most likely because of the following reasons:. If the knownx's and knowny's ranges are of different lengths or empty, the #N/A! Error occurs. If the x value is non-numeric, the formula returns the #VALUE!. If the variance of knownx's is zero, the #DIV/0! Error occurs.
How to use FORECAST function in Excel - formula example As already mentioned, the Excel FORECAST and FORECAST.LINEAR functions are purposed for linear trend forecasting. They work best for linear datasets and in situations when you want to forecast a general trend ignoring insignificant data fluctuations.
As an example, we will try to predict our web-site traffic for the next 7 days based on the data for the previous 3 weeks. With the known y-values (no. Of visitors) in B2:B22 and the known x-values (dates) in A2:A22, the forecast formula goes as follows. Excel 2019 - Excel 2000: =FORECAST(A23, $B$2:$B$22, $A$2:$A$22) Excel 2016 and Excel 2019: =FORECAST.LINEAR(A23, $B$2:$B$22, $A$2:$A$22) Where A23 is a new x-value for which you wish to predict a future y-value.
Depending on your Excel version, insert one of the above formulas in any empty cell in row 23, copy it down to as many cells as needed and you will get this result: Please pay attention that we lock the ranges with (like $A$2:$A$2) to prevent them from changing when copying the formula to other cells. Plotted on a graph, our linear forecast looks as follows: The detailed steps to make such a graph are described in If you'd like to predict future values based on the recurring pattern observed in your historical data, then use FORECAST.ETS instead of the Excel FORECAST function. And the next section of our tutorial shows how to do this. Excel FORECAST.ETS function The FORECAST.ETS function is used to do exponential smoothing forecasts based on a series of existing values. More precisely, it predicts a future value based on the AAA version of the Exponential Triple Smoothing (ETS) algorithm, hence the function's name. This algorithm smoothes out insignificant deviations in data trends by detecting seasonality patterns and confidence intervals. 'AAA' stands for additive error, additive trend and additive seasonality.
The FORECAST.ETS function is available in Excel for Office 365, Excel 2019, and Excel 2016. The syntax of the Excel FORECAST.ETS is as follows. FORECAST.ETS(targetdate, values, timeline, seasonality, datacompletion, aggregation) Where:. Targetdate(required) - the data point for which to forecast a value.
It can be represented by a date/time or number. Values(required) - a range or array of historical data for which you want to predict future values. Timeline (required) - an array of dates/times or independent numeric data with a constant step between them.
Seasonality (optional) - a number representing the length of the seasonal pattern:. 1 or omitted (default) - Excel detects seasonality automatically by using positive, whole numbers. 0 - no seasonality, i.e.
A linear forecast. The maximum allowed seasonality is 8,760, which is the number of hours in a year. A higher seasonality number will result in the #NUM!. Data completion(optional) - accounts for missing points. 1 or omitted (default) - fill in the missing points as the average of the neighboring points (liner inrerpolation).
0 - treat the missing points as zeros. Aggregation(optional) - specifies how to aggregate multiple data values with the same time stamp. 1 or omitted (default) - the AVERAGE function is used for aggregation. Your other options are: 2 - COUNT, 3 - COUNTA, 4 - MAX, 5 - MEDIAN, 6 - MIN and 7 - SUM. 5 things you should know about FORECAST.ETS. For the correct work of the FORECAST.ETS function, the timeline should have a regular interval - hourly, daily, monthly, quarterly, yearly, etc.
The function is best suited for non-linear data sets with seasonal or other repetitive pattern. When Excel cannot detect a pattern, the function reverts to a linear forecast. The function can work with incomplete datasets where up to 30% data points are missing.
The missing points are treated according to the value of the data completion argument. Although a timeline with a consistent step is required, there may be duplicates in the date/time series. The values with the same timestamp are aggregated as defined by the aggregation argument. FORECAST.ETS function not working: If your formula produces an error, this is likely to be one of the following:. The #N/A occurs if the values and timeline arrays have different length. The #VALUE! Error is returned if the seasonality, data completion or aggregation argument is non-numeric.
Error may be thrown because of the following reasons:. A consistent step size cannot be detected in timeline. The seasonality value is out of the supported range (0 - 8,7600). The data completion value is other than 0 or 1. The aggregation value is out of the valid range (1 - 7). How to use FORECAST.ETS function in Excel - formula example To see how the future values calculated with exponential smoothing are different from a linear regression forecast, let's make a FORECAST.ETS formula for the same data set that we used in the previous example.
=FORECAST.ETS(A23, $B$2:$B$22, $A$2:$A$22) Where:. A23 is the target date. $B$2:$B$22 are the historical data ( values). $A$2:$A$22 are the dates ( timeline) By omitting the last three arguments ( seasonality, data completion or aggregation) we rely on Excel defaults.
And Excel forecasts the trend perfectly: Excel FORECAST.ETS.CONFINT function The FORECAST.ETS.CONFINT function is used to calculate the confidence interval for a forecasted value. The confidence interval is kind of a measure of the prediction accuracy. The smaller the interval, the more confidence in the prediction for a specific data point.
The FORECAST.ETS.CONFINT is available in Excel for Office 365, Excel 2019, and Excel 2016. The function has the following arguments. FORECAST.ETS.CONFINT(targetdate, values, timeline, confidencelevel, seasonality, data completion, aggregation) As you see, the syntax of FORECAST.ETS.CONFINT is very similar to that of the function, except this additional argument: Confidencelevel (optional) - a number between 0 and 1 that specifies a level of confidence for the calculated interval. Typically, it is supplied as a decimal number, though percentages are also accepted. For instance, to set a 90% confidence level, you enter either 0.9 or 90%. If omitted, the default value of 95% is used, meaning that 95% of the time a predicted data point is expected to fall within this radius from the value returned by FORECAST.ETS. If the confidence level is outside of the supported range (0 - 1), the formula returns the #NUM!
FORECAST.ETS.CONFINT formula example To see how it works in practice, let's calculate the confidence interval for our sample data set: =FORECAST.ETS.CONFINT(A23, $B$2:$B$22, $A$2:$A$22) Where:. A23 is the target date. $B$2:$B$22 are the historical data. $A$2:$A$22 are the dates The last 4 arguments are omitted, telling Excel to use the default options:.
Set the confidence level to 95%. Detect seasonality automatically. Complete missing points as the average of the neighboring points. Aggregate multiple data values with the same timestamp by using the AVERAGE function. To grasp what the returned values actually mean, please take a look at the screenshot below (some rows with historical data are hidden for the sake of space).
The formula in D23 gives the result 6441.22 (rounded to 2 decimal points). What it means is that 95% of the time, the prediction for 11-Mar is expected to fall within 6441.22 of the forecasted value 61,075 (C3). That is 61,075 ± 6441.22.
To find out the range within which the forecasted values are likely to fall, you can calculate the confidence interval bounds for each data point. To get the lower bound, subtract the confidence interval from the forecasted value: =C23-D23 To get the upper bound, add the confidence interval to the forecasted value: =C23+D23 Where C23 is the predicted value returned by FORECAST.ETS and D23 is the confidence interval returned by FORECAST.ETS.CONFINT.
Copy the above formulas down, plot the results on a chart, and you will have a clear visual representation of the predicted values and the confidence interval. FORECAST.ETS.SEASONALITY(values, timeline, datacompletion, aggregation) For our data set, the formula takes the following shape: =FORECAST.ETS.SEASONALITY(B2:B22, A2:A22) And returns the seasonality 7, which perfectly agrees with the weekly pattern of our historical data: Excel FORECAST.ETS.STAT function The FORECAST.ETS.STAT function in returns a specified statistical value relating to a time series exponential smoothing forecasting. Like other ETS functions, it is available in Excel for Office 365, Excel 2019, and Excel 2016. The function has the following syntax. FORECAST.ETS.STAT(values, timeline, statistictype, seasonality, datacompletion, aggregation) The statistictype argument indicates which statistical value to return:.
Alpha (base value) - the smoothing value between 0 and 1 that controls the weighting of data points. The higher the value, the more weight is given to recent data. Beta (trend value) - the value between 0 and 1 that determines the trend calculation. The higher the value, the more weight is given to recent trends.
Gamma (seasonality value) - the value between 0 and 1 that controls the seasonality of the ETS forecast. The higher the value, the more weight is given to the recent seasonal period. MASE (mean absolute scaled error) - a measure of the forecast accuracy. SMAPE (symmetric mean absolute percentage error) - a measure of accuracy based on percentage or relative errors.
MAE (mean absolute error) - measures the average magnitude of the prediction errors, regardless of their direction. RMSE (root mean square error) - a measure of the differences between the predicted and observed values. Step size detected - the step size detected in the timeline. For example, to return the Alpha parameter for our sample data set, we use this formula: =FORECAST.ETS.STAT(B2:B22, A2:A22, 1) The screenshot below shows the formulas for other statistical values: That's how you do time series forecasting in Excel. To investigate all the formulas discussed in this tutorial, you are welcome to download our.
I thank you for reading and hope to see you on our blog next week! You may also be interested in:.
Excel for Office 365 Excel 2019 Excel 2016 If you have historical time-based data, you can use it to create a forecast. When you create a forecast, Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. A forecast can help you predict things like future sales, inventory requirements, or consumer trends. Information about and can be found at the bottom of this article. Create a forecast. In a worksheet, enter two data series that correspond to each other:.
A series with date or time entries for the timeline. A series with corresponding values These values will be predicted for future dates. Tip: If you select a cell in one of your series, Excel automatically selects the rest of the data.
On the Data tab, in the Forecast group, click Forecast Sheet. In the Create Forecast Worksheet box, pick either a line chart or a column chart for the visual representation of the forecast. In the Forecast End box, pick an end date, and then click Create. Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. You'll find the new worksheet just to the left ('in front of') the sheet where you entered the data series. Customize your forecast If you want to change any advanced settings for your forecast, click Options. You'll find information about each of the options in the following table.
Forecast Options Description Forecast Start Pick the date for the forecast to begin. When you pick a date before the end of the historical data, only data prior to the start date are used in the prediction (this is sometimes referred to as 'hindcasting'). Tips:. Starting your forecast before the last historical point gives you a sense of the prediction accuracy as you can compare the forecasted series to the actual data. However, if you start the forecast too early, the forecast generated won't necessarily represent the forecast you'll get using all the historical data.
Using all of your historical data gives you a more accurate prediction. If your data is seasonal, then starting a forecast before the last historical point is recommended. Confidence Interval Check or uncheck Confidence Interval to show or hide it. The confidence interval is the range surrounding each predicted value, in which 95% of future points are expected to fall, based on the forecast (with normal distribution). Confidence interval can help you figure out the accuracy of the prediction. A smaller interval implies more confidence in the prediction for the specific point.
The default level of 95% confidence can be changed using the up or down arrows. Seasonality Seasonality is a number for the length (number of points) of the seasonal pattern and is automatically detected. For example, in a yearly sales cycle, with each point representing a month, the seasonality is 12. You can override the automatic detection by choosing Set Manually and then picking a number. Note: When setting seasonality manually, avoid a value for less than 2 cycles of historical data.
With less than 2 cycles, Excel cannot identify the seasonal components. And when the seasonality is not significant enough for the algorithm to detect, the prediction will revert to a linear trend. Timeline Range Change the range used for your timeline here. This range needs to match the Values Range.
Values Range Change the range used for your value series here. This range needs to be identical to the Timeline Range. Fill Missing Points Using To handle missing points, Excel uses interpolation, meaning that a missing point will be completed as the weighted average of its neighboring points as long as fewer than 30% of the points are missing. To treat the missing points as zeros instead, click Zeros in the list. Duplicate Aggregates Using When your data contains multiple values with the same timestamp, Excel will average the values. To use another calculation method, such as Median, pick the calculation from the list. Include Forecast Statistics Check this box if you want additional statistical information on the forecast included in a new worksheet.
Doing this adds a table of statistics generated using the function and includes measures, such as the smoothing coefficients (Alpha, Beta, Gamma), and error metrics (MASE, SMAPE, MAE, RMSE). Formulas used in forecasting data When you use a formula to create a forecast, it returns a table with the historical and predicted data, and a chart. The forecast predicts future values using your existing time-based data and the AAA version of the Exponential Smoothing (ETS) algorithm. The table can contain the following columns, three of which are calculated columns:. Historical time column (your time-based data series). Historical values column (your corresponding values data series).
Forecasted values column (calculated using ). Two columns representing the confidence interval (calculated using ). These columns appear only when the Confidence Interval is checked in the Options section of the box. Download a sample workbook Need more help?
You can always ask an expert in the, get support in the, or suggest a new feature or improvement on. Related Topics.