Анализ временных рядов и прогнозирование в Excel

Министерство образования  и науки Российской Федерации

Государственное образовательное  учреждение высшего профессионального  образования

Новгородский государственный  университет имени Ярослава Мудрого

 

Институт экономики  и управления

Кафедра Прикладной экономики

 

 

 

 

 

 

 

Отчет по лабораторной работе

Анализ временных  рядов и прогнозирование в Excel

По дисциплине «Прогнозирование в социально-экономических исследованиях»

по специальности 080502 – экономика и управление на предприятии (строительство)

 

 

 

 

 

 

 

Проверила

Преподаватель

__________

«____»___________2012г.

 

Выполнил

Студент группы

«____»___________2012г.

 

 

 

Содержание

 

Введение...................................................................................................................3

1. Прогнозы с применением метода скользящего среднего.................................5

2. Прогнозы  с применением функций регрессии.................................................9

3. Прогноз с помощью  функции экспоненциального сглаживания..................14

4. Аддитивная и мультипликативная модели......................................................21

5. Заключение.........................................................................................................32

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Введение

 

  Цель работы: исследовать возможности анализа временных рядов и прогнозирования в Excel и приобрести практические навыки применения данных возможностей.

      Временной  ряд - это последовательность  измерений в последовательные  моменты времени. Анализ временных рядов включает широкий спектр разведочных процедур и исследовательских методов, которые ставят две основные цели:

(a) определение природы  временного ряда;

(b) прогнозирование (предсказание  будущих значений временного  ряда по настоящим и прошлым  значениям). Обе эти цели требуют, чтобы модель ряда была идентифицирована и, более или менее, формально описана. Как только модель определена, вы можете с ее помощью интерпретировать рассматриваемые данные (например, использовать в вашей теории для понимания сезонного изменения цен на товары, если занимаетесь экономикой).      Не обращая внимания на глубину понимания и справедливость теории, вы можете экстраполировать затем ряд на основе найденной модели, т.е. предсказать его будущие значения.

     Вне зависимости от природы каждого временного ряда, можно выделить следующие основные типы задач, которые обычно решают при проведении анализа исходных данных.

      На первом  этапе пытаются построить простую  математическую систему или модель, которая описывает поведение временного ряда в сжатой форме.

Затем делается попытка  объяснить его поведение с  помощью других переменных и выяснить степень связи как между наблюдениями одного ряда, так и между разными  рядами;

   Полученные выше  в пунктах a и b результаты используют для прогнозирования временного ряда, причем более эффективны результаты пункта b, так как здесь более полно выясняются причинно-следственные связи.

    Часто возникает  необходимость контроля и управления  за динамикой развития одного  или нескольких временных рядов. Выработки определенных сигналов, предупреждающих о нежелательных последствиях.

     С помощью Excel можно прогнозировать изменения многих переменных величин, если есть приемлемая базовая линия (временной ряд)  для составления прогноза. Кроме того, из всех методов, предлагаемых электронными таблицами, необходимо выбрать наиболее приемлемые.

    Необходимые  требования к временному ряду:

• все временные периоды базовой линии должны иметь одинаковую продолжительность. Иначе говоря, нельзя смешивать, например, наблюдения за неделю с наблюдениями за 3 дня. На практике незначительные отклонения обычно игнорируются;
• наблюдения должны фиксироваться в одно и то же время (в одинаковый момент каждого периода);
• если при наблюдении за незначительный промежуток времени отсутствуют данные, лучше всего восполнить их хотя бы приблизительными значениями (например, рассчитав их как среднее арифметическое предшествующего и последующего значения).

 

 

 

 

 

 

 

 

 

 

1. Прогнозы  с применением метода скользящего  среднего

 

   Задание 1.

   По ряду динамики  потребность в работниках, заявленная  в службы занятости за месяц  за 2002-2010 годы (файл ORT.xls) спрогнозируйте  развитие изучаемого процесса  на два месяца следующего за  отчетным года методами скользящего  среднего. Используйте различную величину ширины окна скольжения и на основе какого-либо критерия (например, ошибка аппроксимации) выберите лучшую величину ширины окна скольжения, по которой целесообразно проводить прогнозирование. Сделайте выводы.

    Для выполнения  данного задания используем метод построения прогноза с помощью скользящего среднего, при котором прогноз любого периода получается как среднее по некоторому фиксированному числу значений временного ряда.

     Одним из  способов создания скользящего  среднего в Excel является прямое введение формулы СРЗНАЧ и автозаполнение на нужное количество периодов.

