Matrix BKG: an example of construction and analysis in "Excel" and "Word"

Enterprises that produce goods or provide services in a wide range, are forced to conduct a comparative analysis of the business units of the firm to make a decision on the allocation of investment resources. The maximum financial investments are given to the priority sphere of the firm's activity, which brings the maximum profit. The instrument for managing the product range is the BKG matrix, an example of construction and analysis that helps marketers make decisions about the development or liquidation of business units of the firm.

The concept and essence of the BCG matrix

Formation of long-term plans of the company, the correct allocation of financial resources between the components of the company's strategic portfolio is through the use of a tool created by the Boston Consulting Group. Hence the name of the tool is the BCG matrix. An example of building a system is based on the dependence of the relative market share on the rate of its growth.

The competitiveness of the goods is expressed by the indicator of the relative market share and is plotted along the X-axis. A high value is considered to be a value whose value is greater than one.

The attractiveness, maturity of the market is characterized by the value of the rate of its growth. Data on this parameter are plotted on the matrix along the Y axis.

After calculating the relative share and market growth rates for each good produced by the firm, the data is transferred to a system called the BCG matrix (an example of the system will be discussed below).

Matrix Quadrants

When commodity groups are distributed according to the BCG model, each assortment unit falls into one of the four quadrants of the matrix. Each quadrant has its own name and recommendations for making decisions. Below is a table consisting of the same categories as the BCG matrix, an example of construction and analysis of which can not be done without knowledge of the features of each zone.

Analysis Objects

An example of constructing and analyzing the BCG matrix is impossible without defining the goods that can be considered in the projection of this system.

1. Areas of business that are not related. This can be: hairdressing services and the production of electric kettles.
2. Assortment groups of the company, sold in one market. For example, selling apartments, renting apartments, selling houses and the like. That is, the real estate market is being considered.
3. Goods classified into one group. For example, the production of glassware, metal or ceramics.

Matrix BCG: an example of construction and analysis in Excel

To determine the product life cycle and strategic planning of the company's marketing activities, an example with fictitious data will be considered to understand the topic of the article.

The first step is to collect and collate the data in the table for the analyzed goods. This operation is simple, you need to create a table in Excel and enter information about it in the company.

The second step is the calculation of market indicators: the rate of growth and the relative share. To do this, you need to enter formulas for automatic calculation in the cells of the created table:

• In cell E3, in which the market growth rate will be, this formula looks like this: = C3 / B3. If it turns out a lot of signs after the decimal point, then you need to reduce the bit depth to two.
• The procedure is similar for each product.
• In cell F9, which is responsible for the relative market share, the formula looks like this: = C3 / D3.

The result is a completed table.

According to the table, it is clear that sales of the first product fell by 37% in 2015, and for goods 3 increased by 49%. Competitiveness or relative market share in the first category of goods is lower than that of competitors by 47%, however, for the third and fourth products, 33% and 26%, respectively, are higher.

Graphical display

Based on the data in the table, the BCG matrix is constructed, the example of construction in the Excel which is based on the choice of the "Bubble" type diagram.

After selecting a chart type, an empty field appears, by right-clicking on the window you need to call up a window to select the data to fill in the future matrix.

Adding a row, its data is filled. Each row is the goods of the enterprise. For the first product, the data will be as follows:

1. The row name is cell A3.
2. The X-axis is cell F3.
3. The Y-axis is cell E3.
4. The size of the bubble is cell C3.

Thus, the BCG matrix is created (for all four benefits), an example of building the rest of the goods is similar to the first one.

Changing the axis format

When the diagram graphically displays all the goods, it is necessary to break it into quadrants. This distinction is made by the X, Y axes. It will only be necessary to change the automatic settings of the axes. Clicking the mouse on the vertical scale selects the "Format" tab and the "Format of Selected" window is displayed on the left side of the panel.

Changing the vertical axis:

• The minimum value is "0".
• The maximum value is the average SDR multiplied by 2: (0.53 + 0.56 + 1.33 + 1.26) / 4 = 0.92; 0.92 * 2 = 1.84.
• The main and intermediate divisions are the average SDR.
• The intersection with the X axis is the mean SDR.

Changing the horizontal axis:

• The minimum value is "0".
• The maximum value is "2".
• The remaining parameters are "1".

The resulting diagram is the BCG matrix. An example of construction and analysis of such a model will give an answer about the priority development of the assortment units of the firm.

Signatures

To finalize the construction of the BCG system, it remains to create signatures of axes and quadrants. It is necessary to select the diagram and refer to the section of the "Layout" program. Using the "Inscription" icon, the cursor is moved to the first quadrant and its name is written. This procedure is repeated in the next three zones of the matrix.

To create the name of the diagram, which is located in the center of the BCG model, the icon with the same name, following from the "Caption", is selected.

Following from the left to the right in the Excel 2010 toolbar of the "Layout" section, similar to the previous ones, the axes signatures are created. As a result, the BCG matrix, an example of construction in Excel which was considered, has the following form:

Analysis of assortment units

Building a diagram of the dependence of the market share on the rate of its growth is half the solution to the task of strategic marketing. The responsible moment is the correct interpretation of the position of goods in the market and the choice of further actions (strategies) for their development or liquidation. The BCG matrix, an example of analysis:

Item No. 1, is located in the zone of low market growth rates and relative share. This commodity unit has already passed its life cycle and it does not bring profit to the company. In the real situation, it would be necessary to conduct a detailed analysis of such goods and determine the conditions for their release in the absence of profit from their sale. Theoretically, it is better to exclude this commodity group and release the released resources for the development of prospective goods.

Item No. 2 is in a growing market, but it requires investment to increase competitiveness. Is a promising commodity.

Product number 3 is at the peak of its life cycle. This type of assortment unit has high indicators of SDT and market growth rates. An increase in investments is required, so that in the future the business unit of the firm that produces this product would bring a stable income.

Item No. 4 is a profit generator. The money received by the company from the sale of this category of the assortment unit is recommended to be directed to the development of goods No. 2, 3.

Strategies

An example of the construction and analysis of the BCG matrix facilitates the identification of the following four strategies.

1. Increase in market share. Such a development plan is acceptable to goods located in the zone of "Wild Cats", with a view to their transition to the "Stars" quadrant.
2. Preservation of market share. To obtain a stable income from the "Dairy Cows" it is recommended to apply this strategy.
3. Decrease in market share. We apply the plan to weak "Dairy Cows", "Dogs" and unpromising "Wild Cats".
4. Elimination is a strategy for the "Dogs" and the hopeless "Wild Cats."

The BCG matrix: an example of construction in a Word

The method of constructing the model in the "Word" is more laborious and not entirely clear. An example will be considered based on the data that were used to build the matrix in Excel.

The column "Market Growth Rate" appears, the values of which are calculated as follows: (1-data growth rate) * 100%.

A table of four rows and columns is constructed. The first column merges into one cell and is signed as "Market Growth Rate". In the remaining columns, you need to merge the rows in pairs to get two large cells at the top of the table and two lines left at the bottom. As in the figure.

In the lowest line will be the coordinate "Relative market share", above it - the value: less or more 1. Referring to the table data (to the last two columns), the definition of goods by quadrants begins. For example, for the first product, SDR = 0.53, which is less than one, then its location will be either in the first or in the fourth quadrant. The rate of market growth is a negative value of -37%. Since the growth rate in the matrix is divided by a value of 10%, then unambiguously the product under number 1 falls into the fourth quadrant. The same distribution occurs with the remaining assortment units. The result should coincide with the Excel chart.

Matrix BKG: an example of construction and analysis determines the strategic positions of the assortment units of the firm and participates in making decisions about the allocation of the resources of the enterprise.