Методика выполнения регрессионного анализа в программной среде Microsoft Excel

• 1. На основании данных о динамике прироста курса акций у за 10 месяцев, приведенных в таблице и предположения, что генеральное уравнение регрессии имеет вид у = 0+ 1х +, требуется:
  • а) Найти оценку и проверить на 5% уровне значимости уравнения регрессии, то есть гипотезу Н0:1=0;
  • б) Построить таблицу дисперсионного анализа для расчета F-критерия Фишера;
  • в) Найти коэффициент детерминации R2;
  • г) Найти интервальную оценку для прогноза при x=11;

Таблица 1.

Решение.

Расчеты проведем в программе Excel на листе 1 рабочей книги.

В столбец, А занесем данные задачи по переменной х (месяц). В столбец В — данные переменной у (прирост курса акций) (см. рис. 1.1).

Исходные данные будут записываться по столбцам. В частности, значения х будут располагаться в ячейках А2: А11, значения у — в ячейках В2: В11.

Рис. 1.1. Данные задачи

Для того чтобы проверить, существует ли зависимость между признаками, построим диаграмму рассеивания с помощью Мастера диаграмм (см. рис. 1.2).

По рисунку видно, что между признаками существует нелинейная взаимосвязь.

Проведем линеаризацию модели, прологарифмируем и получим:

lny = ln0 + 1 x+ln.

Таблица 2. Исходные данные.

Построим уравнение регрессии по табл. 2, используя Анализ данных. Для этого необходимо провести преобразование переменной у на lny, используя Мастер функции fx.

Построим регрессию Сервис>Анализ данных >Регрессия. В качестве зависимой переменной следует указать переменную lny.

Таблица 3. Итоги регрессии.

Получили уравнение регрессии:

lgy = 1,907−0,105 x.

.

Само уравнение и коэффициенты регрессии значимо отличаются от нуля. Коэффициент при переменной x означает, что с каждым месяцем снижение курса акций составляет 0,105 д.ед.

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

Таблица 4. Нахождение прогнозных значений у.

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

Для оценки значимости уравнения регрессии и для нахождения значения F критерия, рассчитаем Qобщ, Qост и Qрегр, то есть рассчитаем таблицу дисперсионного анализа (табл. 5).

Таблица 5. Расчет таблицы однофакторного дисперсионного анализа.

Для оценки значимости уравнения регрессии проверим гипотезу H0: в1=0.

По таблице F-распределения находят Fкр с числом степеней свободы н1=1, н2=n-2=10−2=8. Найдем его с помощью математических функций: определим критическое значение распределения Фишера:

Fкрит=FРАСПОБР (0,05;1;8) = 5,318.

Так как Fнабл = 2,023 < Fкр=5,318, то гипотеза не принимается и уравнение считается значимым (см. табл. 3).

Проверим значимость каждого коэффициента регрессии.

Для проверки гипотезы H0: в0=0 рассчитали t-статистику. Находим критическое значение распределения Стьюдента с помощью статистической функции СТЮДРАСПОБР (0,05;8) = 2,306, где вероятность (уровень значимости) равна 0,05 и число степеней свободы n-2=10−2=8. В таблице 1.2 t-статистика параметра регрессии b1 меньше критического значения, следовательно, параметр регрессии статистически не значим, а поправочный коэффициент регрессии b0 значим, поскольку его P-вероятность меньше 0,05.

Рассчитаем несмещенную оценку остаточной дисперсии и стандартные ошибки коэффициентов регрессии:

Стандартные ошибки коэффициентов b0 и b1 вычисляют по формулам:

дисперсионный однофакторный регрессия Найдем коэффициент детерминации:

Множественный коэффициент корреляции равен 0,449, что говорит о слабой обратной зависимости между признаками. Коэффициент детерминации показывает, что прирост курса акций на 15,6% обусловлен временем.

Найдем 95% доверительные интервалы для каждого коэффициента регрессии, а затем для прогнозного значения х=11.

Интервальная оценка для параметра в0:

Следовательно, коэффициент b0 изменяется в интервале от -2,972 до 6,786 (табл. 6).

Аналогично интервальная оценка для коэффициента в1:

в1=[-0,105±2,306*0,341].

В ячейках D22 — D25 Листа 1 рабочей книги приведены расчеты для нижней и верхней границ коэффициентов регрессии.

Таблица 6. Расчет доверительных интервалов.

Интервальная оценка для прогноза yпрогноз при x=x0 находится следующим образом:

Интервальная оценка для уравнения регрессии у при х=11:

Получено, что доверительный интервал для прогноза прироста курсовой стоимости акций при приросте фондового индекса х=11 находится в пределах от 0,354 до 10,114 с 95% уровнем надежности.

Таким образом, по полученному уравнению регрессии:

.

можно сформулировать следующие выводы:

