How to create a BCG matrix in Excel

Having used this type of chart a lot recently, I thought it was time I had a decent version in Excel.  In case you are not aware a BCG matrix, also known as a growth-share matrix is a management planning tool.  It is used to portray a company’s / SBU’s product portfolio on a quadrant showing relative market share (horizontal axis) and speed of market growth (vertical axis).  It basically shows you the potential of your company’s products.

A BCG matrix is basically a bubble chart with a few tweaks.  When complete, it should look something like this:

BCG matrix

You can download my sample chart here.

Step 1: Set up your data

To be able to plot a BCG matrix successfully, you need the following data: market growth,market share, market share of the largest competitor, and relative market share.  You can calculate the last by dividing the market share / market share of the largest competitor for example:

=C2/D2

A sample data table might look like this:

BCG data

Step 2: Create a bubble chart

Highlight the first three columns of your table, in my case cells A1:C6. Then click on Insert > Other Chart > Bubble Chart.

insert bubble chartYour bubble chart will look something like this:

bubble chartYou can then delete the title, legend and horizontal gridlines by clicking on them and pressing the delete key.

Step 3: Convert the chart to a BCG matrix

Right click on the x axis and then choose “Select Data”.  In the pop up menu that appears, click on the “Edit” button.

select data

You have to change the series X values.  Highlight the Relative Market Share values.  In this example we need to change it from column A to column E.

Edit X series values

It should say:

='BCG Matrix'!$E$2:$E$6

Your BCG Matrix chart will update. Now, all of the values should be correct.

Adjusted X axis

Right click on the X axis and then choose format axis.

format X axis

Select “Values in reverse order”.  Then change where the “Vertical axis crosses” to have an Axis value of 2.  This value will change depend upon your data.

Without closing the formatting pop up menu box, click on the Y axis.  You can then adjust the minimum and maximum values to “0” and “4”.  The Axis value this time should be 0.2.  Again, this value will change dependent on the data added.

format Y axis

You can also change the number format using the Number tab to “Number” with 2 decimal places.

number

You can then press the close button.  Your chart should now look like this:
complete BCG matrix

Step 4: Format the BCG Matrix

So in my example, I created some custom pictures to use as fills for the different bubbles: a star, a cow, a dog and a question mark.

custom bubbles

The bubble in the left upper quadrant is the star.  Once you’ve created your star picture, click on it and press Ctrl+C.  Then, click on the bubble twice and press Ctrl+V.  The bubble should then update with the picture.  repeat the process for the other bubbles.  The left lower quadrant is for the cow picture.  The right upper quadrant contains the question mark bubbles, the right lower quadrant contains the dog bubbles.

The final touch is to add data labels.  Ensure all of the bubbles are selected (click off the chart and then click once on the series).  Then click on Chart Tools > Layout > Data labels > Right.  The chart will show the values of the chart.  To change the data labels so that they show the product names, click on one of the data labels twice (slowly). Then click inside the function test entry field and type the cell reference that shows the name of the product.

edit data label

Step 6: Add the coloured background

Click on Insert > Shapes > Rectangle and draw a rectangle over the top of one of the quadrants.  Format the rectangle as you wish. Repeat until you have a different colour for each quadrant.  Select all four, by clicking on them while the shift key is held down.  Then right click and press “Group”.  Then with the group still selected click on Drawing Tools > Format > Send Backward > Send to Back.

Your chart should now look like this:

BCG matrix

And that’s it!  You can download the sample worksheet here.

+Alesandra Blakeston

Innovation and fast cars

I saw a great post this weekend, “How we fly: Aircraft as Career Metaphors” by Venkat and it got me thinking about Innovation programs.  How does your innovation program handle behind the wheel?  Do you have a couple of Lamborghinis in your innovation garage, or are you a Herbie fan?  Is speed important to you, the way your program appears on the outside or are you more concerned with long term results?

Incidentally, my personal view is that you need a fleet of vehicles – no one size fits all!  That being said, what vehicles would you budget for?

+Alesandra Blakeston

