Оценка результатов линейной регрессии. Вычисление линейной регрессии

Оцените качество построенной модели. Улучшилось ли качество модели по сравнению с однофакторной моделью? Дайте оценку влияния значимых факторов на результат с помощью коэффициентов эластичности, - и -коэффициентов.

Для оценки качества выбранной множественной модели (6) , аналогично п.1.4 данной задачи, используем коэффициент детерминации R - квадрат, среднюю относительную ошибку аппроксимации и F -критерий Фишера.

Коэффициент детерминации R -квадрат возьмем из итогов «Регрессии» (таблица «Регрессионная статистика» для модели (6)).

Следовательно, вариация (изменение) цены квартиры Y на 76,77% объясняется по данному уравнению вариацией города области Х 1 , числа комнат в квартире Х 2 и жилой площади Х 4 .

Используем исходные данные Y i и найденные инструментом «Регрессия» остатки (таблица «Вывод остатка» для модели (6)). Рассчитаем относительные погрешности и найдем среднее значение
.

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

Наблюдение	Предсказанное Y	Остатки	Отн. погрешность
1	45,95089273	-7,95089273	20,92340192
2	86,10296493	-23,90296493	38,42920407
3	94,84442678	30,15557322	24,12445858
4	84,17648426	-23,07648426	37,76838667
5	40,2537216	26,7462784	39,91981851
6	68,70572376	24,29427624	26,12287768
7	143,7464899	-25,7464899	21,81905923
8	106,0907598	25,90924022	19,62821228
9	135,357993	-42,85799303	46,33296544
10	114,4792566	-9,47925665	9,027863476
11	41,48765602	0,512343975	1,219866607
12	103,2329236	21,76707636	17,41366109
13	130,3567798	39,64322022	23,3195413
14	35,41901876	2,580981242	6,7920559
15	155,4129693	-24,91296925	19,0903979
16	84,32108188	0,678918123	0,798727204
17	98,0552279	-0,055227902	0,056355002
18	144,2104618	-16,21046182	12,66442329
19	122,8677535	-37,86775351	44,55029825
20	100,0221225	59,97787748	37,48617343
21	53,27196558	6,728034423	11,21339071
22	35,06605378	5,933946225	14,47303957
23	114,4792566	-24,47925665	27,19917406
24	113,1343153	-30,13431529	36,30640396
25	40,43190991	4,568090093	10,15131132
26	39,34427892	-0,344278918	0,882766457
27	144,4794501	-57,57945009	66,25943623
28	56,4827667	-16,4827667	41,20691675
29	95,38240332	-15,38240332	19,22800415
30	228,6988826	-1,698882564	0,748406416
31	222,8067278	12,19327221	5,188626473
32	38,81483144	1,185168555	2,962921389
33	48,36325811	18,63674189	27,81603267
34	126,6080021	-3,608002113	2,933335051
35	84,85052935	15,14947065	15,14947065
36	116,7991162	-11,79911625	11,23725357
37	84,17648426	-13,87648426	19,73895342
38	113,9412801	-31,94128011	38,95278062
39	215,494184	64,50581599	23,03779142
40	141,7795953	58,22040472	29,11020236
Среднее	101,2375		22,51770962

По столбцу относительных погрешностей найдем среднее значение =22.51% (с помощью функции СРЗНАЧ).

Сравнение показывает, что 22.51%>7%. Следовательно, точность модели неудовлетворительная.

С помощью F – критерия Фишера проверим значимость модели в целом. Для этого выпишем из итогов применения инструмента «Регрессия» (таблица «дисперсионный анализ» для модели (6)) F = 39,6702.

С помощью функции FРАСПОБР найдем значение F кр =3.252 для уровня значимости α = 5% , и чисел степеней свободы k 1 = 2 , k 2 = 37 .

F > F кр , следовательно, уравнение модели (6) является значимым, его использование целесообразно, зависимая переменная Y достаточно хорошо описывается включенными в модель (6) факторными переменными Х 1 , Х 2 . и Х 4 .

Дополнительно с помощью t –критерия Стьюдента проверим значимость отдельных коэффициентов модели.

