Матриця БКГ є одним з найпопулярніших інструментів маркетингового аналізу. З її допомогою можна обрати найбільш вигідну стратегію з просування товару на ринку. Давайте з'ясуємо, що являє собою матриця БКГ і як її побудувати засобами 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 побудувати матрицю БКГ не так вже й складно, як це може здатися на перший погляд. Але основою для побудови повинні служити надійні вихідні дані.