The top 3 reasons why your spreadsheet won’t work for optimizing reorder points on spare parts

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.

 

Spare Parts Planning Software solutions

Smart IP&O’s service parts forecasting software uses a unique empirical probabilistic forecasting approach that is engineered for intermittent demand. For consumable spare parts, our patented and APICS award winning method rapidly generates tens of thousands of demand scenarios without relying on the assumptions about the nature of demand distributions implicit in traditional forecasting methods. The result is highly accurate estimates of safety stock, reorder points, and service levels, which leads to higher service levels and lower inventory costs. For repairable spare parts, Smart’s Repair and Return Module accurately simulates the processes of part breakdown and repair. It predicts downtime, service levels, and inventory costs associated with the current rotating spare parts pool. Planners will know how many spares to stock to achieve short- and long-term service level requirements and, in operational settings, whether to wait for repairs to be completed and returned to service or to purchase additional service spares from suppliers, avoiding unnecessary buying and equipment downtime.

Contact us to learn more how this functionality has helped our customers in the MRO, Field Service, Utility, Mining, and Public Transportation sectors to optimize their inventory. You can also download the Whitepaper here.

 

 

White Paper: What you Need to know about Forecasting and Planning Service Parts

 

This paper describes Smart Software’s patented methodology for forecasting demand, safety stocks, and reorder points on items such as service parts and components with intermittent demand, and provides several examples of customer success.

 

    Why Spare Parts Tradeoff Curves are Mission-Critical for Parts Planning

    I’ll bet your maintenance and repair teams would be ok with incurring higher stock out risks one some spare parts if they knew that the inventory reduction savings would be used to spread out the inventory investment more effectively to other parts and boost overall service levels.

    I’ll double down that your Finance team, despite always being challenged with lowering costs, would support a healthy inventory increase if they could clearly see that the revenue benefits from increased uptime, fewer expedites, and service level improvements clearly outweighed the additional inventory costs and risk.

    A spare parts tradeoff curve will enable service parts planning teams to properly communicate the risks and costs of each inventory decision.  It is mission critical for parts planning and the only way to adjust stocking parameters proactively and accurately for each part.  Without it, planners, for all intents and purposes, are “planning” with blinders on because they won’t be able to communicate the true tradeoffs associated with stocking decisions.

    For example, if a proposed increase to the min/max levels of an important commodity group of service parts is recommended, how do you know whether the increase is too high or too low or just right?  How can you fine-tune the change for thousands of spares?  You won’t and you can’t.  Your inventory decision making will rely on reactive, gut feel, and broad-brush decisions causing service levels to suffer and inventory costs to balloon.

    So, what exactly is a spare parts tradeoff curve anyway?

    It’s a fact-based, numerically driven prediction that details how changes in stocking levels will influence inventory value, holding costs, and service levels.  For each unit change in inventory level there is a cost and a benefit.  The spare parts tradeoff curve identifies these costs and benefits across different stocking levels. It lets planners discover the stock level that best balances the costs and benefits for each individual item.

    Here are two simplified examples. In Figure 1, the spare parts tradeoff curve shows how the service level (probability of not stocking out) changes depending on the reorder level.  The higher the reorder level, the lower the stockout risk.  It is critical to know how much service you are gaining given the inventory investment.  Here you may be able to justify that an inventory increase from a reorder point of 35 to 45 is well worth the investment of 10 additional units of stock because service levels jumps from just under 70% to 90%, cutting your stockout risk for the spare part from 30% to 10%!

     

    Cost vs Service Levels for inventory planning

    Figure 1: Cost versus Service Level

     

    Size of Inventory vs Service Levels for MRO

    Figure 2: Service Level versus Size of Inventory

    In this example (Figure 2), the tradeoff curve exposes a common problem with spare parts inventory.  Often stock levels are so high that they generate negative returns.  After a certain stocking quantity, each additional unit of stock does not buy more benefit in the form of a higher service level.  Inventory decreases can be justified when it is clear the stock level is well past the point of diminishing returns. An accurate tradeoff curve will expose the point where it is no longer advantageous to add stock.

    By leveraging #probabilisticforecasting to drive parts planning, you can communicate these tradeoffs accurately, do so at scale across hundreds of thousands of parts, avoid bad inventory decisions, and balance service levels and costs.  At Smart Software, we specialize in helping spare parts planners, Directors of Materials Management, and financial executives managing MRO, spare parts, and aftermarket parts to understand and exploit these relationships.

     

    Spare Parts Planning Software solutions

    Smart IP&O’s service parts forecasting software uses a unique empirical probabilistic forecasting approach that is engineered for intermittent demand. For consumable spare parts, our patented and APICS award winning method rapidly generates tens of thousands of demand scenarios without relying on the assumptions about the nature of demand distributions implicit in traditional forecasting methods. The result is highly accurate estimates of safety stock, reorder points, and service levels, which leads to higher service levels and lower inventory costs. For repairable spare parts, Smart’s Repair and Return Module accurately simulates the processes of part breakdown and repair. It predicts downtime, service levels, and inventory costs associated with the current rotating spare parts pool. Planners will know how many spares to stock to achieve short- and long-term service level requirements and, in operational settings, whether to wait for repairs to be completed and returned to service or to purchase additional service spares from suppliers, avoiding unnecessary buying and equipment downtime.

    Contact us to learn more how this functionality has helped our customers in the MRO, Field Service, Utility, Mining, and Public Transportation sectors to optimize their inventory. You can also download the Whitepaper here.

     

     

    White Paper: What you Need to know about Forecasting and Planning Service Parts

     

    This paper describes Smart Software’s patented methodology for forecasting demand, safety stocks, and reorder points on items such as service parts and components with intermittent demand, and provides several examples of customer success.

     

      How to Select the Right Forecasting Method with Epicor Smart IPO

      Smart Software is pleased to introduce our new series of educational webinars, offered exclusively for Epicor Users. In this webinar, Erik Subatis, Enterprise Solution Engineer at Smart Software, will reveal the statistical models Epicor Smart IP&O uses to forecast and how the automatic “best pick” system works. While automatic modeling is invaluable for large-scale forecasting, occasionally, these forecasts don’t reflect our expectations and/or business knowledge. Understanding how and when to override the model selection can be a valuable tool in a forecaster’s toolbox. Finally, the presentation will conclude by showing how to increase profitability with software-enhanced inventory planning processes in a Live Demo.

      Attending this webinar, you will learn about the statistical models Smart IP&O uses to forecast and how to catch the exceptions so you can make the most of your forecasting tool.

      WEBINAR REGISTRATION FORM

       

      Please register to attend the webinar. If you are interested but not cannot attend, please register anyway – we will record our session and will send you a link to the replay.

      We hope you will be able to join us!

       

      SmartForecasts and Smart IP&O are registered trademarks of Smart Software, Inc.  All other trademarks are the property of their respective owners.


      For more information, please contact Smart Software,Inc., Four Hill Road, Belmont, MA 02478.
      Phone: 1-800-SMART-99 (800-762-7899); E-mail: info@smartcorp.com

       

      Why Days of Supply Targets Don’t Work when Computing Safety Stocks

      Why Days of Supply Targets Don’t Work when Computing Safety Stocks

      CFOs tell us they need to spend less on inventory without impacting sales.  One way to do that is to move away from using targeted day of supply to determine reorder points and safety stock buffers.   Here is how a days of supply model works:

      1. Compute average demand per day and multiply the demand per day by supplier lead time in days to get lead time demand
      2. Pick a days of supply buffer (i.e., 15, 30, 45 days, etc.). Use larger buffers being used for more important items and smaller buffers for less important items.
      3. Add the desired days of supply buffer to demand over the lead time to get the reorder point. Order more when on hand inventory falls below the reorder point

      Here is what is wrong with this approach:

      1. The average doesn’t account for seasonality and trend – you’ll miss obvious patterns unless you spend lots of time manually adjusting for it.
      2. The average doesn’t consider how predictable an item is – you’ll overstock predictable items and understock less predictable ones. This is because the same days of supply for different items yields a very different stock out risk.
      3. The average doesn’t tell a planner how stock out risk is impacted by the level of inventory – you’ll have no idea whether you are understocked, overstocked, or have just enough. You are essentially planning with blinders on.

      There are many other “rule of thumb” approaches that are equally problematic.  You can learn more about them in this post

      A better way to plan the right amount of safety stock is to leverage probability models that identify exactly how much stock is needed given the risk of stock-out you are willing to accept.   Below is a screenshot of Smart Inventory Optimization that does exactly that.  First, it details the predicted service levels (probability of not stocking out) associated with the current days of supply logic.  The planner can now see the parts where predicted service level is too low or too costly.  They can then make immediate corrections by targeting the desired service levels and level of inventory investment. Without this information, a planner isn’t going to know whether the targeted days of safety stock is too much, too little, or just right resulting in overstocks and shortages that cost market share and revenue. 

      Computing Safety Stocks 2

       

      Smart Software to lead a webinar as part of the WERC Solutions Partner Program

      Belmont, MA, – Smart Software, Inc., provider of industry-leading demand forecasting, planning, and inventory optimization solutions, today announced that Greg Hartunian, President and CEO at Smart Software, will lead a 30-minute webinar as part of the WERC Solutions Partner Program 

      The presentation will focus on how a leading Electric Utility implemented Smart Inventory Planning and Optimization (Smart IP&O) as part of the company’s strategic supply chain optimization (SCO) initiative. Smart IP&O was implemented in just 90 days, enabling the utility to optimize its reorder points and order quantities for over 250,000 spare parts. During the first phase of the implementation, the platform helped the electric utility reduce inventory by $9,000,000 while maintaining service levels.

      Finally, the webinar will conclude by showing Smart IP&O in a Live Demo.

       

      Warehousing Education and Research Council (WERC)

      WERC is a professional organization focused on logistics management and its role in the supply chain. Since being founded in 1977, WERC has maintained a strategic vision to continuously offer resources that help distribution practitioners and suppliers stay on top in our dynamic, variable field. In an increasingly complex world, distribution logistics professionals make sense of things so that people get their products and services, companies deliver on their commitments, economies grow, and communities thrive.

      WERC powers distribution logistics professionals to do their jobs, excel in their careers and make a difference in the world. WERC helps its members and companies succeed by creating unparalleled learning experiences, offering quality networking opportunities, and accessing research-driven industry information.

       

      About Smart Software, Inc.
      Founded in 1981, Smart Software, Inc. is a leader in providing businesses with enterprise-wide demand forecasting, planning and inventory optimization solutions.  Smart Software’s demand forecasting and inventory optimization solutions have helped thousands of users worldwide, including customers at mid-market enterprises and Fortune 500 companies, such as Otis Elevator, Mitsubishi, Siemens, Disney, FedEx, MARS, and The Home Depot.  Smart Inventory Planning & Optimization gives demand planners the tools to handle sales seasonality, promotions, new and aging products, multi-dimensional hierarchies, and intermittently demanded service parts and capital goods items.  It also provides inventory managers with accurate estimates of the optimal inventory and safety stock required to meet future orders and achieve desired service levels.  Smart Software is headquartered in Belmont,

       


      For more information, please contact Smart Software, Inc., Four Hill Road, Belmont, MA 02478.
      Phone: 1-800-SMART-99 (800-762-7899); FAX: 1-617-489-2748; E-mail: info@smartcorp.com