t –статистики для коэффициентов уравнения регрессии приведены в итогах инструмента «Регрессия». Получены следующие значения для выбранной модели (6) :

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение	-5,643572321	12,07285417	-0,46745966	0,642988	-30,1285	18,84131	-30,1285	18,84131
X4	2,591405557	0,461440597	5,61590284	2,27E-06	1,655561	3,52725	1,655561	3,52725
X1	6,85963077	9,185748512	0,74676884	0,460053	-11,7699	25,48919	-11,7699	25,48919
X2	-1,985156991	7,795346067	-0,25465925	0,800435	-17,7949	13,82454	-17,7949	13,82454

Критическое значение t кр найдено для уровня значимости α=5% и числа степеней свободы k =40–2–1=37 . t кр =2.026 (функция СТЬЮДРАСПОБР).

Для свободного коэффициента α =–5.643 определена статистика
, t кр , следовательно, свободный коэффициент не является значимым, его можно исключить из модели.

Для коэффициента регрессии β 1 =6.859 определена статистика
, β 1 не является значимым, его и фактор города области можно удалить из модели.

Для коэффициента регрессии β 2 =-1,985 определена статистика
, t кр , следовательно, коэффициент регрессии β 2 не является значимым, его и фактор числа комнат в квартире можно исключить из модели.

Для коэффициента регрессии β 4 =2.591 определена статистика
, >t кр, следовательно, коэффициент регрессии β 4 является значимым, его и фактор жилой площади квартиры можно сохранить в модели.

Выводы о значимости коэффициентов модели сделаны на уровне значимости α=5% . Рассматривая столбец «P-значение», отметим, что свободный коэффициент α можно считать значимым на уровне 0.64 = 64%; коэффициент регрессии β 1 – на уровне 0,46 = 46%; коэффициент регрессии β 2 – на уровне 0,8 = 80%; а коэффициент регрессии β 4 – на уровне 2,27E-06= 2,26691790951854E-06 = 0,0000002%.

При добавлении в уравнение новых факторных переменных автоматически увеличивается коэффициент детерминации R 2 и уменьшается средняя ошибка аппроксимации, хотя при этом не всегда улучшается качество модели. Поэтому для сравнения качества модели (3) и выбранной множественной модели (6) используем нормированные коэффициенты детерминации.

Таким образом, при добавлении в уравнение регрессии фактора «город области» Х 1 и фактора «число комнат в квартире» Х 2 качество модели ухудшилось, что говорит в пользу удаления факторов Х 1 и Х 2 из модели.

Проведем дальнейшие расчеты.

Средние коэффициенты эластичности в случае линейной модели определяются формулами
.

С помощью функции СРЗНАЧ найдем: S Y , при увеличении только фактора Х 4 на одно его стандартное отклонение – увеличивается на 0,914 S Y

Дельта-коэффициенты определяются формулами
.

Найдем коэффициенты парной корреляции с использованием инструмента «Корреляция» пакета «Анализ данных» в Excel.

	Y	X1	X2	X4
Y	1
X1	-0,01126	1
X2	0,751061	-0,0341	1
X4	0,874012	-0,0798	0,868524	1

Коэффициент детерминации был определен ранее и равен 0.7677.

Вычислим дельта-коэффициенты:

;

Поскольку Δ 1 1 и Х 2 выбрана неудачно, и их нужно удалить из модели. Значит, по уравнению полученной линейной трехфакторной модели изменение результирующего фактора Y (цены квартиры) на 104% объясняется воздействием фактора Х 4 (жилой площадью квартиры), на 4% воздействием фактора Х 2 (число комнат), на 0,0859% воздействием фактора Х 1 (город области).

Коэффициент обладает следующими свойствами:

1) не имеет размерности, следовательно, сопоставим для величин различных порядков;

2) изменяется в диапазоне от –1 до +1. Положительное значение свидетельствует о прямой линейной связи, отрицательное – об обратной. Чем ближе абсолютное значение коэффициента к единице, тем теснее связь. Считается, что связь достаточно сильная, если коэффициент по абсолютной величине превышает 0,7, и слабая, если он менее 0,3.

Значение коэффициента легко вычисляется при помощи MS Excel (функция КОРРЕЛ).

Величина r 2 называется коэффициентом детерминации . Он определяет долю вариации одной из переменных, которая объясняется вариацией другой переменной.

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

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

Теснота линейной взаимосвязи между переменной y и рядом переменных x j , рассматриваемых в целом, может быть определена с помощью коэффициента множественной корреляции .

Предположим, что переменная y испытывает влияние двух переменных - x и z . В этом случае коэффициент множественной корреляции может быть определен по формуле:

