Data analysis in 20 minutes

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.



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. . .

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:

Profile of success: Theranos founder, Elizabeth Holmes

If you don’t know this company, clearly you are not paying attention.   Theranos is a biotechnology start up merging biological systems with information systems.  Home healthcare monitoring systems to monitor disease progression in real-time. Originally, it was catering to pharmaceutical trials and speeding up the response time, A/B testing of new drugs.  Now, it enables you to use just 1 small pin prick of blood to give you advanced test results in 4 hours, with 1/2 the costs of traditional tests.

Elizabeth Holmes

Elizabeth Holmes was on the cover of Forbes. Here are the highlights:

  • Dropped out of Stanford at 19
  • Revolutionized the blood test industry
  • 27 patents on a disruptive innovation
  • 50% ownership of a business that is valued at $9 billion
  • One of the youngest billionaires in the world

What more do you need to know?    Well look at these Stanford videocasts:


  • Pick investors who share a long-term vision
  • Find people who can stand by you to build a business
  • The people you choose to work will absolutely determine the the failure and success of your business

Talent network:

  •  Evaluating candidates is difficult; technically you need to be excellent, but  that is only 50%.  If you don’t have the hunger to take ownership . . .it will not work
  • If everyone in the company who owns what they are working on, then we will be able to solve any problem we face.  Look for people who are hungry
  • Find people you can trust

Cash flow:

  • Most fundamental part of business – building a growing business that has cashflow; the market is telling you that you have something of value
  • Understand WHY you are doing the business; what is the source of passion?
  • Have the conviction to make it work . . not the title, money, or glory.  If you do it for superficial reasons, you may end up in the wrong place

Teenage entrepreneur:

  • Lived in the basement of some Stanford graduates
  • How do you convince people that you can pull it off, when you are 19 years old?
  • Michael Dell, Bill Gates, they demonstrated that it is not about age
  • It is about dedication and entrepreneurship … to make it work
  • You have to find people who will back you, who believe in you
  • You have to have the conviction because in the end, it is up to you

BCG: List of top 100 emerging market growth companies

Boston Consulting Group (BCG) just released a report called 2014 BCG Global Challengers here which lists the top 100 fast-growing and formidable companies from emerging markets.  It’s their 6th study and argues these companies are not only growing, but reaching a new level of maturity.  Jumping to the next S-curve.

  1. Fewer companies fell off the list, implying they are stronger and stable
  2. More retail companies, indicating more purchasing power from emerging markets
  3. Five companies graduated from the list, because they are now global leaders

The top 100 global challenger list.  These companies are impressive in their breadth, speed, and cost-competitiveness. While I am not the most global traveler or investor, I recognized 22 of the top 100 and recognized all of the 12 that graduated the list.

100 challengers

What are they doing right?  

  • Capturing middle class consumers – from 2009 to 2020, the global middle class is expected to expand from 1.8 billion to 3.2 billion, almost entirely coming from the emerging markets; monster growth
  • Meeting digital needs – New companies do not have the legacy infrastructure that prevents them from moving faster; asset-light technology also allows them to scale globally faster.  Multiple examples from the telecommunications industry
  • Building and supplying the world – This is familiar story line; local natural-resource companies develop a local monopoly and scale globally.
  • Growing through acquisitions – As just one example, Tencent (Chinese internet company) made 100 overseas acquisitions in the last few years.

End of easy growth.  BCG argues that it will get harder for these fast growth companies as they expand beyond their local (often protected) markets. Unsurprisingly, as these challengers grow, they become more like the staid, inert global Fortune 500 companies they were beating.  The teenager becomes like his/her old parents.

Two markets.  BCG argues that the dichotomy between the two markets is stark and growing.  1) A slow-growth, somewhat homogeneous mature market 2) A set of fast-growing, volatile, diverse, local markets.  Simple, makes sense.

They note that it is difficult for companies on either side of the fence to transition to the other side.  In fact, many emerging challengers are opting to grow into adjacent emerging markets, rather than over-extending into mature markets.

How to growth from a challenger to a leader?  Glad you asked.

