PFEP Spreadsheets Are a Bad Idea!
Trying to implement a PFEP system using spreadsheets is a bad idea. The classic book, "Making Materials Flow", from the Lean Enterprise Institute is a good introduction to the concepts that go into a PFEP system, but it is simplified and hypothetical. The more recent book, "Turbo Flow: Using Plan for Every Part (PFEP) to Turbo Charge Your Supply Chain", goes into much greater detail about what a PFEP system can and should do for you, but doesn't cover implementation details.
"Making Materials Flow" uses spreadsheets as an analogy for database tables, not as an alternative to them. Of course, you can track everything this way, but how will you print it? Good databases are about usability, accuracy, consistency, accountability, automating tasks. (Yes, I was one of the developers of Microsoft Access from pre-release in 1992 through the 2003 version when I was with Microsoft, but I genuinely believe that it's a great product and it is the world standard (since it comes with most corporate versions of Microsoft Office.))
I'm adding this page to PFEP.Info because so many visitors to this site get here by searching for "PFEP Spreadsheet", "PFEP Excel", "PFEP Sample XLS Files", etc., that it seems important enough of a misconception to devote a page to explaining why this is a bad idea.
It's useful for me to explain how one class of Parts might have more than one Delivery Type with this example:
"Making Materials Flow" uses spreadsheets as an analogy for database tables, not as an alternative to them. Of course, you can track everything this way, but how will you print it? Good databases are about usability, accuracy, consistency, accountability, automating tasks. (Yes, I was one of the developers of Microsoft Access from pre-release in 1992 through the 2003 version when I was with Microsoft, but I genuinely believe that it's a great product and it is the world standard (since it comes with most corporate versions of Microsoft Office.))
I'm adding this page to PFEP.Info because so many visitors to this site get here by searching for "PFEP Spreadsheet", "PFEP Excel", "PFEP Sample XLS Files", etc., that it seems important enough of a misconception to devote a page to explaining why this is a bad idea.
It's useful for me to explain how one class of Parts might have more than one Delivery Type with this example:
Let's say your Factory builds cars. You have a WorkCenter that puts in the car stereos. 20% of your customers get the Cheap model, 40% get the Standard one and 30% get the Deluxe stereo. Those three models of car stereos are delivered to the WorkCenter as Bulk Parts, because they are used so often. Of your remaining customers, 5% get nothing (so a blank faceplate is another Bulk Part at the WorkCenter) and the final 5% choose a Super High-End car stereo from your catalog of 100 different models. When your Factory is building a car with one of these Super High-End car stereos, the specific model of car stereo is Sequenced to the WorkCenter.
Did that make sense? 95% of the time, the "Car Stereo WorkCenter" did it's job using one of 4 Bulk Parts. 5% of the time, the WorkCenter does it's job using one of 100 possible Sequenced Parts.
I like that analogy because it explains the concept of delivering one class of Part via more than one Delivery Type using an example that most of us can relate to. On the other hand, if your really are building cars, this may not be the best way for your company to install car stereos - it's just an analogy that I'm using to explain a concept. Likewise, "the other guys" are using spreadsheets to explain the type of data that your PFEP system might need to track, without getting into teaching you about database design.
I do know of a number of companies that, for one reason or another, need to gather information from a large number of people and choose to use spreadsheets as the data collection mechanism. That makes sense compared with training hundreds of people in how to use a new program. On the other hand, in all of these cases that I am aware of, another program reads the data entered into all of the submitted spreadsheets into a database, where all further processing is done.
Spreadsheets also have a problem known as "column creep". In the example from the "Making Materials Flow" book, the authors are tracking the height, width and depth of the original Containers of each Part. (These are Parts that should be repacked into standardized, durable, reusable Containers for use in any real Lean Manufacturing system.) It sounds like most of these Parts are arriving in cardboard boxes - why not add a column to indicate if the Container is a cardboard box, plastic box, metal can, etc. Likewise, why not add a column for the Container color and weight - that information might be useful for something later on. Also, … well you can see how "column creep" happens. If you have a good tech person on-staff, they can make the Excel data entry process a bit easier. On the other hand, if you have actual people doing the data entry, you'll discover that they are inconsistent. Early on dimensions look like 8.375", then 8.5" and end up as 8", if they even bother to enter the data at all after the first hundred Parts. They might rationalize it as "all Bolts from Brand X arrive in the same size box, so why am I constantly retyping it's dimensions?". (Sounds like it would also be a good idea to add another column for Supplier.)
I like that analogy because it explains the concept of delivering one class of Part via more than one Delivery Type using an example that most of us can relate to. On the other hand, if your really are building cars, this may not be the best way for your company to install car stereos - it's just an analogy that I'm using to explain a concept. Likewise, "the other guys" are using spreadsheets to explain the type of data that your PFEP system might need to track, without getting into teaching you about database design.
I do know of a number of companies that, for one reason or another, need to gather information from a large number of people and choose to use spreadsheets as the data collection mechanism. That makes sense compared with training hundreds of people in how to use a new program. On the other hand, in all of these cases that I am aware of, another program reads the data entered into all of the submitted spreadsheets into a database, where all further processing is done.
Spreadsheets also have a problem known as "column creep". In the example from the "Making Materials Flow" book, the authors are tracking the height, width and depth of the original Containers of each Part. (These are Parts that should be repacked into standardized, durable, reusable Containers for use in any real Lean Manufacturing system.) It sounds like most of these Parts are arriving in cardboard boxes - why not add a column to indicate if the Container is a cardboard box, plastic box, metal can, etc. Likewise, why not add a column for the Container color and weight - that information might be useful for something later on. Also, … well you can see how "column creep" happens. If you have a good tech person on-staff, they can make the Excel data entry process a bit easier. On the other hand, if you have actual people doing the data entry, you'll discover that they are inconsistent. Early on dimensions look like 8.375", then 8.5" and end up as 8", if they even bother to enter the data at all after the first hundred Parts. They might rationalize it as "all Bolts from Brand X arrive in the same size box, so why am I constantly retyping it's dimensions?". (Sounds like it would also be a good idea to add another column for Supplier.)
During my six years at Microsoft I was a part of the User Interface and Usability groups and have seen these same scenarios play out over and over again. This type of human behaviour is very predictible. Our goal is not to force our people to respond like machines. Our goal is to get as close as possible to having our systems understand the true intentions of our people while making these same systems as easy to use as possible for our people. If our people don't have to spend all of their time and energy fighting with a data entry system, then they are able to focus their time and energy on other things.
Why doesn't this happen in a well designed database? Well, building on this same example, something known as Database Normalization occurs. A good database developer, who should be involved with your whole Lean Manufacturing project from day one, would look at the boxes and look for common sizes. They would also evaluate why are you tracking this information. (For example, if you need to know how many boxes fit on a two foot wide shelf, you really only need to track widths of over 24" (doesn't fit), 12"-24", 8"-12", 6"-8", 4.8"-6", 4"-4.8", etc.) Your database developer will come up with a list of likely sizes and your data entry people will be able to simply choose a size off of a list. The actual dimensions of each class of sizes would end up being stored in a separate table. Likewise for color. Rather than the list of Red, Blue, Purple, Plum, Magenta, Mauve, Violet, Light Purple, etc. that you are likely to get from people being driven to madness by long hours of data entry work, your database developer will help you develop a standardized list of colors up front (and maybe even a board with physical samples and their matching color names) and your users will choose the colors from a pre-built list. It might even make sense to carry it out another level - if 25% of your parts arrive in the same size yellow boxes, then your developer should just add a button to push to indicate "yep, another one". Compared to the end of the previous paragraph, now your workers can just click one extra button for each Part and fill in all of the data that you need, rather than just leaving fields blank because "it's obvious".
'5 Rules of Data Normalization'
This poster was originally designed in 1989 by Marc Rettig as a subscription premium for Database Programming and Design magazine. He now makes it available as a free downloadable PDF file.
This poster was originally designed in 1989 by Marc Rettig as a subscription premium for Database Programming and Design magazine. He now makes it available as a free downloadable PDF file.
It still is important not to waste people's time and energy tracking more information than is really needed. It's all too easy for spreadsheet people to just add another column or two, but since database people ask "should these columns be 'normalized' into a separate table?" and "how can I reasonably display this information using Forms, Reports, etc.?" they are much more likely to look for correlations up front and enable easier data input, if an item really is required.
