Построение матрицы БКГ в Microsoft Excel

Матрица БКГ является одним из самых популярных инструментов маркетингового анализа. С её помощью можно избрать наиболее выгодную стратегию по продвижению товара на рынке. Давайте выясним, что представляет собой матрица БКГ и как её построить средствами Excel.

Матрица БКГ

Матрица Бостонской консалтинговой группы (БКГ) – основа анализа продвижения групп товаров, которая базируется на темпе роста рынка и на их доле в конкретном рыночном сегменте.

Согласно стратегии матрицы, все товары разделены на четыре типа:

  • «Собаки»;
  • «Звёзды»;
  • «Трудные дети»;
  • «Дойные коровы».

«Собаки» — это товары, имеющие малую долю рынка в сегменте с низким темпом роста. Как правило, их развитие считается нецелесообразным. Они являются неперспективными, их производство следует сворачивать.

«Трудные дети» — товары, занимающие малую долю рынка, но на быстро развивающемся сегменте. Данная группа имеет также ещё одно название – «тёмные лошадки». Это связано с тем, что у них имеется перспектива потенциального развития, но в то же время они требуют для своего развития постоянных денежных вложений.

«Дойные коровы» — это товары, занимающие значительную долю слабо растущего рынка. Они приносят постоянный стабильный доход, который компания может направлять на развитие «Трудных детей» и «Звезд». Сами «Дойные коровы» вложений уже не требуют.

«Звёзды» — это наиболее успешная группа, занимающая существенную долю на быстрорастущем рынке. Эти товары уже в настоящее время приносят значительный доход, но вложения в них позволят этот доход ещё больше увеличить.

Суть матрицы БКГ в Microsoft Excel

Задачей матрицы БКГ является определение того, к какой из данных четырех групп можно отнести конкретный вид товара для того, чтобы проработать стратегию его дальнейшего развития.

Создание таблицы для матрицы БКГ

Теперь на конкретном примере построим матрицу БКГ.

  1. Для нашей цели возьмем 6 видов товаров. Для каждого из них нужно будет собрать определенную информацию. Это объем продаж за текущий и предыдущий период по каждому наименованию, а также объем продаж у конкурента. Все собранные данные заносим в таблицу.
  2. Первичные данные для построения матрицы БКГ в Microsoft Excel
  3. После этого нам нужно рассчитать темп роста рынка. Для этого нужно разделить по каждому наименованию товара величину продаж за текущий период на величину продаж за предыдущий период.
  4. Расчет темпа роста рынка в Microsoft Excel
  5. Далее рассчитываем для каждого товара относительную долю рынка. Для этого объем продаж за текущий период нужно разделить на объем продаж у конкурента.
Расчет относительной доли рынка в Microsoft Excel

Построение диаграммы

После того, как таблица заполнена исходными и расчетными данными, можно приступать к непосредственному построению матрицы. Для этих целей более всего подойдет пузырьковая диаграмма.

  1. Перемещаемся во вкладку «Вставка». В группе «Диаграммы» на ленте щелкаем по кнопке «Другие». В открывшемся списке выбираем позицию «Пузырьковая».
  2. Переход к созданию пузырьковой диаграммы в Microsoft Excel
  3. Программа попытается построить диаграмму, скомплектовав данные, как она считает нужным, но, скорее всего, эта попытка окажется неверной. Поэтому нам нужно будет помочь приложению. Для этого щелкаем правой кнопкой мыши по области диаграммы. Открывается контекстное меню. Выбираем в нем пункт «Выбрать данные».
  4. Переход к выбору данных в Microsoft Excel
  5. Открывается окно выбора источника данных. В поле «Элементы легенды (ряды)» кликаем по кнопке «Изменить».
  6. Выбор источника данных в Microsoft Excel
  7. Открывается окно изменения ряда. В поле «Имя ряда» вписываем абсолютный адрес первого значения из столбца «Наименование». Для этого устанавливаем курсор в поле и выделяем соответствующую ячейку на листе.

    В поле «Значения X» таким же образом заносим адрес первой ячейки столбца «Относительная доля рынка».

    В поле «Значения Y» вносим координаты первой ячейки столбца «Темп роста рынка».

    В поле «Размеры пузырьков» вносим координаты первой ячейки столбца «Текущий период».

    После того, как все вышеперечисленные данные введены, жмем на кнопку «OK».

  8. Настройка изменения ряда пузырчатой диаграммы в Microsoft Excel
  9. Аналогичную операцию проводим для всех остальных товаров. Когда список полностью будет готов, то в окне выбора источника данных жмем на кнопку «OK».
