И регрессионного Анализа

В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период.

Требуется:

1. Построить однофакторную модель регрессии.

2. Оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке.

3. Отобразить на графике исходные данные, результаты моделирования.

Номер наблюдения
Прибыль (У)
% ставка (Х)

Таблица с исходными данными имеет вид.

Для вычисления параметров модели составим расчетную таблицу следующего вида.

Эта же таблица в режиме индикации формул выглядит так, как показано на следующем рисунке.

В ячейки С19 и С20 введены формулы для вычисления параметров а₁ и а₀:

Значения параметров приведены на этом рисунке.

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

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

В ячейку С22 введена следующая формула:

Прогнозное значение прибыли составит 13 тыс. руб.

Рассчитаем таблицу остатков.

Таблица остатков в режиме индикации формул имеет следующий вид.

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

В ячейку С38 введена формула для вычисления величины отклонения с использованием встроенной математической функции КОРЕНЬ.

Величина отклонения от линии регрессии составляет 3,4401.

На следующем этапе рассчитываются верхняя граница прогноза и нижняя. Для расчета доверительного интервала воспользуемся следующей формулой:

,

которая введена в ячейку С40.

Значение t_a выбирается по таблице 1. Этот коэффициент является табличным значением t – статистики Стьюдента при заданном уровне значимости a и числе наблюдений. Если задать вероятность попадания прогнозируемой величины внутрь доверительного интервала, равную 90% (a = 0,01), число степеней свободы df = 10-1-1, то t_a= 1,8595.

Значение U=6,804.

Критические значения t-критерия Стьюдента при уровне значимости

0,10, 0,05, 0,01(двухсторонний)

Таблица 1

Число степеней свободы d.f.	α	Число степеней свободы d.f.	α
0,1	0,05	0,01	0,1	0,05	0,01
	6,3138	12,706	63,657		1,7341	2,1009	2,8784
	2,9200	4,3027	9,9248		1,7291	2,0930	2,8609
	2,3534	3,1825	5,8409		1,7247	2,0860	2,8453
	2,1318	2,7764	4,6041		1,7207	2,0796	2,8314
	2,0150	2,5706	4,0321		1,7171	2,0739	2,8188
	1,9432	2,4469	3,7074		1,7139	2,0687	2,8073
	1,8946	2,3646	3,4995		1,7109	2,0639	2,7969
	1,8595	2,3060	3,3554		1,7081	2,0595	2,7874
	1,8331	2,2622	3,2498		1,7056	2,0555	2,7787
	1,8125	2,2281	3,1693		1,7033	2,0518	2,7707
	1,7959	2,2010	3,1058		1,7011	2,0484	2,7633
	1,7823	2,1788	3,0545		1,6991	2,0452	2,7564
	1,7709	2,1604	3,0123		1,6973	2,0423	2,7500
	1,7613	2,1448	2,9768		1,6839	2,0211	2,7045
	1,7530	2,1315	2,9467		1,6707	2,0003	2,6603
	1,7459	2,1199	2,9208		1,6577	1,9799	2,6174
	1,7396	2,1098	2,8982	∞	1,6449	1,9600	2,5758

Для расчета верхней и нижней границ прогноза соответственно вводим ячейки С42 и С43 формулы, как показано на следующем рисунке.

Верхняя граница прогноза равна 19,81 тыс. руб., нижняя – 6,20 тыс. руб.

График исходных данных и результаты моделирования приведены на следующем рисунке.

Для вычисления параметров модели можно было также использовать встроенные статистические функции, такие как НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, СТОШУХ и др.

Функция НАКЛОН вычисляет наклон линии регрессии, в нашем примере это параметр а₁.

Функция ОТРЕЗОК вычисляет параметр а₀.

Функция ЛИНЕЙН одновременно вычисляет оба эти параметра. Перед вводом функции необходимо выделить диапазон ответов (две ячейки), а после заполнения аргументов функции нажать комбинацию клавиш Ctrl+Shift+Enter.

Функция СТОШУХ вычисляет стандартную ошибку, в нашем примере это величина S_y.

Диалоговое окно встроенной статистической функции НАКЛОН с введенными аргументами.

Диалоговое окно встроенной статистической функции ОТРЕЗОК с введенными аргументами.

Диалоговое окно встроенной статистической функции ЛИНЕЙН с введенными аргументами.

Диалоговое окно встроенной статистической функции СТОШУХ с введенными аргументами.

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

Так же для построения модели можно было использовать встроенный инструмент Пакета анализа Регрессия. Выполните команду /Сервис/Анализ данных и в диалоговом окне Анализ данных, выберите инструмент Регрессия.

Заполните аргументы диалогового окна Регрессия, как показано на рисунке.

Excel сгенерирует лист отчета, содержащий следующие таблицы:

· регрессионная статистика;

· дисперсионный анализ;

· таблица остатков,

а также построит график остатков.

График остатков имеет следующий вид.

Дата добавления: 2015-12-07