Практична робота № 3

Тема: Застосування кореляційно-регресійного аналізу в табличному процесорі MS Excel для визначення параметрів функціональної залежності між результативним фактором та факторами-показниками при обґрунтуванні бізнес-плану створення нової  структурної одиниці.

Мета: оволодіти методикою застосування кореляційно-регресійного аналізу в табличному процесорі MS Excel для визначення параметрів функціональної  залежності

ТЕОРЕТИЧНІ ВІДОМОСТІ

Для визначення ступеня зв’язку між результативними показниками (наприклад, прибутку) та окремими факторами (наприклад, чисельність працівників, індекс інфляції) використовують коефіцієнт кореляції Розрахунок коефіцієнтів кореляції здійснюється за допомогою статистичної функції КОРРЕЛ. Функція має такий синтаксис:

= КОРРЕЛ (масив1; масив2),

один з аргументів якої задає область значень залежної змінної (масив 1), а другий (масив 2) - область значень незалежної змінної. Для кожного показника фактора розраховується коефіцієнт кореляції.

 Усі коефіцієнти кореляції (К) оцінюються за такими критеріями:

• К<0,5 - слабка залежність (фактор можна далі не досліджувати, оскільки він є не впливовим на результативний показник);

• 0,5<K<0,7 - середній зв’язок (варто досліджувати вплив фактора на результативний показник далі);

• K>0,7 - зв’язок сильний (фактор обов’язково включають до подальшого дослідження).

Для перевірки лінійності зв’язку між прибутком та факторами (наприклад, чисельністю працівників), які суттєво пов’язані з результативним показником) застосують статистичну функцію ЛИНЕЙН. Ця функція використовує метод найменших квадратів для обчислення прямої лінії та повертає масив, який її описує. Рівняння для прямої має такий вигляд: Y=a₁X₁+a₂X₂+...+b (у випадку множинної регресії) або Y=aX+b, де залежне значення  Y є функцією незалежного значення Х. Значення а - це коефіцієнти, які відповідають кожній незалежній змінній Х, а b - це константа. У нашому випадку Y та X є масивами. Також функція ЛИНЕЙН може повертати додаткову регресійну статистику. Синтаксис функції:

=ЛИНЕЙН (відомі значення Y; відомі значення Х; конст; статистика).

Для дослідження зв’язку між результативним показником (прибутком) та сукупністю факторів (наприклад, чисельність працівників, індекс інфляції та вартістю основних засобів) також обраховуються коефіцієнти лінійного рівняння виду Y=a₁X₁+a₂X₂+a₃X₃+b та параметри лінійної множинної регресії за допомогою статистичної функції ЛИНЕЙН. Обчислюються значення параметрів множинної лінійної регресії та коефіцієнти рівняння прямої Y=a₁X₁+a₂X₂+a₃X₃+b для масиву незалежної змінної - прибутку від реалізації (Y) та масиви залежних змінних - чисельності працівників (Х₁), індексу інфляції (Х₂), вартості основних засобів (Х₃).

Для перевірки не випадковості лінійності зв’язку визначають табличне значення F-критерія та порівнюють його з одержаним за допомогою функції ЛИНЕЙН. Для визначення табличного значення F-критерія застосвують функцію FРАСПРОБР. Функція має наступний синтаксис:

= FРАСПРОБР (ймовірність; ступені вільності1;ступені вільності2),

де ступені вільності 1 - число змінних, ступені вільності 2 - число точок даних, ймовірність - ймовірність, пов’язана з F-розподіленням.

ЗАВДАННЯ ТА ПОРЯДОК ВИКОНАННЯ РОБОТИ

Визначити тісноту зв’язку між результативним показником - прибуток від реалізації  та кожним з показників-факторів (торгова площа, чисельність робітників, індекс інфляції, вартість основних засобів, власний капітал), використовуючи функцію КОРРЕЛ. Записати формулу залежності між ними та  параметри цієї залежності (функція ЛИНЕЙН).  Довести, що визначена залежність не є  випадковою (за критерієм Фішера). Визначити формулу та параметри множинної регресії між результативним показником та всіма показниками-факторами. Необхідні дані наведені в таблиці 3.3. Результати проілюструвати  відповідними графіками.

Таблиця 3.3

ЗАВДАННЯ ТА ПОРЯДОК ВИКОНАННЯ РОБОТИ

1. Створити на робочому листі подану в лабораторній роботі таблицю, значення факторів якої будуть використовуватися в подальшому для проведення кореляційно-регресійного аналізу. На робочому листі таблиця займе комірки А1:G21.

2. Для розрахунку коефіцієнтів кореляції та визначення ступеня зв’язку між результативним показником (фактичний розмір прибутку від реалізації) та окремими факторами (торгова площа, чисельність працівників, індекс інфляції, вартість основних засобів, власний капітал) побудуйте на цьому ж  робочому листі таблицю 3.4.

Таблиця 3.4.

На робочому листі таблиця займе комірки A24:D29. Розрахувавши коефіцієнт кореляції визначаємо за допомогою функції ЕСЛИ ступінь зв’язку. Кореляційний аналіз показав, що: між прибутком від реалізації та торговою площею існує слабка залежність (К=-0.16558551); між прибутком від реалізації та чисельністю працівників - сильний зв’язок (К=0.989475321); між прибутком від реалізації та індексом інфляції - середній зв’язок (К=0.602440569); між прибутком від реалізації та вартістю основних засобів - сильний зв’язок (К=0.862496521); між прибутком від реалізації та власним капіталом - слабка залежність (К=-0.015051516).

