Побудова матриці БКГ в Microsoft Excel

Матриця БКГ є одним з найпопулярніших інструментів маркетингового аналізу. З її допомогою можна обрати найбільш вигідну стратегію з просування товару на ринку. Давайте з'ясуємо, що являє собою матриця БКГ і як її побудувати засобами Excel.

матриця БКГ

Матриця Бостонської консалтингової групи (БКГ) - основа аналізу просування груп товарів, яка базується на темпі зростання ринку і на їх частці в конкретному ринковому сегменті.

Згідно зі стратегією матриці, всі товари розділені на чотири типи:

• «Собаки»;
• «Зірки»;
• «Важкі діти»;
• «Дійних корів».

«Собаки» - це товари, які мають малу частку ринку в сегменті з низьким темпом росту. Як правило, їх розвиток вважається недоцільним. Вони є безперспективними, їх виробництво слід згортати.

«Важкі діти» - товари, що займають малу частку ринку, але на швидко розвивається сегменті. Дана група має також ще одну назву - «темні конячки». Це пов'язано з тим, що у них є перспектива потенційного розвитку, але в той же час вони вимагають для свого розвитку постійних грошових вкладень.

«Дійних корів» - це товари, що займають значну частку слабо зростаючого ринку. Вони приносять постійний стабільний дохід, який компанія може направляти на розвиток «Важких дітей» і «Зірок». Самі «дійних корів» вкладень вже не вимагають.

«Зірки» - це найбільш успішна група, що займає істотну частку на швидко зростаючому ринку. Ці товари вже в даний час приносять значний дохід, але вкладення в них дозволять цей дохід ще більше збільшити.

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

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

Тепер на конкретному прикладі побудуємо матрицю БКГ.

1. Для нашої мети візьмемо 6 видів товарів. Для кожного з них потрібно буде зібрати певну інформацію. Це обсяг продажів за поточний і попередній період по кожному найменуванню, а також обсяг продажів у конкурента. Всі зібрані дані заносимо в таблицю.
2. Після цього нам потрібно розрахувати темп зростання ринку. Для цього потрібно розділити по кожному найменуванню товару величину продажів за поточний період на величину продажів за попередній період.
3. Далі розраховуємо для кожного товару відносну частку ринку. Для цього обсяг продажів за поточний період потрібно розділити на обсяг продажів у конкурента.

побудова діаграми

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

1. Переміщаємося у вкладку «Вставка». У групі «Діаграми» на стрічці клацаємо по кнопці «Інші». У списку вибираємо позицію «Бульбашкова».
2. Програма спробує побудувати діаграму, скомплектувати дані, як вона вважає за потрібне, але, швидше за все, ця спроба виявиться невірною. Тому нам потрібно буде допомогти з додатком. Для цього клацаємо правою кнопкою миші по області діаграми. Відкривається контекстне меню. Вибираємо в ньому пункт «Вибрати дані».
3. Відкривається вікно вибору джерела даних. В поле «Елементи легенди (ряди)» натискаємо на кнопку «Змінити».
4. Відкривається вікно зміни ряду. В поле «Ім'я ряду» вписуємо абсолютний адресу першого значення колонки «Найменування». Для цього встановлюємо курсор в поле і виділяємо відповідну клітинку на аркуші.

   В поле «Значення X» таким же чином заносимо адресу першого осередку стовпця «Відносна частка ринку».

   В поле «Значення Y» вносимо координати першої комірки стовпчика «Темп росту ринку».

   В поле «Розміри бульбашок» вносимо координати першої комірки стовпчика «Поточний період».

   Після того, як всі вищеперелічені дані введені, тиснемо на кнопку «OK».

5. Аналогічну операцію проводимо для всіх інших товарів. Коли список повністю буде готовий, то у вікні вибору джерела даних тиснемо на кнопку «OK».

Після цих дій діаграма буде побудована.

урок: Як зробити діаграму в Ексель

Налаштування осей

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

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

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

3. Потім, перебуваючи все в тій же вкладці «Макет», знову тиснемо на кнопку «Осі». Але тепер послідовно переходимо по пунктам «Основна вертикальна вісь» і «Додаткові параметри основної вертикальної осі».
4. Відкривається вікно налаштувань вертикальної осі. Але, якщо для горизонтальній осі все введені нами параметри постійні і не залежать від вступних даних, то для вертикальної деякі з них доведеться розрахувати. Але, перш за все, як і в минулий раз, переставляємо перемикачі з позиції «Авто» в позицію «Фіксовані».

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

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

   За ціну основного поділу приймаємо середній показник відносної частки ринку. У нашому випадку він дорівнює «1,09».

   Цей же показник слід занести в поле «Ціна проміжних поділок».

   Крім того, нам слід змінити ще один параметр. У групі налаштувань «Горизонтальна вісь перетинає» переставляємо перемикач в позицію «Значення осі». У відповідне поле знову вписуємо середній показник відносної частки ринку, тобто, «1,09». Після цього тиснемо на кнопку «Закрити».

5. Потім підписуємо осі матриці БКГ за тими ж правилами, за якими підписуємо осі на звичайних діаграмах. Горизонтальна вісь буде носити назву «Частка ринку», а вертикальна - «Темп росту».

урок: Як підписати осі діаграми в Excel

аналіз матриці

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

• «Собаки» - нижня ліва чверть;
• «Важкі діти» - верхня ліва чверть;
• «Дійних корів» - нижня права чверть;
• «Зірки» - верхня права чверть.

Таким чином, «Товар 2» і «Товар 5» відносяться до «Собакам». Це означає, що їх виробництво потрібно згортати.

«Товар 1» відноситься до «Важким дітям» Цей товар потрібно розвивати, вкладаючи в нього кошти, але поки він належної віддачі не дає.

«Товар 3» і «Товар 4» - це «дійних корів». Дана група товарів уже не вимагає значних вкладень, а виторг від їх реалізації можна спрямувати на розвиток інших груп.

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

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

• ← Застосування спеціальної вставки в Microsoft Excel
• Обчислення функції Лапласа в Microsoft Excel →