Выполнение Задания 4

По десяти кредитным учреждениям получены данные, характеризующие зависимость объема прибыли (Y) от среднегодовой ставки по кредитам (X1), ставки по депозитам (X2) и размера внутрибанковских расходов (X3). Осуществим анализ зависимости прибыли предприятия (Y) от представленных факторов. Данные приведены в условных единицах (табл.19).

Таблица 19

Y
X1
X2
X3

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результаты регрессионного анализа представлены на рис. 9.

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

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

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

При построении многофакторной регрессии необходимо определить набор факторных признаков, включаемых в уравнение множественной регрессии. Выбор факторных признаков осуществляется на основе анализа матрицы парных корреляций. Анализируя матрицу парных корреляций (рис.7), можно утверждать, что между факторными признаками X1 и X3 существует эффект мультиколлинеарности (коэффициент корреляции между ними 0,843608>0,8). Для построения уравнения множественной регрессии следует выбрать признаки X2 и X3 (X1 исключаем, так как коэффициент корреляции между X1 и Y меньше, чем между X3 и Y 0,783838<0,883761).

Расчет параметров регрессионной модели можно осуществить с помощью инструмента анализа данных Регрессия (рис. 10).

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

Результаты регрессионного анализа данных представлены на рис. 11.

Рис.11. Вывод итогов регрессионного анализа

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

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

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

5) Оценим влияние на результативный признак факторных признаков, рассчитав коэффициенты эластичности, бетта- и дельта- коэффициенты.

Коэффициент эластичности рассчитывается по формуле:

, (24)

где - среднее значение соответствующего факторного признака,

- среднее значение результативного признака,

b_i – коэффициенты регрессии при факторных признаках из уравнения множественной регрессии

ß-коэффициент определяется по формуле:

, (25)

где - среднеквадратическое отклонение (СКО) соответствующего факторного признака (рассчитывается как корень квадратный из дисперсии признака),

- СКО результативного признака.

∆-коэффициент определяется по формуле:

, (26)

где - коэффициент парной корреляции результативного признака и соответствующего фактора X (1-й столбец таблицы парной корреляции),

- коэффициент детерминации (1 таблица (регрессионная статистика), 2-я строка сверху из вывода итогов для Регрессии (см.рис. 10)).

На рис. 12 представлены формулы расчетов описанных выше коэффициентов.

Рис. 12. Формулы расчетов коэффициентов

Результаты вычислений представлены в таблице 20.

Таблица 20

Результаты расчета коэффициентов

	Y	X2	X3
ср.знач	47,8	59,4	88,4
эласт.		0,245114	0,881663
дисп	134,6222	67,6	247,8222
ско	11,60268	8,221922	15,74237
bi		0,197247	0,592429
Bi		0,139774	0,803801
ryxi		0,599604	0,883761
deltai		0,105529	0,894471

Выводы: Частный коэффициент эластичности показывает, на сколько процентов изменится среднее значение результативного признака, если среднее значение конкретного факторного признака изменится на 1%, т.е при увеличении на 1% размера внутрибанковских расходов (X3) объём прибыли увеличится на 0,88%.

β-коэффициент показывает, на какую величину изменится СКО результативного признака, если СКО конкретного факторного признака изменится на 1 единицу, т.е. при увеличении на 1 единицу СКО ставки по депозитам (X2), СКО объёма прибыли увеличится на 0,139.

∆-коэффициент показывает удельный вес влияния конкретного факторного признака в совместном влиянии всех факторных признаков на результативный показатель, т.е. удельный вес влияния внутрибанковских расходов (X3) от совместного влияния X2 и X3 на результативный признак объём прибыли составляет 89,4%. Удельный вес влияния ставки по депозитам (X2) на объём прибыли (Y) составляет 10,5 %.

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

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

Таблица 21

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

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение	-16,2872	14,93646	-1,090434022	0,3116	-51,606	19,031	-51,606	19,031
X2	0,197247	0,295027	0,668573305	0,5251	-0,5003	0,8948	-0,5003	0,8948
X3	0,592429	0,154086	3,844787137	0,0063	0,2280	0,9567	0,2280	0,9567

· t _х2 = 0,668573 < t_таб=2,364624, следовательно, фактор Х₂ статистически не значим.

· t _х3 = 3,844787 > t_таб=2,364624, следовательно, фактор Х₃ статистически значим и информативен, и, следовательно, его можно оставить в уравнении регрессии.

Построим уравнение регрессии от информативного фактора Х3 (рис. 13).

Рис.13. Регрессия с информативным фактором

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

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

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

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

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

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

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

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

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

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

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

Рис.17. Прогноз по фактору X2

Из полученного уравнения тренда: X2_Прогн.=1,8061*11+49,467=69,3341

Аналогично получим X3_Прогн. (рис. 18).

Рис.18. Прогноз по фактору X3

Из полученного уравнения тренда: X3_Прогн.=4,9455 *11+61,2=115,6005

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

Y_Прогн.=0,197247*X2_Прогн.+0,592429*X3_прогн.-16,2872

Y_Прогн.=0,197247*69,3341+0,592429*115,6005-16,2872=65,873832

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

(27)

где = 5,968678 (стандартная ошибка из таблицы регрессионная статистика, рис. 10),

Y_Прогн. – рассчитанное выше значение точечного прогноза результативного признака,

Кр= t_таб= 2,364624 табличный коэффициент Стьюдента, можно определить с помощью функции СТЬЮДРАСПОБР(0,05;7),

- среднее значение результирующего показателя.

Получим U(k)= 5,968678*2,364624*√(1+0,1+326,6634/1211,6)= 16,51731

Таким образом, прогнозное значение Y_расч = 65,873832 будет находиться между верхней границей, равной

65,873832 + 16,51731 = 82,39113827 (млн.рублей)

и нижней границей, равной

65,873832 – 16,51731= 49,3565254 (млн.рублей).

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

Значение F-критерия Фишера=13,50486 (колонка F в таблице дисперсионный анализ, рис. 10) при вероятности ошибки α, соответствующей расчетному значению F-критерия, p<0,004, свидетельствует о статистической значимости построенного уравнения множественной регрессии и о возможности и целесообразности использования данного уравнения для построения прогнозов объема прибыли организации.

Прибыль организации на последующий период с вероятностью расчетов 95 % будет находиться в пределах от 49,35 до 82,39 млн.рублей.