You are here

Pareto Powered Presentations

Introduction

You don't have to be a Six Sigma Green Belt to start harnessing the tools of Six Sigma. Six Sigma sets a continuous improvement target for reducing product or service defects to 3.4 per million opportunities, but how can you start on this continuous improvement journey when you have just started rolling out an ITIL Service Management programme, and barely understand the basics of Six Sigma.

Whilst Six Sigma provides a wide range of data analysis and statistical methods to demonstrate quality performance and highlight areas requiring improvement, the pareto chart is probably one of the simplest and most effective tools for defining improvement priorities.

Even if you are not leading an ITIL or Lean roll out, as a Technical Leader trying to get a point across to managers, stakeholders or even the customer, then adding a basic pareto chart to a presentation on defect prevention generally holds the audience attention for longer,  than by just displaying or citing a list of issues and causes.

 


 

In this article I would like to demonstrate how anyone (with rudimentary skills in MS Excel) can generate a pareto chart as part of a process improvement analysis cycle. If you have never created a pareto chart, then I encourage you to try this for yourselves. Whilst there are many online resources offering free templates for pareto charting and other data analysis (within Excel), you will get more value out of "rolling your own".  You will also have a better understanding of how the template works by working through this exercise.

The template spreadsheet will end up having two worksheets. We'll display the pareto chart (as above) on the first worksheet, and manage the input information and formulas on the second worksheet.  Here are the data cells used to create the above Pareto chart.

I've made it rather generic, but you can substitute whatever values you wish. It is required however, that you enter values in descending order.

 

Before proceeding, you might want to have a quick look at the excellent guide on creating pareto charts at  Bright Hub PM,  Creating Pareto Charts with Microsoft Excel , or alternatively just follow my instructions below (and then critique the hell of my guidance), before going to the pros.


Building the Pareto Template

So let's start with a fresh MS Excel spreadsheet (97, 2003 - who cares?).  For this article, we will build the template with real data that may be of some interest to many of us:

Reasons for ITIL Implementation Failures.

Just about anyone with some knowledge or involvement with ITIL V3 for IT Services Management will have opinions on what worked and what didn't in their own environments, hence many of us would be able to tweak the data inputs in our template to make the output pareto chart more relevant and interesting to themselves and work colleagues.

As a starting baseline, enter the information (including formulas) from the following table into the first worksheet (we will move the worksheet to the second tab after we generate the chart itself).  The data I used included some general reasons on ITIL failures published by the author Malcolm Fry in his document,  "Top Ten Reasons Organizations are Unsuccessful Implementing ITIL" (a pdf file). I've already modified the list and scale of impact bsed on my own experience and judgement.  Feel free to modify your own data (and colours are not required).

ITIL Implementation Failures

A

Reason

B

Count

C

Percent of Total

D

Cumulative Percent

E

Horizontal Line Value

 Lack of Management Commitment 60  =(B3*100/$B$15)   =C3 80.00
 Lack of persistant and total staff involvement 55  =(B4*100/$B$15)  =(D3+C4) 80.00
 Not creating work instructions 50  =(B5*100/$B$15)  =(D4+C5) 80.00
 Not assigning accountable process owners 45  =(B6*100/$B$15)  =(D5+C6) 80.00
 Failing to maintain project moment 30  =(B7*100/$B$15)  =(D6+C7) 80.00
 Departmental demarcation and conflicts 18  =(B8*100/$B$15)  =(D7+C8) 80.00
 Ignoring solutions other than ITIL 12  =(B9*100/$B$15)  =(D8+C9) 80.00
 Too much time spent on complex process diagrams 10  =(B10*100/$B$15)  =(D9+C10) 80.00
 Lack of training and budget for quality management 8  =(B11*100/$B$15)  =(D10+C11) 80.00
 Too much concentration on Performance 6  =(B12*100/$B$15)  =(D11+C12) 80.00
 Being too ambiguous with targets and components 4  =(B13*100/$B$15)  =(D12+C13) 80.00
 No regular management  reviews of entire ITIL framework 2  =(B14*100/$B$15)  =(D13+C14) 80.00
 Totals =SUM(B3:B14)  =SUM(C3:C14)  =D14 80.00

 


Note that the highlighted formulae entered into columns C and D can be copied/pasted into the underlying columns. After formating and saving your spreadsheet file, your Excel table should look something like:

In my example, I highlighted the reason cells in the first 5 data rows to indicate the issues making up 80% of total reasons of failure (The underlying problems would be the subject of an Impact analysis and defect management exercise, to greatly improve the overall effectiveness and success of the ITIL roll-out.

To generate the pareto chart, follow these steps:

1.  Select columns A, C, D and (using Ctrl key and mouse cursor)
2.  From the Excel Insert Menu, select the Column chart dropdown and choose the first 2D chart (Clustered column)
3.  Reposition and resize the chart away from the table (either below or to the right)
4.  Right-click on any one of the vertical bars for Cumulative Percent, and change the "Series Chart Type" to first Line Chart
5.  Repeat step 4 for the Horizontal Line Value column (in our table / chart set to 80%

As a practical tool and template, you might want to cut/copy the pareto char you just generated, and insert into a new Worksheet tab (named Pareto). Rename the first worksheet tab to Data, and drag it to the second Tab position.  Save your work.


If all went to plan, your pareto chart should look like:

 

You could easily copy and paste this into a MS Powerpoint presentation for your team or manager.

 


 

pooraveragegoodexcellent
How useful or interesting would you rate this article?
How would you rate the article content for detail and clarity?
How would you rate this article for relevance to your role, company or studies?
How would you rate the overall layout, content and usefulness of the QualityHelp site?
Please rate this article
Please provide feedback or suggestions

Comments

Thanks KenDar .... using this to train some of our new staff :)

Site maintained by the QualityHelp Community