Практична робота №11 на тему Застосування кореляційно-регресійного аналізу в табличному процесорі MS Excel, НУДПСУ

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

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

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

Порядок виконання роботи

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

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

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

На робочому листі таблиця займе комірки A24:D29. Розрахувавши коефіцієнт кореляції визначаємо за допомогою функції ЕСЛИ ступінь зв’язку.

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

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

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

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

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

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

Таблиця 3.

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

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

Таблиця 4.

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

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

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

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

Таблиця 5. 

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

Таблиця 6.

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

13. Зберегти результати на сервері. Виконати контрольне завдання.

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

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

       Таблиця 7.

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