5. Для прогнозирования результативного показателя подставим в уравнение значение фактического показателя, равного 80% от его максимального значения

Хmax=8254 х(t)=0,8*Хmax=0,8*8254=6603,2 у = – 17,3204 + 0,0395*x1

у = – 17,3204 + 0,0395*x1*6603,2=243,788

Для отображения прогнозного значения результативного признака нужно воспользоваться мастером диаграмм. В качестве входных диапазонов необходимо взять данные для факторного признака Х1, для результативного признака –Y, на полученном графике отразить линию тренда.

Для отображения на графике прогноза прибыли (Y) необходимо:

Выделить полученный график, щелкнуть правой кнопкой мыши и выбрать исходные данные, добавить новый ряд и ввести прогнозные значения X,Y, рассчитанные ранее., т.е. Х=6603.2, Y=243.788

Для расчета коэффициента воспользуемся функцией Excel СТЬЮДЕНТ.ОБР.2Х, вероятность возьмем равную 0,1, число степеней свободы 12

Т табл.(t _х1 ) = 1,7958848 Т расч. = 2,81 Трасч. является больше Т табл., следовательно, соответствующий факторный признак признается статистически значимым.Такой фактор рекомендуется в модели регрессии оставить.

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

• стандартная ошибка S=70,5704(таблица «Регрессионная статистика» итогов применения инструмента «Регрессия» пакета «Анализ данных»);

• по столбцу данных Х₁ найдем среднее значение ₌ 5153,46

• определим 187,21429

t_α – коэффициент Стьюдента для уровня значимости α=0.1 = 10% и числа степеней свободы k=11. t_α=1,7958848 (функция СТЬЮДЕНТ.ОБР.2Х).

Тогда, предельная ошибка прогноза:

Определим верхнюю и нижнюю границу интервала.

243,788+136,528 = 380,316

243,788 – 136,528= 107,261

Таким образом, прогнозное значение Y =243,788 будет находиться между нижней границей = 107,261 и верхней = 380,316.

• Задача 2. Построение модели множественной регрессии

1. Осуществите анализ матрицы парных корреляций на предмет мультиколлинеарности.

2. Используя пошаговую множественную регрессию (метод исключения или метод включения), постройте модель множественной регрессии. Дайте экономическую интерпретацию коэффициентов модели регрессии.

3. Осуществите проверку выполнения предпосылок МНК.

4. Оцените качество построенной модели. Улучшилось ли качество модели по сравнению с однофакторной моделью? Дайте оценку влияния значимых факторов на результат с помощью коэффициентов эластичности,  - и  - коэффициентов.

5. Постройте (по лучшей модели) прогноз результативного признака, если предположить, что значения факторных признаков увеличатся относительно средних значений на 10 %.

6. Внесите рекомендации по совершенствованию управления процессом (организацией).

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

№ банка	Прибыль (млн. руб.)	Собственный капитал (млн. руб.)	Привлеченные средства (млн. руб.)	Депозитная ставка (% годовых)	Кредитная ставка (% годовых)
1	115	4428	3278	12,5	17,7
2	80	3756	5696	11,7	18,2
3	97	2970	2210	11,2	19,1
4	92	6231	5823	9,7	15,2
5	129	3960	4569	13,5	18,5
6	223	7354	2896	10,8	18,6
7	251	4662	3526	12,1	15,7
8	267	4760	2259	11,7	16,6
9	137	4569	4596	13,7	17,3
10	163	5274	3271	12,5	19,3
11	225	5418	4596	12,8	17,8
12	278	5359	3256	11,2	14,5
13	367	8254	5189	10,4	13,7

1. Построили в excel матрицу парных корреляций

Вводим исходные данные

Для построения матрицы парных коэффициентов корреляции воспользуемся инструментом Корреляции. Данные-Анализ данных-корреляция

Заполняем необходимые поля диалогового меню

Для анализа матрицы парных корреляций нужно выполнить следующее:

1. Выберем главный ведущий фактор.

