Pareto F09

Document Sample
Pareto F09 Powered By Docstoc
					                                         Pareto Analysis in Excel

Purpose of the Assignment

1.   To complete a Pareto analysis in Excel.
2.   To sort data in Excel.
3.   To set up cumulative sums in Excel.
4.   To develop a professionally formatted chart with a secondary axis.

Creating a Data Table

Pareto analysis is used to analyze the types of complaints or defects related to a process. We are given
(1) types of complaints or defects, and (2) the number of complaints or defects of each type. Create a
data table like the one below:

Sorting Data in Office 2007

We want to sort the complaints, from highest to lowest number of complaints.

1. Highlight cells A4:B12, which contain the complaint types and the number of complaints.

2. Click the Sort & Filter editing tool in the upper right hand corner in the tool bar. On the dropdown
   menu, select the Custom Sort option.

3. A dialog box will appear.
    In the Sort by dropdown menu, select Number of Complaints.
    In the Sort on dropdown menu, select Values.
    In the Order dropdown menu, select Largest to Smallest.

4. Click OK. The dialog box should disappear, and the data should be sorted from largest to smallest
   number of complaints.


1. In cell B13, compute the Total Number of Complaints in cells B4:B12.
2. Compute the percentage of complaints of each type. The formula for cell C4 is =B4/$B$13. Copy
   that formula to cells C5:C12.
3. As a check, compute the total percentage for all complaints in cell C13. It should be 100%.
4. Highlight the cells in Column C. On the ribbon, click Format and select Format cells. Highlight
   Percentage and change the number of decimal places to 1. Click OK.
5. For the first type of complaint, the cumulative percentage equals the percentage. The formula for
   cell D4 is =C4.
6. For the second and subsequent types of complaints, the easiest way to compute cumulative
   percentage is (cumulative % for preceding type) + (% for this type). The formula for cell D5 is
   = D4 + C5 . Copy this formula to cells C6:C12
7. If the cells in column D are not formatted as percentages, change the number formats to Percentage
   as described above.

Your final spreadsheet should be similar to the one below:

Making a Chart with Number of Complaints and Cumulative Percentage of Complaints

   We will plot the number of complaints as a bar graph on the primary (left-hand) axis.
   We will plot the cumulative percentage of complaints as a line graph on the secondary (right-hand

1. The complaint labels in column A are too long to use as data labels in a chart. Click on a cell in
   column B of the data table. Then right-click. Select Insert from the menu, and then click the radio
   button next to Entire Column.
2. In the new column B, type brief abbreviations (usually 3-4 letters) for each type of complaint. On the
   Home tab of the ribbon, click Format and then AutoFit Column Width. The new data table is shown

3. Highlight the cells containing the number of complaints, percentage of complaints, and cumulative
   percentage of complaints. In the spreadsheet above, you would highlight cells C4:E12.
4. Click the Insert tab on the ribbon. Then click Column, and click on the first (leftmost) 2D chart, as
   highlighted in the portion of the Column menu shown below.

5. A chart should appear. Drag the chart to the place on the spreadsheet where you want it.

6. We need to delete Series 2 (percentages) from the chart. Click a blank space on the chart. Then
   right-click, and click Select Data. The Select Data Source dialog box will appear, as shown on the
    next page. Highlight Series 2 and then click Remove. Series 2 will disappear from the dialog box and
    the chart.

7. We need data labels for Series 1 and Series 3. In the dialog box, highlight Series 1 and then click Edit.
   The Edit Series dialog box will appear. Type the desired name for Series 1 into the box and click OK.

8. The Select Data Source dialog box will reappear. Change the name for Series 3, as described above

9. We will use the right-hand side of the Select Data Source dialog box to change the data labels on
   the horizontal axis of the chart. Under Horizontal (Category) Axis Labels, click Edit. When the Axis
   Labels dialog box appears, highlight the cells that contain the axis labels and click OK.

    Note: This chart appears in Sheet 3 of a sample spreadsheet. In most cases, there would be only
    one worksheet in a workbook, and the sheet number would not appear.

10. When the Select Data Source dialog box reappears, click OK to finalize your changes. Your chart
    should be similar to the one below:

11. The number of complaints varies from 2 to 60, while the cumulative percentage varies from 30 to
    100% (0.30 to 1). Since the cumulative percentages are much smaller than 60, they are hard to see.
    We will add a secondary (right-hand axis) for the cumulative percentages. Click on the Cum. % series
    in the chart. Then click the Chart Tools menu at the top of the screen. Next, click the Format tab.

    If the name of the series that you want to graph on the right-hand axis is not displayed in the
    Current Selection group on the left side of the screen, click on the series in the chart and try again.
12. Click Format Selection. When the Format Selection dialog box is displayed, click the Secondary Axis
    radio button. Then click Close.

13. We now have a new problem. The Cumulative % bar is covering up some of the Complaints bars.
    This problem can be solved by displaying the Cumulative % series as a line graph. Click on one of the
    Cumulative % bars to select the series. Then right click, and select Change Series Chart Type. In the
    Change Chart Type dialog box, click on Line. The default line type is fine. Click OK

The current chart is shown below:

14. We now have a good graphical display of the data. We need to add at title to the chart, and a title
    for each axis. To add the titles, click on the chart and then on the Format tab of the Chart Tools
    menu. Use the Chart Title and Axis Titles menus in the Labels group to add titles. The completed
    chart is shown on the next page.


Shared By: