Множественная линейная регрессия в ms Excel

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

Рассмотрим следующую задачу.

Задача 3. Инвестиционная компания «Аргон-Инвест» рассматривает инвестиционный проект, связанный с покупкой 20%-ного пакета акций АО «N-ский металлургический комбинат». Стоимость пакета C_П составляет 70 млн. USD. Менеджерами компании собрана информация об аналогичных сделках. Для оценки стоимости пакета акций ими выбраны следующие параметры:

• стоимость основных фондов предприятия C_ОФ, млн. USD;

• объем годового оборота предприятия V_О, млн. USD;

• кредиторская задолжненность предприятия V_К, млн. USD;

• дебиторская задолженность предприятия V_Д, млн. USD;

• задолженность предприятия по заработной плате V_ЗП, тыс. USD.

Для решения задачи 3 средствами MS Excel составляем таблицу исходных данных (рис. 10) и вызовем окно Анализ данных (рис. 1), где выбираем раздел Регрессия (рис. 11). Параметры Входной интервал Y и Входной интервал X представляют собою зависимую и независимые переменные уравнения множественной линейной регрессии. Результаты расчетов приведены на рис. 12.

Рис. 10

Рис. 11

Рис. 12

По этим результатам может быть построено следующее уравнение регрессии:

CP = 0,103CO + 0,541VO – 0,031VK +0,405VD +0,691VZP – 265,844

или

y = 0,103x1 + 0,541x2 – 0,031x3 +0,405x4 +0,691x5 – 265,844

Коэффициент множественной корреляции, коэффициент детерминации, критерий Фишера и критерий Стьюдента позволяют не отвергнуть гипотезу о линейном характере зависимости стоимости пакета акций предприятий от параметров, приведенных в таблице.

Подставив соответствующие данные для N-го металлургического комбината (таблица 1) в полученное уравнение регрессии получаем искомое значение стоимости пакета акций.

Таблица 1

СОФ, USD	VО, USD	VК, USD	VД, USD	VЗП, USD	CП, USD
102,50	535,50	45,20	41,50	21,55	64,72

Таким образом, стоимость пакета акций не должна превышать 64,72 млн. USD. То есть, инвестиционной компании «Аргон-Инвест» нецелесообразно приобретать предлагаемый пакет акций, так как его сумма завышена.

3. Расчеты прогнозных данных в ms Excel

Использование линии тренда. Для того, чтобы построить линию тренда необходимо сначала построить точечную диаграмму того же самого процесса. Возвратимся к задаче 1 (рис.7) и построим точечную диаграмму (рис. 13).

Рис. 13

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

а)

б)

Рис. 14

На вкладке Тип (рис. 14а) выбирается тип аппроксимации данных (в нашем случае – линейная), а на вкладыше Параметры (рис. 14б) – необходимо у

становить флажки Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R2) (коэффициент детерминации R²). Вид построенной линии тренда показан на рис. 15. Из рисунка видно, что уравнение регрессии не отличается от полученного в разделе .

Рис. 15

Если в качестве типа аппроксимации избрать полином второй степени (параболу), то построенная линия тренда будет иметь вид рис. 16.

Рис. 16

Из этого рисунка видно, что полученные в данном случае результаты будут более адекватны, чем полученные ранее.

На вкладке Параметры (рис. 14б) кроме того, можно указать количество единиц прогноза, тем самым спрогнозировать искомую величину (рис. 17).

Рис. 17

Использование функции ПРЕДСКАЗ(). Функция ПРЕДСКАЗ() предназначена для расчета значений зависимой случайной величины по известным статистическим данным на основе линейного приближения методом наименьших квадратов и имеет следующий синтаксис: