Решение с помощью 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

 

7. Оценка с помощью обычного МНК вектора коэффициентов регрессии с использованием матричных функций Excel

.

8. Нахождение прогнозной оценки объема продаж на следующий период

при и с учетом того, что коррелированно с предыдущим значением в выборочном периоде

815, 79+0, 60 (832 – 796, 96) = 836, 89.