Файл: Методические указания по выполнению расчётнографической работы и организации самостоятельной работы содержание.docx

Решение.

Выполним графическую визуализацию исходного временного ряда дохода предприятия. Используем из Главного меню MS Excel: Вставка – Диаграммы – Точечная – Точечная с гладкими кривыми и маркерами.



Рис. 1. Динамика дохода предприятия за 4 года

Вывод 1: График временного ряда демонстрирует линейный тренд с периодически повторяющейся сезонной волной примерно одинаковой амплитуды. В силу наличия тренда математическое ожидание дохода зависит от времени, и, следовательно, процесс является нестационарным. Для прогноза представляется возможным применить аддитивную (в силу примерно одинаковой амплитуды в сезонной волне) тренд-сезонную модель:

.

1. 
   Чтобы сформировать расчетную таблицу 1 (последовательно выполняя пункты 1-4 данной лабораторной работы), необходимо использовать Мастер формул MS Excel. В данном пункте достаточно заполнить столбцы: t – сквозной порядковый номер периода (или момента) времени, Y_t – уровень временного ряда для данного периода (момента) времени.
   

Таблица 1

Подготовка данных для построения аддитивной тренд-сезонной модели

t	Yt	Скользящая средняя за 4 квартала	Центрированная скольз. средняя	Оценка сезонной вариации	St	Yt-St = Tt+Et	Tt	Tt+St	Et=Yt-(Tt+St)
1	6
2	4,4
3	5
4	9
5	7,2
6	4,8
7	6
8	10
9	8
10	5,6
11	6,4
12	11
13	9
14	6,6
15	7
16	10,8

---

2. 
   Чтобы выполнить расчет скользящей средней за 4 квартала в расчетной таблице 1 надо последовательно просуммировать уровни ряда за каждые четыре квартала со сдвигом на один момент времени и затем разделить каждую полученную сумму на 4 согласно формулам:
   

и т. д.

3. 
   Чтобы выполнить расчет центрированной скользящей средней в расчетной таблице 1 необходимо определить средние значения из двух последовательных скользящих средних:
   

и т. д.

4. 
   Чтобы рассчитать оценку сезонной вариации Is необходимо найти разность между уровнями и центрированными скользящими средними:
   

и т. д.

Таблица 1

Подготовка данных для построения аддитивной тренд-сезонной модели

t	Yt	Скользящая средняя за 4 квартала	Центрированная скольз. средняя	Оценка сезонной вариации	St	Yt-St = Tt+Et	Tt	Tt+St	Et=Yt-(Tt+St)
1	6
2	4,4
3	5	6,1	6,25	-1,25
4	9	6,4	6,45	2,55
5	7,2	6,5	6,625	0,575
6	4,8	6,75	6,875	-2,075
7	6	7	7,1	-1,1
8	10	7,2	7,3	2,7
9	8	7,4	7,45	0,55
10	5,6	7,5	7,625	-2,025
11	6,4	7,75	7,875	-1,475
12	11	8	8,125	2,875
13	9	8,25	8,325	0,675
14	6,6	8,4	8,375	-1,775
15	7	8,35
16	10,8

---

5. 
   Расчет скорректированной сезонной компоненты S_t необходимо выполнить в расчетной таблице 2, которую можно заполнить в 4 шага:
   

Шаг 1: оценку сезонной вариации Is необходимо разместить согласно соответствующему году и соответствующему кварталу в расчетной таблице 2;

Шаг 2: суммы и средние по столбцам в расчетной таблице 2 необходимо определить с помощью функций СУММ(…) и СРЗНАЧ(…).

---

Комментарий: сумма средних сезонных компонент в аддитивной модели равна нулю.

Шаг 3: необходимо определить сумму по строке «Среднее» в расчетной таблице 2. Она, к сожалению, не равна нулю. Поэтому определим коэффициент корректировки:

.

Шаг 4: из средней сезонной компоненты для каждого квартала вычтем коэффициент корректировки, равный 0,01875. Полученный результат из последней строки расчетной таблицы 2 перенесем в столбец S_t – Сезонная компонента, расчетной таблицы 1.

Таблица 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
St	0,58125	-1,9770	-1,2937	2,68958	0

---

6. 
   В расчетной таблице 1 необходимо заполнить столбец:
   

7. 
   В расчетной таблице 1 необходимо заполнить столбец T_t – Трендовая компонента, применив статистическую функцию Тенденция к данным из столбца . В Главном меню MS Excel выберем: Формулы – Вставить функцию – Статистические – ТЕНДЕНЦИЯ(…).
   

Рис. 2. Окно параметров функции ТЕНДЕНЦИЯ (…) в MS Excel

Чтобы определить трендовую компоненту T_t в поле Известные значения Y необходимо выбрать столбец . В поле Известные значения X необходимо выбрать столбец t, который содержит сквозной порядковый номер времени (в нашем случае квартала). Оба поля надо зафиксировать значком $. В поле Новые значения Х необходимо выбрать ячейку для t=1. В поле Константа необходимо ввести 1 (что соответствует логическому значению Истина), чтобы уравнение тренда вычислить обычным образом, со свободным коэффициентом (рис 3).



Рис.3. Окно параметров функции ТЕНДЕНЦИЯ (…) с введенными значениями в MS Excel

8. 
   В расчетной таблице 1 необходимо заполнить столбец T+S и столбец для t=1…16.
   

Таблица 1

Подготовка данных для построения аддитивной тренд-сезонной модели

t	Yt	Скользя-щая средняя за 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

---