The Cost of Spreadsheet Planning

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.

 

Big Ass Fans Turns to Smart Software as Demand Heats Up

Big Ass Fans is the best-selling big fan manufacturer in the world, delivering comfort to spaces where comfort seems impossible.  BAF had a problem:  how to reliably plan production to meet demand.  BAF was experiencing a gap between bookings forecasts vs. shipments, and this was impacting revenue and customer satisfaction.  BAF turned to Smart Software for help.

BAF’s Supply Chain Manager took the lead to flesh out their planning needs and methodically address them.  In his words, “it came down to fundamentals. Our planning process needed to be data driven, collaborative, and continually improved by assessing and enhancing our monthly forecasting process.”

A big part of this was bringing the disparate planning processes together.  Product managers produce monthly demand forecasts, while the operations team forecasts shipments and associated material requirements.  BAF needed a tighter, data-driven process that combines advanced analytics with team collaboration.  This would need to address seasonality, a huge factor driving demand fluctuations, incorporate input from international as well as US markets, and capture the impact of market promotions.

BAF’s Customer Service Director and S&OP Team Lead explained what this means.  “Now we have one unified, global process, one shared business view that provides the framework for all of our cross-business planning.”  She likens it to having one source for the truth.  “Every month the entire team sees updated orders and shipments and can compare forecast against actual performance.  Individual managers view business through their required  business lens – by product line or service, region, international geography, channel, customer, you name it.”

“This is enabling technology that makes us better,” she continued.  “Smart IP&O is, among other things, the vehicle for our monthly SIOP process.  We review our own business segments then convene as a group, consider results to date, the impact of promotions, events and seasonality, and agree on our consensus plan going forward.  This is an invaluable process, enabling manufacturing to stay ahead of demand and deliver what our customers need, when they need it.”

BAF Case Study SIOP planning Inventory Warehouse

“Smart Inventory Planning & Optimization is the critical tool we use to manage our forecasts across a large and dynamic set of Products/Parts, multi-national sites, and complex supply chains,” added the Supply Chain Manager.  “The ability of the software to provide a statistical forecast as baseline, allow adjustments by various subject matter experts, each recorded as ‘snapshots’ for consensus building and later use in accuracy/improvement efforts, then ultimately feed the forecast data directly into our Material Requirements Planning software is central to our S&OP process.”

BAF has refined its monthly Sales, Inventory and Operations Planning process utilizing Smart Demand Planner, Smart’s collaborative forecasting and demand planning application. Smart’s API based bi-directional integration with BAF’s Epicor Kinetic ERP automatically captures all order and shipment data that in turn drives the creation of monthly statistical forecasts.  Through its monthly SIOP process, BAF product managers produce initial forecasts, share these with sales managers who can suggest adjustments, and bring together consensus plans across 25 product lines for monthly review, adjustment, and presentation to the executive team as the company’s rolling 12-month plan.

The team credits Smart Demand Planner with providing a thorough and accurate forecast of future demand that is central to BAF’s monthly SIOP process.  BAF extended Smart’s utilization to its international offices, where subject matter experts manage their own forecasts.  “Within Smart they can manage both demand forecasts that key on their shipments to local end users and supply forecasts based on their purchase history as key customers to BAF-US.  This significantly enhances our global demand view and has improved forecast accuracy.”

About Smart Software:

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 such as Disney, Arizona Public Service, and Ameren. Smart’s Inventory Planning & Optimization Platform, Smart IP&O, provides 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, Massachusetts.  Learn more at www.smartcorp.com.

BAF Case Study SIOP planning manufacturing

About Big Ass Fans

At Big Ass Fans, we are driven by our mission to create safer, healthier, more productive environments worldwide. What started as a big idea in airflow became a revolution and is now best practice for designers, managers, and business owners across every imaginable industry and application. Today, our products are proudly spinning and serving more than 80 percent of the Fortune 500 in 175 countries. From factories to homes and everywhere in between, Big Ass Fans delivers comfort, style, and energy savings to make life more enjoyable. With more than 235 awards, 350 patents, an experiment on the International Space Station and the only HVLS Research & Design lab in the world, we go big every day.

Procon Pumps Uses Smart Demand Planner to Keep Business Flowing

Introduction:
Procon, an industry leading pump manufacturer, uses Smart IP&O’s demand planning and inventory optimization modules from Smart Software to make sure they have the products their customers need, when they need them.  You might not have heard of their products, but if you’ve ever eaten at McDonalds or sipped a coffee at Starbucks, you have been served by Procon.  Procon’s broad portfolio of over 7,000 SKUs is supplied to more than 70 countries worldwide through their direct sales channel and an extensive distributor network.  Procon operates manufacturing facilities in the US, Mexico, Ireland, and through a licensed manufacturing partner in Japan.  We spoke with Procon’s Shankar Suman, Director of Sales, and Emer Horan, Global Supply Chain Manager, to learn more.

The Challenge
If Procon cannot ship a required product, their customers cannot ship theirs.  Accurate forecasting is a key driver of supply chain success and customer satisfaction. Procon’s monthly planning establishes the consensus demand plan that drives procurement, production, and stocking policies.  But they found they had a gap between sales and procurement, which historically led to missed deliveries and excess inventory.  What Procon needed was a robust demand forecasting and inventory optimization tool that was easy to use, enabled collaborative planning with their sales team and partners, and integrated with their  ERP system to drive procurement and production planning.

The Solution:
They found this in Smart IP&O,  web-based platform for statistical forecasting, demand planning, and inventory optimization.

  • Shankar Suman cited a broad mix of capabilities that convinced them to utilize Smart. Chief among them were:
  •   Smart Demand Planner supports the easy, orchestrated flow of information that yields an accurate consensus plan.  Presenting performance history and statistical forecast by product, territory, and partner, SDP provide the sales team with perspective that they can complement – adjusting for expected opportunities or demand shifts.
  • Forecast accuracy. Smart is an industry leader in statistical analytics, leveraging innovations developed over its forty-plus year history.  This combined with robust forecast vs. actuals analysis helps Procon continually improve the quality of their forecasts.
  • Transparent connectivity with Procon’s enterprise software, Epicor Kinetic. Daily sales and shipment data are automatically pulled into the Smart platform, fueling Smart’s forecasting engine, and results are easily pushed back to the ERP (MRP) via an API based integration to drive ordering and production planning.

Results:
Emer Horan explained how this plays out over the course of each month.   Emer provides forecasts for each of their five sales managers, they meet to compare statistical and sales forecasts, and agree on a revised 12-month consensus plan.  The sales managers have a good sense for the top accounts that represent 80% of revenue, often including direct input from customers themselves, and the statistical forecast fills in the gaps.  Next month they use the forecast vs. actual analytics to help improve accuracy, then repeat the process.

“Our sales team is incentivized to maintain and improve sales forecast accuracy,” said Emer, “and we have the tools to help them succeed.  This not only ensures optimal inventory levels but also contributes to improved on-time delivery and higher customer satisfaction.”

“Our journey with Smart Software has been quite remarkable,” added Shankar. “We began with an initial idea of the functionality and interface, and it has continually evolved from there. The Smart team has shown tremendous support and patience with our scope changes, delivering the product exactly the way we needed and wanted it.  We have been using Smart for over three years now, and this journey is ongoing. We continue to receive excellent support from the Smart team and truly enjoy working with them.”

 

 

Weathering a Demand Forecast

For some of our customers, weather has a significant influence on demand. Extreme short-term weather events like fires, droughts, hot spells, and so forth can have a significant near-term influence on demand.

There are two ways to factor weather into a demand forecast: indirectly and directly. The indirect route is easier using the scenario-based approach of Smart Demand Planner. The direct approach requires a tailored special project requiring additional data and hand-crafted modeling.

Indirect Accounting for Weather

The standard model built into Smart Demand Planner (SDP) accommodates weather effects in four ways:

  1. If the world is steadily getting warmer/colder/drier/wetter in ways that impact your sales, SDP detects these trends automatically and incorporates them into the demand scenarios it generates.
  2. If your business has a regular rhythm in which certain days of the week or certain months of the year have consistently higher or lower-than-average demand, SDP also automatically detects this seasonality and incorporates it into its demand scenarios.
  3. Often it is the cussed randomness of weather that interferes with forecast accuracy. We often refer to this effect as “noise”. Noise is a catch-all term that incorporates all kinds of random trouble. Besides weather, a geopolitical flareup, the surprise failure of a regional bank, or a ship getting stuck in the Suez Canal can and have added surprises to product demand. SDP assesses the volatility of demand and reproduces it in its demand scenarios.
  4. Management overrides. Most of the time, customers let SDP churn away to automatically generate tens of thousands of demand scenarios. But if users feel the need to touch up specific forecasts using their insider knowledge, SDP can make that happen through management overrides.

