Практическое занятие № 18. Тема: применение корреляционно-регрессионного анализа для определения параметров функциональной зависимости между результативным фактором и факторами-показателями при обосновании бизнес-плана создания новой структурной единицы.

Практическое занятие № 18

Тема: Применение корреляционно-регрессионного анализа для определения параметров функциональной зависимости между результативным фактором и факторами-показателями при обосновании бизнес-плана создания новой структурной единицы.

Задание и ход выполнения работы:

· Определить тесноту связи между результативным показателем – прибыль от реализации и каждым показателем-фактором (торговая площадь, численность работников, индекс инфляции, стоимость основных средств, собственный капитал), используя функцию КОРРЕЛ.

· Записать формулу зависимости между ними и параметры этой зависимости (функция ЛИНЕЙН).

· Определить формулу и параметры множественной регрессии между результативным показателем и всеми показателями-факторами.

Необходимые данные приведены в таблице результаты проиллюстрировать соответствующими графиками.

Таблица 1

Период исследования, квартал

Фактический размер прибыли от реализации, тыс. грн.

Торговая площадь, кв.м.

Численность работников, чел

Индекс инфляции

Стоимость основных средств, тыс

Собственный капитал, тыс. грн.

1-й квартал

1,2

2-й квартал

1,25

3-й квартал

1,35

4-й квартал

1,4

Указания к работе:

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

Таблица 2

Результативный показатель

Фактор

Коэффициент корреляции

Степень связи

Фактический размер прибыли от реализации

Торговая площадь

Фактический размер прибыли от реализации

Численность работников

Фактический размер прибыли от реализации

Индекс инфляции

Фактический размер прибыли от реализации

Стоимость основных средств

Фактический размер прибыли от реализации

Собственный капитал

Расчёт коэффициентов корреляции осуществляется с помощью статистической функции

КОРРЕЛ (массив 1; массив 2), один из аргументов которой задает область значений зависимой переменной, а другой – область значений независимой переменной. Для каждого показателя фактора рассчитывается коэффициент корреляции. Все коэффициенты корреляции (К) оцениваются по таким критериям:

· К<0,5 – слабая зависимость (фактор можно дальше не исследовать, поскольку он не является влияющим на результативный показатель);

· 0,5<K<0,7 – средняя связь (следует исследовать влияние фактора на результативный показатель далее);

· K>0,7 – связь сильная (фактор обязательно включают в дальнейшее исследование).

 В столбец «Степень связи» вывод от степени зависимости между факторами выполнить с помощью функции ЕСЛИ, задав соответствующие логические выражения для соответствующей ячейки из столбца «Коэффициент корреляции».

2. Для проверки линейности связи между прибыли реализации и величиной собственного капитала, между прибылью от реализации и индексом инфляции, между прибылью от реализации и стоимостью основных средств (отобраны те факторы, которые существенно связаны с результативным показателем) используем статистическую функцию ЛИНЕЙН. Эта функция использует метод наименьших квадратов для вычисления прямой линии и возвращает массив, который её описывает. Уравнение прямой имеет следующий вид:  (в случае множественной регрессии) или  где зависимое значение Y является функцией независимого значения Х. Значения а – это коэффициенты, отвечающие каждой независимой переменной Х, а b – константа. В нашем случае Y и X являются массивами.

Также функция ЛИНЕЙН может возвращать дополнительную регрессионную статистику.

Синтаксис этой функции:

=ЛИНЕЙН(известные значения Y; известные значения Х; конст; статистика). Известные значения Y и Х – это множество значений у и х, известные уже для соотношения ; конст – это логическое значение, указывающее на то, нужно ли, чтобы константа b была бы равной 0 ( при значении 1 – b вычисляется обычным способом; при значении 0 – означает, что b=0 и все значения а подбираются таким образом, чтобы ); статистика - это логическое значение, которое указывает на то, нужно ли выдавать дополнительную статистику по регрессии (при 0 выдаются только коэффициенты а и значение константы b) Дополнительную информацию можете узнать в справочной системе EXCEL.

3. Для того чтобы записать формулу зависимости между результативным показателем и каждым показателем-фактором и параметры этой зависимости дополните таблицу 2 двумя столбцами справа. В заглавии первого из них запишите «Коэффициент а», второго – «Коэффициент b». Напротив факторов, имеющих сильную и среднюю связь, выделите две ячейки из новых столбцов. Далее введите функцию ЛИНЕЙН. При этом помните, что формулу в этом случае необходимо ввести как формулу массива. После выделения диапазона, начинайте вводить формулу. Нажмите клавишу F2, а затем — клавиши CTRL+SHIFT+ENTER. Если формула не будет введена как формула массива, то она выдаст единственное значение. 

4. Чтобы определить формулу и параметры множественной регрессии между результативным показателем и всеми показателями-факторами необходимо выделить соответствующее количество ячеек (на одну больше количества независимых факторов, имеющих существенную связь), и ввести функцию ЛИНЕЙН как было сказано выше. При этом вы получите коэффициенты в следующем порядке (конст =1). Далее под этими значениями вводим значения переменных-факторов . В специально отведенной ячейке можем подсчитать значение результативного показателя – прибыли – при введенных значениях показателей-факторов по формуле: , заменив  и  соответствующими адресами ячеек.

Результаты анализа оформить соответствующим образом.

Познавательные статьи:



Как правильно слушать собеседника

Типичные ошибки при выполнении бросков в баскетболе

Принятие христианства на Руси и его значение

Средства массовой информации США