Файл: Лабораторная работа 3 по теме Моделирование изолированного динамического ряда 2 содержание.pdf

13
Таблица 2
Скорректированная сезонная компонента
Год
№ квартала
1 2
3 4
1
-1,25 2,55 2
0,575
-2,075
-1,1 2,7 3
0,55
-2,025
-1,475 2,875 4
0,675
-1,775
Итого
1,8
-5,875
-3,825 8,125
Среднее
0,6
-1,9583
-1,275 2,70833 0,075
S
t
0,58125
-1,9770
-1,2937 2,68958 0
6. В расчетной таблице 1 необходимо заполнить столбец:
t
t
t
t
Y
S
T
E



7. В расчетной таблице 1 необходимо заполнить столбец T
t
– Трендовая компонента, применив статистическую функцию Тенденция к данным из столбца
t
t
t
t
Y
S
T
E



. В Главном меню MS Excel выберем: Формулы – Вста- вить функцию – Статистические – ТЕНДЕНЦИЯ(…).
Рис. 2. Окно параметров функции ТЕНДЕНЦИЯ (…) в MS Excel
Чтобы определить трендовую компоненту T
t в поле Известные значения
Y
необходимо выбрать столбец
t
t
t
t
Y
S
T
E



. В поле Известные значения X необходимо выбрать столбец t, который содержит сквозной порядковый номер времени (в нашем случае квартала). Оба поля надо зафиксировать значком $.
В поле Новые значения Х необходимо выбрать ячейку для t=1. В поле Кон- станта необходимо ввести 1 (что соответствует логическому значению Ис-
тина
), чтобы уравнение тренда вычислить обычным образом, со свободным коэффициентом (рис 3).

14
Рис.3. Окно параметров функции ТЕНДЕНЦИЯ (…) с введенными зна- чениями в MS Excel
8. В расчетной таблице 1 необходимо заполнить столбец T+S и столбец


E
Y
T
S



для t=1…16.
Таблица 1
Подготовка данных для построения аддитивной тренд-сезонной модели
t
Y
t
Скользя- щая сред- няя за
4 квартала
Центриро- ванная скольз. средняя
Оценка се- зонной ва- риации
S
Y
-S
=T+E
T
T
+S
E
=Y-
(T+S)
1 6
0,58 5,42 5,90 6,48
-0,48 2
4,4
-1,98 6,38 6,09 4,11 0,29 3
5 6,1 6,25
-1,25
-1,29 6,29 6,27 4,98 0,02 4
9 6,4 6,45 2,55 2,69 6,31 6,46 9,15
-0,15 5
7,2 6,5 6,625 0,575 0,58 6,62 6,65 7,23
-0,03 6
4,8 6,75 6,875
-2,075
-1,98 6,78 6,83 4,86
-0,06 7
6 7
7,1
-1,1
-1,29 7,29 7,02 5,73 0,27 8
10 7,2 7,3 2,7 2,69 7,31 7,21 9,90 0,10 9
8 7,4 7,45 0,55 0,58 7,42 7,39 7,97 0,03 10 5,6 7,5 7,625
-2,025
-1,98 7,58 7,58 5,60 0,00 11 6,4 7,75 7,875
-1,475
-1,29 7,69 7,77 6,47
-0,07 12 11 8
8,125 2,875 2,69 8,31 7,95 10,64 0,36 13 9
8,25 8,325 0,675 0,58 8,42 8,14 8,72 0,28 14 6,6 8,4 8,375
-1,775
-1,98 8,58 8,33 6,35 0,25 15 7
8,35
-1,29 8,29 8,51 7,22
-0,22 16 10,8 2,69 8,11 8,70 11,39 -0,59
Используя из Главного меню MS Excel: Вставка – Диаграммы – Точеч- ная – Точечная с гладкими кривыми и маркерами, представим графически ком- поненты: сезонную – S
t
, трендовую – T
t
, случайную – E
t