Direct Accounting for Weather

Sometimes a user will be able to articulate subject matter expertise linking factors outside their company (such as interest rates or raw materials costs or technology trends) to their own aggregate sales. In these situations, Smart Software can arrange for one-off special projects that provide alternative (“causal”) models to supplement our standard statistical forecasting models. Contact your Smart Software representative to discuss a possible causal modeling project.

Meanwhile, don’t forget your umbrella.

 

 

 

Extend Epicor BisTrack with Smart IP&O’s Dynamic Reorder Point Planning & Forecasting

In this article, we will review the “suggested orders” functionality in Epicor BisTrack, explain its limitations, and summarize how Smart Inventory Planning & Optimization (Smart IP&O) can help reduce inventory & minimize stock-outs by accurately assessing the tradeoffs between stockout risks and inventory costs.

Automating Replenishment in Epicor BisTrack
Epicor BisTrack’s “Suggested Ordering” can manage replenishment by suggesting what to order and when via reorder point-based policies such as min-max and/or manually specified weeks of supply. BisTrack contains some basic functionality to compute these parameters based on average usage or sales, supplier lead time, and/or user-defined seasonal adjustments. Alternatively, reorder points can be specified completely manually. BisTrack will then present the user with a list of suggested orders by reconciling incoming supply, current on hand, outgoing demand, and stocking policies.

How Epicor BisTrack “Suggested Ordering” Works
To get a list of suggested orders, users specify the methods behind the suggestions, including locations for which to place orders and how to determine the inventory policies that govern when a suggestion is made and in what quantity.

Extend Epicor BisTrack Planning and Forecasting

First, the “method” field is specified from the following options to determine what kind of suggestion is generated and for which location(s):

Purchase – Generate purchase order recommendations.

  1. Centralized for all branches – Generates suggestions for a single location that buys for all other locations.
  2. By individual branch – Generates suggestions for multiple locations (vendors would ship directly to each branch).
  3. By source branch – Generates suggestions for a source branch that will transfer material to branches that it services (“hub and spoke”).
  4. Individual branches with transfers – Generates suggestions for an individual branch that will transfer material to branches that it services (“hub and spoke”, where the “hub” does not need to be a source branch).

Manufacture – Generate work order suggestions for manufactured goods.

  1. By manufacture branch.
  2. By individual branch.

Transfer from source branch – Generate transfer suggestions from a given branch to other branches.

Extend Epicor BisTrack Planning and Forecasting 2222

Next, the “suggest order to” is specified from the following options:

  1. Minimum – Suggests orders “up to” the minimum on hand quantity (“min”). For any item where supply is less than the min, BisTrack will suggest an order suggestion to replenish up to this quantity.
  2. Maximum when less than min – Suggests orders “up to” a maximum on-hand quantity when the minimum on-hand quantity is breached (e.g. a min-max inventory policy).
  1. Based on cover (usage) – Suggests orders based on coverage for a user-defined number of weeks of supply with respect to a specified lead time. Given internal usage as demand, BisTrack will recommend orders where supply is less than the desired coverage to cover the difference.
  1. Based on over (sales) – Suggests orders based on coverage for a user-defined number of weeks of supply with respect to a specified lead time. Given sales orders as demand, BisTrack will recommend orders where supply is less than the desired coverage to cover the difference.
  1. Maximum only – Suggests orders “up to” a maximum on-hand quantity where supply is less than this max.

Finally, if allowing BisTrack to determine the reorder thresholds, users can specify additional inventory coverage as buffer stock, lead times, how many months of historical demand to consider, and can also manually define period-by-period weighting schemes to approximate seasonality. The user will be handed a list of suggested orders based on the defined criteria. A buyer can then generate POs for suppliers with the click of a button.

Extend Epicor BisTrack Planning and Forecasting

Limitations

Rule-of-thumb Methods

