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

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

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

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

 

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

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

 

 

 

 

 

 

 

 

 

 

 

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

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

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

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

 

 

 

 

 

 

 

 

 

 

 

Проверил

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

__________ Т.В. Кудряшова

«____»___________2012г.

 

Выполнила

Студентка группы 8431

__________Егарева Т.Н

«____»___________2012г.

 

 

 

 

 

Содержание

 

 

 

Введение

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

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

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

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

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

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

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

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

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

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

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

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

 

 

 

 

 

Задание 1. По ряду динамики оборота розничной торговли N-ой области за 2004-2011 годы  спрогнозируйте развитие изучаемого процесса на два месяца следующего за отчетным года методами скользящего среднего. Одним из способов создания скользящего среднего в Excel является прямое введение формулы СРЗНАЧ и автозаполнение на нужное количество периодов.

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

Таблица 1 –  Прогноз с использованием метода скользящего среднего

Период		№ периода	Оборот розничной торговли за месяц, млн.руб.	Скользящее среднее (интерва=3)	Стандартные погрешности	Скользящая средняя (интервал= 6)	Стандартные погрешности
1	2	3	4	5	6	7	8
2004	январь	1	234,8
	февраль	2	286,1
	март	3	292,7
	апрель	4	331,8	271,20
	май	5	302,1	303,53
	июнь	6	305,7	308,87	20,87
	июль	7	330,2	313,20	17,33	292,20
	август	8	319,8	312,67	11,68	308,10
	сентябрь	9	294,3	318,57	11,03	313,72
	октябрь	10	279,2	314,77	15,58	313,98
	ноябрь	11	230,4	297,77	15,97	305,22
	декабрь	12	309,5	267,97	26,92	293,27	30,89
2005	январь	13	249,1	273,03	32,07	293,90	31,05
	февраль	14	233,9	263,00	31,27	280,38	32,34
	март	15	243,9	264,17	28,51	266,07	34,82
	апрель	16	260,2	242,30	19,25	257,67	34,34
	май	17	262,5	246,00	19,32	254,50	32,74
	июнь	18	308,2	255,53	9,18	259,85	20,35
	июль	19	344,7	276,97	20,21	259,63	27,69
	август	20	357,3	305,13	29,38	275,57	37,42
	сентябрь	21	376,8	336,73	31,43	296,13	43,03
	октябрь	22	338,7	359,60	27,59	318,28	48,89
	ноябрь	23	325,1	357,60	18,94	331,37	48,93
	декабрь	24	365,4	346,87	19,38	341,80	49,39
2006	январь	25	326,9	343,07	21,05	351,33	45,60
	февраль	26	264,7	339,13	19,34	348,37	36,87
	март	27	288,7	319,00	34,63	332,93	38,88
	апрель	28	306,2	293,43	32,25	318,25	32,97
	май	29	320,7	286,53	33,45	312,83	32,94
	июнь	30	365,8	305,20	14,71	312,10	32,42
	июль	31	397,8	330,90	24,80	312,17	38,70

Продолжение  таблицы 1

1	2	3	4	5	6	7	8
	август	32	449,1	361,43	30,45	323,98	48,26
	сентябрь	33	600,2	404,23	38,96	354,72	55,11
	октябрь	34	516,9	482,37	75,76	406,63	95,59
	ноябрь	35	465,5	522,07	72,86	441,75	100,35
	декабрь	36	577,9	527,53	76,94	465,88	100,29
2007	январь	37	458,9	520,10	49,04	501,23	102,75
	февраль	38	427,4	500,77	54,59	511,42	100,55
	март	39	473,3	488,07	54,08	507,80	98,50
	апрель	40	488,6	453,20	44,11	486,65	59,06
	май	41	528,3	463,10	39,73	481,93	50,54
	июнь	42	547,6	496,73	26,15	492,40	52,62
	июль	43	635,5	521,50	27,86	487,35	48,93
	август	44	695,1	570,47	44,37	516,78	65,45
	сентябрь	45	701,3	626,07	56,79	561,40	78,65
	октябрь	46	787,7	677,30	56,48	599,40	88,80
	ноябрь	47	849,9	728,03	54,47	649,25	105,23
	декабрь	48	1015,4	779,63	55,00	702,85	120,26
2008	январь	49	825,8	884,33	92,51	780,82	151,75
	февраль	50	579,9	897,03	95,20	812,53	143,91
	март	51	622,3	807,03	156,89	793,33	159,13
	апрель	52	616,4	676,00	140,89	780,17	166,57
	май	53	678,6	606,20	134,88	751,62	166,13
	июнь	54	723,4	639,10	38,94	723,07	155,96
	июль	55	762,4	672,80	37,53	674,40	124,71
	август	56	889,5	721,47	43,96	663,83	130,93
	сентябрь	57	826,1	791,77	67,79	715,43	120,83
	октябрь	58	787,9	826,00	61,18	749,40	106,90
	ноябрь	59	828,9	834,50	62,51	777,98	91,63
	декабрь	60	1203,8	814,30	28,19	803,03	90,43
