When it comes to visualizing data in the S&OP process, most stakeholders like to see forecasting outputs in the form that they are most familiar with – Excel. This is certainly true in my experience working in global FMCG companies.

Below I present an Excel tool which I have used many times as a Demand Planner. What you see below has been employed at the Demand Review meetings to great effect at large corporations with very mature demand planning and S&OP processes. It is an ideal solution both for smaller companies just starting their S&OP process that do not want to make a big investment in technology, as well as bigger companies where stakeholders want an easy to understand and recognizable format of data presentation.

Click Here To Download The Forecasting Demand Review Tool

It opens in the web version of Excel. I recommend to download to your computer and open in the Excel application. You can input your own data into this tool from an advanced system (e.g. statistical forecast from APO).

Purpose Of The Tool

The purpose of the tool is too support discussion at the Demand Review meeting with data in format familiar to all stakeholders, and with analytical functionalities that will help to finish the meeting with best possible consensus forecast, including incorporating information from Sales.

As the meeting involves many departments including Demand Planning, Sales , Marketing and Finance, the goal is not to review forecasts SKU by SKU, but on an aggregated level. The level of aggregation depends on the product complexity, i.e. the number of categories/brands/SKU’s. Discussion should be had for groups of products with similar sales characteristics, e.g. groups of products that are sold in similar percent splits. Further volume split per SKU can be determined by more or less advanced statistical methods.

View & Functionalities

The tool operates on historical sales data and forecast figures. Data are presented on different level of aggregation. For product dimension, the tool presents historical sales data and forecast at three aggregation levels: product group/segment/category. For the customer dimension, there are also 3 aggregation levels at which the user can review historical sales: customer/channel/market.

Selection and historical data:

The above screenshot shows customer and product data. The user can select historical sales data and forecast of Product Group A. It is possible to drill down to sales of Product Group A for particular a customer or sales channel. The tool shows the current and previous two years of sales. The reason I choose 2 previous years of sales is that when looking at the chart it is quite easy to understand the seasonality of the product.

Forecast data:On the above screenshot you can see forecast data from the last cycle, labelled “Frcst 2019”, then below, you can see the new figures which were updated in the meeting. The user simply types new figures in this line at the lowest level of Product dimension (Product Group) and saves them. The next line “Change 2019” shows the differences between the updated and last cycle figures. The line “Stat forecast 19” is a Seasonal+Linear regression model that is built in Excel and calculates a statistical forecast based on historical sales in the current selection.

The user has the possibility to select what kind of data lines he/she would like to see on the chart through check boxes.

Exclude customers functionality:

The functionality in the red box in the above screenshot allows users to exclude particular customers simply by pasting their name into the pink area. It is useful when we want to look at overall market sales without customers that cause outlying sales. These volatile customers can skew our view of the whole market.

How To Use The Demand Review Tool

Usually, a more detailed customer forecast revision is done for the period which is going to be frozen in current S&OP/IBP cycle. Demand review and analysis of the forecast for this particular month can be structured in the following way:

Forecast analysis on product group level for frozen month:

• The starting point for analysis can be last year’s figures (naïve forecast)
• Creation of building blocks – separation of 2 or 3 top customers with biggest events (in analogical period LY or planned for future) and rest of the market
• Revision and agreement on the forecast for each building block
• Aggregation of figures

Such an approach differs from the standard approach where segmentation is done according to product dimension. This higher-level approach can help Demand Planners to speak the same language as the Sales team which is oriented towards activities per customer. We can imagine a team of Key Account Managers and each of them comes to the meeting sharing details about their customer/channel. The above tool can be very useful in consolidating their input and challenging it with data (historical sales or statistical forecast).

Another benefit of the tool is easier estimation of promo uplift for particular customers (e.g. by finding periods of similar promotion in data). It is impossible to judge promo impact of a particular customer looking at data for whole market. Of course, the alternative is to deep dive into sales reports per customers.

High-Level Demand Review Process Steps

• Sales to prepare info of upcoming events by customer that will impact sales and prepare info about marketing campaigns/events
• Demand planning to analyze historical data, especially for focus month and recent trends
• Reviewing most important/volatile SKU’s.
• Revision of the forecast by product groups (see chart above) and agreement on final figures

The role of Demand Planning is to work out the optimum SKU mix. Commercial teams should not be involved in forecast revision SKU by SKU. However, if they are aware about events concerning single SKUs (e.g. recommendation at the cashier or new listing), the Demand Review meeting is the right place to deliver such information.