3. Для перевірки лінійності зв’язку між прибутком реалізації та чисельністю працівників, між прибутком реалізації та індексом інфляції, між прибутком реалізації та вартістю основних засобів (відібрані ті фактори, які суттєво пов’язані з результативним показником) застосуйте статистичну функцію ЛИНЕЙН. Обчисліть значення параметрів лінійної регресії та коефіцієнти рівняння прямої Y=aX+b  для масиву незалежної змінної - прибуток від реалізації (Y) та масиву залежної змінної - чисельністю працівників (Х). Функція ЛИНЕЙН буде мати такий синтаксис =ЛИНЕЙН(B2:B21;D2:D21;1;1) та вигляд у вікні Майстра функцій.

Після натискання кнопки Готово у комірці B34 з’явиться тільки перша величина з масиву параметрів лінійної регресії - значення коефіцієнта а. Для того, щоб на робочому листі MS Excel з’явилися всі величини зазначеного вище масиву, необхідно за допомогою мишки виділити блок комірок B34:C38, поставити курсор в кінець рядка формул та одночасно натиснути комбінацію клавіш Ctrl+Shift+Enter. Після цих дій у блоці комірок B34:C38 з’являться всі параметри лінійної регресії, значення яких представлені в  таблиці 3.5.

Таблиця 3.5.

4. Виходячи з одержаних параметрів лінійної регресії, запишіть рівняння Y=0,274X+28,71, яке описує лінійний зв’язок між прибутком, одержаним від реалізації, та чисельністю працівників. Коефіцієнт детермінованості становить 0,9771. Для перевірки не випадковості лінійності зв’язку визначіть табличне значення F-критерія та порівняйте його з одержаним за допомогою функції ЛИНЕЙН. Для визначення табличного значення F-критерію застосуйте функцію  =FРАСПРОБР(0.05;1;20), становить 4,35, і воно в 193 рази менше F-критичного, поверненого функцією ЛИНЕЙН. Цей результат свідчить про не випадковість лінійності зв’язку між прибутком та чисельністю працівників. Вікно функції FРАСПРОБР у вікні Майстра функцій.

5. Розрахуйте теоретичні значення Y згідно з функцією Y=0,274X+28,71 та порівняємо їх з експериментальними. Для цього побудуйте на робочому аркуші табличного процесора таблицю 3.6.

Таблиця 3.6.

На робочому листі табличного процесора ця таблиця займає блок комірок A39:C59.

6. За даними таблиці побудуйте діаграму у вигляді графіка з зображенням різниці між Y-теоретичним та Y-експериментальним за допомогою майстра діаграм MS Excel. Під час побудови графіка вкажіть діапазон даних у діалоговому вікні Майстра діаграм.

7. Для перевірки лінійності зв’язку між прибутком реалізації та індексом інфляції, між прибутком реалізації та вартістю основних засобів, а також його кількісного виразу слід повторити наведене у пункті 3 дослідження,

8. Тепер дослідіть лінійність зв’язку між результативним показником - прибутком реалізації та сукупністю факторів: чисельність працівників, індекс інфляції та вартістю основних засобів. А також обрахуйте коефіцієнти лінійного рівняння виду Y=a₁X₁+a₂X₂+a₃X₃+b та параметри лінійної множинної регресії за допомогою статистичної функції ЛИНЕЙН, яка буде мати такий синтаксис = ЛИНЕЙН(B2:B21;D2:F21;1;1). Вона поверне масив параметрів лінійної множинної регресії, значення яких представлені в таблиці 3.7.

Таблиця 3.7. 

9. Виходячи з одержаних параметрів лінійної регресії, запишіть рівняння Y= а₁₁*X₁ + а₁₂*Х₂ + а₁₃*Х₃ + а₁₄, яке описує лінійний зв’язок між прибутком, одержаним від реалізації, та факторами: чисельністю працівників, індексом інфляції та вартістю основних засобів. Коефіцієнт детермінованості становить 0,863.

10. Для перевірки не випадковості лінійності зв’язку визначіть табличне значення F-критерія та порівняйте його з одержаним за допомогою функції ЛИНЕЙН, для чого застосуйте функцію FРАСПРОБР з таким синтаксисом: = FРАСПРОБР(0.05;1;20). Результат становить 4,35 і воно приблизно в 8 разів менше F-критичного, поверненого функцією ЛИНЕЙН. Цей результат свідчить про не випадковість лінійності зв’язку між прибутком та факторами: чисельністю працівників, індексом інфляції та вартістю основних засобів.

11. Розрахуйте теоретичні значення Y згідно з функцією Y=0,10X₁-38,49Х₂+0,09Х₃+228,43 та порівняйте їх з експериментальними. Для цього побудуйте на робочому аркуші таблицю 3.8.

Таблиця 3.8.

12. За даними таблиці побудуйте діаграму у вигляді графіка з зображенням різниці між Y- теоретичним та Y-експериментальним за допомогою Майстра діаграм MS Excel.

13. Оформити звіт, зробити висновки, відповісти на контрольні питання і виконати контрольні завдання.

Контрольні питання

1. Для чого потрібно проводити кореляційно-регресивний аналіз?

2. Пояснити призначення і аргументи функції FРАСПРОБР.

3. Пояснити призначення і аргументи функції ЛИНЕЙ.

4. Пояснити призначення і аргументи функції КОРРЕЛ.

5. Які ще ви можете назвати статистичні функції MS Excel, які можуть бути використані для аналізу діяльності підприємства?
   

Контрольні завдання

Провести кореляційно-регресійний аналіз зв’язку зміни   балансового прибутку підприємства та факторів, що його ймовірно обумовлюють, за даними таблиці 3.9.                              

Таблиця 3.9.

З повагою ІЦ "KURSOVIKS"!