Другим способом является использование надстройки «Пакет анализа». В меню «Данные» необходимо выбрать  «Анализ данных», затем выбираем «Скользящее среднее». Результат  представлен в таблице 1.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Таблица 1

 

Период		№ периода	Потребность в работниках, заявленная в службы занятости за месяц (7)	Прогноз потребности в работниках, заявленных в службе занятости за месяц (интервал  3 мес.)	Стандартные погрешности	Прогноз потребности в работниках, заявленных в службе занятости за месяц (интервал  6 мес.)	Стандартные погрешности
		1	2	3	4	5	6
2002	январь	1	970
	февраль	2	921
	март	3	1057
	апрель	4	1203	982,67
	май	5	1700	1060,33
	июнь	6	1948	1320,00	238,24
	июль	7	1775	1617,00	302,39	1299,83
	август	8	1729	1807,67	291,56	1434,00
	сентябрь	9	1690	1817,33	198,69	1568,67
	октябрь	10	1480	1731,33	59,38	1674,17
	ноябрь	11	1429	1633,00	104,75	1720,33
	декабрь	12	1037	1533,00	109,44	1675,17	336,83
2003	январь	13	1078	1315,33	192,95	1523,33	287,84
	февраль	14	1197	1181,33	181,63	1407,17	285,54
	март	15	1283	1104,00	179,63	1318,50	282,32
	апрель	16	1323	1186,00	97,87	1250,67	282,56
	май	17	1342	1267,67	83,90	1224,50	268,01
	июнь	18	1979	1316,00	66,20	1210,00	254,23
	июль	19	1984	1548,00	251,33	1367,00	296,04
	август	20	1853	1768,33	278,66	1518,00	325,23
	сентябрь	21	1257	1938,67	282,61	1627,33	334,36
	октябрь	22	1261	1698,00	287,71	1623,00	365,99
	ноябрь	23	1096	1457,00	282,98	1612,67	391,08
	декабрь	24	802	1204,67	285,60	1571,67	433,30
2004	январь	25	1151	1053,00	194,27	1375,50	424,43
	февраль	26	435	1016,33	176,02	1236,67	381,02
	март	27	528	796,00	265,49	1000,33	435,84
	апрель	28	527	704,67	244,72	878,83	433,75
	май	29	517	496,67	232,70	756,50	419,89
	июнь	30	574	524,00	103,57	660,00	376,84
	июль	31	602	539,33	26,90	622,00	295,93
	август	32	804	564,33	29,83	530,50	295,30
	сентябрь	33	771	660,00	88,24	592,00	203,54
	октябрь	34	733	725,67	89,83	632,50	155,27
	ноябрь	35	590	769,33	89,65	666,83	126,73
	декабрь	36	490	698,00	70,80	679,00	118,21
2005	январь	37	582	604,33	93,20	665,00	136,73
	февраль	38	623	554,00	92,23	661,67	137,48
	март	39	642	565,00	75,76	631,50	106,87
	апрель	40	768	615,67	40,17	610,00	91,62
	май	41	782	677,67	63,82	615,83	107,35
	июнь	42	851	730,67	61,88	647,83	114,91
	июль	43	872	800,33	66,74	708,00	107,27
	август	44	535	835,00	46,80	756,33	112,61
	сентябрь	45	629	752,67	130,79	741,67	140,66
	октябрь	46	477	678,67	130,66	739,50	147,14
	ноябрь	47	384	547,00	135,09	691,00	159,45
	декабрь	48	489	496,67	81,77	624,67	179,10
2006	январь	49	606	450,00	79,82	564,33	172,09
	февраль	50	518	493,00	94,84	520,00	169,17
	март	51	700	537,67	69,94	517,17	146,63
	апрель	52	900	608,00	84,89	529,00	156,01
	май	53	1167	706,00	124,48	599,50	178,20
	июнь	54	1200	922,33	187,94	730,00	232,23
	июль	55	1409	1089,00	191,33	848,50	271,25
	август	56	1570	1258,67	177,75	982,33	320,44
	сентябрь	57	1781	1393,00	148,60	1157,67	361,97
	октябрь	58	1363	1586,67	174,83	1337,83	398,60
	ноябрь	59	1378	1571,33	193,65	1415,00	379,84
	декабрь	60	1272	1507,33	180,64	1450,17	336,63