Lamborghinis: You have one or two superstars in your innovation garage.  They have speed and agility, but lack capacity and are expensive to run
434398_8840804072
Tanks: You have a couple of heavy hitter teams that have won some major battles, but unfortunately the rest of your teams are following behind on foot
Public Transportation: You have a fleet of fast moving buses that build consensus, take everyone along for the ride and create real momentum
Herbie: You are the proud owner of an award winning innovation program.  Not only do you win the race with style and panache, you add unique value
5662414081_78ba9e9318_m
Rolls Royce: Your program is beautiful to look at, but can’t keep up in this era of change as fast as change. Lacks capacity, speed and agility
Police Service: Are you constantly trying to enforce your innovation program, making sure that everyone follows the rules?
Police Service: Are you busy enforcing the rules in your program?  Your vehicles are fast, and ready for action, but do your teams want to be involved?
Sixties hippy van:  Seen as far fatched and out there, you have the capability to bring others along,
Sixties hippy van: A bit out there, you have the capability to bring others along, but somehow the message isn’t being accepted by all. It’s slow and not agile
Motorbike: Fast and agile, the bikes can go off road as well as weaving through dense traffic.
Motorbike: Fast. agile and powerful, bikes can go off road or weave through dense traffic.  Unfortunately, they also lack capacity & have a tendency to crash
4334109721_3bf05b260b
Ford Focus: Fast, easy to park and agile, so why doesn’t everyone buy one?  Are they distinctive?  Do they add unique value?  Can they build consensus?

 

The Future of Work

Just loving the bold bright design of this SlideShare by SprintBiz:

http://www.slideshare.net/SprintBiz/future-of-work-35928727

In addition to the great design, it has some really powerful messages:

  • When we go to work, we look for meaning; for autonomy; for recognition and affirmation.
  • And yes, for fun.
  • Only when you’ve hired the right people… Then – and only then – can you think about how to deploy new technology and to reinvent work
    • New collaboration tools can turbo-charge your teams.
    • New data, analytics and tracking tools can make you smarter.
    • Better tech can improve every process.
  • Change.  How well can you pivot?
  • Competition.  If they zig, can you zag?
  • Complexity.  Who will be the first to lighten up and simplify?
  • [It] always comes down to how well you engage your people.

What do you think?  Do you agree? +Alesandra Blakeston

5 ways to effectively probe to prevent misunderstandings

Are you good at listening and questioning?  How would you rate yourself on a scale of 1 to 10?  The sad fact is that most of us think we are good at questioning, when really the majority only listen long enough to be able to formulate an answer.  Then, when we are questioning we only go deep enough to appear interested or to validate what we think we already know.

I’m preparing some exercises for a training session on facilitation, which will of course, include listening and probing.  It made me re-evaluate my own skills, so I thought I would share some of my insights with you.  Of course, anyone who’s tried to improve their listening, questioning and probing skills will have heard phrases like “Tell me more,” and “Can you give me an example” which can be used to help dig a little deeper.  But how do you know when you’ve probed enough?

0010051796Q-849x565

I just finished reading “Taking Responsibility for Receiving Intended Information: Clarification Tactics,” a chapter in Dr. Richard Halley‘s book, Listening Models and Procedures.  In it he says that the burden of understanding is shared between the speaker and the listener.  He gives a long list of techniques to help you probe and question, but here are my favourite five!

1. Discover and understand any hidden objectives

When speaking with someone, you might realise that they have an axe to grind.  If you are not careful, it can derail your whole conversation and prevent you from reaching an understanding.  Also, if someone has a hidden goal, it can be hard to get that person to focus on the question you want answered.  Instead of ignoring the elephant in the room, saying something about it can sometimes help the person focus more on your information needs. For instance, you might say, “The reason I approached you was _____________, but it seems you would like to talk about ____________.”  This will then allow the person time to voice their feelings and then you can bring the subject back to the matter at hand.

2. Ask for specifics

Reduce ambiguity, by asking for a specific description of what the speaker is trying to convey.  It reduces the chance of misunderstanding and add depth to the discussion.  For instance, “I really appreciated the way the manager took me to one side after the meeting, and took the time to listen to what I had to say” gives us a lot more information than “I spoke with the manager.  It went well.”  When you hear a phrase that is really general, ask a probing question to discover more, “So what exactly was it about ___________ that makes you say that?”  Or “I’d appreciate it if you could expand a little more on this.  What was it about specifically?”  Of course, even when they’ve given you more detail, you can continue to probe until you’ve exhausted the subject and there is no room for misinterpretation.

0010826909P-849x565

3. Try to find what something is not

Usually we are trying to understand what something is, but it is also useful to understand what it is no.  You might be able to eliminate categories of options and avoid misinterpretations in this way, especially when someone is having a hard time expressing themselves.  Phrases like “So, it’s not that it was horrible or anything?” will help to identify a mediocre experience.

4. Be aware of your assumptions

This is difficult.  We all have internal biases that can cause us to make false assumptions.  As listeners though, it is important to be sensitive to our biases and to check out any assumptions we have made.  When one customer tells you that they had a bad experience with a product, and you’ve heard from another customer that they also had a bad experience it could be natural to assume that they had the same experience.  Check the assumptions at the door and continue to probe to ensure you have the correct understanding.  It also works with people.  When someone is being quiet and retiring, don’t assume they have nothing to say.   Instead ask, “How about you?  I bet you have something to add to what the others have said.”

Optimized-7K0A0079

5. Ask the speaker to review

We can easily miss words or misinterpret what is said.  Asking the participant to review their comments, even if we think we know what they said, gives both the speaker and the listener a chance to add more clarity to what was said.  You might say, “I know you’ve already explained that once but it would help me really understand if you could go over it one more time.”  They will naturally use different words, and this can lead to more insights.  You can also weave earlier comments into your questions to make the speaker review what they have said in another way.  It also shows that you were listening and processing what they said and the speaker does not need to recontextualize their comment.  They can then pick up where they laid off – another way of probing deeper with the subsequent response.

What questions do you use to probe and dig deeper?  Let me know!

+Alesandra Blakeston

40 going on 4

Believe it or not, I actually turn 40 this year.  I have more in common with some of my millennial friends than with the typical 40 year old woman, and yet all of my friends have been telling me that I am getting old or have asked me if it bothers me.  The truth?  No.  Not even slightly.  The fact is that in many, many ways, I am still around four years old.  Old enough to know better, but still young enough to get up to mischief  I still love super heroes (Avengers and X-Men anyone?), I still devour fantasy stories (The Hobbit, Labyrinth, Dark Crystal to name but a few) and I still love Disney (Little Mermaid, Frozen!!!).  Of course, I’ve learned a lot along the way.  I’ve been hurt, knocked down and bruised.  I’ve laughed, loved and soared high.  Frankly I wouldn’t change a thing – even the REALLY painful parts.  Since not everyone I know feels this way about themselves and their lives, I thought I would share my life lessons in the hopes that it will help not just them, but others too.

It seems as though I’ve always been little miss confident, little miss independent as you can see from the photos below.  Was this behaviour learned or inherited, I couldn’t say, you would probably have to ask my family.  I do know though that I was the first and only one in my family to go to university, the first to travel outside of Europe and the first to work abroad.  Confidence can be learned, failure embraced and learned from, and fear should be seen as a motivator.  If you’re not afraid, you are not challenging yourself enough!  Why do I say this?  I present to you my facts of life:

20140502_0947241. Children learn by making mistakes.  You learn to walk by falling, you learn to ride a bike by crashing.  This first photo shows me at the age of three checking to see if my parents are watching before I sneak off to climb my neighbour’s six foot brick wall.  Funnily, there are plenty of other similar photos of me.  I once was grounded for peddling my tricycle on the main road.  There was a long queue of traffic behind me, the first and foremost of which was a six wheeler flatbed truck.  Everyone in my street was watching the funny parade of traffic following this 3 / 4 year old child being honked and blared at by the impatient drivers.  When I eventually turned off onto my side street, the lorry driver pulled up to talk to my parents.  When he asked “Didn’t I hear him honking?”  My reply was simple. “I was peddling as fast as I could…”

