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.

​

​​In a highly configurable manufacturing environment, forecasting finished goods can become a complex and daunting task. The number of possible finished products skyrockets when many components are interchangeable. A traditional MRP would force us to forecast every single finished product, which can be unrealistic or even impossible. Several leading solutions introduce the concept of the “Planning BOM,” which allows the use of forecasts at a higher level in the manufacturing process. In this article, we will discuss this functionality in Epicor Kinetic and how you can take advantage of it with Epicor Smart Inventory Planning and Optimization (Smart IP&O) to get ahead of your demand in the face of this complexity.

Why Would I Need a Planning BOM?

Traditionally, each finished product or SKU would have a rigidly defined bill of materials. If we stock that product and want to plan around forecasted demand, we will forecast demand for those products and then feed MRP to blow this forecasted demand from the finished good level down to its components via the BOM.

Many companies, however, offer highly configurable products where customers can select options on the product they buy. As an example, recall the last time you bought a cellphone. You chose a brand and model, but from there, you were likely presented with options: what screen size do you want? How much storage do you want? What color do you prefer? If that business wants to have these cellphones ready and available to ship to you in a reasonable time, suddenly, they are no longer just anticipating demand for that model—they must forecast that model for every type of screen size, for all storage capacities, for all colors, and all possible combinations of those as well! For some manufacturers, these configurations can compound to hundreds or thousands of possible finished good permutations.

There may be so many possible customizations that the demand at the finished product level is completely unforecastable in a traditional sense. Thousands of those cellphones may sell every year, but for each possible configuration, the demand may be extremely low and sporadic—perhaps certain combinations sell once and never again.

This often forces these companies to plan reorder points and safety stock levels mostly at the component level, while largely reacting to firm demand at the finished good level via MRP. While this is a valid approach, it lacks a systematic way to leverage forecasts that may account for anticipated future activity such as promotions, upcoming projects, or sales opportunities. Forecasting at the “configured” level is effectively impossible, and trying to weave in these forecast assumptions at the component level isn’t feasible either.

Planning BOM Explained This is where Planning BOMs come in. Perhaps the sales team is working on a big B2B opportunity for that model, or there’s a planned promotion for Cyber Monday. While trying to work in those assumptions for every possible configuration isn’t realistic, doing it at the model level is totally doable—and tremendously valuable.

The Planning BOM can use a forecast at a higher level and then blow demand down based on predefined proportions for its possible components. For example, the cellphone manufacturer may know that most people opt for 128GB of storage, and far fewer opt for upgrades to 256GB or 512GB. The planning BOM allows the organization to (for example) blow 60% of the demand down to the 128GB option, 30% to the 256GB option, and 10% to the 512GB option. They could do the same for screen sizes, colors, or other available customizations.

The business can now focus its forecast at this model level, leaving the Planning BOM to determine the component mix. Clearly, defining these proportions requires some thought, but Planning BOMs effectively allows businesses to forecast what would otherwise be unforecastable.

The Importance of a Good Forecast

Of course, we still need a good forecast to load into Epicor Kinetic. As explained in this article, while Epicor Kinetic can import a forecast, it often cannot generate one, and when it does it tends to require a great deal of hard-to-use configurations that don’t often get revisited, resulting in inaccurate forecasts. It is, therefore, up to the business to come up with its own sets of forecasts, often manually produced in Excel. Forecasting manually generally presents a number of challenges, including but not limited to:

• The inability to identify demand patterns like seasonality or trend.
• Overreliance on customer or sales forecasts.
• Lack of accuracy or performance tracking.

No matter how well configured the MRP is with your carefully considered Planning BOMs, a poor forecast means poor MRP output and mistrust in the system—garbage in, garbage out. Continuing along with the “cellphone company” example, without a systematic way of capturing key demand patterns and/or domain knowledge in the forecast, MRP can never see it.

Smart IP&O: A Comprehensive Solution

Smart IP&O supports planning at all levels of your BOM, though the “blowing out” is handled via MRP inside Epicor Kinetic. Here is the method we use for our Epicor Kinetic customers, which is straightforward and effective:

• Smart Demand Planner: The platform contains a purpose-built forecasting application called Smart Demand Planner that you will use to forecast demand for your manufactured products (usually finished goods). It generates statistical forecasts, enables planners to make adjustments and/or weave in other forecasts (such as sales or customer forecasts), and tracks accuracy. The output of this is a forecast that goes into forecast entry inside Epicor Kinetic, where MRP will pick it up. MRP will subsequently use demand at the finished good level, and also blow out material requirements through the BOM, so that demand is recognized at lower levels as well.
• Smart Inventory Optimization: You simultaneously use Smart Inventory Optimization to set min/max/safety levels both for any finished goods you make to stock (if applicable; some of our customers operate purely make-to-order off of firm demand), as well as for raw materials. The key here is that at the raw material level, Smart will leverage job usage demand, supplier lead times, etc., to optimize these parameters while at the same time using sales orders/shipments as demand at the finished good level. Smart handles these multiple inputs of demand elegantly via the bidirectional integration with Epicor Kinetic.

When MRP runs, it nets out supply & demand (which, once again, includes raw material demand blown out from the finished good forecast) against the min/max/safety levels you have established to suggest PO and job suggestions.

Extend Epicor Kinetic with Smart IP&O

Smart IP&O is designed to extend your Epicor Kinetic system with many integrated demand planning and inventory optimization solutions. For example, it can generate statistical forecasts automatically for large numbers of items, allows for intuitive forecast adjustments, tracks forecast accuracy, and ultimately allows you to generate true consensus-based forecasts to better anticipate the needs of your customers.

