In this issue:

• Pareto Diagrams
• Pareto Diagram Example
• Steps in Constructing a Pareto Diagram
• Excel Tips
• The Bookworm Answer

Greetings,

Welcome to the SPC for Excel e-zine. Each month you will receive information on a featured SPC topic, Excel tips and other items. We hope you enjoy this issue. Please let us know your ideas for topics to cover as well as any ideas you might have for improving the e-zine.

This e-zine introduces the Pareto Diagram. There are always problems on which to work. There is not enough time in the day to work on all problems. How do you determine which problem to work on first? How do we get everyone to agree on what the major problem really is? Where will we get the most return for our investment? The Pareto diagram provides a data-based methodology of doing this. The Pareto diagram is useful for gaining consensus on the major problem and is useful as a communication tool.

Pareto Diagrams

The Pareto diagram is a special type of bar chart used to determine which problem to work on first to improve a process. An Italian economist named Vilfredo Pareto developed the Pareto chart in the late 1800s. It is based on what is now called the Pareto principle. Pareto found that 80% of Italy's wealth was held by only 20% of the people. This 80/20 rule is generally true for many things. For example, 80% of our problems are probably due to only 20% of the possible causes. The Pareto diagram allows us to separate the "vital few" from the "trivial many." This permits us to focus our time and resources where they will be most beneficial.

The Pareto diagram can also be used to determine how often causes of problems occur. The problem or cause is listed on the x (horizontal axis). The frequency of occurrence or cost associated with each problem or cause is plotted on the y (vertical) axis. The problems or causes on the x-axis are listed in decreasing order. The problem or cause that happens most frequently or costs the most is listed first. This is usually the problem you want to work on first. The problem or cause that happens least frequently or costs the least is listed last.

We offer PowerPoint training modules for Pareto Diagrams and other SPC tools. Please click here for more information.

Pareto Diagram Example

The Pareto diagram in the figure shows the reasons for consumer complaints against U.S. airlines in 2002. Each bar on the chart represents the frequency with which each complaint was received. It is easy to see that the major complaint was for flight problems (cancellations, delays and other deviations from schedule). The second largest complaint was for customer service (rude or unhelpful employees, inadequate meals or cabin service, treatment of delayed passengers).

The line on the Pareto diagram is called a cumulative line. This line gives the cumulative percentage. Flight problems account for 21% of the complaints. Flight problems and customer service account for 40% of the complaints. The top three complaint categories account for 55% of the complaints. So, if the airlines want to reduce the number of complaints, they need to work on flight delays, customer service, and baggage problems.

We also offer on-site SPC training. For more information, click here. »

Steps in Constructing a Pareto Diagram

A team is working on reducing the time it takes to complete a billing process. Errors cause delays in billing. The team decided to collect data for one month on reasons why a bill did not go through the first time. 1. Determine the problems or causes to be used on the diagram. The team brainstormed the list of reasons why the billing process would fail.

• Wrong PO #
• Addition
• Freight
• Cost
• Not on File
• Wrong Quantity

2. Select the time period to be covered on the diagram.

The team decides to collect data for a period of one month.

3. Total the frequency of occurrence (or cost) for each problem during the time period.

After one month of data collection, the team had the following results:

• Wrong PO #: 1
• Addition: 15
• Freight: 8
• Cost: 11
• Not on File: 24
• Wrong Quantity: 3

4. Draw the x and y axes, putting the proper units on the y-axis.

Since the Pareto diagram will include the cumulative line, the maximum value on the y-axis should be at least equal to the total number of problems (reasons). The total number is 62. There are 6 different reasons. The x-axis is divided into 6 equal segments.

5. Under the x-axis, write in the most important problem (largest frequency) first, then the next most important, etc.

As can be seen in the table above, Not on File is the largest reason. This reason is listed first. The next most frequent reason is Addition. This is written in next. The other reasons are then written in, in order of decreasing frequency.

