Explore BrainMass

Procter & Gamble Inventory Management System

APA Style- # references, 4-6 pages and brief discussion of case, answer below questions end with discussion and summary.

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 demand as well as the 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 an order quantity (number of items per unit) or a multiple of the order quantity. The models have been developed to accommodate the demand and production situation that P&G 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 Carlo simulations (which we learn a bit more about in the next chapter) 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 results 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 factor uses parameters such as a 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 a Raw and Packing Materials Inventory Model; an 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 functions from 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 its 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 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.

Questions for the Case

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?

Source: Based on I. Farasyn, K. Perkoz, and W. Van de Velde, "Spreadsheet Models for Inventory Target Setting at Procter and Gamble," Interfaces, Vol. 38, No. 4, July/August 2008, pp. 241-250.

Solution Preview

Essay on the Analysis of the Spreadsheet Model-Based Decision Support for Inventory Target Setting at Procter & Gamble
To be able to provide a complete tutorial on how each of the four questions should be addressed, I used the following resources arranged alphabetically:
1. Dunn, D. & Thomas, C. (1994). Partnering with customers. The Journal of Business & Industrial Marketing, 9(1), 34.
2. Farasyn, I., Perkoz, K. & Van de Velde, W. (2008). Spreadsheet Models for Inventory Target Setting at Procter and Gamble. Interfaces, 38(4), 241-250.
3. Hannon, D. (2009). Purchasing drives deeper into logistics. Purchasing, 138(7), 76.
If you want a copy of the above files for your review, please let me know and I will upload them. All these materials were accessed through the ProQuest academic database.
This question is actually intuitive meaning there is no need to read beyond the information originally provided to write a well-crafted answer to the question.
One of the basic concepts in inventory management, as thought in business schools, is the importance of inventory management. Why? First, companies earn money by selling their products to their customers and supplying these customers the right products at the right time and quantity is important. Second, a lot of the company's assets are tied up in inventory and while these assets are in inventory, they don't earn the money anything. As a matter of fact, the more resources are tied in inventory, the more costly it will be for the company dues to storage and obsolescence costs among other costs associated to inventory.
However, this question is about the benefits of developing a distribution requirements planning system in-house compared to buying an off-the-shelf program which can also be customized for the company's ...

Solution Summary

The tutorial provides for suggestions and guidelines on how the following questions should be answered:
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?