Пример выполнения задания в среде Microsoft Excel

По данным, представленным в таблице 5.3, изучается зависимость балансовой прибыли предприятия торговли Y (тыс. руб.) от следующих факторов:

X1 - объем товарных запасов, тыс. руб.;

X2 - фонд оплаты труда, тыс. руб.;

X3 - издержки обращения, тыс. руб.;

X4 - объем продаж по безналичному расчету, тыс. руб.

Таблица 5.4 - Информация для построения модели регрессии

1. Для заданного набора данных постройте линейную модель множественной регрессии.

2. Оцените точность и адекватность построенного уравнения регрессии. Выделите значимые и незначимые факторы в модели.

3. Постройте уравнение регрессии со статистически значимыми факторами. Дайте экономическую интерпретацию параметров модели. Определите коэффициенты эластичности.

4. Получите прогнозные значения прибыли в зависимости от изменения факторов.

Решение

Для получения отчета по построению модели в среде EXCEL необходимо выполнить следующие действия:

1. В меню Сервис выбираем строку Анализ данных. На экране появится окно, в котором выбираем пункт Регрессия. Появляется диалоговое окно, в котором задаем необходимые параметры. Входной интервал Y - диапазон (столбец), содержащий данные со знамениями объясняемой переменной; Входной интервал X - диапазон (столбцы), содержащий данные со значениями объясняющих переменных. Метки - флажок, который указывает, содержат ли первые элементы отмеченных диапазонов названия переменных (столбцов) или нет; Константа-ноль - флажок, указывающий на наличие или отсутствие свободного члена в уравнении регрессии (); Выходной интервал - достаточно указать левую верхнюю ячейку будущего диапазона, в котором будет сохранен отчет; Новый рабочий лист - можно задать произвольное имя нового листа, в котором будет сохранен отчет.

Если необходимо получить значения и графики остатков (е_i), установите соответствующие флажки в диалоговом окне. Нажмите на кнопку ОК. В отчете о результатах регрессионного анализа («вывод итогов») содержатся три таблицы. Рассмотрим таблицу «Регрессионная статистика» (табл. 5.5).

Таблица 5.5 - Регрессионная статистика

Множественный R - это , где R² - коэффициент детерминации. R-квадрат — это R². В нашем примере значение R² = 0, 8203 свидетельствует о том, что изменения зависимой переменной Y (балансовой прибыли) в основном (на 82, 03%) можно объяснить изменениями включенных в модель объясняющих переменных – X1, Х2, X3, X4. Такое значение свидетельствует об адекватности модели. Нормированный R-квадрат - поправленный (скорректированный по числу степеней свободы) коэффициент детерминации. Стандартная ошибка регрессии S = , где S² = - необъясненная дисперсия (мера разброса зависимой переменной вокруг линии регрессии); п — число наблюдений (в нашем примере равно 24), т - число объясняющих переменных (в нашем примере равно 4). Наблюдения - число наблюдений п. Рассмотрим результаты дисперсионного анализа (табл. 5.6).

Таблица 5.6 - Дисперсионный анализ

df - degrees of freedom - число степеней свободы связано с числом единиц совокупности п и с числом определяемых по ней констант (m+1). SS - sum of squares - сумма квадратов (регрессионная (RSS-regression sum of squares), остаточная (ESS — error sum of squares) и общая (TSS— total sum of squares), соответственно). MS-mean sum - сумма квадратов на одну степень свободы. F - расчетное значение F-критерия Фишера. Если нет табличного значения, то для проверки значимости уравнения регрессии в целом можно посмотреть Значимость F. На уровне значимости уравнение регрессии признается значимым в целом, если Значимость F < 0.05, и незначимым, если Значимость F 0.05.

Для нашего примера имеем следующие значения:

Расчетное значение F -критерия Фишера составляет 21, 68.

Таблица 5.7 - Результаты дисперсионного анализа

	df	SS	MS	F	Значимость F
Регрессия	m = 4	RSS=2, 82E+09	RSS/df=7, 06E+08	=21, 68	7, 28E-07
Остаток	n-m-1=19	ESS=6, 19E+08	ESS/df=3, 26E+07
Итого	n-1 = 23	TSS=3, 44E+09

Значимость F= 7, 28Е-07, что меньше 0, 05. Таким образом, полученное уравнение в целом значимо. Таблица 5.8 содержит коэффициенты регрессии и оценку их значимости.

Таблица 5.8 - Оценка параметров регрессии

Таблица 5.9 - Доверительные интервалы для значений коэффициентов

В таблице приведены значения параметров (коэффициентов) модели, их стандартные ошибки и расчетные значения t-критерия Стьюдента для оценки значимости отдельных параметров модели.

Таблица 5.10 - Оценка коэффициентов регрессии

	Коэффициенты	Стандартная ошибка	t-статистика	Р-Значение	Нижние 95%	Верхние 95%
