Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


Spreadsheet Models for Inventory Control

by Andrew Leung and Wei Guo

Andrew Leung

on 14 January 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Spreadsheet Models for Inventory Control

Background-P&G Spreadsheet Model Problem Spreadsheet Models for Inventory Control
at Procter & Gamble (cc) photo by Metro Centric on Flickr (cc) photo by Franco Folini on Flickr (cc) photo by jimmyharris on Flickr (cc) photo by Metro Centric on Flickr One of the largest manufacturers of a wide range of consumer goods Net income is over $11 billion dollar. Founded in 1987 Embarked on implementations of Distribution Requirements Planning (DRP) Too many sale points around the world Objective of the model Documentation Products demand : HUGE High variation “Bullwhip” effect Safety stock levels at item and location levels are not easily forecasted 1. To educate supply chain planners on the various types, roles, and root causes of inventories in supply chains 2. To provide a quick method for setting safety stocks within a DRP framework Monte Carlo Simulation Upgrade Limitations The model cannot respond the dynamic market change The model cannot deal with the complex cases Low security level of spreadsheet model Wrong interpretation of the data through accounting system Portability ADV:
win-win-win situation for the retailer, the supplier and the consumer
The spreadsheet inventory level system adapted by supermarket can detect the inventory level and send the details of the inventory to their suppliers immediately. DISADV:
Avoid the use of macros
As spreadsheet works reliably and consistently for different versions and includes Microsoft Windows, including different languages, but macros are not portable in different versions, there may be errors. THANK YOU by Wei Guo and Leung Ka Ming Inventory
Framework Design Features
Specification test
Formula test
Numeric test
Robustness test
Macros test Become a truly global inventory model from a European initiative
Used by hundreds of supply chain planners
Include a well-documented work process
A central support group is available to assist users
Provide training by the central support group and business units
Incorporate a formal release process Quantifiable Results Benefits Intangible Results In all P&G regions and business units, there is a drastic inventory reduction in the range of 10 to 30%.
If 70% P&G business units use the models and assuming a reduction of 10% in inventory, there would be at least $350 million cash flow improvement. Separate the input sections from the calculation and results sections
Toggle the hidden status of the intermediate results quickly
Highlight input fields by color
Display warnings and errors in the data input
Use graph
Employ UDF to calculate safety factor automatically and adjust the forecast error for forecast bias
No password protected
Avoid the use of macros Confirm the validity of the calculations that evaluate the custom service levels so that planners could be more confident with the results Test every 18-24 months Evaluation & Insights
Full transcript