Simple rules to avoid sales forecasting mistakes

Whether you generate sales forecasts using sophisticated statistical or judgemental methods, you will need much more from your demand forecasting software.  Especially where the same software extends the forecasts to revenue, margin and beyond for account planning and financial forecasting / budgeting purposes.

Our previous blog post, 6 key components of an integrated sales forecasting solution provides a good starting point on what to look for.

In this post, we look at a key element of a single component – “Control”.  How do we eliminate silly mistakes, like forecasting a quantity without a price or standard cost?  How do we lock the future horizons for some items and not others?  How do we ensure that the forecasts for some items in nominated customers meet a minimum order quantity?  How can we prevent users forecasting more than a ceiling quantity on a limited stock item?

These ‘control’ items don’t help you see into the future better.  But they do help you produce better business forecasts.

Take control of your forecasts!

The November Prophecy™ update contributes further to one of the six key components of a sales forecasting software solution, ‘Analysis and Control’.

Specifically, Prophecy™’s new rules engine helps you avoid and optionally correct data entry errors or other types of forecasts which violate a flexible set of rules defined in your Prophecy™ database.

Here is a quick video of the new Prophecy™ rules engine in action:

How do Prophecy™ rules help you produce more accurate forecasts?

Effectively, they catch your mistakes as soon as they happen.  They are fully customised to your Prophecy™ implementation (and can typically be implemented in minutes):

    • Prevent a volume forecast unless there is already a matching Price and / or Standard Cost.  Or warn the user when this issue occurs.
    • Prevent inadvertent deletion of Price forecast where this is an existing volume forecast.
    • Lock forecast periods for specific customers and products only.
    • Enforce a minimum order quantity by SKU (and customer, if relevant).
    • Automatically round forecasts to whole pallet quantities for specified customers (and skus).
    • Enforce (or warn the user) a maximum or minimum volume forecast per time period, based on any metric.  For example, not greater than Budget, not greater than three times the recent running rate etc..
    • Limit allocation of a sku in a future time period to a preset maximum across all customers.

These are just ideas of what’s possible.  In fact, virtually any sales forecasting rule you care to think of can be implemented.  The only requirement is that the rule logic returns a simple Pass / Fail result, and an optional ‘corrected forecast’.

The rule execution can utilise data from any SQL source (not just the Prophecy database) in its logic.   That’s it – loads of power for a very, very low entry cost!

The technical bit

There are several pre-defined rule types.  The most flexible rule type is a SQL-based rule, which can run any SQL against any data source.  The SQL simply needs to return one record containing one field, which contains either a ‘1’ or a ‘0’, where ‘1’ means the rule is triggered.  A second (optional) field, if provided, contains the ‘corrected’ forecast that will replace the existing forecast in Prophecy™ :

Sales forecasting rules implementation
The SQL must return the ‘Fire’ field, where ‘1’ means the rule was triggered and ‘0’ means the forecast passed the rule test.

One last thing to add to the ‘techie’ bit.  The SQL-based rule is written in T-SQL, the language of Microsoft SQL Server.  Meaning you have full access to stored procedures etc., not just standard ‘simple’ SQL.


Here are a couple of screenshots, extracted from our November Newsletter:

Example sales forecast rule message box
This rule is triggered when the user enters a quantity but there is currently no forecasted Price.
On demand sales forecasting rules check dialog
This dialog lets you choose which rules to run in an ‘on demand’ rules check
Results window for on-demand sales forecast rules check
Results window for on-demand rules check. Clicking on a warning or error navigates to the corresponding cell in your Prophecy report.

Sales forecast rules can be configured to run on data entry, or on demand.  On entry means the rules run immediately a forecast goes into a cell in a Prophecy report.  On demand means the rules can be run over all the items in the Prophecy report in a single batch.

Bottom line

There’s a lot more to improving sales forecasting accuracy than just applying statistical algorithms to history.  Prophecy™’s rules-based validations and checks are just one of the many ways that Prophecy™ adds value and quality to your forecasting process.  Control out-of-range or missing forecasts, set rules for sensible maximums or stock availabilities, or any other unique, implementation-specific requirement.  All these requirements, and many more we haven’t thought of yet, can now be met easily within Prophecy™!

6 key components of an integrated sales forecasting solution

This post covers the 6 main pillars of any sales forecasting solution. Yes, it relates them to our solution, Prophecy. But they apply equally to any solution, not just ours, and therefore represent a blueprint for a sales forecasting solution.

Here’s a summary graphic:

Components of a sales forecast software solution
6 components of a comprehensive sales forecasting software solution

1. Multi-level forecasting

Multi-level sales forecasting means forecasting through time, over measures, across multi-level hierarchies of customers and products.

In other words, it lets you forecast at the level of hierarchical detail that exactly meets your need at the time. Bottom-up, top-down or middle and both ways.

It’s true that there is probably no substitute for forecasting at the sku level for the best accuracy. However, a top-down view and forecast override can help correct generally over-optimistic or over-pessimistic forecasts at the sku level.

