Automatic Forecast Adjustment Made Simple!

What is Automatic Forecast Adjustment, anyway?

In sales forecasting, unlike investments, the recent past is very often a good indicator of the near-term future.

An automatic forecast adjustment uses data from the immediate past to update the forecasts for the immediate future.  No more, no less.

You can improve the accuracy of your near-term forecasts quickly and efficiently by deploying automatic or semi-automatic adjustment algorithms.  Obviously, this is done through your sales forecasting software solution.

Automatic forecast adjustment

Understanding how the Recent Past Affects the Immediate Future

Arguably forecasting is easier for many products if you are selling direct to the end customer because last period’s sale more closely reflects real demand.  It’s much harder to draw inferences if you are selling to a retailer, who sells to the end customer.

Consider the effect of an under-sale last week.  It could mean your customer’s stock has reduced and therefore this week’s order will be correspondingly higher.  Alternatively, could it mean a decrease in demand that needs to be reflected in near-term forecasts?

The same dilemma applies to over-sales.  Do they reflect real increases in demand, or a bringing forward of future orders from your customer?

So, How Do We Auto-Adjust?

There is clearly no “one-size-fits-all” solution.  As with all things forecasting, it pays to know as much as possible about your market, your products and all the influences on sales.

You need to carefully select which Auto-Adjust rules to apply to different products and customers through careful, advanced categorisation.  Once you’ve done that – admittedly, a non-trivial exercise – you can start taking advantage of the Auto-Adjust methods your software provides.  (It does, doesn’t it?)

Automatic Forecast Adjustment Methods

Method 1: Assume the forecast last period was right

This method assumes recent variance in actual versus forecast is fully attributable to phasing issues.  Therefore:

    • An under-sale last period will be fully recovered this period or in the near-term.
    • An over-sale last period represents brought-forward orders, and therefore this period’s forecast must be correspondingly reduced.

These assumptions are particularly applicable to promotions, where, frequently, the total forecast for the promotion has been fixed and assumptions on the phasing of sales-in are proven incorrect.

