Корреляционно-регрессионный анализ данных

Задание:

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

Таблица 4: данные по заданию.

№ компании	Годовая прибыль	Собственные средства	Страховые резервы	Страховые премии	Страховые выплаты

Осуществить анализ данных в соответствии с типовым заданием.

Решение задания:

1) Матрицу парных коэффициентов корреляции признаков можно рассчитать, используя инструмент Анализа данных Корреляция. Для этого:

1. Присвоение переменных:

Годовая прибыль =>Y

Собственные средства =>X1

Страховые резервы =>X2

Страховые премии =>X3

Страховые выплаты =>X4

2. В главном меню выбрать Сервис->Анализ данных->Корреляция

3. Заполнить диалоговое окно ввода параметров (рис. -).

Рис.5. Параметры инструмента «Корреляция».

Рис.6. Матрица коэффициентов парной корреляции.

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

2) Построимлинейную регрессию от ведущего фактора (парную регрессию) с помощью инструмента Анализа данных Регрессия.

Технология выполнения расчетов следующая:

1. В главном меню выберите Сервис->Анализ данных->Регрессия

2. Заполните диалоговое окно ввода данных и параметров вывода (рис. -).

· Входной интервал Y – диапазон, содержащий данные результативного признака.

· Входной интервал X – диапазон, содержащий данные факторов независимого признака (так как модель однофакторная, то ведущим фактором признан X2).

· Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет.

· Константа-ноль – флажок, указывающий на наличие или отсутствие свободного члена в уравнении (метку напротив параметра не ставить).

· Выходной интервал – достаточно указать левую верхнюю ячейку диапазона вывода итогов регрессионного анализа.

Рис.7. Параметры инструмента «Регрессия».

Рис. 8. Результаты регрессионного анализа.

Оценим качество построенной однофакторной регрессии, используя коэффициент детерминации R-квадрат. По данным осуществленных расчетов он равен 0,54862 значение не достаточно близкое к 1, следовательно, общее качество построенного уравнения регрессии не является достаточно

высоким. Следовательно, нужно провести множественную регрессию.

3) Построение многофакторной регрессионной модели

В данной работе по матрице коэффициентов парной корреляции

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

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

Рис.9. Диалоговое окно Регрессия.

Рис 10. Результаты регрессионного анализа.

На основе полученных данных можно записать уравнение множественной регрессии:

Y= 0,01000*X1 + 0,01228*X2 + 0,00118*X3 + (-0,00506*X4) - 49,38447

4) Оценим качество уравнения множественной регрессии с помощью коэффициента детерминации R-квадрат. Исходя из полученных результатов, R-квадрат для множественной регрессии равен 0,80559.
При построении уравнения однофакторной регрессии (от ведущего фактора) R-квадрат равен 0,62588, следовательно, можно утверждать, что уравнение множественной регрессии обладает более высоким качеством.

5) Для построения регрессии с информативными факторами необходимо осуществить оценку статистической значимости факторных признаков, используя уравнение множественной регрессии с помощью t-критерия Стьюдента.

С помощью функции СТЬЮДРАСПОБР(A1;A2) определим табличное значение t_таб, A1 = 0,05, A2 = n - m -1, где n - количество наблюдений, m - количество функций. СТЬЮДРАСПОБР(0,05;12-4-1) = 2,364624. Сравним расчетные значения (t-статистика) с табличным по модулю (расчетные значения берутся из столбца t-статистика табл. -, получаемой при регрессионном анализе):

Рис 11. Результаты регрессионного анализа с выделением t-статистика.

tx1 = 2,21329 ≈ 2,36, следовательно, фактор Х₁ статистически значим и информативен, и, следовательно, его можно оставить в уравнении регрессии.

tx2 = 1,92832 < 2,36 следовательно, фактор Х₂ статистически не значим.

tx3= 0,29798 < 2,36 следовательно, фактор Х₃ статистически не значим.

tx4= -1,41379 < 2,36 следовательно, фактор Х₄ статистически не значим.

