Имеются поквартальные данные о прибыли предприятия за 4 года

Выполним графическую визуализацию исходного временного ряда дохода предприятия. Используем из Главного меню MS Excel: Вставка – Диаграммы – Точечная – Точечная с гладкими кривыми и маркерами.
Вывод 1: График временного ряда демонстрирует линейный тренд с периодически повторяющейся сезонной волной примерно одинаковой амплитуды. В силу наличия тренда математическое ожидание дохода зависит от времени, и, следовательно, процесс является нестационарным. Для прогноза представляется возможным применить аддитивную (в силу примерно одинаковой амплитуды в сезонной волне) тренд-сезонную модель:
Yt=Tt+St+Et
1.1. Чтобы сформировать расчетную таблицу 1 (последовательно выполняя пункты 1-4 данной лабораторной работы), необходимо использовать Мастер формул MS Excel. В данном пункте достаточно заполнить столбцы: t – сквозной порядковый номер периода (или момента) времени, Yt – уровень временного ряда для данного периода (момента) времени.
1.2. Чтобы выполнить расчет скользящей средней за 4 квартала в расчетной таблице 1 надо последовательно просуммировать уровни ряда за каждые четыре квартала со сдвигом на один момент времени и затем разделить каждую полученную сумму на 4 согласно формулам:
Y3=Y1+Y2+Y3+Y44=11,5+10,8+11,1+154=12,1
Y4=Y2+Y3+Y4+Y54=10,8+11,1+15+13,14=12,5
Y5=Y3+Y4+Y5+Y64=11,1+15+13,1+10,94=12,525 и т.д.
1.3. Чтобы выполнить расчет центрированной скользящей средней в расчетной таблице 1 необходимо определить средние значения из двух последовательных скользящих средних:
Y3*=Y3+Y42=12,1+12,52=12,3
Y4*=Y4+Y52=12,5+12,5252=12,51 и т.д.
1.4. Чтобы рассчитать оценку сезонной вариации Is необходимо найти разность между уровнями и центрированными скользящими средними:
IS3=Y3-Y3*=11,1-12,3=-1,2
IS4=Y4-Y4*=15-12,51=2,49 и т.д.
Таблица 1
Подготовка данных для построения аддитивной тренд-сезонной модели
t yt
Скользящая средняя за 4 квартала Центрированная скольз. средняя Оценка сезонной вариации
1 11,5      
2 10,8      
3 11,1 12,1 12,3 -1,2
4 15 12,5 12,5125 2,4875
5 13,1 12,525 12,65 0,45
6 10,9 12,775 12,9 -2
7 12,1 13,025 13,175 -1,075
8 16 13,325 13,3875 2,6125
9 14,3 13,45 13,4875 0,8125
10 11,4 13,525 13,6375 -2,2375
11 12,4 13,75 13,8375 -1,4375
12 16,9 13,925 14,075 2,825
13 15 14,225 14,3625 0,6375
14 12,6 14,5 14,5375 -1,9375
15 13,5 14,575    
16 17,2      
1.5. Расчет скорректированной сезонной компоненты St необходимо выполнить в расчетной таблице 2, которую можно заполнить в 4 шага:
Шаг 1: оценку сезонной вариации Is необходимо разместить согласно соответствующему году и соответствующему кварталу в расчетной таблице 2;
Шаг 2: суммы и средние по столбцам в расчетной таблице 2 необходимо определить с помощью функций СУММ(…) и СРЗНАЧ(…).
Шаг 3: необходимо определить сумму по строке «Среднее» в расчетной таблице 2. Она, к сожалению, не равна нулю. Поэтому определим коэффициент корректировки:
k=-0,02084=-0,00521
Шаг 4: из средней сезонной компоненты для каждого квартала вычтем коэффициент корректировки, равный -0,00521. Полученный результат из последней строки расчетной таблицы 2 перенесем в столбец St – Сезонная компонента, расчетной таблицы 1.
Таблица 2
Скорректированная сезонная компонента
Год № квартала  
  1 2 3 4  
