Пример 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 |
Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная, т.е. объем реализации имеет тесную связь с индексом потребительских расходов (ryx5= 0.816), с расходами на рекламу (ryx2 = 0.646) и со временем (ryx1 = 0.678). Однако факторы Х2 и Х5 тесно связаны между собой (rх 1x5 = 0.96), что свидетельствует о наличии мультиколлинеарности. Из этих двух переменных оставим в модели Х5 - индекс потребительских расходов. В этом примере 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х1 + 15.754х2
Расчетные значения 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-квадрат | Коэффициент детерминации, R2 | 
| |
| Нормированный R-квадрат | Скорректированный R2 | 
| |
| Стандартная ошибка | Стандартная ошибка оценки | 
| |
| Наблюдения | Количество наблюдений, n | n |
Пояснения к таблице 4.5.
| Df – число степеней свободы | SS – сумма квадратов | MS | F – критерий Фишера | |
| Регрессия | k =2 | 
| /k
| 
|
| Остаток | n-k-1 = 13 | 
| 
| |
| Итого | n-1 = 15 | 
|
Пояснения к таблице 4.6.
Во втором столбце таблицы 4.6. содержатся коэффициенты уравнения регрессии a0, a1, a2. В третьем столбце содержатся стандартные ошибки коэффициентов уравнения регрессии, а в четвертом - t-статистика, используемая для проверки значимости коэффициентов уравнения регрессии.
Уравнение регрессии зависимости объема реализации от затрат на рекламу и индекса потребительских расходов можно записать в следующем виде:
y = -1471.314 + 9.568х1 + 15.754х2
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 - критерия Стъюдента статистическую значимость коэффициентов уравнения множественной регрессии.
Значимость коэффициентов уравнения регрессии a0, а 
, а 
оценим с использованием t-критерия Стьюдента.
Значения t-критерия вычислим по формулам:
taj=aj/Saj
Saj = 
´ 
,
где bjj - диагональный элемент матрицы (ХТ Х)-1.
(Xт X)-1 =
b11 =39.2314
b22 = 0.00299
b33 = 0.00354
ta0 = -1471.314 /259.766 = -1471.314 / 41.473 
=- 5.664
ta1 = 9.5684/2.2659 = 9.5684 / 41.473 
= 4.223
ta2 = 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 , то коэффициенты a1, а 
и существенны (значимы).
.
Рисунок 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. Определить точечные и интервальные прогнозные оценки объема реализации на два квартала вперед (t0,7 = 1,12)
Исходные данные представлены временными рядами, поэтому прогнозные значения 
, 
и 
, 
можно определить с помощью методов экспертных оценок, с помощью средних абсолютных приростов или вычислить на основе экстраполяционных методов.
Для фактора Х1 Затраты на рекламу выбрана модель
Х1 = 12.83-11.616t +4.319t2 –0.552t3+0.020t4-0.0006t5,
по которой получен прогноз на 2 месяца вперед[2]. График модели временного ряда Затраты на рекламу приведен на Рисунке 4.5.
| Упреждение | Прогноз |
| 5.75 | |
| 4.85 |
Рисунок 4.5. Прогноз показателя Затраты на рекламу.
Для временного ряда Индекс потребительских расходов в качестве аппроксимирующей функции выбран полином второй степени (парабола), по которой построен прогноз на 2 шага вперед. На рисунке 4.6. приведен результат построения тренда для временного ряда Индекс потребительских расходов.
Х2 = 97.008+1.739t – 0.0488t2.
Рисунок 4.6. Прогноз показателя Индекс потребительских расходов.
| Упреждение | Прогноз | |
| 112.468 | ||
| 112.488 |
Для получения прогнозных оценок зависимостей переменной по модели
Y = -1471.438 + 9.568X1 + 15.754X2
подставим в нее найденные прогнозные значения факторов X1 и X2.
Yt=17 = -1471.438 + 9.568*5.75 + 15.754*112.468=355.399
Yt=18 = -1471.438 + 9.568*4.85 + 15.754*112.488=344.179
Доверительный интервал прогноза будет иметь следующие границы:
Верхняя граница прогноза: 
(n+l)+ U(l),
Нижняя граница прогноза: (n+ l) - U(l).
u(l) = Se tкр 
= Se 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] Внимание!!! Полиномы таких высоких порядков редко используются при прогнозировании экономических показателей.