BCG provides a clever way to visualize what it sees as the four ingredients to help these companies jump the S-curve.

  • Global enterprise DNA – know who you are and what you are trying to do
  • Operations – Scaling up with concrete processes + flexibility to adapt
  • People and organization – As Jim Collins says, “Get the right people on the bus”
  • Go to market – Knowing that the customer will tell you what they like

BCG challenger to leader framework

Alibaba. Lots of talk of this profitable Chinese internet giant last week.  One of the largest IPOs in history, 100% growth YoY in revenues, and 40%+ margins.  Trust me, you will see a lot more of these ground-breaking IPOs in the near future.

Related posts:

Consulting secret: stories and storytelling

This week I went out to dinner with team mates twice. On each night, I heard inspirational stories from my team mates – 1 personal and 1 professional – that really made me think deeply about how I am living my life.  One story was about friendship; staying friends and keeping a beautiful tradition for 40 years.  Another story was about bravery and leadership; knowing when to leave a company because it is not a fit.

Consultants tell stories.  From the time we make proposals to clients, we are telling stories of how we did the work previously, how the industry is changing, what we know of their competitors, and why we can be trusted as consultants.  When we recruit, we tell candidates what we are looking for, and paint a picture of what their life and career will be like, if they join us.  When we make recommendations, consultants pull together all the data, interviews, benchmarks, industry trends, hypotheses, and insights into a cohesive narrative, which is often told by Powerpoint.  Simply, we tell stories.

Life is about stories.  My wife and I – certainly the older we get – believe that life is about stories because the most important things in life (getting spiritual here) is about people and experiences.  Once you have enough money, safety, and get over the ambition of the 20s and 30s, it is about people and experiences.

People experience stories

Stories come from travel.  “Are you going on vacation this year?” is one of my favorite questions to ask clients, friends, and strangers.  It gets people thinking about good things: relaxation, friends, family, travel, and fun.  It is what stories are made of.

I was once told that Europeans rarely speak of work, or ask “what do you do?” when first meeting someone at a party or social.  It is crass, and actually, a bit boring. Instead, conversation gravitates to travel, food, hobbies, and sports.  Americans talk about work, while Europeans are more apt to tell stories.  European readers, tell me if this is true.

Stories come from sacrifice.  Very possibly, things that have the most value to us are the things that we worked hardest to achieve.  For hard-charging consultant types, the projects where we really had to invest our head, heart, and hands are the ones we are gloriously proud of.  Also, when we run into cowards, bullies, and ignorant people – they are anti-mentors who we learn not to be like.  This is informative too.  These experience make us better coaches, and managers.   Tough times make us more competitive.

“If you are going through hell, keep going.”  – Winston Churchill

Leaders tell stories.  This is so true, it is almost a cliche.  Leaders get things done through other people.  Leaders make voluntary followers.  Leaders motivate through words, vision, direction, credibility, authenticity, and ultimately, stories.

Storytelling is an art.  

1) Think of 3 people who you know who tell great stories.   What is their secret?  Travel and diverse experiences?  Openness to try new things?  A strong sense of timing, and really making the “punch line” of the story count?  A good listener who knows what the audience is interested in hearing?  Learn from great presenters.

2) Think of 3 people who are terrible at telling stories.  Do they drag on and on?  Do they laugh at their own jokes, when others don’t?  Do they talk about boring things people are not interested in?  Do they repeat the same stories?

3) Watch TED.  Listen to story-telling podcasts.  Visit with people 20-30 years older than you, and ask them about their lives. Pre-internet stories are WAY better.  Trust me.

My favorite story-telling podasts:

Have a story-worthy week.  This is what they say at the end of the Moth broadcast. What a wonderful thought.  Live life fully.  Do work that matters.  Make it memorable. Have a story-worthy week. Amen.

Meeting minutes: 8 good reasons to write up those notes

Meeting minutes are not boring.  Most people see this as a bureaucratic habit straight out of Mad Men, where Joan is typing notes at an old typewriter.  I disagree

1. Notes show effort At the very minimum, it shows good follow-through and commitment.  While others are barely paying attention in the meeting, and promptly forgetting what was said, you are adding some (albeit) minor value.

2. Notes emphasize communication.  I am convinced that better communication could save most businesses 20-30% of their SG&A costs.  Most meetings are not needed.   I did a post on bad meetings here.  I also also conducted a survey of this blog post’s readers.  As of today, 52 people responded to the question:

Meeting survey on consultantsmind

You can see that 82% of people (44% + 38%) felt that meetings were only useful <50% of the time.  That is pretty consistent with my experience as well.  Most meetings would not be needed, if people knew the overall mission, their role, and trusted each other.

3. Notes benefits others.  This is a huge service for all attendees.  It serves as a a summary of topics, agreement and drives accountability on action items.  It’s a force-multiplier because your small effort is enjoyed by lots of people, including people who did not even attend the boring meeting.  It provides continuity for the next meeting because you can just see what was discussed last time.

4. Notes require thinking, if done well.  Good consulting is all about structuring problems, and putting information into buckets.  Meeting minutes are a great way to practice this by putting the content into categories, bullets, and making it easy to understand what was said.  Synthesize what was said, into something concise and consulting-worthy.  You are taking a collection of thoughts and adding structure.

5. Notes define the narrative.  If you write up notes after a meeting, you are crafting the results in your own voice.  It also summarizes the story line of the conversation.  This is a powerful tool – not for bad, or misleading – where you can move the conversation along and prevent people from dwelling or wandering off topic.

6. Notes are flexible.  You can type up “minutes” after a meeting, client interview, or even a phone call.  Call me geeky, but sometimes, I type up a conversation with a peer or my boss, especially if a lot of content was covered.   This reduces misunderstanding.

7. Notes keeps the conversation going.  Meeting minutes are a reason to contact meeting participants, ensure that action items are completed, and driving activity.

8.  Notes allow you to be different.  Let’s agree, most meeting minutes are boring, useless, and administrative.  People write them without thinking.  It is just a written transcript of what was said, without any grouping of thoughts.  It does not advance the discussion and is a waste of time.  It makes the meeting seem even worse.

Luckily, after reading this post, you are not like that.  Your meeting minutes will be professional, useful, and a way to brand yourself.  A few tips for notes:

  • Outline who attended, the date, and the topic
  • Write them up the same day, don’t wait too long or you will forget everything
  • Ask a friend or someone else who was there to “proof-read” the notes
  • Adopt the right “tone” and not be too forceful, or misses the nuance
  • Remember that your notes will likely be forwarded to other people
  • Don’t be afraid to add in weblinks to things that were mentioned
  • Don’t be afraid to cut/paste presentation material from the meeting. .into the notes
  • Outline action items, owners and dates (if they were agreed upon)
  • Put 5% of your own voice in the notes. . . what was the purpose of the meeting, and to what extent was this accomplished.  Re-read the notes and add/subtract words that help you to tell the story that needs to be told.  Be yourself.

Related posts:

Consulting advice: walk with your clients

Clearly, some of the best conversations you have with clients are the ones on the way back from meetings.   Trust me on this one, when a meeting finishes, ask your client if it is okay to walk them back to their office.  Walk and talk.

  • You have their dedicated attention, even if only for 5 minutes
  • It is authentic and personal; it’s how friends and colleagues relate to each other
  • It is often a great follow-up to the meeting you just finished

Often, the key things you want to say to a client are personal, and can easily be conveyed in a few minutes in an informal setting.  It does not deserve an email, phone call or a meeting.   Walk them back to their office.

A few other ways to have impactful, and brief interactions with clients:

  • Share a coffee or a meal together
  • Offer to help with a simple task (create graph, draft a communication)
  • Send over an analyst report or benchmark study
  • Type up meeting minutes, or interview notes and send over
  • Connect your current client with other people in your network
  • Help them identify and hire talented people


Flickr: Creative Commons, Randy Robertson

Consulting advice: read

When I speak with eager MBAs on what they should do to prepare for management consulting, I often fall back on this basic idea: read.

We don’t read enough.  I really started enjoying reading when I turned 25 years old. Obviously, that is a fail because my brain was pretty hard-wired by that point already.   Reading gives us a broader perspective, and helps to structure some of the many random thoughts that run around our brain.  Well-crafted books teach us the beauty, mystery and power of story-telling.

Bill Gates reads a book a week.   Clearly, that is not for everyone.   These are the books that Bill Gates read recently and the ones he has reviewed here.

Bill Gates Books