(6.9)

где r yx , r yz , r xz - простые коэффициенты линейной парной корреляции, определенные из соотношения (6.4).

Коэффициент множественной корреляции заключен в пределах 0 ≤ R ≤ 1. Он не меньше, чем абсолютная величина любого парного или частного коэффициента корреляции с таким же первичным индексом.

С помощью множественного коэффициента (по мере приближения R к 1) делается вывод о тесноте взаимосвязи, но не о ее направлении. Величина R 2 , называемая множественным коэффициентом детерминации , показывает, какую долю вариации исследуемой переменной (y ) объясняет вариация остальных учтенных переменных (x , z ).

7. Коэффициент частной корреляции

Иногда представляет интерес измерение частных зависимостей (между y и x j ) при условии, что воздействие других факторов, принимаемых во внимание, устранено. В качестве соответствующих измерителей приняты коэффициенты частной корреляции .

Рассмотрим порядок расчета коэффициента частной корреляции для случая, когда во взаимосвязи находятся три случайные переменные – x , y , z . Для них могут быть получены простые коэффициенты линейной парной корреляции – r yx , r yz , r xz . Однако большая величина этого коэффициента может быть обусловлена не только тем, что y и x действительно связаны между собой, но и в силу того, что обе переменные испытывают сильное действие третьего фактора – z .

Коэффициент частной корреляции отличается от простого коэффициента линейной парной корреляции тем, что он измеряет парную корреляцию соответствующих признаков (y и x ) при условии, что влияние на них третьего фактора (z ) устранено.

Соответствующая расчетная формула:

(6.10)

Частный коэффициент корреляции, так же как и парный коэффициент корреляции r (рассчитанный по формуле (6.4)), может принимать значения от -1 до 1.

Попробуем для начала найти ответ на каждый из обозначенных нами вопросов в ситуации, когда наша каузальная модель содержит всего две независимые переменные.

Множественная корреляция R и коэффициент детерминация R2

Для оценки совокупной связи всех независимых переменных с зависимой переменной используется множественный коэффициент корреляции R. Отличие коэффициента множественной корреляции R от бивариативного коэффициента корреляции г заключается в том, что он может быть лишь положительным. Для двух независимых переменных он может быть оценен следующим образом:

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

(9.2)

Если наши независимые переменные будут трансформированы в единицы стандартного нормального распределения, или Z-распределения, уравнение (9.2), очевидно, примет следующий вид:

(9.3)

В уравнении (9.3) коэффициент β обозначает стандартизированное значение коэффициента регрессии В.

Сами стандартизированные коэффициенты регрессии могут быть вычислены по следующим формулам:

Теперь формула для вычисления коэффициента множественной корреляции будет выглядеть так:

Еще одним способом оценки коэффициента корреляции R является вычисление бивариативного коэффициента корреляции r между значениями зависимой переменной У и соответствующими им значениями , вычисленными на основании уравнения линейной регрессии (9.2). Иными словами, величина R может быть оценена следующим образом:

Наряду с этим коэффициентом мы можем оценить, как и в случае простой регрессии, величину R 2, которую принято еще обозначать как коэффициент детерминации. Так же как и в ситуации оценки связи между двумя переменными, коэффициент детерминации R 2 показывает, какой процент дисперсии зависимой переменной Y , т.е. , оказывается связанным с дисперсией всех независимых переменных – . Иными словами, оценка коэффициента детерминации может быть осуществлена следующем образом:

Также мы можем оценить процент остаточной дисперсии зависимой переменной, нс связанный ни с одной из независимых переменных 1 – R 2. Квадратный корень от этой величины, т.е. величина , так же, как и в случае бивариативной корреляции, называют коэффициентом отчуждения.

Корреляция части

Коэффициент детерминация R 2 демонстрирует, какой процент дисперсии зависимой переменной может быть связан с дисперсией всех независимых переменных, включенных в каузальную модель. Чем больше этот коэффициент, тем более значимой является выдвинутая нами каузальная модель. Если этот коэффициент оказывается не слишком большим, то и вклад исследуемых нами переменных в общую дисперсию зависимой переменной также оказывается незначительным. На практике, однако, часто требуется не только оценить совокупный вклад всех переменных, но и отдельный вклад каждой из рассматриваемых нами независимых переменных. Такой вклад может быть определен как корреляция части.

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

