1.3.5 Модель с мультипликативной компонентой
Применение мультипликативных моделей обусловлено тем, что в некоторых временных рядах значение сезонной компоненты представляет собой определенную долю трендового значения. Мультипликативная модель в общем виде может быть представлена в виде формулы:
F = T * S * E, (1.14)
T – трендовая составляющая,
S – сезонная составляющая,
E – случайная составляющая (ошибка прогноза)
Алгоритм построения модели с мультипликативной компонентой во многом схож с предыдущим. В процессе разработки мультипликативной модели также необходимо определить сезонную компоненту. Сезонная компонент будет определяться как отношение фактического объема продаж к центрированной скользящей средней.
Шаг 1
Расчет значений сезонной компоненты
Рисунок 1.30 – Расчет сезонной компоненты
Далее необходимо рассчитать средние значения сезонной компоненты.
Т.к. значения сезонной компоненты в мультипликативной модели – это доли, а число кварталов равно 4, то сумма сезонных компонент должна быть равна 4. Если это условие не выполняется, то необходимо выполнить корректировку. Например, сумма сезонных компонент = 4,014
Разница между фактическим значением и нормативным делится на 4 и прибавляется к каждой компоненте.
Рисунок 1.31 – Расчет скорректированных значений сезонных компонент
Результат представлен на рис.1.32.
Рисунок 1.32 – Результат расчетов
На этом расчет сезонных компонент закончен. Переходим к шагу 2.
Шаг 2
Десезонализация данных. Расчет тренда.
Рисунок 1.33 – Расчет десезонализированного объема продаж
Далее на основе данных десезонализированного объема продаж строятся трендовые модели различного вида (линейная, степенная, экспоненциальная, логарифмическая) и выбирается модель с наибольшим значением коэффициента детерминации. Эта модель и будет использоваться для расчета трендового значения (Т). В результате проведенных расчетов установлено, что наиболее точной является линейная трендовая модель (R2=0,959).
Рисунок 1.34 – Линейная трендовая модель
Прогнозные значения объема продаже (F) рассчитывается путем произведения соответствующего значения сезонной компоненты (S) и трендовых значений (T). Дальнейший расчет показателей, характеризующих точность прогноза, выполняются аналогично расчетам модели с аддитивной компонентой. Расчеты рекомендуется выполнять в форме таблицы (см.рис.1.35)
Рисунок 1.35 – Оценка точности прогноза
Таким образом, наибольшая точность достигается в случае использования модели с аддитивной компонентой.
1.3.6 Выбор объектов прогнозирования и уровня детализации
Один из наиболее сложных вопросов в области прогнозирования заключается в выборе объекта исследования и уровня детализации. При этом необходимо определить будут ли прогнозироваться обобщающие показатели, характеризующие финансовый результат организации или необходимо составлять прогнозы факторных переменных. Более детальные прогнозы имеют безусловное аналитическое преимущество, однако требуют больших трудовых и финансовых затрат. Для выбора уровня детализации могут быть использованы метод АВС и XYZ-анализа.
Метод АВС-анализа основан на делении определенной совокупности объектов по удельному весу каждой группы, определяемому по тому или иному выбранному показателю. АВС-анализ основан на принципе Парето, который означает, что 20% усилий дают 80% результата, а остальные 80% усилий - лишь 20% результата.
Число групп при проведении АВС-анализа может быть любым, но наибольшее распространение получило деление рассматриваемой совокупности на три группы: А, В и С (75:20:5), чем и обусловлено название метода (ABC-Analysis).
Группа А включает незначительное число объектов с высоким уровнем удельного веса по выбранному показателю, группа В - среднее число объектов со средним уровнем удельного веса по выбранному показателю, группа С - большое число объектов с незначительной величиной удельного веса по выбранному показателю.
Экономический смысл применения АВС-анализа в прогнозных исследованиях сводится к сокращению трудоемкости. Необходимо наиболее тщательным образом прогнозировать товары и услуги, относящиеся к группе А. Таким образом может быть достигнут максимальный эффект при минимальных затратах. Алгоритм проведения АВС-анализа может быть представлен в виде следующего алгоритма:
Определение цели анализа
Определение объектов анализа
Определение параметра для дифференциации объектов
Сортировка объектов анализа в порядке убывания значения параметра.
Деление на группы А, В и С.
Для определения принадлежности выбранного объекта к группе необходимо:
определить долю каждого объекта,
рассчитать кумулятивную долю,
присвоить значения групп выбранным объектам.
Интерпретация результатов
Для выделения групп объектов (А, В, С) могут быть использованы различные подходы, наиболее распространенными же являются эмпирический подход и метод касательных. Эмпирический подход заключается в разделении объектов на группы на основе усредненных результатов ранее проведенных исследований, наиболее распространенным является разделение на группы, представленные в таблице 1.9.
Таблица 1.9 - Разбиение объектов анализа на группы
Наименование группы
|
Характеристика диапазона
|
Группа А
|
включает примерно 15% объектов, которые определяют 75% результата.
|
Группа В
|
35% объектов, которые обеспечивают до 20% результата
|
Группа С
|
около 50% объектов, которые обеспечивают 5% результата
|
Для разбиения на группы также может быть использован метод касательных, предложенный Лукинским В.С. Предполагает выполнение следующие последовательности действий:
Этап 1 - На основе данных таблицы отсортированной по убыванию построим кумулятивную кривую.
Этап 2 – Соединим начало и окончание графика прямой ОZ.
Этап 3 – Проведем касательную к кривой АВС, параллельную прямой ОZ.
Точка касания N разделит группы А и В.
Этап 4 - Теперь соединим точки N и Z и проведем касательную к кривой АВС-анализа, параллельную NZ. Точка касания K разделяет группы В и С.
Преимущество метода в его гибкости, простоте и наглядности. Недостатком можно назвать сложность его автоматизации.
О
Z
N
K
A
B
C
Нарастающий итог доли объектов, %
15%
35%
50%
75%
20%
5%
Уд. вес результатов, обеспечиваемых объектами
Рисунок 1.36 – Графическая иллюстрация метода касательных
Одновременно с АВС используется XYZ-анализ. Основная идея XYZ-анализа состоит в группировании объектов по стабильности спроса, оцениваемого коэффициентом вариации.
 , (1.15)