Summary

People and the underlying process are the foundation of effective S&OP, especially for companies that are just starting to implement it. This Excel tool can be a great support to start your S&OP journey. After building process maturity, investment in advanced systems can provide further results.

I have never seen two demand planners working in the same way. Sometimes I am really surprised how different the same role can be in different organizations – or even within the same organization. There’s one universal truth, however: that interaction with sales teams is challenging, and getting the most out of them requires a well-thought out plan.

The worst-case scenario I can imagine for a Demand Planner is where they are limited to the role of “stock provider” who merely takes figures from Sales and maintains the system. The best-case scenario is when a real partnership is built with Sales teams where they provide not only quality inputs for forecasts, but collaborate with Demand Planners about how to best quantify their input.

Sales Are Not The Enemy

A stereotype outside of Supply Chain is that Demand Planners have the wrong priorities, like focusing on forecast accuracy metrics instead of fulfilling customer orders. I think that all Demand Planners agree that they’ll be in much bigger trouble when there’s insufficient stock to fulfill customer orders than failing to meet accuracy targets. But it is up to us as Demand Planners to communicate how things like forecast accuracy metrics help ensure there’s sufficient stock to fulfill customer orders. A lack of understanding and transparency about what we do erodes confidence in the forecasting process, and can lead to a situation where agreement on final numbers is driven by silo-based interests instead of fact-based analysis. The work of Sales teams and Demand Planners should be complementary, not competitive – and that means being transparent about what is it you’re doing and how it serves the aims of Sales.

Here I share two scenarios of cooperation with Commercial teams, one ineffective and one effective.

How To Ineffectively Work With Sales Teams

Coming to the meeting with pre-defined numbers from both sides is not the best idea. Why is that? Firstly, because two teams are doing the same work, and it is a waste of time for two functions to do the same thing when it can be dome by one.

Secondly, a competitive approach to the numbers is pointless – it does not help the company gain an accurate view of future demand. What’s more, the Sales team is likely to win this pointless game every time for one simple reason: asymmetry of information. Both Demand Planning and Sales have the same historical data but Sales has the advantage of knowing upcoming events that will impact sales orders. By events I mean activities that will not only boost sales like promotions but also other factors that can negatively impact demand (lack of repeatable promotion, loss of distribution, price increases etc.).

At this point we need to ask an important question: Should Sales teams actually be involved in forecasting at all? Let’s move to the second scenario where I will provide the answer to this question.

How To Effectively Work With Sales Teams

Sales should focus on selling, not forecasting. But they do need to be part of the S&OP process. There are 3 major areas where they need to add value to the S&OP process: providing unbiased, quality information; consulting on final figures for the forecast; and providing input to post-validation of forecast accuracy.

I will expand on each of these points:

Providing unbiased quality information: An example here would be promotions by a customer (type of promo and when). The action for the Demand Planner would be to find in historical data what kind of uplift was generated by similar promotions by this customer in the past, and combining it with statistical forecasts for the remaining customers to arrive at a total figure for whole market. The above discussion between teams can happen at the first demand review meeting whose aim is to gather assumptions to prepare the forecast. Methods for including the promo uplift data into the statistical forecast is a topic for very long debate, and I will not go into details in this article.

Consulting on final figures: Once the Demand Planner prepares the forecast based on a combination of statistics and the estimated promo uplift, Sales should tell us if these figures makes sense. If not, what assumptions should be changed or estimated differently? Is the statistical trend wrong? Should promo volumes be projected differently? This discussion ideally takes place at the second demand review meeting before reaching consensus and presenting the forecast to upper management. The level of discussion is not SKU level, but rather product group or another aggregation level that makes sense depending on the business.

Input to post-validation of accuracy results: Updating the plan/forecast is only one side of the coin, similar attention should be paid to post-validation of forecast accuracy and finding the root causes of errors. Here Sales plays a major role as they have access to all market knowledge that helps to interpret actual sales data. An example here is ad-hoc promotion initiated by customers which explains a sales peak that was not accounted for in the forecast. The role of demand planner is to keep track of circumstances that can explain data anomalies.

There are 3 benefits to be gained from proper documentation of forecast error root causes. Firstly, it gives us information to be used in future (like overly-optimistic assumptions for promo sales or new products). Secondly, this information helps us to clean sales history or decide about adjustments to forecasts for corresponding months next year. Thirdly, it means we have documented knowledge that we can pass on to the new Demand Planner when the current one leaves the company or changes his/her role.

