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.

​

MRO organizations exist in a wide range of  industries, including public transit, electrical utilities, wastewater, hydro power, aviation, and mining. To get their work done, MRO professionals use Enterprise Asset Management (EAM) and Enterprise Resource Planning (ERP) systems. These systems are designed to do a lot of jobs. Given their features, cost, and extensive implementation requirements, there is an assumption that EAM and ERP systems can do it all.

For example, at a recent Maximo Utilities Working Group event, several prospects stated that “Our EAM will do that” when asked about requirements for forecasting usage, netting out supply plans, and optimizing inventory policies. They were surprised to learn it did not and wanted to know more.

In this post, we summarize the need for add-on software that addresses specialized analytics for inventory optimization, forecasting, and service parts planning.   

EAM Systems

EAM systems can’t ingest forecasts of future usage – these systems simply aren’t designed to conduct supply planning and many don’t even have a place to hold forecasts. So, when an MRO business needs to net out known requirements for planned production or capital projects, an add-on application like Smart IP&O is needed.

Inventory Optimization software with features that support planning known future demand will take project-based data not maintained in the EAM system (including project start dates, duration, and when each part is expected to be needed) and compute a period-by-period forecast over any planning horizon. That “planned” forecast can be projected alongside statistical forecasts of “unplanned” demand arising from normal wear and tear. At that point, parts planning software can net out the supply and identify gaps between supply and demand. This ensures that these gaps won’t go unnoticed and result in shortages that would otherwise delay the completion of the projects. It also minimizes excess stock that would otherwise be ordered too soon and needlessly consumes cash and warehouse space. Again, MRO businesses sometimes mistakenly assume that these capabilities are addressed by their EAM package.

ERP Systems

ERP systems, on the other hand, typically do include an MRP module that is designed to ingest a forecast and net out material requirements. Processing will consider current on hand inventory, open sales orders, scheduled jobs, incoming purchase orders, any bill of materials, and items in transit while transferring between sites. It will compare those current state values to the replenishment policy fields plus any monthly or weekly forecasts to determine when to suggest replenishment (a date) and how much to replenish (a quantity).

So, why not use the ERP system alone to net out the supply plan to prevent shortages and excess? First, while ERP systems have a placeholder for a forecast and some systems can net out supply using their MRP modules, they don’t make it easy to reconcile planned demand requirements associated with capital projects. Most of the time, the data on when planned projects will occur is maintained outside of the ERP, especially the project’s bill of materials detailing what parts will be needed to support the project. Second, many ERP systems don’t offer anything effective when it comes to predictive capabilities, relying instead on simple math that just won’t work for service parts due to the high prevalence of intermittent demand. Finally, ERP systems don’t have flexible user-friendly interfaces that support interacting with the forecasts and supply plan.

Reorder Point Logic

Both ERP and EAM have placeholders for reorder point replenishment methods such as Min/Max levels. You can use inventory optimization software to populate these fields with the risk-adjusted reorder point policies. Then within the ERP or EAM systems, orders are triggered whenever actual (not forecasted) demand drives on-hand stock below the Min. This type of policy doesn’t use a traditional forecast that projects demand week-over-week or month-over-month and is often referred to as “demand driven replenishment” (since orders only occur when actual demand drives stock below a user defined threshold).

But just because it isn’t using a period-over-period forecast doesn’t mean it isn’t being predictive. Reorder point policies should be based on a prediction of demand over a replenishment lead time plus a buffer to protect against demand and supply variability. MRO businesses need to know the stockout risk they are incurring with any given stocking policy. After all, inventory management is risk management – especially in MRO businesses when the cost of stockout is so high. Yet, ERP and EAM do not offer any capabilities to risk-adjust stocking policies. They force users to manually generate these policies externally or to use basic rule of thumb math that doesn’t detail the risks associated with the choice of policy.

Summary

Supply chain planning functionality such as inventory optimization isn’t the core focus of EAM  and ERP. You should leverage add-on planning platforms, like Smart IP&O, that support statistical forecasting, planned project management, and inventory optimization. Smart IP&O will develop forecasts and stocking policies that can be input to an EAM or ERP system to drive daily ordering.

True or false: The forecast doesn’t matter to spare parts inventory management.

At first glance, this statement seems obviously false. After all, forecasts are crucial for planning stock levels, right?

It depends on what you mean by a “forecast”. If you mean an old-school single-number forecast (“demand for item CX218b will be 3 units next week and 6 units the week after”), then no. If you broaden the meaning of forecast to include a probability distribution taking account of uncertainties in both demand and supply, then yes.

The key reality is that many items, especially spare and service parts, have unpredictable, intermittent demand. (Supplier lead times can also be erratic, especially when parts are sourced from a backlogged OEM.)  We have observed that while manufacturers and distributors typically experience intermittent demand on just 20% or more of their items the percentage grows to 80%+ for MRO based businesses.  This means historical data often show periods of zero demand interspersed with random periods of non-zero demand. Sometimes, these non-zero demands are as low as 1 or 2 units, while at other times, they unexpectedly spike to quantities several times larger than their average.

This isn’t like the kind of data usually faced by your peer “demand planners” in retail, consumer products, and food and beverage. Those folks usually deal with larger quantities having proportionately less randomness. And they can surf on prediction-enhancing features like trends and stable seasonal patterns. Instead, spare parts usage is much more random, throwing a monkey wrench into the planning process, even in the minority of cases in which there are detectable seasonal variations.

In the realm of intermittent demand, the best forecast available will significantly deviate from the actual demand. Unlike consumer products with medium to high volume and frequency, a service part’s forecast can miss the mark by hundreds of percentage points. A forecast of one or two units, on average, will always miss when the actual demand is zero. Even with advanced business intelligence or machine learning algorithms, the error in forecasting the non-zero demands will still be substantial.

