Companies that depend on spreadsheets for demand planning, forecasting, and inventory management are often constrained by the spreadsheet’s inherent limitations. This post examines the drawbacks of traditional inventory management approaches caused by spreadsheets and their associated costs, contrasting these with the significant benefits gained from embracing state-of-the-art planning technologies.

Spreadsheets, while flexible for their infinite customizability, are fundamentally manual in nature requiring significant data management, human input, and oversight. This increases the risk of errors, from simple data entry mistakes to complex formula errors, that cause cascading effects that adversely impact forecasts.  Additionally, despite advances in collaborative features that enable multiple users to interact with a common sheet, spreadsheet-based processes are often siloed. The holder of the spreadsheet holds the data.  When this happens, many sources of data truth begin to emerge.  Without the trust of an agreed-upon, pristine, and automatically updated source of data, organizations don’t have the necessary foundation from which predictive modeling, forecasting, and analytics can be built.

In contrast, advanced planning systems like Smart IP&O are designed to overcome these limitations. Such systems are built to automatically ingest data via API or files from ERP and EAM systems, transform that data using built in ETL tools, and can process large volumes of data efficiently.  This enables businesses to manage complex inventory and forecasting tasks with greater accuracy and less manual effort because the data collection, aggregation, and transformation is already done. Transitioning to advanced planning systems is key for optimizing resources for several reasons.

Spreadsheets also have a scaling problem. The bigger the business grows, the greater the number of spreadsheets, workbooks, and formulas becomes.  The result is a tightly wound and rigid set of interdependencies that become unwieldy and inefficient.  Users will struggle to handle the increased load and complexity with slow processing times and an inability to manage large datasets and face challenges collaborating across teams and departments.

On the other hand, advanced planning systems for inventory optimization, demand planning, and inventory management are scalable, designed to grow with the business and adapt to its changing needs. This scalability ensures that companies can continue to manage their inventory and forecasting effectively, regardless of the size or complexity of their operations. By transitioning to systems like Smart IP&O, companies can not only improve the accuracy of their inventory management and forecasting but also gain a competitive edge in the market by being more responsive to changes in demand and more efficient in their operations.

Benefits of Jumping in: An electric utility company struggled to maintain service parts availability without overstocking for over 250,000-part numbers across a diverse network of power generation and distribution facilities. It replaced their twenty-year-old legacy planning process that made heavy use of spreadsheets with Smart IP&O and a real-time integration to their EAM system.  Before Smart, they were only able to modify Min/Max and Safety Stock levels infrequently.  When they did, it was nearly always because a problem occurred that triggered the review.  The methods used to change the stocking parameters relied heavily on gut feel and averages of the historical usage.   The Utility leveraged Smart’s what-if scenarios to create digital twins of alternate stocking policies and simulated how each scenario would perform across key performance indicators such as inventory value, service levels, fill rates, and shortage costs.  The software pinpointed targeted Min/Max increases and decreases that were deployed to their EAM system, driving optimal replenishments of their spare parts.  The result:  A significant inventory reduction of $9 million that freed up cash and valuable warehouse space while sustaining 99%+ target service levels.

Managing Forecast Accuracy: Forecast error is an inevitable part of inventory management, but most businesses don’t track it.  As Peter Drucker said, “You can’t improve what you don’t measure.”  A global high-tech manufacturing company utilizing a spreadsheet-based forecast process had to manually create its baseline forecasts and forecast accuracy reporting.  Given the planners’ workload and siloed processes, they just didn’t update their reports very often, and when they did, the results had to be manually distributed.  The business didn’t have a way of knowing just how accurate a given forecast was and couldn’t cite their actual errors by group of part with any confidence.  They also didn’t know whether their forecasts were outperforming a control method.  After Smart IP&O went live, the Demand Planning module automated this for them. Smart Demand Planner now automatically reforecasts their demand each planning cycle utilizing ML methods and saves accuracy reports for every part x location.  Any overrides that are applied to the forecasts can now be auto-compared to the baseline to measure forecast value add – i.e., whether the additional effort to make those changes improved the accuracy.  Now that the ability to automate the baseline statistical forecasting and produce accuracy reports is in place, this business has solid footing from which to improve their forecast process and resulting forecast accuracy.

Get it Right and Keep it Right:  Another customer in the aftermarket parts business has used Smart’s forecasting solutions since 2005 – nearly 20 years!  They were faced with challenges forecasting intermittently demanded parts sold to support their auto aftermarket business. By replacing their spreadsheet-based approach and manual uploads to SAP with statistical forecasts of demand and safety stock from SmartForecasts, they were able to significantly reduce backorders and lost sales, with fill rates improving from 93% to 96% within just three months.  The key to their success was leveraging Smart’s patented method for forecasting intermittent demand – The “Smart-Willemain” bootstrap method generated accurate estimates of the cumulative demand over the lead time that helped ensure better visibility of the possible demands.

Connecting Forecasts to the Inventory Plan: Advanced planning systems support forecast-based inventory management, which is a proactive approach that relies on demand forecasts and simulations to predict possible outcomes and their associated probabilities.  This data is used to determine optimal inventory levels.  Scenario-based or probabilistic forecasting contrasts with the more reactive nature of spreadsheet-based methods. A longtime customer in the fabric business, previously dealt with overstocks and stockouts due to intermittent demand for thousands of SKUs. They had no way of knowing what their stock-out risks were and so couldn’t proactively modify policies to mitigate risk other than making very rough-cut assumptions that tended to overstock grossly.  They adopted Smart Software’s demand and inventory planning software to generate simulations of demand that identified optimal Minimum On-Hand values and order quantities, maintaining product availability for immediate shipping, highlighting the advantages of a forecast-based inventory management approach.

Better Collaboration:  Sharing forecasts with key suppliers helps to ensure supply.  Kratos Space, part of Kratos Defense & Security Solutions, Inc., leveraged Smart forecasts to provide their Contract Manufacturers with better insights on future demand.  They used the forecasts to make commitments on future buys that enabled the CM to reduce material costs and lead times for engineered-to-order systems. This collaboration demonstrates how advanced forecasting techniques can lead to significant supply chain collaboration that yields efficiencies and cost savings for both parties.

​

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.