There’s one thing I’m reminded of almost every day at Smart Software that puzzle me: most companies do not understand how forecasts are created, and stocking policies are determined.  It’s an organizational black box. Here is an example from a recent sales call:

How do you forecast?
We use history.

How do you use history?
What do you mean?

Well, you can take an average of the last year, last two years, average the most recent periods, or use some other type of formula to generate the forecast.
I’m pretty sure we use an average of the last 12 months.

Why 12 months instead of a different amount of history?
12 months is a good amount of time to use because it doesn’t get skewed by older data but it’s recent enough

How do you know it’s more accurate than using 18 months or some other length of history?
We don’t know. We do adjust the forecasts based on feedback from sales.  

Do you know if the adjustments make things more accurate or less than if you just used the average?
We don’t know but are confident that forecasts are inflated

What do the inventory buyers do then if they think the numbers are inflated?
They have lots of business knowledge and adjust their buys accordingly

So, is it fair to say they would ignore the forecasts at least some of the time?
Yes, some of the time.

How do the buyers decide when to order more? Do you have a reorder point or safety stock specified in your ERP system that helps guide these decisions?
Yes, we use a safety stock field.

How is safety stock calculated?
Buyers determine this based on the importance of the item, lead times, and other considerations such as how many customers purchase the item, the velocity of the item, it’s cost.  They’ll carry different amounts of safety stock depending on this.

The discussion continued. The main takeaway here is that when you scratch just below the surface, far more questions are revealed than answers.  This often means that the inventory planning and demand forecast process is highly subjective, varies from planner to planner, is not well understood by the rest of the organization, and likely to be reactive.  As Tom Willemain has described it’s “chaos masked by improvisation.”   The “as-is” process needs to be fully identified and documented.  Only then can gaps be exposed, and improvements can be made.   Here is a list of 10 questions  you can ask that will reveal your organization’s true forecasting, demand planning, and inventory planning process.

In a recent LinkedIn post, I detailed four questions that, when answered, will reveal how forecasts are being used in your business.  In this article, we’ve listed questions you can ask that will reveal how forecasts are created.

1. When we ask users how they create forecasts, their answer will often be “we use history.” This obviously isn’t enough information, as there are different types of demand history that require different forecasting methods. If you are using historical data, then make sure to find out if you are using an averaging model, a trending model, a seasonal model, or something else to forecast.

2. Once you know the model used, ask about the parameter values of those models. The forecast output of an “average” will differ, sometimes significantly, depending on the number of periods you are averaging.  So, find out whether you are using an average of the last 3 months, 6 months, 12 months, etc.

3. If you are using trending models, ask how the model weights are set. For example, in a trending model, such as double exponential smoothing, the forecasts will differ significantly depending on how the calculations weight recent data compared to older data (higher weights put more emphasis on the recent data).

4. If you are using seasonal models, the forecast results are going to be impacted by the “level” and “trending weights” used. You should also determine whether seasonal periods are forecasted with multiplicative or additive seasonality.  (Additive seasonality says, e.g., “Add 100 units for July”, whereas multiplicative seasonality says “Multiply by 1.25 for July.”) Finally, you may not be using these types of methods at all.  Some practitioners will use a forecast method that simply averages prior periods (i.e., next June will be forecasted based on the average of the prior three Junes).

5. How do you go about choosing one model over another? Does the choice of technique depend on the type of demand data or when new demand data are available? Is this process automated? Or if a planner chooses a trend model subjectively, will that item continue to be forecasted with that model until the planner changes it again?

6. Are your forecasts “fully automatic,” so that trend and/or seasonality are detected automatically? Or are your forecasts dependent on item classifications that must be maintained by users? The latter requires more time and attention from planners to define what behavior constitutes trend, seasonality, etc.

7. What are the item classification rules used? For example, an item may be considered a trending item if demand increases by more than 5% period-over-period. An item may be considered seasonal if 70% or more of the annual demand occurs in four or fewer periods. Such rules are user-defined and often require overly broad assumptions. Sometimes they are configured when a system was originally implemented but never revised even as conditions change. It’s important to make sure any classification rules are understood and, if necessary, updated.

