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.