15
Рис. 4. Сезонная компонента в доходе предприятия за 4 года
Рис. 5. Трендовая компонента в доходе предприятия за 4 года
Рис. 6. Случайная компонента в доходе предприятия за 4 года
9. Необходимо продлить переменную t значениями 17, 18, 19, 20. Затем надо получить прогноз тренда Т, продлив функцию Тенденция на значение
t
=17, затем на значение t=18, t=19, t=20.
10. Получить прогноз дохода, просуммировав T
17
+ S
1
, T
18
+ S
2
, T
19
+ S
3
,
T
20
+ S
4
соответственно.
-3,00
-2,00
-1,00 0,00 1,00 2,00 3,00 0
5 10 15 20
S - сезонная компонента
0,00 2,00 4,00 6,00 8,00 10,00 0
5 10 15 20
T - трендовая компонента
-0,80
-0,60
-0,40
-0,20 0,00 0,20 0,40 0,60 0
5 10 15 20
E- случайная компонента

16
Таблица 1
Подготовка данных для построения аддитивной тренд-сезонной модели
t
Y
t
Скользя- щая сред- няя за
4 квар- тала
Центриро- ванная скольз. средняя
Оценка сезон- ной ва- риации
S
Y-S=
=T+E
T
T+S
E=Y-
-
(T+S)
1 6
0,58 5,42 5,90 6,48
-0,48 2
4,4
-1,98 6,38 6,09 4,11 0,29 3
5 6,1 6,25
-1,25
-1,29 6,29 6,27 4,98 0,02 4
9 6,4 6,45 2,55 2,69 6,31 6,46 9,15
-0,15 5
7,2 6,5 6,625 0,575 0,58 6,62 6,65 7,23
-0,03 6
4,8 6,75 6,875
-2,075
-1,98 6,78 6,83 4,86
-0,06 7
6 7
7,1
-1,1
-1,29 7,29 7,02 5,73 0,27 8
10 7,2 7,3 2,7 2,69 7,31 7,21 9,90 0,10 9
8 7,4 7,45 0,55 0,58 7,42 7,39 7,97 0,03 10 5,6 7,5 7,625
-2,025
-1,98 7,58 7,58 5,60 0,00 11 6,4 7,75 7,875
-1,475
-1,29 7,69 7,77 6,47
-0,07 12 11 8
8,125 2,875 2,69 8,31 7,95 10,64 0,36 13 9
8,25 8,325 0,675 0,58 8,42 8,14 8,72 0,28 14 6,6 8,4 8,375
-1,775
-1,98 8,58 8,33 6,35 0,25 15 7
8,35
-1,29 8,29 8,51 7,22
-0,22 16 10,8 2,69 8,11 8,70 11,39
-0,59 17 0,58 8,88 9,47 18
-1,98 9,07 7,09 19
-1,29 9,26 7,96 20 2,69 9,44 12,13
Используя из Главного меню MS Excel: Вставка – Диаграммы – Точеч- ная – Точечная с гладкими кривыми и маркерами, представим графически про- гноз дохода предприятия на 4 квартала следующего года (красным цветом).
Вывод 2: Предварительное выделение сезонной и трендовой компо- ненты позволило спрогнозировать снижение дохода в 1 и 2 квартале и сохра- нить в прогнозе сезонную волну и растущий тренд.
Рис. 7. Прогноз дохода предприятия на 4 квартала следующего года
0,00 2,00 4,00 6,00 8,00 10,00 12,00 14,00 0
5 10 15 20 25
T+S

17
Задание 2. Имеются поквартальные данные о прибыли предприятия за
4 года, млн. руб. Построить мультипликативную модель временного ряда и получить прогноз прибыли на два следующих квартала.
t
1 2
3 4
5 6
7 8
t
Y
40 50 60 70 60 80 100 110
t
9 10 11 12 13 14 15 16
t
Y
50 70 80 130 30 50 60 70
Для этого необходимо:
1.
Сформировать расчетную таблицу: Скользящая средняя за 4 квар- тала, Центрированная скользящая средняя, Оценка сезонной вариации, S,
/
Y S
T E
 
, T,
T S

,


/
E
Y
T S


2.
Выполнить расчет скользящей средней за 4 квартала: просумми- ровать уровни ряда за каждые четыре квартала со сдвигом на один момент времени; разделить полученные суммы на 4, найдем скользящие средние.
3.
Выполнить расчет центрированной скользящей средней: найти центрированные скользящие средние как средние значения из двух последо- вательных скользящих средних.
4.
Выполнить оценку сезонной вариации S: найти отношение между уровнями и центрированными скользящими средними.
5.
Выполнить расчет скорректированной сезонной компоненты в таблице:
Год
№ квартала
1 2
3 4
1 2
3 4
Итого
Среднее
S
6.
Выполнить расчет
/
Y S
T E
 
