, , ,

5-whys Analysis using an Excel Spreadsheet Table

Leave a Comment

Find out how to visualize your five-whys analysis by putting it into a spreadsheet, including a downloadable five why template and tutorial. Part 2 of a four part series on 5-whys.

By Karn G. Bulsuk



More information: An Introduction to 5-whys, 5-whys Analysis using a Fishbone Diagram and The Weaknesses of 5-Whys

Downloads: 5-whys Template Download and Step-by-step example on how to perform a 5-why analysis
Visualizing your 5-whys analysis in a table is the best way to show the causal links between your causes and the ultimate root causes. One of the easiest ways to do this is to put it into a spreadsheet - the 5-why Excel spreadsheet template as well as the example in the article is available for download at the bottom of this article.

Imagine that there is a company called Alencia which specializes in receiving outsourced executive recruitment work, where they match talent to specific jobs and receive commission for doing so.

In the past year, demand has boomed and their business has expanded rapidly, but at a price: while demand has increased, capacity has remained the same, leading to a large back log of job requests. Previous attempts to bring in a computerized system have been met with staff resistance and failed. Clients and potential job seekers are both unhappy with the slow service, and are gradually turning away to more nimble competitors.

Mio has been put in charge of fixing the current problems, and has been given full authority to act. To keep things simple, she looked for the root causes using the table method.

Setting Up the Excel Sheet


In Excel, Mio arranges the table so that it has twelve columns, and set up like in the illustration below. Each “why” column will contain your why analysis, while each column in between will consist of arrows leading your reader to the correct Why.

The root cause analysis column will contain all the root causes you identified, while the recurrence prevention column will contain your recommendations on how to prevent it from happening again.

To help you save time on setup, a template in Microsoft Office format can be downloaded at the end of this article.

The First Why


The first why Mio asks comes directly from the stated problem: “Why are processing of jobs delayed?” From here, she can identify two main causes:


Why Question Answers
Why are processing of jobs delayed?
  1. There is no computerized solution to handle job applications.
  2. There was no formal set of procedures to handle job requests, and procedures were passed on by mouth as opposed to being documented.





Mio maps those two causes in the “Why 1” column of the spreadsheet.

The Second Why


If there is more than one answer to a “why” question, they will handled separately when it comes to their turn to ask why.


Why Question Answers
Why is there no computerized solution to handle job applications?
  1. There was staff resistance


Why was there was no formal set of procedures to handle job requests, and why were procedures were passed on by mouth as opposed to being documented?
  1. There was no system in place to do so.




She would then add the answer in the “Why 2” column, with an arrow to link the chain-of-reasoning together.

The Third Why



This part demonstrates how there can be multiple answers to a Why question, and how to deal with a branching Why analysis.


Why Question Answers
Why was there staff resistance?
  1. They were not explained the full benefits of the system.
  2. They feared being made redundant.
  3. They were uncomfortable about changing the way they worked


Why wasn’t there a system in place to handle job requests?
  1. The company grew at an exponential rate that there was no time to document anything.





To map this out, Mio would add each answer to its own individual cell in “Why 3”. As you can see, each are legitimate answers to the question, and such branching will help us to obtain a much deeper and wider 5-why analysis.

The Fourth Why


This set of whys are becoming more involved, but not more difficult as the same basic principles still apply. She asks “why” to the previous point, and then writes down her answers in the “Why 4” column.

Why Question Answers
Why were staff not explained the full benefits of the system?
  1. There was a lack of communication.

Why did they fear being made redundant?
  1. They thought the computer system was designed to replace them.


Why were they uncomfortable about changing the way they worked?
  1. They had always been doing it this way.
  2. The positive aspects of the change were not communicated.


Why did the company grow at an exponential rate that there was no time to document anything?
  1. There was insufficient planning





If you notice, the third why which says “they were uncomfortable about changing the way they worked” branches out into another two whys. Even at this stage, there could be several answers to the why question.

The Fifth Why



At the final why, you should be focusing on wrapping up your analysis and identifying the root causes of the problem.