Ведущим признается факторный признак, оказывающий наибольшее влияние на результативный показатель (анализируется 1 столбец матрицы и выбирается наибольшее значение по модулю)

В нашей матрице наибольшее значение 0,6465

1.2 установим фактор наличия мультиколлинеарности.

Необходимо смотреть всю оставшуюся часть матрицы кроме 1 столбца. В этой области необходимо определить есть ли коэффициент по модулю больше 0.8

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

2. Строим модель множественной регрессии

Для этого воспользуемся инструментом Регрессия.

Данные-анализ данных-регрессия

Выбираем данные

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

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

у=316,235 + 0,0387*X1-0,0356*X2+28,153*X3-30,563*X4

Оценим качество построенной модели множественной регрессии по следующим направлениям:

Коэффициент детерминации R²=0,799, достаточно близок к единице, следовательно качество модели можно признать хорошим.

Критерий Фишера=7,963

Fтабл=3,84, Fрасч.=7,963, это значит, что уравнение регрессии является статистически значимым. Такое уравнение целесообразно использовать для анализа и прогнозирования.

Оценим качество построенной модели множественной регрессиис помощью коэффициента эластичности  и △коэффициентов. эластичности определяется:

,

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

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

b_i – коэффициенты регрессии соответствующих факторных признаков.

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

,

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

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

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

,

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

- коэффициент детерминации.

Необходимо определить среднее значение всех признаков: Мастер функций

= >СРЗНАЧ.

Коэффициент b_iдля всех значений возьмем из таблицы 3 «Вывода итогов»

Для расчета дисперсии всех значений необходимо: Мастер функций =>ДИСП.

Расчет эластичности показывает на сколько % изменится значение у при изменении значения фактора на 1 %.

Возьмем значения из таблицы матрицы парной корреляции результативного и соответствующего факторного признаков.

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

Частый коэффициент эластичности показывает на сколько процентов изменится среднее значение результативного признака, если среднее значение конкретного факторного признака изменится на 1%. Т.е. при увеличении на 1% собственного капитала (Х1) прибыль (Y) возрастает на 1,07%

• при увеличении на 1% привлеченных средств (Х2) прибыль (Y) уменьшится на 0.75%

• при увеличении на 1% депозитной ставки (Х3) прибыль (Y) увеличится на 1,786%

• при увеличении на 1% кредитной ставки (Х4) прибыль (Y) снизится на 2,802%

В-коэффициент показывает на какую величину изменится СКО результативного признака, если СКО конкретного факторного признака изменится на 1 единицу, т.е.при увеличении на 1 единицу собственного капитала (Х1), СКО прибыль (Y) увеличивается на 0,038 единиц;

• при увеличении на 1 единицу привлеченных средств (Х2), СКО прибыль (Y) уменьшится на 0,49 единицы;

• при увеличении на 1 единицу депозитной ставки (Х3), СКО прибыль (Y) увеличится на 0,375 единиц;

• при увеличении на 1 единицу кредитной ставки (Х4), СКО прибыль (Y) уменьшится на 0,624 единиц;

Δ-коэффициент показывает удельный вес влияния конкретного факторного признака в совместном влиянии всех факторных признаков на результативный показатель, т.е. удельный вес влияния собственного капитала (Х1) на прибыль (Y) составляет 0,512%

• удельный вес влияния привлеченных средств (Х2) на прибыль (Y) составляет 0,108%

• удельный вес влияния депозитной ставки (Х3) на прибыль (Y) составляет -0,108%

• удельный вес влияния кредитной ставки (Х4) на прибыль (Y) составляет 0,488%

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

С помощью функции СТЬЮДРАСПОБР(0,05;10) определим табличное значение=2,20

Сравним расчетные значения t-статистики, взятые по модулю, с табличным значением этого критерия.

Tx1= 3,125>2.31

Tx2= -2,91>2.31

Tx3=1,899<2.31

Tx4= -3,1>2.31

Для анализа возьмем Х2 т.к. он наиболее приближен к табличному значению.