The above points can be summarized by below graphic:Summary

Sales’ engagement is critical for a successful forecasting process and, subsequently, a successful S&OP/IBP process. One question that remains is what their actual role should be in the process. Should it be preparing their own high-level estimations or focusing on providing reliable information? I am signing up for the latter. Obviously, information provided by Sales will always carry some degree of uncertainty as customers can change their mind at the last moment.

Sales teams should not hesitate to give their opinions, and be prepared to consult on, and sign off on, the official figures. However, they should not spend time on preparing their own estimations as this is the Demand Planner’s responsibility and area of expertise. Although it takes time to break silos and build trust between functions, the above efforts can deliver better forecast accuracy and better control of planning.

Imagine you’re in the middle of the monthly S&OP meeting, and you’ve come to the point on the agenda that says: “review forecast accuracy KPIs”. You can already feel the Salespeople zoning out because they don’t have a clue what you’re about to talk about. And when we talk at length about MAPE and the like, can you blame them?

Salespeople are not familiar with forecast accuracy measures. They have no reason to be because it is not clear how these KPIs add value. They don’t help sell a product and they don’t make money in any obvious way. For me this is quite understandable and touches upon an important concept – that there has to be demonstrable monetary value in every step of the S&OP process. If our KPIs don’t reveal to other functions how S&OP makes the company money, then we’re not doing or jobs properly.

One way to get Commercial’s buy in into S&OP process is to show forecast accuracy as a cause-effect relationship

Show Forecast Accuracy As A Cause/Effect Relationship

One way to get Commercial’s buy in into S&OP process is to show forecast accuracy as a cause-effect relationship, showing predicted sales and actual sales, and the reasons for those particular numbers. What’s more, we need to demonstrate tangible KPIs like inventory, OTIF (On Time In Full) and SLOB (Slow moving and obsolete). These are far more real and relevant to the Salesperson than MAPE. You can easy explain how decreasing SLOB has a positive effect on bottom line, but are you going to convince Sales of the value of MAPE? Almost certainly not.

Often we lose track of this basic idea – that everything we do is designed to make money.

Measures like inventory level, OTIF, SLOB and write-offs have two main causes: forecast or supply. Tracking root causes behind performance of these measures is a standard activity and translating these into dollarized amounts will get peoples’ attention. Inventory, SLOB & write-off cost driven by forecast error is something everybody in monthly S&OP meeting should pay attention to.

In most companies you can trace the reasons for unfulfilled orders. Lost revenue due to forecast error is easy to identify – SLOB can easily be attributed to over-forecasting, for example. It’s easy to communicate and gets quite lot of interest from other functions at the meeting. The agenda for the monthly S&OP meeting should include presentation of KPIs, not only forecast accuracy but all financial KPIs affected by the forecast. Make sure to discuss the root causes.

Dollarize Your Forecast Accuracy

The main goal of S&OP is to increase profitability by fulfilling the highest level of customer orders whilst optimizing inventory. It is impossible to present the results of a Demand Planner’s work without referring to main these main principles. But often we lose track of this basic idea – that everything we do is designed to make money. In Forecasting and Demand Planning, there is not enough space dedicated to dollarizing forecast accuracy. Dr. Jain, Editor of the Journal of Business Forecasting, said in one of his interviews that “an average company can save $3.52 mil. for every one-percent improvement in the under-forecasting error, and $1.43 mil. in improving the over-forecasting error.” That speaks for itself and if you can put MAPE in these terms, that X increase in your forecast accuracy has delivered Y dollar amount, then Sales will immediately be more engaged.

Showing your forecast accuracy in this way, including historical data to show improvement, is a way of communicating the value of the S&OP process. This can help secure resources for the process like more staff. Not only that, making it clear to Sales that we as Demand Planners actually make money will make them more inclined to give us their time.

The key to success here is to show the impact of forecasts in dollarized amounts.

Evaluating Your S&OP With KPIs Is Very Important

A big topic of conversation in the Demand Planning community is how to make S&OP work and research shows that a lot of he time, it just doesn’t work at all. If S&OP isn’t working for your company, remember that a good process measures its outputs and evaluates itself. As S&OP is the process that all function are plugged into, all functions should be interested in how it’s performing. Forecast accuracy metrics are not always interesting for commercial functions because they are complicated or appear irrelevant. The mathematical calculation formulas might be off-putting for the less numerically inclined, but the good news is that the interpretation is quite easy and straightforward. The key to success here is to show the impact of forecasts in dollarized amounts.