Thanks to highly flexible product hierarchies, Smart IP&O is perfectly suited to forecasting at the Planning BOM level, so you can capture key patterns and incorporate business knowledge at the levels that matter most. Furthermore, you can analyze and deploy optimal safety stock levels at any level of your BOM.

Leveraging Epicor Kinetic’s Planning BOM capabilities alongside Smart IP&O’s advanced forecasting and inventory optimization features ensures that you can meet demand efficiently and accurately, regardless of the complexity of your product configurations. This synergy not only enhances forecast accuracy but also strengthens overall operational efficiency, helping you stay ahead in a competitive market.

Just as the renowned astronomer Copernicus transformed our understanding of astronomy by placing the sun at the center of our universe, today, we invite you to re-center your approach to inventory management. And while not quite as enlightening, this advice will help your company avoid being caught in the gravitational pull of inventory woes—constantly orbiting between stockouts, surplus gravity, and the unexpected cosmic expenses of expediting?

In this article, we’ll walk you through the process of crafting a spare parts inventory plan that prioritizes availability metrics such as service levels and fill rates while ensuring cost efficiency. We’ll focus on an approach to inventory planning called Service Level-Driven Inventory Optimization. Next, we’ll discuss how to determine what parts you should include in your inventory and those that might not be necessary. Lastly, we’ll explore ways to enhance your service-level-driven inventory plan consistently.

In service-oriented businesses, the consequences of stockouts are often very significant.  Achieving high service levels depends on having the right parts at the right time. However, having the right parts isn’t the only factor. Your Supply Chain Team must develop a consensus inventory plan for every part, then continuously update it to reflect real-time changes in demand, supply, and financial priorities.

Managing inventory with Service-level-driven planning combines the ability to plan thousands of items with high-level strategic modeling. This requires addressing core issues facing inventory executives:

• Lack of control over supply and associated lead times.
• Unpredictable intermittent demand.
• Conflicting priorities between maintenance/mechanical teams and Materials Management.
• Reactive “wait and see” approach to planning.
• Misallocated inventory, causing stockouts and excess.
• Lack of trust in systems and processes.

The key to optimal service parts management is to grasp the balance between providing excellent service and controlling costs. To do this, we must compare the costs of stockout with the cost of carrying additional spare parts inventory. The costs of a stockout will be higher for critical or emergency spares, when there is a service level agreement with external customers, for parts used in multiple assets, for parts with longer supplier lead times, and for parts with a single supplier. The cost of inventory may be assessed by considering the unit costs, interest rates, warehouse space that will be consumed, and potential for obsolescence (parts used on a soon-to-be-retired fleet have a higher obsolescence risk, for example).

To arbitrate how much stock should be put on the shelf for each part, it is critical to establish consensus on the desired key metrics that expose the tradeoffs the business must make to achieve the desired KPIs. These KPIs will include Service Levels that tell you how often you meet usage needs without falling short on stock, Fill Rates that tell you what percentage of demand is filled, and Ordering costs detail the expenses incurred when you place and receive replenishment orders. You also have Holding costs, which encompass expenses like obsolescence, taxes, and warehousing, and Shortage costs that pertain to expenses incurred when stockouts happen.

An MRO business or Aftermarket Parts Planning team might desire a 99% service level across all parts – i.e., the minimum stockout risk that they are willing to accept is 1%. But what if the amount of inventory needed to support that service level is too expensive? To make an informed decision on whether there is going to be a return on that additional inventory investment, you’ll need to know the stockout costs and compare that to the inventory costs. To get stockout costs, multiply two key elements: the cost per stockout and the projected number of stockouts. To get inventory value, multiply the units required by the unit cost of each part. Then determine the annual holding costs (typically 25-35% of the unit cost). Choose the option that yields a total lower cost. In other words, if the benefit associated with adding more stock (reduced shortage costs) outweighs the cost (higher inventory holding costs), then go for it. A thorough understanding of these metrics and the associated tradeoffs serves as the compass for decision-making.

Modern software aids in this process by allowing you to simulate a multitude of future scenarios. By doing so, you can assess how well your current inventory stocking strategies are likely to perform in the face of different demand and supply patterns. If anything falls short or goes awry, it’s time to recalibrate your approach, factoring in current data on usage history, supplier lead times, and costs to prevent both stockouts and overstock situations.

Enhance your service-level-driven inventory plan consistently.

In conclusion, it’s crucial to assess your service-level-driven plan continuously. By systematically constructing and refining performance scenarios, you can define key metrics and goals, benchmark expected performance, and automate the calculation of stocking policies for all items. This iterative process involves monitoring, revising, and repeating each planning cycle.

The depth of your analysis within these stocking policies relies on the data at your disposal and the configuration capabilities of your planning system. To achieve optimal outcomes, it’s imperative to maintain ongoing data analysis. This implies that a manual approach to data examination is typically insufficient for the needs of most organizations.

For information on how Smart Software can help you meet your service supply chain goals with service-driven planning and more, visit the following blogs.

–   “Explaining What  Service-Level Means in Your Inventory Optimization Software”  Stocking recommendations can be puzzling, especially when they clash with real-world needs.  In this post, we’ll break down what that 99% service level means and why it’s crucial for managing inventory effectively and keeping customers satisfied in today’s competitive landscape.

–  “Service-Level-Driven Planning for Service Parts Businesses” 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.

–   “How to Choose a Target Service Level.” This is a strategic decision about inventory risk management, considering current service levels and fill rates, replenishment lead times, and trade-offs between capital, stocking and opportunity costs.  Learn approaches that can help.

–   “The Right Forecast Accuracy Metric for Inventory Planning.”  Just because you set a service level target doesn’t mean you’ll actually achieve it. If you are interested in optimizing stock levels, focus on the accuracy of the service level projection. Learn how.