Reference no: EM131733535

Project Instructions:

Read and complete the following Application Case.

Spreadsheet Model-Based Decision Support for Inventory Target Setting at Procter & Gamble.

Procter & Gamble (P&G) is a large, diversified, multinational consumer products company. Its modeling and decision support group was asked to create global inventory models. P&G has needed "scientific" models to help it control its inventory since the mid-1980s when it implemented Distribution Requirements Planning (DRP). P&G needed an easy way to establish reliable safety stock levels at both the item and location levels. This safety stock is needed to allow for uncertainty in production during the time when replenishments would be delivered.

The original solution was created using Lotus 1-2-3, but over the years the company has developed a suite of global inventory models using Excel. When developing the spreadsheet models, the company kept two goals in mind: "educate supply chain planners on various types, roles and root causes of inventories in supply chains and provide a quick method for setting safety stock within a DRP framework." The latest model, in addition to growing into a global inventory model, also provides a mechanism for the central support group to train users and assist those who have questions. The inventory components in the models include: cycle stocks, safety stocks, frozen stocks, and anticipation stocks.

Most of P&G's models use a continuous review policy. Continuous review policy, as the name implies, means that inventory levels are monitored continually. When inventory goes below a set order point, the company reorders up to a set amount using and order quantity (number of items per unit) or a multiple of the order faces. Examples of these issues in the models include:

1. Modeling of normal and gamma distributions for demands or forecast errors.

2. Recognition of a two-tier distribution network: customers receive replenishments directly from the plant or through a local distribution center.

3. Pull and push policies.

4. Integration of forecast bias in the safety stock calculation.

5. Automatic pooling of demands across shipping points.

6. Replenishment intervals (shipping calendar) to effectively address replenishments across many items.

The modelers at P&G employed Monte Carol simulations in spreadsheets to evaluate different inventory policies by analyzing the policy's impact on the customer-service levels. These model simulations enable decision makers to identify the best inventory setting policies.

Over the years, P&G has made numerous improvements to its spreadsheet models and has released 10 versions in 20 years. Some of the improvements to the models include separating the various types of data, such as input, calculations, and results, by grouping and formatting differently; putting all pertinent data on the same screen; using color coding and highlights to designate both mandatory and optional fields; drawing attention to obvious mistakes, such as negative numbers where a negative is impossible or abnormally high or low numbers from what is expected in that field; and using fewer graphs, and then only when they make understanding the result easier. Additionally, the company made an improvement using a safety factor that is automatically calculated to determine how many standard deviations of demand are kept as safety stock to ensure the target fill rate. Previous versions of the models required time-consuming manual entry of safety factors that had to be looked up, which limited flexibility and accuracy. Computation of the safety factors uses parameters such as target fill rate, reaction times, lot size, forecast error, and type of probability distribution. Using these factors, the function uses a binary search to automatically calculate the safety factor. The system is utilized by hundreds of supply chain planners, incorporates well-documented work processes, and integrates a formal release process.

Success of this DSS has resulted in P&G developing other related systems, such as Raw and Packaging Materials Inventory Model; and Extended Inventory Model, which is able to model more intricate distribution networks; and a Retailer Inventory Model, which can calculate inventory at the store shelves level. These models use common terminology and are built using a common function library that extends the statistical functions in Excel with user-defined inventory management functions that are written in Visual Basic for Applications.

An interesting system development issue is that the company uses very few macros. Users are located all over the world and speak different languages and own various computer systems, and macros do not necessarily translate from one computer system to another very well.

Finally, the company upgrades is systems every 18 to 24 months and announces the upgrades via the P&G intranet site. Users can do self-training on the upgrades through the computer or attend training seminars in person. User manuals are provided with the upgrades.

This case demonstrates a decision support model that can be developed using commercially available tools. Of course, significant expertise is needed to develop the underlying mathematical models. The case also touches upon the need for systems developers to be aware of the unique needs of a global, diversified company in terms of diversity of languages, systems in use, and so on.

Project requirements:

Answer the following questions and save your responses in a Microsoft Word document.

1. Describe the benefits of the developed inventory decision systems in use at P&G.

2. What other inputs might be relevant in building inventory decision models?

3. Would it be better for this DSS to be available as a Web-based DSS?

4. What lessons can you learn from studying this case?