HOW TO DETECT PROBLEMS IN COLLABORATIVE DEMAND PLANNING WITH SIMPLE METRICS

Why plan? Why try to predict the future, if the market is so volatile and unpredictable? Why not just fulfill orders as they appear in our system? If even customers don't know what they're going to order, how are we going to know? We are constantly faced with questions like these when implementing planning processes, especially in commercial areas. And it couldn't be otherwise – time is a scarce resource, and it's to be expected to be questioned when we ask for a few hours a month to help the integrated planning team (or S&OP, IBP, POVE, etc.) put together a demand plan.

The short answer is: planning is important because it reduces uncertainty. Uncertainty leads to supply chain lead times, and lead times lead to costs. To name just a few: a product waiting to be sold (inventory) has an opportunity cost of invested capital and a risk of obsolescence/perishability; a machine waiting for a production order has an idle cost; a customer giving up waiting for a product and buying the competition has the cost of lost margin.

A good integrated planning process attempts, among other things, to reduce uncertainty and balance associated costs, balancing customer service, inventory levels, and supply chain productivity. And as every process needs good metrics to guide it towards continuous improvement, let's explore in this article some ways to measure the demand planning process (let's leave out service level metrics and operational planning), associating them with to common problems faced by the planning team.

THE PROCESS

In integrated sales and operations planning (S&OP) processes, demand planning is the first and most important input. The division of the various stages of the process and their names vary from author to author. For this article, we will adopt the names from the figure below.

We adopted a process that divides the Demand stage into four phases: Statistics, Analyst Plan, Collaborations and Unrestricted Demand. There are other equally valid ways of organizing the process, such as zero-based forecasting and bottom-up from salespeople to regional managers, but in our experience these four steps strike a good balance between accuracy, effort invested, and usefulness of the plan.

Statistic

In this step, mathematical models are applied to try to detect past patterns and extrapolate them into the future. There are several possible models for different cases, and this step is easily streamlined through automatic model selection mechanisms. This first number generates the basis on which the next steps will be discussed.

Analyst Plan

Not everything is explainable by a mathematical model. The role of the demand analyst[1] it is fundamental to enrich the plan with information external to the historical demand series – such as a new product launch or the lack of a competing product on the market – and to make adjustments to the series itself, as is the case with unusual sales. He should invest his time sparingly, analyzing the main products and regions and leaving the more stable and less important demands to Statistics.

collaborations

Collecting all relevant information for the demand plan for the next few months is not a simple job, and it is difficult for the analyst to have all of them at hand to include in the analyst's plan. The collaboration of the areas closest to the market is essential to guarantee the inclusion of all planning assumptions, and to generate commitment to the number. The analyst puts together the best plan he can with the information he has at hand, and suggests this plan to the employees, who can accept it or make adjustments according to their impressions of the market.

Unrestricted Demand

With all the collaborations in hand, it is necessary to reach a consensus, a single number that will be used for the planning of all the operations and finance functions. This consensus is often reached at a meeting, where major divergences between plans are discussed and a final number is reached.

Each of these steps brings a different value to the process, and face different deployment and maintenance issues, which can be detected and prevented with the right metrics.

BASIC METRICS

There are several metrics to measure the adherence of plans to reality. We've chosen three basic metrics that, in our experience, perfectly meet the needs of the overwhelming majority of companies. They are relatively easy to understand and implement, free of scale (which means that we can compare a product that sells a thousand units per month with another that sells a hundred thousand) and easily aggregated for management levels.

The first is the MAPE – Mean Absolute Percentage Error, or Mean Absolute Percentage Error. It tells us how much, on average, we are missing the calculation aggregation level[2], without compensating negative errors with positive errors. That is, if we sell a thousand units above the plan for product A in Rio de Janeiro, and a thousand units below the same product in Manaus, these errors are not compensated. After all, a lack of products in the Rio de Janeiro distribution center would hardly be compensated for by an excess of stock in Manaus. As we made a mistake twice (due to excess inventory in Manaus and a possible stockout in Rio de Janeiro), we have to account for both errors.

The second is WAPE, or WMAPE – Weighted Mean Absolute Percentage Error, or Weighted Mean Absolute Percentage Error. This tells us something similar to MAPE, but takes into account the relative importance of each product/location in the calculation. This importance (or “weight”) can be sales volume, revenue, total contribution margin, or even grades from 1 to 10. This helps to avoid, for example, that low volume products distort the indicator to up or down. A product that has a planned sale of 5 units and sells 10 units has a 100% error, but the operational and financial impact of this error is much smaller than a 20% error on a product that sells 5.000 units.

Figure 4. WMAPE Calculation Example, using actual demand as weight

The third and final metric in this article is MPE – Mean Percentage Error, or Average Percentage Error. Unlike the previous two, the MPE considers negative and positive errors, indicating whether the plan has a more pessimistic or optimistic bias. In the example below, negative MPE indicates that, on average, we are being optimistic with our plan.

Figure 5. MPE Calculation Example

All the analyzes that we are going to show use these three simple indicators, with small calculation variations.

PROCESS PROBLEMS

With these three simple metrics in place, the company can then measure the accuracy of its demand plan and find out if it is getting the product mix right, in the balance between the regions, if the error is concentrated in products of great importance, and if there is a general bias of optimism or pessimism. But what to do if the error is too high, or on a growing trend? Obviously, there is a randomness component to the error that is uncontrollable and inherent in the market in which the company operates, but there may be components of the error brought about by flaws in the planning process itself.

