Построение аддитивной модели в excel
Возможности MS Excel для построения мультипликативных и аддитивных моделей временных рядов. Построение графика зависимости уровня ряда от времени, мультипликативной модели. Оценка сезонной компоненты. Расчет значений с учетом циклической компоненты.
Рубрика | Экономико-математическое моделирование |
Предмет | Экономико-математическое моделирование |
Вид | лабораторная работа |
Язык | русский |
Прислал(а) | Р.И. Яковлев |
Дата добавления | 30.05.2018 |
Размер файла | 1,4 M |
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
Подобные документы
Статистические методы анализа одномерных временных рядов, решение задач по анализу и прогнозированию, построение графика исследуемого показателя. Критерии выявления компонент рядов, проверка гипотезы о случайности ряда и значения стандартных ошибок.
контрольная работа [325,2 K], добавлен 13.08.2010
Структурные компоненты детерминированной составляющей. Основная цель статистического анализа временных рядов. Экстраполяционное прогнозирование экономических процессов. Выявление аномальных наблюдений, а также построение моделей временных рядов.
курсовая работа [126,0 K], добавлен 11.03.2014
Тесты, с помощью которых можно построить эконометрические модели. Эконометрическое моделирование денежного агрегата М0, в зависимости от валового внутреннего продукта и индекса потребительских цен. Проверка рядов на стационарность и гетероскедастичность.
курсовая работа [814,0 K], добавлен 24.09.2012
Анализ временных рядов с помощью статистического пакета "Minitab". Механизм изменения уровней ряда. Trend Analysis – анализ линии тренда с аппроксимирующими кривыми (линейная, квадратическая, экспоненциальная, логистическая). Декомпозиция временного ряда.
методичка [1,2 M], добавлен 21.01.2011
Основные элементы эконометрического анализа временных рядов. Задачи анализа и их первоначальная обработка. Решение задач кратко- и среднесрочного прогноза значений временного ряда. Методы нахождения параметров уравнения тренда. Метод наименьших квадратов.
Аддитивная сезонность измеряется в тех же единицах, что и ряд, т.е. если мы рассматриваем ряд с продажами в рублях по месяцам, то аддитивная сезонность будет выражена в отклонениях одного месяца относительно средней или тренда в рублях.
Мультипликативная сезонность измеряется в относительных единицах – коэффициентах и в среднем равна 1. Т.е. коэффициент января у нас может получится - 0,9, февраля - 1,1…
Аддитивную сезонность имеет смысл использовать, если амплитуда колебаний сезонности из года в год не меняется. Если амплитуда колебаний сезонности из года в год меняется (т.е. размах уменьшается или увеличивается), то используем мультипликативную сезонность.
Как рассчитать аддитивную сезонность в Excel?
Возьмем продажи, например, муки по месяцам. Сезонность есть, но продажи из года в год стабильны, возрастающей амплитуды колебаний сезонности не наблюдается.
Для расчета аддитивной сезонности:
- Выделим линейный тренд из данных;
- Рассчитаем разницу «фактические продажи минус тренд»;
- Определим аддитивную сезонность по месяцам - среднее отклонение продаж от тренда для каждого месяца.
1. Выделим линейный тренд из данных.
Для расчета значений тренда для каждого периода времени пронумеруем значения временного ряда – продажи по месяцам:
С помощью функции Excel =предсказ() рассчитаем значения тренда по месяцам:
- D5 – X – номер периода, для которого рассчитываем значение тренда;
- $C$5:$C$40 – известные значения y — фиксированная ссылка на диапазон с объемами продаж;
- $D$5:$D$40 – известные значения X – фиксированная ссылка на диапазон с номерами периодов.
Рассчитали значения тренда:
2. Рассчитываем разницу значений ряда и тренда — объем продаж минус тренд:
3. Определим аддитивную сезонность по месяцам - среднее отклонение продаж от тренда для каждого месяца.
Определяем среднее отклонение для каждого месяца:
Т.к. первый и последний годы не полные, чтобы не запутаться с месяцами и формулами, воспользуемся формулой:
- =СУММЕСЛИ - формула суммирует отклонения по заданным месяцам
- $B$5:$B$40; - ссылка на диапазон с номерами месяцев
- B5; - номер конкретного месяца для суммирования
- $F$5:$F$40 - ссылка на диапазон для суммирования
- / - делим сумму за определенный месяц на количество, получаем среднее по месяцам
- СЧЁТЕСЛИ - формула считает количество месяцев в диапазоне
- $B$5:$B$40; - диапазон с номерами месяцев
- B5 – номер конкретного месяца для счета
Получаем среднее отклонение по месяцам – аддитивную сезонность:
Для расчета прогноза:
- Продлеваем тренд в будущее;
- К тренду прибавляем аддитивную сезонность соответствующего месяца.
Программа Forecast4AC PRO умеет автоматически подбирать аддитивную или мультипликативную сезонность, модель прогноза и подходит для прогноза большого массива данных.
Если есть вопросы, пожалуйста, обращайтесь!
Точных вам прогнозов!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite - автоматический расчет прогноза в Excel .
- 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.
Простейшим подходом к моделированию временных рядов, содержащих сезонные колебания, является построение аддитивной или мультипликативной моделей временного ряда.
Выбор одной из этих моделей основывается на анализе структуры временного ряда.
Если амплитуда сезонных колебаний примерно постоянна, то строят аддитивную модель. Если же амплитуда колебаний непостоянна, то есть возрастает или уменьшается, то строят мультипликативную модель.
Процесс построения модели ряда в этом случае включает следующие этапы:
1. Выравнивание исходного ряда методом скользящей средней. Расчет значений сезонной компоненты .
2. Устранение сезонной компоненты из исходных уровней ряда и получение выровненных данных в аддитивной или в мультипликативной модели.
3. Аналитическое выравнивание уровней или и расчет
значений с использованием полученного уравнения тренда.
4. Расчет полученных по модели значений или .
5. Расчет абсолютных и/или относительных ошибок.
Пример. Имеются данные о количестве продукции (тыс.шт.), проданной фирмой «Вега» в течение последних 20 кварталов.
Квартал | Объем продаж | Квартал | Объем продаж | Квартал | Объем продаж | Квартал | Объем продаж |
8,4 | 9,1 | 10,1 | 12,2 | ||||
8,6 | 9,2 | 10,8 | 11,9 | ||||
8,8 | 9,9 | 10,5 | 12,3 | ||||
9,5 | 9,7 | 10,7 | 12,5 | ||||
8,5 | 9,9 | 13,2 |
Этап 1. Про ведем выравнивание ряда методом скользящей средней. Для этого просуммируем уровни ряда по 4 кварталам последовательно. Далее разделим полученные суммы на 4 и найдем скользящие средние, уже не содержащие сезонной компоненты. Найдем центрированные скользящие средние, для чего вычислим средние значения из двух последовательных скользящих средних. Вычислим оценки сезонной компоненты как разность между фактическим уровнем продаж и центрированными скользящими средними.
Квартал | Объем продаж, тыс.шт. | Итого за 4 квартала | Скользящая средняя за 4 квартала | Центрированная скользящая Средняя | Оценка сезонной компоненты |
8,4 | |||||
8,6 | |||||
35,3 | 8,825 | ||||
8,8 | 8,8375 | -0,0375 | |||
35,4 | 8,85 | ||||
9,5 | 8,9125 | 0,5875 | |||
35,9 | 8,975 | ||||
8,5 | 9,025 | -0,525 | |||
36,3 | 9,075 | ||||
9,1 | 9,125 | -0,025 | |||
36,7 | 9,175 | ||||
9,2 | 9,325 | -0,125 | |||
37,9 | 9,475 | ||||
9,9 | 9,575 | 0,325 | |||
38,7 | 9,675 | ||||
9,7 | 9,7875 | -0,0875 | |||
39,6 | 9,9 | ||||
9,9 | 10,0125 | -0,1125 | |||
40,5 | 10,125 | ||||
10,1 | 10,225 | -0,125 | |||
41,3 | 10,325 | ||||
10,8 | 10,425 | 0,375 | |||
42,1 | 10,525 | ||||
10,5 | 10,6375 | -0,1375 | |||
10,75 | |||||
10,7 | 10,925 | -0,225 | |||
44,4 | 11,1 | ||||
. | 11,275 | -0,275 | |||
45,8 | 11,45 | ||||
12,2 | 11,65 | 0,55 | |||
47,4 | 11,85 | ||||
11,9 | 12,0375 | -0,1375 | |||
48,9 | 12,225 | ||||
12,3 | 12,35 | -0,05 | |||
49,9 | 12,475 | ||||
12,5 | |||||
13,2 |
Используем полученные оценки сезонной компоненты для расчета сезонности . Для этого найдем средние квартальные оценки сезонной компоненты, использовав данные всех кварталов. Заметим, что сумма значений сезонной компоненты по всем кварталам должна быть равна нулю, поэтому значения сезонной компоненты корректируются на величину, полученную как частное отделения суммы оценок сезонных компонент на число сезонов.
Квартал | ||||
Год | ||||
- | - | -0,0375 | 0,5875 | |
Показатели | -0,525 | -0,025 | -0,125 | 0,325 |
-0,0875 | -0,1125 | -0,125 | 0,375 | |
-0,1375 | -0,225 | -0,275 | 0,55 | |
-0,1375 | -0,05 | - | - | |
Итого за квартал | -0,8875 | -0,4125 | -0,5625 | 1,8375 |
Средняя оценка сезонной компоненты для квартала | -0,2218 | -0,1031 | -0,1406 | 0,4593 |
Скорректированная оценка сезонной компоненты | -0,2203 | -0,1015 | -0,1390 | 0,4609 |
Рассчитаем корректирующий коэффициент:
Скорректированные оценки сезонной компоненты определяются путем вычитания из средней оценки сезонной компоненты для квартала корректирующего коэффициента. Полученные таким образом значения занесены в таблицу 5.7.
Этап 2. Устраним сезонную компоненту из исходных уровней ряда и получим выровненные данные (столбец 4).
8,4 | -0,2203 | 8,6203 | 8,1545 | 7,9341 | 0,6861 | 0,4707 |
8,6 | -0,1015 | 8,7015 | 8,3845 | 8,2829 | 0,4185 | 0,1751 |
8,8 | -0,1390 | 8,9390 | 8,6146 | 8,4755 | 0,4635 | 0,2148 |
9,5 | 0,46093 | 9,0390 | 8,8446 | 9,3056 | -0,2666 | 0,0710 |
8,5 | -0,2203 | 8,7203 | 9,0747 | 8,8544 | -0,1344 | 0,0179 |
9,1 | -0,1015 | 9,2015 | 9,3047 | 9,2032 | -0,0016 | 0,0000 |
9,2 | -0,1390 | 9,3390 | 9,5348 | 9,3957 | -0,0566 | 0,0032 |
9,9 | 0,46093 | 9,4390 | 9,7648 | 10,2258 | -0,7867 | 0,6189 |
9,7 | -0,2203 | 9,9203 | 9,9949 | 9,7746 | 0,1457 | 0,0212 |
9,9 | -0,1015 | 10,0010 | 10,2249 | 10,1234 | -0,1218 | 0,0148 |
10,1 | -0,1390 | 10,2390 | 10,4550 | 10,3159 | -0,0769 | 0,0059 |
10,8 | 0,46093 | 10,3390 | 10,6850 | 11,1460 | -0,8069 | 0,6511 |
10,5 | -0,2203 | 10,7203 | 10,9151 | 10,6948 | 0,0254 | 0,0006 |
10,7 | -0,1015 | 10,8015 | 11,1451 | 11 ,0436 | -0,2420 | 0,0585 |
-0,1390 | 11,1390 | 11,3752 | 11,2361 | -0,0971 | 0,0094 | |
12,2 | 0,46093 | 11,7390 | 11,6052 | 12,06622 | -0,3271 | 0,1070 |
11,9 | -0,2203 | 12,1203 | 11,8353 | 11,6150 | 0,5052 | 0,2553 |
-0,1015 | 12,4015 | 12,0653 | 11,9638 | 0,4377 | 0,1916 | |
12,5 | -0,1390 | 12,6390 | 12,2954 | 12,1563 | 0,4826 | 0,2329 |
13,2 | 0,46093 | 12,7390 | 12,5254 | 12,9864 | -0,2473 | 0,0611 |
Таблица 5.8 Таблица 5.12 Расчёт значений и ошибок в аддитивной модели
Этап 3. Определим компоненту . Для этого проведем аналитическое выравнивание ряда с помощью линейного тренда. Имеем линейный тренд вида:
Стандартная ошибка коэффициента регрессии 0,293.
Подставляя в уравнение тренда последовательно получим значения тренда для каждого уровня временного ряда (столбец 5, табл. 5.8).
Этап 4. Найдем значения уровней ряда, полученные по аддитивной модели как (столбец 6, табл. 5.8).
Этап 5. Рассчитаем абсолютную ошибку как , (столбец 7, табл. 5.8). Качество полученной модели можно проверить, используя сумму квадратов абсолютных ошибок (столбец 8). Сумма квадратов абсолютных ошибок равна 3,18. По отношению к сумме квадратов отклонений исходных уровней ряда от его среднего уровня, равной 40,32, эта величина составит 7,89%.
Следовательно, аддитивная модель объясняет 92,11% общей вариации объема продаж за 20 кварталов.
Рассмотрим построение мультипликативной модели на примере.
Пример. Имеются поквартальные данные об объеме экспорта одной из областей РФ за 5 лет (млн. долл.).
Квартал | Объем экспорта, млн.долл. | Квартал | Объем экспорта, млн.долл. | Квартал | Объем экспорта, млн.долл. | Квартал | Объем экспорта, млн.долл. |
19,3 | 15,8 | 20,3 | 25,4 | ||||
12,3 | 17,2 | 22,3 | 31,8 | ||||
13,2 | 19,9 | 29,7 | 23,9 | ||||
15,6 | 26,3 | 21,1 | 25,8 | ||||
21,5 | 19,1 | 23,7 | 27,4 |
Этап 1. Проведем выравнивание ряда методом скользящей средней. для этого просуммируем уровни ряда по 4 кварталам последовательно. Далее разделим полученные суммы на 4 и найдем скользящие средние, уже не содержащие сезонной компоненты. Найдем центрированные скользящие средние, для чего вычислим средние значения из двух последовательных скользящих средних. Вычислим оценки сезонной компоненты как частное от деления фактического о уровня экспорта на центрированные скользящие средние.
Квартал | Объем продаж, тыс.шт. | Итого за 4 квартала | Скользящая средняя за 4 квартала | Центрированная скользящая Средняя | Оценка сезонной компоненты |
19,3 | |||||
12,3 | |||||
60,4 | 15,1 | ||||
13,2 | 15,375 | 0,858537 | |||
62,6 | 15,65 | ||||
15,6 | 16,0875 | 0,969697 | |||
66,1 | 16,525 | ||||
21,5 | 17,025 | 1,262849 | |||
70,1 | 17,525 | ||||
15,8 | 18,0625 | 0,87474 | |||
74,4 | 18,6 | ||||
17,2 | 19,2 | 0,895833 | |||
79,2 | 19,8 | ||||
19,9 | 20,2125 | 0,984539 | |||
82,5 | 20,625 | ||||
26,3 | 21,0125 | 1,251636 | |||
85,6 | 21,4 | ||||
19,1 | 21,7 | 0,880184 | |||
20,3 | 22,425 | 0,90524 | |||
91,4 | 22,85 | ||||
22,3 | 23,1 | 0,965368 | |||
93,4 | 23,35 | ||||
29,7 | 23,775 | 1,249211 | |||
96,8 | 24,2 | ||||
21,1 | 24,5875 | 0,85816 | |||
99,9 | 24,975 | ||||
23,7 | 25,2375 | 0,939079 | |||
25,5 | |||||
25,4 | 25,85 | 0,982592 | |||
104,8 | 26,2 | ||||
31,8 | 26,4625 | 1,201701 | |||
106,9 | 26,725 | ||||
23,9 | 26,975 | 0,886006 | |||
108,9 | 27,225 | ||||
25,8 | |||||
27,4 |
Таблица 5.10 Расчёт оценок сезонной компоненты
Используем полученные оценки сезонности для расчета сезонной компоненты . Для этого найдем средние квартальные оценки сезонной компоненты, используя данные всех кварталов
Квартал | ||||
Год | ||||
- | - | 0,8585 | 0,9696 | |
Показатели | 1,2628 | 0,8747 | 0,8958 | 0,9845 |
1,2516 | 0,8801 | 0,9052 | 0,9653 | |
1,2492 | 0,8581 | 0,9390 | 0,9825 | |
1,2017 | 0,8860 | - | - | |
Итого за квартал | 4,9653 | 3,4990 | 3,5986 | 3,9021 |
Средняя оценка сезонной компоненты для квартала | 1,2413 | 0,8747 | 0,8996 | 0,9755 |
Скорректированная оценка сезонной компоненты | 1,2440 | 0,876 | 0,9016 | 0,9776 |
Таблица 5.11 Расчёт значений сезонной компоненты
Заметим, что сумма значений сезонной компоненты по всем кварталам должна быть равна числу периодов в цикле. В нашем примере, цикл - год, в котором соответственно 4 квартала. Поэтому окончательный вариант сезонной компоненты будет получен корректировкой, заключающейся в умножении средней оценки сезонной компоненты для квартала на коэффициент
Полученные таким образом значения занесены в табл. 5.11 (строка 3).
Этап 2. Устраним сезонную компоненту из исходных уровней ряда и получим выровненные данные (столбец 4, табл. 5.12).
19,3 | 1,2440 | 15,5139 | 14,2959 | 17,7847 | 0,8723 | 0,7609 |
12,3 | 0,8766 | 14,0303 | 15,0690 | 13,2105 | 1,0620 | 1,1279 |
13,2 | 0,901 | 14,6402 | 14,2836 | 1,0249 | 1,0505 | |
15,6 | 0,9776 | 15,9563 | 1,6151 | 16,2440 | 0,9822 | 0,9648 |
21,5 | 1 ,2440 | 17,2823 | 17,3882 | 0,7989 | 0,6383 | |
15,8 | 0,8766 | 18,1127 | 18,1613 | 15,9214 | 1,1319 | 1,2813 |
17,2 | 0,9016 | 19,0767 | 18,9344 | 17,0717 | 1,1174 | 1,2486 |
19,9 | 0,9776 | 20,3546 | 19,7074 | 19,2673 | 1,0564 | 1,1160 |
26,3 | 1,2440 | 21,1407 | 20,4805 | 25,4786 | 0,8297 | 0,6884 |
19,1 | 0,8766 | 21,7869 | 18,6324 | 1,1693 | 1,3672 | |
20,3 | 0,9016 | 22,5149 | 22,0266 | 19,8597 | 1,1336 | 1,2852 |
22,3 | 0,9776 | 22,8094 | 22,7997 | 22,2905 | 1,0232 | 1,0471 |
29,7 | 1,2440 | 23,8738 | 23,5728 | 29,3255 | 0,8140 | 0,6627 |
21,1 | 0,8766 | 24,0683 | 24,3459 | 21,3433 | 1,1276 | 1,2716 |
23,7 | 0,9016 | 26,2859 | 25,1189 | 22,6478 | 1,1606 | 1,3470 |
25,4 | 0,9776 | 25,9802 | 25,8920 | 25,3137 | 1,0263 | 1,0533 |
31,8 | 1,2440 | 25,5618 | 26,6651 | 33,1725 | 0,7705 | 0,5937 |
23,9 | 0,8766 | 27,2622 | 27,4381 | 24,0542 | 1,1333 | 1,2845 |
25,8 | 0,9016 | 28,6150 | 28,2112 | 25,4359 | 1,1249 | 1,2655 |
27,4 | 0,9776 | 28,0259 | 28,9843 | 28,3369 | 0,9890 | 0,9781 |
Таблица 5.12 Расчёт значений и ошибок в мультипликативной модели
Этап 3. Определим компоненту . Для этого проведем аналитическое выравнивание ряда с помощью линейного тренда. Имеем линейный тренд вида:
Стандартная ошибка коэффициента регрессии 0,735.
Подставляя в уравнение тренда последовательно получим значения тренда для каждого уровня временного ряда (столбец 5, табл. 5.8).
Этап 4. Найдем значения уровней ряда, полученные по аддитивной модели как (столбец 6, табл. 5.8).
Этап 5. Рассчитаем абсолютную ошибку как , (столбец 7, табл. 5.8). Качество полученной модели можно проверить, используя сумму квадратов абсолютных ошибок (столбец 8). Сумма квадратов абсолютных ошибок равна 21б033. По отношению к сумме квадратов отклонений исходных уровней ряда от его среднего уровня, равной 530б072, эта величина составит 3б9681%.
Следовательно, аддитивная модель объясняет 96,03% общей вариации экспорта.
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Любому бизнесу интересно заглянуть в будущее и правильно ответить на вопрос: «А сколько денег мы заработаем за следующий период?» Ответить на такого рода вопросы позволяют различные методики прогнозирования. В данной статье мы с вами рассмотрим несколько таких методик и произведем все необходимые расчеты в Excel. Еще больше про анализ данных в Excel мы рассказываем на нашем открытом курсе «Аналитика в Excel».
Постановка задачи
Исходные данные
Для начала, давайте определимся, какие у нас есть исходные данные и что нам нужно получить на выходе. Фактически, все что у нас есть, это некоторые исторические данные. Если мы говорим о прогнозировании продаж, то историческими данными будут продажи за предыдущие периоды.
Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.
Составляющие прогноза
Следующий шаг: давайте определимся, что нам нужно учесть при построении прогноза. Когда мы исследуем наши данные, нам необходимо учесть следующие факторы:
- Изменение нашей пронозируемой величины (например, продаж) подчиняется некоторому закону. Другими словами, в временном ряде можно проследить некую тенденцию. В математике такая тенденция называется трендом.
- Изменение значений в временном ряде может зависить от промежутка времени. Другими словами, при построении модели необходимо будет учесть коэффициент сезонности. Например, продажи арбузов в январе и августе не могут быть одинаковыми, т.к. это сезонный продукт и летом продажи значительно выше.
- Изменение значений в временном ряде периодически повторяется, т.е. наблюдается некоторая цикличность.
Эти три пункта в совокупность образуют регулярную составляющую временного ряда.
Примечание. Не обязательно все три элемента регулярной составляющей должны присутствовать в временном ряде.
Однако, помимо регулярной составляющей, в временном ряде присутствует еще некоторое случайное отклонение. Интуитивно это понятно — продажи могут зависеть от многих факторов, некоторые из которых могут быть случайными.
Вывод. Чтобы комплексно описать временной ряд, необходимо учесть 2 главных компонента: регулярную составляющую (тренд + сезонность + цикличность) и случайную составляющую.
Виды моделей
Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”
Обычно выделяют два основных вида:
- Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
- Мультипликативная модель: Уровень временного ряда = Тренд X Сезонность X Случайные отклонения
Иногда также выделают смешанную модель в отдельную группу:
- Смешанная модель: Уровень временного ряда = Тренд X Сезонность + Случайные отклонения
С моделями мы определились, но теперь возникает еще один вопрос: «А когда какую модель лучше использовать?»
Классический вариант такой:
— Аддитивная модель используется, если амплитуда колебаний более-менее постоянная;
— Мультипликативная – если амплитуда колебаний зависит от значения сезонной компоненты.
Решение задачи с помощью Excel
Итак, необходимые теоретические знания мы с вами получили, пришло время применить их на практике. Мы будем с вами использовать классическую аддитивную модель для построения прогноза. Однако, мы построим с вами два прогноза:
- с использованием линейного тренда
- с использованием полиномиального тренда
Во всех руководствах, как правило, разбирается только линейный тренд, поэтому полиномиальная модель будет крайне полезна для вас и вашей работы!
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Модель с линейным трендом
Пусть у нас есть исходная информация по продажам за 2 года:
Учитывая, что мы используем линейный тренд, то нам необходимо найти коэффициенты уравнения
- y — значения продаж
- x — номер периода
- a — коэффициент наклона прямой тренда
- b — свободный член тренда
Рассчитать коэффициенты данного уравнения можно с помощью формулы массива и функции ЛИНЕЙН. Нам необходимо будет сделать следующую последовательность действий:
- Выделяем две ячейки рядом
- Ставим курсор в поле формул и вводим формулу =ЛИНЕЙН(C4:C27;B4:B27)
- Нажимаем Ctrl+Shift+Enter, чтобы активировать формулу массива
На выходе мы получили 2 числа: первое — коэффициент a, второе — свободный член b.
Теперь нам нужно рассчитать для каждого периода значение линейного тренда. Сделать это крайне просто — достаточно в полученное уравнение подставить известные номера периодов. Например, в нашем случае, мы прописываем формулу =B4*$F$4+$G$4 в ячейке I4 и протягиваем ее вниз по всем периодам.
Нам осталось рассчитать коэффициент сезонности для каждого периода. Учитывая, что у нас есть исторические данные за два года, разумно будет учесть это при расчете. Можем сделать следующим образом: в ячейке J4 прописываем формулу =(C4+C16)/СРЗНАЧ($C$4:$C$27)/2 и протягиваем вниз на 12 месяцев (т.е. до J15).
Что нам это дало? Мы посчитали, сколько суммарно продавалось каждый январь/каждый февраль и так далее, а потом разделили это на среднее значение продаж за все два периода.
То есть мы выяснили, как продажи двух январей отклонялись от средних продаж за два года, как продажи двух февралей отклонялись и так далее. Это и дает нам коэффициент сезонности. В конце формулы делим на 2, т.к. в расчете фигурировало 2 периода.
Примечание. Рассчитали только 12 коэффициентов, т.к. один коэффициент учитывает продажи сразу за 2 аналогичных периода.
Итак, теперь мы на финишной прямой. Нам осталось рассчитать тренд для будущих периодов и учесть коэффициент сезонности для них. Давайте амбициозно построим прогноз на год вперед.
Сначала создаем столбец, в котором прописываем номера будущих периодов. В нашем случае нумерация начинается с 25 периода.
Далее, для расчета значения тренда просто прописываем уже известную нам формулу =L4*$F$4+$G$4 и протягиваем вниз на все 12 прогнозируемых периодов.
И последний штрих — умножаем полученное значение на коэффициент сезонности. Вуаля, это и есть итоговый ответ в данной модели!
Модель с полиномиальным трендом
Конструкция, которую мы только что с вами построили, достаточно проста. Но у нее есть один большой минус — далеко не всегда она дает достоверные результаты.
Посмотрите сами, какая модель более точно аппроксимирует наши точки — линейный тренд (прямая зеленая линия) или полиномиальный тренд (красная кривая)? Ответ очевиден. Поэтому сейчас мы с вами и разберем, как построить полиномиальную модель в Excel.
Пусть все исходные данные у нас будут такими же. Для простоты модели будем учитывать только тренд, без сезонной составляющей.
Для начала давайте определимся, чем полиномиальный тренд отличается от обычного линейного. Правильно — формой уравнения. У линейного тренда мы разбирали обычный график прямой:
У полиномиального тренда же уравнение выглядит иначе:
где конечная степень определяется степенью полинома.
Т.е. для полинома 4 степени необходимо найти коэффициенты уравнения:
Согласитесь, выглядит немного страшно. Однако, ничего страшного нет, и мы с легкостью можем решить эту задачку с помощью уже известных нам методов.
- Ставим в ячейку F4 курсор и вводим формулу =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^);1;1). Функция ЛИНЕЙН позволяет произвести расчет коэффициентов, а с помощью функции ИНДЕКС мы вытаскиваем нужный нам коэффициент. В данном случае за выбор коэффициента отвечает самый последний аргумент. У нас стоит 1 — это коэффициент при самой высокой степени (т.е. при 4 степени, коэффициент). Кстати, узнать о самых полезных математических формулах Excel можно в нашем бесплатном гайде «Математические функции Excel».
- Аналогично прописываем формулу =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^);1;2) в ячейке ниже.
- Делаем такие же действия, пока не найдем все коэффициенты.
Кстати говоря, мы можем легко сами себя проверить. Давайте построим график наших продаж и добавим к нему полиномиальный тренд.
- Выделяем столбец с продажами
- Выбираем «Вставка» → «График» → «Точечный» → «Точечная диаграмма»
- Нажимаем на любую точку графика правой кнопкой мыши и выбираем «Добавить линию тренда»
- В открывшемся справа меню выбираем «Полиномиальная модель», меняем степень на 4 и ставим галочку на «Показывать уравнение на диаграмме»
Теперь вы наглядно можете видеть, как рассчитанный тренд аппроксимирует исходные данные и как выглядит само уравнение. Можно сравнить уравнение на графике с вашими коэффициентами. Сходится? Значит сделали все верно!
Помимо всего прочего, вы можете сразу оценить точность аппроксимации (не полностью, но хотя бы первично). Это делается с помощью коэффициента R^2. Тут у вас снова есть два пути:
- Вы можете вывести коэффициент на график, поставив галочку «Поместить на диаграмму величину достоверности аппроксимации»
- Вы можете рассчитать коэффициент R^2 самостоятельно по формуле =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^;;1);3;1)
Заключение
Мы с вами подробно разобрали вопрос прогнозирования — изучили необходимые термины и виды моделей, построили аддитивную модель в Excel с использованием линейного и полиномиального тренда, а также научились отображать результаты своих вычислений на графиках. Все это позволит вам эффективно внедрять полученные знания на работе, усложнять существующие модели и уточнять прогнозы. Чем большим количеством методов и инструментов вы будете владеть, тем выше будет ваш профессиональный уровень и статус на рынке труда.
Если вас интересуют еще какие-то модели прогнозирования — напишите нам об этом, и мы постараемся осветить эти темы в дальнейших своих статьях! Или запишитесь на курс «Excel Academy» от SF Education, где мы рассказываем про возможности Excel, необходимые для анализа.
Автор: Алексанян Андрон, эксперт SF Education
Научитесь использовать все прикладные инструменты из функционала MS Excel.
На сегодняшний день наука достаточно далеко продвинулась в разработке технологий прогнозирования. Специалистам хорошо известны методы нейросетевого прогнозирования, нечёткой логики и т.п. Разработаны соответствующие программные пакеты, но на практике они, к сожалению, не всегда доступны рядовому пользователю, а в то же время многие из этих проблем можно достаточно успешно решать, используя методы исследования операций, в частности имитационное моделирование, теорию игр, регрессионный и трендовый анализ, реализуя эти алгоритмы в широко известном и распространённом пакете прикладных программ MS Excel.
Аддитивную модель прогнозирования можно представить в виде формулы:
где:
F — прогнозируемое значение;
Т — тренд;
S — сезонная компонента;
Е — ошибка прогноза.
Применение мультипликативных моделей обусловлено тем, что в некоторых временных рядах значение сезонной компоненты представляет собой определенную долю трендового значения. Эти модели можно представить формулой:
На практике отличить аддитивную модель от мультипликативной можно по величине сезонной вариации. Аддитивной модели присуща практически постоянная сезонная вариация, тогда как у мультипликативной она возрастает или убывает, графически это выражается в изменении амплитуды колебания сезонного фактора, как это показано на рисунке 1.
Рис. 1. Аддитивная и мультипликативные модели прогнозирования
Алгоритм построения прогнозной модели
Для прогнозирования объема продаж, имеющего сезонный характер, предлагается следующий алгоритм построения прогнозной модели:
1. Определяется тренд, наилучшим образом аппроксимирующий фактические данные. Существенным моментом при этом является предложение использовать полиномиальный тренд, что позволяет сократить ошибку прогнозной модели.
2 . Вычитая из фактических значений объёмов продаж значения тренда, определяют величины сезонной компоненты и корректируют таким образом, чтобы их сумма была равна нулю.
3. Рассчитываются ошибки модели как разности между фактическими значениями и значениями модели.
4. Строится модель прогнозирования:
где:
F — прогнозируемое значение;
Т — тренд;
S — сезонная компонента;
Е — ошибка модели.
5. На основе модели строится окончательный прогноз объёма продаж. Для этого предлагается использовать методы экспоненциального сглаживания, что позволяет учесть возможное будущее изменение экономических тенденций, на основе которых построена трендовая модель. Сущность данной поправки заключается в том, что она нивелирует недостаток адаптивных моделей, а именно, позволяет быстро учесть наметившиеся новые экономические тенденции.
F пр t = a F ф t-1 + (1-а) F м t
где:
F пр t — прогнозное значение объёма продаж;
F ф t-1 — фактическое значение объёма продаж в предыдущем году;
F м t — значение модели;
а — константа сглаживания
- для составления прогноза необходимо точно знать величину сезона. Исследования показывают, что множество продуктов имеют сезонный характер, величина сезона при этом может быть различной и колебаться от одной недели до десяти лет и более;
- применение полиномиального тренда вместо линейного позволяет значительно сократить ошибку модели;
- при наличии достаточного количества данных метод даёт хорошую аппроксимацию и может быть эффективно использован при прогнозировании объема продаж в инвестиционном проектировании.
Применение алгоритма рассмотрим на следующем примере.
Исходные данные: объёмы реализации продукции за два сезона. В качестве исходной информации для прогнозирования была использована информация об объёмах сбыта мороженого “Пломбир” одной из фирм в Нижнем Новгороде. Данная статистика характеризуется тем, что значения объёма продаж имеют выраженный сезонный характер с возрастающим трендом. Исходная информация представлена в табл. 1.
Объем продаж (руб.)
Объем продаж (руб.)
Задача: составить прогноз продаж продукции на следующий год по месяцам.
Реализуем алгоритм построения прогнозной модели, описанный выше. Решение данной задачи рекомендуется осуществлять в среде MS Excel, что позволит существенно сократить количество расчётов и время построения модели.
1. Определяем тренд , наилучшим образом аппроксимирующий фактические данные. Для этого рекомендуется использовать полиномиальный тренд, что позволяет сократить ошибку прогнозной модели).
Рис. 2. Сравнительный анализ полиномиального и линейного тренда
На рисунке показано, что полиномиальный тренд аппроксимирует фактические данные гораздо лучше, чем предлагаемый обычно в литературе линейный. Коэффициент детерминации полиномиального тренда (0,7435) гораздо выше, чем линейного (4E-05). Для расчёта тренда рекомендуется использовать опцию “Линия тренда” ППП Excel.
Рис. 3. Опция "Линии тренда"
- логарифмический R 2 = 0,0166;
- степенной R 2 = 0,0197;
- экспоненциальный R 2 = 8Е-05.
2. Вычитая из фактических значений объёмов продаж значения тренда , определим величины сезонной компоненты , используя при этом пакет прикладных программ MS Excel (рис. 4).
Рис. 4. Расчёт значений сезонной компоненты в ППП MS Excel
Значение тренда
Сезонная компонента
Скорректируем значения сезонной компоненты таким образом, чтобы их сумма была равна нулю.
Сезонная компонента
3. Рассчитываем ошибки модели как разности между фактическими значениями и значениями модели.
Значение модели
Находим среднеквадратическую ошибку модели (Е) по формуле:
Е= Σ О 2 : Σ (T+S) 2
где:
Т — трендовое значение объёма продаж;
S — сезонная компонента;
О — отклонения модели от фактических значений
Е= 0,003739 или 0.37 %
Величина полученной ошибки позволяет говорить, что построенная модель хорошо аппроксимирует фактические данные, т.е. она вполне отражает экономические тенденции, определяющие объём продаж, и является предпосылкой для построения прогнозов высокого качества.
Построим модель прогнозирования:
Построенная модель представлена графически на рис. 5.
5. На основе модели строим окончательный прогноз объёма продаж. Для смягчения влияния прошлых тенденций на достоверность прогнозной модели, предлагается сочетать трендовый анализ с экспоненциальным сглаживанием. Это позволит нивелировать недостаток адаптивных моделей, т.е. учесть наметившиеся новые экономические тенденции:
F пр t = a F ф t-1 + (1-а) F м t
где:
F пр t — прогнозное значение объёма продаж;
F ф t-1 — фактическое значение объёма продаж в предыдущем году;
F м t — значение модели;
а — константа сглаживания.
Константу сглаживания рекомендуется определять методом экспертных оценок, как вероятность сохранения существующей рыночной конъюнктуры, т.е. если основные характеристики изменяются / колеблются с той же скоростью / амплитудой что и прежде, значит предпосылок к изменению рыночной конъюнктуры нет, и следовательно а ® 1, если наоборот, то а ® 0.
Рис. 5. Модель прогноза объёма продаж
Таким образом, прогноз на январь третьего сезона определяется следующим образом.
Определяем прогнозное значение модели:
F м t = 1 924,92 + 162,44 = 2087 ± 7,8 (руб.)
Фактическое значение объёма продаж в предыдущем году (F ф t-1 ) составило 2 361 руб. Принимаем коэффициент сглаживания 0.8. Получим прогнозное значение объёма продаж:
F пр t = 0,8*2 361 + (1-0.8)*2087 = 2306,2 (руб.)
Для учёта новых экономических тенденций рекомендуется регулярно уточнять модель на основе мониторинга фактически полученных объёмов продаж, добавляя их или заменяя ими данные статистической базы, на основе которой строится модель.
Кроме того, для повышения надёжности прогноза рекомендуется строить все возможные сценарии прогноза и рассчитывать доверительный интервал прогноза.
-
Дмитриев Михаил Николаевич, заведующий кафедрой экономики и предпринимательства Нижегородского архитектурно-строительного университета (ННГАСУ), доктор экономических наук, профессор.
Юрий
Перед таблицой №3 написано: Скорректируем значения сезонной компоненты таким образом, чтобы их сумма была равна нулю.
Как это сделать?
На мой взгляд, авторы увлеклись использованием доступного инструмента (Эксель), что само по себе очень хорошо, но очень заблуждаются в прогнозировании. Они подробно описывают, как можно дать АНАЛИТИЧЕСКОЕ ОПИСАНИЕ ИМЕЮЩИХСЯ ДАННЫХ (за те 2 года продаж мороженного нашим русским братьям). Да, так можно построить модель. Кстати, не только так - методов существует множество. И выбирать модель лучше с учетом СУТИ описываемого процесса. Например, если в его основе лежат периодические колебания, может оказаться очень полезной модель в виде ряда Фурье. Но это отдельная тема.
Итак, авторы описали "прошлое". Посчитали ошибку - и восхитились. И тут же - фантастический вывод:
"Величина полученной ошибки позволяет говорить, что построенная модель хорошо аппроксимирует фактические данные, т.е. она вполне отражает экономические тенденции, определяющие объём продаж, и является предпосылкой для построения прогнозов высокого качества."
Да откуда же следует, что полученная модель что-то там отражает? И является предпосылкой для прогнозов?
Скажу иначе. Какой смысл с точностью до долей % описывать то, что было, если разница в "одноименных" месяцах парвого и второго года составляет порядка 10%? Мы имеем процесс с сильной случайной составляющей, которая ограничивает возможную точность прогноза и уж конечно не позволит выйти на погрешности в доли процента. Совершенствование прогнозов в таких случаях основано на увеличении статистики - до той степени, пока не начинает мешать нестационарность процесса. В нашем случае, привлечение данных еще по нескольким годам может только навредить, т.к. за такой период произошли существенные изменения в данном процессе и считать его стационарным нельзя ни в каком приближении. Вот и получается, что с имеющейся случайной составляющей мы можем довольно приблизительно кое-что предсказать. Но описывать процесс так старательно вовсе не обязательно. Есть метод и попроще - будут желающие, поделюсь.
Мои слова подтверждаются и замечанием Алексея.
И еще одно. Сама матоснова прогнозирования также должна опираться на предположения о характере процесса. Поэтому "универсального" алгоритма и не существует. Где-то можно и пренебречь случайной составляющей. Но не в приведенном примере, ИМХО.
D!G.ua
В примере фактические данные одного года по месяцам уж оччень пропорциональны факту другого года в тех же месяцах. Другими словами, в примере слишком подавляющее влияние сезонного фактора. А в таком случае прогнозы делать очень легко на факте предыдущих лет. Так что пример не показателен. А вот если бы скажем, в 9-10й месяцы одного года был "нетипичный" провал/пик (на 20%)- погрешность сразу увеличивается до неприемлемых размером. И статистические методы очень плохо работают.
Это не мой метод. В одном из дипломных курсов Школы Бизнеса Лондонского Открытого университета нам приводили нечто подобное, я немного исказил для своих нужд. Более того, использование его в условиях моего
предприятия дает мало пользы. У нас крупнодискретная реализация, за
месяц может быть около десятка продаж. И очень велика случайная
составляющая. Поэтому и точность прогноза невелика.
Однако я допускаю, что в условиях квазинепрерывного потока продаж и
несколько меньшего влияния случайных факторов может быть польза.
Идея же состоит в том, что мы пытаемся прогнозировать по такой
матмодели, которая, НА НАШ ВЗГЛЯД, соответствует реальным
зависимостям. Уже отсюда следует, что УНИВЕРСАЛЬНОГО метода быть не
может. И как ни описывай (очччень точно!) полиномиальными моделями
процесс, у которого налицо периодическая составляющая - для прогноза
это не годится. Аналогично, если в процессе очень сильна случайная
составляющая, то лучше, чем фильтрация ничего нет. Мы просто фильтруем
и берем это ("среднее") значение в качестве прогнозного. Все. Это даст
наименьшуюю ошибку. Естественно, если другие факторы (кроме случайного
шума) слабы. И т.д.
Нужен пример. Пусть мы предполагаем в процесс просто шум. Т.е. модель
того, что мы прогнозируем: постоянная составляющая + шум (с нулевым
средним значением). Тогда алгоритм прогноза: по всем имеющимся данным
находим среднее и считаем это прогнозом.
Теперь мы решили, что в процессе есть и шум, и тренд (это определяется
обработкой даже в Экселе). Тогда строим линию тренда (при этом шум
автоматически фильтруется) и ее продолжение за текущий момент -
наилучший прогноз.
Пусть теперь нам пришло в голову, что в процессе есть периодичность
(продажи очень часто имеют недельную, месячную или годовую
периодичность). Тогда следует решить, какой функцией описать эту
периодичность, найти параметры данной функции по известным данным - и
затем продолжить ее в область будущего. Если функция близка к синусу -
можно просто определить ее частоту, амплитуду и фазу. Но если функция
искажена, то для ее хорошего описания потребуется находить параметры
нескольких гармоник (спектр). Это может быть громоздко. Тогда можно
просто найти т.н. "структуру" в пределах периода. Например, период -
неделя. Тогда можно найти, что в среднем продажи в понедельник
отличаются от среднего значения за предыдущие 7 дней на минус 14%, во
вторник - на минус 15%, а в субботу - на плюс 33% от этого же
среднего. Эти числа назовем коэффициентами структуры. Дальше просто -
продлеваем среднее (просто усреднением или как линию тренда - см.
предыдущие примеры) на будущее, а к ней прибавляем произведение этого
же среднего на коэффициент структуры.
Путано? Пожалуй. Кто сам пробовал - поймет. Кто знает больше -
поправит или предложит свое. Главное в описанном подходе - изучаем
исходные данные не для точного их описания, а для решения об
адекватной модели. Затем ищем параметры модели - и продлеваем эту
функцию в будущее. Угадали модель - прогноз лучше. Шумы мешают. Но
увеличение длительности наблюдения чревато тем, что для построения
модели мы начинаем использовать такие старые данные, что поведение
процесса уже существенно изменилось. Что ж, подбираем оптимум,
набиваем шишки, влетаем в убытки, банкротим предприятие, переходим в
другое, но уже с чуством огромного опыта за плечами :)
У меня есть примерчик на Экселе. ВОт его не знаю, как выложить. Пожалуй, снова подожду - если не разочаровал вас словами - обращайтесь, пришлю табличку.
С уважением и благодарностью к заинтересовавшимся и в надежде на конструктивную критику.
Сергей, ruskin@navigator.lv
Юра, Вы не могли бы прислать файлик примера продаж в Экселе! Заранее благодарю!
Читайте также:
- В excel пропали листы как включить
- Как подгрузить веткад в автокад
- Удалить часть строки в vba excel
- Программа для прошивки rockchip
- Право вывода информации в 1с это