Tracking forecast errors with an Excel template (with free download)

ByLance T. Lee

Apr 22, 2022

Peter Drucker’s famous axiom “You can’t improve what you don’t measure” is particularly relevant to business forecasting. As demand planners, we want to measure our forecast performance so we can iterate and improve. Here I present an Excel-based forecast performance tracker (free download available below) that you can use for your own error measurement.

There are different methods and metrics to track and evaluate forecast performance. Some of the most used metrics are MAPE, WMAPE, MAD, MSE, RMSE, BIAS, Tracking Signal, as well as Michael Gilliland’s FVA (Forecast Value Added). Demand planning teams monitor and report forecast performance. When tracking forecast errors using these metrics, it is essential to know why the error occurred so that the root cause can be addressed. There will always be some innate volatility and variability in forecasting. And, since the prediction is validated by human interference and judgment, bias is always present to some degree.

Having an understanding of error allows us to make decisions that will reduce it. Forecasting errors can be problematic for organizations, not only within the supply chain/operations, but at the enterprise level. Although actions taken based on understanding forecast errors are reactive, we can use these actions to reduce future errors.

Forecast error simply defined is the difference between actual demand (sales) and forecast demand. Forecast Error = (Forecast – Actual) / Actual. Root Cause Analysis (RCA) can be divided into 3 categories: overstatement, product unavailability and understatement. The following table (Table 1) gives an overview of these 3 RCA classifications.

Figure 1 | Root Cause Analysis Classification Model

The RCA classification template above details our 3 classifications of product over, under and unavailability. The framework also gives details on negative or positive biases. Above all, it also displays some of the potential impacts on the business. There is also another factor we should be aware of that is not included in the table – random variation. If there is a random variation, the error usually corrects itself.

Template for tracking forecast error root cause analysis

Overestimation and underestimation are widely discussed in demand planning literature. However, I haven’t seen much discussion about product unavailability. An Excel-based forecast KPI tracker is prepared (see preview below).

[CLICK TO DOWNLOAD THE FORECAST TRACKER]

The most important elements are the forecast, actual sales and inventory (closing) for the given forecast period (month, week, etc.). To simplify, we use 2 products (P1, P2) and 3 locations (L1, L2 and L3). The forecast horizon is monthly, from January to April. Other details like sales rep, product segment, and categories can be added as per your business needs. The goal is to monitor forecast performance by product and location on a monthly basis.

You will also see the different error metrics: error, absolute error, MAPE/WMAPE, bias, overstatement, understatement, and product unavailability.

Forecast tracking screenshot

After

In this tracker, when you add the monthly forecasts, actuals, and inventory data, the rest of the report updates accordingly. All data analysis is handled in Excel with formulas, pivot tables and charts.

Forecast Performance Dashboard

The template contains an interactive dashboard which, at the end of the month, can be used to share forecast errors in demand planning sessions/S&OP meetings as a standard report. The dashboard presents data through powerful visualizations that describe the story behind key performance indicators, including key insights and recommendations on a single screen.

The most important component of the dashboard is key insights and recommendations. Coming into all meetings where the dashboard is used, demand planners should have a good understanding of key forecast errors and be prepared to facilitate discussion around actionable steps to address the causes. The goal is for senior management to make informed decisions.

Below (figure 2) you can see the dashboard. The main features are the monthly MAPE trend and the top locations and products with the highest MAPE for the month. For example, location L1 is experiencing an under-prediction error and therefore needs to be addressed in the meeting to identify what can be done to fix it. The L2 location faces a lower forecast. To some extent, this underestimation is correlated to product unavailability as sales attempted to offset the forecast target with products available and in demand.

Figure 2 | Forecast tracking dashboard snapshot

After

Benefits from Root Cause Analysis Forecasting

As Arthur C. Clarke said, “I don’t claim we have all the answers. But the questions are certainly worth pondering.This methodology does just that – allowing you to measure forecast error and discuss root causes in a simple yet effective way. With insight into root causes, you can better optimize your supply responses and shape demand accordingly. Improved forecast accuracy will naturally follow.

Key points to remember

1 – Demand planners must demonstrate strategic value by providing key insights and recommendations to facilitate informed decision-making.

2 – The purpose of these models is not to highlight “WHO” (any function area/role) but to effectively address the “WHAT” (cause of over or under estimation).

3 – Art is an important trait required for demand planners. They must transmit the key information, and only the data.

4 – Demand and supply variability is high these days, so be aware that improving forecast error has a limit as we have no control over external factors impacting demand.

5 – Estimating all error components from demand history is not possible (or even appropriate). Uncertainty is intrinsic.

6 – Demand planners should constantly develop data analysis skills with a clear approach to storytelling instead of only delivering reports based on convoluted mathematical formulas.

7 – Emphasis on forecast accuracy figures will lead to bias. Therefore, the focus should be on providing key highlights to the leadership team. The most consuming part of the reports are the information and recommendation sections that help businesses make better decisions.

8 – As mentioned in my previous blog, Segmentation framework for analysis of causal demand factors, Forecast accuracy is not the goal but a means to achieving broader business goals.

Do you find this model useful? Are there any other improvements that can be made? I am open to hearing from you.

Do you want to understand the logic behind this forecast performance tracker and dashboard in Excel? Connect me for a session. I’ll be happy to walk you through the tracker and dashboard.

Connect with Manas on LinkedIn and follow him on Medium.


For more information on demand planning, join us at IBF Global Conference on S&OP and IBP Best Practices in Chicago from June 15 to 17. You’ll learn the ingredients for effective planning, whether you’re just getting started or refining an existing process. The Early Bird price is now open – more details here.


Source link