2007	январь	61	1240	1337,67	146,56	1462,17	314,25
	февраль	62	1363	1296,67	89,91	1434,00	273,29
	март	63	1674	1291,67	64,84	1399,50	215,81
	апрель	64	2038	1425,67	152,72	1381,67	167,58
	май	65	2251	1691,67	249,47	1494,17	277,35
	июнь	66	2459	1987,67	289,23	1639,67	371,95
	июль	67	2566	2249,33	278,84	1837,50	443,50
	август	68	3020	2425,33	210,63	2058,50	483,06
	сентябрь	69	3767	2681,67	243,73	2334,67	558,04
	октябрь	70	4424	3117,67	430,46	2683,50	702,02
	ноябрь	71	3750	3737,00	579,68	3081,17	862,59
	декабрь	72	3668	3980,33	561,74	3331,00	843,23
2008	январь	73	3370	3947,33	448,35	3532,50	806,05
	февраль	74	3461	3596,00	246,41	3666,50	788,32
	март	75	3494	3499,67	208,65	3740,00	745,75
	апрель	76	4484	3441,67	135,78	3694,50	605,95
	май	77	4926	3813,00	389,22	3704,50	409,78
	июнь	78	5368	4301,33	530,15	3900,50	560,30
	июль	79	5405	4926,00	587,60	4183,83	737,96
	август	80	5556	5233,00	452,83	4523,00	812,15
	сентябрь	81	5737	5443,00	281,49	4872,17	851,20
	октябрь	82	5063	5566,00	154,48	5246,00	870,65
	ноябрь	83	4487	5452,00	253,86	5342,50	818,40
	декабрь	84	4010	5095,67	428,58	5269,33	772,34
2009	январь	85	3546	4520,00	510,52	5043,00	735,29
	февраль	86	3436	4014,33	532,26	4733,17	803,67
	март	87	3400	3664,00	420,88	4379,83	846,42
	апрель	88	3938	3460,67	302,77	3990,33	856,93
	май	89	4465	3591,33	242,10	3802,83	851,09
	июнь	90	4989	3934,33	367,63	3799,17	834,40
	июль	91	5122	4464,00	475,19	3962,33	833,10
	август	92	4478	4858,67	457,01	4225,00	770,24
	сентябрь	93	4140	4863,00	405,46	4398,67	667,71
	октябрь	94	3626	4580,00	370,21	4522,00	641,93
	ноябрь	95	3220	4081,33	427,84	4470,00	726,47
	декабрь	96	3019	3662,00	445,83	4262,50	796,87
2010	январь	97	2855	3288,33	398,01	3934,17	773,90
	февраль	98	2882	3031,33	315,70	3556,33	739,45
	март	99	3174	2918,67	187,06	3290,33	757,32
	апрель	100	2717	2970,33	156,97	3129,33	741,31
	май	101	3132	2924,33	169,13	2977,83	664,95
	июнь	102	5229	3007,67	182,51	2963,17	515,54
	июль	103	4407	3692,67	897,92	3331,50	852,22
	август	104	4927	4256,00	894,16	3590,17	869,19
	сентябрь	105	4377	4854,33	892,26	3931,00	945,01
	октябрь	106	4198	4570,33	147,72	4131,50	950,14
	ноябрь	107	3845	4500,67	211,55	4378,33	947,02
	декабрь	108	3818	4140,00	268,33	4497,17	981,31
2011	январь	109	3953,67	3953,67	256,28	4262,00	629,08
	февраль	110		3872,22	193,27	4186,44	541,83
средняя погрешность					244,37		475,13

Рисунок 1

  По результатам расчетов стандартных погрешностей и по результатам графического анализа, представленного на рисунке 1, можно сделать вывод о том, что прогноз, сделанный с помощью скользящего среднего с интервалом 3, является более достоверным по сравнению с прогнозом, сделанным с помощью скользящего среднего с интервалом 6, поскольку в первом случае средняя величина погрешности меньше, чем во втором (244,37< 475,13).

   График в первом  случае более сглажен. Таким  образом, с относительно большей  степенью вероятности можно говорить о том, что в первые два месяца 2011 года стоит ждать понижение потребности в работниках.

