Forecast Accuracy Formula: 4 Calculations In Excel (2024)

The forecast reliability or forecast accuracy is a key indicator in demand planning. If chosen correctly and measured properly, it will allow you to reduce your stock-outs, increase your service rate and reduce the cost of your Supply Chain. Learn in 5 steps how to master forecast accuracy formulas and implement the right KPI in your business.

You can download the Forecast Accuracy Calculator used in this article here:

There are many methods to gauge the quality of sales forecasts. I studied them a lot: I surrounded myself with experts, read reference books and compared them to my own experiences in sales forecasting. I came to the conclusion that THE perfect method does not exist and that the many existing solutions are like a maze of mathematical formulas. Sorting through them can be difficult.

That’s why the best thing for you to do is to choose a consistent method that’s easy to implement and maintain, and that allows you to estimate the quality of your sales forecasts at a glance.

I’ll walk you through step-by-step on how to do this, from selecting the parameters to the details of the calculation.

Table of Contents

1) Having a demand forecast

The first step is… to have a demand or sales forecast. You may smile, but many companies don’t have one. If you don’t have one, simply start with this calculation: average sales X seasonality X growth. If you have an ERP or other software, you probably already have forecasts.

Forecast Accuracy Formula: 4 Calculations In Excel (2)

Sales forecasting is a very broad topic, and I won’t go into it any further in this article. If you have a specific need in this area, my “Forecasting Expert” program (still in the works) will provide the best forecasting models for your entire supply chain.

If you really can’t wait, you can have a look at my article: Forecasting in Excel in 3 Clicks: Complete Tutorial with Examples, where I provide easy methods to forecast in Excel in less than 5 minutes.

2) Define a demand forecast horizon

Once you have your forecasts properly implemented, you need to define a forecast horizon. This is simply the length of time into the future for which forecasts are to be prepared.

In the following example, a sales forecast was calculated at the item level for the month of May. These forecasts are updated each month and take into account the order history: in January, the forecast for May indicated sales of 500 quantities. In February, it was 700. In March, it was 900, etc. Finally, 1,000 quantities were sold.

Forecast Accuracy Formula: 4 Calculations In Excel (3)

It becomes obvious that the Forecast Accuracy computation will not be the same if we choose a one-month horizon (April forecasts) or a four-month horizon (January forecasts).

So, how do you correctly choose the forecast horizon?

Ideal solution: the average supply lead time of the item

Let’s assume you have a 90-day supply lead time for a specific item. To order May quantities, you should base your order on the forecast made in February.

So ideally, you should choose a 90-day horizon in your forecast accuracy computation: the reason is that after February, you could not react anymore.

The problem is that procurement lead times are very often item or supplier-specific. So you would end up with item-specific horizons, and item-specific forecast accuracy KPIs. Extending these calculation rules across your entire product portfolio would quickly become very complicated, which is the big disadvantage of this method. And there is a risk your colleagues could not keep up with you, due to this complexity.

To be transparent with you, while this method is ideal, I have rarely seen it used. Even within large corporations.

Recommended solution: the general supply lead time

If you want to keep it simple, I recommend using an average time to supply. If your average supply time is 2 months, compare your sales with your forecast made 2 months before. If you are in a very short-term business like manufacturing, go with a forecast horizon of one to two weeks.

Keep the same rule for all your products.

If you have no idea what time frame to take, simply use the previous month. While this may seem to be too short to react, it is still worthwhile as it is a simple way to take action in creating your KPI.

3) Save your demand forecast history

Forecast Accuracy Formula: 4 Calculations In Excel (4)

To be able to perform the calculations, you need to have access to two sets of data: the forecast history and the demand history.

So start recording historical data by article. And if possible, by week.

If you don’t want this to be too difficult to maintain, I really recommend creating a single table or database that centralizes all this data. If you do it by hand, only keep the data from the previous month.

Please pay attention to the following:

  • Be sure to use actual demand, not sales. For example, if you are asked for 1,000 pieces and only sell 500 because of a stock out, the forecast accuracy calculation should assume a demand of 1,000, not 500.
  • Save your quantity and value data (I recommend sale price, but purchase price may also be appropriate). For example, if you sell screws and motors, the screws are certainly much less important than the motors in terms of margin. So it’s important to value them to give them weight in the calculation.