Рис. 9.1. Соотношение дисперсий зависимой (Y ) и двух независимых (X 1 и Х 2) переменных в корреляционном анализе с двумя независимыми переменными

Как показано на рис. 9.1, вся дисперсия Y , связанная с двумя нашими независимыми переменными, состоит из трех частей, обозначенными а, b и с. Части а и b дисперсии Y принадлежат по отдельности дисперсии двух независимых переменных – Х 1 и Х 2. В то же время дисперсия части с одновременно связывает и дисперсию зависимой переменной У, и дисперсию двух наших переменных X. Следовательно, для того чтобы оценить связь переменной X 1 с переменной Y, которая не обусловлена влиянием переменной Х 2 на переменную Y , необходимо из величины R" 2 вычесть величину квадрата корреляции Y с Х 2:

(9.6)

Аналогичным образом можно оценить часть корреляции У с Х 2, которая не обусловлена ее корреляцией с Х 1.

(9.7)

Величина sr в уравнениях (9.6) и (9.7) и есть искомая нами корреляция части.

Определить корреляцию части можно также и в терминах обычной бивариативной корреляции:

По-другому корреляция части называется полупарциальной корреляцией. Это название означает, что при расчете корреляции эффект второй независимой переменной устраняется применительно к значениям первой независимой переменной, но нс устраняется по отношению к зависимой переменной. Эффект Х 1 как бы корректируется с помощью значений Х 2, так что коэффициент корреляции рассчитывается не между Y и X 1 а между Y и , причем значения рассчитываются на основе значений Х 2 так, как было рассмотрено в главе, посвященной простой линейной регрессии (см. подпараграф 7.4.2). Таким образом, оказывается справедливым следующее соотношение:

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

Частные корреляции

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

Сами значения частной корреляции рr могут быть найдены на основе значений бивариативной корреляции:

Частная корреляция, таким образом, может быть определена как обычная бивариативная корреляция между скорректированными значениями как зависимой, так и независимой переменной. Непосредственно коррекция осуществляется в соответствии со значениями независимой переменной, выступающей в качестве контрольной. Иными словами, частная корреляция между зависимой переменной Y и независимой переменной X i может быть определена как обычная корреляция между значениями и значениями , причем значения и предсказываются на основе значений второй независимой переменной Х 2.

Регрессионный анализ — это статистический метод исследования, позволяющий показать зависимость того или иного параметра от одной либо нескольких независимых переменных. В докомпьютерную эру его применение было достаточно затруднительно, особенно если речь шла о больших объемах данных. Сегодня, узнав как построить регрессию в Excel, можно решать сложные статистические задачи буквально за пару минут. Ниже представлены конкретные примеры из области экономики.

Виды регрессии

Само это понятие было введено в математику в 1886 году. Регрессия бывает:

линейной;
параболической;
степенной;
экспоненциальной;
гиперболической;
показательной;
логарифмической.

Пример 1

Рассмотрим задачу определения зависимости количества уволившихся членов коллектива от средней зарплаты на 6 промышленных предприятиях.

Задача. На шести предприятиях проанализировали среднемесячную заработную плату и количество сотрудников, которые уволились по собственному желанию. В табличной форме имеем:


		Количество уволившихся	Зарплата
			30000 рублей
			35000 рублей
			40000 рублей
			45000 рублей
			50000 рублей
			55000 рублей
			60000 рублей

Для задачи определения зависимости количества уволившихся работников от средней зарплаты на 6 предприятиях модель регрессии имеет вид уравнения Y = а 0 + а 1 x 1 +…+а k x k , где х i — влияющие переменные, a i — коэффициенты регрессии, a k — число факторов.

Для данной задачи Y — это показатель уволившихся сотрудников, а влияющий фактор — зарплата, которую обозначаем X.

Использование возможностей табличного процессора «Эксель»

Анализу регрессии в Excel должно предшествовать применение к имеющимся табличным данным встроенных функций. Однако для этих целей лучше воспользоваться очень полезной надстройкой «Пакет анализа». Для его активации нужно:

с вкладки «Файл» перейти в раздел «Параметры»;
в открывшемся окне выбрать строку «Надстройки»;
щелкнуть по кнопке «Перейти», расположенной внизу, справа от строки «Управление»;
поставить галочку рядом с названием «Пакет анализа» и подтвердить свои действия, нажав «Ок».