I have to admit I don’t remember either of these stories actually happening, but my parents (and their friends) have told me these stories and others enough times that they (and I) are village legend.  As you can imagine, I used to scare the pants off my parents on a regular basis!  I never fell off that wall, but I am sure I fell off enough smaller walls to be confident enough to tackle the bigger one.  I’m sure you get the point I am making.  Dont’ be afraid of falling or of making mistakes. Instead, be afraid of not trying.  Life is very very boring if you play it safe!  Also, you’ll never be noticed by doing what everyone else does.

20140502_0946442. Find out what makes you special and own it!  Both of my parents worked when I was young.  It was necessary as my parents weren’t rich by any standard.  This of necessity made me independent.  I used to walk myself and my brother to school.  In the small village where we grew up, it was perfectly safe back then!  As a result, I learned responsibility young.  That being said, I distinctly remember hating the dresses I was put in to go to school.  One of the parents of a classmate of mine was a dressmaker and almost every girl in the village wore these dresses as they were of a good quality and inexpensive.  Of course, I was a tomboy and hated dresses on general principle.  The only way my mother could get me into the day’s dress was if it had the colour red in it.  (I still prefer that colour to any other).  We also compromised with my school coat.  It had to have big buttons, so that I could use it as a cape when playing G-Force or Wonder Woman in the playground.

Growing up in a family where we had very little and in a village where everyone was related and knew everyone else’s business made me determined to escape, to be better and to be different.  Luckily for me, my mother encouraged me to read and let me live and play in my fantasy world.  I was always making up stories and little fantasies where I would be the hero and save the day.  Now I work for a global corporation, and have done not one but two different secondments outside of the UK.  My job entails creativity and innovation (I get to play and teach games for a living) and being a thought leader.

That being said, I went through a horrible phase (both professionally and personally) in the early part of my career with the company.  Work and home felt like a battleground.  During that time, I was lost.  I lost sight of who I was and why I should care.  I felt mired in failure and thought I couldn’t do anything right.  Fortunately one or two amazing people mentored me and helped me to regain that self-confidence that I lost for a while. They saw potential in me and helped me to bring it out.  I won’t name them (they know who they are) but because of them, I was able to win a National Training Award for the company and for a short while flew with the stars.  It only seems fair to pay that gift forward.

The fact is that everyone has a gift.  Some more than one.  If I can help just one person find what makes them unique, special and help them to turn that gift into a talent that they can leverage, then I have done well.  One of my friends is going through a bad patch at the moment.  She’s lost faith in herself and doesn’t know which way to turn.  She’s trying to fit in and keep her head down.  I keep telling her that regardless of what others around her think and say, it’s her opinion of herself that should have the most weight.  Value yourself and others will value you too.  Being different worked for me as a child, and being different is what has made me successful as an adult.  The truly successful are unique and original.

3. Be proud of your connections and help them to develop.  As the eldest child of three brothers and sisters, I was forever hearing “Take your brother / sister with you!”  or my favourite, usually when my brother had done something stupid and of course, it was my fault,  “Why didn’t you stop him?  You know better!”  Regardless of whether I was popular or not at school (and for a while, I really wasn’t!), I always had someone tagging along.  I learned a lot about teaching and mentoring and it made me a better person because of it.  I learned patience with my brother who is three years younger than me and I learned teaching with my sister who is eight years younger than me.  When I was a teenager, she was just a little girl.  I taught her to avoid all the mistakes I made.  Now I do the same with the young people I mentor at work and the interns whom I employ.  I learned that being responsible for someone (being a leader) means looking out for their welfare and development as well as bossing them around (like big sisters do).  You have no idea how proud I am of my little brother and how far he has come, especially since the birth of his gorgeous little daughter…  I feel the same way about my interns.  There’s nothing like the pride you feel when you see someone you have encouraged succeed.  The bike in the photo below eventually became my brother’s.  Both of us started with stabilisers and eventually moved on to a bigger better bike (I eventually moved onto a motorbike – but that’s a whole other story!)

