In this tutorial you will learn how to create BCG Matrix in Excel. BCG Matix is a chart which show you potential of your company’s products. BCG stands for Boston Consulting Group – this company created this chart for the first time.
Preparing Data to BCG Matrix
First you need some data. You need market growth, market share for all of your products and relative market share.
To get relative market share just divide market share of your product and market share of your largest competitor.
Inserting BCG Matrix
To insert BCG Matrix in Excel select columns A, B and C for all products. Market Growth will be values of Y axis. Market Share will be the bubble in the chart.
Go to Ribbon > Insert > Other Charts and click Bubble Chart
Bubble Chart inserted
Now change X-axis values. Right-click X-axis values and go to Select Data.
Click Edit to change source of data.
You have to change series X values.
Select Relative Market Share values. It is column E in this example.
Your BCG Matrix chart is a bit changed. Now, all values are correct.
Let’s continue formatting of X Axis. Right-click X-axis and go to Format Axis. Click Values in reverse Order and change Axis Value to 1 (it depends on your market!).
Your Bubble Chart starts to be similar to BCG Matrix.
Click the chart. Go to Ribbon > Layout tab. Click:
- Gridlines > Primary Horizontal Gridlines > None
- Legend > None
- Add both Axis Titles
Right-click Y-axis and go to Format Axis. Change Axis value to 0,1 (also depends on yor market!)
BCG Matrix is almost ready but needs some formatting
Do some formatting:
- Go to Ribbon > Insert > Text Box and insert box in the chart to create some labels
- Change titles of chart and axes
BCG Matrix is ready.
This is a portfolio of your products:
- Product3 is your star. You will earn money on this product.
- Product2 is a cash cow. This is product which is giving you money now.
- Product4 is a qustion mark. You should invest money in this product.
- Product1 and Product5 are dogs. Just don’t sell them any more.
I prepared template of BCG Matrix. You can download it here for free.