Dec 01, 2016
My forecasting formula keeps getting it wrong [Updated]
What's in this article:
Despite bold claims being made by many forecast applications in the market today, inventory planners have found a lot of these to be unhelpful and, at times, even downright incorrect in their forecast recommendations. Let’s take an in-depth look and see what the problems are.
Is there a perfect forecasting engine?
No, unfortunately, no forecast engine will deliver perfect forecasts for all your products all of the time. Firstly, so that we are all on the same page, dumping data from your ERP into a spreadsheet isn’t going to give you a forecast. Even though there are varying forecasting formulas in Excel, these are designed for single data sets and are not optimal for hundreds or thousands of items. You’ll quickly hit the limit of Excel if you try to automate this. By the time you have checked every single record and updated one by one in your spreadsheet, the data is most likely outdated. The ultimate job of a proper forecast solution is to guide you with recommendations so that you have the right amount of stock, in the right locations, and at the right time.
So how do you get the most accurate demand forecast?
A robust and structured process is critical to achieving accurate forecasting. Improved forecast results are seen when there is a reduction of slow-moving and obsolete stock, as well as a reduction in stock-outs. Improved structured processes are evident when your team members, across departments, are talking to each other regularly to help improve these forecasts and processes.
What does a structured forecast process look like?
Usually, there are monthly and weekly activities, let’s start with what a monthly process should look like.
At the start of each month, the system forecast should be reviewed to make sure that it is reasonable and that all known information is included. The steps to achieving this include:
- Create computer-generated forecasts for all of your items by allowing a forecast engine to do all the grunt work:
- Use sales or demand history to generate a forecast.
- Pick up on trends, seasonality, intermittent demand, one-off sales spikes, and factor in data such as lost sales.
- Any worthy forecast engine will generate forecasts by using several different algorithms. Once done, it should then compare all of the generated forecasts with the sales/demand history to determine the “best fit” forecast. This results in accurate forecasts for the bulk of your items.
- There will be a small percentage of your items that will need manual intervention, as no forecast engine will get every forecast right. Monitor those items with consistent variances between sales and forecast:
- Adjust the forecast up where sales have consistently exceeded the forecast.
- Adjust the forecast down where sales have consistently been lower than the forecast.
Aligning sales and forecasts means there is less risk of generating excess inventory or experiencing costly stock-outs.
- Adjust your forecast for new or lost customers, as soon as you are aware of the change. Use the computer forecast but:
- Subtract a lost customer’s monthly demand.
- Add in new customer’s expected monthly demand.
- Since new items will have no sales history, these need to be manually forecasted for the first few months. Check to make sure that the “new” item is not a replacement for a product, where a cheaper or better quality product has been sourced and will now be sold instead of the old product. In this instance, you would link the “new” item to the “old” item, which results in the sales history of the old item being used to generate the forecast for the new item. This has the added benefit of getting an earlier indication of forecast accuracy, which is vital to get the right safety stock in place sooner.
- Adjust forecasts to include additional promotional demand on top of the expected regular sales. Use team discussions and inputs to help guide these adjustments. The better the input, the better the forecast result, the better the stock position and success of the promotion!
- Report on forecasting performance and make sure your measure also distils the bias between over and under forecasting. Measuring the difference between your system generated forecast and the manually adjusted forecast is useful to do to establish whether the manual intervention improved the result.
- Conduct a sanity check at a macro level. After making changes to individual item forecasts on an exception basis, review overall sales to forecast to ensure that the overall growth is not too extreme or too conservative. Most forecasting applications will enable macro forecast adjustments to be made, should they be required.
Once the monthly forecast review and endorsement has been completed, the focus switches to a weekly review aimed at highlighting exceptions between the pro-rated forecast and the actual sales. Here any severe deviations between sales and pro-rated forecasts highlight potential issues with the forecast on individual items. Reviewing these alerts enables prompt response to possible changes in demand.Review forecasts for the top 5-10 sales versus forecast exceptions:
- If you are selling more than the forecast, consider increasing the forecast.
- If you are selling less than the forecast, consider reducing the forecast.
- Consider that you may be selling less due to stock-outs.
Adjust the forecast as soon as you see a trend emerging before it results in stock-outs or excess inventory. Remember that these adjusted forecasts will help trigger further actions that may be needed to help manage the impact on the stock. There is nothing worse than running out of stock because your promotion was more successful than anticipated, and you could have addressed that by acting earlier!
Be patient and reap the rewards.
This process takes time and focus, which is why it’s important to remember how relevant it is to your company’s bottom line. Investing in the right tools to help you with your inventory management will enable you to reduce your investment in inventory while improving your customer fill rates.
Written by Barry Kukkuk
Barry comes from a systems architect and application development background. He started his career as the co-founder and chief developer for Icon Retail Management, a full-fledged retail management system that integrated with mainstream ERP. Barry later conceptualized and developed Inventory Optimiza for Barloworld Logistics and provided technical support for the application. It was here where Barry’s passion for Inventory Management solutions began and the industry where he would later return. Barry went on to start his own business in 2008, where he was an avid user of cloud-based apps and would only use online solutions for his business. In 2010 Barry began his journey with NETSTOCK. His enthusiasm for Inventory Management and his strong belief in “all things Cloud” collided, and we saw the release of the Inventory Management solution - NETSTOCK. Barry is the CTO at NETSTOCK, where he is responsible for all customer-facing technologies and systems that keep thousands of NETSTOCK customer instances working correctly.