Making BCG Matrix in Excel – How To

1
5810

BCG matrix is a strategic decision making technique that helps in resource allocation among different cost centers or cost objects by categorizing or ranking them based on their ability to generate cash inflows against cash consumption or cash outflows.

BCG matrix helps putting products in four different quadrants of graph based on their relative market share and market growth. Such presentation help take prioritizing and deciding what product to be given how much attention and resources.

Here is the BCG matrix built right in Excel:

bcg2

Its really easy to make. All you need is an appropriate data and the way to plot the bubble chart with few tweaks if needed. Following are step-by-step to get the chart with all the needed bits and rest is simple formatting:

Step 1: For BCG matrix the data is quite straight forward. Following is the data I used:

bcg3

All the numbers are given except the relative market share which is calculated by taking the ration of Market share % with best competitor market share.

Step 2: Once we have the data, simply select ONLY the figures of the last three columns i.e. relative market share, sales revenue and Market growth %. Go to Insert > charts group > click scatter chart button > click bubble chart. A chart will be inserted that is probably not what we require.

bcg4

Step 3: In my case as chart isn’t plotted against with the right variables on y axis, so it will require an adjustment. Its easy to do. Simply click on any bubble to select the series and shuffle the quadrants of revenue and Market growth with each other. This will fix the chart.

bcg5

Now that we have our chart ready, we need to make cosmetic changes to it like moving both axis in the middle to make four quadrants and give it a nice background to make things easy to understand.

Step 4: Right click on x-axis > click format axis. Change the minimum bound to 0 under axis options.

“Under vertical axis crosses” select axis value and mention half of maximum relative market share is in your data or any other suitable figure. As my data is plotted till “2.00” and I have “0.0” as the minimum so half is 1.00.

Click Labels and select low from the list of label position. I also made changes to axis color to make more prominent.

bcg6

Step 5: Right click on y-axis and click format axis. Set the maximum value to 1.0.

Under “Horizontal axis crosses” select at value and mention 0.5

Click labels and for label position select low.

Again, I change the color of the axis and also made it a little thick to make it prominent.

bcg7

Step 6: For background we need an image divided in four quadrants preferably with different colors. I used Excel to do that for me. And made as following:

bcg7

Step 7: Now here is the nifty trick. Click “print screen” button on your keyboard and go to MS Paint and have it pasted. Once pasted, select the needed part and click Cut.

bcg8

Step 8: Once selected and cut, go back to Excel right click inside chart > click format plot area. Under fill select picture or texture fill. Automatically Excel will pull the latest graphics from clipboard.

bcg9

This is basically it!

With few more formatting tweaks like changing chart title, your chart is ready. However, I did one additional step so that I can put x-axis and y-axis labels as well. And for that I shifted the graph to another worksheet. Aligned it with cells. Added labels around it and then used camera tool to capture that range and show under the data range.

bcg10

Once formatted simply select the area and hit Camera button to capture it. And then go to worksheet where you want picture to be pasted and simply left click and Excel will put it there. You can change the dimension of picture just like normal one but to keep the aspect ratio intact I held SHIFT key on the keyboard and changed its size using only diagonal points.

bcg11

Next I added labels to the products so that one can easily recognize which product is plotted where. To get the names however, there is another trick involved and that is to get the labels from the range of cells.

bcg12

And here is the final result with added borders around bubbles:

bcg2

Download Fully Worked File
You can download the fully worked file free of cost but if you like the effort and gained something valuable then you can set the price whatever you deem fit and pay. If you want to download it for free then simply set the price to “0”. Enjoy!
Download Now

Check out more Excel tutorials related to business analysis

  1. Dynamic Financial Scenario Analysis using Excel
  2. Break-even + Target profit analysis with Excel Goal Seek – How To
  3. Dynamic Common Size and DuPont Analysis Financial Statements in Excel
  4. ABC Inventory Analysis using Excel Charts
  5. 10 ways to present variance analysis reports in Excel

1 COMMENT

LEAVE A REPLY