The kind of automatic adjustment that can be used here is:

    • Subtract over-sales from the near-term forecasts until the whole over-sale has been exhausted.  (Possibly set a time limit for the periods affected, beyond which the over-sale actually becomes a [partial] oversale.
    • Add under-sales back to current period or a defined number of near-term periods.

Consider the following over-sale example:

Automatic forecast adjustment - oversales

Last period we over-sold by 435.  Therefore we reduce week 34’s forecast to zero, leaving 114 of the over-sale unaccounted, which is subtracted from the week 35 forecast.  Week 34’s forecast is therefore reduced to zero and week 35’s to 207.

Once the decision has been made to subtract over-sales from the near-term, the process can be automated by the software.  It’s just a mechanical forecasting chore that software can perform instantly.

And here’s how an under-sale would be adjusted:

Automatic forecast adjustment - undersale


Here, the under-sale of 50 is recovered in the next 3 periods, where half the under-sale is expected to be recovered in current period and the remainder spread across the following 2 periods.  Again, the rule details determine the spread – you may prefer the whole under-sale to be added back to current period on a rolling process.

Finally, we may need to constrain, or think again,  on a large over-sale:

automatic forecast adjustment - constrained horizon for adjustment

Here, we have a massive over-sale of 14,614.  The rule is set up to stop applying the rule after 4 weeks.  Therefore, the next 4 weeks’ forecasts are set to zero and the forecast thereafter is unchanged.  13,499 of the over-sale is left unaccounted for, as shown in the corresponding cell.  In this case, maybe some human intervention is required to explore the reason for the over-sale further and consider making manual adjustments to the mechanical execution of the rule.

Productivity, Productivity, Productivity!

Running the type of rule-set described above (and Prophecy has others) can become a regular part of a demand planner / forecaster’s workflow.  It allows large number of forecasts to be adjusted very quickly.

The software needs to clearly show the execution of the rules so that the planner can understand, sanity-check and, ultimately take control of and responsibility for the forecast changes made.

The way Prophecy implements these rule-sets does just that.  The planner chooses the products and customers applicable to each rule-set.  They can then preview and amend the forecast changes proposed by the rule-set.  When they are happy with the results, they can click the ‘Commit!’ button to apply the forecast changes to the plan.  Here is the Prophecy interface:

Automatic forecast adjustment - Prophecy Screenshot

Everything is colour-coded to aid readability, the grid is fully multi-dimensional (like an Excel pivot table), the ‘tree’ on the left lets you instantly find an item and the optional graph shows the forecast and changes for the item the cursor is on.

So, should you use Automatic Adjustment rules?

It’s true that mechanistic forecasting techniques, applied blindly, are potentially dangerous.  However, there is definitely a role for auto-adjustment in the planning process, providing it is applied appropriately,  That means, identifying which products, customers and market situations it will work in and which it won’t.  It’s important to have this categorisation nailed before blindly running automated forecasting rules.

Then, be sure to monitor whether deployment of the rules is getting you better sales forecasting accuracy.  If it is, they free up the planner’s time to add value to forecasts which cannot be managed in this way.

Data Perceptions recommends using Automatic Adjustment on fixed quantity promotions, to re-phase over periods where the initial phasing as proven incorrect.  There are surely other situations and other rules where Automatic Adjustment has a role but this would be a great starting point.

The Marketing Bit

OK, it had to come!  Just to say that Data Perceptions forecasting solution, Prophecy for Windows, has just been updated to feature a full implementation of automatic adjustment rules.

It fully implements the kind of rule-set described in this post.

In addition, it provides a ‘naive’ weighted average rule – i.e. make a weighted average of the last [n] periods and apply it to the next [n] periods.

Let us know in the comments if there are additional rule-sets we could add which would help you react to short term changes faster, better or more accurately.  We’d love to hear!

Covid-19 – 3 Ways Demand Forecasting Software Should Help

Demand Forecasting through a Pandemic

The coronavirus pandemic poses the biggest challenge to the world within most of our lifetimes.  Focussing on demand forecasting at a time like this seems unimportant.  BUT we need to get it as right as possible to avoid shortages which are damaging for our businesses and, more importantly, to help our customers stay safe and comfortable.

Covid-19 - 3 Ways Demand Forecasting Software Should Help 1


No one knows, at this point, whether the forecast economic downturn will be U-shaped, V-shaped or any-shaped.  Nor its duration.  Indeed, some industries may benefit, at least in the short term, due to panic-buying, more time for in-home hobbies and pastimes or medical necessity.

Conventionally, demand variation ripples backwards from the end customer:

Supply chain profile
Increasing variation backwards through supply chain

In other words, customer consumption tends to be relatively smooth, but the logistics of the supply chain mean the ripples get bigger going backwards through the supply chain, due to promotions, minimum order quantities, packaging volume discounts, inventory fill etc..  Panic-buying has distorted this pattern due to the increased amplitude of end-user demand.  Forecasting uncertainty has increased as a result.

Suply chain ripple during covid-19
Covid-19 Supply Chain Profile

It is likely there will be no single observed demand pattern during the pandemic.  Therefore there will be a variety of different demand patterns as we come out.  It is unlikely that the pandemic will not simply disappear and neither will its effect on demand.  Lockdowns will be lifted and re-started in phases in order to manage the allocation of scarce healthcare resources, but over as yet unknown timescales.  So we currently have limited information on both the duration and amplitude of Covid-19’s run-out.  As demand forecasters, we just have to work with that and adjust the forecasts as new information manifests itself.

Forecasting during the pandemic

The key to demand forecasting during this time has to be the ability to quickly detect and respond to new data and new information.  No one can forecast accurately when so much is unknown about the future pattern of the virus.  But we can all respond to information as it becomes available – it’s optimizing how we respond, and how quickly we respond, that matters.

Here are three key elements we think your demand forecasting software should offer, in order to help you forecast through the crisis more effectively.

1. Top-down forecasting

The 2020 forecast we started the year with is almost certainly wrong by now.  However, with some adjustments for cancelled promotions and other activity, the mathematical relation between skus, or skus within customers, is still likely to be right, at least at some aggregate level.  We don’t want to lose all the forecasting effort we invested in getting those relations right.

Therefore, the application of top-down or multi-level forecast adjustments (i.e. from the middle to both up and down) will allow us to retain the relationships at the detail levels, by dialling down or up the forecasts at the more aggregated levels by a judgemental percentage or amount.

Multi-level forecasting
Flexing the top of the hierarchies

Unfortunately, no forecasting system can tell you the extent of the adjustment required, because there has never been a pandemic like this.  But once you have taken a view of the likely impact, your forecasting solution should allow you to apply it from the top-down.  In the Prophecy screenshot above, April and May are in the process of being dialled down by 10 percent.  When this forecast change is made, the software will roll it down over the product and customer hierarchies shown, reducing each dependent forecast by 10%.

This is very powerful – you can re-forecast for the pandemic in seconds.

2.  Detect exceptions

Many items are likely to suffer from “the toilet roll effect”.  In Britain, people panic-purchased toilet rolls to ensure that they had an adequate supply in the event of forced self-isolation.  Once it became known that panic-buying was occurring it just caused further panic-buying.  At some point, this stock will be used, and sales will dip below the normal rate.

In situations like these, the earlier a change in trend can be detected, the quicker it can be reflected in the demand forecast.

Spotting exceptions in Prophecy
Data driven selections to spot exceptions

One way your forecasting solution can help you detect exceptions earlier is via data-driven selections.  In other words, “show me the items where the latest data is different from expectation“.

In the Prophecy example above, we can instantly pull in all the items which over or undersold by 15% or more last week.  If we have point of sale data, we can devise alternative selection criteria.

The point is, statistical forecasting algorithms are of limited use when things are changing so quickly and unpredictably.  We have to identify the exceptions quickly, and try to work out the implications on the forecast.  Anything your forecasting software can do to help you in this will help you focus forecasting time on actually re-forecasting, rather than the unproductive activity of trying to find the exceptions.

3.  Adjusting History

Whilst there is a case for arguing that things may never go completely back to normal as we’ve all learned different behaviours during the crisis, the virus will eventually go, and we will need to create forecasts for new forecasting years.

If we use statistical forecasting, how will it cope with or understand what has been happening with covid-19?  It is likely that the disrupted sales history we acquire during the pandemic may span 12 to 24 months.  How can we use it to forecast beyond the pandemic?

We definitely cannot just use the pandemic sales data ‘as-is’.  Therefore, our forecasting solution needs to be able to ‘clean-up’ history by reversing out the effect of the pandemic disruption from the sales history used for generating future years’ forecasts.  Either that, or we may have to consider abandoning statistical forecasting altogether.

Here we can see how Prophecy lets us devise persistent history overrides to take out the coronavirus effects:

History Overrides Management
Manage history overrides

The orange line shows the cleaned up history.  Using this data, rather than the raw history shown in the blue line, is likely to generate a much more reliable statistical forecast, for as long as the covid-19 period stays in the sales history.

And because statistical forecasting ideally requires two to three years of data, the adjustments we make now will affect the statistical forecasts we generate for the next two to three years.


Forecasting demand and sales is challenging even in normal times.  We live in far from normal times and the effect of the coronavirus will be with us at least into the medium term.  No one really knows, at this stage, the duration or overall effect of the distortion.  As forecasters, we need to use the best software tools available to help us understand and manage the uncertainty in the best way possible.

Any additional ideas for helping forecasters address the issues caused by coronavirus?  Please put them in the comments below!

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™!

How ‘Forecastability’ Analysis Improves Forecast Accuracy

What is ‘Forecastability’?

How 'Forecastability' Analysis Improves Forecast Accuracy 2

Every forecaster knows that there is no single, ‘best practice’ way to forecast sales of an item.  But if different items possess different ‘forecastability’ characteristics, surely one can infer different best methods for forecasting them, and hence improve productivity and forecast accuracy.

By ‘forecastability’ (a word that does not actually exist in the dictionary!) we mean identifying how forecastable an item actually is.  Are some items totally unforecastable, for example?

How ‘Forecastability’ Helps Forecasters

Consider an item that is ordered very intermittently and irregularly through time.  Assume also that each order is totally different in quantity to the preceding and following order.  Demand like this is called ‘lumpy’ in the forecasting literature.  Arguably, items like this are actually impossible to forecast accurately:

Lumpy forecast demand
Lumpy demand pattern

Options for addressing ‘Lumpy’ demand are:

    1. Will the customer engage in discussion over making the demand pattern more predictable?
    2. If not, consider forecasting the item at a higher level of customer hierarchy (aggregation).  This may smooth out the irregularity of level and interval and make the item more forecastable.
    3. If this is not possible, consider a safety stock strategy (especially for low volume / value items) because the item is simply not forecastable.  (Holding stock is clearly a difficult ‘sell’ for the forecaster because no one wants to tie up money in inventory.  However, ‘Forecastability’ measurement will help the ‘sell’ by providing an objective and irrefutable measurement of the issue to the business.)

If none of these options are possible then a question has to be answered over whether it is economic to sell this item due to the high cost of meeting demand.

The other end of the ‘forecastability’ spectrum is items which are ordered regularly and where the demand level is similar each period.  These items have ‘Smooth’ demand:

Smooth demand characteristic
Smooth demand characteristic

These items are relatively easy to forecast.  They are very suited to automated statistical forecasting (e.g. via Prophecy’s R engine).  Software, such as Prophecy, should let you quickly identify these classic statistical forecasting candidates and push them into the statistical engine.

Between the ‘Lumpy’ and ‘Smooth’ group of items are the ‘in-between’ items.

An item with ‘Intermittent’ demand has irregular ordering time periods, but a similar quantity in each order:

Intermittent demand pattern
Intermittent demand pattern

These items require a similar approach to the ‘Lumpy’ category.  Forecasting at a higher level of product aggregation can even out the demand interval and make the item easier to forecast, if it’s not possible to work with the customer.  For low volume items, the cost of safety stock may be a fallback where neither of these options works.

Finally, ‘Erratic’ items.  These have orders every forecast period but at vastly different levels.  They are likely to be highly seasonal or promotional.

Erratic demand pattern
Erratic demand pattern

Seasonal items can still be forecasted using statistical methods.  Many statistical forecasting models, including those in Prophecy™, employ ‘outlier detection’ to remove the peaks and troughs of promotions, allowing you to forecast the ‘standard sales’ statistically and overlay promotions afterwards (e.g. using Prophecy™’s Trade Promotions Planning module).

These items require the highest ‘judgemental’ element to the forecasting process.  Particularly for items where the statistical forecasting MAPE (mean average percent deviation) proves high – i.e. erratic or no seasonality.

A forecaster can only “play the hand they’ve been dealt” and one has to be realistic about how easy or difficult the different items can be to forecast.

However, the strategies described here at least provide a starting point to making the best of the hand.

Pulling the four demand categories together into a single view we get:

Forecastability Quadrants
Forecastability Quadrants

The Mechanics of Forecastability

As illustrated above, forecastability is a function of demand level variability (the x-axis on the quadrant graphic) and demand interval variability (the y-axis on the quadrant graphic).

Demand level variability is measured by the Coefficient of Variation (CV) : the ratio of the standard deviation to the mean.  A high variation from the mean indicates a high variability of demand.

Demand interval variability is measured using the Average Demand Interval (ADI).  For example, say there are 36 time periods in the history but only 12 have a sale.  The ADI would then be 36 / 12 = 3.  In other words, on average, a sale occurs every three periods.

Plotting the CV and the ADI provides the quadrant analysis shown in the preceding graphic.  In Prophecy™ you’ll see it rendered like this:

How 'Forecastability' Analysis Improves Forecast Accuracy 3

The red lines delineate the quadrants.  Hover tooltips help identify the quadrants or the item at each plot point.  The items from any quadrant can be instantly pushed into a Prophecy report and statistically / judgementally forecasted with a single click of one of the buttons in the panel labelled ‘Push into Prophecy Report..’.  You can filter out low volume items, which can make it harder to see spot the key forecastability opportunities.  Finally, the graph can also be rendered as a ‘bubble’ plot, where the size of each bubble corresponds to the average sale per period.

The ‘pay-off’

Being able to categorise items for their ‘forecastability’ characteristics has the potential to make forecasters more productive, by helping them to:

  • Choose the best forecasting strategy for each item.
  • Instantly identify the items that lend themselves to automatic forecasting.
  • Objectively measure of the difficulty of forecasting.

Some people, particularly those who are not directly responsible for forecasting, have unrealistic expectations about forecast accuracy.  ‘Forecastability’ measurement can help them understand the issues in a more data-driven and objective way, particularly relating to the ‘lumpy’, or unforecastable items.

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!

5 methods for measuring sales forecast accuracy

Why Measure?

Failing to learn from your forecasting mistakes makes you a lot less likely to become a better sales forecaster.  Measuring accuracy should be a positive thing (and definitely not a stick for beating sales forecasters with).

However, calculating accuracy data over hundreds, possibly thousands of items can be a real challenge – especially finding a balanced ‘single number’ measure for accuracy.  Here, we explain the different alternatives of calculating accuracy available in Prophecy, Data Perceptions sales forecasting software solution.  They are mostly well-known methods for getting to a single, summary number that describes the overall accuracy of a group of many separate forecasts.

Exceptions Analysis

Before we get to exceptions analysis, let’s remember that summary measurement is useful for tracking accuracy over time.  Exceptions analysis – identifying and explaining the reasons for the biggest / most expensive forecast errors – arguably has a bigger payoff.  It provides a valuable opportunity to learn from mistakes and apply the lessons of experience to current and future forecasts.

Therefore, step 1 in accuracy analysis should be a process for rapidly identifying the exceptions – the big deviations that caused the most problems.  Prophecy’s Accuracy Analysis control dialog lets you specify an exception percent and only items exceeding this limit are listed in the resulting detail report:

5 methods for measuring sales forecast accuracy 4

Step 1 is to review these exceptions and try to explain them.  Could the reasons have been anticipated?  Forecasting is constrained to skilfully playing the hand the forecaster was dealt at the time the forecast was made.  What’s to learn from reviewing the exceptions?  Clearly, if better information or interpretation was available at the time the forecast was made there is a lesson to apply to future forecasts.

Moving on to obtaining a single number to describe forecast accuracy, there are several common measures and a couple of deviated ones.  Prophecy’s Accuracy module calculates them ‘for free’ – Excel forecasters will need a little more work but they are mathematically simple.

The important thing to bear in mind when looking at all these measures is that they measure what they measure – i.e. simple maths.  They DO NOT measure the real impact on your company’s supply chain or sales, revenue and profit.

Weighted Average % Error

This statistic calculates a percentage deviation by dividing the sum of the absolute, unit deviations by the sum of the actuals, as illustrated in the following example:

5 methods for measuring sales forecast accuracy 5

The ‘simple’ % error, 15%, shown in the ‘TOTAL’ line does not allow for a mix of over and under-forecasts.  They cancel each other out and are therefore misleading.

The weighted average method effectively weights the absolute variance by the size of the actual.  This gives a more appropriate indication of overall forecast accuracy relative to volume, but assumes a product selling twice as much has twice as much impact.  This is not necessarily the case.  For example, where one item has a very high price and the other a very low price.  In that case, consider measuring accuracy using revenue instead of quantity.

However, if you’re trying to measure supply chain impact, both quantity and revenue can be misleading.  Sku-1 could have a long lead time, Sku-2 a short lead time.  If they sell the same and are the same price, how can accuracy measures measure supply chain impact?

Alternate Weighted Average % Error

This calculation is similar to the Weighted Average % Error.  However, instead of dividing by the sum of the actuals as shown in the example above, it divides by the sum of the forecast or actual for each line, whichever is greater.

5 methods for measuring sales forecast accuracy 6

This means that actual=0/forecast=100 has the same weight as actual=100/forecast=0.  For two items with these forecasts and actuals, the weighted percent is 200% (misleadingly high?) whereas the Alternate Weighted percent is 100% (perhaps closer to ‘correct’ because both items were effectively 100% out?).

The Alternate Weighted Average % approach was suggested by a Prophecy customer and is thought to be non-standard.  Nevertheless, it has the stated advantages over the more usual calculation.

Mean Absolute Percent Error (MAPE)

This is a commonly used measure in sales forecasting.  It is the mathematical average of the individual percent errors, ignoring their signs, as shown in the cell outlined in red here:

5 methods for measuring sales forecast accuracy 7

It is a moot point whether to divide by 3 or 4 in the example shown, where Sku-4 has no actual.  If you divide by 4 you are rewarded with a lower MAPE than dividing by 3, but if you divide by 3 you are effectively ignoring Sku-4!

In addition, this method gives equal weight to a percent deviation, irrespective of the relative size of the forecast or actual.  An item that sells a million has the same weight as one that sells 10.

Mean Average Deviation (MAD)

This measure definitely has the most appropriate acronym!  It is calculated from the mathematical average of the individual unit deviations, regardless of their sign:

5 methods for measuring sales forecast accuracy 8

The advantage of MAD over MAPE is that instances like Sku-4, where there is no actual, are included in the measure.  The same percentage error on a high volume item has a much bigger effect on the result.  This may or may not be desirable where, for example, the high seller is low value and the low seller is high value.

In addition, unlike a percent measure, it can be hard to know if the MAD is good or bad – it is just a number.


As with so many areas of sales forecasting, there is no right answer or single ‘best’ measure that can be used to describe sales forecasting accuracy.

The first and most beneficial purpose of accuracy analysis is to learn from your mistakes.  In that sense, exceptions analysis has the highest return.  The summary measures are helpful for tracking cumulative improvement over time and are control barometers.

Author: Peter Boulton, Data Perceptions.

Sales Forecasting with Excel Spreadsheets

This won’t be the first or last blog post on the internet about the dangers of large spreadsheets!

It is inspired by a recent Prophecy implementation where the customer, like many Prophecy customers, is migrating from a spreadsheet-based system that had grown over the years and now suffered many of the (not unusual) issues of complex spreadsheets:

  • The original developer was long gone.
  • It did not support multiple concurrent forecasters.  (All customers were forecasted in a single spreadsheet, avoiding the need for multiple linked spreadsheets but preventing multi-user access.)
  • There appeared to be design errors which lead to incorrect financial forecasts
  • It contained no sales history, other than year to date.
  • It was cumbersome to navigate and locate items quickly.

There are, of course, many additional shortcomings associated with using Excel spreadsheets for sales forecasting and they are documented on our main site at

A recent discovery, for me at least, is research and data on the incidence of serious errors (i.e. errors that affect key numbers) in corporate spreadsheets.

The bottom line from this research is that more than 90% of corporate spreadsheets contain serious errors.  Yes, 90%.  Ray Panko is a Professor of IT Management and has curated the results of numerous studies on corporate spreadsheet errors on his website at  If you doubt the claim about 90% errors, please do hop over to his site and see for yourself!

So, the point of this post is really to say that there is a high probability (90%!) that relying on spreadsheets for sales forecasting imposes hidden costs on your organisation through formula and other errors.  As well as the limitations of spreadsheets in the 10% or so that are actually error free.

That’s why a purpose-built, proven solution like Prophecy™ has to be the right way to go.  If it does nothing else, it avoids the high probability of spreadsheet errors, as well as opening up a raft of serious, forecaster-specific features that will help your forecasters develop better, more defensible sales forecasts, in less time.


The effect of Spectre and Meltdown on Prophecy performance

The Spectre and Meldown ‘bugs’ have, rightfully, been hitting the headlines these last two to three weeks.  There is still a lot of ‘noise’ and uncertainty, as well as a degree of hysteria about these bugs.   And rightfully so – Intel (for example) have been inconsistent about the impact of the microcode ‘fixes’ they have released in response.  Initially, they recommended everyone should apply them but when the number of spontaneous reboots increased ‘out there’ they reined back on that recommendation.  PC vendors were initially encouraged to push the fixes, until some of the deployment issues emerged.  Principally these were increased reboots and performance ‘hits’.

What should we do about Spectre and Meltdown?  Definitely apply the fixes, wait till first adopters work out the bugs?  It is surprising that there has been hardly any questioning over how dangerous these issues actually are in the real world.  The enormity if Spectre/Meltdown relates to the scope of cpus affected – i.e. all from about 1995 onwards rather than the size of the risk.  The following mitigations appear to be correct:

  1. Exploiting these vulnerabilities requires a significantly greater level of technical ability than more traditional malware development.
  2. The data that can theoretically be obtained is much more limited – i.e. no hard disc data, no keystrokes etc..
  3. Like traditional malware, something has to run in order to exploit the vulnerability – ‘drive-by’ is not possible.
  4. OS and browser vendors have already moved to reduce the likelihood of these vulnerabilities having real world impacts.

So overall, there are so many cherries lower down the tree waiting to be picked.  Why would common-garden malware use these exploits when the degree of technical difficulty is so high and the data you can get from them is so limited?

The lowest risk is of course to just patch.

To test the performance impact of the current Windows 10 Spectre/Meltdown mitigations we ran some Prophecy benchmarks, with and without the patched Windows, on the same machine – a gen 3 i5 with 8gb of memory.

Bottom line – the patched Windows took 13% more time in our tests.

So, everyone has to make their own decision and what we see on Prophecy, is sure to be different to other applications.  But a 13% speedup is non-trivial.

Incidentally, you can test your software in the same way.  Steve Gibson / GRC have a small utility which evaluates your machine for the vulnerability and allows you to toggle the protections on and off.  It’s here: