How to Make Boston BCG Matrix in Excel
In this tutorial, you will learn how to create a BCG Matrix in Excel. The BCG Matix is a chart that shows the potential of your company’s products. BCG stands for Boston Consulting Group; this company created this chart for the first time. The other name is the Ansoff Matrix.
The BCG Matrix is a tool that shows the situation in a company compared to its biggest competitors. It is the basis for planning the strategic future of a company. On the BCG Matrix, the axes report market share (horizontal) and market growth (vertical). Assessing the evaluation of these indicators allows you to classify the product while highlighting the 4 roles of the product that goes, sells or produces.
Why Make a BCG Matrix in Excel?
The BCG Matrix is a valuable tool for businesses of all sizes. It can help you to:
- Assess your product portfolio and identify areas for improvement.
- Make strategic decisions about which products to invest in and which products to divest.
- Track the performance of your product portfolio over 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 the market share of your product by the market share of your largest competitor.
Inserting a BCG Matrix
To insert the BCG Matrix into Excel, select columns A, B and C for all products. Market Growth will be measured using the values of Y axis. Market Share will be the bubble in the chart.
Go to Ribbon > Insert > Other Charts and click Bubble Chart.
This is what the basic bubble chart looks like.
For further formatting, change the X-axis values. Right-click X-axis values and choose Select Data.
Click Edit to change the source of data.
You have to change the 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 the formatting of the X Axis. Right-click the 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 look similar to the BCG Matrix.
Click the chart. Go to the Ribbon > Layout tab. Click:
- Gridlines > Primary Horizontal Gridlines > None
- Legend > None
- Add both Axis Titles
Right-click the Y-axis and go to Format Axis. Change Axis value to 0,1 (also depends on yor market!)
The BCG Matrix is almost ready but needs some more 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 a template of the BCG Matrix. You can download it here for free.
Leave a Reply