Home | Business News | Browse by Publication | I | Interfaces

Spreadsheet models for inventory target setting at Procter & Gamble.

Article Excerpt
Spreadsheet models have been instrumental in helping Procter & Gamble (P&G) to set inventory targets. The Global Analytics group of P&G's Business Analysis Solutions organization, an in-house modeling, decision-support, and consultancy group, created global inventory models. The models determine the best inventory levels and yield the required customer-service levels, subject to the characteristics and constraints of a particular supply chain. These spreadsheet models are easy to use and share. Therefore, they have become the standard tool for setting inventory targets at P&G; hundreds of supply chain planners worldwide now use them. They have contributed to inventory reductions of over $350 million and significant intangible benefits.

Key words: information systems: decision-support systems; inventory/production: applications. History: This paper was refereed.

**********

The need for "scientific" models for setting inventory targets at Procter & Gamble (P&G) arose in the mid 1980s when the company embarked on implementations of Distribution Requirements Planning (DRP) in various parts of the world. P&G business units needed an easy and consistent way to set safety stock levels at item and location levels.

To address this need, P&G's Western European Business Analysis group created a spreadsheet model that eventually grew into a suite of global inventory models. It developed the original model using Lotus 1-2-3 (release 2.01), which was then the de facto standard at P&G. Judged by today's standards, the original model was primitive: it required that supply chains be decomposed lane-by-lane; it lacked built-in functions for the Normal distribution (let alone anything as exotic as a gamma distribution!). Users had to rely on lookup tables, iterative calculations, and approximations. Our model had two objectives: to educate supply chain planners on the various types, roles, and root causes of inventories in supply chains, and to provide a quick method for setting safety stocks within a DRP framework.

The model has exceeded its design goals by far:

* It has grown from a European initiative into a truly global inventory model;

* Hundreds of supply chain planners actively use it;

* It includes a well-documented work process;

* A central support group is available to assist users;

* The central support group, as well as the business units, provide training;

* It incorporates a formal release process.

P&G developed several spin-off models based on this model; these include: a Raw and Packing Materials Inventory Model (MIM), an Extended Inventory Model (XIM) capable of modeling more complex distribution networks, and a Retailer Inventory Model (RIM) that quantifies inventories up to the level of store shelves. The models in this family share a common terminology and are based on a common function library that extends Excel's set of statistical functions with user-defined functions (UDFs) that are specific to inventory management, and are written using Visual Basic for Applications (VBA).

This paper is organized as follows: The Inventory Modeling section describes the inventory modeling functionality. The Spreadsheet Modeling section illustrates the extended functionality in the models--they are more than "yet another spreadsheet." Work Process and Deployment describes the integration of the models into supply chain planners' work processes. Training discusses training the user base. Results reviews the business results. We evaluate the strengths and weaknesses of our approach and provide general insights in the Spreadsheet Models: Evaluation and Insights section. Finally, we provide a Conclusion section.

Inventory Modeling

Our global inventory models quantify the inventory components as textbooks (e.g., Silver et al. 1998) describe them. These components include cycle stocks, safety stocks, frozen stocks (e.g., pipeline or transit inventory), and anticipation stocks. Cycle stocks result from items produced or procured in batches, instead of one by one. Safety stocks provide protection against uncertainty in demand and supply over the replenishment lead time. Cycle and safety stock are generally more actionable components of inventory; therefore, the model emphasizes quantifying these components and identifying their main drivers. Frozen stocks are a straightforward translation of supply chain lead times and process constraints, e.g., production lots awaiting clearance from quality inspection. Anticipation stocks, which cover periods where demand peaks exceed available production capacity, are generally managed separately.

Textbook formulas provide a highly stylized view of the world. To be practical and relevant, the formula components must be translated into language that business understands, e.g., "lot sizes" become "production run lengths" or "order quantities." Moreover, textbook formulas tend to ignore real-world issues such as shipping calendars or forecast bias. Tiwari and Gavirneni (2007) describe the "disconnect" between inventory theory and practice. Many supply chain planners have only a basic knowledge of statistics; they do not want to understand the mathematical intricacies of a set of formulas--they only want to trust the results of the calculations.

Most of our models implement some form of continuous-review (s, nQ) policy: whenever the inventory position falls below a reorder point s, order a quantity Q or a multiple of Q. The customer-service target is defined as a fill rate (percentage of demand supplied from inventory); Silver et al. (1998) refer to this as [P.sub.2].

Functionality in the models includes the following:

* Support of normal and gamma distributions for demands or forecast errors;

* Support of a two-tier distribution network: customers receive replenishments directly from the plant or through a local distribution center;

* Pull and push policies (some plants "push" production to the distribution centers, in advance of demand; other plants require the distribution centers to "pull" products from the plants, as needed);

* Integration of forecast bias in the safety stock calculation (through a UDF);

* Automatic pooling of demands across shipping points;

* Replenishment intervals (shipping calendar) to effectively address replenishments across...

View this article FREE - Now for a Limited Time, try Goliath Business News
Free for 3 Days!