Методичні вказівки до практичної роботи №13 - Застосування кореляційно-регресійного аналізу в Excel
« Назад
Лабораторна робота № 2Тема роботи: Застосування кореляційно-регресійногоаналізу в табличному процесорі MSExcelдлявизначення параметрів функціональної залежності між результативним фактором та факторами-показниками при обґрунтуванні бізнес-плану створення нової структурної одиниціМета роботи: оволодіти методикою застосування кореляційно-регресійного аналізу в табличному процесорі MSExcelдля визначення параметрів функціональної залежності.
Завдання та порядок виконання роботи1. Визначити тісноту зв'язку між результативним показником - прибуток від реалізації та кожним з показників-факторів (торгова площа, чисельність робітників, індекс інфляції, вартість основних засобів, власний капітал), використовуючи функцію КОРЕЛ. Записати формулу залежності між ними та параметри цієї залежності (функція ЛИНЕЙН). Довести, що визначена залежність не є випадковою (за критерієм Фішера). Визначити формулу та параметри множинної регресії між результативним показником та всіма показниками-факторами. Необхідні дані наведені в таблиці. Результати проілюструвати відповідними графіками.
ПОРЯДОК РОЗВ'ЯЗКУ ЗАВДАНЬ ЛАБОРАТОРНОЇ РОБОТИ1. Створимо на робочому аркуші подану в лабораторній роботі таблицю, значення факторів якої будуть використовуватися в подальшому для проведення кореляційно-регресійного аналізу. 2. Для розрахунку коефіцієнтів кореляції та визначення ступеня зв'язку між результативним показником (фактичний розмір прибутку від реалізації) та окремими факторами (торгова площа, чисельність працівників, індекс інфляції, вартість основних засобів, власний капітал) побудуємо на цьому ж робочому аркуші наступну таблицю (в комірках стовпчика Коефіцієнт кореляції нижче значень цих коефіціентів наведена також функція КОРРЕЛ із списком аргументів для їх розрахунку):
Вигляд цієї таблиці на робочому аркуші в табличному процесорі подано на малюнку. Розрахунок коефіцієнтів кореляції здійснюється за допомогою статистичної функції КОРРЕЛ (масив1; масив2), один з аргументів якої задає область значень залежної змінної, а другий - область значень незалежної змінної. Для кожного показника фактора розраховується коефіцієнт кореляції. Усі коефіцієнти кореляції (К) оцінюються за такими критеріями:
Кореляційний аналіз показав, що: між прибутком від реалізації та торговою площею існує слабка залежність (К=-0.16558551); між прибутком від реалізації та чисельністю працівників - сильний зв'язок (К=0.989475321); між прибутком від реалізації та індексом інфляції - середній зв'язок (К=0.602440569); між прибутком від реалізації та вартістю основних засобів - сильний зв'язок (К=0.862496521); між прибутком від реалізації та власним капіталом - слабка залежність (К=-0.015051516). 3. Для перевірки лінійності зв'язку між прибутком реалізації та чисельністю працівників, між прибутком реалізації та індексом інфляції, між прибутком реалізації та вартістю основних засобів (відібрані ті фактори, які суттєво пов'язані з результативним показником) застосуємо статистичну функцію ЛИНЕЙН. Ця функція використовує метод найменших квадратів для обчислення прямої лінії та повертає масив, який її описує. Рівняння для прямої має такий вигляд: Y=a1X1+a2X2+...+b (у випадку множинної регресії) або Y=aX+b, де залежне значення Y є функцією незалежного значення Х. Значення а - це коефіцієнти, які відповідають кожній незалежній змінній Х, а b - це константа. У нашому випадку Y та X є масивами. Також функція ЛИНЕЙН може повертати додаткову регресійну статистику. Розглянемо синтаксис функції =ЛИНЕЙН (відомі значення Y; відомі значення Х; конст; статистика). Відомі значення Y та Х - це множина значень у та х, які вже відомі для співвідношення Y=aX+b; конст - це логічне значення, яке вказує на те, чи потрібно, щоб константа b була рівна 0 ( значення 1 - b обчислюється звичайним способом; значення 0 означає, що b=0 і значення а підбираються таким чином, щоб Y=aX); статистика - це логічне значення, яке вказує на те, чи потрібно повернути додаткову статистику по регресії (значення 0 - функція ЛИНЕЙН повертає тільки коефіцієнт а та константу b; значення 1 - функція ЛИНЕЙН повертає додаткову регресійну статистику, та масив, що повертається цією функцією буде мати вигляд: {an;an-1;...;a1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid}. Тлумачення величин масиву подано в таблиці:
В наступній таблиці показано, в якому порядку повертається регресійна статистика функцією ЛИНЕЙН на полі робочого аркуша табличного процесора:
Обчислимо значення параметрів лінійної регресії та коефіцієнти рівняння прямої Y=aX+b для масиву незалежної змінної - прибуток від реалізації (Y) та масиву залежної змінної - чисельністю працівників (Х). У нашому випадку функція ЛИНЕЙН буде мати такий синтаксис =ЛИНЕЙН(B2:B21;D2:D21;1;1) та вигляд у вікні Майстра функцій. Після натискання кнопки Готово у комірці B34 з'явиться тільки перша величина з масиву параметрів лінійної регресії - значення коефіцієнта а. Для того, щоб на робочому листі MS Excel з'явилися всі величини зазначеного вище масиву, необхідно за допомогою мишки виділити блок комірок B34:C38, поставити курсор в кінець рядка формул та одночасно натиснути комбінацію клавіш Ctrl+Shift+Enter. Після цих дій у блоці комірок B34:C38 з'являться всі параметри лінійної регресії, значення яких представлені в наступній таблиці:
Виходячи з одержаних параметрів лінійної регресії, запишемо рівняння Y=0,274X+28,71, яке описує лінійний зв'язок між прибутком, одержаним від реалізації, та чисельністю працівників. Коефіцієнт детермінованості становить 0,9771. Для перевірки не випадковості лінійності зв'язку визначимо табличне значення F-критерія та порівняємо його з одержаним за допомогою функції ЛИНЕЙН. Для визначення табличного значення F-критерія застосуємо функцію FРАСПРОБР(ймовірність; ступені вільності1;ступені вільності2), де ступені вільності1 - число змінних, ступені вільності2 - число точок даних, ймовірність - ймовірність, пов'язана з F-розподіленням. У нашому випадку табличне F-критичне, розраховане за допомогою функції =FРАСПРОБР(0.05;1;20), становить 4,35, і воно в 193 рази менше F-критичного, поверненого функцією ЛИНЕЙН. Цей результат свідчить про невипадковість лінійності зв'язку між прибутком та чисельністю працівників. Вікно функції FРАСПРОБР у вікні Майстра функцій. Розрахуємо теоретичні значення Y згідно з функцією Y=0,274X+28,71 та порівняємо їх з експериментальними. Для цього побудуємо на робочому аркуші табличного процесора наступну таблицю:
На робочому аркуші табличного процесора ця таблиця займає блок комірок A39:C59. За даними таблиці побудуємо діаграму у вигляді графіка з зображенням різниці між Y-теоретичним та Y-експериментальним за допомогою Майстра діаграм MS Excel. Алгоритм побудови графіка проілюструємо послідовністю діалогових вікон Майстра діаграм. Побудована діаграма наведена в Додатку А. Для перевірки лінійності зв'язку між прибутком реалізації та індексом інфляції, між прибутком реалізації та вартістю основних засобів, а також його кількісного виразу слід повторити наведене у пункті 3 дослідження, яке стосувалося таких факторів, як прибуток реалізації і чисельність працівників. При цьому необхідно за допомогою статистичної функції ЛИНЕЙН отримати коефіцієнти лінійного рівняння та параметри лінійної регресії, довести невипадковість лінійного зв'язку, а також розрахувати теоретичні значення результативного показника-прибутку реалізації та візуально порівняти їх з експериментальними за допомогою діаграми. 4. Тепер дослідимо лінійність зв'язку між результативним показником- прибутком реалізації та сукупністю факторів: чисельність працівників, індекс інфляції та вартістю основних засобів. А також обрахуємо коефіцієнти лінійного рівняння виду Y=a1X1+a2X2+a3X3+b та параметри лінійної множинної регресії за допомогою статистичної функції ЛИНЕЙН. Обчислимо значення параметрів множинної лінійної регресії та коефіцієнти рівняння прямої Y=a1X1+a2X2+a3X3+b для масиву незалежної змінної - прибутку від реалізації (Y) та масиву залежних змінних - чисельності працівників (Х1), індексу інфляції (Х2), вартості основних засобів (Х3). У нашому випадку функція ЛИНЕЙН буде мати такий синтаксис = ЛИНЕЙН(B2:B21;D2:F21;1;1), вона поверне масив параметрів лінійної множинної регресії, значення яких представлені в наступній таблиці:
Виходячи з одержаних параметрів лінійної регресії, запишемо рівняння Y=0,10X1-38,49Х2+0,09Х3+228,43, яке описує лінійний зв'язок між прибутком, одержаним від реалізації, та факторами: чисельністю працівників, індексом інфляції та вартістю основних засобів. Коефіцієнт детермінованості становить 0,863. Для перевірки невипадковості лінійності зв'язку визначимо табличне значення F-критерія та порівняємо його з одержаним за допомогою функції ЛИНЕЙН. Для визначення табличного значення F-критерія застосуємо функцію FРАСПРОБР з таким синтаксисом: = FРАСПРОБР(0.05;1;20) становить 4,35 і воно приблизно в 8 разів менше F-критичного, поверненого функцією ЛИНЕЙН. Цей результат свідчить про невипадковість лінійності зв'язку між прибутком та факторами: чисельністю працівників, індексом інфляції та вартістю основних засобів. Розрахуємо теоретичні значення Y згідно з функцією Y=0,10X1-38,49Х2+0,09Х3+228,43 та порівняємо їх з експериментальними. Для цього побудуємо на робочому аркуші табличного процесора наступну таблицю:
За даними таблиці побудуємо діаграму у вигляді графіка з зображенням різниці між Yтеоретичним та Yекспериментальним за допомогою Майстра діаграм MS Excel. Побудована діаграма наведена в Додатку Б. 5. Самостійно провести кореляційно-регресійний аналіз зв"язку зміни балансового прибутку підприємства та факторів, що його ймовірно обумовлюють, за даними наступної таблиці:
З повагою ІЦ "KURSOVIKS"! |