Forecast Accuracy Formula: 4 Calculations In Excel (5)
  • Finally, I would advise you to have the history of active/inactive items, as some companies calculate their forecast accuracy only on active items.

4) Choose a forecast accuracy calculation method

Once you have determined the history and forecast horizon, you can get started on the forecast accuracy calculation.

The following examples use a Forecast Accuracy Calculator that you can download here:

  • The first step is to calculate the forecast error at the item level. Simply subtract the forecast from the demand for each item.
  • The next step is to retrieve the absolute value of the error calculated earlier (use the =ABS() formula in Excel).
  • Finally, you need to calculate the % of the error, again at the item level. Simply divide the absolute value of the error by the demand, and multiply it by 100.

Here is the Excel result of these 3 calculations for a 2-month horizon:

Forecast Accuracy Formula: 4 Calculations In Excel (7)

Now that you have the errors by article, the question is how to tally all of these errors, to get an overall indicator.

There are many ways to get this global indicator. Here, I’ll introduce you to the 4 most commonly used mathematical formulas. This is going to be kept very simple.

1- BIAS forecast accuracy (consistent forecast error)

Prediction bias is the systematic deviation between estimated and actual values. It is useful to see if you tend to overestimate (BIAS > 0) or underestimate your predictions (BIAS < 0) for all your items. I suggest you take a look at this small article from the University of Arizona that illustrates the difference between accuracy, precision, and bias.

The forecast accuracy formula is straightforward : just divide the sum of your errors by the total demand.

Forecast Accuracy Formula: 4 Calculations In Excel (8)

2-MAPE forecast accuracy (Mean Absolute Percentage Error)

MAPE is the average percentage of error. The forecast accuracy formula is simply the average of the error percentages:

Forecast Accuracy Formula: 4 Calculations In Excel (9)

This method is really not recommended, because there is no weighting, neither on quantities nor on values. If you rely on this indicator to optimize your forecasts, you will tend to underestimate periods of high demand. I recommend this method only in the context of an ABC classification.

3- MAE forecast accuracy (Mean Absolute Error)

MAE, also known as MAD (Mean Absolute Deviation) or WAPE (Weighted Absolute Percentage Error), is the calculation of the average of weighted absolute errors. It involves dividing your total absolute errors by the total demand.

Forecast Accuracy Formula: 4 Calculations In Excel (10)

This is for me the recommended method in demand planning, it is simple to explain and is weighted by quantity or value (I always recommend weighting by value and not by quantity).

4- RMSE forecast accuracy (Root Mean Squared Error)

The RMSE is calculated from the MSE (Mean Squared Error, or MSD for Mean Squared Deviation).

The MSE is the average squared error per article. Compared to the previous indicators, we need to add the squared errors per article.

Then we can get the RMSE which is the square root of the MSE.

Forecast Accuracy Formula: 4 Calculations In Excel (11)

The RMSE is much more complex to explain and understand. The advantage of this formula is that it strongly penalizes large forecast errors.

I only recommend these 2 indicators for people who are already comfortable with statistics and data analysis (even in data science and machine learning), as they will be better able to avoid computation errors, interpret results correctly, and most importantly, communicate with decision makers.

5) Calculation of the Forecast Accuracy KPI

I therefore recommend you to use the MAE to calculate your KPI, it is simple to implement and interpret. It facilitates performance tracking and allows for effective communication with your colleagues in sales forecasting. You can use the bias as a complement, to detect recurrent errors you won’t notice with MAE alone.

Forecast Accuracy Formula: 4 Calculations In Excel (12)

Then, the KPI is derived from the overall % error we just calculated. It doesn’t matter which formula is chosen.

The accuracy KPI is simply calculated as 1 – % Total Error (MAE, RMSE etc.). For example, if your MAE is 20%, then you have a 20% error rate and 80% forecast accuracy.

Using the accuracy as a KPI rather than using the error is somehow a more positive way to communicate.


