Расчет линейной регрессии и корреляции

 

Цель задачи: ознакомиться с технологиями построения уравнение линейной регрессии, реализации метода наименьших квадратов и расчета линейной корреляции, реализованными в Excel.

Предлагается, пользуясь встроенными функциями Excel:

1) построить уравнение линейной регрессии: ;

2) определить при помощи метода наименьших квадратов величину корреляции между исчисленным расчетным значением y (по построенному уравнению регрессии) и значением y из таблицы 1.1 для 2001-2009 годов.

3) провести анализ полученных результатов.

Для расчетов понадобятся следующие функции Excel:

· КОРЕНЬ() – для расчета квадратного корня числа;

· СУММ() – для суммирования по столбцу (строке);

· КОРРЕЛ() – для вычисления парной корреляции.

ПРИМЕЧАНИЕ: Порядок использования функций – см. в мастере функций, который находится на листе Excel в закладке Вставка в подзакладке Функция (f_x).

Исходные данные для задания 2 представлены в таблице 1.1:

Таблица 1.1

Год	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009
Объем выпуска продукции, млн. руб. (yi)	2910	2890	2933	2941	2945	2952	2957	2964	2968	2970
Объем капиталовложений, млн. руб. (xi)	1356	1390	1410	1422	1431	1446	1452	1458	1461	1464

 

Алгоритм расчетов

Линейная регрессия сводится к определению параметров а₀ и а₁ уравнения:

                                                                                                              (3)

Система уравнений для определения параметров а₀ и а₁ выглядит так:

                                                                                      (4)

где y_i и x_i – данные таблицы 1;

n – количество членов временного ряда.

Решение системы (4) находим с помощью метода исключения переменных (вычитая из 1 уравнения системы (4) второе уравнение системы) по следующим формулам:

                                                                                    (5)

                                                                  (6)

Расчет подготовительных вычислений для системы (4) осуществляем в Excel по формулам:

1)

=B5*B5,

где В5 – ячейка Excel, в которой хранится значение объема капиталовложений за текущий год (в данном случае – первый расчетный год).

2)

=B5*B4,

где В5 – ячейка Excel, в которой хранится значение объема капиталовложений за текущий год (в данном случае – первый расчетный год);

В4 – ячейка Excel, в которой хранится значение объема выпуска продукции за текущий год (в данном случае – первый расчетный год).

За все остальные года расчеты (1-2) осуществляются по тому же алгоритму (см. рис. 2).

3)

=СУММ(B4:K4),

где В4:К4 – диапазон суммирования ячеек Excel, в которых хранятся значения объема выпуска продукции за весь расчетный период.

4)

=СУММ(B5:K5),

где В5:К5 – диапазон суммирования ячеек Excel, в которых хранятся значения объема капиталовложений за весь расчетный период.

5)

=СУММ(B9:K9),

где В9:К9 – диапазон суммирования ячеек Excel, в которых хранятся значения объема капиталовложений, возведенного в квадрат, за весь расчетный период.

6)

=СУММ(B11:K11),

где В11:К11 – диапазон суммирования ячеек Excel, в которых хранятся значения объема капиталовложений, умноженного на объем выпуска продукции, за весь расчетный период.

 

Расчет искомых коэффициентов уравнения линейной регрессии а₁ (5) и а₀ (6) осуществляем в Excel по формулам:

1) а₁:

=(L4-(L4-L11)/(B6-L5)*B6)/(L5-(L5-L9)/(B6-L5)*B6),

где L4 – ячейка Excel, в которой хранится значение  за весь расчетный период;

L5 – ячейка Excel, в которой хранится значение  за весь расчетный период;

L9 – ячейка Excel, в которой хранится значение  за весь расчетный период;

L11 – ячейка Excel, в которой хранится значение  за весь расчетный период;

В6 – ячейка Excel, в которой хранится значение количества лет расчета (10);

 

2) а₀:

=(L4-L11-B14*(L5-L9))/(B6-L5),

где L4 – ячейка Excel, в которой хранится значение  за весь расчетный период;

L5 – ячейка Excel, в которой хранится значение  за весь расчетный период;

L9 – ячейка Excel, в которой хранится значение  за весь расчетный период;

L11 – ячейка Excel, в которой хранится значение  за весь расчетный период;

В6 – ячейка Excel, в которой хранится значение количества лет расчета (10);

В14 – ячейка Excel, в которой хранится значение а₁.

 

Расчетные значения Y_i по уравнению регрессии получаем в Excel по формулам (представлена формула для первого года расчетного периода):

=$B$15+ $B$14*B5,

где В5 – ячейка Excel, в которой хранится значение объема капиталовложений за 1 год расчета;

$В$14 – ячейка Excel, в которой хранится значение а₁ (для удобства дальнейших расчетов в Excel адрес этой ячейки фиксируется с помощью значка «$»);

$В$15 – ячейка Excel, в которой хранится значение а₀ (для удобства дальнейших расчетов в Excel адрес этой ячейки фиксируется с помощью значка «$»).

По этому алгоритму осуществляются расчеты за остальные годы расчетного периода (см. рис. 2).

 

Для применения метода наименьших квадратов используем формулу:

                                                                                                       (7)

где В – суммарная оценка погрешности расчетов по методу наименьших квадратов;

Y_i – расчетные значения по уравнению регрессии по годам;

у_i – исходные значения объема выпуска продукции по годам.

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

=(B18-B4)*(B18-B4),

где В18 – ячейка Excel, в которой хранится расчетное значение объема выпуска продукции за 1 год расчета;

В4 – ячейка Excel, в которой хранится исходное значение объема выпуска продукции за 1 год расчета.

По этому алгоритму осуществляются расчеты за остальные годы расчетного периода (см. рис. 2).

Суммарную погрешность расчета В за весь период вычислим в Excel по формуле:

=КОРЕНЬ(СУММ(B20:K20)),

где В20:К20 – диапазон суммирования ячеек Excel, в которых хранятся значения погрешности расчетов за весь расчетный период.

Рис. 2. Алгоритм расчетов по 2 заданию

Суммарную относительную погрешность расчета Y_i за период вычислим в Excel по формуле:

=B22*B6/L4*100,

где В22 – погрешность расчета за весь расчетный период по методу наименьших квадратов;

В6 – ячейка Excel, в которой хранится значение количества лет расчета (10);

где L4 – суммарный объем выпуска продукции за весь расчетный период.

Парную корреляция по y за период вычислим в Excel по формуле:

= КОРРЕЛ(B18:K18;B4:K4),

где В18:К18 – диапазон ячеек Excel, в которых хранятся расчетные значения Y_i за весь расчетный период;

В4:К4 – диапазон ячеек Excel, в которых хранятся значения объема выпуска продукции за весь расчетный период.

Парную корреляцию по х и y за период вычислим в Excel по формуле:

=КОРРЕЛ(B4:K4;B5:K5),

где В5:К5 – диапазон ячеек Excel, в которых хранятся значения объема капиталовложений за весь расчетный период;

В4:К4 – диапазон ячеек Excel, в которых хранятся значения объема выпуска продукции за весь расчетный период.

Алгоритм расчетов на листе Excel с указанием используемых формул и полученными результатами представлен на рисунке 2.

Результаты расчетов позволяют сделать следующие выводы:

· Высокое значение показателя парной корреляции (>0,91) и малая величина относительной погрешности прогнозирования (<1,09%) позволяют утверждать, что составленное уравнение регрессии (у=0,672484783387301 х+1982,01924453955) для рассматриваемого временного ряда имеет высокую степень достоверности прогноза;

· Такие результаты объясняются высоким значением показателя парной корреляции для исходных временных рядов. y_i и x_i (>0,91).