How to Calculate Forecast Accuracy in Excel

Creating a sales forecast is one thing, but knowing if it's any good is another. Measuring your forecast's accuracy is the only way to improve it, helping you make smarter decisions about inventory, staffing, and budget. This article will walk you through exactly how to calculate the most important forecast accuracy metrics right inside Microsoft Excel.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Bother Measuring Forecast Accuracy?

Jumping straight into calculations without understanding the "why" can feel like busy work. So, why should you track how accurate your forecasts are? It boils down to a few key business benefits:

• Better Resource Planning: An accurate forecast helps you avoid costly mistakes. For retailers, it means not overstocking on a slow-moving product or running out of a bestseller. For service businesses, it helps with staffing levels, ensuring you aren't overstaffed during lulls or understaffed during peaks.
• Improved Financial Health: Reliable forecasting is the bedrock of a solid budget. It informs revenue projections, which in turn dictate how much you can spend on marketing, hiring, and new projects. The more accurate the forecast, the fewer financial surprises you'll have.
• Increased Trust and Credibility: When you consistently produce accurate forecasts, you build trust with stakeholders, whether they're team members, department heads, or investors. A 'forecast vs. actual' report with minimal variance shows you have a strong handle on the business.
• Continuous Improvement: You can't improve what you don't measure. By regularly calculating forecast accuracy, you can test different forecasting methods, identify biases like consistently over- or under-forecasting, and refine your model over time.

In short, measuring forecast accuracy turns forecasting from a guessing game into a strategic tool for growth.

Setting Up Your Data in Excel

Before we get to the formulas, you need to structure your data correctly. All you need are three simple columns. Your spreadsheet should look something like this:

• Column A: The time period (e.g., Day, Week, Month).
• Column B: Your actual results for that period (e.g., "Actual Sales").
• Column C: Your forecasted numbers for that period (e.g., "Forecasted Sales").

It’s important that each row corresponds to the same time period. Having clean, parallel data is the foundation for all the calculations that follow.

Here’s a sample dataset we'll use for our examples. You can copy this directly into Excel to follow along.

A1: Month B1: Actual Sales C1: Forecasted Sales

A2: Jan | B2: 2,100 | C2: 2,000 A3: Feb | B3: 1,950 | C3: 1,900 A4: Mar | B4: 2,300 | C4: 2,400 A5: Apr | B5: 2,550 | C5: 2,500 A6: May | B6: 2,700 | C6: 2,650 A7: Jun | B7: 2,850 | C7: 3,000 A8: Jul | B8: 3,100 | C8: 3,000 A9: Aug | B9: 3,000 | C9: 3,100 A10: Sep | B10: 2,750 | C10: 2,700 A11: Oct | B11: 2,600 | C11: 2,500 A12: Nov | B12: 3,500 | C12: 3,300 A13: Dec | B13: 4,100 | C13: 4,000

With this data in place, you’re ready to start analyzing your accuracy.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Calculating Key Forecast Accuracy Metrics in Excel

There are several ways to measure forecast accuracy, each telling you something slightly different about your performance. We’ll cover the four most common and useful metrics: Forecast Error, Mean Absolute Deviation (MAD), Mean Absolute Percentage Error (MAPE), and Root Mean Square Error (RMSE).

1. Forecast Error

The simplest starting point is the Forecast Error. This tells you the direction and magnitude of the error for each individual period.

What it is: Forecast Error is the difference between the actual value and the forecasted value.

• A positive error means you under-forecasted (the actual was higher than the forecast).
• A negative error means you over-forecasted (the actual was lower than the forecast).

How to Calculate it in Excel:

1. In cell D1, create a new header called "Forecast Error."
2. In cell D2, type the following formula and press Enter:

=B2-C2

1. Click on cell D2, grab the small square (the fill handle) in the bottom-right corner, and drag it down to the last row of your data (D13) to apply the formula to all months.

Your "Forecast Error" column will now show you exactly how far off you were each month. While useful for a period-by-period review, these numbers aren't great for summarizing overall performance because the positive and negative values can cancel each other out.

2. Mean Absolute Deviation (MAD)

To get a better sense of the overall error magnitude, you need to use a metric that ignores the direction of the error. That’s where Mean Absolute Deviation (MAD) comes in.