1     -1,2 2,4875  
2 0,45 -2 -1,075 2,6125  
3 0,813 -2,238 -1,4375 2,825  
4 0,637 -1,938      
Итого 1,9 -6,175 -3,7125 7,925  
Среднее 0,6333 -2,0583 -1,2375 2,6417 -0,0208
S 0,6385 -2,0531 -1,2323 2,6469 0,0000
1.6 . В расчетной таблице 1 необходимо заполнить столбец:
Yt-St=Tt+Et
1.7. В расчетной таблице 1 необходимо заполнить столбец Tt – Трендовая компонента, применив статистическую функцию Тенденция к данным из столбца Yt-St=Tt+Et. В Главном меню MS Excel выберем: Формулы – Вставить функцию – Статистические – ТЕНДЕНЦИЯ(…).
Рис.2. Окно параметров функции ТЕНДЕНЦИЯ (…) с введенными значениями в MS Excel
1.8. В расчетной таблице 1 необходимо заполнить столбец T+S и столбец E=Y-(T+S) для t=1…16.
Таблица 1
Подготовка данных для построения аддитивной тренд-сезонной модели
t yt
Скользящая средняя за 4 квартала Центри-рованная скольз. средняя Оценка сезонной вариации S Y-S=T+E T T+S E=Y-(T+S)
1 11,5       0,64 10,86 11,82 12,46 -0,96
2 10,8       -2,05 12,85 12,02 9,97 0,83
3 11,1 12,1 12,3 -1,2 -1,23 12,33 12,23 11,00 0,10
4 15 12,5 12,5125 2,4875 2,65 12,35 12,44 15,08 -0,08
5 13,1 12,525 12,65 0,45 0,64 12,46 12,64 13,28 -0,18
6 10,9 12,775 12,9 -2 -2,05 12,95 12,85 10,79 0,11
7 12,1 13,025 13,175 -1,075 -1,23 13,33 13,05 11,82 0,28
8 16 13,325 13,3875 2,6125 2,65 13,35 13,26 15,91 0,09
9 14,3 13,45 13,4875 0,8125 0,64 13,66 13,47 14,10 0,20
10 11,4 13,525 13,6375 -2,2375 -2,05 13,45 13,67 11,62 -0,22
11 12,4 13,75 13,8375 -1,4375 -1,23 13,63 13,88 12,64 -0,24
12 16,9 13,925 14,075 2,825 2,65 14,25 14,08 16,73 0,17
13 15 14,225 14,3625 0,6375 0,64 14,36 14,29 14,93 0,07
14 12,6 14,5 14,5375 -1,9375 -2,05 14,65 14,49 12,44 0,16
15 13,5 14,575     -1,23 14,73 14,70 13,47 0,03
16 17,2       2,65 14,55 14,91 17,55 -0,35
Используя из Главного меню MS Excel: Вставка – Диаграммы – Точечная – Точечная с гладкими кривыми и маркерами, представим графически компоненты: сезонную – St, трендовую – Tt, случайную – Et.
Рис. 3. Сезонная компонента в доходе предприятия за 4 года
Рис. 4. Трендовая компонента в доходе предприятия за 4 года
Рис. 5. Случайная компонента в доходе предприятия за 4 года
1.9. Необходимо продлить переменную t значениями 17, 18, 19, 20. Затем надо получить прогноз тренда Т, продлив функцию Тенденция на значение t=17, затем на значение t=18, t=19, t=20.
1.10. Получить прогноз дохода, просуммировав T17 + S1, T18 + S2, T19 + S3, T20 + S4 соответственно.
Таблица 1
Подготовка данных для построения аддитивной тренд-сезонной модели
t yt
Скользящая средняя за 4 квартала Центри-рованная скольз. средняя Оценка сезонной вариации S Y-S=T+E T T+S E=Y-(T+S)
1 11,5       0,64 10,86 11,82 12,46 -0,96
2 10,8       -2,05 12,85 12,02 9,97 0,83
3 11,1 12,1 12,3 -1,2 -1,23 12,33 12,23 11,00 0,10
4 15 12,5 12,5125 2,4875 2,65 12,35 12,44 15,08 -0,08
5 13,1 12,525 12,65 0,45 0,64 12,46 12,64 13,28 -0,18
6 10,9 12,775 12,9 -2 -2,05 12,95 12,85 10,79 0,11
7 12,1 13,025 13,175 -1,075 -1,23 13,33 13,05 11,82 0,28
8 16 13,325 13,3875 2,6125 2,65 13,35 13,26 15,91 0,09
9 14,3 13,45 13,4875 0,8125 0,64 13,66 13,47 14,10 0,20
10 11,4 13,525 13,6375 -2,2375 -2,05 13,45 13,67 11,62 -0,22
11 12,4 13,75 13,8375 -1,4375 -1,23 13,63 13,88 12,64 -0,24
12 16,9 13,925 14,075 2,825 2,65 14,25 14,08 16,73 0,17
13 15 14,225 14,3625 0,6375 0,64 14,36 14,29 14,93 0,07
14 12,6 14,5 14,5375 -1,9375 -2,05 14,65 14,49 12,44 0,16
15 13,5 14,575     -1,23 14,73 14,70 13,47 0,03
16 17,2       2,65 14,55 14,91 17,55 -0,35
17         0,64   15,11 15,75  
18         -2,05   15,32 13,27  
19         -1,23   15,52 14,29  
20         2,65   15,73 18,38  
Используя из Главного меню MS Excel: Вставка – Диаграммы – Точечная – Точечная с гладкими кривыми и маркерами, представим графически прогноз дохода предприятия на 4 квартала следующего года (красным цветом)