Завершение работы в окне выбора источника данных в Microsoft Excel

После этих действий диаграмма будет построена.

Диаграмма построена в Microsoft Excel

Урок: Как сделать диаграмму в Экселе

Настройка осей

Теперь нам требуется правильно отцентровать диаграмму. Для этого нужно будет произвести настройку осей.

  1. Переходим во вкладку «Макет» группы вкладок «Работа с диаграммами». Далее жмем на кнопку «Оси» и последовательно переходим по пунктам «Основная горизонтальная ось» и «Дополнительные параметры основной горизонтальной оси».
  2. Переход к настройкам горизонтальной оси в Microsoft Excel
  3. Активируется окно параметров оси. Переставляем переключатели всех значений с позиции «Авто» в «Фиксированное». В поле «Минимальное значение» выставляем показатель «0,0», «Максимальное значение»«2,0», «Цена основных делений»«1,0», «Цена промежуточных делений»«1,0».

    Далее в группе настроек «Вертикальная ось пересекает» переключаем кнопку в позицию «Значение оси» и в поле указываем значение «1,0». Щелкаем по кнопке «Закрыть».

  4. Настройка горизонтальной оси в Microsoft Excel
  5. Затем, находясь все в той же вкладке «Макет», опять жмем на кнопку «Оси». Но теперь последовательно переходим по пунктам «Основная вертикальная ось» и «Дополнительные параметры основной вертикальной оси».
  6. Переход к настройкам вертикальной оси в Microsoft Excel
  7. Открывается окно настроек вертикальной оси. Но, если для горизонтальной оси все введенные нами параметры постоянные и не зависят от вводных данных, то для вертикальной некоторые из них придется рассчитать. Но, прежде всего, как и в прошлый раз, переставляем переключатели из позиции «Авто» в позицию «Фиксированные».

    В поле «Минимальное значение» устанавливаем показатель «0,0».

    А вот показатель в поле «Максимальное значение» нам придется высчитать. Он будет равен среднему показателю относительной доли рынка умноженному на 2. То есть, в конкретно нашем случае он составит «2,18».

    За цену основного деления принимаем средний показатель относительной доли рынка. В нашем случае он равен «1,09».

    Этот же показатель следует занести в поле «Цена промежуточных делений».

    Кроме того, нам следует изменить ещё один параметр. В группе настроек «Горизонтальная ось пересекает» переставляем переключатель в позицию «Значение оси». В соответствующее поле опять вписываем средний показатель относительной доли рынка, то есть, «1,09». После этого жмем на кнопку «Закрыть».

  8. Настройка вертикальной оси в Microsoft Excel
  9. Затем подписываем оси матрицы БКГ по тем же правилам, по которым подписываем оси на обычных диаграммах. Горизонтальная ось будет носить название «Доля рынка», а вертикальная – «Темп роста».
Матрица БКГ готова в Microsoft Excel

Урок: Как подписать оси диаграммы в Excel

Анализ матрицы

Теперь можно проанализировать получившуюся матрицу. Товары, согласно своему положению на координатах матрицы, делятся на категории следующим образом:

  • «Собаки» — нижняя левая четверть;
  • «Трудные дети» — верхняя левая четверть;
  • «Дойные коровы» — нижняя правая четверть;
  • «Звезды» — верхняя правая четверть.

Таким образом, «Товар 2» и «Товар 5» относятся к «Собакам». Это означает, что их производство нужно сворачивать.

«Товар 1» относится к «Трудным детям» Этот товар нужно развивать, вкладывая в него средства, но пока он должной отдачи не дает.

«Товар 3» и «Товар 4» — это «Дойные коровы». Данная группа товаров уже не требует значительных вложений, а выручку от их реализации можно направить на развитие других групп.

«Товар 6» относится к группе «Звёзд». Он уже приносит прибыль, но дополнительные вложения денежных средств способны увеличить размер дохода.

Как видим, с помощью инструментов программы Excel построить матрицу БКГ не так уж и сложно, как это может показаться на первый взгляд. Но основой для построения должны служить надежные исходные данные.