Estimating future sales is critical to your business, so it’s crucial to measure the reliability of those estimates.

By taking it step by step, I’m sure creating a forecast accuracy indicator will be a breeze for you. If you don’t have access to historical data or don’t have any forecast yet, don’t be discouraged and just start, as explained in this article.

You can download the Forecast Accuracy Calculator used in this article here:

If you want to develop your skills and increase your attractiveness, you can also check my articles:

  • 10 KPIs for your Supply Chain
  • Forecasting in Excel in 3 Clicks: Complete Tutorial with Examples

Forecast Accuracy Formula: 4 Calculations In Excel (14)

Edouard Thieuleux

Founder of AbcSupplyChain | Supply Chain Expert | 15 years experience in 6 different countries –> Follow me on LinkedIn

Forecast Accuracy Formula: 4 Calculations In Excel (2024)


How do you calculate total accuracy in Excel? ›

You can use the ABS function in excel to calculate the percentage of the error first. Then, subtracting the error from 1 will give you the accuracy percentage.

How do you measure the accuracy of a forecast? ›

One simple approach that many forecasters use to measure forecast accuracy is a technique called “Percent Difference” or “Percentage Error”. This is simply the difference between the actual volume and the forecast volume expressed as a percentage.

What is the formula for calculating accuracy? ›

Mathematically, this can be stated as:
  1. Accuracy = TP + TN TP + TN + FP + FN. Sensitivity: The sensitivity of a test is its ability to determine the patient cases correctly. ...
  2. Sensitivity = TP TP + FN. Specificity: The specificity of a test is its ability to determine the healthy cases correctly. ...
  3. Specificity = TN TN + FP.

How accurate is forecasting in Excel? ›

Most of the time, 95 percent is the standard value for the confidence interval. This means that Excel is 95 percent confident that the predicted value will fall between those two lines. Seasonality defines the repeating nature of your timeline. Most of the time, Excel will calculate this automatically.

How do you calculate forecast accuracy and bias? ›

You can determine the numerical value of a bias with this formula:
  1. Forecast bias = forecast - actual result.
  2. Forecast bias = forecast / actual result.
  3. The marketing team at Stevie's Stamps forecasts stamp sales to be 205 for the month. ...
  4. Forecast bias = 205 - 225.
  5. Forecast bias = -20.
29 Sept 2021

What is the forecasting formula? ›

The formula is "sales forecast = total value of current deals in sales cycle x close rate."

What is a forecast accuracy? ›

Forecast accuracy is the degree to which sales leaders successfully predict sales (in both the long and short term). Accurate sales forecasts are essential for making key decisions about short-term spending and deals for key accounts.

How do you calculate MAPE? ›

Once you have the absolute percent error for each data entry, you can calculate the MAPE. Add all the absolute percent errors together, then divide the sum by the number of errors. For example, if your dataset included 12 entries, you would divide the sum by 12. The final result is the MAPE.

What is forecasting in Excel? ›

The FORECAST Function[1] is categorized under Excel Statistical functions. It will calculate or predict a future value using existing values. In financial modeling, the FORECAST function can be useful in calculating the statistical value of a forecast made.

How do you calculate forecast error? ›

There are many standards and some not-so-standard, formulas companies use to determine the forecast accuracy and/or error. Some commonly used metrics include: Mean Absolute Deviation (MAD) = ABS (Actual – Forecast) Mean Absolute Percent Error (MAPE) = 100 * (ABS (Actual – Forecast)/Actual)

What are different methods of forecasting? ›

Top Four Types of Forecasting Methods
1. Straight lineConstant growth rate
2. Moving averageRepeated forecasts
3. Simple linear regressionCompare one independent with one dependent variable
4. Multiple linear regressionCompare more than one independent variable with one dependent variable
23 Jan 2022

How do you calculate data accuracy percentage? ›

You do this on a per measurement basis by subtracting the observed value from the accepted one (or vice versa), dividing that number by the accepted value and multiplying the quotient by 100.

What is a good forecast accuracy percentage? ›

Measure Sales Forecast Accuracy

