Порядок выполнения лабораторной работы. Расчет коэффициента конкордации проводится по следующим этапам

Расчет коэффициента конкордации проводится по следующим этапам.

1.Формируем таблицу (матрицу) рангов для исходных данных. Данные ранжируются по экспертам (по столбцам). Для этого в ячейку E3 помещается формула =Ранг(B3;B$3:B$9;1) и согласованно копируется во все ячейки диапазона E3:G9.

Таблица 1 – Исходные данные

Вариант 3
№ эксперта

2. В ячейки B10 и B11 вводится число экспертов m =ЧИСЛСТОЛБ(C3:E3) и число объектов n =ЧСТРОК(C3:C9).

3. Формируется столбец вспомогательных результатов для расчета величины S. Для этого суммируется построчно (в нашем случае) ранги для i -го объекта, из суммы вычитается m (n +1)/2 и результат возводится в квадрат. С этой целью в ячейку H3 помещается формула: =(СУММ(E3:G3)-($B$10*($B$11+1))/2)^2. После этого согласованно копируется во все ячейки диапазона H3:H9.

4. Находим значение величины S, просуммировав значения по столбцу, в ячейку H10 вводится формула =СУММ(H3:H9).

5. Помещаем в ячейку H11 формулу = 12*H10/(B10^2*(B11^3-B11)), рассчитывается значение коэффициента конкордации. Рассчитанный коэффициент конкордации (0,777778) требует корректировки, так как в нем не учтено то, что в полученных ранжировках имеются одинаковые значения.

6. Визуально определяем для каждой ранжировки Hj –количество связок и помещаем их в соответствующие ячейки диапазона E12:G12; также определяем h_k – размер связок в k –ойгруппе, поместив значения в диапазон ячеек E13:G14.

7.Вычислим выражение T_j, которое используется для корректировки W в случае наличия связок.

8.Определим средний ранг по формуле =СУММ(E3:G9)/B11, результат помещаем в ячейку G18. Также находится значение величины S сначала по объектам, например, по 1-му ячейка I3 =(СУММ(E3:G3)-$G$18)^2, далее значения суммируются, определяется S в ячейке I10.

Значение коэффициента конкордации с учетом связок рассчитываем в ячейке I11 по формуле = 12*I10/(B10^2*(B11^3-B11)-B10*СУММ(E17:G17)). В результате коэффициент конкордации равен Wкор = 0,776557.

9. Результаты вышеизложенных расчетов приведены в таблице 2.

Таблица 2 – результаты расчета коэффициента конкордации

B	C	D	E	F	G	H	I
Эксперт 1	Эксперт 2	Эксперт 3	Матрица рангов	1 способ	2 способ (коррек.)
							5,897959
							5,897959
							12,7551
							43,18367
							41,32653
							29,46939
							43,18367
					S=		181,7143
					W=	0,777778	0,776557
Количество связок, Hi
Размер связок, Hk
Ti (корректирующая сумма)
					10,57142857

10. Мнения экспертов согласованы, так как по объектам незначительно варьируются и Wкор = 0,7765>0,6.

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

ФГБОУ ВПО «СИБИРСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНОЛОГИЧЕСКИЙ УНИВЕРСИТЕТ»

Факультет: Экономический

Кафедра: Управление качеством и математические методы экономики

Лабораторная работа № 7. Прогнозирование пассажиропотоков. Одномерные временные ряды

Вариант №3

Выполнил:

ст-т гр.86-2

___________ Е.П. Игнатова

подпись, дата

Проверил:

___________ Н.Ю. Юферова

подпись

___________

Оценка, дата

Красноярск 2014 г.

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

Задачи:

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

- расчет значений сезонной компоненты;

- устранение сезонной компоненты и исходных уров­ней ряда и получение выровненных данных в аддитивной или в мультипликативной модели;

- аналитическое выравнивание уровней ряда;

