There has arguably never been a more volatile time in supply chain history than today. A global pandemic coupled with political tensions and rising costs has resulted in a nearly unpredictable imbalance of supply and demand.
Global logistics providers are strained from a staffing perspective and are also faced with challenges at every port and terminal. Suppliers are being squeezed by raw material shortages and costlier packaging.
When disruptions to supply and demand occur, supply chain modelers and planners go to work to retune the forecast and rebalance product available with customer orders. But how quickly can the numbers be re-crunched and coordinated, particularly across multi-regional networks or transmodal routes?
Many supply, demand, capacity, and production (S&OP) planners rely on a series of spreadsheets to do the work. Often, planners will have refined spreadsheet models that have been improved and expanded over time, and have been passed from planner to planner as the organization grows. In many cases, these models include highly-tuned forecast calculations and segmented sheets by SKU, region, warehouse, or other dimensions of the business. Some even have nearly real-time connectivity to the ERP or MRP system, so planners can quickly refresh their boards to see the current inventory levels or lead times projected for next month’s shipments. These models are iterated with each supply chain crises or disruption to, ideally, shorten the time it takes to redo the plan the next time an unexpected event strikes.
As a former supply planner, here are three critical challenges with maintaining spreadsheet models in periods of high uncertainly and volatility:
- Lack of transparency / readability within the model. When new planners are onboarded and inherit former planners’ spreadsheets, there is very little time to focus on learning the details of how the model operates. In businesses where the S&OP cycle is executed monthly, planners often must devote their time to fighting (hopefully not literal) fires and understanding the business, rather than studying formulas and macros in a spreadsheet they’re expected to use daily to drive the organization’s supply accuracy and service level performance. It can be a struggle to learn how to use the model correctly, and just a small mistake could mean incorrectly forecasting the required product – in extreme cases, resulting in a customer stockout or a warehouse capacity breach.
- Fragility when changes occur. When a new SKU, plant code, or packaging configuration is created at the MRP level, ensuring the change is properly and fully incorporated into the planning model is a tedious task. Leaving off the new SKU in just one location in the model could skew the plan or result in a less-than-optimal suggested production volume. In other cases, incorrectly mapping a packaging configuration could also have far-reaching implications with trade regulations or logistics provider costs, if order details are not properly communicated to service providers downstream of production. For models that rely on add-ins or integrations, a new change in the ERP system could result in VBA update code crashing or corrupting a model file (believe me, it’s the worst). And while it is great that more “mature” models contain better sheets to represent all sorts of potential scenarios and perspectives, these models are massive in size and are prone to crashing at just the moment you need them not to (again, believe me, it’s the worst).
- “We need a new plan.” When an unexpected crisis strikes in a global supply chain network, it often means getting everyone on the Zoom call to problem solve together. The Asia, Europe, and Americas supply planners jump on a call, each equipped with their own version of a spreadsheet model. The production planners or manufacturing counterparts come with (you guessed it) their own spreadsheets too. We need a new plan – one that syncs across regions, operates within the constraints of warehouse and terminal throughput, and maintains profitable operations at each of our global production facilities. How do we do it? With half a dozen people on a call at all hours of the day and night thanks to time zones, we are faced with the insurmountable task of somehow harmonizing all of our spreadsheets together to find the solution. Stakes are high, and by the time our team has worked together to pull the numbers, it’s likely the circumstances have changed yet again.
Have you been here before? As supply chain professionals, it’s all too easy to dive into a vicious cycle of late night “quick response team” or “war room” calls to resketch the supply-demand balance, only to determine early the next morning that the plans have changed again. Constantly chasing uncertainty with cobbled together spreadsheets (even when we’ve done immense work to streamline and fail-proof them) is a battle lost. If not lost quantitatively, then surely, emotionally – supply chain professionals are among the smartest, most resilient folks I’ve ever worked with, but even the strongest planners burn out after months of constant fire fighting with tools that fail to provide fast enough insights.
We certainly can’t control the volatility in the world, so what can be done to make the planning process better, for the sake of both our organizations and our supply chain professionals?
The need to combine existing business structure and plans with “clean-sheet” brainstorming is the missing piece in an organization that relies on disparate spreadsheets. Given the 3 challenges described above, it’s incredibly difficult to take a highly-tuned, VBA-laced S&OP spreadsheet model and create a new scenario to cover, let’s say, a worker strike at a production facility. Quickly transforming the existing plan and inventory to reflect the sudden unavailability of massive amounts of product is not possible, particularly when inventory is being fed into the model by the ERP system and cannot easily be changed. In fact, ERP and MRP systems are mostly incapable of incorporating “imaginary” scenarios (such as strikes) into the platform in order to answer common “what if?” questions that planners have.
We need a way to take the existing SKUs, networks, plants, and demand, and overlay our human context of the situation on top of the model. This means not only assessing the situation operationally, but financially as well – making these strategic decisions requires visibility into our end-to-end supply chain costs and revenues. Instead of opening a blank spreadsheet file and starting from scratch, we need a way for all 6 planners to work within one model simultaneously, adding their respective inventory constraints, incoming sales orders, and suggested rebalances. As the situation changes daily or even hourly, each planner should be able to enter the model and add a new scenario for lead time delay, or a new assumption for how much inventory could be transferred from one plant to another to make up for lost production and reduce holding costs. Once the newest version of the plan is optimized, it should be able to be sent directly back to the ERP and MRP systems for execution.
If you or your S&OP organization are caught in this relentless planning cycle, a professional financial modeling solution can unleash flexibility and agility like never before. That’s where Quantrix comes in.
- Load in your organization’s supply/demand/production/business structure to ensure every SKU, warehouse, minimum order quantity, transportation lane, cost, etc. is included in the model
- Receive real-time inventory and order updates from your ERP, MRP and other systems
- Write formulas using real language (such as product names, months, goods in-transit) rather than cryptic cell coordinates
- Create new scenarios and hypothetical cases on the fly with one-click (with automatic formula and business logic extension)
- Overlay financial data with operational plans to see a holistic view of your organization and make informed strategic decisions
- Maintain a single source of truth with one model and multi-user collaboration in real time
- Preserve security with granular user and user group permissions to ensure planners (and sales, manufacturing, procurement, etc.) only have access to the portions of the model they need
- Deploy the model online to share with stakeholders and quickly gather buy-in (and make new scenarios directly from presentation mode without having to edit formulas)
As a former supply chain modeler, I would love to discuss with you how our financial modeling software can reduce the time your planners spend fixing spreadsheets and battling corrupted macros or add-ins to create new forecasts. With Quantrix, S&OP professionals can have greater confidence when the next disruption occurs and someone says, “We need a new plan.”
Lyndsey Weber
Senior Solutions Consultant
lweber@quantrix.com
SUPPLY CHAIN CASE STUDIES:
 |  |  |