20140502_094654

4. You should never stop learning, regardless of your age.  Incidentally, I would never have gotten as good on social media and blogging if it weren’t for my younger friends helping me out and paying me back.  Just because you are older and wiser, it doesn’t mean you are better.  The younger generation has a lot to teach us about life in general (not just the latest tech and which apps you should have on your smartphone). Having a reverse mentor doesn’t just keep your outlook young, it can help you be more assertive and dare I say it more successful.  I belong to Generation X, but have Baby Boomers as friends as well as Millennials.  Believe it or not, I learned how to demand what I want from my job and my position from my millennial friends.  I learned diplomacy, tact and how to behave at work from my baby boomer friends.  No one knows everything, regardless of how old they are or how wise they are.  Being willing to change and adapt, understanding what to do with information, is more important in today’s world than being an expert.  Let’s face it, you can find anything you might wish to know with Google search.

20140502_0947165. Passion and Humour makes the day go faster.  This year, I will have worked for the company for fifteen years.  Strangely enough, that feels more like a milestone than my turning 40!  As a typical Brit, I am a master of sarcasm and wit (ask any one of my team!) and of course self-deprecating humour.  That doesn’t mean though that I don’t value myself or others.  In fact, the people that I am most fond of, usually get the most stick, myself included!  When preparing a facilitation session or a presentation, I always try to add in some fun and some jokes.  After all, if you can make people laugh, they are much more likely to remember you and by extension, your message.  After 15 years I have hundreds of co-workers (or should I say co-conspirators?), many with the same irreverent sense of humour, and I think  I have stayed so long because the company believes in its people and doesn’t take itself too seriously.  We’re passionate about what we do and it shows.  Frankly, if you can’t be passionate about your subject, then stop talking!  My blog is chock full of tips and techniques, musings and inspirations.  Above all though, it is about things that I am passionate about, whether that’s developing people, innovation, simplifying difficult topics, Excel charts or PowerPoint presentations.  As a child I grew up knowing that I was an odd little duck – my blog (and my twitter feed) is no different.  But I was given this advice by an old friend who mothered me on more than one occasion.

Stick out your tongue, tell the world to get in line, be good to your friends and march to your own tune!

Don’t you agree?

+Alesandra Blakeston

Who am I: Innovation Program Manager

A few weeks ago the communication department of the company I work for posted a soundbite video of me and how I’ve moved to the United States. This week, they are posting a video on what I do in terms of innovation.  You can watch it on YouTube below.  Hope you like it!

Enjoy!

+Alesandra Blakeston

Intern Advice

A colleague of mine has recently passed her 40th work anniversary. That’s an amazing 40 years working for the same company. As part of our women’s network, she was asked to give her advice to new young women joining the company. Very inspiring stuff. In fact, it inspired me to pass on some life learning to my interns. Here goes…

  1. If plan A doesn’t work, the alphabet has 25 more letters! Stay cool – Claire Cook
  2. Life is either a #DaringAdventure or nothing – Helen Keller
  3. Don’t be afraid of being #Different. Be afraid of being the same as everyone else – Unknown
  4. #Don’tCompare your chapter 1 to someone else’s chapter 20.  Everyone was a beginner at some point – Unknown
  5. If you are working on something #exciting that you really care about, you don’t have to be pushed.  The vision pulls you – Steve Jobs

This slideshow requires JavaScript.

Hope these inspire you!

+Alesandra Blakeston

Tough week?

I’ve been doing a lot of travelling with work recently, which I usually enjoy.  This week has been the first back in the office for a while and as a result, it’s been tough.   I was reminded of this quote by Muhammed Ali:

I know where I’m going and I know the truth, and I don’t have to be what you want me to be. I’m free to be what I want.

Since I’m probably not the only one having “a bad week”, I thought I would share.  Hope it helps you too!

quote (Small)

 

Thanks to teetasse for the photo!

+Alesandra Blakeston