Методические рекомендации по проведению корРеляционно-регрессионного анализа в microsoft excel минск 2010

методические рекомендации по проведению корРеляционно-регрессионного анализа

в microsoft excel

МИНСК 2010

Наиболее широкое применение в практике экономических исследований получило стохастическое моделирование, которое является дополнением и углублением детерминированного анализа. В анализе финансово-хозяйственной деятельности стохастические модели используются, когда необходимо:

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

В Microsoft Excel представлено большое число статистических, финансовых и инженерных функций. Некоторые из них являются встроенными, другие доступны только после установки пакета анализа - набора средств анализа данных, предназначенного для решения сложных статистических задач. Для анализа данных с помощью этих инструментов следует указать входные данные и выбрать параметры; анализ будет выполнен с помощью подходящей статистической макрофункции, а результат будет помещен в выходной диапазон. Другие средства позволяют представить результаты анализа в графическом виде.

Настоящие методические рекомендации призваны раскрыть организационные, методические и практические (на условном примере) аспекты проведения корреляционного и регрессионного анализа с использованием возможностей Microsoft Excel.

1. Теоретические основы проведения корреляционного и регрессионного анализа
Стохастический (от греч. stochastikos - умеющий угадывать) анализ - это метод решения широкого класса задач статистического оценивания. Он предполагает изучение массовых данных путем построения моделей изменения показателей за счет факторов, не находящихся в функциональной связи, в прямой или обратной взаимозависимости. Стохастический анализ направлен на изучение косвенных связей в случае невозможности определения цепи прямой связи. Его использование обусловлено тем, что в экономических исследованиях чаще встречаются стохастические зависимости, которые отличаются приблизительностью и неопределенностью.

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

Таким образом, применение корреляционно-регрессионного анализа позволяет решить две задачи:

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

- изучается характер связи, т.е. количественно измеряется действие одного или нескольких факторов на изменение результативного показателя. В результате по значению коэффициента регрессии (b) устанавливают, насколько изменяется величина результативного показателя при изменении факторного показателя на единицу.

Долю влияния учтенных факторов, включенных в многофакторную корреляционную модель, на результативный показатель можно установить с помощью коэффициента множественной детерминации (R²).

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

В случае если связь всех факторных показателей с результативным показателем носит прямолинейный характер, то для записи этих зависимостей можно использовать линейную функцию.

Наиболее простым уравнением, которое устанавливает линейную зависимость между двумя показателями, является уравнение прямой (1):
(1)

где у_х - результативный показатель;

х - факторный показатель;

а - свободный член уравнения, который не зависит от фактора х;

b - коэффициент регрессии, который показывает влияние на результативный показатель изменения величины фактора на единицу его измерения.

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

Уравнение множественной регрессии при линейной форме связи имеет вид (2):

(2)

 где у_х - результативный показатель;

а – свободный член уравнения, который не зависит от факторов х;

b_1, b₂ …b_n – коэффициенты регрессии, которые показывают количественное влияние на результативный показатель изменения величины фактора на единицу его измерения;

х₁, х₂ ... х_n - факторные показатели.

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

При отборе факторов следует соблюдать следующие правила:

- не включать в корреляционную модель факторы, связь которых с результативным показателем носит функциональный характер;

- отбирать самые значимые факторы, которые оказывают решающее воздействие на результативный показатель;

- для составления уравнения множественной регрессии необходимо, чтобы число наблюдений было в 5-6 раз больше числа отобранных факторов;

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

• 
  аналитических инструментов Пакета анализа
  

• 
  возможностей Мастера функций
  

Для выполнения расчетов в Microsoft Excel формируется таблица, содержащая исходные данные о факторных и результативном показателях (рисунок 1). Сохраните рабочую книгу под именем «Корреляция».

Рисунок 2- Окно выбора инструментов Анализа данных

Далее заполняют элементы появившегося диалогового окна «Корреляция» (рисунок 3), исходя их логического содержания.

Рисунок 3 – Диалоговое окно «Корреляция

Результаты корреляционного анализа представлены на рисунке 4

Рисунок 4 – Результаты корреляционного анализа данных

Исходя из исчисленных значений коэффициентов корреляции теснота взаимосвязи между показателями характеризуется следующим образом (таблица 3).
Таблица 3 - Характеристика тесноты связи между показателями

Взаимосвязанные показатели	Условное обозначение взаимосвязи	Значение коэффициента корреляции	Характеристика тесноты связи
Уровень рентабельности (у),% Скорость обращения товаров (х1), раз	У ↔ Х1	0,351734538	Слабая
Уровень рентабельности (у), % Уровень издержек обращения (х2), %	У ↔ Х2	0,902562505	Весьма сильная
Уровень рентабельности (у),% Выработка на одного работника (х3), млн.руб.	У ↔ Х3	0,355760027	Слабая
Скорость обращения товаров (х1), раз Уровень издержек обращения (х2), %	Х1 ↔ Х2	0,176468217	Очень слабая
Скорость обращения товаров (х1), раз Выработка на одного работника (х3), млн.руб.	Х1 ↔ Х3	0,797276421	Сильная
Уровень издержек обращения (х2), % Выработка на одного работника (х3), млн.руб.	Х2 ↔ Х3	0,161722767	Очень слабая

Регрессионный анализ
В Microsoft Excel для проведения регрессионного анализа и определения коэффициентов регрессии, коэффициента детерминации и множественной корреляции используют инструмент анализа Регрессия: Сервис → Анализ данных → Регрессия (рисунок 5).

Рисунок 5 – Диалоговое окно «Регрессия»

С помощью аналитического инструмента Регрессия определим влияние на результативный показатель рентабельность продаж (у) трех факторов: скорости обращения товаров (х1), уровня издержек обращения (х2) и выработки на одного работника (х3). Заполняют элементы появившегося диалогового окна «Регрессия», исходя их логического содержания (таблица 4).

После заполнения необходимых пользователю элементов диалогового окна «Регрессия» следует нажать экранную кнопку ОК. Результаты вычислений отражены на рисунке 6.

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

На рисунке 6 представлены также примечания к ячейкам, которые позволяют интерпретировать результаты вычислений и построить уравнение регрессии.

Коэффициент детерминации множественной регрессии равен 0,86180673, из чего следует, что в факторную модель включено 86,18 % факторов, повлиявших на изменение рентабельности продаж.

Уравнение многофакторной регрессии имеет следующий вид (3):

 

Y = -0,28767 + 0,09814*Х1 + 1,20181*Х2 + 0,00879*Х3.

(3)

 

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

Проведенный анализ позволил выявить следующие тенденции:

- при росте скорости обращения товаров на 1оборот уровень рентабельности продаж увеличивается на 0,09814%;

- при увеличении уровня расходов на реализацию на 1 % уровень рентабельности продаж возрастал на 1,20181%;

- при увеличении производительности труда на одного работника на 1 млн.руб. уровень рентабельности продаж увеличивается на 0,00879%.

• 
  рентабельностью продаж (у) и скоростью обращения товаров (х1);
  
• 
  рентабельностью продаж (у) и уровнем издержек обращения (х2);
  
• 
  рентабельностью продаж (у) и выработкой на одного работника (х3).
  

Рисунок 7 Диалоговое окно функции коррел.

В поле Массив1 вносится интервал значений по одному их взаимосвязанных показателей, в поле Массив 2 — интервал значений по другому показателю.

Примечание. Массив1 и Массив2 должны иметь одинаковое количество данных, иначе функция КОРРЕЛ выдает значение ошибки (Н/Д).

Если интервал значений массивов содержит текст, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.

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

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

Сравнив полученные коэффициенты парной корреляции (рисунок 8) с результатами вычислений, представленными на рисунке 4 можно сделать вывод об их абсолютной идентичности.
Регрессионный анализ
При помощи функции ЛИНЕЙН можно вычислить коэффициенты уравнения регрессии, показывающие линейную зависимость результативного и факторных показателей. По данным таблицы исходных данных (рисунок 1), используя функцию ЛИНЕЙН Мастера функций, вычислим коэффициенты уравнения множественной регрессии, отражающего взаимосвязь между результативным показателем - рентабельность продаж (у) и факторными показателями: скорость обращения товаров (х1), уровень издержек обращения (х2) и выработка на одного работника (х3).

Перед тем, как задать функцию ЛИНЕЙН следует выделить массив для вывода результатов. Число столбцов массива определяется по количеству участвующих в расчете показателей (в нашем примере их 4), число строк равно 5 (исходя из объема дополнительных регрессионной статистики). Таким образом, выделяется массив размером 4х5 ячеек (рисунок 9).

Рисунок 8 – Массив ячеек для вывода результатов

Далее выбирается функция ЛИНЕЙН либо из главного меню (Вставка → Функции → ЛИНЕЙН), либо нажатием пиктограммы в строке формул (рисунок 9).

Рисунок 9 – Диалоговое окно функции ЛИНЕЙН

Заполняют аргументы функции ЛИНЕЙН по данным таблицы рисунка 1, исходя их логического содержания (таблица 5).
Таблица 5 – Аргументы функции ЛИНЕЙН

Аргумент функции	Содержание аргумента
Известные_значения_y	Ссылка на диапазон анализируемых данных по результативному показателю . Диапазон должен состоять из одного столбца без заголовка.
Известные_значения_x	Ссылка на диапазон анализируемых данных по факторным показателям. Диапазон может содержать одно или несколько множеств переменных без заголовочного текста – только цифры
Конст	Логическое значение, которое указывает, требуется ли, чтобы константа а была равна 0. Если Конст имеет значение ИСТИНА или опущено, то а вычисляется обычным образом. Если аргумент Конст имеет значение ЛОЖЬ, то а полагается равным 0 и значения b подбираются так, чтобы выполнялось соотношение y = bx.
Статистика	Логическое значение, которое указывает, требуется ли выводить дополнительную статистику по регрессии. Если аргумент Статистика имеет значение ИСТИНА, то функция ЛИНЕЙН отражает на выходе дополнительную регрессионную статистику, Если аргумент статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только коэффициенты регрессионного уравнения b и постоянную а.

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

Рисунок 10 – Результаты выполнения функции ЛИНЕЙН с дополнительной статистикой

1. 
   Кравченко Л.И. Анализ хозяйственной деятельности в торговле. Учебник для студентов экон. спец. вузов. – 10-е изд., испр.- М.: Новое знание, 2008.
   
2. 
   Савицкая Г.В. Экономический анализ, 13-е изд., перераб. и доп. - М.: ИНФРА-М, 2007.
   
3. 
   Савицкая Г.В. Теория анализа хозяйственной деятельности. - М. ИНФРА-М, 2007.
   
4. 
   Теория анализа хозяйственной деятельности/ Учебник под ред. Л.И. Кравченко. Мн.: Новое знание, 2005.
   
5. 
   Теория экономического анализа: учебник / под ред. Баканова М.И.., изд. 5-е перераб. и доп. - М.: Финансы и статистика, 2006.
   
6. 
   Экономический анализ в торговле / Под ред. М.И.Баканова. М. «Финансы и статистика», 2005.