If you are routinely within 10% with your Day 1 Forecast then you should feel pretty good. If not, it is time to find a way to improve your forecasts. Like most things in business, the fastest way to improve is to measure your current process.

How do you calculate accuracy in method validation? ›

Accuracy is measured by spiking the sample matrix of interest with a known concentration of analyte standard and analyzing the sample using the “method being validated.” The procedure and calculation for Accuracy (as% recovery) will be varied from matrix to matrix and it will be given in respective study plan or ...

What is MAPE score? ›

The Mean Absolute Percentage Error (MAPE) is one of the most commonly used KPIs to measure forecast accuracy. MAPE is the sum of the individual absolute errors divided by the demand (each period separately). It is the average of the percentage errors.

How do you calculate forecast bias percentage? ›

How To Calculate Forecast Bias
  1. BIAS = Historical Forecast Units (Two-months frozen) minus Actual Demand Units.
  2. If the forecast is greater than actual demand than the bias is positive (indicates over-forecast). ...
  3. On an aggregate level, per group or category, the +/- are netted out revealing the overall bias.
6 Aug 2021

What is the most common metric used for forecast accuracy? ›

MAPE: Mean Absolute Percentage Error is the most widely used measure for checking forecast accuracy. It comes under percentage errors which are scale independent and can be used for comparing series on different scales.

What three methods are used to determine the accuracy of any given forecasting method? ›

There is probably an infinite number of forecast accuracy metrics, but most of them are variations of the following three: forecast bias, mean average deviation (MAD), and mean average percentage error (MAPE).

What are the 3 types of forecasts? ›

There are three basic types—qualitative techniques, time series analysis and projection, and causal models.

How do I create a forecast sheet in Excel 2010? ›

Insert an Excel Forecast Sheet
  1. Enter Your Data series.
  2. Select Forecast Sheet under the Data Tab.
  3. Select either a Line chart or Column Chart and also choose a Forecast End date.
  4. Forecast sheet is created.
  5. Column chart styled Forecast sheet.
  6. Customize your Forecast sheet using the forecast option.

Why is forecast accuracy important? ›

It helps reduce uncertainty and anticipate change in the market as well as improves internal communication, as well as communication between a business and their customers. It also helps increase knowledge of the market for businesses.

Why is it important to measure forecast accuracy? ›

It's important to track forecast accuracy because it can help to: Improve the forecasting process: Tracking forecast accuracy can improve the overall forecasting process by helping businesses learn when they can make adjustments to a forecast to increase its accuracy.

How do you calculate MAD and MAPE in Excel? ›

How to Use Excel to Calculate MAD, MSE, RMSE & MAPE - YouTube

What is MAPE in ML? ›

The Mean Absolute Percentage Error (MAPE) can be used in machine learning to measure the accuracy of a model. More specifically, the MAPE is a loss function that defines the error of a given model.

Can MAPE be more than 100%? ›

Expressed as a percentage, which is scale-independent and can be used for comparing forecasts on different scales. We should remember though that the values of MAPE may exceed 100%.

How do you forecast data? ›

These are the three steps of predictive modeling: Ask a question and collect a sample set of time-series data that answers this question for a past time period. Train the computer software or forecasting algorithm using the past values. Use the forecasting algorithm to make future observations.

How do I calculate growth rate in Excel? ›

2 methods for calculating an average annual growth rate in Excel
  1. Annual growth rate = (ending value - starting value) / starting value.
  2. Average growth rate = annual growth rate / periods of time assessed.
  3. Compound annual growth rate = (ending value / starting value) ^ (1 / periods of time) - 1.
8 Oct 2021

What are the 7 steps in the forecasting system? ›

These seven steps can generate forecasts.
  • Determine what the forecast is for.
  • Select the items for the forecast.
  • Select the time horizon. Interested in learning more? ...
  • Select the forecast model type.
  • Gather data to be input into the model.
  • Make the forecast.
  • Verify and implement the results.

What model is best for forecasting? ›

Time series model

This type of model uses historical data as the key to reliable forecasting. You'll be able to visualize patterns of data better when you know how the variables interact in terms of hours, weeks, months or years.

What are forecasting tools? ›