6. Draw in the bars as shown in the figure above. The height of the bar will correspond to the frequency of occurrence for each problem on the x-axis. If possible, it is a good idea to write in the actual frequency on the top of each bar.

7. Calculate the cumulative percentage.

The cumulative percentage (CP) is calculated as shown in the table below. In this table, the data is given in descending order based on frequency of occurrence.

Not on File: 24 CP = 24/62 = 39%
Addition: 15 CP = (24+15)/62 = 63%
Cost: 11 CP = (24+15+11)/62 =81%
Freight: 8 CP = (24+15+11+8)/62 =94%
Wrong Quantity: 3 CP = (24+15+11+8+3)/62 = 98%
Wrong Purchase Order Number:1 CP = (24+15+11+8+3+1)/62 = 100%

8. Plot the cumulative percentage. The 100% mark on the cumulative percent axis corresponds to the total number of occurrences on the frequency axis.

9. Title the graph and include any other important information as shown in the figure. You should always include the dates the data were collected.

Our SPC for Excel has all the tools you need for the problem-solving model. Click here for more info

Excel Tips

One of the great features of our software, SPC for Excel, is that it runs as a Microsoft Excel add-in. Entering data, navigating around the workbook, etc., are necessary skills to use Excel. So, we have included some tips to help you with Excel. Several people have asked for a list of Excel shortcuts and so we have generated the list below.

Document actions
Open a file: CTRL+O
New file: CTRL+N
Save As; F12
Save: CTRL+S
Print: CTRL+P
Find: CTRL+F
Replace: CTRL+H
Go to: F5

Cursor Movement
One cell up: up arrow
One cell down: down arrow
One cell right: Tab
One cell left: SHIFT+Tab
Top of worksheet (cell A1): CTRL+Home
End of worksheet (last cell with data): CTRL+End
End of row: Home
End of column: CTRL+left arrow
Move to next worksheet: CTRL+PageDown

Formulas
Apply AutoSum: ALT+=
Current date: CTRL+;
Current time: CTRL+:
Spelling: F7
Help: F1
Macros: ALT+F8

Selecting Cells
All cells left of current cell: SHIFT+left arrow
All cells right of current cell: SHIFT+right arrow
Entire column: CTRL+Spacebar
Entire row: SHIFT+Spacebar
Entire worksheet: CTRL+A

Text Style
Bold: CTRL+B
Italics: CTRL+I
Underline: CTRL+U
Strikethrough: CTRL+5

Formatting
Edit active cell : F2
Format as currency with 2 decimal places: SHIFT+CTRL+$
Format as percent with no decimal places: SHIFT+CTRL+%
Cut: CTRL+X
Copy: CTRL+C
Paste: CTRL+V
Undo: CTRL+Z
Redo: CTRL+Y
Format cells dialog box: CTRL+1

Have a tip to share with us? Please click here...

The Bookworm Answer

Creativity is also the ability to look at the same thing as everyone else, but to see something different.
Let's take the challenge of the "life of a bookworm."

Each of the four volumes in the picture has the same number of pages and the width from the first to the last page of each volume is two inches. Each volume has two covers and each cover is one-sixth of an inch thick.

Our microscopic bookworm was hatched on page one of volume one. During his life he ate a straight hole across the bottom of the volumes. He ate all the way to the last page of volume four. The bookworm ate in a straight line, without zigzagging. The volumes are in English and are right-side up on a bookcase shelf.

Challenge: how many inches did the bookworm travel during his lifetime? ____________

The answer is five inches. Look at four books on a shelf. The first page of the first book is on the right hand side of the first book. So the worm does not eat through book 1; only through one cover (1/6 inch). The worm eats through both middle books (4 inches total) as well as four covers for those two books (4 x 1/6 = 4/6). The last page of the last book is on the left hand side of the last book. So the worm does not eat through book 4; only through a cover (1/6).

So the total distance the worm ate is:

1/6 + 4 + 4/6 + 1/6 = 5 inches.