Why Question Answers
Why was there was a lack of communication? We assumed that the benefits were obvious.
Why did they think the computer system was designed to replace them? Because we didn't tell them how it would help make their jobs easier.
Why had they always been doing it this way? All the work was done manually prior.
Why were the positive aspects of the change were not communicated? We assumed that the benefits were obvious.
Why was there was insufficient planning? Top management were too busy fire fighting and dealing with operational work, rather than developing a strategy.



Root Causes



At the end of your analysis, you will often come up with a series of common root causes. In this analysis, Mio has identified the following root causes, and the following solutions to prevent them from happening again:


Main Root Cause Identified Recurrence Prevention
Insufficient communication in the following areas:




- Benefits of bringing in a computerized system.
- How the computerized system will assist them in their jobs and not make them redundant.
Develop a communication strategy to clearly inform staff of the benefits of a software system to them, and to reassure them that it is not designed to replace their jobs.
No culture of change and sense of insecurity among staff. Include change management in implementation plan. Also, assure staff that comprehensive training will be offered in order to allay fears of change.
Management level: poor work delegation and lack of advanced planning leads management to engage in operational level work, and to fire fight instead of focusing on the strategic level. Develop vision, and coordinate resources to free up management to engage in strategic planning. Hire more people as necessary, and implement a computerized system.



So at the end…


With these findings, Mio is now equipped to develop a strategic plan to solve the current problems at Alencia.

Downloads

Please note that the Excel 2007 files have more comprehensive formatting which is not present in the Excel 97-2003 versions, due to program limitations.

Share this article

Related Posts Plugin for WordPress, Blogger...