2. Прогнозы  с применением функций регрессии

 

  Задание 2.

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

   Для выполнения  данного задания используем метод  построения прогноза с применением  функций регрессии, которые позволяют оценивать взаимосвязь между фактическими данными наблюдений и другими данными (чаще всего временной компонентой). Самым простым способом вычисления прогноза с помощью регрессии является функция ТЕНДЕНЦИЯ.

   Функция ТЕНДЕНЦИЯ  вычисляет прогнозы, основанные на линейной связи между результатом наблюдений и временем, в которое это наблюдение было зафиксировано. Однако взаимосвязь показателей не обязательно носит линейный характер, например, объем продаж новой продукции и прирост населения. В этом случае можно использовать функцию РОСТ с теми же аргументами. Воспользуемся этими способами при выполнении данного анализа. Результаты отразим в таблице 2.

 

Таблица 2

Период		№ периода	Потребность в работниках, заявленная в службы занятости за месяц	Прогноз потребность в работниках, заявленная в службы занятости за месяц с помощью функции ТЕНДЕНЦИЯ	Прогноз потребность в работниках, заявленная в службы занятости за месяц с помощью функции РОСТ
1	2	3	4	5	6
2002	январь	1	970
	февраль	2	921
	март	3	1057
	апрель	4	1203	1069,67	1069,07
	май	5	1700	1246,50	1255,79
	июнь	6	1948	1692,80	1730,50
	июль	7	1775	2037,13	2151,72
	август	8	1729	2098,00	2252,70
	сентябрь	9	1690	2096,07	2259,30
	октябрь	10	1480	2067,42	2223,75
	ноябрь	11	1429	1957,20	2075,39
	декабрь	12	1037	1857,84	1946,55
2003	январь	13	1078	1652,92	1666,62
	февраль	14	1197	1513,15	1502,55
	март	15	1283	1441,00	1430,85
	апрель	16	1323	1408,01	1403,19
	май	17	1342	1391,95	1392,19
	июнь	18	1979	1383,51	1387,88
	июль	19	1984	1518,18	1509,00
	август	20	1853	1629,04	1616,25
	сентябрь	21	1257	1693,97	1686,77
	октябрь	22	1261	1634,08	1622,75
	ноябрь	23	1096	1583,91	1571,08
	декабрь	24	802	1512,30	1491,30
2004	январь	25	1151	1401,85	1353,73
	февраль	26	435	1362,55	1319,56
	март	27	528	1218,37	1111,23
	апрель	28	527	1106,68	984,76
	май	29	517	1008,98	885,88
	июнь	30	574	921,95	805,28
	июль	31	602	853,00	750,86
	август	32	804	795,80	710,31
	сентябрь	33	771	770,50	701,23
	октябрь	34	733	744,28	690,00
	ноябрь	35	590	716,67	676,32
	декабрь	36	490	675,86	648,22
2005	январь	37	582	628,27	611,34
	февраль	38	623	595,50	590,87
	март	39	642	570,42	577,21
	апрель	40	768	549,90	567,01
	май	41	782	544,12	568,15
	июнь	42	851	540,54	570,40
	июль	43	872	544,15	577,28
	август	44	535	549,72	585,18
	сентябрь	45	629	524,49	566,97
	октябрь	46	477	509,85	558,80
	ноябрь	47	384	483,37	538,39
	декабрь	48	489	451,19	510,78
2006	январь	49	606	430,35	496,48
	февраль	50	518	420,80	492,22
	март	51	700	405,12	482,32
	апрель	52	900	405,02	484,70
	май	53	1167	420,53	496,56
	июнь	54	1200	455,38	518,06
	июль	55	1409	490,62	540,24
	август	56	1570	539,00	568,58
	сентябрь	57	1781	596,01	601,21
	октябрь	58	1363	664,48	639,27
	ноябрь	59	1378	700,12	664,94
	декабрь	60	1272	734,76	690,60
2007	январь	61	1240	760,41	711,94
	февраль	62	1363	782,58	731,49
	март	63	1674	811,50	755,08
	апрель	64	2038	858,63	788,33
	май	65	2251	925,99	831,32
	июнь	66	2459	1002,89	879,55
	июль	67	2566	1088,34	932,82
	август	68	3020	1175,74	988,82
	сентябрь	69	3767	1285,36	1055,12
	октябрь	70	4424	1432,71	1136,64
	ноябрь	71	3750	1610,21	1231,16
	декабрь	72	3668	1740,94	1315,97
