Тема 1.6. Економіко-мате­матичні методи в економічному аналізі

4.3. Кореляційно-регресійний аналіз

Завдання 6. На основі наведених даних (табл. 4.3.1) визначити залежність чисельності персоналу бухгалтерії від товарообороту, кількості працівників, які працюють на підприємстві та кількості матеріально відповідальних осіб.

1. Визначити теоретичну лінію зв'язку і параметри зв’язку (а₀) і (a₁) при парній кореляції між чисельністю бухгалтерів і:

а) товарооборотом (X1, табл. 4.3.2);

б) кількістю робітників підприємства (Х2, табл. 4.3.3);

в) кількістю матеріально відповідальних осіб (Х3, табл. 4.3.4).

2. Розрахувати показники тісноти зв'язку при прямолінійній і логарифмічній формах залежності. Показники розрахувати окремо для визначення тісноти зв'язку між чисельністю персоналу у бухгалтерії і:

а) товарооборотом (XI, табл. 4.3.2);

б) кількістю робітників підприємства (Х2, табл. 4.3.3);

в) кількістю матеріально відповідальних осіб (ХЗ, табл. 4.3.4).

4. Проаналізувати отримані дані.

3. Побудувати окремо графіки при прямолінійній, логарифмічній формах залежності чисельності персоналу у бухгалтерії від таких чинників:

а) товарообороту (XI, табл. 4.3.2);

б) кількості робітників підприємства (Х2, табл. 4.3.3);

в) кількості матеріально відповідальних осіб (ХЗ, табл. 4.3.4).

Методика виконання

При визначенні теоретичної лінії зв’язку слід здійснити наступну послідовність операцій. Для виконання роботи використовуємо Microsoft Excel.

1.1. Побудувати макет таблиці (табл. 4.3.3, 4.3.4, 4.3.5).

1.2. Вносимо дані до електронної таблиці. Починаємо заповнення з комірок А4-D28, послідовно вводячи необхідні дані.

1.2. Визначаємо теоретичну лінію зв'язку і параметри зв'язку (а₀) і (а₁) при парній кореляції між чисельністю бухгалтерів і:

а) товарооборотом (XI, табл. 4.3.2);

б) кількістю робітників підприємства (Х2, табл. 4.3.3);

в) кількістю матеріально відповідальних осіб (ХЗ, табл. 4.3.4).

Рис. 4.3.1. Макет таблиці «Розрахунок прямолінійної форми залежності між чисельністю персоналу бухгалтерії,Y і товарооборотом, Х1»

При цьому використовуємо два теоретичні рівняння зв'язку:

а

;

) прямолінійне – [4.3.1]

б

.

) логарифмічне – [4.3.2]

Під час розрахунку теоретичної лінії зв'язку використовувати дані табл. 4.3.1. При цьому будуються таблиці єдиної форми, в якій змінюються чинник і форма зв'язку (форми – зразки додаються: табл. 4.3.3, 4.3.4, 4.3.5, 4.3.6, 4.3.7, 4.3.8).

Розраховуємо стовпець Е. Для цього в комірку Е4 вводимо формулу =D4^2 і копіюємо цю формулу в блок Е5:Е28. Для розрахунку значень стовпця F у комірку F4 вводимо формулу =С4*D4 і копіюємо цю формулу в блок F5:F28. Підраховуємо суми показників по стовпчиках. У комірку С29 вводимо формулу =СУММ(С4:С28) і копіюємо її в блок D29:F29.

Якщо рівняння регресії для прямолінійної залежності пред­ставити за допомогою системи рівнянь [4.3.3], то з нього можна визначити параметри регресії а₁ (коефіцієнт регресії) і а₀.

[4.3.3]

У комірці В30 розраховуємо коефіцієнт регресії – а₁ за допомогою формули [4.3.4]. Для цього вводимо формулу =(25F29-C29D29)/(25E29-D29^2)

. [4.3.4]

Для розрахунку а₀ у комірку В31 вводимо формулу [4.3.5]: =(C29–B30D29)/25.

.

.

[4.3.5]

«Далі» розраховуємо теоретичну чисельність персоналу бухгалтерії. У комірку G4 вводимо формулу [4.3.1]: =$B$31+$B$30D4 і копіюємо цю формулу в блок G5:G28.