Коэффициент вариации рассчитывается по формуле 1.15, в которой участвуют:
значение параметра по оцениваемому объекту за каждый интервал;
среднее значение параметра по оцениваемому объекту анализа за указанный период;
число интервалов указанного периода.
В качестве параметра как правила выступает объем продаж. Результатом XYZ –анализа является группировка товаров по трем категориям, исходя из стабильности их поведения:
категория Х – это группа товаров, характеризующиеся стабильной величиной потребления и высокой степенью прогнозирования. Коэффициент вариации не превышает 10%;
категория Y включает объекты с колебанием продаж от 10 до 25%;
к категории Z относятся объекты с нерегулярным спросом, значение коэффициента вариации по которым превышает 25%.
Объединение результатов АВС и XYZ-анализа позволяет сформировать эффективные решения для каждой типовой группы. Для прогнозирования спроса на товары групп АХ, ВХ и СХ рекомендуется использовать количественные методы. Группы АY, BY, CY характеризуются нерегулярным спросом, что обуславливает необходимость использования комбинированных методик прогнозирования, основанных на сочетании математических подходов и интуитивно-логического анализа, прогнозирование объектов категорий AZ, BZ, CZ целесообразно применять экспертный подход.
Рассмотрим возможность практической реализации методов АВС, ХYZ- анализа в среде MS Excel.
Алгоритм реализации методов АВС, ХYZ - анализа в среде MS Excel
Составить таблицу с исходными данными
Таблица 1.10 – Исходные данные
Наименование товара
|
Объем продаж, т.р.
|
ИТОГО за 1 квартал
|
январь
|
февраль
|
март
|
1
|
2
|
3
|
4
|
5
|
ноутбук Toshiba A660-181
|
945 410.92
|
961 148.68
|
1 036 132.97
|
|
ноутбук Toshiba L670-15M
|
984 796.00
|
872 978.40
|
1 047 182.10
|
|
ноутбук Toshiba L655-14J
|
866 785.29
|
1 042 062.57
|
937 396.89
|
|
ноутбук HP Compaq Mini CQ10
|
821 998.10
|
942 604.80
|
1 041 031.60
|
|
ноутбук HP Mini 210-2000er
|
830 358.20
|
911 174.76
|
856 087.00
|
|
ноутбук HP Mini 110-3100er
|
729 068.66
|
884 821.50
|
889 985.60
|
|
ноутбук Acer AOD255-2BQrr
|
736 085.73
|
826 247.79
|
921 645.90
|
|
ноутбук Acer AO753-U341ss
|
389 240.54
|
547 760.20
|
1 075 447.80
|
|
ноутбук Acer AOD255-2BQrr
|
429 478.60
|
741 863.40
|
572 784.70
|
|
ноутбук Acer AO532H-2DBK
|
493 860.30
|
649 020.40
|
530 318.50
|
|
ноутбук Sony VPC-M13M1R/P
|
338 511.40
|
599 182.70
|
734 636.80
|
|
ноутбук Sony VPC-M13M1R/L
|
441 274.69
|
607 812.93
|
623 102.74
|
|
ноутбук Sony VPC-M13M1R
|
477 330.84
|
696 157.22
|
438 818.95
|
|
ноутбук Asus EeePC 1001PX
|
505 559.90
|
468 336.70
|
492 033.80
|
|
ноутбук Asus EeePC 900AX
|
322 333.90
|
406 149.40
|
528 966.60
|
|
ноутбук Asus EeePC 1001PX
|
251 226.62
|
344 745.52
|
533 520.26
|
|
ноутбук Samsung N150-KA02
|
541 881.60
|
193 549.90
|
384 810.42
|
|
ноутбук Samsung N145-JP01
|
280 140.00
|
333 625.00
|
381 965.00
|
|
ноутбук Samsung N150-JP04
|
278 859.70
|
296 152.90
|
388 759.80
|
|
ноутбук Samsung N150-JP01
|
361 660.00
|
345 546.10
|
249 920.80
|
|
ноутбук eMachines 250-01G16i
|
254 094.50
|
234 383.40
|
316 476.40
|
|
электронная книга iRiver Story EB02
|
197 041.70
|
188 686.70
|
233 503.80
|
|
электронная книга ViewSonic VEB620-B
|
172 143.70
|
153 479.60
|
211 204.40
|
|
электронная книга PocketBook 301
|
128 116.00
|
183 171.70
|
215 736.00
|
|
коммуникатор Acer E101
|
148 111.80
|
147 255.80
|
163 568.20
|
|
телеприемник TV-tuner Beholder
|
93 629.70
|
79 715.30
|
123 497.10
|
|
веб-камера Logitech WebCam C120
|
2 124.40
|
2 174.00
|
2 476.30
|
|
веб-камера Genius Eye 312
|
3 170.00
|
2 784.60
|
3 288.40
|
|
веб-камера Genius iSlim 1320
|
2 031.90
|
2 726.30
|
3 301.40
|
|
веб-камера Microsoft LifeCam
|
2 545.50
|
2 606.00
|
2 779.90
|
|
принтер Samsung ML-1665
|
62 963.50
|
66 624.10
|
61 495.40
|
|
принтер HP LaserJet P2055dn
|
54 725.90
|
56 390.40
|
56 983.10
|
|
принтер Xerox Phaser 3140
|
71 144.30
|
70 394.40
|
68 845.10
|
|
принтер HP LJ P4515
|
61 838.60
|
65 138.20
|
64 405.90
|
|
принтер HP Color LJ CP1215
|
55 044.30
|
62 365.30
|
68 626.60
|
|
принтер Samsung CLP-325
|
23 337.20
|
17 756.50
|
16 531.70
|
|
принтер Epson S22
|
42 401.70
|
46 896.80
|
38 200.90
|
|
сканер Epson Perfection V330 Photo
|
93 924.10
|
82 654.10
|
94 895.70
|
|
сканер HP ScanJet 5590C (L1910A)
|
38 363.90
|
39 740.30
|
41 536.60
|
|
клавиатура Genius SlimStar 330, Multimedia
|
8 150.00
|
8 850.00
|
9 769.00
|
|
клавиатура Genius KB06X
|
11 357.89
|
14 979.00
|
16 496.10
|
|
клавиатура Genius KB110
|
14 638.40
|
17 561.20
|
14 244.70
|
|
клавиатура Genius G235
|
13 178.70
|
11 048.80
|
15 531.50
|
|
клавиатура Genius LuxeMate 325B
|
9 019.31
|
10 111.80
|
9 672.30
|
|
Окончание табл.1.10
|
1
|
2
|
3
|
4
|
5
|
клавиатура Genius SlimStar 335
|
7 270.00
|
5 180.00
|
5 059.00
|
|
клавиатура Genius SlimStar 320
|
5 765.00
|
5 645.00
|
5 340.00
|
|
клавиатура Logitech DeLuxe 250
|
9 751.70
|
8 616.70
|
7 534.00
|
|
клавиатура Mitsumi Classic KFK-EA4XY
|
7 030.00
|
8 950.00
|
8 230.00
|
|
мышь Asus UT200 Optical USB Black
|
2 437.00
|
2 474.10
|
2 134.30
|
|
мышь Asus WT400 Cordless
|
3 425.50
|
3 860.10
|
2 875.00
|
|
мышь Asus BX700 Bluetooth Laser
|
2 183.20
|
3 151.70
|
3 679.80
|
|
мышь Asus GX800 Laser USB
|
8 772.00
|
9 003.80
|
9 729.20
|
|
мышь Genius NetScroll 110 Optical
|
9 545.90
|
9 924.30
|
12 726.80
|
|
мышь Genius Xscroll Optical
|
2 063.20
|
5 419.40
|
6 746.30
|
|
мышь Genius NetScroll 100X Optical
|
3 222.00
|
4 226.40
|
3 380.30
|
|
пульт ДУ Genius Media Pointer
|
6 099.79
|
8 494.45
|
7 039.75
|
|
мышь Logitech B110 Optical Mouse
|
7 136.40
|
8 720.90
|
8 424.50
|
|
мышь Logitech LS1 Laser Mouse
|
3 849.70
|
3 707.70
|
4 240.00
|
|
мышь Logitech RX1000 Laser
|
7 576.75
|
5 710.04
|
6 072.15
|
|
мышь Microsoft Wireless Mobile Mouse 3500
|
10 828.60
|
11 063.20
|
12 361.60
|
|
мышь Microsoft Arc, беспроводная
|
5 032.44
|
5 423.45
|
7 622.07
|
|
геймпад Genius G-08XU
|
2 055.00
|
2 525.10
|
1 635.70
|
|
геймпад Genius MaxFire Grandias
|
4 256.80
|
4 582.00
|
6 008.20
|
|
руль Genius Speed Wheel 3 с виброотдачей
|
1 359.70
|
1 152.90
|
959.80
|
|
руль Genius Speed Wheel 5 Pro
|
998.10
|
604.80
|
1 031.60
|
|
руль Genius Speed Wheel RV
|
1 085.73
|
1 247.79
|
945.90
|
|
Определить суммарный объем продаж за 1 квартал по каждому объекту и общий объем продаж за квартал по всем объектам с помощью функции =СУММ (ссылка)
Рисунок 1.37 – Расчет суммарного объема продаж
Определить долю каждого объекта в общем обороте
Рисунок 1.38 – Расчет доли в общем обороте
Отсортировать таблицу по убыванию по критерию «Доля в общем обороте». Для этого необходимо выделить область таблицы, зайти в раздел «ДАННЫЕ» и выбрать пункт «СОРТИРОВКА».
Рисунок 1.39 - Сортировка данных
Расчет кумулятивной доли, т.е. доли в обороте нарастающим итогом:
Рисунок 1.40 - Расчет кумулятивной доли
Для разбиения совокупности данных на группы АВС необходимо создать блок вспомогательных ячеек:
Затем в ячейку Н3 введите формулу:
=ЕСЛИ(G3<$B$74,"A",ЕСЛИ(G3>$B$75,"С","B"))
Формулу необходимо растянуть на весь диапазон ячеек столбца «АВС группа».
Рисунок 1.41 – Формирование именованных групп
Для разбиения совокупности данных на группы XYZ необходимо определить коэффициент вариации, который позволяет судить о стабильности спроса.
Рисунок 1.42 – Форма таблицы для проведения XYZ-анализа
В ячейки I3 и J3 необходимо ввести следующие формулы:
I3: =СРЗНАЧ(B3:D3)
J3: =(КОРЕНЬ(((B3-I3)^2+(C3-I3)^2+(D3-I3)^2)/3))/I3
Формулы необходимо растянуть на весь диапазон ячеек соответствующего столбца.
Для присвоения категории X, Y, Z создать блок вспомогательных ячеек:
Затем в ячейку K3 введите формулу:
=ЕСЛИ(J3<$B$77,"X",ЕСЛИ(J3>$B$78,"Z","Y"))
Формулу необходимо растянуть на весь диапазон ячеек соответствующего столбца.
Для объединения результатов АВС и XYZ анализа в ячейку L3 необходимо ввести формулу: =СЦЕПИТЬ(H3,K3)
Формулу растянуть на весь диапазон ячеек соответствующего столбца. Для повышения наглядности результатов анализа можно воспользоваться инструментом «УСЛОВНОЕ ФОРМАТИРОВАНИЕ».
Предварительно выделив соответствующую область ячеек (L3:L68), нажмите  . В появившемся диалоговом окне выберите пункты «ПРАВИЛА ВЫДЕЛЕНИЯ ЯЧЕЕК»/ «РАВНО»
Рисунок 1.43 – Условное форматирование ячеек
В появившемся диалоговом окне выберите желаемый цвет для выделения ячеек.
Рисунок 1.44 – Диалоговое окно определения формата ячеек
После нажатия ОК повторите аналогичную операцию для других групп:
AX, BX, CX; AY, BY, CY; AZ, BZ, CZ.
|
