by A New Approach to Production and Inventory Planning
Disruptions have become a way of life for manufacturers. But the production and inventory planning process that many companies, especially SMEs, use are not capable of handling the uncertainty. A new inexpensive method that was developed in research involving two companies can. It doesn’t have to be automated but can be.
Production and inventory-planning processes commonly used by companies, called sales & operations planning (S&OP), require predictable demand and supply to function properly. In the post-pandemic world, as uncertainty and disruptions have increased in frequency, this approach is no longer suited to the job.
Companies, including small and medium-sized enterprises (SMEs), need a better system to make decisions when there is uncertainty. In research studies that my colleagues and I conducted with two companies, we devised new data analytics methods that are up to the challenge. They can be performed in Excel but also can be automated, using SQL and Python programming tools for data management, computations, and visualization. In this article, I describe how they work.
The Current S&OP Process
The predictability of demand and supply are necessary for traditional supply-chain-planning systems to work. In these systems, a manager or a cross-functional team begins by making forecasts of demand for the upcoming weeks. Near-term forecasts are frozen and converted into targets. Production and inventory flows are then planned to meet those targets at minimum cost. Performance is measured through metrics such as “days of cover,” the number of days of demand forecast covered by inventory. For instance, if I have 100 pallets of inventory on hand and the average forecast of daily demand is four pallets, then I have 25 days of cover. If I then want to increase the days of cover to 30, I will have to order 20 more pallets.
This system works beautifully when there is limited uncertainty in demand and supply. If the forecast of demand is accurate and supply is reasonably certain, the days of cover can be controlled easily, and the company can focus on increasing cost efficiency, on-time fulfillment, and capacity utilization. Benefiting from this clockwork system, companies have been able to create efficient supply chains reliant on large-scale, global, single-source factories, ports, and shipping lines.
This system has the added advantage of simplicity. It does not require significant data-analytics capabilities because the weekly demand forecast is a single number and the inventory plan follows from the forecast. SMEs can run the entire process on Excel spreadsheets.
But even ardent supporters of the conventional S&OP process acknowledge that plans don’t always succeed. Their response to an unexpected event is to make a new plan. But this can be challenging. Consider the previous example: If average weekly demand were still four pallets but actual demand varied between one to 10 pallets, then days of cover could be 100 days or it could be 10 days, so it’s difficult to set a target and determine how much to order.
In the post-pandemic world, when uncertainty arises almost daily from any number of sources — demand and price shocks, supply disruptions, shipping delays, and labor shortages — managers need a better system that doesn’t just whipsaw in reaction to uncertainty but tells them proactively what to do and what to expect over the medium and long-term time horizons. Creating such systems requires new data analytics tools to measure uncertainty and explicitly incorporate those measures in future planning. Here is how two different companies built new models of demand uncertainty and price uncertainty to solve this problem.
A Distributor of Packaged Foods
The U.S. distributor of a multinational sweet baked goods company imports cakes and croissants from Europe for sales to retail chains in the United States. Before the pandemic, the company had a predictable lead time of about 50 days from placing an order to receiving goods in its New Jersey distribution center, including production (21 days) and ocean freight (28 days). After receiving a shipment, the company would distribute product to retail stores throughout the country. Timeliness was of the essence because shipments of perishable food products to retailers must meet a minimum shelf-life requirement.
During the pandemic, shipment lead times grew significantly longer, more uncertain, and costlier. Due to the resulting uncertainty in inflows and outflows, the days of cover in the New Jersey distribution center stopped being a reliable indicator at any point in time. Planning inventory became akin to guesswork. The result: increased stockouts, an inability to meet growth targets, higher cost of waste from perishable food products, and significant senior management time spent on managing inventory item by item.
To solve this problem, historical demand and shipment data, stored in Excel spreadsheets, was harvested and used to estimate the standard deviation of weekly demand forecast and supply lead times. This gave the company a measurement of the amount of uncertainty that it needed to incorporate in its plan. Then, days of cover was replaced by a new inventory planning metric: the target inventory position for each product — the sum of the forecast of demand over the lead time plus an inventory buffer for the standard deviations of demand and lead time.
For example, suppose the weekly demand forecast has an average value of 25 pallets and standard deviation of three pallets, the order lead time has an average value of 10 weeks with standard deviation one week, and the company desires a 98% fill rate. Then the average forecast of demand over the lead time is 25 x 10 = 250 pallets and the desired buffer is 2 x square root of (3 x 3 x 10 + 25 x 25 x 1 x1) = 2 x 27 = 54 pallets, where the multiple 2 corresponds to the 98% fill rate and the other numbers come from the average and standard deviation of demand and lead time. The resulting target inventory position is 250 + 54 = 304 pallets.
This approach was superior to days of cover because the target inventory position was computed from the lead time and uncertainties via an exact formula, whereas the target days of cover was an arbitrary number. The process was run weekly. First, data was updated, then the target inventory position was calculated in Excel, and finally order quantities were determined to make up the difference between the target and the actual inventory and checked by a manager.
This new method enabled the company to replace guesswork that required experience and judgment with a standardized, unambiguous formulaic approach that workers could execute with little training. Moreover, as new data came in weekly, the new method automatically adjusted to changes in the marketplace. If the degree of uncertainty increased, the inventory target would get revised upwards. Larger inventory buffers were built for products whose demand was more uncertain. As uncertainty increased further during the pandemic, the company found that the advance planning prepared it well for the challenge.
A Manufacturer of Durable Seasonal Goods
A medium-sized manufacturer of recreational durable goods, such as barbeque grills, swimming-pool equipment, and camping equipment, faced a different kind of challenge as its supply chain shifted. Previously, it sold its products through a network of dealers spread throughout the United States. Since the products were seasonal, with most of the demand occurring in May and June, the company required distributors to place orders six months in advance for optimal scheduling of manufacturing.
However, a few years ago, when the company’s distributors started selling online, the consumer market for the products became more competitive and prices became volatile. The distributors responded by placing fewer advance orders and more just-in-time orders during the selling season. This upended the company’s S&OP process: Inventory build-ups and ramp-downs could not be planned in advance, and past sales and cash flows stopped being a predictor of the future.
The novelty of this situation was that it arose from a new variable: downstream price uncertainty, which the company had previously not considered. We used daily prices from the online marketplace to measure price uncertainty and the company’s historical orders data to measure the timing of orders for each product. The data told us that products with more price uncertainty received more just-in-time orders. This gave the company a method to predict when distributors would place orders as a function of price and demand uncertainty. Thus, the company was able to obtain more informed internal guidance as well as be more effective in its negotiations with distributors. In particular, it could give higher discounts for early orders for products with large price uncertainty to incentivize distributors to order early, which helped reduce inventory risk.
In both examples, the companies focused on measuring uncertainty and directly including it in supply-chain planning. This enabled them to easily solve problems that previously seemed unmanageable. And the beauty of the approach is that it was inexpensive and fast to create. It took only a few months and could be implemented in Excel, which made it easy to use.
As the number of products and customers grows, a company should consider automating the analytics in a programming language, such as Python, to reduce the chances of human errors and improve productivity. A good supply-chain engineer with knowledge of programming can create these tools in a few months. Typically, this would require converting all the manual data management tasks from Excel sheets into database management rules in SQL, implementing the computation of target inventory position in Python, and building visualization dashboards in Python, Tableau, PowerBI, or an equivalent tool for performance monitoring and reporting.
An SME organization should automate one step at a time, starting with building a database and visualization dashboards, then creating software for estimating demand, price, and lead time uncertainty, and finally computing the target inventory position. This approach offers SMEs a fairly inexpensive way to manage their inventory and production processes in uncertain times.