Perhaps because of the difficulty of statistical forecasting in the inventory domain, inventory planning in practice often relies on intuition and planner knowledge. Unfortunately, this approach doesn’t scale across tens of thousands of parts. Intuition just cannot cope with the full range of demand and lead time possibilities, let alone accurately estimate the  probability of each possible scenario. Even if your company has one or two exceptional intuitive forecasters, personnel retirements and product line reorganizations mean that intuitive forecasting can’t be relied on going forward.

The solution lies in shifting focus from traditional forecasts to predicting probabilities for each potential demand and lead time scenario. This shift transforms the conversation from an unrealistic “one number plan” to a range of numbers with associated probabilities. By predicting probabilities for each demand and lead time possibility, you can better align stock levels with the risk tolerance for each group of parts.

Software that generates demand and lead time scenarios, repeating this process tens of thousands of times, can accurately simulate how current stocking policies will perform against these policies. If the performance in the simulation falls short and you are predicted to stock out more often than you are comfortable with or you are left with excess inventory, conducting what-if scenarios allows adjustments to policies. You can then predict how these revised policies will fare against random demands and lead times. You can conduct this process iteratively and refine it with each new what-if scenario or lean on system prescribed policies that optimally strike a balance between risk and costs.

So, if you are planning service and spare parts inventories, stop worrying about predicting demand the way traditional retail and CPG demand planners do it. Focus instead on how your stocking policies will withstand the randomness of the future, adjusting them based on your risk tolerance. To do this, you’ll need the right set of decision support software, and this is how Smart Software can help.

For too many companies, a critical piece of data fact-finding ― the measurement of demand uncertainty ― is handled by simple but inaccurate rules of thumb.  For example, demand planners will often compute safety stock by a user-defined multiple of the forecast or historical average.  Or they may configure their ERP to order more when on hand inventory gets to 2 x the average demand over the lead time for important items and 1.5 x for less important ones. This is a huge mistake with costly consequences.

The choice of multiple ends up being a guessing game.  This is because no human being can compute exactly how much inventory to stock considering all the uncertainties.  Multiples of the average lead time demand are simple to use but you can never know whether the multiple used is too large or too small until it is too late.  And once you know, all the information has changed, so you must guess again and then wait and see how the latest guess turns out.  With each new day, you have new demand, new details on lead times, and the costs may have changed.  Yesterday’s guess, no more matter how educated is no longer relevant today.  Proper inventory planning should be void of inventory and forecast guesswork.  Decisions must be made with incomplete information but guessing is not the way to go.

Knowing how much to buffer requires a fact-based statistical analysis that can accurately answer questions such as:

• How much extra stock is needed to improve service levels by 5%
• What the hit to on-time delivery will be if inventory is reduced by 5%
• What service level target is most profitable.
• How will the stockout risk be impacted by the random lead times we face.

Intuition can’t answer these questions, doesn’t scale across thousands of parts, and is often wrong.  Data, probability math and modern software are much more effective. Winging it is not the path to sustained excellence.

Do MRO companies genuinely prioritize reducing excess spare parts inventory? From an organizational standpoint, our experience suggests not necessarily. Boardroom discussions typically revolve around expanding fleets, acquiring new customers, meeting service level agreements (SLAs), modernizing infrastructure, and maximizing uptime. In industries where assets supported by spare parts cost hundreds of millions or generate significant revenue (e.g., mining or oil & gas), the value of the inventory just doesn’t raise any eyebrows, and organizations tend to overlook massive amounts of excessive inventory.

Consider a public transit agency.  In most major cities, the annual operating budgets will exceed $3 billion.  Capital expenses for trains, subway cars, and infrastructure may reach hundreds of millions annually. Consequently, a spare parts inventory valued at $150 million might not grab the attention of the CFO or general manager, as it represents a small percentage of the balance sheet.  Moreover, in MRO-based industries, many parts need to support equipment fleets for a decade or more, making additional stock a necessary asset. In some sectors like utilities, holding extra stock can even be incentivized to ensure that equipment is kept in a state of good repair.

We have seen concerns about excess stock arise when warehouse space is limited. I recall, early in my career, witnessing a public transit agency’s rail yard filled with rusted axles valued at over $100,000 each.  I was told the axles were forced to be exposed to the elements due to insufficient warehouse space. The opportunity cost associated with the space consumed by extra stock becomes a consideration when warehouse capacity is exhausted. The primary consideration that trumps all other decisions is how the stock ensures high service levels for internal and external customers.  Inventory planners worry far more about blowback from stockouts than they do from overbuying.  When a missing part leads to an SLA breach or downed production line, resulting in millions in penalties and unrecoverable production output, it is understandable.

Asset-intensive companies are missing one giant point. That is, the extra stock doesn’t insulate against stockouts; it contributes to them. The more excess you have, the lower your overall service level because the cash needed to purchase parts is finite, and cash spent on excess stock means there isn’t cash available for the parts that need it.  Even publicly funded MRO businesses, like utilities and transit agencies, acknowledge the need to optimize spending, now more than ever.  As one materials manager shared, “We can no longer fix problems with bags of cash from Washington.”  So, they must do more with less, ensuring optimal allocation across the tens of thousands of parts they manage.

This is where state-of-the-art inventory optimization software comes in, predicting the required inventory for targeted service levels, identifying when stock levels yield negative returns, and recommending reallocations for improved overall service levels.  Smart Software has helped asset intensive MRO based businesses optimize reorder levels across each part for decades. Give us a call to learn more.