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,...

1 answer below »
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?
Review the grading rubric below before beginning this activity.
Project 3 grading rubric =40 points
Project Requirements Points possible for satisfactory completion of task
Points earned by student
1. The response describes the benefits of the system by
recognizing and explaining possible Decision Support System
(DSS) configurations, including the key differences and
similarities between DSS and Business Intelligence (BI)
systems.
0-10
2. The response indicates what inputs might be relevant by
comparing the unique role of the user in DSS versus
management information systems (MIS) making can be provided
in practice.
0-10
3. The response discusses the benefits and outcomes of the
switch to web-enabled tools by familiarization with DSS
application areas and applications, and understanding important,
current DSS issues.
0-10
4. The response discusses how the approach can offer lessons to
others facing similar issues by familiarization with DSS
application areas and applications, and understanding important,
current DSS issues.
0-10
5. One point will be deducted for each APA, spelling, grammar
and length errors
AS NEEDED
Answered Same DayDec 20, 2021

Solution

Robert answered on Dec 20 2021
3 Votes
Application Case – P&G Spreadsheet Model Based Decision Support System

Application Case – P&G
Spreadsheet Model Based
Decision Support System
Case Analysis

Student Name
[Pick the date]
Application Case – P&G Spreadsheet Model Based Decision Support System

2

Table of Contents

Describe the benefits of the developed inventory decision systems in use at P&G. ............................... 3
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? ............................................................................. 5
References ............................................................................................................................................... 7
Application Case – P&G Spreadsheet Model Based Decision Support System

3

Describe the benefits of the developed inventory decision systems in use at P&G.
Being a large and diversified multinational consumer products company, P&G required an
inventory decision support system that not only helps it in controlling its inventory but also
help the company in establishing a reliable safety stock levels. Following are the benefits of
the inventory decision system used in P&G:
 Educated the supply chain planners, whereby providing a quick method of setting
safety stock
 Helps in monitoring the inventory levels continuously
 Replenishments are ordered as soon as the inventory reaches the set threshold, thus
ensuring the situation of stock-outs doesn’t arise
The above benefits shows that the DSS or the decision support system is similar to the BI or
the Business intelligence tool to the extent that they both help in “making business decisions”
in an efficient and effective manner. Besides the visible similarity, the major difference
etween DSS and BI was that though BI can be shared across various applications, DSS
always remained self-contained.
What other inputs might be relevant in building inventory decision models?
Inputs for inventory decision models can be classified as...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here