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.
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?
We value our customers and so we ensure that what we do is 100% original..
With us you are guaranteed of quality work done by our qualified experts.Your information and everything that you do with us is kept completely confidential.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
The Product ordered is guaranteed to be original. Orders are checked by the most advanced anti-plagiarism software in the market to assure that the Product is 100% original. The Company has a zero tolerance policy for plagiarism.Read more
The Free Revision policy is a courtesy service that the Company provides to help ensure Customer’s total satisfaction with the completed Order. To receive free revision the Company requires that the Customer provide the request within fourteen (14) days from the first completion date and within a period of thirty (30) days for dissertations.Read more
The Company is committed to protect the privacy of the Customer and it will never resell or share any of Customer’s personal information, including credit card data, with any third party. All the online transactions are processed through the secure and reliable online payment systems.Read more
By placing an order with us, you agree to the service we provide. We will endear to do all that it takes to deliver a comprehensive paper as per your requirements. We also count on your cooperation to ensure that we deliver on this mandate.Read more