8. Does the forecast regenerate automatically when new data are available, or do you have to manually regenerate the forecasts?

9. Do you check for any change in forecast from one period to the next before deciding whether to use the new forecast? Or do you default to the new forecast?

10. How are forecast overrides that were made in prior planning cycles treated when a new forecast is created? Are they reused or replaced?

11. How do you incorporate forecasts made by your sales team or by your customers? Do these forecasts replace the baseline forecast, or do you use these inputs to make planner overrides to the baseline forecast?

12. Under what circumstances would you ignore the baseline forecast and use exactly what sales or customers are telling you?

13. If you rely on customer forecasts, what do you do about customers who don’t provide forecasts?

14. How do you document the effectiveness of your forecasting approach?  Most companies only measure the accuracy of the final forecast that is submitted to the ERP system, if they measure anything. But they don’t assess alternative predictions that might have been used. It is important to compare what you are doing to benchmarks. For example, do the methods you are using outperform a naïve forecast (i.e., “tomorrow equals today,” which requires no thought), or what you saw last year, or the average of the last 12 months.  Benchmarking your baseline forecast insures you are squeezing as much accuracy as possible out of the data.

15. Do you measure whether overrides from sales, customers, and planners are making the forecast better or worse? This is just as important as measuring whether your statistical approaches are outperforming the naïve method.  If you don’t know whether overrides are helping or hurting, the business can’t get better at forecasting – you need to know which steps are adding value so that you can do more of those and get even better. If you aren’t documenting forecast accuracy and conducting “forecast value add” analysis, then you aren’t able to properly assess whether the forecasts being produced are the best you could make.  You’ll miss opportunities to improve the process, increase accuracy, and educate the business on what type of forecast error is to be expected.

We often encounter Excel-based reorder point planning methods.  In this post, we’ve detailed an approach that a customer used prior to proceeding with Smart.  We describe how their spreadsheet worked, the statistical approaches it relied on, the steps planners went through each planning cycle, and their stated motivations for using (and really liking) this internally developed spreadsheet.

Their monthly process consisted of updating a new month of actuals into the “reorder point sheet.”  An embedded formula recomputed the Reorder Point (ROP) and order-up-to (Max) level.  It worked like this:

• ROP = LT Demand + Safety Stock
• LT Demand = average daily demand x lead time days (assumed constant to keep things simple)
• Safety Stock for long lead time parts = Standard deviation x 2.0
• Safety Stock for short lead time parts = Standard deviation x 1.2
• Max = ROP + supplier-dictated Minimum Order Quantity

Historical averages and standard deviations used 52-weeks of rolling history (i.e., the newest week replaced the oldest week each period).  The standard deviation of demand was computed using the “stdevp” function in Excel.

Every month, a new ROP was recomputed. Both the average demand and standard deviation were modified by the new week’s demand, which in turn updated the ROP.

The default ROP is always based on the above logic. However, planners would make changes under certain conditions:

1. Planners would increase the Min for inexpensive parts to reduce risk of taking an on-time delivery hit (OTD) on an inexpensive part.

2. The Excel sheet identified any part with a newly calculated ROP that was ± 20% different from the current ROP.

3. Planners reviewed parts that exceed the exception threshold, proposed changes, and got a manager to approve.

4. Planners reviewed items with OTD hits and increased the ROP based on their intuition. Planners continued to monitor those parts for several periods and lowered the ROP when they felt it is safe.

5. Once the ROP and Max quantity were determined, the file of revised results was sent to IT, who uploaded into their ERP.

6. The ERP system then managed daily replenishment and order management.

Objectively, this was perhaps an above-average approach to inventory management. For instance, some companies are unaware of the link between demand variability and safety stock requirements and rely on rule of methods or intuition exclusively.  However,  there are problems with their approach:

1. Manual data updates
The spreadsheets required manual updating. To recompute, multiple steps were required, each with their own dependency. First, a data dump needed to be run from the ERP system.  Second, a planner would need to open the spreadsheet and review it to make sure the data imported properly.  Third, they needed to review output to make sure it calculated as expected.  Fourth, manual steps were required to push the results back to the ERP system.

2. One Size Fits All Safety Stock
Or in this case, “one of two sizes fit all”. The choice of using 2x and 1.2x standard deviation for long and short lead time items respectively equates to service levels of 97.7% and 88.4%.    This is a big problem since it stands to reason that not every part in each group requires the same service level.  Some parts will have higher stock out pain than others and vice versa. Service levels should therefore be specified accordingly and be commensurate with the importance of the item.  We discovered that they were experiencing OTD hits on roughly 20% of their critical spare parts which necessitated manual overrides of the ROP.  The root cause was that on all short lead time items they they were planning for an 88.4% service level target. So, the best they could have gotten was to stock out 12% of the time even if “on plan.”   It would have been better to plan service level targets according to the importance of the part.

3. Safety stock is inaccurate.  The items being planned for this company are spare parts to support diagnostic equipment.  The demand on most of these parts is very intermittent and sporadic.  So, the choice of using an average to compute lead time demand wasn’t unreasonable if you accept the need for ignoring variability in lead times.  However, the reliance on a Normal distribution to determine the safety stock was a big mistake that resulted in inaccurate safety stocks.  The company stated that its service levels for long lead time items ran in the 90% range compared to their target of 97.7%, and that they made up the difference with expedites.  Achieved service levels for shorter lead time items were about 80%, despite being targeted for 88.4%.    They computed safety stock incorrectly because their demand isn’t “bell shaped” yet they picked safety stocks assuming they were.  This simplification results in missing service level targets, forcing the manual review of many items that then need to be manually “monitored for several periods” by a planner.  Wouldn’t it be better to make sure the reorder point met the exact service level you wanted from the start?  This would ensure you hit your service levels while minimizing unneeded manual intervention.

There is a fourth issue that didn’t make the list but is worth mentioning.  The spreadsheet was unable to track trend or seasonal patterns.  Historical averages ignore trend and seasonality, so the cumulative demand over lead time used in the ROP will be substantially less accurate for trending or seasonal parts. The planning team acknowledged this but didn’t feel it was a legitimate issue, reasoning that most of the demand was intermittent and didn’t have seasonality.  It is important for the model to pick up on trend and seasonality on intermittent data if it exists, but we didn’t find their data exhibited these patterns.  So, we agreed that this wasn’t an issue for them.  But as planning tempo increases to the point that demand is bucketed daily, even intermittent demand very often turns out to have day-of-week and sometimes week-of-month seasonality. If you don’t run at a higher frequency now, be aware that you may be forced to do so soon to keep up with more agile competition. At that point, spreadsheet-based processing will just not be able to keep up.

In conclusion, don’t use spreadsheets. They are not conducive to meaningful what-if analyses, they are too labor-intensive, and the underlying logic must be dumbed down to process quickly enough to be useful.  In short, go with purpose-built solutions. And make sure they run in the cloud.

When managing service parts, you don’t know what will break and when because part failures are random and sudden. As a result, demand patterns are most often extremely intermittent and lack significant trend or seasonal structure. The number of part-by-location combinations is often in the hundreds of thousands, so it’s not feasible to manually review demand for individual parts. Nevertheless, it is much more straightforward to implement a planning and forecasting system to support spare parts planning than you might think.

This conclusion is informed by hundreds of software implementations we’ve directed over the years. Customers managing spare parts and service parts (the latter for internal consumption/MRO), and to a lesser degree aftermarket parts (for resale to installed bases), have consistently implemented our parts planning software faster than their peers in manufacturing and distribution.

The primary reason is the role in manufacturing and distribution of business knowledge about what might happen in the future. In a traditional B2B manufacturing and distribution environment, there are customers and sales and marketing teams selling to those customers. There are sales goals, revenue expectations, and budgets. This means there is a lot of business knowledge about what will be purchased, what will be promoted, whose opinions need to be accounted for. A complex planning loop is required. In contrast, when managing spare parts, you have a maintenance team that fixes equipment when it breaks. Though there are often maintenance schedules for guidance, what is needed beyond a standard list of consumable parts is often unknown until a maintenance person is on-site. In other words, there just isn’t the same sort of business knowledge available to parts planners when making stocking decisions.