Для підрахунку тісноти зв’язку між показниками необхідно розрахувати стовпці Н, I, J. У стовпчику Н розраховується середня чисельність персоналу бухгалтерії. Для цього в комірку Н4 вводимо формулу [4.3.6]: =$C$29/25 і копіюємо її до блоку Н5:Н28.

[4.3.6]

Для розрахунку стовпців І вводимо в комірку І4 формулу =(C4–H4)^2 і копіюємо її у блок І5:І28. У комірку J4 вводимо формулу =(C4–G4)^2 і копіюємо формулу в комірки J5:J28.

Потрібно підрахувати суму показників. Для цього копіюємо формулу з комірки F29 і вставляємо її до комірок G29:J29.

Підраховуємо тісноту зв’язку між показниками за допомогою коефіцієнта кореляції для прямолінійного зв’язку [4.3.7].

.

[4.3.7]

У комірку В32 вводимо формулу =КОРЕНЬ((I29/25-J29/25)/(I29/25)).

Рис. 4.3.2. Макет таблиці «Розрахунок прямолінійної форми залежності між чисельністю персоналу бухгалтерії,Y і чисельністю робітників підприємства, Х2»

• Для оцінки коефіцієнта кореляції використовується таблиця Чеддока.

Значення коефіцієнта кореляції	Тіснота зв’язку
0,1–0,3	Слабка
0,31–0,5	Помірна
0,51–0,7	Помітна
0,71–0,9	Висока
0,91–0,99	Дуже висока

За результатами проведених розрахунків робимо висновки.

Ті ж самі розрахунки проводимо для прямолінійної форми залежності чисельності персоналу бухгалтерії від кількості робітників підприємства (рис 4.3.2) і кількості матеріально відповідальних осіб (рис 4.3.3).

Рис. 4.3.3. Макет таблиці «Розрахунок прямолінійної форми залежності між чисельністю персоналу бухгалтерії,Y і кількістю матеріально відповідальних осіб, Х3»

При побудові графіку залежності чисельності персоналу бухгалтерії від товарообороту необхідно виділити блок {D4:D28;C4:C28;G4:H28} і натиснути кнопку «Майстер діаграм» на панелі інструментів Microsoft Excel. Обрати тип діаграми – крапкова діаграма і натиснути «Далі». У вікні, що з’явилось (рис. 4.3.4), ввести ім’я рядків і натиснути «Далі».

Рис. 4.3.4. Введення даних для побудови діаграми

У параметрах діаграми ввести її назву і назву осей координат і натиснути «Далі». Обрати розміщення діаграми на окремому аркуші та натиснути «Готово». Після цього на окремому аркуші з’явиться діаграма (рис. 4.3.5).

Для розрахунку теоретичної лінії зв’язку для логарифмічної залежності [4.3.2] між чисельністю персоналу бухгалтерії і товаро­оборотом, кількістю робітників підприємства і кількістю матеріально відповідальних осіб використовуємо макети таблиць 4.3.6, 4.3.7 і 4.3.7 відповідно.

Заповнюємо блок А4:D28 даними з умови завдання відповідно до варіанта (рис. 4.3.6). Розраховуємо стовпець Е. Для цього в комірку Е4 вводимо формулу =LN(D4) і копіюємо цю формулу в блок Е5:Е28. Для розрахунку значень стовпця F у комірку F4 вводимо формулу =Е4^2 і копіюємо цю формулу в блок F5:F28. У комірку G4 вводимо формулу =С4Е4 і копіюємо її до комірок G5:G28. Підраховуємо суми показників по стовпчиках. У комірку С29 вводимо формулу =СУММ(С4:С28) і копіюємо її у блок D29:G29.

Рис. 4.3.5. Діаграма прямолінійної залежності між чисельністю персоналу бухгалтерії і товарооборотом

Якщо рівняння регресії для логарифмічної залежності представити за допомогою системи рівнянь [4.3.8], то з нього можна визначити параметри регресії а₁ і а₀.

. [4.3.8]

У комірці В30 розраховуємо параметр регресії – а₁ за допомогою формули [4.3.9]. Для цього вводимо формулу =(25G29-C29E29)/(25F29–E29^2).

. [4.3.9]

