Модифицированный BCG анализ
Цель: освоить методику модифицированного BCG анализа ассортимента.
Задачи:
1) применить на практике полученные при изучении теоретической части знания;
2) ознакомиться с примером проведения модифицированного BCG анализа;
3) выполнить расчетное задание по приведенным исходным данным.
Ход анализа описан для программного обеспечения MS Office 2010 и компьютера с мышью.
Теоретические основы модифицированного BCG анализа ассортимента
Существуют разные методики проведения модифицированного BCG анализа ассортимента. Предлагаемая ниже методика отличается от той, которая приводится в лекции. У приведенной ниже методики есть ограничение: она может применяться только если в анализируемой группе товаров есть товары с ростом объемов продаж за рассматриваемые периоды. Если объемы продаж у всех товаров падают, то данная методика не применима.
Целью анализа является определение направления усилий по работе с каждым товаром.
Изучаемый параметр – объем продаж за весь период наблюдений. У каждого товара есть два признака: объем продаж (большой, малый) и рост объема продаж (большой, малый).
Все товары разбиваются на 4 категории:
· С большим объемом продаж и малым его ростом. Это – «дойные коровы ». Они обычно приносят самую большую прибыль, но развивать и продвигать их уже не стоит, их жизненный цикл заканчивается. Ресурсы им не выделяют, а прибыль, получаемую от их продаж, направляют на развитие других товаров. Эти товары обеспечивают работу организации в настоящее время.
· С большим объемом продаж и большим его ростом. Это – «звезды ». Им выделяются большие ресурсы, объем их производства следует увеличивать. Эти товары – гарантия успеха в ближайшем будущем.
· С малым объемом продаж и большим его ростом. Это – «знаки вопроса ». Еще неясно, станут ли они звездами. Им выделяют определенный объем ресурсов, но относятся к ним критически. Без этих товаров организация не сможет работать в отдаленном будущем.
· С малым объемом продаж и малым его ростом. Это – «собаки ». Рекомендуется рассмотреть вопрос снятия их с производства.
Проблема состоит в определении границ между высоким и низким уровнями продаж и его роста. В модифицированном варианте границы проводятся исходя только из данных по ассортименту анализируемой организации.
Принимается, что граница между высоким и низким ростом проходит по уровню нулевого роста: большим ростом считается любой рост объема продаж, а малым – снижение объема продаж.
В рамках данного задания принимается, что граница между большим и малым объемом продаж проходит по уровню 0,5 (доля объема продажа в совокупном объеме продажа 50%) от объема продаж товара-лидера. Рост объема продаж характеризуетсянаклоном линейного тренда, сглаживающего объемы продаж (объяснения см. ниже в Примере расчета).
Для единообразия анализа точки в координатах {объем продаж, рост объема продаж} удобно расположить в следующей области:
· объем продаж от 0 до 1; единице соответствует объем продаж товара-лидера;
· рост объема продаж: от -1 до 1, где 1 соответствует наибольшему росту объема продаж.
Пример расчета для проведения модифицированного BCG анализа
Исходные данные для расчетного примера
Процесс анализа иллюстрируется на примере.
Вариант задания содержит данные об объемах продаж по периодам. Период принимается равным одному году, чтобы упростить задачу, исключив сезонность. Однако принимается допущение, что за все время анализа товары находятся на одной и той же стадии жизненного цикла: по всем периодам продолжается внедрение, рост, зрелость или спад. Исходя из сделанного допущения, можно заменить реальные кривые изменения объема выпуска на отрезки прямых линий, то ест линейным трендом с формулой
y(x)=a+bx,
где: x – период (x=1, 2, …, N); y(x) – объем продаж за период x, руб.; а – константа линейного тренда; b – константа линейного тренда при x, является тангенсом угла наклона прямой и характеризует ее наклон.
Такой анализ является краткосрочным. Данные приводятся для 12 товаров и 6 периодов (таблица 1).
Таблица 1. Данные по продажам 12 товаров за 6 периодов, млн. руб.
Порядок проведения анализа.
1. Скопируйте данные таблицы 1 в Excel, начиная с ячейки B2. Итоговый вид таблицы после всех расчетов показан на рисунке 1. Для выполнения копирования:
Рис. 1. Расчет для BCG-анализа
1.1. Выделите всю таблицу 1 в данном документе.
1.2. Скопируйте ее, нажав комбинацию клавиш CTRL+C.
1.3. Откройте программу Excel.
1.4. Выделите ячейку B2.
1.5. Вставьте скопированную таблицу, нажав комбинацию клавиш CTRL+V.
2. Замените заголовки Период 1 и т.д. На цифры от 1 до 6. Это понадобится для дальнейших расчетов.
3. Вычислите суммы продаж по всем периодам.
3.1. В ячейку I2 занесите текст: Итого.
3.2. В ячейку I3 введите формулу: =СУММ(C3:H3). Это – суммирование данных о продажах за 6 периодов по строке.
3.3. Протяните формулу вниз, нажав левой кнопкой мыши на квадратик в правой нижней части выделенной ячейки I3; отпустите, когда выделится ячейка I14.
4. Рассчитайте нормированный объем продаж по каждому товару как долю от продаж товара-лидера.
4.1. Найдите максимум суммарных объемов продаж, введя в ячейку I15 формулу =МАКС(I4:I14).
4.2. В дополнительном столбце J, озаглавленном «X », рассчитывается нормированный объем продаж каждого товара. В графическом представлении ему будет соответствовать горизонтальная ось X. Для товара-лидера рассчитанное значение равно единице, а для других товаров – доле их продаж от продаж товара-лидера. В ячейку J3 введите формулу: =I3/$I$15.
4.3. Протяните формулу до последнего товара. В итоге получатся нормированные значения, причем все они не должны иметь значения, большие 1.
5. Определите значение нормированного роста объема продаж. Требуется, чтобы все значения роста объема продаж находились бы в пределах от минус 1 до 1.
5.1. Добавьте справа столбец, озаглавив его Наклон (вводится в ячейку K2).
5.2. В ячейку для первого товара, K3, введите формулу: =НАКЛОН(C3:H3;$C$2:$H$2). Эта формула вычисляет тангенс угла наклона линии линейного тренда, построенного по объемам продаж за все периоды. Для данного анализа важно, что положительный знак этой функции соответствует росту, а отрицательный – снижению объема продаж. Также важно, что чем больше абсолютное значение результата, тем быстрее происходят изменения. Первый аргумент функции НАКЛОН – значения объема продаж по всем шести периодам; второй аргумент – номера периодов. Таким образом, для каждой точки задаются ее координаты в двумерном пространстве {время; объем продаж}.
5.3. Протяните формулу вниз до последнего товара.
5.4. Введите в ячейку L2 слова Модуль наклона.
5.5. Введите в ячейку L3 функцию вычисления абсолютного значения = ABS(K3) и протяните ее вниз до K14.
5.6. Найдите максимум модуля наклона, введя в ячейку L15 формулу =МАКС(L4:L14).
5.7. Добавьте справа столбец, озаглавив его «Y » (вводится в ячейку M2).
5.8. В ячейку для первого товара, M3, введите формулу: =K3/$L$15.
5.9. Протяните формулу до последнего товара. Теперь все значения Y должны находиться в пределах от минус 1 до 1.
6. Создайте графическое представление матрицы BCG. Итоговый вид матрицы представлен на рисунке 2.
Рис. 2. Вид матрицы BCG
6.1. Выделите данные двух столбцов: J3:J14 и M3:M14. Вторая область выделяется при нажатой клавише CTRL.
6.2. Выделите Лента: Вставка → Точечная → .
Будет создана диаграмма. Перенесите ее так, чтобы она не загораживала таблицу.
6.3. Уберите легенду (обозначение: для данных Ряд 1), выделив его щелчком мыши и нажав клавишу Delete.
6.4. Откорректируйте сетку вертикальной оси, щелкнув правой кнопкой мыши по одной из меток (по числу) этой оси и выбрав опцию: Формат оси… В появившемся окне задайте: Параметры оси → минимальное значение → фиксированное → -1. Аналогично задайте максимальное значение, равное 1, цену основных делений, равную 1.
6.5. Аналогично откорректируйте формат горизонтальной оси, вызвав контекстное меню щелчком правой кнопки мыши по одной из меток горизонтальной оси. Установите: минимальное значение:0, максимальное 1, цена основных делений: 0,5.
6.6. Включите показ сетки для горизонтальной оси, выделив диаграмму щелчком мыши и выбрав: Лента →Макет → Сетка →Вертикальные линии сетки по основной оси →Основные линии сетки.
В итоге диаграмма приобретет вид четырех ячеек матрицы BCG.
6.7. Задайте названия осей, выделив график щелчком левой кнопки мыши и выбрав Лента →Макет →Названия осей →Название основной горизонтальной оси →Под осью и Лента →Макет →Названия осей →Название основной вертикальной оси →Повернутое. Горизонтальная ось должна называться Нормированный объем продаж, а вертикальная – Нормированный рост объема продаж.
6.8. Добавьте подписи данных, чтобы можно было определить товар, соответствующий каждой точке. Для этого выберите в контекстном меню точки данных опцию Добавить подписи данных, а затем, снова вызвав то же контекстное меню, выберите опцию Формат подписей данных… В окне задайте показ координат X и Y.
6.9. Добавьте текстовые обозначение согласно рисунку 2, применив опцию: Лента → Вставка →Фигуры →Надпись.
6.10. Укажите в расчетной таблице категории каждого товара в соответствии с рисунком 2. Для этого воспользуйтесь метками точек, найдя соответствие между точками на графике и товаром в таблице. Добавьте справа столбец, озаглавив его Категория, и введите в него соответствующую категорию. Например, точка на рисунке 2, соответствующая товару 1, для которого X=0,67, а Y=-0,54, находится в области Дойные коровы. Напоминаем правила для отнесения товара к различным категориям на рисунке 1.
Звезда. X≥0,5; Y≥0
Дойная корова. X≥0,5; Y<0
Знак вопроса. X<0,5; Y≥0
Собака. X<0,5; Y<0
7. Опишите решения для каждого товара согласно рекомендациям в теоретической части данного задания.
Расчетное задание
1. Выполните расчеты для модифицированного BCG анализа. Задание следует выполнять в соответствии с процедурой, приведенной в расчетном примере. Исходные данные для расчетов приведены в таблице 2.
Таблица 2.Данные по продажам товаров за 6 периодов, млн. руб.
2. Не округляйте промежуточные результаты вычислений, выполняйте операции с клетками в таблице Excel во избежание накопления погрешностей вычислений.