• — с каждым месяцем снижение курса акций составляет 0,105 д.ед.;
• — коэффициент детерминации показывает, что полученная модель слабо отражает зависимость между признаками;
• — уравнение регрессии не значимо, а, соответственно, его не следует применять при прогнозировании;
• — поправочный коэффициент регрессии статистически значим, а коэффициент b1 — не значим;
• — в 11 месяце прирост курса акций составит 5,234 д.ед.;
• — доверительный интервал для прогноза прироста курсовой стоимости акций при х=11 находится в пределах 0,354 до 10,114 с 95% уровнем надежности.

Построим в поле корреляции уравнение линейной регрессии (рис. 1.3).

Рис. 1.3. Полулогарифмическая регрессия

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

2. Обозначения и наименование показателей: У — производительность труда (тыс.руб./чел.); Х1 — коэффициент платежеспособности предприятия; Х2 — удельный вес рабочих в составе промышленно-производственного персонала (%); Х6 — удельный вес потерь от брака (%); Х7 — фондоотдача (тыс.р. на 1 р.); Х9 — коэффициент ликвидности.

Таблица 7. Исходные данные.

Решение Задачу построения модели множественной регрессии решим с помощью пакета «Анализ данных» в Excel (Рис. 2.1).

Рис. 2.1 Диалоговое окно «Регрессия»

При использовании инструмента «Регрессия» входным интервалом для Y будут ячейки B2: B54, для X — ячейки C2: G54. Результат регрессионного анализа представим в табл. 8.

Таблица 8. Вывод итогов регрессионного анализа.

В столбце «Коэффициенты» получены коэффициенты уравнения регрессии.

Таким образом, получили уравнение регрессии:

Параметры регрессии показывают, что при изменении коэффициент платежеспособности предприятия на 1 единицу производительность труда увеличивается на 13 руб./чел., при увеличении удельного веса рабочих в составе ППП на 1% производительность труда увеличивается на 0,077 тыс. руб./чел., при увеличении удельного веса потерь от брака на 1% производительность труда снижается на 1,513 тыс. руб./чел., при увеличении фондоотдачи на 1 тыс. руб./чел. производительность труда снижается на 0,387 тыс. руб./чел., а при увеличении коэффициента ликвидности на 1 единицу производительность труда увеличивается на 0,044 тыс. руб./чел.

Стандартные ошибки коэффициентов составляют соответствующую графу в таблице 9.

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

н = n-k-1=53−5-1=47.

Критическое значение находим из таблиц распределения Стьюдента или с помощью статистической функции:

СТЮДРАСПОБР (0,05;47) = 2,012.

Для проверки гипотезы H0: вj=0 сравниваем полученные значения для всех коэффициентов tнабл с tкр=2,012. Получим, что все коэффициенты не значимы, кроме b0 и b1. То есть на производительность труда значимо оказывает влияние параметр «Коэффициент платежеспособности предприятия».

Для проверки значимости коэффициентов также можно использовать Р-значения.

По величине Р-значения возможно определять значимость коэффициентов, не находя критическое значение t-статистики. Если значение t-статистики велико, то соответствующее значение вероятности значимости мало — меньше 0,05, и можно считать, что коэффициент регрессии значим. И наоборот, если значение t-статистики мало, соответственно вероятность значимости больше 0,05 — коэффициент считается незначимым. Результат проверки коэффициентов на значимость будет одинаковым.

Далее представлены доверительные интервалы (нижняя и верхняя границы), которые показывают, в каких пределах лежат коэффициенты полученного уравнения регрессии с 95%-ой надежностью.

В разделе Регрессионная статистика получили:

• — множественный коэффициент корреляции (множественный R) равен 0,572, что говорит о заметной (по шкале Чеддока) степени связи между результативным и факторными признаками;
• — коэффициент детерминации R2=0,327 показывает, что модель достаточно описывает данные, то есть 32,7% вариации производительности труда описывается факторами, входящими в полученную модель;
• — скорректированный коэффициент детерминации имеет тот же смысл, что и R2, но считается, что он точнее отражает степень адекватности модели (его значение довольно низкое).

В Дисперсионном анализе вычисляются:

• — df — число степеней свободы;
• — SS — суммы квадратов разностей;
• — МS — оценки дисперсий;
• — F — вычисленное значение критерия Фишера;
• — Значимость F.

Сумма квадратов регрессии вычисляется по формуле:

Qрегр = SS1;

сумма квадратов остатков:

Qост = SS2;

общая сумма квадратов:

Qобщ=SS.

Выполняется условие:

SS1+SS2=SS.

То есть 115,7646+238,5256=354,2902. Число степеней свободы df для SS1 равно df1=5 (k — число независимых переменных или факторов), для SS2: df2 = n — k — 1= 53−5-1 =47, для SS: df = n — 1= 53 — 1 =52.

Получены оценки средних квадратов:

наблюдаемое значение F-критерия:

Fнабл=4,562.

Сравним полученное значение Fнабл с критическим. Так как Fкрит=2,413.

Значимость F — это вероятность значимости для F критерия. В нашем случае она равна 0,002, то есть гипотеза H0: в1=в2=0 отвергается и уравнение считается значимым.

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