Для розрахунку а₀ в комірку В31 вводимо формулу [4.3.10]: =(C29–B30E29)/25.

. [4.3.10]

Рис. 4.3.6. Макет таблиці «Розрахунок логарифмічної форми залежності між чисельністю персоналу бухгалтерії,Y і товарооборотом, Х1»

«Далі» розраховуємо теоретичну чисельність персоналу бухгалтерії. У комірку Н4 вводимо формулу [4.3.2]: =$B$31+$B$30E4 і копіюємо цю формулу в блок Н5:Н28.

Для підрахунку тісноти зв’язку між показниками необхідно розрахувати стовпці I, J, K. У стовпчику І розраховується середня чисельність персоналу бухгалтерії. Для цього в комірку І4 вводимо формулу [4.3.11]: =$C$29/25 і копіюємо її до блоку І5:І28.

. [4.3.11]

Рис. 4.3.7. Макет таблиці «Розрахунок логарифмічної форми залежності між чисельністю персоналу бухгалтерії,Y і чисельністю робітників підприємства, Х2»

Для розрахунку стовпців J вводимо в комірку J4 формулу =(C4–I4)^2 і копіюємо її в блок J5:J28. У комірку K4 вводимо формулу =(C4-H4)^2 і копіюємо формулу в комірки К5:К28.

Необхідно підрахувати суму показників. Для цього копіюємо формулу з комірки G29 і вставляємо її до комірок І29:К29.

Підраховуємо тісноту зв’язку між показниками за допомогою коефіцієнта кореляції для криволінійного зв’язку [4.3.12].

. [4.3.12]

У комірку В32 вводимо формулу =КОРЕНЬ((К29/25)/(J29/25))

За результатами проведених розрахунків робимо висновки.

Р ис. 4.3.8. Макет таблиці «Розрахунок логарифмічної форми залежності між чисельністю персоналу бухгалтерії,Y і кількістю матеріально відповідальних осіб, Х3»

Рис 4.3.7. Вибір типу діаграми

Ті ж самі розрахунки проводимо для логарифмічної форми залежності чисельності персоналу бухгалтерії від кількості робітників підприємства (рис 4.3.5) і кількості матеріально відповідальних осіб (рис 4.3.6).

При побудові графіку залежності чисельності персоналу бухгалтерії від товарообороту необхідно виділити блок {D4:D28;C4:C28;Н4:І28} і натиснути кнопку «Майстер діаграм» на панелі інструментів Microsoft Excel. Обрати тип діаграми – крапкова діаграма і натиснути «Далі» (рис 4.3.7).

У вікні, що з’явилось ввести ім’я рядків і натиснути «Далі». У параметрах діаграми ввести її назву і назву осей координат і натиснути «Далі». Обрати розміщення діаграми на окремому аркуші і натиснути «Готово». Після цього на окремому аркуші з’явиться діаграма (рис. 4.3.8).

Рис. 4.3.8. Діаграма логарифмічної залежності між чисельністю персоналу бухгалтерії і товарооборотом

Таблиця 4.3.1

Чисельність персоналу бухгалтерії, товарооборот, кількість робітників підприємства і кількість матеріально відповідальних осіб

№ пор.	Номер підпри­ємства	Чисельність персоналу бухгалтерії, Y	Товаро­оборот, тис. грн, XI	Кількість персоналу, Х2	Кількість матеріально відповідаль-них осіб, чол., ХЗ
1	01	4	8,2	54	2
2	02	6	8,2	56	3
3	03	6	9,4	105	4
4	04	8	9,5	106	4
5	05	7	12,4	109	5
6	06	17	16,2	114	5
7	07	8	16,8	152	9
8	08	6	15,9	153	9
9	09	7	16,4	170	12
10	10	12	17,2	189	14
11	11	20	17,4	240	15
12	12	21	23,7	250	15
13	13	19	24,2	270	15
14	14	20	26,4	305	19
15	15	28	31,0	306	22
16	16	32	31,8	380	22
17	17	31	39,0	420	23
18	18	33	38,2	430	24
19	19	40	56,4	480	24
20	20	42	64,2	495	24
21	21	40	86,3	504	29
22	22	36	86,4	540	30
23	23	39	92,2	610	33
24	24	42	98,4	615	39
25	25	48	98,6	630	39

Таблиця 4.3.2