Если все сделано правильно, в правой части вкладки «Данные», расположенном над рабочим листом «Эксель», появится нужная кнопка.

в Excel

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

щелкаем по кнопке «Анализ данных»;
в открывшемся окне нажимаем на кнопку «Регрессия»;
в появившуюся вкладку вводим диапазон значений для Y (количество уволившихся работников) и для X (их зарплаты);
подтверждаем свои действия нажатием кнопки «Ok».

В результате программа автоматически заполнит новый лист табличного процессора данными анализа регрессии. Обратите внимание! В Excel есть возможность самостоятельно задать место, которое вы предпочитаете для этой цели. Например, это может быть тот же лист, где находятся значения Y и X, или даже новая книга, специально предназначенная для хранения подобных данных.

Анализ результатов регрессии для R-квадрата

В Excel данные полученные в ходе обработки данных рассматриваемого примера имеют вид:

Прежде всего, следует обратить внимание на значение R-квадрата. Он представляет собой коэффициент детерминации. В данном примере R-квадрат = 0,755 (75,5%), т. е. расчетные параметры модели объясняют зависимость между рассматриваемыми параметрами на 75,5 %. Чем выше значение коэффициента детерминации, тем выбранная модель считается более применимой для конкретной задачи. Считается, что она корректно описывает реальную ситуацию при значении R-квадрата выше 0,8. Если R-квадрата<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Анализ коэффициентов

Число 64,1428 показывает, каким будет значение Y, если все переменные xi в рассматриваемой нами модели обнулятся. Иными словами можно утверждать, что на значение анализируемого параметра оказывают влияние и другие факторы, не описанные в конкретной модели.

Следующий коэффициент -0,16285, расположенный в ячейке B18, показывает весомость влияния переменной Х на Y. Это значит, что среднемесячная зарплата сотрудников в пределах рассматриваемой модели влияет на число уволившихся с весом -0,16285, т. е. степень ее влияния совсем небольшая. Знак «-» указывает на то, что коэффициент имеет отрицательное значение. Это очевидно, так как всем известно, что чем больше зарплата на предприятии, тем меньше людей выражают желание расторгнуть трудовой договор или увольняется.

Множественная регрессия

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

y=f(x 1 +x 2 +…x m) + ε, где y — это результативный признак (зависимая переменная), а x 1 , x 2 , …x m — это признаки-факторы (независимые переменные).

Оценка параметров

Для множественной регрессии (МР) ее осуществляют, используя метод наименьших квадратов (МНК). Для линейных уравнений вида Y = a + b 1 x 1 +…+b m x m + ε строим систему нормальных уравнений (см. ниже)

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

Отсюда получаем:

где σ — это дисперсия соответствующего признака, отраженного в индексе.

МНК применим к уравнению МР в стандартизируемом масштабе. В таком случае получаем уравнение:

в котором t y , t x 1, … t xm — стандартизируемые переменные, для которых средние значения равны 0; β i — стандартизированные коэффициенты регрессии, а среднеквадратическое отклонение — 1.

Обратите внимание, что все β i в данном случае заданы, как нормируемые и централизируемые, поэтому их сравнение между собой считается корректным и допустимым. Кроме того, принято осуществлять отсев факторов, отбрасывая те из них, у которых наименьшие значения βi.

Задача с использованием уравнения линейной регрессии

Предположим, имеется таблица динамики цены конкретного товара N в течение последних 8 месяцев. Необходимо принять решение о целесообразности приобретения его партии по цене 1850 руб./т.


номер месяца	название месяца	цена товара N
		1750 рублей за тонну
		1755 рублей за тонну
		1767 рублей за тонну
		1760 рублей за тонну
		1770 рублей за тонну
		1790 рублей за тонну
		1810 рублей за тонну
		1840 рублей за тонну

Для решения этой задачи в табличном процессоре «Эксель» требуется задействовать уже известный по представленному выше примеру инструмент «Анализ данных». Далее выбирают раздел «Регрессия» и задают параметры. Нужно помнить, что в поле «Входной интервал Y» должен вводиться диапазон значений для зависимой переменной (в данном случае цены на товар в конкретные месяцы года), а в «Входной интервал X» — для независимой (номер месяца). Подтверждаем действия нажатием «Ok». На новом листе (если так было указано) получаем данные для регрессии.