2008	январь	73	3370	1860,69	1400,32
	февраль	74	3461	1958,28	1478,71
	март	75	3494	2056,08	1559,65
	апрель	76	4484	2150,96	1641,68
	май	77	4926	2293,46	1746,64
	июнь	78	5368	2452,31	1862,04
	июль	79	5405	2626,56	1988,00
	август	80	5556	2794,81	2116,91
	сентябрь	81	5737	2963,08	2250,93
	октябрь	82	5063	3132,82	2390,67
	ноябрь	83	4487	3262,24	2516,97
	декабрь	84	4010	3358,24	2628,60
2009	январь	85	3546	3427,30	2725,36
	февраль	86	3436	3471,45	2804,92
	март	87	3400	3508,47	2878,93
	апрель	88	3938	3542,13	2950,09
	май	89	4465	3598,67	3040,02
	июнь	90	4989	3676,47	3146,41
	июль	91	5122	3774,31	3267,93
	август	92	4478	3874,02	3392,80
	сентябрь	93	4140	3941,71	3496,59
	октябрь	94	3626	3992,10	3587,00
	ноябрь	95	3220	4018,51	3655,21
	декабрь	96	3019	4026,64	3703,22
2010	январь	97	2855	4025,88	3739,65
	февраль	98	2882	4018,17	3766,07
	март	99	3174	4011,63	3792,83
	апрель	100	2717	4016,92	3833,39
	май	101	3132	4003,54	3848,60
	июнь	102	5229	4006,88	3884,80
	июль	103	4407	4092,15	3999,33
	август	104	4927	4142,42	4085,60
	сентябрь	105	4377	4210,82	4188,31
	октябрь	106	4198	4255,80	4270,39
	ноябрь	107	3845	4292,35	4344,07
	декабрь	108	3818	4314,34	4401,71
2011	январь	109	3953,67	4334,43	4456,69
	февраль	110		4358,68	4515,96
ошибка аппроксимации				36,13%	37,87%

 

Результаты прогноза представлены графически на рисунке 2.

Рисунок 2

 

 

   По приведенным  в таблице расчетам можно заметить, что использование функций ТЕНДЕНЦИЯ и РОСТ дает разные результаты. Прогноз, составленный на основе функции ТЕНДЕНЦИЯ, дает нам возможность предположить, что в первые два месяца 2011 года по сравнению с предыдущими годами будет наблюдаться рост потребности в работниках. Аналогичное представление нам даёт составление прогноза на основе функции РОСТ. 
       В результате расчета ошибки аппроксимации можно заметить, что прогноз, выполненный с помощью функции ТЕНДЕНЦИЯ, является более достоверным, по сравнению с использованием функции ПОСТ. Так для функции ТЕНДЕНЦИЯ ошибка аппроксимации составила 36,13 %, а для функции РОСТ 37,87%. 
   Таким образом, можно сказать, что, рассматривая две функции: ТЕНДЕНЦИЯ и РОСТ, в нашем случае, из них наилучшей является функция ТЕНДЕНЦИЯ и, соответственно, она дает нам более достоверные результаты прогноза.

 

3. Прогноз с помощью функции  экспоненциального сглаживания

 

 

Задание 3.

Оцените возможность  использования метода экспоненциального  сглаживания для прогнозирования по выбранному Вами ряду (потребность в работниках) на основе значения автокорреляции. Спрогнозируйте развитие исследуемого процесса на два месяца следующего за отчетным года с помощью экспоненциального сглаживания и оцените результаты графически и аналитически.

        При выполнении данного задания  для построения прогноза воспользуемся  функцией экспоненциального сглаживания.  Сглаживание – это способ, обеспечивающий  быстрое реагирование прогноза  на все события, происходящие  в течении периода протяженности базовой линии.

Метод экспоненциального  сглаживания может давать более  удачные результаты при наличии  высокого уровня автокорреляции во временном  ряду. Значение корреляции более 0,5 означает достаточно высокий уровень автокорреляции во временном ряду.

r_а = КОРРЕЛ (D3:D98;D4:D99) = 0,978

     В результате  расчета автокорреляции было  получено значение 0,978, которое является  больше 0,5, что означает достаточно  высокий уровень автокорреляции. Это означает, что дальше можно  проводить экспоненциальное сглаживание для данного временного ряда.

     При использовании  методов регрессии ко всем  точкам прогноза применяется  одна и та же формула и  тем самым ослабляется реакция  на изменение базовой линии.  Сглаживание представляет собой  простой способ обойти данную проблему. Результаты прогноза представим в таблице 3.