SAles forecasting hierarchy glyphMulti-Level Sales Forecasting

Multi-level sales forecasting is the ability to generate sales forecasts over hierarchies of products and customers via:

  • Bottom up forecasting - at sku+customer level and aggregated up the hierarchies
  • Top down forecasting - at the Total level and spread down the hierarchies
  • Middle / both ways forecasting - at intermediate hierarchy levels and spread down / aggegated up the hierarchies

Prophecy™ fully supports multi-level sales forecasting.  If the user has the security rights to do it, a forecast can be changed at any level in the product and customer hierarchies.  This change is then spread downward over the 'children' of the forecasted item and aggregated upwards over the 'parents'.  All in real time and without the need for manual consolidations.

Example of Multi-Level Sales Forecasting

Product and customer hierarchical recalculation example

Here is an example of multi-level sales forecasting in Prophecy.  The hierarchy trees show customers and products, each with 4-level hierarchies. (Prophecy™ actually supports up to 15 levels of each.)

In the image, we are about to change the Volume sales forecast for 'PACKET TEAS' at level 2 of the product hierarchy and 'Other Multiples from level 2 of the customer hierarchy.  This is shown by the black connectors from the Prophecy screenshot at the top of the image to the hierarchy trees and the red boxed items in the hierarchy trees.
In other words, we are changing a middle level in both hierarchies.
The changes (including revenue and profit impact) are then pro-rated down every permutation of dependent product / customer combination (the green-boxed items in each tree) and aggregated up over the parent combinations (boxed in magenta).
Prophecy™ has been tuned and optimised to apply these changes to the database as quickly as possible.

Applications for Multi-Level Sales Forecasting

For sales forecasting

Data Perceptions advocates bottom-up forecasting as general sales forecasting best practice.  In other words, forecasting by SKU (stockkeeping unit) on the productaxis and at base level on the customer axis.

To avoid drowing in too much detail, base level in the customer hierarchy should be designed using the 80:20 (Pareto) rule. (See Data Perceptions whitepaper on applying the Pareto Principle to sales forecasting). In most companies, the top 20% of customers typically account for around 80% of sales, and should therefore be individually forecasted.  The remainder should be pre-aggregated into trade sector or geographical groups and forecasted at that level in Prophecy.

So, start bottom-up.  Then use a top-down or middle-both-ways perspective to 'sense check' the forecasts and identify / address issues like insufficient allowance for sku-on-sku cannibalisation or a general tendancy to optimism or pessimism in the forecasts (by, for example, sense-checking the projected annualised growth rate or comparing forecast with Prophecy's statistical forecast).

Prophecy's top down reforecasting can be used where the top-down review suggests that an overall change to the individual forecasts is required.  Providing, of course, a great time saving versus editing the item-by-item forecasts individually, as the forecaster would have to do in an Excel-based sales forecasting solution.

For 'massaging' Budgets and Sales Plans

Another use for top down or middle-both-ways forecasting is in managing budgets.  Prophecy™ can store one or more budgets as well as the evolving sales forecasts.  That way, users can review and compare the sales forecast (year to date actual plus rest of year forecast) with the budgets at any combination of product and customer hierarchy levels.  The budget may be initialised with the current forecast, but then 'tuned' and rephased over time to match the financial aspirations and objectives of the organisation.  Prophecy™ supports this requirement, not just for volume forecasts and budgets, but for the full extension to profit.