Read books.  The more you read, the more likely you will have something to say.  Even if you are not an extroverted conversationalist, if you read, at least you have some some content you can share with clients, prospects, team members and hotel clerks.

Listen to podcasts.  I probably read a book a month, but I get fed intellectually more by listening to podcasts while traveling to/from clients, cutting the grass, and cooking.

Listen to audiobooks.  Buying audiobooks are expensive.  Try renting them from, or even better, get them from your local library.  The last few books I got for free from my library were:

Read the Economist.  I have read this for the last 20 years.  Well-written, British, writing and wit with a decided international bent, free-market point of view with a heart of empathy for the weak.  Informative and easy to read.  It certainly covers a lot of marcoeconomic and political issues, but also provides thoughtful vignettes and stories to keep you thinking.  We Americans need to open our eyes to a bigger and broader world.  Lots for us to know, appreciate, enjoy and engage.

No more excel graphs, Tableau is the future

Tableau.  Yes, this is french for Table.  It is also the most user-friendly, powerful, and amazing desktop visualization tool for consultants.  Tableau is a company based out of Seattle Washington, founded in 2003 to commercialize work out of Stanford.  Fast-forward 10 years, and it is a publicly traded company of 1,500+ employees, $232 million in revenue and traded under the ticker symbol of DATA.  See their investor relationship pdf (2Mb) here.

No more Excel graphs.  People on my teams who use Tableau no longer use Excel to draw any graphs.  Let me say that again, they don’t use excel to draw graphs.  My team mates list the many reasons they prefer Tableau:

  • More flexible; it has “drag and drop” functionality like pivot tables
  • More graphical and unique ways to show data
  • Intuitive interface to link data sets
  • It can be published and shared with clients

Gartner agrees.  Gartner ranked Tableau as a leader in its magic quadrant ranking of business intelligence and visualization tools here.  This is pretty strong social proof, for those who care about the legitimacy and market acceptance of the tools.

Accenture uses Tableau.  Accenture recently did some research on the rise of the market technologist function, and put their survey results on Tableau Public here.  Click over to some of the tabs, and see how Tableau visualizes simple bar and line charts.  See how you can select different options and the graph changes automatically.  This is the type of flexibility that Tableau offers.  If you are a consultant, and you can’t use Tableau, you might be in trouble.

Survey Demographics

Free trial.  Of course they offer a free trial, which I have also done.  Here it is.  Even if you don’t join the Tableau train now, that is fine.  I am only a beginner and rely on my teams to crunch data.  Don’t be discouraged.   You will see Tableau mentioned again in your professional life, I am sure.  No question Tableau is the future.  I told you first.

Tableau revolution


Don’t be a consulting mailman

Mailman is a word that I have used many times this week.  It has come to be a short-hand way for me to say overhead.  Like the post I wrote about not supervising others here, if you find yourself merely sitting in meetings where you are only taking notes, setting up calls and logistics for others.  You are not adding value.  Questions to ask:

  • Are you bringing new ideas to the client?
  • Are you making PowerPoint pages to distill thinking down?
  • Are you connecting with the client, and getting them to open up?
  • Are you coaching junior consultants?
  • Are you taking risks that the client applauds?

You are a mailman if you don’t have a value-added role, and instead are creeping into meetings, and delivering messages from here to there.  You have become a human email system.  If you find yourself sayings things like:

  • I am not too familiar with this topic, but. . .
  • I will have to ask him, what he thinks
  • Let me run this by her and let you know
  • Let’s get A and B on the call, and discuss
  • Well, she is really the one who knows the answer, but. . .

Time to reassess what it is you are doing on the project.  Chances are you are project managing, and doing it badly.  Overly involved in the wrong things, not trusting your people, and generally just playing mailman.  Taking messages from here to there.

Decision rights.  Effective organizations know who does what.  Great HBR article on using an organizational structure to facilitate and accelerate decisions here.

Bain talks alot about decision rights, but the framework is simple.  People can either.  . .R.A.P.I.D.  Recommend, Agree, Perform, give Input, Decide.  Think about your role on the project, and if you are R, A, P, I, or ..D.  Read about Bain tools here.

If you are not doing one of those 5 things with decisions, you are a mailman.  You are just shuttling information back and forth.   Don’t be a mailman.

Confession: I have been a mailman recently.


Related Posts: