Оценка параметров регрессии осуществляется по методу наименьших квадратов по формуле, используя данные[1], приведенные в таблице 4.3

Пример 4.1.

Задача состоит в построении модели для предсказания объема реализации одного из продуктов фирмы.

Объем реализации – это зависимая переменная Y(млн. руб.) В качестве независимых, объясняющих переменных выбраны: время - X1, расходы на рекламу X 2 (тыс. руб.), цена товара X3 (руб.), средняя цена товара у конкурентов X4 (руб.), индекс потребительских расходов X5 (%).

Требуется:

1. Осуществить выбор факторных признаков для построения двухфакторной регрессионной модели.

2. Рассчитать параметры модели.

3. Для оценки качества всего уравнения регрессии определить:

- линейный коэффициент множественной корреляции,

- коэффициент детерминации,

4. Осуществить оценку значимости уравнения регрессии.

5. Оценить с помощью t - критерия Стьюдента статистическую значимость коэффициентов уравнения множественной регрессии.

6. Оценить влияние факторов на зависимую переменную по модели

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

1 Построение системы показателей (факторов). Анализ матрицы коэффициентов парной корреляции. Выбор факторных признаков для построения двухфакторной регрессионной модели.

Статистические данные по всем переменным приведены в таблице 4.1. В этом примере n = 16, m = 5.

Таблица 4.1

Y	Х1	X2	X3	X4	X5
Объем реализации	Время	Реклама	Цена	Цена конкурента	Индекс потребительских расходов

		4,8	14,8	17,3	98,4
		3,8	15,2	16,8	101,2
		8,7	15,5	16,2	103,5
		8,2	15,5		104,1
		9,7
		14,7	18,1	20,2	107,4
		18,7		15,8	108,5
		19,8	15,8	18,2	108,3
		10,6	16,9	16,8	109,2
		8,6	16,3		110,1
		6,5	16,1	18,3	110,7
		12,6	15,4	16,4	110,3
		6,5	15,7	16,2	111,8
		5,8		17,7	112,3
		5,7	15,1	16,2	112,9

Использование инструмента Корреляция (Анализ данных в EXCEL).

Для проведения корреляционного анализа выполните следующие действия:

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

2. Выберите команду СервисÞАнализ данных.

3. В диалоговом окне Анализ данных выберите инструмент Корреляция, а затем щелкните на кнопке ОК.

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

5. Выберите параметры вывода. В данном примере Новый рабочий лист.

6. ОК.

Таблица 4.2. Результат корреляционного анализа.

	Объем реализации	Время	Реклама	Цена	Цена конкурента	Индекс потребительских расходов
	Столбец 1	Столбец 2	Столбец 3	Столбец 4	Столбец 5	Столбец 6
Объем реализации
Время	0.678
Реклама	0.646
Цена	0.233	0.174	-0.003
Цена конкурента	0.226	-0.051	0.204	0.698
Индекс потребительских расходов	0.816	0.960	0.273	0.235	0.03

Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная, т.е. объем реализации имеет тесную связь с индексом потребительских расходов (ryx₅= 0.816), с расходами на рекламу (ryx₂= 0.646) и со временем (ryx₁ = 0.678). Однако факторы Х₂ и Х₅ тесно связаны между собой (rх ₁x₅ = 0.96), что свидетельствует о наличии мультиколлинеарности. Из этих двух переменных оставим в модели Х₅- индекс потребительских расходов. В этом примере n = 16, m = 5, после исключения незначимых факторов n = 16, k =2.

2. Выбор вида модели и оценка ее параметров

Оценка параметров регрессии осуществляется по методу наименьших квадратов по формуле, используя данные[1], приведенные в таблице 4.3

Таблица 4.3

Y	X0	X1	X2
Объем реализации		Реклама	Индекс потребительских расходов

		4,8	98,4
		3,8	101,2
		8,7	103,5
		8,2	104,1
		9,7
		14,7	107,4
		18,7	108,5
		19,8	108,3
		10,6	109,2
		8,6	110,1
		6,5	110,7
		12,6	110,3
		6,5	111,8
		5,8	112,3
		5,7	112,9

(X^тX) =

(X^тX)^-1=

a = (X^тX)^-1 X ^тY = =

Уравнение регрессии зависимости объема реализации от затрат на рекламу и индекса потребительских расходов можно записать в следующем виде:

y = -1471.314 + 9.568х₁+ 15.754х₂

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

Применение инструмента Регрессия (Анализ данных в EXCEL).