29 comments »

  • ali anani said:  

    The Bulsuk Way! Thanks for such an expressive example.

  • Karn G. Bulsuk said:  

    Hi Ali

    Thank you for your comment!

    I am curious to know how my articles are being used, so if you happen to be using them in your training or something, please do give me some feedback to see how I can make them even better.

  • ali anani said:  

    Hi Karn,
    I have only recently been fortunate to land in your website. I shall keep you posted. I wonder if you have a full listing of your spreadsheets.
    Thank you

  • Karn G. Bulsuk said:  

    Hi Ali

    The spreadsheets and other tools are all available on my blog. Is there something specific you would like?

  • ali anani said:  

    Karn,
    You may consider other loops such as the OODA loop.

  • ali anani said:  

    Hi Karn,
    It has been long since your last addition. You may consider adding a template for the Blue Ocean Strategy. Or, a spreadsheet for studying value network analysis. I am waiting for your juicy creativity

  • Karn G. Bulsuk said:  

    Hi Ali

    Thank you for the comments. I do have several more quality articles up my sleeve, but some are still in writing while others are still having the artwork done up. I'll certainly be publishing more models as we go along!

    I'd be interested in looking at your other suggestions as well. Blue Ocean Strategy certainly sounds like an interesting one.

  • ali anani said:  

    Hi Karn,

    I published two days ago a slideshare presentation on Blue Ocean Strategy and relating it to team building by using a different concept. If interested, the link is
    http://www.slideshare.net/hudali15/blue-ocean-strategy-balanced-scorecard-strategy-and-team-forming-a-shared-perspective

  • ali anani said:  

    Hi Karn, sorry to be back so soon. I just want to draw your attention to my other slideshare presentation on the 5- Whys and my ideas about stretching it. The link is
    http://www.slideshare.net/hudali15/extend-the-five-whys-to-eight-whys-why

  • Anonymous said:  

    Hi Karn,

    I found this method is very helpful to implement. I could not open excel spreed sheet coul you send it to me, my mail is: nilgunyetis@yahoo.com
    best regards,
    nilgun

  • Trent said:  

    Hi, Karn:
    I have been looking for tool of continuous quality improvement for a while and find '5 Whys' recently. Your articles are very helpful. Although I want to learn more about '5 Whys'. Is there book or web where I can find more about it?
    Thank you for your time,

    Trent
    San Diego, CA

  • Karn G. Bulsuk said:  

    Hi Trent

    Thank you for your question.

    I am not aware of any books which deal specifically with 5-whys. The 5-whys is simply a tool which facilitates continuous improvement rather than being a topic on its own. The tool itself is quite simple as you can see on this site and my other articles on 5whys here.

    That said, the book titled "The Toyota Way" is always a good starting point to learn more about the entire process.

    Best wishes


    Karn

  • Carolt said:  

    KARN,
    I really enjoyed the visualization of the spreadsheet. I am involved in a large project and have been using the "why" system--however I came about it naturally (trying to get to the 'root' while we attempt to improve our organization). It works so well because it allows people to analyze without becoming defensive about their own previous decisions, and assists in building a strategic plan beautifully. Instead of just taking my "meeting notes" I will use Excel! Thank you!

  • Carolt said:  

    Karn,
    Thanks to some of your information I will now be doing alot more research on this so I can be effective! Thanks for the intro to 5-whys.
    Carol

  • Karn G. Bulsuk said:  

    Hi Carolt

    I'm glad you found it useful. In many cases people often do not know how analyse for root causes and end up trying to solve just what is in front of them - which is precisely why the problem reoccurs. The Excel method is simply one way of making the analysis easy in a business environment. :)

    Karn

  • IGOR said:  

    Hi Karn,

    I've been working on a consulting firm in which all the services we offer comes from the PDCA method. And your objective explanation about it and 5-Why have made my understanding so much easier. Thank you very much for sharing up your knowledge. Greetings from Rio de Janerio, Brazil.

  • Karn G. Bulsuk said:  

    Hi Igor

    Thanks for your comment - it's good to know people find it useful. Good luck to your consulting business!

    Karn

  • the unfinished collection said:  

    Thanks alot Karn this article is of a great help to me
    i am a student in a total quality management class & i have a problem that should be solved with the 5 whys & fish bone way
    this article was very helpful to use the 5 whys method in a way that will show causes of every why & so will simplify the fish bone diagram as well.
    i wonder if u can help me how to present short term , medium term , long term solution for a problem

  • Anonymous said:  

    Hi,
    So why stop at 5 and not have a 10 why? How do you determine how many whys one should ask?
    What is the difference between a 5W and a 5 why?

  • Karn G. Bulsuk said:  

    @The unfinished collection
    Once you've performed 5-why's analysis, you can then sort out the root causes and their countermeasures based on long, medium and short term priorities.

    @anonymous
    It is possible to go beyond 5-why's to 10 why's as you mentioned, but the number of issues you would come up with plus the time to perform that detailed analysis would male it difficult to use the tool.

    In general, 5 whys should be sufficient to come up with the root cause. Too few than 5 may indicate the analysis is insufficient and you haven't reached the root cause yet. More than 5 or 6 may indicate you're going too deep and pinpointing only a part of the root cause, and missing the big picture.

    5W has nothing to do with 5 whys. It stands for who, what, when, why and where, assuming this is the correct acronym you're thinking of.

  • Aditya Kusuma said:  

    Useful and I will use as reference to my in-house training for RCA.

  • karnbulsuk said:  

    Hi Aditya, glad it was useful. Thanks for dropping by!

    Karn

  • Yulita Veranda Usman said:  

    Hi Karn..
    Thank for your this usefull information.  I permit to use your templete in any kind of problems and may I want to sharing with anyone.

  • karnbulsuk said:  

    Please do use it and share it with others who would be interested!

  • Yulita Veranda Usman said:  

    Sorry... I make a mistake.... I mean, I hope You permit me to use your template in any kind of problems and sharing with anyone.

  • Yulita Veranda Usman said:  

    thanks for kindness...

  • Ayu Maniez said:  

    Sorry, Can I asked something? I used this method for my final project. I
    want to know some literature books or journal that you read to create
    this table. Thanks before

  • karnbulsuk said:  

    Hi Ayu

    This is something I learned while I worked at Toyota. There are books out there which will cover the concept: try the Toyota Handbook, I believe they may also cover it.
    Good luck on your final project!

  • Ayu Maniez said:  

    Ok Thank You (^_^)

  • Leave your comments