We have compiled below some of the main problems that we have already encountered in processes that we have implemented and monitored. These examples can be found in planning processes with different levels of maturity, and in companies of all sizes.

Badly invested analysis time

It is often infeasible to go through and analyze the plan for every product, location, and month of the planning horizon. Therefore, it is fundamental that the demand analyst knows how to prioritize his path of analysis and allocate his time in the weakest points of the plan. This problem usually has two facets: either the analyst does not prioritize and tries to encompass the entire plan, which leads to shallow analysis, or prioritizes the wrong vectors, and ends up leaving aside critical points.

The first question to ask yourself is whether the analyst's time is actually being invested in improving Statistics, or if we are ending up with an Analyst Plan with worse accuracy than the numbers automatically generated by the models. The answer comes from simply comparing the MAPEs and WAPEs of these two plans against demand over the last few months.

The second question is whether this problem is localized or widespread. The analysis of the MAPE by family and region helps in the search for the root cause of the problem. Another useful analysis for this purpose is the Pareto chart of the absolute error of the Analyst Plan compared to actual demand, which can indicate a concentration of the error in one product or location. We can find a large concentration of errors in forecasts based on assumptions of marketing actions that did not take place, or did not perform as expected.

Finally, we can compare WAPE with MAPE to assess whether the analyst is getting more right (and therefore probably investing more analysis time) in the most important products and regions for the company.

Lack of commitment to the plan

As we said in the introduction, getting commercial areas to commit to the planning process is not usually an easy task. Rarely does forecast error figure among the goals of these areas, and when it does, it is in a negligible percentage. Employees usually invest little time in analyzing the plan, which directly reflects on its quality.

Keeping each employee's plans separately and comparing their MAPEs for a few months with the Analyst's Plan is an excellent way to find symptoms of low commitment. With the analysis in hand, the demand analyst can talk to the employees individually, and try to make them aware of the importance of a good plan. Connecting the planning error with cases of low level of customer service is always a good argument. As a last resort, the analyzes can also be taken to the executive meeting to gain political support with the board.

Optimistic or Pessimistic Employees

Aggressive targets exist to challenge and motivate the sales team, and are a valid and effective tool to increase sales volume. However, they often have a negative side effect on planning: it's hard to separate the number we'd like to sell from the number we think we're likely to sell. No salesperson wants to present an under-budget plan to their board of directors, the general trend is for these numbers to come out pretty much the same. There is also the opposite effect, when sales targets are derived from the plan itself, which creates an incentive to underestimate the plan.

The MPE's analysis of the last months of the employees' plans can show these biases.

Of course, we're not saying that budgets and targets shouldn't be pursued. But when we know that the most likely scenario is not achieved and we increase the plan anyway, we are only inflating inventories and worsening the company's financial cycle. It is up to the S&OP team to negotiate scenarios for meeting the target with the commercial and the operations, so that the risks are balanced with the associated costs.

Hierarchical influence in the process

The integrated planning team, including the demand analyst, often has to demand deadlines and action plans from people higher in the hierarchy than they are. This is most evident in midsize companies, which rarely invest in a senior manager or director vacancy for the planning function, but it is the same in larger companies. We often see good demand analysts simply accept, without questioning, the plans of collaborators in the consensus stage (Unrestricted Demand), because they feel embarrassed to question the numbers of regional sales managers and channel managers, even knowing that the accuracy of these plans is not good.

The main symptom that this may be happening is a WAPE of unrestricted demand constantly higher than the analyst's plan. If this is happening, we recommend two analyses. The first is the comparative MAPE analysis of all plans, which can show whether the collaborators' plan is in fact less accurate than the Analyst's. We can use the Statistics MAPE as a benchmark, and show the relative MAPE of the other plans month by month in the analysis period.

The second is the analysis of the influence of the collaboration on the Unrestricted Demand, that is, on the final consensus of the process. To use the same metrics, we can calculate the MAPE between the employees' plan and unrestricted demand instead of actual demand. A low MAPE indicates that the two planes are very close. We can also compare this with MAPE between the analyst's plan and unrestricted demand, comparing the influences.

CONCLUSION

Integrated planning processes often represent significant cultural shifts, and are consequently very fragile and susceptible to failure. They are often seen as just another set of unproductive meetings, and a waste of time. Knowing what the most common problems are and how to detect them quickly is essential to react before the process loses credibility and falls to the ground.

Since the randomness of error is, on average, the same for all planners, we can discover many process failures by comparing plans against each other. For this, keeping the planning stages well defined and separated is fundamental.

Finally, in the search for process improvement, it is important to avoid creating a climate of accusations. The main message must always be that of collaboration for a greater objective.

[1] We are using the term “demand analyst” to designate the demand planning role. This role can be performed by more people, from different hierarchical levels, depending on the scale of the company.

[2] The calculation aggregation level is a function of the level of detail the operation needs. In our example tables, if it made no difference to stock/sell/produce in SP or RJ, we should have calculated the forecast errors for the products as if they had been planned for the same location. It should only be measured at the level that significantly impacts the company's operation. remember the trade-offs Utility x Effort x Resources.

Authors:
Diego de Souza, managing partner of Plannera
Daniel Silveira, Planning Specialist at Plannera