- расчет полученных по модели значений;

- расчет абсолютных и/или относительных ошибок.

Порядок выполнения лабораторной работы:

Необходимо рассчитать тренд и спрогнозировать количество перевезенных пассажиров филиалом ОАО «ФПК» на основе наблюдений в течение 10 лет и сделать выводы по результатам.

Исходные данные за 4 года представлены в таблице 1.1.

Таблица 1.1 – Расчёт оценок сезонной компоненты в аддитивной модели, тыс. чел.

Номер квартала, t	Перевезенные пассажиры, yt	Итого за 4 квартала	Скользящая средняя за 4 квартала	Центрированная скользящая средняя	Оценка сезонной компоненты
1	2	3	4	5	6
	846,88	–	–	–	–
	1 076,22	3847,15	961,7875	–	–
	1 133,13	4 014,51	1003,6275	982,7075	150,42
	790,92	4354,6	1088,65	1046,13875	-255,22
	1 014,24	4765,19	1191,2975	1139,97375	-125,73
	1 416,31	5039,74	1259,935	1225,61625	190,69
	1 543,72	5136,39	1284,0975	1272,01625	271,70
	1 065,47	5158,5	1289,625	1286,86125	-221,39
	1 110,89	5020,63	1255,1575	1272,39125	-161,50
	1 438,42	5016,57	1254,1425	1254,65	183,77
	1 405,85	5080,94	1270,235	1262,18875	143,66
	1 061,41	5128,48	1282,12	1276,1775	-214,77
	1 175,26	5133,21	1283,3025	1282,71125	-107,45
	1 485,96	5442,84	1360,71	1322,00625	163,95
	1 410,58	5456,32	1364,08	1362,395	48,18
	1 371,04	5342,72	1335,68	1349,88	21,16
	1188,74	5172,35	1293,0875	1314,38375	-125,64
	1372,36	4967,77	1241,9425	1267,515	104,85
	1240,21	4862,51	1215,6275	1228,785	11,43
	1166,46	4620,19	1155,0475	1185,3375	-18,88
	1083,48	4734,44	1183,61	1169,32875	-85,85
	1130,04	4552,5	1138,125	1160,8675	-30,83
	1354,46	4192,1	1048,025	1093,075	261,39
	984,52	4270,9	1067,725	1057,875	-73,36
	723,08	4746,05	1186,5125	1127,11875	-404,04
	1208,84	4793,07	1198,2675	1192,39	16,45
	1829,61	5178,85	1294,7125	1246,49	583,12
	1031,54	5140,88	1285,22	1289,96625	-258,43
	1108,86	4459,93	1114,9825	1200,10125	-91,24
	1170,87	4653,2	1163,3	1139,14125	31,73
	1148,66	4742,12	1185,53	1174,415	-25,75
	1224,81	4741,78	1185,445	1185,4875	39,32
	1197,78	4985,17	1246,2925	1215,86875	-18,09
	1170,53	4943,95	1235,9875	1241,14	-70,61
	1392,05	4932,86	1233,215	1234,60125	157,45
	1183,59	5311,9	1327,975	1280,595	-97,01
	1186,69	5699,29	1424,8225	1376,39875	-189,71
	1549,57	5728,72	1432,18	1428,50125	121,07
	1779,44
	1213,02

В таблице показано, что данный временной ряд со­держит сезонные колебания с периодичностью 4. Объемы спроса на пассажирские перевозки в осенне-зимний период времени (I и IV кварталы) ниже, чем весной и летом (II и III кварталы). По гра­фику этого ряда можно установить наличие прибли­зительно равной амплитуды колебаний. Это свидетельствует о соответствии этого ряда аддитивной модели. Рассчитаем ее ком­поненты.

Таблица 1.2 – Основные виды функций, применяющихся для построения трендов

Вид тренда	Функция
Линейный
Логарифмический
Экспоненциальный
Тренд в форме степенной функции
Парабола второго и более высоких порядков

