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

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

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

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

---

Комментарий: сумма средних сезонных компонент в мультипликативной модели равна количеству циклов в исследуемом периоде (нашем случае равна 4, т.к. в году четыре квартала);

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

.

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

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

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



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

8. 
   В расчетной таблице 1 необходимо заполнить столбец и столбец для 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

---

Используя из Главного меню 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. 
    Получить прогноз дохода, умножив соответственно.
    

Таблица 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 квартале, сохранить в прогнозе сезонную волну.



Рис. 7. Прогноз прибыли предприятия на 4 квартала следующего года

---