While BisTrack enables organizations to generate reorder points automatically, these methods rely on simple averages that do not capture seasonality, trends, or the volatility in an item’s demand. Averages will always lag behind these patterns and are unable to pick up on trends. Consider a highly seasonal product like a snow shovel—if we take an average of Summer/Fall demand as we approach the Winter season instead of looking ahead, then the recommendations will be based on the slower periods instead of anticipating upcoming demand. Even if we consider an entire years’ worth of history or more, the recommendations will overcompensate during the slower months and underestimate the busy season without manual intervention.

Rule of thumb methods also fail when used to buffer against supply and demand variability.  For example, the average demand over the lead time might be 20 units.  However, a planner would often want to stock more than 20 units to avoid stocking out if lead times are longer than expected or demand is higher than the average.  BisTrack allows users to specify the reorder points based on multiples of the averages.  However, because the multiples don’t account for the level of predictability and variability in the demand, you’ll always overstock predictable items and understock unpredictable ones.   Read this article to learn more about why multiples of the average fail when it comes to developing the right reorder point.

Manual Entry
Speaking of seasonality referenced earlier, BisTrack does allow the user to approximate it through the use of manually entered “weights” for each period. This forces the user to have to decide what that seasonal pattern looks like—for every item. Even beyond that, the user must dictate how many extra weeks of supply to carry to buffer against stockouts, and must specify what lead time to plan around. Is 2 weeks extra supply enough? Is 3 enough? Or is that too much? There is no way to know without guessing, and what makes sense for one item might not be the right approach for all items.

Intermittent Demand
Many BisTrack customers may consider certain items “unforecastable” because of the intermittent or “lumpy” nature of their demand. In other words, items that are characterized by sporadic demand, large spikes in demand, and periods of little or no demand at all. Traditional methods—and rule-of-thumb approaches especially—won’t work for these kinds of items. For example, 2 extra weeks of supply for a highly predictable, stable item might be way too much; for an item with highly volatile demand, this same rule might not be enough. Without a reliable way to objectively assess this volatility for each item, buyers are left guessing when to buy and how much.

Reverting to Spreadsheets
The reality is most BisTrack users tend to do the bulk of their planning off-line, in Excel. Spreadsheets aren’t purpose-built for forecasting and inventory optimization. Users will often bake in user-defined rule of thumb methods that often do more harm than good.  Once calculated, users must input the information back into BisTrack manually. The time consuming nature of the process leads companies to infrequently compute their inventory policies – Many months and on occasion years go by in between mass updates leading to a “set it and forget it” reactive approach, where the only time a buyer/planner reviews inventory policy is at the time of order.  When policies are reviewed after the order point is already breached, it is too late.  When the order point is deemed too high, manual interrogation is required to review history, calculate forecasts, assess buffer positions, and to recalibrate.  The sheer volume of orders means that buyers will just release orders rather than take the painstaking time to review everything, leading to significant excess stock.  If the reorder point is too low, it’s already too late.  An expedite may now be required, driving up costs, assuming the customer doesn’t simply go elsewhere.

Epicor is Smarter
Epicor has partnered with Smart Software and offers Smart IP&O as a cross platform add-on to its ERP solutions including BisTrack, a speciality ERP for the Lumber, hardware, and building material industry.  The Smart IP&O solution comes complete with a bidirectional integration to BisTrack.  This enables Epicor customers to leverage built-for-purpose best of breed inventory optimization applications.  With Epicor Smart IP&O you can generate forecasts that capture trend and seasonality without manual configurations.  You will be able to automatically recalibrate inventory policies using field proven, cutting-edge statistical and probabilistic models that were engineered to accurately plan for intermittent demand.   Safety stocks will accurately account for demand and supply variability, business conditions, and priorities.  You can leverage service level driven planning so you have just enough stock or turn on optimization methods that prescribe the most profitable stocking policies and service levels that consider the real cost of carrying inventory. You can support commodity buys with accurate demand forecasting over longer horizons, and run “what-if” scenarios to assess alternative strategies before execution of the plan.

Smart IP&O customers routinely realize 7 figure annual returns from reduced expedites, increased sales, and less excess stock, all the while gaining a competitive edge by differentiating themselves on improved customer service. To see a recorded webinar hosted by the Epicor Users Group that profiles Smart’s Demand Planning and Inventory Optimization platform, please register here.