Помогла ли Вам статья?
Да Нет
Задайте вопрос или оставьте мнение
Получить ответ на Email
Уведомить о

7 ответов
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
Елена
17 января 2018 14:48

Я Вас благодарю, за четкое объяснение. Неоценимая помощь!!!

Аноним
12 декабря 2021 21:39

Построила матрицу по Вашему способу, получилась лабуда

Анна
24 августа 2023 20:50

Огромное спасибо за статью. Без нее не смогла бы построить матрицу. Все очень четко и понятно описано.

Елена
18 января 2018 02:51

Подскажите, пжл, если у всех трех продуктов относительная доля рынка не превышает 0,23, как построить вертикальную ось, если горизонтальную выстраивам по значению мах 2, а min 1. При том, что темпы роста более 30%

Дарья
4 марта 2019 16:54

Максим, здравствуйте!
Спасибо за информацию.
Скажите, пожалуйста, почему берете в расчет одного конкурента? А если их несколько? К примеру, продукция моей компании занимает в целом 25% рынка (0,25). Это меньше единицы. При этом при построении вертикальной оси все продукты улетают за пределы диаграммы. Что то делаю не так?

Аноним
27 апреля 2022 07:05

Нужно построить матрицу по формуле. а не диаграмму или график

Инструкции по операционным системам:
AndroidAndroid iOSiOS Windows 11Windows 11 Windows 10Windows 10 Windows 7Windows 7 Windows 8Windows 8 Windows XPWindows XP Общее по компьютерамОбщее по компьютерам LinuxLinux macOSmacOS
Инструкции по мессенджерам и соц.сетям:
ВконтактеВконтакте ОдноклассникиОдноклассники TelegramTelegram ViberViber WhatsAppWhatsApp SkypeSkype ZoomZoom InstagramInstagram
Инструкции по работе в программах:
WordWord ExcelExcel PowerPointPowerPoint OutlookOutlook DirectXDirectX PhotoshopPhotoshop LightroomLightroom Premiere ProPremiere Pro Google ChromeGoogle Chrome Яндекс БраузерЯндекс Браузер Mozilla FirefoxMozilla Firefox OperaOpera Internet ExplorerInternet Explorer Уроки по остальным браузерамПо остальным браузерам Расширения для браузеровРасширения браузеров Уроки по антивирусамПо антивирусам AvastAvast AudacityAudacity Apple IDApple ID AutoCADAutoCAD BandicamBandicam BlueStacksBlueStacks DAEMON ToolsDAEMON Tools DiscordDiscord Epic GamesEpic Games FL StudioFL Studio HamachiHamachi iСloudiСloud iTunesiTunes OBSOBS OriginOrigin Play MarketPlay Market Sony VegasSony Vegas SpotifySpotify SteamSteam TeamViewerTeamViewer The Bat!The Bat! TikTokTikTok TunngleTunngle UltraISOUltraISO uTorrentuTorrent VirtualBoxVirtualBox Торрент клиентыТоррент клиенты Яндекс ДискЯндекс Диск Яндекс.КартыЯндекс.Карты Яндекс.НавигаторЯндекс.Навигатор В остальных программахВ остальных программах Драйвера для устройствДрайвера Подборки программПодобрать программу Остальные программыСкачать программы
Инструкции по онлайн-сервисам:
ЯндексЯндекс GoogleGoogle ДзенДзен YouTubeYouTube FacebookFacebook TwitterTwitter AliExpressAliExpress AvitoAvito ChatGPTChatGPT QiwiQiwi Другие онлайн-сервисыДругие сервисы Все о почтеВсе о почте Яндекс почтаЯндекс почта GmailGmail Почта Mail.ruПочта Mail.ru Рамблер почтаРамблер почта
Инструкции по телефонам:
SamsungSamsung XiaomiXiaomi HuaweiHuawei Прошивки устройствПрошивки телефонов ТелефонОбщие по телефонам
Инструкции по периферийным устройствам:
МышкаМышка КлавиатураКлавиатура ПринтерПринтер РоутерРоутер ФлешкаФлешка Карта памятиКарта памяти HDMIHDMI Игровые консолиИгровые консоли Яндекс СтанцияЯндекс Станция AirPodsAirPods
Инструкции по комплектующим:
Материнская платаМатеринская плата ПроцессорПроцессор ВидеокартаВидеокарта SSD дискиSSD диски Жесткий дискЖесткий диск BIOSBIOS