A Look at the Use of Spreadsheets in Demand Planning.
For any business, demand planning (a.k.a. forecasting) can be a double-edged sword. Done well, it enhances our power to profit. Done poorly, it saps our ability to compete. Every company feels the need to accurately anticipate demand trends and fluctuations. Good anticipation can result in increased revenue and happy customers, as well as better aligned inventory and lower costs. On the flip side, when we over- or underproduce, we skew our operating expenses to the detriment of the business and our customers.
In theory, the planning goal is a simple two-step process:
- Predict what we’re likely to need at a point in time beyond the lead periods of our products.
- Estimate everything we’ll have to create and stage to meet that need.
As the saying goes, it’s easier said than done. Most organizations find they can only get so far given their current systems, resources and processes. There soon comes a time to consider the options for more capable forecasting to enhance top-line competitiveness and bottom-line results.
Foundation.
Traditionally forecasts are shaped by the past experiences of the organization. Forecasters often take either a top-down or bottom-up approach, rarely breaking from either of these two approaches. Top-down forecasting starts with projecting aggregate sales and working down to contributions of individual items. Bottom-up forecasting works the other way, building up from product-level data such as price and sales volume, conversion rates, available resources and capacity, and more.
The demand plan uses available historical data to identify past trends that we think are pertinent to shaping future demand. If seasonal patterns are clear in the historical sales numbers and we’re certain that the past is destined to repeat itself exactly, we could simply reuse the numbers from last year. But virtually no business situation is that static or stable. Better to at least average multiple previous sales periods together to flatten out the “noise” (ups and downs) in our historical data set and look for trends over time. A technique called “regression analysis” uses a statistical algorithm to project what our future demand will look like given our known historical information. This projection can be a bottom-up calculation based on low-level data such as individual SKUs or a top-down technique using aggregate sales, economic growth, and other high-level info.
This foundational demand planning technique is performed in many software solutions, from commercial forecasting apps to modules in ERP systems – and, all too commonly, spreadsheets.
Familiarity.
The most common choice for lightweight demand planning is a purpose-configured spreadsheet set built in Google Sheets or the ubiquitous Microsoft Excel. Excel is by far the most common choice for fast, low-cost, and agile formula calculation. Familiarity with Excel is universal. Given its user-friendly interface, abundant video tutorials and problem-solving forums online, it’s a natural starting point. Entering data is simple. Information is organized into tables that can be displayed through a multitude of graphs and chart types. Almost everyone in business has some experience with its formulas and functions, making it an intoxicatingly quick, easy-to-learn platform for one-off calculations.
But there is a problem. Simply put, a table processor can’t handle the, er, demands of demand planning. Spreadsheet statistical processes have limited breadth and depth. Built-in forecast functions do simple time-series analysis to base predictions on patterns present in our existing data. As discussed above, it estimates future data values from our previous values, tailoring the prediction by a technique called exponential smoothing. Smoothing gives more weight to more recent data points over older ones. It assumes that newer values are better because of their greater timeliness, thus should be more relevant to the prediction.
On the surface, this may sound okay; however, you need more than this math processing engine can provide when your operations must be performed repeatedly, or you have interactions with multiple people. The challenges compound when we consider the importance of data consistency, formula integrity, and secure collaboration, none of which are sufficiently supported. Now add, aspects such as integration with external data sources and generation of insightful reports based on user roles and you quickly realize the limitations of this approach.
Hindered.
As these significant shortcomings appear over time, the attractiveness of simplicity and familiarity falls away. Planners can begin to feel impeded by the significant drawbacks and issues plaguing their homegrown solutions.
Still, some may feel skeptical or intimidated at the thought of leaving them behind.
Let’s say we’re a bit worried that moving beyond DIY spreadsheets may turn out to be an onerous journey. It’s best to remind ourselves of exactly what we will gain, and why, so we can embrace a more powerful and reliable forecasting process enthusiastically. If we take a realistic look at what we stand to gain, we’ll soon be raring to go.
Road to Success Says “Buh-Bye” to Spreadsheets of the Past
Here’s a quick roundup of some of the compelling reasons we should stop forecasting based on homegrown spreadsheets and gut instincts.
- Minimize errors. This is the big, easy-to-identify Achilles Heel of demand planning in a spreadsheet. Studies going back more than a decade have found that spreadsheets are riddled with errors. The consensus has been that about 50% of spreadsheets used operationally by businesses have material defects. And, according to The European Spreadsheet Risks Interest Group, more than 90% of spreadsheets in business use contain errors. The majority are introduced by users – for instance, copying values into cells that were supposed to contain formulas. Because spreadsheets are rarely tested, some errors are discovered after months of inaccurate results. Or maybe, never. Relying on manual processes to manipulate data means it’s easy to paste values into the wrong cells and break the logic, but human error can creep into the process in other ways, such as data import corruptions, value transpositions, even cell formatting errors. Adopting an automated tool can dramatically reduce the possibility of such errors.
- Remove Bias. All our years of real-world experience give us a big advantage in forecasting, right? Right? Not so much. Successful demand planning is often very counter-intuitive. Our backgrounds give us a personal professional bias. These assumptions can lead us in the exact wrong direction at the exact wrong time. In fact, given the heightened instability in everything from global supply chains to local labor pools, general rules of thumb work even less well today than they used to. Only data and the power of statistical methods (more on those later) can find the best path forward. This has been proven again and again across industries and regions by small companies and large, multinational organizations. A neutral, data-driven approach to demand planning is much less prone to bias and error, and that can turbocharge our operating performance and competitive advantage.
- Foster Teamwork. Collaboration is essential to deliver the end-to-end improvements we seek. Spreadsheets and similar tools have minimal collaboration capabilities and force organizations to rely on one main “power user” – one author – for maintenance work, updates and capability improvements, and quality assurance. This key individual understands every cell of the spreadsheet, the formulas in use, and how each interim calculation feeds the logical processes. This situation creates a bottleneck, yes, but more than that, it is a mission critical single point of failure that can leave us in the lurch if our Excel expert becomes unavailable…or leaves.
Beyond the “one guru” pitfall, look at all the planners and stakeholders that may need to participate in the demand planning process. We may have individual planners for specific product categories, geographies, or market segments. Each requires easy collaboration among key players inside and outside our organization, such as suppliers and vendors. We need teamwork to create complete demand forecasts, and that calls for a powerful collaborative solution. Now, let’s say we try to share a spreadsheet. The more people we allow into the spreadsheet, the more trouble we create. If we allow information to be replicated in duplicate spreadsheets owned by several people, whenever a record is altered in one sheet, the other sheets must be updated manually, endangering both data integrity and consistency. - Make it effortless. Labor and resource management can quickly become a burden when dealing with spreadsheets. They are laborious; manually collecting and entering data into tables is an arduous activity, along with syncing and updating. It is critical to make the process as frictionless as possible through robust integrations with internal and external sources of a multitude of data such as sales and inventory data. Standard integrations with data sources for both structured and unstructured data, whether inside and outside the enterprise, including even real-time IoT data (e.g., vehicle sensors, inventory storage), as well ERP, CRM, and other systems significantly simplifies the process for planners it also makes the plan exponentially more reliable.
- Accuracy. Demand forecasting is a discipline that requires statistics calculations to generate useful predictions. Statistical techniques are nonintuitive to many. There are several algorithms and methodologies that can be used to derive the best possible forecast, depending on available data and the specific type of demand behavior our products tend to encounter. It’s impossible to determine which one of many different models and algorithms produce the most accurate forecast – or when we should switch from one model to another as conditions evolve.
To achieve the greatest accuracy, we need to move beyond a DIY approach. You need a data-driven approach to help automate routine processes, freeing up resources to be more strategic – to focus on achieving business objectives versus firefighting unforeseen challenges. These systems utilize AI and machine learning to sense changes, deliver recommendations, and automatically select from among the possible algorithmic approaches to create a data-driven optimized forecast free from bias in real time.
It is also important for us to know just how much wiggle room there is in the forecast. To what degree might future results deviate from our forecast? For this we need measure the confidence level of a forecast, a useful indication of the likelihood that our predictions will be correct or off by a certain degree. That helps us set our expectations correctly and informs our business decisions.
90% of spreadsheets in business contain errors - The European Spreadsheet Risks Interest Group
How to Know You’re Ready
Homegrown spreadsheets may be adequate for foundational demand planning when the number of SKUs is limited, and the demand signal is fairly steady. But as your business evolves you are confronted with opportunities for expansion, more new product launches, acquisitions and partnerships, added sales regions and markets. One thing is certain: at some point, the scale will hit a point where your systems will be inadequate to handle the volume of data, increased user scope, and complexity of calculation the demand planning effort will require.
Questions.
One way to judge whether you are about to outstrip the capabilities of homegrown spreadsheets is to ask questions like these:
- Does the team seem to always be in firefighting mode?
- Do my key stakeholders question whose data is right and who should drive the plan?
- Are we struggling to meet service level commitments due to a lack of visibility and understanding of demand drivers?
- Do unexpected events occur that we don’t understand or know how to respond to?
- Has someone on the team said, “We should have planned better for that to happen.”?
- Are you able to quickly derive insights from real- or near real-time data sources (e.g. IoT sensors, syndicated sources such as weather or housing starts, POS data, or other internal and external enterprise systems)?
A Good Start.
The good news is that the transition to an automated, data-driven forecasting platform can be easy and smooth. Today, planners in companies of any size and maturity level can successfully leave spreadsheets behind and reap the rewards of more sophisticated forecasting and demand planning.
The key is to start with what we’ve been doing right all along…but do it in a better way. Most forecasting efforts start with a “naïve” forecast that is simple to calculate based on average activity over recent periods or saved values from the prior year. We may already be tracking the accuracy of our predictions against the observed real-world demand and calculating the percentage by which our forecast varied from the actual.
With a supply chain planning solution such as the Atlas Planning Platform, we can easily import our data from any source systems within or outside our enterprise landscape. A few more clicks and we can set permissions for everyone who needs to interact with the demand planning process so that no one can inadvertently disrupt the integrity of our data or our calculations. The AI-powered, data-driven engine will automatically select and optimize the best forecast methods to remove bias, improve service and account for variances in the plan to give us the most accurate predictions – forecasts we can share with confidence and use as a collaboration mechanism to pull all our stakeholders together around our “best single version of the truth.” The best part of this, you don’t need a team of data scientists to take advantage of the power AI delivers to supply chain. Instead, the AI is there, quietly driving a better plan, a better process, making you look better.
Now, we can configure our forecasts to address any level of aggregation, whether top-down, middle-out, or bottom-up, and effortlessly output at any forecasting horizon we desire, from yearly to quarterly, monthly, weekly, or daily. The demand planning platform offers intuitive online dashboards with a range of reports-on-demand tuned to various users’ needs so they can easily understand and confidently take action.
Change the Game.
Once we have pulled all our people and predictions together in a single planning environment, we can start to add capabilities at our own pace to continuously improve our planning expertise. Here are just three of the many advanced capabilities awaiting us:
- Forecastability. Analyzing and characterizing our products using data to quantify their relative value and impact on our business as well as our ability to predict their behavior. Forecastability analysis helps us hone in on the key demand planning drivers that impact our business goals.
- Scenario Analysis. Initiatives like new product launches and marketing promotions present both opportunities and risks. Before we decide how to invest our budget and our effort, it is hugely helpful to run through multiple demand scenarios that deliver insight into the range of possible outcomes. Performing complex “what-if” analyses in spreadsheets is virtually impossible. It is too complicated and impractical to incorporate factors such as seasonality, inventory mix, competitor pricing, economic indicators, and others. This is where the power of a dedicated planning platform really shines, building and performing “what-if” scenarios for multiple cases very quickly and then recommending the actions to take or, based on your readiness, automating the processes of your choosing.
- Demand Sensing. We have data on sales from previous periods, be they months, quarters, or years. But conditions have changed since then. Our forecast may benefit greatly if we incorporate recently captured information about markets, events, indexes, weather, buying patterns, and more. The Atlas Planning Platform lets us apply such statistically meaningful information to improve our forecast accuracy.
Alright, Already, It’s Time to Move On
The bottom line is it is almost never a good time to “stick with our spreadsheets”. Why? Because trying to do demand planning in a homegrown environment is sure to cost us in terms of missed revenue, excess inventory, wasted manual labor, and more tenuous/timid business decision making.
With the Atlas Planning Platform, all stakeholders can easily and intuitively interact and stay up to date with intelligent alerts, notifications, and dashboards. Data and user access are managed securely. Information from other systems are imported, validated, and cleansed automatically. Machine learning makes the most of causal data such as socio-economic, real-time IoT and weather, social listening, e-commerce, and more. Task flows can be easily automated to improve team productivity. Data is automatically updated using machine learning to intelligently remove outliers and adjust for trends. Forecasting is guaranteed to be state-of-the-art, using field-proven methods honed over years of research and thousands of real-world customers. Instead of an internal spreadsheet programmer, Atlas uses award-winning AI-driven science to examine any type of data available and determine best-fit algorithms that will produce optimal forecast accuracy.
That’s why, no matter what your current situation or level of demand planning maturity, it can be said with confidence that the time has come to go beyond homegrown spreadsheets. A world of better business performance and enhanced competitive advantage lies straight ahead.
Continue Learning.
If you’re ready to continue your exploration, download our eBook, Guide to Evolve Your Demand Planning Maturity. In this paper, we outline the challenges, steps and benefits of evolving your demand planning process across three key stages from where you are today (Start) to a medium advanced level where you are driving measurable benefits (Evolve) to a highly advanced, mature demand planning process that employs the latest advances in supply chain planning technologies to automate and drive greater value.