Last week I got the opportunity to present to the Oracle Retail User Group on the topic of Managing by Exception in Oracle Retail Demand Forecasting (RDF). The one topic that seemed to resonate the most in this area was the concept of using what I referred to as a Master Alert.

The premise behind the Master Alert is based on these principles:

1. One alert/exception rule that combines all other key rules/exceptions
2. The ability for a user to easily turn on and off which rules/exceptions are used in this master alert
3. The ability to control the frequency of when the alert will next need to be checked
4. The ability to sort/rank the product/location level forecasted by a key magnitude metric

Before I dig in, it’s important to note that I use exception and alert interchangeably to mean some type of defined rule or check that then produces a flag or notification within the system to pinpoint the user to troubled and/or out-of-tolerance occurrences. So yes, let’s dig in.

Those in retail that forecast/plan their business down at the Item/Store or SKU/Store level know that there are by far too many combinations for even a well staffed forecasting organization to review each forecast on a regular basis. In the good old days, when Ms. Pac-man was considered to have amazing graphics and forecasting systems ran on less disk space than that found on today’s iPod, the decision was made easy for us; forecast and manage our products at rolled-up/aggregate levels along the product or location hierarchy. And even at these aggregate levels with checks and balances in place to review and manage the profiles and forecasts, it still could be an overwhelming task.

Fast forward to current day, and now that we can create forecasts for the SKU, at the Store at a week, day or even hourly interval the ability to ensure quality becomes even more daunting. And although the science is getting better and “the system” is getting smarter, there is still a need to have the human element to provide the “art” in the equation to best prevent bad forecasts from being released to the world. Hence how alerts and managing by exception came to be all the rage.

Creating exception rules or defining alerts helps focus the forecaster on true problem areas, but even this can result in too many SKU/Store occurrences to manage each week. Let’s say that the forecasting solution has alert rules that evaluate the forecast against:

• Exception 1: Forward Forecast vs. Last 4 weeks of Sales
• Exception 2: Forward Forecast vs. Last Year Sales
• Exception 3: Forecast vs. Sales (Accuracy Over Last 4 Weeks)
• Exception 4: Extremely High Forecast Deviations
• Exception 5: New Item/New Store/Insufficient History

If the forecaster is responsible for 500 items across 1000 stores, that’s half a million Item/Store combinations they have to manage. If each of the alerts produce an average of 1000 hits across the five alerts listed above, that’s approximately 5000 combinations the forecaster has to review. If the forecaster only allows 30 seconds a hit, it would still take them just over 40 hours in a week to review all their alerts, say good-bye to leaving early on Friday to play Ms. Pac-man…

In the example above, if all 5000 hits were unique Item/Store combinations, that would represent exactly 1% of the forecaster’s Item/Stores; however, chances are that an Item/Store that has gone out of tolerance may very well trigger in more than one of the five alert rules. And though it is important for the forecaster to understand why the forecast was bad, chances are they will have to evaluate the entire time series of the forecast, or look at other variables to determine the final remedy (if there even is one). This is the value behind producing a one stop shop for all the forecaster’s exception management needs, the Master Alert.

The Master Alert is nothing more than combining the five alerts together in a way that if ANY of the five alerts are out of tolerance then the Item/Store is flagged in the Master Alert. The forecaster no longer uses the other five alerts to drive workflow, instead they only use these as reference as to why their Master Alert flagged.

Along with having the flexibility to review the “input alerts” when using the Master Alert, it’s also key to provide a super user an easy way to turn on and off which input alerts are checked when the Master Alert does it calculations. This is as easy as giving the super user a series of check boxes in their front end screen to the effect of “Use Exception 1 in the Master Alert”. It’s even better if this setting can be controlled at a lower level on the hierarchy so users can set input alerts differently across businesses.

The last two concepts of using the Master Alert are more along the line of better managing the day to day workflow for the forecaster. The first of which is giving the forecaster the ability to not only resolve the alert by making forecast adjustments and/or checking the forecast was reviewed, but to take it a step further and allow the user to set a number of weeks or next date to alert again. This technically allows the user to have the Item/Store not be considered for alert for a certain period of time. And although this may seem like a dangerous option that will allow a forecaster to go from 5000 to 0 from one week to the next, if the process is managed properly it allows the forecaster to identify those cases in which they don’t need to check the forecast again for a few weeks. This may be because the approved forecast is acceptable for many weeks out or we might just not have to touch the Item/Store again. Either way, this keeps the alert count low and more representative of what was to be worked for the week.

Finally, when a solution has many different alert rules, there’s a tendency to prioritize workflow by the alert, i.e. work Exception 1: Forward Forecast vs. Last 4 weeks of Sales, then move to the next alert. By using the Master Alert, we now know that we have a bunch of Item/Store occurrences that are out of tolerance, so it would be better to work the Item/Stores by something like Average Monthly Sales, Product Cluster/Group, or any other business driver. If the system allows you to sort or rank your alerts, then including some type of magnitude metric can be invaluable. To take it one step further, if the forecasting solution lists your Item/Stores in a spreadsheet like grid mode and your sorting options are limited, setting up this magnitude metric to only populate when the Master Alert is triggered, will ensure that all acceptable Item/Stores (those that weren’t out of tolerance are left empty in the magnitude metric) will be sorted to the bottom of your alert/workflow list.

Well that’s the extent of what’s rolling around in my head on managing exceptions with the use of a Master Alert. If you have a variation or any other ideas on this topic I’d love to hear more! Thanks.