Шаг 1. Проведем выравнивание исходных уровней ряда ме­тодом скользящей средней. Для этого:

1) просуммируем уровни ряда последовательно за каждые четыре квартала со сдвигом на один момент времени и определим условные годовые значения перевезенных пассажиров (гр. 3 таблица 7.2);

2) разделив полученные суммы на 4, найдем скользящие средние (гр. 4 таблица 7.2). Отметим, что полученные таким образом вы­равненные значения уже не содержат сезонной компоненты;

3) приведём эти значения в соответствие с фактическими моментами времени, для чего найдём средние значения из двух последовательно скользящих средних – центрированные скользящие средние (гр. 5 таблица 7.2);

4) найдём оценки сезонной компоненты как разность между фактическими уровнями ряда и централизованными скользящими средними (гр. 6 таблица 7.2).

Шаг 2. На этом этапе используем оценки коэффициента детерминации для расчёта сезонной компоненты S. Для этого найдём средние за каждый квартал (по всем годам) оценки сезонной компоненты .

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

Таблица 1.3 – Расчёт значений сезонной компоненты в аддитивной

Модели

Год/Квартал
	–	–	150,4225	-255,21875
	-125,73375	190,69375	271,70375	-221,39125
	-161,50125	183,77	143,66125	-214,7675
	-107,45125	163,95375	48,185	21,16
	-125,64375	104,845	11,425	-18,8775
	-85,84875	-30,8275	261,385	-73,355
	-404,03875	16,45	583,12	-258,42625
	-91,24125	31,72875	-25,755	39,3225
	-18,08875	-70,61	157,44875	-97,005
	-189,70875	121,06875	–	–
Итого за i -й квартал (за все годы)	-1309,25625	711,0725	1601,59625	-1078,5588
Средняя оценка сезонной компоненты для i -го квартала	-145,472917	79,0080556	177,955139	-119,83986
Скорректированная компонента	-143,385521	81,0954514	180,042535	-117,75247
сумма сезонных компонент	-8,349583333
k	-2,087395833
проверка равенства нулю сез.комп

Шаг 3. Занесём полученные значения сезонной компоненты в таблицу 1.4 для соответствующих кварталов каждого года (гр. 3 таблица 1.4). Элиминируем влияние сезонной компоненты, вычитая её значение из каждого уровня исходного временного ряда. Получим T + E = Y – S (гр. 4 таблица 1.4). Эти значения рассчитываются для каждого момента времени и содержат только тенденцию и случайную величину.

Таблица 1.4 – Расчёт выровненных значений Т и ошибок Е

в аддитивной модели, тыс. чел.

Номер квартала, t	yt	St	T + E = yt – St	T	T+S	E = yt - (T + S)	E2
1	2	3	4	5	6	7	8
	846,88	-143,3855	990,265521	878,880188	735,4947	111,3853327	12406,69
	1 076,22	81,09545	995,124549	1072,46555	1153,561	-77,3410032	5981,631
	1 133,13	180,04	953,087465	1136,99401	1317,037	-183,906541	33821,62
	790,92	-117,7525	908,672465	1169,25823	1051,506	-260,585768	67904,94
	1 014,24	-143,3855	1157,62552	1188,61677	1045,231	-30,9912492	960,4575
	1 416,31	81,09545	1335,21455	1201,52246	1282,618	133,6920877	17873,57
	1 543,72	180,0425	1363,67747	1210,74081	1390,783	152,9366537	23389,62
	1 065,47	-117,7525	1183,22247	1217,65457	1099,902	-34,4321093	1185,57
	1 110,89	-143,3855	1254,27552	1223,03195	1079,646	31,24357503	976,161
	1 438,42	81,09545	1357,32455	1227,33384	1308,429	129,9907058	16897,58
	1 405,85	180,0425	1225,80747	1230,85358	1410,896	-5,04611138	25,46324
	1 061,41	-117,7525	1179,16247	1233,78669	1116,034	-54,624223	2983,806
	1 175,26	-143,3855	1318,64552	1236,26855	1092,883	82,37696897	6785,965
	1 485,96	81,09545	1404,86455	1238,39586	1319,491	166,4686851	27711,82
	1 410,58	180,0425	1230,53747	1240,23953	1420,282	-9,70206841	94,13013
	1 371,04	-117,7525	1488,79247	1241,85275	1124,1	246,9397202	60979,23
	1188,74	-143,3855	1332,12552	1243,27617	1099,891	88,84935399	7894,208
	1372,36	81,09545	1291,26455	1244,54143	1325,637	46,72311796	2183,05
	1240,21	180,0425	1060,16747	1245,67351	1425,716	-185,506044	34412,49
	1166,46	-117,7525	1284,21247	1246,69238	1128,94	37,52008614	1407,757
	1083,48	-143,3855	1226,86552	1247,61421	1104,229	-20,7486934	430,5083
	1130,04	81,09545	1048,94455	1248,45225	1329,548	-199,507697	39803,32
	1354,46	180,0425	1174,41747	1249,21741	1429,26	-74,7999403	5595,031
	984,52	-117,7525	1102,27247	1249,9188	1132,166	-147,646337	21799,44
	723,08	-143,3855	866,465521	1250,56409	1107,179	-384,098566	147531,7
	1208,84	81,09545	1127,74455	1251,15973	1332,255	-123,415185	15231,31
	1829,61	180,0425	1649,56747	1251,71126	1431,754	397,8562063	158289,6
	1031,54	-117,7525	1149,29247	1252,22339	1134,471	-102,930924	10594,78
	1108,86	-143,3855	1252,24552	1252,7002	1109,315	-0,45467994	0,206734
	1170,87	81,09545	1089,77455	1253,14522	1334,241	-163,370676	26689,98
	1148,66	180,0425	968,617465	1253,56154	1433,604	-284,944072	81193,12
	1224,81	-117,7525	1342,56247	1253,95183	1136,199	88,610635	7851,845
	1197,78	-143,3855	1341,16552	1254,31847	1110,933	86,84705161	7542,41
	1170,53	81,09545	1089,43455	1254,66354	1335,759	-165,228993	27300,62
	1392,05	180,0425	1212,00747	1254,98889	1435,031	-42,9814294	1847,403
	1183,59	-117,7525	1301,34247	1255,29617	1137,544	46,0462922	2120,261
	1186,69	-143,3855	1330,07552	1255,58684	1112,201	74,48867904	5548,563
	1549,57	81,09545	1468,47455	1255,86221	1336,958	212,6123365	45204,01
	1779,44	180,0425	1599,39747	1256,12346	1436,166	343,2740043
	1213,02	-117,7525	1330,77247	1256,37165	1138,619	74,40081795	5535,482

Шаг 4. Определим компоненту Т данной модели. Для этого проведем аналитическое выравнивание ряда (Т + Е) с помощью линейного тренда.

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

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

Для этого необходимо в среде MS Excel рассчитать уравнение линейного тренда для всех его форм. Параметры каждого из перечисленных выше трендов можно определить обычным методом наименьших квадратов, используя в качестве независимой переменной время t = 1, 2,..., n, а в качестве зависимой перемен­ной – выровненный тренд (Т + Е). Для нелинейных трендов предварительно проводят стандартную процедуру их линеаризации.

Чтобы построить линейный тренд, необходимо воспользоваться специальной функцией MS Excel «ЛИНЕЙН». Выделяем ячейку В20, заходим в «Мастер функций» и выбираем «ЛИНЕЙН». В появившемся диалоговом окне напротив строки «Известные значения у» записываем соответствующие значения Т + Е, напротив строки «известные значения х» – номер квартала. Чтобы программа вывела дополнительную статистику по тренду, напротив строки «Статистика» необходимо набрать – ИСТИНА, затем нажать ОК. Далее выделить на листе MS Excel диапазон В20:С24, нажать клавишу F2, затем одновременно нажать клавиши <CTRL>+<SHIFT>+<ENTER>.

Таким же образом рассчитать значения для всех форм тренда. Исключение составляет функция экспоненциального тренда. Для определения соответствующего ей коэффициента детерминации необходимо воспользоваться функцией «ЛГРФПРИБЛ». В остальных случаях действовать аналогично для функции «ЛИНЕЙН».

Результаты расчетов приведены в таблице 1.4

Таблица 1.4 – Рассчитанные формы тренда с дополнительной статистикой

Линейный	Экспоненциальный	Степенной	Логарифмический	Параболический
	990,2655208		990,2655208		990,2655208		990,2655208			990,2655208
	995,1245486		995,1245486	0,5	995,1245486	0,301029996	995,1245486			995,1245486
	953,0874653		953,0874653	0,333333333	953,0874653	0,477121255	953,0874653			953,0874653
	908,6724653		908,6724653	0,25	908,6724653	0,602059991	908,6724653			908,6724653
	1157,625521		1157,625521	0,2	1157,625521	0,698970004	1157,625521			1157,625521
	1335,214549		1335,214549	0,166666667	1335,214549	0,77815125	1335,214549			1335,214549
	1363,677465		1363,677465	0,142857143	1363,677465	0,84509804	1363,677465			1363,677465
	1183,222465		1183,222465	0,125	1183,222465	0,903089987	1183,222465			1183,222465
	1254,275521		1254,275521	0,111111111	1254,275521	0,954242509	1254,275521			1254,275521
	1357,324549		1357,324549	0,1	1357,324549		1357,324549			1357,324549
	1225,807465		1225,807465	0,090909091	1225,807465	1,041392685	1225,807465			1225,807465
	1179,162465		1179,162465	0,083333333	1179,162465	1,079181246	1179,162465			1179,162465
	1318,645521		1318,645521	0,076923077	1318,645521	1,113943352	1318,645521			1318,645521
	1404,864549		1404,864549	0,071428571	1404,864549	1,146128036	1404,864549			1404,864549
	1230,537465		1230,537465	0,066666667	1230,537465	1,176091259	1230,537465			1230,537465
	1488,792465		1488,792465	0,0625	1488,792465	1,204119983	1488,792465			1488,792465
	1332,125521		1332,125521	0,058823529	1332,125521	1,230448921	1332,125521			1332,125521
	1291,264549		1291,264549	0,055555556	1291,264549	1,255272505	1291,264549			1291,264549
	1060,167465		1060,167465	0,052631579	1060,167465	1,278753601	1060,167465			1060,167465
	1284,212465		1284,212465	0,05	1284,212465	1,301029996	1284,212465			1284,212465
	1226,865521		1226,865521	0,047619048	1226,865521	1,322219295	1226,865521			1226,865521
	1048,944549		1048,944549	0,045454545	1048,944549	1,342422681	1048,944549			1048,944549
	1174,417465		1174,417465	0,043478261	1174,417465	1,361727836	1174,417465			1174,417465
	1102,272465		1102,272465	0,041666667	1102,272465	1,380211242	1102,272465			1102,272465
	866,4655208		866,4655208	0,04	866,4655208	1,397940009	866,4655208			866,4655208
	1127,744549		1127,744549	0,038461538	1127,744549	1,414973348	1127,744549			1127,744549
	1649,567465		1649,567465	0,037037037	1649,567465	1,431363764	1649,567465			1649,567465
	1149,292465		1149,292465	0,035714286	1149,292465	1,447158031	1149,292465			1149,292465
	1252,245521		1252,245521	0,034482759	1252,245521	1,462397998	1252,245521			1252,245521
	1089,774549		1089,774549	0,033333333	1089,774549	1,477121255	1089,774549			1089,774549
	968,6174653		968,6174653	0,032258065	968,6174653	1,491361694	968,6174653			968,6174653
	1342,562465		1342,562465	0,03125	1342,562465	1,505149978	1342,562465			1342,562465
	1341,165521		1341,165521	0,03030303	1341,165521	1,51851394	1341,165521			1341,165521
	1089,434549		1089,434549	0,029411765	1089,434549	1,531478917	1089,434549			1089,434549
	1212,007465		1212,007465	0,028571429	1212,007465	1,544068044	1212,007465			1212,007465
	1301,342465		1301,342465	0,027777778	1301,342465	1,556302501	1301,342465			1301,342465
	1330,075521		1330,075521	0,027027027	1330,075521	1,568201724	1330,075521			1330,075521
	1468,474549		1468,474549	0,026315789	1468,474549	1,579783597	1468,474549			1468,474549
	1599,397465		1599,397465	0,025641026	1599,397465	1,591064607	1599,397465			1599,397465
	1330,772465		1330,772465	0,025	1330,772465	1,602059991	1330,772465			1330,772465
4,611580441	1130,100351	1,003783819	1121,654615	-387,1707273	1266,050916	174,220229	1015,958306	5,754235898	-0,027869645	1122,101763
2,346024	55,19392299	0,001947902	0,045827474	154,4463149	31,08406159	70,43371383	88,39311409	9,742661827	0,230444714	86,6120872
0,092298401	171,2758199	0,09001906	0,142210188	0,141906123	166,5297793	0,138680849	166,8424493	0,092657075	173,5406301	#Н/Д
3,863978274		3,759116493		6,284198977		6,118373476		1,889204001		#Н/Д
113351,3733	1114745,446	0,076023386	0,768502028	174274,458	1053822,362	170313,5092	1057783,31	113791,8589	1114304,961	#Н/Д

В нашем случае скорректированный коэффициент детерминации принимает максимальное значение по степенному уравнению тренда. Таким образом, уравнение тренда имеет следующий вид:

=$F$42+$E$42*E2

Подставив в это уравнение значения t = 1, …, 40, найдём уровни Т для каждого момента времени (гр. 5 таблица 7.4). График уравнения тренда приведён на рисунке 1

Рисунок 1 – Количество перевезенных пассажиров (фактические, выравненные и полученные по аддитивной модели)

Шаг 5. Найдём значения уровня ряда, полученные по аддитивной модели. Для этого прибавим к уровням Т значения сезонной компоненты для соответствующих кварталов. Графически значения (Т + S) представлены на рисунке 1.

Шаг 6. В соответствии с методикой построения аддитивной модели расчёт ошибки производится по формуле E = Y – (T + S). Это абсолютная ошибка.

Для оценки качества построенной модели применим сумму квадратов полученных абсолютных ошибок:

,

(7.3)

где – среднее арифметическое количества перевезенных пассажиров.

В нашем случае коэффициент равен 0,075. Это означает, что аддитивная модель на 7,5 % объясняет общую вариацию количества перевезенных пассажиров по кварталам за 10 лет.

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

Прогнозное значение F_t уровня временного ряда в аддитивной модели в соответствии с соотношением Y = T + S + E есть сумма трендовой и сезонной компонент: F_t = T_t + S_i.

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

Таким образом, прогнозные значения перевезённых пассажиров будут иметь следующий вид:

Т41 = 1534,6

Т42 = 1535,0

Т43 = 1535,3

Т44 = 1535,7

Таким образом:

F41 = T41+S1 = 1719,5

F42 = T42+S2 = 1510,1

F43 = T43+S3 = 1229,8

F44 = T44+S4 = 1681,2

F = 1399,9932+1175,7371+1077,0044+1375,004 = 6140,6

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