Пусть X2 статически не значим, тем не менее является вторым статическим фактором по важности.

Построим уравнение регрессии для информативных факторов Х1 и X2.

Рис. 12. уравнение регрессии для информативных факторов Х1 и X2.

Оценим качество данного уравнения регрессии, используя коэффициент детерминации: R-квадрат равен 0,76859, следовательно, качество построенного уравнения регрессии можно признать высоким.

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

6) Определим точечный и интервальный прогноз объема прибыли организации, используя уравнение множественной регрессии:

Y=0,197247*X2+0,592429*X3-16,2872

Определим предварительно прогнозные значения для каждого фактора, включенного в модель регрессии. Для этого построим графики X2(t), X3(t) и подберем тренд по каждому из факторов.

Выбор типа диаграммы и технология построения приведены на рис.14 и рис.15-16.

7) Определим точечный и интервальный прогноз объема прибыли организации, используя статически значимые информативные факторы X1, X2 и уравнение множественной регрессии для этих факторов.

Y= 0,00973*X1+ 0,01538*X2 -84,88002

Определим предварительно прогнозные значения для каждого фактора, включенного в модель регрессии. Для этого построим графики X1(t) и X2(t), подберем тренд по каждому из факторов.

Выбор типа диаграммы и технология построения приведены на рис. и рис.

Рис 13. Выбор типа диаграммы.

Рис.14. Выбор источника данных.

На полученной диаграмме добавить линию тренда (Диаграмма->Добавить линию тренда). В настройках тренда указать Параметры->Показать уравнение на диаграмме, Параметры ->Прогноз вперед на 1 единицу (рис. 16).

Рис.15. Параметры линии тренда.

Результат данных X1 представлен на рис.16. и рис.17.

Линию тренда на графике X1 построили линейную и полиноминальную.

Рис.16. Линейная линия тренда для фактор X1.

Рис.17. Полиноминальная линия тренда для фактор X1.

В полиноминальной модели величина аппроксимации R² 0.212, а в линейной модели 0.145.

0.212 > 0.145. Эффективность полиноминальной модели выше, и она более подходит для прогнозирования.

Из полученного уравнения тренда спрогнозируем значение X1 в 13 и 14 расчетный период:

X1_{Прогн.13} = -50,47*13² + 429,8*13 + 4017 = 1074.97

X1_{Прогн.14} = -50,47*14² + 429,8*14 + 4017 = 142.08

Результат данных X2 представлен на рис.18.

Линию тренда на графике X2 построили полиноминальную как наиболее эффективную.

Рис.18. Полиноминальная линия тренда для фактор X2.

Из полученного уравнения тренда спрогнозируем значение X2 в 13 и 14 расчетный период:

X2_{Прогн.13} = 46,42*13² - 735,0*13 + 9503 = 7792,98

X2_{Прогн.14} = 46,42*14² - 735,0*14 + 9503 = 8311,32

Графики прогноза X1 и X2 ниже. Рис.19. и Рис.20.

Рис.19. Графики прогноза для фактора X1.

Рис.20. Графики прогноза для фактора X2.

Рассчитаем точечный прогноз прибыли (результативного признака) Y, подставив в уравнение множественной регрессии точечные прогнозы факторных признаков X1 и X2 за 13 и 14 период:

Y_прог.13=0,00973*X1_прог.13+0,01538*X2_прог.13-84,88002= 0,00973*1074,97+0,01538*7792,98-84,88002= 45,43547

Y_прог.14=0,00973*X1_прог.14+0,01538*X2_прог.14-84,88002= 0,00973*142,08+0,01538*8311,32-84,88002= 44,33052

Вывод: Полученный коэффициент детерминации R²=0,74502, следовательно, вариация результативного признака Y на 74,5% учтена в модели и обусловлена влиянием включенных в модель факторов. Коэффициент множественной корреляции R= 0,80559 показывает, что зависимая переменная Y тесно связана с включенными в модель факторами X1 и X2.

Прибыль организации на 13 и 14 последующий период составит 45,44 и 44,33 тыс. руб.