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

Размещено на http://www.allbest.ru

Размещено на http://www.allbest.ru

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).

Рис. 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набл=4,562, то гипотеза Н0: в1=в2=0 отвергается и уравнение считается значимым.

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

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

Размещено на Allbest.ru