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.