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

Enterprises that produce goods orproviding services in a large 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 instrument is the matrix of the BCG. 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 characteristics of each zone.

Wild cats A zone of new goods. High level of sales. The need for investment for further development. In the short term, a low rate of return.	Stars Leaders of a growing market. High level of sales. Growing profits. Investing in significant investments.
Dogs Unpromising products: a new group that has failed or the goods of an unattractive (falling) market. Low income. Desirable disposal of them or termination of investment.	Cash Cows Goods of the market with a falling sales level. Stable profit. Lack of growth. Minimum costs for holding positions. Distribution of income for promising groups of goods.

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 BKG: 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 the data about the enterprise into it.

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 shows that the sale of the firstgoods 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 diagram according to the "Bubble" type.

After selecting a chart type, an empty field appears, by right-clicking on the window, you will be asked to select 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 the construction of other goods is similar to the first one.

Changing the axis format

When the diagram graphically displays allgoods, 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, the "Format" tab is selected 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 average 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 constructing and analyzing 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 systemThe BCG 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 left to right in the Excel toolbar2010 section of the "Layout" is similar to the previous inscriptions, signatures of axes 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 relationship between the market share ofThe pace of its growth is half the solution of the task of strategic marketing. The responsible moment is the correct interpretation of the position of goods on the market and the choice of further actions (strategies) for their development or liquidation. The BCG matrix, an example of analysis:

Item No. 1, located in the zone of low indicatorsmarket growth and relative share. This commodity unit has already passed its life cycle and it does not bring profit to the company. In a 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.

Item No. 3 is at the peak of its lifecycle. 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 for goods located in the zone "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 will apply the plan to weak "Dairy cows", "Dogs" and unpromising "Wild cats".
4. Elimination is a strategy for "Dogs" and unpromising "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.

Product	Revenue, den.		The volume of sales of the leading competitor, den.ed.	Estimated figures		Rate of market growth,%
	2014	2015		The growth rate of the market	Relative market share
Product 1	521	330	625	0,63	0,53	-37
Product 2	650	900	1600	1,38	0,56	62
Product 3	806	1200	901	1,49	1,33	51
Product 4	1500	1050	836	0,70	1,26	-30

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

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

Rate of market growth	High (more than 10%)	1 Item No. 1	2 Item No. 2
	Low (less than 10%)	4 Item No. 4	3 Item No. 3
		Low (less than 1)	High (more than 1)
		Relative market share

The bottom line will be the coordinate"Relative market share", above it - values: 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, the 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 company's assortment units and participates in making decisions about the distribution of the company's resources.