What comes to mind when you think about the Demand Planning role? Sophisticated econometric models, highly analytical work, and number crunching? This was my first impression when I saw an advertisement for an internship in a Demand Planning department. The reality turned out to be a little different…

How Effective Is Your Company’s Forecasting Process?

What struck me when I started my first job in a Demand Planning team was that you learn a lot about reports and templates, and read a lot of PowerPoint decks, but you don’t learn much about how to actually build a good forecast for your portfolio of products. I started to dig a little deeper into how to improve my forecasts, and what occurred to me was that every one of my colleagues had a different approach to forecasting. The goal of this article is to initiate a discussion on the different ways of working in Demand Planning, and to discuss bottom up and top down forecasting, and why we need to think beyond these two methodologies.

The base forecast generated by statistical models is less biased as it is derived from the performance of each product.

Bottom Up Forecasting

The fundamental component of the bottom up forecasting process is statistics (usually through SAP APO software). The process is organized with a baseline forecast on the SKU level generated by a centralized team, and a final forecast that combines statistics with market intelligence from local Demand Planners working in particular markets. The monthly process consists of the following:

• Statistical models are recalculated with data from the recently finished month.
• Monthly calls between the local Demand Planner and the Central Demand Planner to discuss recent forecast changes, top sales outliers and major root causes of forecast errors.
• The base forecast Demand Planner meets with the Sales team to add market knowledge.
• Creation of a separate forecast for New Product Development and New Product implementation provided by Marketing.
• Presentation of final forecast figures to upper management to achieve official sign off on forecast figures (reflected in USD value, not volume).
• Develop Operational Sales Plan with monthly update to aid product availability forecast.

Sales teams members must share responsibility for forecast accuracy. That means putting narrow functional objectives to one side.

Top Down Forecasting

The top down forecasting process relies strongly on Sales engagement and is more simplified. This process consists of:

• Monthly forecast revision with Sales team representatives (which is an additional function within the wider Sales structure) who bring projected sales figures at the product family level.
• The Demand Planner challenges those figures and its major assumptions.
• The final forecast figures form the basis of the Operational Sales Plan for the company, which is signed off by management (working to the one number concept).
• The Demand Planner forecasts a mix of products, arriving at a total figure for the product family as agreed with Sales.

The base for forecasting the product mix is historical sales split within product family. The split is modified to include all latest information like new listing and delisting at customers’ stores, promotions, Marketing support, New Product Developments and cannibalization.

In both processes, Sales teams have very different objectives to us as Demand Planners.

Pros And Cons Of Each Process

In both methods, the role of the Demand Planner and its impact on final figures is different. The advantage of the bottom up approach is higher autonomy for the Demand Planner and greater control. The base forecast generated by statistical models is less biased as it is derived from the performance of each product. On the other hand, higher engagement with Sales in the top down forecast process gives the benefit of inputting valuable market intelligence.

In both processes, Sales teams have very different objectives to us as Demand Planners. In bottom up forecasting, they tend to increase volumes to secure the stock to avoid missing out on potential sales, which leaves the company at risk of holding excess inventory. Sales also declare numbers only in dollars and on a brand level. Once this Sales Plan is signed off by management, these numbers form the basis of the Operational Sales Plan, meaning the Sales team’s conservative bias affects the whole organization.

In top down forecasting, as the final figures become the official Sales Plan, Sales try to lower them as it is always better for them to set low targets and outperform them, rather than set a realistic range. For top down forecasting it is important, in my opinion, that Sales team members participating in the forecasting process are sharing responsibility for forecast accuracy. That means putting narrow functional objectives to one side.

These Two Forecasting Methodologies Place Limitations on Demand Planning

I cannot help but feel these two approaches place limitations on the Demand Planner function and overall forecast accuracy. There is a need of wider knowledge regarding different ways to work in the Demand Planning role and different methodologies, especially for young people entering this profession. Such knowledge should cover the topics Demand Planners face day to day like incorporating input from Sales regarding a promo event (i.e how to translate information into volumes), how to structure the forecast review meeting, the best tools for forecast analysis, and how to challenge the Commercial team using your statistical output.