7.
Используя статистическую функцию Тенденция к столбцу
/
Y S
T E
 
, выполнить расчет T для t=1…16.
8.
Выполнить расчет
T S

,


/
E
Y
T S


для t=1…16.
9.
Продлить переменную t значениями 17, 18. Получить прогноз тренда Т, продлив функцию Тенденция на значение t=17, затем на значение
t
=18.

18 10. Получить прогноз прибыли, умножив T
17
и T
18
со значениями S
1
и
S
2
соответственно.
Решение.
Выполним графическую визуализацию исходного временного ряда до- хода предприятия. Используем из Главного меню MS Excel: Вставка – Диа- граммы – Точечная – Точечная с гладкими кривыми и маркерами.
Рис. 1. Динамика прибыли предприятия за 4 года
Вывод 1: График временного ряда демонстрирует линейный тренд с се- зонной волной растущей амплитуды. В силу наличия тренда и растущей ам- плитуды сезонных колебаний математическое ожидание прибыли зависит от времени, и, следовательно, процесс является нестационарным. Для прогноза представляется возможным применить мультипликативную (в силу растущей амплитуды в сезонной волне) тренд-сезонную модель:
t
t
t
t
Y
T S E



1. Чтобы сформировать расчетную таблицу 1 (последовательно выпол- няя пункты 1-4 данной лабораторной работы), необходимо использовать Ма- стер формул MS Excel. В данном пункте достаточно заполнить столбцы: t – сквозной порядковый номер периода (или момента) времени, Y
t
– уровень вре- менного ряда для данного периода (момента) времени.
0 20 40 60 80 100 120 140 0
5 10 15 20
Yt

19
Таблица 1
Подготовка данных для построения мультипликативной тренд-сезонной модели
t
Y
t
Скользя- щая сред- няя за 4 квартала
Центри- рованная скольз. средняя
Оценка сезонной вариации
S
t
Y
t
/S
t
=
=T
t
*E
t
T
t
T
t
*S
t
E
t
=
=Y
t
/
(T
t
*S
t
)
1 40 2
50 3
60 4
70 5
60 6
80 7
100 8
110 9
50 10 70 11 80 12 130 13 30 14 50 15 60 16 70 2. Чтобы выполнить расчет скользящей средней за 4 квартала в расчет- ной таблице 1 надо последовательно просуммировать уровни ряда за каждые четыре квартала со сдвигом на один момент времени и затем разделить каж- дую полученную сумму на 4 согласно формулам:
1 2
3 4
3 2
3 4
5 4
3 4
5 6
5 40 50 60 70 55.
4 4
50 60 70 60 60.
4 4
60 70 60 80 67,5.
4 4
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y



























и т. д.
3. Чтобы выполнить расчет центрированной скользящей средней в рас- четной таблице 1 необходимо определить средние значения из двух последо- вательных скользящих средних:
*
3 4
3
*
4 5
4 55 60 57,5.
2 2
60 67,5 63, 75.
2 2
Y
Y
Y
Y
Y
Y










и т. д.

20 4. Чтобы рассчитать оценку сезонной вариации Is необходимо разделить исходные уровни на центрированные скользящие средние:
*
3 3
3
*
4 4
4
/
60 / 57,5 1,043.
/
70 / 63,75 1,098.
Is
Y Y
Is
Y
Y






