Решение с помощью Excel

Задание 3.2.2.1. Госпожа Арешникова В.В., президент компании «Преслава», собрала данные о месячных объемах продаж своей компании (, тыс. руб.) вместе с несколькими другими показателями, как она полагала, способными оказывать влияние на объем продаж. В качестве этих показателей ею были выбраны расходы на рекламу (, тыс. руб.) и индекс потребительских расходов (, %). Собранные госпожой Алешниковой В.В. данные представлены в табл. 3.2.2.1. Требуется оценить степень взаимосвязи между этими показателями, построив соответствующее линейное уравнение регрессии. Для построенного уравнения следует проверить гипотезу о наличии автокорреляции в остатках. В случае подтверждения этой гипотезы необходимо оценить параметры регрессии обобщенным МНК и получить прогнозную оценку объема продаж на следующий месяц при условии того, что расходы на рекламу составят 7,9 тыс. руб., а индекс потребительских расходов возрастет до 114,9 %.

Т а б л и ц а 3.2.2.1


4,0	97,9	14,6	109,2
5,8	98,4	10,2	110,1
4,6	101,2	8,5	110,7
6,7	103,5	6,2	110,3
8,7	104,1	8,4	111,8
8,2		8,1	112,3
9,7	107,4	6,9	112,9
12,7	108,5	7,5	113,1
13,5	108,3	7,7	113,4

Решение с помощью Excel

1. Ввод исходных данных с включением в модель дополнительной переменной , принимающей единственное значение, равное 1.

2. Нахождение вектора оценок коэффициентов регрессии с использованием матричных функций Excel (МУМНОЖ, ТРАНСП, МОБР)

3. Расчет остатков .

4. Вычисление разностей и оформление промежуточных результатов в виде табл. 3.2.2.2.

Т а б л и ц а 3.2.2.2


-1-	-2-	-3-	-4-	-5-	-6-	-7-
				97,9	234,74	17,26
			5,8	98,4	286,43	-12,43

О к о н ч а н и е т а б л. 3.2.2.2

-1-	-2-	-3-	-4-	-5-	-6-	-7-
			4,6	101,2	346,93	-50,93
			6,7	103,5	459,27	-77,27
			8,7	104,1	518,06	29,94
			8,2		595,80	144,20
			9,7	107,4	638,37	125,63
			12,7	108,5	732,62	57,38
			13,5	108,3	742,80	-8,80
			14,6	109,2	792,40	-58,40
			10,2	110,1	730,35	-88,35
			8,5	110,7	714,02	-100,02
			6,2	110,3	655,21	6,79
			8,4	111,8	745,33	-55,33
			8,1	112,3	754,39	-26,39
			6,9	112,9	748,21	19,79
			7,5	113,1	766,46	24,54
			7,7	113,4	779,61	52,39

5. Проверка гипотезы о наличии автокорреляции в остатках

5.1. Проверка гипотезы о наличии автокорреляции в остатках с использованием критерия Дарбина – Уотсона.

5.1.1. Вычисление и . Оформление результатов расчетов в виде табл. 3.2.2.3.

Т а б л и ц а 3.2.2.3


298,00			3411,10	2460,98
154,61	881,91		7806,06	896,84
2593,94	1481,98		10004,50	136,20
5970,72	693,78		46,17	11409,94
896,49	11494,40		3061,38	3859,45
20794,50	13055,69		696,56	837,36
15782,21	345,06		391,48	2132,44
3292,91	4657,16		602,38	22,63
77,38	4379,83		2744,81	775,49
Сумма	78625,21	59521,14

5.1.2. Расчет статистики Дарбина – Уотсона

=59521,14 / 78625,21= 0,757.

Так как , т.е. , то существует положительная автокорреляция остатков.

5.2. Проверка гипотезы о наличии автокорреляции в остатках с использованием метода рядов.

Последовательное определение знаков отклонений позволяет получить следующие ряды:

(+) (– – –) (+ + + +) (– – – –) (+) (– –) (+ + +)

и сделать вывод о присутствии автокорреляции в остатках.

5.3. Проверка гипотезы о наличии автокорреляции в остатках с использованием графического представления зависимости остатков от времени (рис. 3.2.2.1).

Р и с. 3.2.2.1. График зависимости остатков от времени

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

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

6. Преобразование исходных данных.

6.1. Оценка параметра .

6.1.1. Вычисление и оформление результатов расчетов в виде табл. 3.2.2.4.

Т а б л и ц а 3.2.2.4


17,26	298,00		-58,40	3411,10	513,75
-12,43	154,61	-214,65	-88,35	7806,06	5160,16
-50,93	2593,94	633,29	-100,02	10004,50	8837,18
-77,27	5970,72	3935,44	6,79	46,17	-679,63
29,94	896,49	-2313,59	-55,33	3061,38	-375,95
144,20	20794,50	4317,65	-26,39	696,56	1460,29
125,63	15782,21	18115,83	19,79	391,48	-522,20
57,38	3292,91	7208,98	24,54	602,38	485,61
-8,80	77,38	-504,77	52,39	2744,81	1285,85
Сумма	78625,21	47343,24

6.1.2. Вычисление коэффициента автокорреляции

= 47343,24 / 78625,21 = 0,6021.

6.2. Преобразование исходных данных по формулам (3.2.1.3) и оформление результатов расчетов в виде табл. 3.2.2.5.

Т а б л и ц а 3.2.2.5


201,19	0,80	3,19	78,16	292,03	0,40	6,47	43,99
122,26	0,40	3,39	39,45	200,03	0,40	1,41	44,35
131,01	0,40	1,11	41,95	227,43	0,40	2,36	44,40
203,77	0,40	3,93	42,56	292,29	0,40	1,08	43,64
317,98	0,40	4,67	41,78	291,38	0,40	4,67	45,38
410,03	0,40	2,96	44,32	312,52	0,40	3,04	44,98
318,42	0,40	4,76	42,97	329,64	0,40	2,02	45,28
329,97	0,40	6,86	43,83	328,56	0,40	3,35	45,12
258,31	0,40	5,85	42,97	355,71	0,40	3,18	45,30