Для проведения регрессионного анализа выполните следующие действия:

1. Выберите команду СервисÞАнализ данных.

2. В диалоговом окне Анализ данных выберите инструмент Регрессия, а затем щелкните на кнопке ОК

3. В диалоговом окне Регрессия в поле Входной интервал Y введите адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал Х введите адреса одного или нескольких диапазонов, которые содержат значения независимых переменных (Рисунок 4.1.).

4. Если выделены и заголовки столбцов, то установить флажок Метки в первой строке.

5. Выберите параметры вывода. В данном примере Новая рабочая книга

6. В поле Остатки поставьте необходимые флажки.

7. ОК.

Рисунок 4.1. Диалоговое окно Регрессия подготовлено к выполнению анализа данных.

Результат регрессионного анализа содержится в таблицах 4.4 –4.7. Рассмотрим содержание этих таблиц.

Таблица 4.4.

Регрессионная статистика
Множественный R	0.927
R-квадрат	0.859
Нормированный R-квадрат	0.837
Стандартная ошибка	41.473
Наблюдения	16.000

Таблица 4.5

Дисперсионный анализ
	df	SS	MS	F
Регрессия		136358.334	68179.167	39.639
Остаток		22360.104	1720.008
Итого		158718.438

Таблица 4.6

	Коэффициенты	Стандартная ошибка	t-статистика
Y-пересечение	-1471.314	259.766	-5.664
Реклама	9.568	2.266	4.223
Индекс потребительских расходов	15.753	2.467	6.386

Таблица 4.7

ВЫВОД ОСТАТКА

Наблюдение	Предсказанное	Остатки
	142,25	-16,25
	124,70	12,30
	159,24	-11,24
	242,35	-51,35
	247,02	26,98
	307,06	62,94
	361,20	70,80
	416,80	28,20
	424,18	-57,18
	350,32	16,68
	345,37	-24,37
	334,72	-27,72
	386,79	-55,79
	352,05	-7,05
	353,23	10,77
	361,73	22,27

Пояснения к таблице 4.4.

Регрессионная статистика
№	Наименование в отчете EXCEL	Принятые наименования	Формула
	Множественный R	Коэффициент множественной корреляции, индекс корреляции
	R-квадрат	Коэффициент детерминации, R²
	Нормированный R-квадрат	Скорректированный R²
	Стандартная ошибка	Стандартная ошибка оценки
	Наблюдения	Количество наблюдений, n	n

Пояснения к таблице 4.5.

	Df – число степеней свободы	SS – сумма квадратов	MS	F – критерий Фишера
Регрессия	k =2		/k
Остаток	n-k-1 = 13
Итого	n-1 = 15

Пояснения к таблице 4.6.

Во втором столбце таблицы 4.6. содержатся коэффициенты уравнения регрессии a₀, a₁, a_2. В третьем столбце содержатся стандартные ошибки коэффициентов уравнения регрессии, а в четвертом - t-статистика, используемая для проверки значимости коэффициентов уравнения регрессии.

y = -1471.314 + 9.568х₁+ 15.754х₂

3.Оценка качества всего уравнения регрессии

В таблице 4.7 приведены вычисленные (предсказанные) по модели значения зависимой переменной Y и значения остаточной компоненты .

Значение коэффициентов детерминации и множественной корреляции можно найти в таблице Регрессионная статистика.

Коэффициент детерминации:

= 1- 22360.104/158718.44 = 136358.3/158718.44 = 0.859

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

Коэффициент множественной корреляции R:

= 0.927.

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

4. Проверку значимости уравнения регрессии произведем на основе вычисления F-критерия Фишера:

Значение F-критерия Фишера можно найти в таблице 4.6 протокола EXCEL.

Табличное значение F-критерия при доверительной вероятности 0,95 при = k =2 и =n – k -1= 16 – 2 - 1=13 составляет 3.81. Табличное значение F-критерия можно найти с помощью функции FРАСПОБР (Рис. 4.3)

Рисунок 4.3. Определение табличного значения F-критерия.

Поскольку F >F , уравнение регрессии следует признать адекватным.

4.Оценить с помощью t - критерия Стъюдента статистическую значимость коэффициентов уравнения множественной регрессии.

Значимость коэффициентов уравнения регрессии a₀, а , а оценим с использованием t-критерия Стьюдента.

Значения t-критерия вычислим по формулам:

t_aj=a_j/S_aj

S_aj₌ ´ ,

где b_jj - диагональный элемент матрицы (Х^ТХ)^-1.

(X^тX)^-1=

b₁₁ =39.2314

b₂₂= 0.00299

b₃₃= 0.00354

t_a0 = -1471.314 /259.766 = -1471.314 / 41.473 =- 5.664

t_a1 = 9.5684/2.2659 = 9.5684 / 41.473 = 4.223

t_a₂= 15.7529/2.4669 = 15.7529/ 41.473 = 6.3858

Расчетные значения t-критерия Стьюдента для коэффициентов уравнения регрессии а , а приведены в четвертом столбце таблицы 4.7 протокола EXCEL. Табличное значение t-критерия Стьюдента можно найти с помощью функции СТЬЮДРАСПОБР (Рис. 4.4)

Рисунок 4.4. Определение табличного значения t-критерия Стьюдента.

Табличное значение t-критерия при 5% уровне значимости и степенях свободы (16-2-1=13) составляет 2,16. Так как |t |>t , то коэффициенты a₁, а и существенны (значимы).

Рисунок 4.2. График остатков.

5. Проанализировать влияние факторов на зависимую переменную по модели (для каждого коэффициента регрессии вычислить коэффициент эластичности, b-коэффициент.

Учитывая, что коэффициент регрессии невозможно использовать для непосредственной оценки влияния факторов на зависимую переменную из-за различия единиц измерения, используем коэффициент эластичности (Э) и бета-коэффициент, которые соответственно рассчитываются по формулам:

9.568´9.294/306.813= 0.2898

15.7529´107.231/306.813= 5.506

9.568´4.913/102.865= 0.457

15.7529´4.5128/102.865= 0.691

Коэффициент эластичности показывает, на сколько процентов изменяется зависимая переменная при изменении фактора на один процент.

Бета-коэффициент с математической точки зрения показывает, на какую часть величины среднего квадратического отклонения меняется среднее значение зависимой переменной с изменением независимой переменной на одно среднеквадратическое отклонение при фиксированном на постоянном уровне значении остальных независимых переменных. Это означает, что при увеличении затрат на рекламу в нашем примере на 4.91 тыс. руб. объем реализации увеличится на 47 тыс. руб. (0.457*102.865).

6. Определить точечные и интервальные прогнозные оценки объема реализации на два квартала вперед (t_0,7= 1,12)

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

Для фактора Х₁ Затраты на рекламу выбрана модель

Х₁ = 12.83-11.616t +4.319t²–0.552t³+0.020t⁴-0.0006t⁵,

по которой получен прогноз на 2 месяца вперед[2]. График модели временного ряда Затраты на рекламу приведен на Рисунке 4.5.

Упреждение	Прогноз
	5.75
	4.85

Рисунок 4.5. Прогноз показателя Затраты на рекламу.

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

Х₂ = 97.008+1.739t – 0.0488t².

Рисунок 4.6. Прогноз показателя Индекс потребительских расходов.

Упреждение	Прогноз
	112.468
	112.488

Для получения прогнозных оценок зависимостей переменной по модели

Y = -1471.438 + 9.568X₁+ 15.754X₂

подставим в нее найденные прогнозные значения факторов X₁ и X₂.

Y_t=17 = -1471.438 + 9.568*5.75 + 15.754*112.468=355.399

Y_t=18 = -1471.438 + 9.568*4.85 + 15.754*112.488=344.179

Доверительный интервал прогноза будет иметь следующие границы:

Верхняя граница прогноза: (n+l)+ U(l),

Нижняя граница прогноза: (n+ l) - U(l).

u(l) = S_e t_кр = S_e t_кр

S= 41.473

t_кр = 1,77 (Значение t_кр получено с помощью функции СТЬЮДРАСПРОБР(0.1;13) для выбранной вероятности 90% с числом степеней свободы равным 13.)

На первый шаг:

l =1

Х_пр^Т = (1; 5.75; 112.468)

(X^тX)^-1=

u(1) = 81,45

На второй шаг:

l=2

Х_пр^Т = (1; 4.85; 112.488)

u(2) = 82б47

Результаты прогнозных оценок модели регрессии представим в табл. 4.8.

Табл. 4.8.

Таблица прогнозов (p = 90%)
Упреждение	Прогноз	Нижняя граница	Верхняя граница
	355,399	273,94	436,85
	344,179	261,71	426,65

[1] Для вычисления а0 добавлен столбец Х0.

[2] Внимание!!! Полиномы таких высоких порядков редко используются при прогнозировании экономических показателей.