A forecast is a useful tool for setting targets, as it allows you to assess the underlying factors in meeting a set goal, such as a targeted number of units sold or new clients gained, and determine a realistic projection.

How does pivot table calculate accuracy? ›

Click on pivot builder the entry Sum of Sales and select Value Field Settings. In the Value Field Settings window, on the Show Values As tab, choose % of Column Total. Click OK.
Create and format your pivot table
  1. Click anywhere in the data.
  2. Go to Insert > Recommended PivotTables. ...
  3. Click OK.

How do I calculate percentage quality in Excel? ›

The basic formula for calculating a percentage is =part/total. Say you want to reduce a particular amount by 25%, like when you're trying to apply a discount. Here, the formula will be: =Price*1-Discount %. (Think of the “1” as a stand-in for 100%.)

How do you calculate precision and recall in Excel? ›

F-Measure = (2 * Precision * Recall) / (Precision + Recall) F-Measure = (2 * 1.0 * 1.0) / (1.0 + 1.0) F-Measure = (2 * 1.0) / 2.0. F-Measure = 1.0.
We can calculate the precision as follows:
  1. Precision = TruePositives / (TruePositives + FalsePositives)
  2. Precision = 95 / (95 + 55)
  3. Precision = 0.633.
3 Jan 2020

What is precision formula? ›

Precision Formula

Precision evaluates the fraction of correctly classified instances or samples among the ones classified as positives. Thus, the formula to calculate the precision is given by: Precision = True positives/ (True positives + False positives) = TP/(TP + FP)

How does percent error measure accuracy? ›

The accuracy is a measure of the degree of closeness of a measured or calculated value to its actual value. The percent error is the ratio of the error to the actual value multiplied by 100. The precision of a measurement is a measure of the reproducibility of a set of measurements.

Can you do calculations in a PivotTable? ›

Here are the steps to add a Pivot Table Calculated Field: Select any cell in the Pivot Table. Go to Pivot Table Tools –> Analyze –> Calculations –> Fields, Items, & Sets. From the drop-down, select Calculated Field.

What is the formula to calculate percentage? ›

Percentage can be calculated by dividing the value by the total value, and then multiplying the result by 100. The formula used to calculate percentage is: (value/total value)×100%.

What is the formula of result in Excel? ›

Excel multiplies the last two numbers and adds the first number to the result.
=A2+A3Adds the values in cells A1 and A2=A2+A3
=A2-A3Subtracts the value in cell A2 from the value in A1=A2-A3
10 more rows

How is quality percentage calculated? ›

You only need to complete several quick steps.
  1. Subtract the actual value from the estimated value.
  2. Divide the results from step 1 with the real value.
  3. Multiply the results by 100 to find the total percentage.

What is accuracy precision recall and F1 Score? ›

F1 Score becomes 1 only when precision and recall are both 1. F1 score becomes high only when both precision and recall are high. F1 score is the harmonic mean of precision and recall and is a better measure than accuracy. In the pregnancy example, F1 Score = 2* ( 0.857 * 0.75)/(0.857 + 0.75) = 0.799.

How do you find the accuracy of more than one class? ›

To calculate accuracy, use the following formula: (TP+TN)/(TP+TN+FP+FN). Misclassification Rate: It tells you what fraction of predictions were incorrect. It is also known as Classification Error. You can calculate it using (FP+FN)/(TP+TN+FP+FN) or (1-Accuracy).

Top Articles
Latest Posts
Article information

Author: Ouida Strosin DO

Last Updated:

Views: 6454

Rating: 4.6 / 5 (56 voted)

Reviews: 87% of readers found this page helpful

Author information

Name: Ouida Strosin DO

Birthday: 1995-04-27

Address: Suite 927 930 Kilback Radial, Candidaville, TN 87795

Phone: +8561498978366

Job: Legacy Manufacturing Specialist

Hobby: Singing, Mountain biking, Water sports, Water sports, Taxidermy, Polo, Pet

Introduction: My name is Ouida Strosin DO, I am a precious, combative, spotless, modern, spotless, beautiful, precious person who loves writing and wants to share my knowledge and understanding with you.