2009	январь	61	1125,2	940,20	154,78	883,10	157,86
	февраль	62	815,4	1052,63	158,08	943,57	169,70
	март	63	763,7	1048,13	207,30	931,22	161,20
	апрель	64	761,8	901,43	161,66	920,82	170,64
	май	65	831,8	780,30	156,50	916,47	181,90
	июнь	66	923,5	785,77	84,52	916,95	184,89
	июль	67	991,9	839,03	56,56	870,23	132,35
	август	68	1063,3	915,73	70,84	848,02	124,37
	сентябрь	69	974,7	992,90	77,23	889,33	135,19
	октябрь	70	1022,4	1009,97	63,25	924,50	120,76
	ноябрь	71	1155,9	1020,13	45,48	967,93	105,31
	декабрь	72	1428,3	1051,00	63,91	1021,95	113,46
2010	январь	73	1137,8	1202,20	143,91	1106,08	172,35
	февраль	74	959,1	1240,67	155,68	1130,40	162,06
	март	75	1012,1	1175,07	190,04	1113,03	158,64
	апрель	76	996,7	1036,33	138,82	1119,27	163,28
	май	77	1053,6	989,30	125,54	1114,98	168,82
	июнь	78	1083,0	1020,80	23,93	1097,93	160,74
	июль	79	1170,5	1044,43	29,54	1040,38	93,99
	август	80	1186,1	1102,37	49,01	1045,83	106,84
	сентябрь	81	1126,3	1146,53	50,65	1083,67	96,00
	октябрь	82	1148,5	1160,97	49,70	1102,70	85,99
	ноябрь	83	1138,8	1153,63	30,52	1128,00	71,64
	декабрь	84	1460,1	1137,87	20,24	1142,20	69,33

 

Окончание таблицы 1

1	2	3	4	5	6	7	8
2011	январь	85	1266,7	1249,13	121,84	1205,05	123,88
	февраль	86	1064,0	1288,53	122,45	1221,08	114,46
	март	87	1163,6	1263,60	168,15	1200,73	120,29
	апрель	88	1164,4	1164,77	115,93	1206,95	121,20
	май	89	1248,8	1130,67	116,88	1209,60	122,31
	июнь	90	1324,7	1192,27	38,01	1227,93	122,60
	июль	91	1425,2	1245,97	59,25	1205,37	81,01
	август	92	1407,6	1332,90	77,27	1231,78	111,58
	сентябрь	93	1379,3	1385,83	71,16	1289,05	108,06
	октябрь	94	1359,7	1404,03	56,58	1325,00	108,88
	ноябрь	95	1337,3	1382,20	23,03	1357,55	107,31
	декабрь	96	1525,3	1358,77	22,94	1372,30	107,92
2012	январь	97	1367,97	1407,43	70,38	1405,73	107,96
	февраль	98	1383,96	1425,74	101,71	1392,26	76,01
Средняя погрешность					62,70		97,96

 

          По результатам расчетов стандартных погрешностей и по результатам графического анализа, представленного на рисунке 1, можно сделать вывод о том, что прогноз, сделанный с помощью скользящего среднего с интервалом 3, является более достоверным по сравнению с прогнозом, сделанным с помощью скользящего среднего с интервалом 6 и 9 , поскольку в первом случае средняя величина погрешности меньше, чем во втором  и в третьем (62,78< 98,08<115,17).

Рисунок 1 –  Прогноз с использованием метода скользящего среднего

 

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

2 Прогноз  оборота розничной торговли с  помощью функций регрессии

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

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

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

Таблица 2 –  Прогноз с применением функций регрессии: ТЕНДЕНЦИЯ и РОСТ

Период		№ периода	Оборот розничной торговли за месяц, млн.руб.	Прогноз оборота розничной  торговли с использованием функции  ТЕНДЕНЦИЯ	Прогноз оборота розничной  торговли с использованием функции  РОСТ
1	2	3	4	5	6
2004	январь	1	234,8	103,82	239,94
	февраль	2	286,1	116,98	244,81
	март	3	292,7	130,15	249,78
	апрель	4	331,8	143,32	254,85
	май	5	302,1	156,49	260,02
	июнь	6	305,7	169,66	265,30
	июль	7	330,2	182,83	270,68
	август	8	319,8	195,99	276,17
	сентябрь	9	294,3	209,16	281,78
	октябрь	10	279,2	222,33	287,50
	ноябрь	11	230,4	235,50	293,33
	декабрь	12	309,5	248,67	299,28
2005	январь	13	249,1	261,84	305,36
	февраль	14	233,9	275,00	311,55
	март	15	243,9	288,17	317,88
	апрель	16	260,2	301,34	324,33
	май	17	262,5	314,51	330,91
	июнь	18	308,2	327,68	337,62
	июль	19	344,7	340,85	344,47
	август	20	357,3	354,01	351,47
	сентябрь	21	376,8	367,18	358,60
	октябрь	22	338,7	380,35	365,88
	ноябрь	23	325,1	393,52	373,30