What it is: MAD is the average of the absolute errors. It tells you, on average, how far your forecast is from the actual result, regardless of whether you were over or under.

How to Calculate it in Excel:

1. First, calculate the Absolute Error. In cell E1, create a header called "Absolute Error."
2. In cell E2, use the ABS function to convert the forecast error into a positive number:

=ABS(D2)

1. Drag the fill handle down from E2 to E13. Now you have a column showing the magnitude of the error for each month.
2. Next, find the average of these absolute errors. Click on a cell below your data (e.g., E15) and type the following formula to calculate the average of the range E2:E13:

=AVERAGE(E2:E13)

The result is your MAD. If your MAD is 95, it means that, on average, your monthly sales forecast was off by $95.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

3. Mean Absolute Percentage Error (MAPE)

MAD is fantastic, but it's expressed in the same units as your data (e.g., dollars, units sold). This makes it hard to compare forecast accuracy across different products or datasets. A $100 error is huge for a product that sells $500 a month but tiny for one that sells $50,000. MAPE solves this by expressing the error as a percentage.

What it is: MAPE is the average of the absolute percentage errors. It's one of the most common metrics for reporting forecast accuracy to leadership because it’s so easy to understand.

How to Calculate it in Excel:

1. First, calculate the Absolute Percentage Error for each period. In cell F1, type the header "Absolute Percentage Error."
2. In cell F2, enter this formula. It calculates the absolute error and divides it by the actual value to get a percentage:

=ABS(D2)/B2

1. Drag the fill handle down from F2 to F13.
2. Select column F and format it as a percentage from the Home tab in the Excel ribbon.
3. Now, find the average of these percentages. In a cell below (e.g., F15), use the AVERAGE function:

=AVERAGE(F2:F13)

If your result is 4.10%, it means your forecast is, on average, off by about 4.10%. MAPE is extremely useful, but be careful: it can be skewed if you have any periods with actual sales close to zero.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

4. Root Mean Square Error (RMSE)

Sometimes, large errors are exponentially more damaging to your business than small errors. Running out of stock by 500 units is much worse than being off by 10 units twice. RMSE is a metric that gives significantly more weight to these larger deviations.

What it is: RMSE is the square root of the average of the squared errors. By squaring the errors, larger mistakes are penalized much more heavily, making RMSE a good metric to use when forecast accuracy is critical.

How to Calculate it in Excel:

1. First, square the forecast errors. In cell G1, create the header "Squared Error."
2. In cell G2, enter the formula to square the original forecast error from column D:

=D2^2

1. Drag the fill handle down from G2 to G13. You'll notice the squared values for larger errors are much bigger.
2. Then, find the average of these squared errors. In a cell below (e.g., G15), calculate the average of the range G2:G13:

=AVERAGE(G2:G13)

1. Finally, take the square root of that average. In the cell next to it (e.g., H15), use the SQRT function to find the square root of the result from the previous step:

=SQRT(G15)

The final number is your RMSE. It's interpreted similarly to MAD but will always be larger, with the difference growing as the variance of your errors increases.

Choosing the Right Metric for the Job

So, which metric should you use? It depends on your goal:

• Use MAD for a straightforward, easy-to-calculate average error in your native units.
• Use MAPE when you need to compare forecast accuracy across different items or want a simple, universal percentage to report.
• Use RMSE when large errors are particularly harmful and you want a metric that heavily penalizes them.

The best practice is often to track multiple metrics. Viewing your data through the lens of both MAPE and RMSE can give you a more complete picture of your forecasting performance.

Final Thoughts

Calculating forecast accuracy is a fundamental step toward improving your business planning and making more reliable, data-driven decisions. By using simple Excel formulas like MAD, MAPE, and RMSE, you can regularly evaluate your performance, identify weaknesses, and continuously refine your forecasting models to be more precise.

While Excel gets the job done, repeatedly downloading CSVs, updating your sheet, and re-checking formulas can become tedious. We built Graphed to automate precisely this kind of manual work. You can connect your live data sources like Shopify, Salesforce, or Google Analytics and ask for a forecast accuracy report in plain English. We instantly generate real-time, interactive dashboards that refresh automatically, freeing you from spreadsheet-wrangling so you can focus on the insights.