Yes, that is a disadvantage, but it also has an upside: there is no need to produce a period-by-period consensus demand forecast with all the work that requires. When planning spare parts, you can usually skip many steps required for a typical manufacturer, distributor, or retailer. These skippable steps include:  

1. Building forecasts at different levels of the business, such as product family or region.
2. Sharing the demand forecast with sales, marketing, and customers.
3. Reviewing forecast overrides from sales, marketing, and customers.
4. Agreeing on a consensus forecast that combines statistics and business knowledge.
5. Measuring “forecast value add” to determine if overrides make the forecast more accurate.
6. Adjusting the demand forecast for known future promotions.
7. Accounting for cannibalization (i.e., if I sell more of product A, I’ll sell less of product B).

Freed from a consensus-building process, spare parts planners and inventory managers can rely directly on their software to predict usage and the required stocking policies. If they have access to a field-proven solution that addresses intermittent demand, they can quickly “go live” with more accurate demand forecasts and estimates of reorder points, safety stocks, and order suggestions.  Their attention can be focused on getting accurate usage and supplier lead time data. The “political” part of the job can be limited to obtaining organization consensus on service level targets and inventory budgets.

​

Service-Level-Driven Service Parts Planning is a four-step process that extends beyond simplified forecasting and rule-of-thumb safety stocks. It provides service parts planners with data-driven, risk-adjusted decision support.

Step 1. Ensure that all stakeholders agree on the metrics that matter. All participants in the service parts inventory planning process must agree on the definitions and what metrics matter most to the organization. Service Levels detail the percentage of time you can completely satisfy required usage without stocking out. Fill Rates detail the percentage of the requested usage that is immediately filled from stock. (To learn more about the differences between service levels and fill rate, watch this 4-minute lesson here.) Availability details the percentage of active spare parts that have an on-hand inventory of at least one unit. Holding costs are the annualized costs of holding stock accounting for obsolescence, taxes, interest, warehousing, and other expenses. Shortage costs are the cost of running out of stock including vehicle/equipment down time, expedites, lost sales, and more. Ordering costs are the costs associated with placing and receiving replenishment orders.

Step 2. Benchmark historical and predicted current service level performance. All participants in the service parts inventory planning process must hold a common understanding of predicted future service levels, fill rates and costs and their implications for your service parts operations. It is critical to measure both historical Key Performance Indicators (KPIs) and their predictive equivalents, Key Performance Predictions (KPPs). Leveraging modern software, you can benchmark past performance and leverage probabilistic forecasting methods to simulate future performance. By stress testing your current inventory stocking policies against all plausible scenarios of future demand, you will know ahead of time how current and proposed stocking policies are likely to perform.

Step 3. Agree on targeted service levels for each spare part and take proactive corrective action when targets are predicted to miss. Parts planners, supply chain leadership, and the mechanical/maintenance teams should agree on the desired service level targets with a full understanding of the tradeoffs between stockout risk and inventory cost. By leveraging what-if scenarios in modern parts planning software, it is possible to compare alternative stocking policies and identify those that best meets business objectives. Agree on what degree of stockout risk is acceptable for each part or class of parts. Likewise, determine inventory budgets and other cost constraints. Once these limits are agreed, take immediate action to avoid stockouts and excess inventory before they occur. Use your software to automatically upload modified reorder points, safety stock levels, and/or Min/Max parameters to your Enterprise Resource Planning (ERP) or Enterprise Asset Management (EAM) system to adjust daily parts purchasing.

Step 4. Make it so and keep it so. Empower the planning team with the knowledge and tools it needs to ensure that you strike agreed-upon balance between service levels and costs by driving your ordering process using optimized inputs (forecasts, reorder points, order quantities, safety stocks). Track your KPI’s and use your software to identify and address exceptions. Don’t let reorder points grow stale and outdated.  Recalibrate the stocking policies each planning cycle (at least once monthly) using up-to-date usage history, supplier lead times, and costs. Remember: Recalibration of your service parts inventory policy is preventive maintenance against both stockouts and excess stock.

​