And top-down saves potentially many hours of manual work when flexing Budget.

Prophecy supports bottom-up, top-down and middle and both ways multi-level forecasting, subject to each user’s security setting.

2. Multi-user forecasting

Forecasting is seldom a single-user activity. Frequently it is the major account sales people who are the active forecasters – after all, they know most about what is going on in their customers.

Concurrent multi-user access to a forecasting solution is a technical challenge. Spreadsheet based solutions face this major hurdle.

Without a single-source, shared forecasting solution you get:

  • Cumbersome manual consolidations
  • Integrity issues with multiple individual documents
  • Maintenance issues – e.g. adding new products, actuals etc..
  • Delays in the forecast / review / re-forecast cycle

Prophecy has supported multiple user, concurrent read-write access to a single, shared database from day 1.

3. A statistical forecasting engine

Business forecasting in the real world doesn’t always lend itself to statistical forecasting. Peaks and troughs caused by promotions, competitor activity, distribution and listing changes and other non-recurrent factors don’t reliably happen at the same time each year. Statistical algorithms don’t ‘know’ about them.

In addition, adding one new period of actuals to the history used by the statistical algorithm surely adds a lot less value to the new forecast than the up-to-date specialist market and customer knowledge of the business forecaster. Please see our white-paper on the pros and cons of statistical forecasting here.

On the other hand, statistical forecasting may be useful as a starting point for an an initial forecast. For example, when you’re initially forecasting a new year. It may also be useful in applying the Pareto principle – where 80% of skus typically account for just 20% of sales. These smaller-selling items can be forecasted with statistical forecasting and reviewed on an exceptions basis via periodic accuracy analysis.

Prophecy has long had a core statistical forecasting engine. It generates forecasts in batch, on demand or as a way of calculating the value added factor of human forecasting.

However, with effect from February 2019, Prophecy statistical forecasting received a major ‘power-up’. It now enables Prophecy users to run of all the forecasting algorithms in the open source R framework for statistical computing, either in ‘Automatic’ mode (forecaster requires minimal statistical / zero R knowledge) or ‘R-Expert’ mode (which provides ‘to the metal’ access to all elements of the full R framework).

As a result, Prophecy enables the generation of statistical forecasts on a par with the very best statistical forecasting systems.

4. Multi-level revenue and profit forecasting

If your forecasting solution cannot integrate volume forecasting for production and inventory control with financial forecasting, how do you know whether what’s planned to sell will deliver the financial forecast?

At a minimum, the volume plan must feed through selling prices and standard costs into revenue and margin forecasts. Ideally, volume, revenue and margin forecasting will be handled in the same solution, removing latency and giving an instant financial forward view.

It probably won’t be relevant (or practical) to analyse revenues and margins at sku and customer level – way too much detail. So using a multi-level view of the financial measures through time is the way to go. Start, for example, at a product group and customer sector level. Then drill through the hierarchies to identify issues or opportunities.

In Prophecy, depending on the measures you configure in your database, you can view revenue, margin or any other measure at any combination of product and customer level through time. For every volume forecast there is a revenue and margin forecast. Prices can be imported or manually forecasted and standard costs are imported automatically. Prophecy handles the hierarchical solves, in real time, providing an always-on view of the financial data broken down over the full hierarchies.

5. Analysis and control

Sales forecasting generates A LOT of numbers! Especially when you’re extending to revenues and margins over large hierarchies of products and customers. You need a solution which lets you tame this data overload and helps you compare the evolving forecasts with previous years, Budgets and Plans.

Prophecy uses a multi-dimensional reporting and graphical environment which lets you flexibly create your own reports and forecast update screens. These screens support drilling though the hierarchies as well as ‘data-driven’ product and customer selections. (An example of a ‘data-driven’ selection is “retrieve the data for products which are currently forecasted at 10% or more below Budget”.)

Prophecy supports a ‘rolling’ forecast for the standard forecasting cycle – i.e. a new forecast version is published each month, with a backward view of (say) the previous 11 versions. In addition, any number of ‘ad-hoc’ versions can be created – for Budgets, Targets or What-If explorations.

Prophecy’s powerful analysis and reporting environment supports full comparatives between all these versions, such as current forecast version versus a Budget.

Prophecy also provides an optional forecaster / reviewer management work-flow cycle.

6. Plan Trade Promotions

Itemised Trade Promotions are very important to many businesses. For these businesses, it’s important to be able to handle the forecasting of these promotions from within their overall forecasting solution.

Prophecy has an optional Trade Promotions Planning module that lets your forecasters plan and manage trade promotions, and to analyse each promotion’s planned and actual incrementality within Prophecy. It aims to provide an appropriate level of business benefit from Trade Promotions Planning, without the very considerable implementation and maintenance complexities and expenses associated with the more full-blown Trade Promotions systems available.


This post has identified the 6 major elements of a viable sales forecasting software solution. One that services all the business processes and helps everyone plan and control the future from within a single, consistent, robust solution. If you’re looking to cover these bases in your new sales forecasting software solution, Prophecy is a great place to start your search!