и т. д.
Таблица 1
Подготовка данных для построения мультипликативной тренд-сезонной модели
t
Yt
Скользя- щая сред- няя за
4 квартала
Центриро- ванная скольз. средняя
Оценка се- зонной ва- риации
St
Yt/St =
=Tt*Et
Tt
Tt*St
Et =
=Yt/(Tt*St)
1 40 2
50 3
60 55 57,5 1,043478 4
70 60 63,75 1,098039 5
60 67,5 72,5 0,827586 6
80 77,5 82,5 0,969697 7 100 87,5 86,25 1,15942 8 110 85 83,75 1,313433 9
50 82,5 80 0,625 10 70 77,5 80 0,875 11 80 82,5 80 1
12 130 77,5 75 1,733333 13 30 72,5 70 0,428571 14 50 67,5 60 0,833333 15 60 52,5 16 70 5. Расчет скорректированной сезонной компоненты S
t необходимо вы- полнить в расчетной таблице 2, которую можно заполнить в 4 шага:
Шаг 1: оценку сезонной вариации Is необходимо разместить согласно соответствующему году и соответствующему кварталу в расчетной таблице 2;
Шаг 2: суммы и средние по столбцам в расчетной таблице 2 необходимо определить с помощью функций СУММ(…) и СРЗНАЧ(…).
Комментарий: сумма средних сезонных компонент в мультипликатив- ной модели равна количеству циклов в исследуемом периоде (нашем случае равна 4, т.к. в году четыре квартала);
Шаг 3: необходимо определить сумму по строке «Среднее» в расчетной таблице 2. Она, к сожалению, не равна 4. Поэтому определим коэффициент корректировки:
4 1, 008 3,969
k 


21
Шаг 4: каждую среднюю сезонную компоненту для каждого квартала умножим на коэффициент корректировки, равный 1,008. Полученный резуль- тат из последней строки расчетной таблицы 2 перенесем в столбец S
t
– Сезон- ная компонента, расчетной таблицы 1.
Таблица 2
Скорректированная сезонная компонента
№ квартала
Год
1 2
3 4
1 1,043478 1,098039 2
0,827586 0,969697 1,15942 1,313433 3
0,625 0,875 1
1,733333 4
0,428571 0,833333
Средняя
0,627053 0,892677 1,067633 1,381602 3,968964
Скорр S
0,631956 0,899657 1,075981 1,392405 4
6. В расчетной таблице 1 необходимо заполнить столбец
/
t
t
t
t
Y
S
T E


7. В расчетной таблице 1 необходимо заполнить столбец Tt – Трендовая компонента, применив статистическую функцию Тенденция к данным из столбца
/
t
t
t
t
Y
S
T E


. В Главном меню MS Excel выберем: Формулы – Вста- вить функцию – Статистические – ТЕНДЕНЦИЯ(…).
Рис. 2. Окно параметров функции ТЕНДЕНЦИЯ (…) в MS Excel
Чтобы определить трендовую компоненту T
t в поле Известные значения
Y
необходимо выбрать столбец
t
t
t
t
Y
S
T
E



. В поле Известные значения X необходимо выбрать столбец t, который содержит сквозной порядковый номер времени (в нашем случае квартала). Оба поля надо зафиксировать значком $.
В поле Новые значения Х необходимо выбрать ячейку для t=1. В поле Кон-

22 станта необходимо ввести 1 (что соответствует логическому значению Ис-
тина
), чтобы уравнение тренда вычислить обычным образом, со свободным коэффициентом (рис 3).
Рис.3. Окно параметров функции ТЕНДЕНЦИЯ (…) с введенными значениями в MS Excel
8. В расчетной таблице 1 необходимо заполнить столбец
T S

и столбец


/
E
Y
T S


для t=1…16.
Таблица 1
Подготовка данных для построения мультипликативной тренд-сезонной модели
t
Yt
Сколь- зящая средняя за 4 квар- тала
Цен- триро- ванная скольз. сред- няя
Оценка сезонной вариации
St
Yt/St =
=Tt*Et
Tt
Tt*St
Et =
=Yt/(Tt*St)
1 40 0,631956 63,29556 73,50572 46,45238 0,861097 2
50 0,899657 55,57672 72,99194 65,66773 0,761409 3
60 55 57,5 1,043478 1,075981 55,76305 72,47816 77,98515 0,769377 4
70 60 63,75 1,098039 1,392405 50,27271 71,96438 100,2036 0,698578 5
60 67,5 72,5 0,827586 0,631956 94,94333 71,4506 45,15363 1,328797 6
80 77,5 82,5 0,969697 0,899657 88,92276 70,93682 63,81882 1,253549 7 100 87,5 86,25 1,15942 1,075981 92,93841 70,42304 75,77388 1,319716 8 110 85 83,75 1,313433 1,392405 78,99998 69,90926 97,34203 1,130036 9
50 82,5 80 0,625 0,631956 79,11945 69,39548 43,85488 1,140124 10 70 77,5 80 0,875 0,899657 77,80741 68,88169 61,96991 1,12958 11 80 82,5 80 1
1,075981 74,35073 68,36791 73,5626 1,087509 12 130 77,5 75 1,733333 1,392405 93,36361 67,85413 94,48047 1,375946 13 30 72,5 70 0,428571 0,631956 47,47167 67,34035 42,55613 0,704951 14 50 67,5 60 0,833333 0,899657 55,57672 66,82657 60,12101 0,831656 15 60 52,5 1,075981 55,76305 66,31279 71,35133 0,840909 16 70 1,392405 50,27271 65,79901 91,6189 0,764035

