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

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



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



Рис. 5. Трендовая компонента в доходе предприятия за 4 года



Рис. 6. Случайная компонента в доходе предприятия за 4 года

9. 
   Необходимо продлить переменную t значениями 17, 18, 19, 20. Затем надо получить прогноз тренда Т, продлив функцию Тенденция на значение t=17, затем на значение t=18, t=19, t=20.
   
10. 
    Получить прогноз дохода, просуммировав T₁₇ + S₁, T₁₈ + S₂, T₁₉ + S₃, T₂₀ + S₄ соответственно.
    

Таблица 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
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 квартала следующего года

Задание 2. Имеются поквартальные данные о прибыли предприятия за 4 года, млн. руб. Построить мультипликативную модель временного ряда и получить прогноз прибыли на два следующих квартала.

t	1	2	3	4	5	6	7	8
	40	50	60	70	60	80	100	110
t	9	10	11	12	13	14	15	16
	50	70	80	130	30	50	60	70

Для этого необходимо:

1. 
   Сформировать расчетную таблицу: Скользящая средняя за 4 квартала, Центрированная скользящая средняя, Оценка сезонной вариации, S, , T, , .
   
2. 
   Выполнить расчет скользящей средней за 4 квартала: просуммировать уровни ряда за каждые четыре квартала со сдвигом на один момент времени; разделить полученные суммы на 4, найдем скользящие средние.
   
3. 
   Выполнить расчет центрированной скользящей средней: найти центрированные скользящие средние как средние значения из двух последовательных скользящих средних.
   
4. 
   Выполнить оценку сезонной вариации S: найти отношение между уровнями и центрированными скользящими средними.
   
5. 
   Выполнить расчет скорректированной сезонной компоненты в таблице:
   

   Год	№ квартала
   	1	2	3	4
   1
   2
   3
   4
   Итого
   Среднее
   S

6. 
   Выполнить расчет .
   
7. 
   Используя статистическую функцию Тенденция к столбцу , выполнить расчет T для t=1…16.
   
8. 
   Выполнить расчет , для t=1…16.
   
9. 
   Продлить переменную t значениями 17, 18. Получить прогноз тренда Т, продлив функцию Тенденция на значение t=17, затем на значение t=18.
   
10. 
    Получить прогноз прибыли, умножив T₁₇ и T₁₈ со значениями S₁ и S₂ соответственно.
    

---

Решение.

Выполним графическую визуализацию исходного временного ряда дохода предприятия. Используем из Главного меню 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	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 согласно формулам:
   

и т. д.

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

и т. д.

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

и т. д.

Таблица 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

---