Строим по ним линейное уравнение вида y=ax+b, где в качестве параметров a и b выступают коэффициенты строки с наименованием номера месяца и коэффициенты и строки «Y-пересечение» из листа с результатами регрессионного анализа. Таким образом, линейное уравнение регрессии (УР) для задачи 3 записывается в виде:

Цена на товар N = 11,714* номер месяца + 1727,54.

или в алгебраических обозначениях

y = 11,714 x + 1727,54

Анализ результатов

Чтобы решить, адекватно ли полученное уравнения линейной регрессии, используются коэффициенты множественной корреляции (КМК) и детерминации, а также критерий Фишера и критерий Стьюдента. В таблице «Эксель» с результатами регрессии они выступают под названиями множественный R, R-квадрат, F-статистика и t-статистика соответственно.

КМК R дает возможность оценить тесноту вероятностной связи между независимой и зависимой переменными. Ее высокое значение свидетельствует о достаточно сильной связи между переменными «Номер месяца» и «Цена товара N в рублях за 1 тонну». Однако, характер этой связи остается неизвестным.

Квадрат коэффициента детерминации R 2 (RI) представляет собой числовую характеристику доли общего разброса и показывает, разброс какой части экспериментальных данных, т.е. значений зависимой переменной соответствует уравнению линейной регрессии. В рассматриваемой задаче эта величина равна 84,8%, т. е. статистические данные с высокой степенью точности описываются полученным УР.

F-статистика, называемая также критерием Фишера, используется для оценки значимости линейной зависимости, опровергая или подтверждая гипотезу о ее существовании.

(критерий Стьюдента) помогает оценивать значимость коэффициента при неизвестной либо свободного члена линейной зависимости. Если значение t-критерия > t кр, то гипотеза о незначимости свободного члена линейного уравнения отвергается.

В рассматриваемой задаче для свободного члена посредством инструментов «Эксель» было получено, что t=169,20903, а p=2,89Е-12, т. е. имеем нулевую вероятность того, что будет отвергнута верная гипотеза о незначимости свободного члена. Для коэффициента при неизвестной t=5,79405, а p=0,001158. Иными словами вероятность того, что будет отвергнута верная гипотеза о незначимости коэффициента при неизвестной, равна 0,12%.

Таким образом, можно утверждать, что полученное уравнение линейной регрессии адекватно.

Задача о целесообразности покупки пакета акций

Множественная регрессия в Excel выполняется с использованием все того же инструмента «Анализ данных». Рассмотрим конкретную прикладную задачу.

Руководство компания «NNN» должно принять решение о целесообразности покупки 20 % пакета акций АО «MMM». Стоимость пакета (СП) составляет 70 млн американских долларов. Специалистами «NNN» собраны данные об аналогичных сделках. Было принято решение оценивать стоимость пакета акций по таким параметрам, выраженным в миллионах американских долларов, как:

кредиторская задолженность (VK);
объем годового оборота (VO);
дебиторская задолженность (VD);
стоимость основных фондов (СОФ).

Кроме того, используется параметр задолженность предприятия по зарплате (V3 П) в тысячах американских долларов.

Решение средствами табличного процессора Excel

Прежде всего, необходимо составить таблицу исходных данных. Она имеет следующий вид:

вызывают окно «Анализ данных»;
выбирают раздел «Регрессия»;
в окошко «Входной интервал Y» вводят диапазон значений зависимых переменных из столбца G;
щелкают по иконке с красной стрелкой справа от окна «Входной интервал X» и выделяют на листе диапазон всех значений из столбцов B,C, D, F.

Отмечают пункт «Новый рабочий лист» и нажимают «Ok».

Получают анализ регрессии для данной задачи.

Изучение результатов и выводы

«Собираем» из округленных данных, представленных выше на листе табличного процессора Excel, уравнение регрессии:

СП = 0,103*СОФ + 0,541*VO - 0,031*VK +0,405*VD +0,691*VZP - 265,844.

В более привычном математическом виде его можно записать, как:

y = 0,103*x1 + 0,541*x2 - 0,031*x3 +0,405*x4 +0,691*x5 - 265,844

Данные для АО «MMM» представлены в таблице:

Подставив их в уравнение регрессии, получают цифру в 64,72 млн американских долларов. Это значит, что акции АО «MMM» не стоит приобретать, так как их стоимость в 70 млн американских долларов достаточно завышена.

Как видим, использование табличного процессора «Эксель» и уравнения регрессии позволило принять обоснованное решение относительно целесообразности вполне конкретной сделки.