23
Используя из Главного меню MS Excel: Вставка – Диаграммы – Точеч- ная – Точечная с гладкими кривыми и маркерами, представим графически ком- поненты: сезонную – S
t
, трендовую – T
t
, случайную – E
t
Рис. 4. Сезонная компонента в прибыли предприятия за 4 года
Рис. 5. Трендовая компонента в прибыли предприятия за 4 года
Рис. 6. Случайная компонента в прибыли предприятия за 4 года
0 0,2 0,4 0,6 0,8 1
1,2 1,4 1,6 0
5 10 15 20
S - сезонная компонента
65 66 67 68 69 70 71 72 73 74 0
5 10 15 20
T - трендовая компонента
0 0,2 0,4 0,6 0,8 1
1,2 1,4 1,6 0
5 10 15 20
E - случайная компонента

24 9. Необходимо продлить переменную t значениями 17, 18, 19, 20. Затем надо получить прогноз тренда Т, продлив функцию Тенденция на значение t=17, затем на значение t=18, t=19, t=20.
10. Получить прогноз дохода, умножив
17 1
18 2
19 3
20 4
,
,
,
T
S
T
S
T
S
T
S




со- ответственно.
Таблица 1
Подготовка данных для построения мультипликативной тренд-сезонной модели
t
Yt
Сколь- зящая сред- няя за
4 квар- тала
Цен- триро- ванная скольз. сред- няя
Оценка сезон- ной ва- риации
St
Yt/St =
=Tt*Et
Tt
Tt*St
Et =
=Yt/(Tt*St)
1 40 0,631956 63,29556 73,50572 46,45238 0,861097 2
50 0,899657 55,57672 72,99194 65,66773 0,761409 3
60 55 57,5 1,043478 1,075981 55,76305 72,47816 77,98515 0,769377 4
70 60 63,75 1,098039 1,392405 50,27271 71,96438 100,2036 0,698578 5
60 67,5 72,5 0,827586 0,631956 94,94333 71,4506 45,15363 1,328797 6
80 77,5 82,5 0,969697 0,899657 88,92276 70,93682 63,81882 1,253549 7 100 87,5 86,25 1,15942 1,075981 92,93841 70,42304 75,77388 1,319716 8 110 85 83,75 1,313433 1,392405 78,99998 69,90926 97,34203 1,130036 9
50 82,5 80 0,625 0,631956 79,11945 69,39548 43,85488 1,140124 10 70 77,5 80 0,875 0,899657 77,80741 68,88169 61,96991 1,12958 11 80 82,5 80 1
1,075981 74,35073 68,36791 73,5626 1,087509 12 130 77,5 75 1,733333 1,392405 93,36361 67,85413 94,48047 1,375946 13 30 72,5 70 0,428571 0,631956 47,47167 67,34035 42,55613 0,704951 14 50 67,5 60 0,833333 0,899657 55,57672 66,82657 60,12101 0,831656 15 60 52,5 1,075981 55,76305 66,31279 71,35133 0,840909 16 70 1,392405 50,27271 65,79901 91,6189 0,764035 17 0,631956 65,28523 41,25738 18 0,899657 64,77145 58,2721 19 1,075981 64,25766 69,14005 20 1,392405 63,74388 88,75733
Используя из Главного меню MS Excel: Вставка – Диаграммы – Точеч- ная – Точечная с гладкими кривыми и маркерами, представим графически про- гноз прибыли предприятия на 4 квартала следующего года (красным цветом).
Вывод 2: Предварительное выделение сезонной и трендовой компо- ненты позволило спрогнозировать снижение прибыли в 1 квартале, сохранить в прогнозе сезонную волну.