Имеются данные о деятельности крупнейших компаний США в 1996 г. (табл. 1).
Таблица 1
…………………………………………………………………..
Задание
Рассчитайте матрицу парных коэффициентов корреляции; оцените статистическую значимость коэффициентов корреляции.
Рассчитайте параметры линейного уравнения множественной регрессии с полным перечнем факторов.
Оцените статистическую значимость параметров регрессионной модели с помощью t-критерия; нулевую гипотезу о значимости уравнения проверьте с помощью F-критерия; оцените качество уравнения регрессии с помощью коэффициента детерминации EMBED Equation.DSMT4 .
Дайте сравнительную оценку силы связи факторов с результатом с помощью коэффициентов эластичности, EMBED Equation.DSMT4 и EMBED Equation.DSMT4 коэффициентов.
Оцените точность уравнения через среднюю относительную ошибку аппроксимации.
Отберите информативные факторы в модель по t-критерию для коэффициентов регрессии. Постройте модель только с информативными факторами и оцените ее параметры.
Рассчитайте прогнозное значение результата, если прогнозные значения факторов составляют 80% от их максимальных значений.
Рассчитайте ошибки и доверительный интервал прогноза для уровня значимости 5 или 10% (а = 0,05; а = 0,10).
Использование инструмента Корреляция (Анализ данных в EXCEL).
Для проведения корреляционного анализа выполните следующие действия:
Данные для корреляционного анализа должны располагаться в смежных диапазонах ячеек.
Выберите команду Сервис?Анализ данных.
В диалоговом окне Анализ данных выберите инструмент Корреляция, а затем щелкните на кнопке ОК.
В диалоговом окне Корреляця в поле Входной интервал необходимо ввести диапазон ячеек, содержащих исходные данные (выбираем $B$7:$F$32). Если выделены и заголовки столбцов, то установить флажок Метки в первой строке.
Выберите параметры вывода.
ОК.
Рис.1 Диалоговое окно Корреляция
Таблица 1. Результат корреляционного анализа.
Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная У имеет тесную связь с Х1 (ryx1=0,848), с Х2 (ryx2=0.763), X3 (ryx3=0.830). Однако факторы X3 и X1 тесно связаны между собой (rx1x3=0.912), что свидетельствует о наличие мультиколлинеарности.
ryxi>rxixk r yx1>r x1x3 r yx1>r x1x2
ryxk>rxixk 0,848>0,912 не верно 0,848>0,898 не верно
rxixk<0,8 r yx3> r x1x3 r yx2>r x1x2
0,830>0,912 не верно 0,763>0,898 не верно
r x1x3<0,8 r x1x2 <0,8
0,912<0,8 не верно 0,898<0,8 не верно
Если приведенные неравенства (или хотя бы одно из них) не выполняются, то в модель включают тот фактор, который наиболее тесно связан с У.
Оценим значимость коэффициента корреляции. Для этого рассчитаем значение t-статистики по формуле
t расч = (r2/(1-r2)(n-2))/^(1/2)
Рис.2 Фрагмент рабочего листа Excel
Табличное значение критерия Стьюдента можно найти с помощью функции СТЬЮДРАСПОБР: t табл(уровень значимости равен 0,05; число степеней свободы k=25-2) = 2,068.
Рис.3 Фрагмент рабочего листа Excel
Сравнивая числовые значения критериев, видно, что t расч> t табл , т.е. полученное значение коэффициента корреляции значимо.
Для проведения регрессионного анализа выполните следующие действия:
Выберите команду Сервис?Анализ данных.
В диалоговом окне Анализ данных выберите инструмент Регрессия, а затем щелкните на кнопке ОК
В диалоговом окне Регрессия в поле Входной интервал Y введите адрес одного диапазона ячеек, который представляет зависимую переменную ($B$7:$B$32). В поле Входной интервал Х введите адреса одного или нескольких диапазонов, которые содержат значения независимых переменных ($C$7:$F$32) (Рисунок 1.).
Если выделены и заголовки столбцов, то установить флажок Метки в первой строке.
Выберите параметры вывода. В данном примере Новая рабочая книга
В поле Остатки поставьте необходимые флажки.
ОК.
Рисунок 4. Диалоговое окно Регрессия подготовлено к выполнению анализа данных.
Результат регрессионного анализа содержится в таблицах 1 –3. Рассмотрим содержание этих таблиц.
Уравнение регрессии можно записать в следующем виде:
y = -0,362 + 0,003х1 + 0.017x2 +0.005х3 + 0,028х4
Оценим статистическую значимость параметров регрессионной модели с помощью t-критерия:
Значимость коэффициентов уравнения регрессии оценим с использованием t-критерия Стьюдента.
ta0 = -0,304
ta1 = 0,1789
ta2 = 0,8029
ta3 = 1,659
ta4 = 0,7823
Расчетные значения t-критерия Стьюдента для коэффициентов уравнения регрессии приведены в четвертом столбце таблицы 7 протокола EXCEL. Табличное значение t-критерия Стьюдента можно найти с помощью функции СТЬЮДРАСПОБР
Табличное значение t-критерия при 5% уровне значимости и степенях свободы (25-4-1=20) составляет 2.08
При ?=0,2 табличное значение t-критерия составляет 1,32.
При ?=0,3 табличное значение t-критерия составляет 1,06.
Значим только Х3.
Рис.5
Рис.6
Проверим нулевую гипотезу о значимости уравнения проверим с помощью F-критерия:
Значение F-критерия Фишера можно найти в таблице 6 протокола EXCEL.
Табличное значение F-критерия при доверительной вероятности 0,95 при EMBED Equation.2 = k =4 и EMBED Equation.2 =n – k -1= 25 – 4 - 1=20 составляет 2.86. Табличное значение F-критерия можно найти с помощью функции FРАСПОБР
Поскольку F EMBED Equation.2 = 15,53004412>F EMBED Equation.2 , уравнение регрессии следует признать адекватным.
Оценим качество уравнения регрессии с помощью коэффициента детерминации EMBED Equation.DSMT4
Значение коэффициентов детерминации и множественной корреляции можно найти в таблице Регрессионная статистика.
Коэффициент детерминации:
EMBED Equation.3 = 0,756454493
Он показывает долю вариации результативного признака под воздействием изучаемых факторов. Следовательно, около 75% вариации зависимой переменной учтено в модели и обусловлено влиянием включенных факторов.
Чем ближе R2 к 1, тем выше качество модели.
Коэффициент множественной корреляции R:
EMBED Equation.3 = 0,869743924.
Он показывает тесноту связи (связь тесная) зависимой переменной Y с включенными в модель объясняющими факторами.
Дайте сравнительную оценку силы связи факторов с результатом с помощью коэффициентов эластичности, EMBED Equation.DSMT4 и EMBED Equation.DSMT4 коэффициентов.
Проанализируем влияние факторов на зависимую переменную по модели.
Учитывая, что коэффициент регрессии невозможно использовать для непосредственной оценки влияния факторов на зависимую переменную из-за различия единиц измерения, используем коэффициент эластичности (Э):
EMBED Equation.2
EMBED Equation.2 0.029?32.8/1.56=0,602
Коэффициент эластичности показывает, на сколько процентов изменяется зависимая переменная при изменении фактора на один процент. Видим, что при изменении фактора рыночная капитализация на 1 процент чистый доход измениться на 60,2%.
Рассчитаем бета-коэффициенты:
EMBED Equation.DSMT4
Рис.8 Расчет в Excel ?
EMBED Equation.DSMT4 2,538
EMBED Equation.DSMT4 3,626
EMBED Equation.DSMT4 57,469
EMBED Equation.DSMT4 0,310
При неизменном уровне остальных признаков увеличение оборотов капитала на величину среднеквадратического отклонения увеличим чистый доход на на 2,538 ее среднеквадратического отклонения.
При неизменном уровне остальных признаков увеличение использ. капитала на величину среднеквадратического отклонения увеличим чистый доход на 3,626 ее среднеквадратического отклонения.
При неизменном уровне остальных признаков увеличение числен служащих на величину среднеквадратического отклонения увеличим чистый доход на на 57,469 ее среднеквадратического отклонения.
При неизменном уровне остальных признаков увеличение рыночной капитализации компании на величину среднеквадратического отклонения увеличим чистый доход на 0,310 ее среднеквадратического отклонения.
Вычислим EMBED Equation.DSMT4 -коэффициенты:
EMBED Equation.DSMT4
Доля влияния оборотного капитала в суммарном влиянии всех факторов составляет 2,846%, а доля влияния использ капитала -3,659%, числ служащих -63,024%, рыночн капитализация компании – 0,11%.
Оцените точность уравнения через среднюю относительную ошибку аппроксимации.
Определим среднюю относительную ошибку:
Рис.9 Рабочий лист Excel
Рис.10 Расчет в Excel
Еотн =1/n*?| yi – yi / yi |*100% = 1/n? | ?i / yi |*100% = 41%
Ошибка аппроксимации меньше 7% свидетельствует о хорошем качестве модели.
В среднем расчетные значения у для линейной модели отличаются от фактических значений на 41%
Отберите информативные факторы в модель по t-критерию для коэффициентов регрессии. Постройте модель только с информативными факторами и оцените ее параметры.
В модель отбираем X3
Рис.11 Диалоговое окно Регрессия
Рассчитайте прогнозное значение результата, если прогнозные значения факторов составляют 80% от их максимальных значений.
x3=596
Y= y=0,6467+0,0079*596= 5,3551
Рассчитайте ошибки и доверительный интервал прогноза для уровня значимости 5 или 10% (а = 0,05; а = 0,10).
Доверительный интервал прогноза будет иметь следующие границы:
Верхняя граница прогноза: Yпр+U(1)
Нижняя граница прогноза Yпр- U(1)
EMBED Equation.DSMT4
Se= 0,754
EMBED Equation.DSMT4
EMBED Equation.DSMT4
EMBED Equation.DSMT4
EMBED Equation.DSMT4
Рис.12 Лист Excel
EMBED Equation.DSMT4
нижняя граница 5,719-1,126=4,594
верхняя граница 5,719+1,126=6,845