Теперь вы знаете, что такое регрессия. Примеры в Excel, рассмотренные выше, помогут вам в решение практических задач из области эконометрики.

В регрессионной статистике указываются множественный коэффициент корреляции (Множественный R) и детерминации (R-квадрат) между Y и массивом факторных признаков (что совпадает с полученными ранее значениями в корреляционном анализе)

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

Нижняя часть таблицы – точ

ечные оценки bi генеральных коэффициентов регрессии вi, проверка их значимости и интервальная оценка.

Оценка вектора коэффициентов b (столбец Коэффициенты ):

Тогда оценка уравнения регрессии имеет вид:

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

Проверим на уровне б=0,05 значимость уравнения регрессии, т.е. гипотезу H0: в1=в2=в3=…=вk=0. Для этого рассчитывается наблюдаемое значение F-статистики:

Excel выдаёт это в результатах дисперсионного анализа :

QR=527,4296; Qост=1109,8673 =>

В столбце F указывается значение F набл .

По таблицам F-распределения или с помощью встроенной статистической функции F РАСПОБР для уровня значимости б=0,05 и числа степеней свободы числителя н1=k=4 и знаменателя н2=n-k-1=45 находим критическое значение F-статистики, равное

Fкр = 2,578739184

Так как наблюдаемое значение F-статистики превосходит ее критическое значение 8,1957 > 2,7587, то гипотеза о равенстве вектора коэффициентов отвергается с вероятностью ошибки, равной 0,05. Следовательно, хотя бы один элемент вектора в=(в1,в2,в3,в4)T значимо отличается от нуля.

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

Проверку значимости регрессионных коэффициентов проводят на основе t-статистики для уровня значимости .

Наблюдаемые значения t-статистик указаны в таблице результатов в столбце t -статистика .

	Коэффициенты (bi)	t-статистика (tнабл)
Y-пересечение
Переменная X5
Переменная X7
Переменная X10
Переменная X15

Их необходимо сравнить с критическим значением tкр, найденным для уровня значимости б=0,05 и числа степеней свободы н=n – k - 1.

Для этого используем встроенную статистическую функцию Excel СТЬЮДРАСПОБР, введя в предложенное меню вероятность б=0,05 и число степеней свободы н= n–k-1=50-4-1=45. (Можно найти значения tкр по таблицам математической статистики.

Получаем tкр= 2,014103359.

Для наблюдаемое значение t-статистики меньше критического по модулю 2,0141>|-0,0872|, 2,0141>|0,2630|, 2,0141>|0,7300|, 2,0141>|-1,6629|.

Следовательно, гипотеза о равенстве нулю этих коэффициентов не отвергается с вероятностью ошибки, равной 0,05, т.е. соответствующие коэффициенты незначимы.

Для наблюдаемое значение t-статистики больше критического значения по модулю |3,7658|>2,0141, следовательно, гипотеза H0 отвергается, т.е. - значим.

Значимость регрессионных коэффициентов проверяют и следующие столбцы результирующей таблицы:

Столбец p -значение показывает значимость параметров модели граничным 5%-ым уровнем, т.е. если p≤0,05, то соответствующий коэффициент считается значимым, если p>0,05, то незначимым.

И последние столбцы – нижние 95% и верхние 95% и нижние 98% и верхние 98% - это интервальные оценки регрессионных коэффициентов с заданными уровнями надёжности для г=0,95 (выдаётся всегда) и г=0,98 (выдаётся при установке соответствующей дополнительной надёжности).

Если нижние и верхние границы имеют одинаковый знак (ноль не входит в доверительный интервал), то соответствующий коэффициент регрессии считается значимым, в противном случае – незначимым

Как видно из таблицы, для коэффициента в3 p-значение p=0,0005<0,05 и доверительные интервалы не включают ноль, т.е. по всем проверочным критериям этот коэффициент является значимым.

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

В случае, когда при оценке регрессии выявлено несколько незначимых коэффициентов, первым из уравнения регрессии исключается регрессор, для которого t-статистика () минимальна по модулю. По этому принципу на следующем этапе необходимо исключить переменную Х5 , имеющую незначимый коэффициент регрессии в2

II ЭТАП РЕГРЕССИОННОГО АНАЛИЗА.

В модель включены факторные признаки X7, X10, X15, исключён X5.

ВЫВОД ИТОГОВ