Data analysis in 20 minutes

By | October 2, 2014

Consultants are in the business of taking messy, unorganized data and turning it into information, and hopefully, some insights. Here is a simple example of excel clean up, and the steps to copy, paste, filter, sort, and cleanse data. For most consultants, the data cleansing would 7-10 minutes (takes some trial and error) and the graphics would be another 10 minutes, if (s)he knew what graph they wanted to make.

Challenge:

Solution:

Here are the outputs.   Sorting of the 465 retailers from highest discount to lowest.

465 retailers

Unsurprisingly, the retailers with the lowest discounts are the generalist retailers and gas stations. After all, who does not need something from BP or Walmart?

Lowest discounts

Steps #1 – 12 to clean the data and make these simple analysis.  

We have lots of consultants on this websites, and other left-brained people so this maybe basic for some. Also, there are likely easier, smarter ways to do this, but this is for education, so don’t be snobby. Some people might learn from this.

Filtering, sorting, copy, pasting.  Analyst basics.  Here we go. . .

http://www.cardcash.com/merchants/

step 1

step 2 and 3step 4-5step 6-7step 8

step 9

step 10-11

step 12

Practice makes perfect. Nothing is worse than a slow consultant, so practice. Hope this was helpful. Note that the website is always updating their inventory of discounted cards, so your results might be different (if you don’t do it on 10/2/2014).

If you want a copy of the starting excel (step #1) and the final clean one (step 12), leave me a message below, and I will send you the excel.

Related posts:

24 thoughts on “Data analysis in 20 minutes

  1. Vlad Maystr

    Being an undergraduate, I find this extremely useful. I am sorry for a late request, but would be lucky to get the Excel versions by e-mail.

    Reply
  2. Renee

    Hi, looking to practice excel and found this extremely helpful. May I have the starting and final sheets, please? Found your blog looking at information for MBA programs. It’s fascinating and helpful. Thank you for your time!

    Reply
  3. renee

    Hi, found your site while looking at info for MBA programs. Your page is immensely helpful! Looking to practice excel, may I have the starting/final sheets please? Thank you kindly for your time.

    Reply
    1. consultantsmind Post author

      Renee, thanks for the compliment. Will send you the practice file. Has 2 main tabs. 1) copy/paste of the messy data 2) finished analysis. Let me know what you think,

      Reply
  4. David Turnbull

    Whoa, brilliant expose of how to use the wonders of Excel. Any chance of a copy by email? Many thanks.

    Reply
  5. Femi

    Hey Good Samaritan! 🙂 can you send me a copy of the excel? Thanks!

    Reply
  6. ketebu

    Hey Consultantsmind, I just got the file in the email. Thanks!!! You are the best. FYI: Femi and Ketebu are same person… so don’t send to both. Ketebu is my WordPress handle – though I have not been writing. *covers face in shame* 🙂

    Reply
  7. Peggy Wurtz

    Found your website while researching Six Sigma methodology. You’re amazing! Love your sense of humor. Will you adopt me? Although I could probably be your grandmother. I’m 60 years old and was just laid off last fall and am looking into getting Six Sigma certified since a lot of the jobs I’m looking at are asking or prefer certification. Never stop learning, right? Will come back here often and I’ll definitely sign up to receive new posts. Thanks for all your help.

    Reply
    1. consultantsmindadmin Post author

      Thank you for reading and the great note. Made my night. Always learning. Yes, great free content out there and look forward to hearing your point of view on any of my posts. All feedback welcome.

      Reply
  8. Priyanka Jain

    Hey Consultantsmind, Please share the initial and the final excel output.

    Reply
    1. consultantsmindadmin Post author

      Hello, yes will send over. Thank you,

      Reply
  9. Sunny

    Great Post, I stumbled upon your website preparing for Case Interviews. I have interviews with 2 out of MBB in September and your articles have been really helpful in my preparation. Thanks for help everyone.

    I was able to complete the analysis, however could you please give a summary of creating the graph posted above?

    Reply
    1. consultantsmindadmin Post author

      Hello, I just sent the file to your email. Best of luck with the MBB interviews.

      Reply
  10. Phuc

    Hi Samaritan! i find your site extremely helpful. That would be grateful if you can share Excel file. After reading your posts, i’m gonna knuckle down and catch up on Excel.

    Thanks

    Reply
    1. consultantsmindadmin Post author

      Surely, will send when I get back to my laptop. Take care. Thanks for reading. What topics do you find most helpful?

      Reply
      1. Phuc

        Consulting tools, skills and tips are the most valuable to me since i ve had my fair share of case interview practice and still have no idea on how consulting works in reality.

        Reply
        1. consultantsmindadmin Post author

          Gotcha. Quora.com is a great resource for “life as a consultant” experiences as well. Thanks for reading and best of luck.

          Reply
  11. Natalie

    Hi I find it very useful since I’m going to start an internship as a consultant. Please share the initial and final Excel. Thank you very much!

    Reply
    1. consultantsmindadmin Post author

      Great to hear. On vacation in Phoenix. Will see if I can forward from phone. Congrats on internship. Do great work.

      Reply
  12. aubrey

    hi. I am very very late to the party. I too would love a copy of the excel. Thanks much

    Reply
    1. consultantsmindadmin Post author

      Hello, happy to send over. Thank you for reading – any particular posts you like, don’t like, want to see more of?

      Reply

Leave a Reply