Y	b0 = 8497, 95	mb0=5214, 99	tb0=8497, 95/5214, 99= 1, 6295	0, 1197	-2417, 16 b0 19413, 06
X1	b1=-0, 0013	mb1=0, 0017	tb1=-0, 0013/0, 0017 = -0, 7703	0, 4506	-0, 0047 b1 0, 0022
X2	b2=0, 8763	mb2=0, 1565	tb2=5, 5992	2, 12E-05	0, 5487 b2 1, 2039
X3	b3=0, 00544	mb3=0, 0971	tb3=0, 0559	0, 9560	-0, 1978 b3 0, 2087
X4	b4=0, 0605	mb4=0, 0263	tb4=2, 2989	0, 0330	0, 0054 b4 0, 1157

Анализ таблицы позволяет сделать вывод о том, что на уровне значимости = 0.05 значимыми оказываются лишь коэффициенты при факторах X2 и Х4, так как только для них Р-значение меньше 0, 05. Таким образом, факторы X1 и Х3 не существенны, и их включение в модель нецелесообразно. Поскольку коэффициент регрессии в эконометрических исследованиях имеют четкую экономическую интерпретацию, то границы доверительного интервала для коэффициента регрессии не должны содержать противоречивых результатов, как, например -0, 0047 b₁ 0, 0022. Такого рода запись указывает, что истинное значение коэффициента регрессии одновременно содержит положительные и отрицательные величины и даже ноль, чего не может быть. Это также подтверждает вывод о статистической незначимости коэффициентов регрессии при факторах X1 и Х5. Исключим несущественные факторы X1 и Х3 и построим уравнение зависимости Y (балансовой прибыли) от объясняющих переменных X2 и Х4. Результаты регрессионного анализа приведены в таблицах

Таблица 5.11 - Регрессионная статистика

Таблица 5.12 - Дисперсионный анализ

Таблица 5.13 - Оценка коэффициентов регрессии

Оценим точность и адекватность полученной модели. Значение R² = 0, 8144 свидетельствует о том, что вариация зависимой переменной Y (балансовой прибыли) по-прежнему в основном (на 81, 44%) можно объяснить вариацией включенных в модель объясняющих переменных – Х₂ и Х₄.Это свидетельствует об адекватности модели. Значение поправленною коэффициента детерминации (0, 7967) возросло по сравнению с первой моделью, в которую были включены все объясняющие переменные (0, 7825). Стандартная ошибка регрессии во втором случае меньше, чем в первом (5515 < 5706). Расчетное значение F-критерия Фишера составляет 46, 08 (для первоначальной модели расчетное значение F-критерия Фишера составляло 21, 68). Значимость F = 2, 08847Е-08, что меньше 0, 05. Таким образом, полученное уравнение в целом значимо.

Далее оценим значимость отдельных параметров построенной модели. Из таблицы видно, что теперь на уровне значимости = 0.05 все включенные в модель факторы являются значимыми: Р-значение < 0, 05.

Границы доверительного интервала для коэффициентов регрессии не содержат противоречивых результатов: с надежностью 0.95 (с вероятностью 95%) коэффициент b₁ лежит в интервале 0.64 b₁ 1, 19; с надежностью 0.95 (с вероятностью 95%) коэффициент b₂ лежит в интервале 0, 01 b₂ 0, 12. Таким образом, модель балансовой прибыли предприятия торговли запишется в следующем виде:

= 5933, 1 + 0, 916*Х₂+ 0, 065*Х_4.

Параметры модели имеют следующую экономическую интерпретацию. Коэффициент b₁ = 0, 916, означает, что при увеличении только фонда оплаты труда (X₂)на 1 тыс. руб. балансовая прибыль в среднем возрастает на 0, 916 тыс. руб., а то, что коэффициент b₂ = 0, 065, означает, что увеличение только объема продаж по безналичному расчету (Х₄) на 1 тыс. руб. приводит в среднем к увеличению балансовой прибыли на 0, 065 тыс. руб. Как было отмечено выше, анализ Р-значений показывает, что оба коэффициента значимы. Средние коэффициенты эластичности рассчитываются по формуле (5.18).

Э_х2=0, 916*22371, 65/29800, 38=0, 688.

Согласно коэффициенту эластичности по второму фактору - рост фонда оплаты труда на 1% приводит к увеличению балансовой прибыли на 0, 688%.

Э_х4=0, 065*52220, 1/29800, 38=0, 114.

Согласно коэффициенту эластичности по четвертому фактору - рост объема продаж по безналичному расчету на 1% приводит к увеличению балансовой прибыли на 0, 114%.

Сделаем предположение, что в периоде упреждения размер фонда оплаты труда и объема продаж по безналичному расчету будут равны максимальным значениям за отчетный период: Х₂=41824, 92; Х₄=200485. Тогда по уравнению регрессии получаем возможное значение = 57276, 25.