2018-02-14
711
Розв’язати задачу: визначити тісноту зв’язку між результативним показником - прибуток від реалізації та кожним з показників-факторів (торгова площа, чисельність робітників, індекс інфляції, вартість основних засобів, власний капітал), використовуючи функцію КОРРЕ (статистична функція). Записати формулу залежності між ними та параметри цієї залежності за допомогою функції ЛИНЕЙН ( статистична функція). Необхідні дані наведені в таблиці (Рис. 3). Результати проілюструвати відповідними графіками.
1. Створити на робочому листі подану в лабораторній роботі таблицю, значення факторів якої будуть використовуватися в подальшому для проведення кореляційно-регресійного аналізу. На робочому листі таблиця займе комірки А1:G21.
Рис. 3.
2. Для розрахунку коефіцієнтів кореляції та визначення ступеня зв’язку між результативним показником (фактичний розмір прибутку від реалізації) та окремими факторами (торгова площа, чисельність працівників, індекс інфляції, вартість основних засобів, власний капітал) побудуйте на цьому ж робочому листі таблицю (Рис.4).
|
|
|
Рис. 4
На робочому листі таблиця займе комірки A24:D29. Розрахувавши коефіцієнт кореляції визначаємо за допомогою функції ЕСЛИ ступінь зв’язку.
Усі коефіцієнти кореляції (К) оцінюються за такими критеріями:
· К<0,5 - слабка залежність (фактор можна далі не досліджувати, оскільки він є не впливовим на результативний показник);
· 0,5<K<0,7 - середній зв’язок (варто досліджувати вплив фактора на результативний показник далі);
· K>0,7 - зв’язок сильний (фактор обов’язково включають до подальшого дослідження).
3. Для перевірки лінійності зв’язку між прибутком реалізації та чисельністю працівників, між прибутком реалізації та індексом інфляції, між прибутком реалізації та вартістю основних засобів (відібрані ті фактори, які суттєво пов’язані з результативним показником) застосуйте статистичну функцію = ЛИНЕЙН (B2:B21;D2:D21;1;1), вигляд у вікні Майстра функцій:
Рис. 5.
Після натискання кнопки Готово у комірці B34 з’явиться тільки перша величина з масиву параметрів лінійної регресії - значення коефіцієнта а. Для того, щоб на робочому листі MS Excel з’явилися всі величини зазначеного вище масиву, необхідно за допомогою мишки виділити блок комірок B34:C38, поставити курсор в кінець рядка формул та одночасно натиснути комбінацію клавіш Ctrl+Shift+Enter. Після цих дій у блоці комірок B34:C38 з’являться всі параметри лінійної регресії, значення яких представлені в таблиці 3.
Таблиця 3.
| 0.274105055 | 28.70879776 |
| 0.00944821 | 18.18253339 |
| 0.979061411 | 37.1013424 |
| 841.6567677 | 18 |
| 1158548.627 | 24777.17294 |
4. Виходячи з одержаних параметрів лінійної регресії, запишіть рівняння Y=0,274X+28,71, яке описує лінійний зв’язок між прибутком, одержаним від реалізації, та чисельністю працівників.
|
|
|
5. Розрахуйте теоретичні значення Y згідно з функцією Y=0,274X+28,71 та порівняємо їх з експериментальними. Для цього побудуйте на робочому аркуші табличного процесора таблицю 4.
Таблиця 4.
| Y-експериментальне (Фактичний розмір прибутку від реалізації) | Х (Чисельність працівників) | Y-теоретичне |
| 325 | 1137,5 | |
| 177 | 619,5 | |
| 1084 | 3794 | |
| 691 | 2418,5 | |
| 233 | 815,5 | |
| 670 | 2345 | |
| 780 | 2730 | |
| 500 | 1750 | |
| 920 | 3220 | |
| 670 | 2500 | |
| 419 | 1000 | |
| 320 | 1120 | |
| 654 | 2289 | |
| 320 | 900 | |
| 204 | 800 | |
| 540 | 1890 | |
| 460 | 1620 | |
| 365 | 1100 | |
| 230 | 900 | |
| 400 | 1300 |
На робочому листі табличного процесора ця таблиця займає блок комірок A39:C59.
6. За даними таблиці побудуйте діаграму у вигляді графіка з зображенням різниці між Y-теоретичним та Y-експериментальним за допомогою майстра діаграм MS Excel.
7. Для перевірки лінійності зв’язку між прибутком реалізації та індексом інфляції, між прибутком реалізації та вартістю основних засобів, слід повторити наведене дослідження у пункті 3, 4, 5. Аналогічно, побудуйте таблиці розрахунку теоретичних значеньрозмірівприбутку, виходячи з одержаних параметрів лінійної регресії. Побудуйте за результатами досліджень діаграми.
8. Тепер дослідіть лінійність зв’язку між результативним показником - прибутком реалізації та сукупністю факторів: чисельність працівників, індекс інфляції та вартістю основних засобів. А також обрахуйте коефіцієнти лінійного рівняння виду Y=a1X1+a2X2+a3X3+b та параметри лінійної множинної регресії за допомогою статистичної функції ЛИНЕЙН, яка буде мати такий синтаксис = ЛИНЕЙН (B2:B21;D2:F21;1;1). Вона поверне масив параметрів лінійної множинної регресії, значення яких представлені в таблиці 5.
Таблиця 5.
| а11 | а12 | а13 | а14 |
| а21 | а22 | а23 | а24 |
| а31 | а32 | а33 | а34 |
| а41 | а42 | а43 | а44 |
| а51 | а52 | а53 | а54 |
9. Виходячи з одержаних параметрів лінійної регресії, запишіть рівняння Y= а11*X1- а12*Х2+ а13*Х3+ а14, яке описує лінійний зв’язок між прибутком, одержаним від реалізації, та факторами: чисельністю працівників, індексом інфляції та вартістю основних засобів. Розрахуйте теоретичні значення Y згідно з функцією Y= а11*X1- а12*Х2+ а13*Х3+ а14 та порівняйте їх з експериментальними. Для цього побудуйте на робочому аркуші таблицю 6.
Таблиця 6.
| Y- експери-ментальне (Фактичний розмір прибутку) | Х1 (Чисельність працівників) | X2 (Індекс інфляції) | X3 (Вартість основних засобів) | Y- теоретичне |
| 325 | 1137,5 | 1,625 | 650 | |
| 177 | 619,5 | 1,25 | 885 | |
| 1084 | 3794 | 5,42 | 4336 | |
| 691 | 2418,5 | 1,4 | 1382 | |
| 233 | 815,5 | 1,165 | 1165 | |
| 670 | 2345 | 3,35 | 4020 | |
| 780 | 2730 | 1,54 | 3900 | |
| 500 | 1750 | 2,5 | 1000 | |
| 920 | 3220 | 1,69 | 3680 | |
| 670 | 2500 | 1,75 | 2680 | |
| 419 | 1000 | 2,095 | 838 | |
| 320 | 1120 | 1,85 | 1280 | |
| 654 | 2289 | 3,27 | 2616 | |
| 320 | 900 | 1,6 | 640 | |
| 204 | 800 | 1,02 | 1020 | |
| 540 | 1890 | 2,05 | 1080 | |
| 460 | 1620 | 2,3 | 1840 | |
| 365 | 1100 | 2,15 | 730 | |
| 230 | 900 | 2,25 | 460 | |
| 400 | 1300 | 2 | 1600 |
12. За даними таблиці побудуйте діаграму у вигляді графіка з зображенням різниці між Y- теоретичним та Y-експериментальним за допомогою Майстра діаграм MS Excel.
13. Зберегти результати на сервері. Виконати контрольне завдання.
Контрольне завдання
Провести кореляційно-регресійний аналіз зв’язку зміни балансового прибутку підприємства та факторів, що його ймовірно обумовлюють, за даними таблиці 7.
Таблиця 7.
| Період дослідже-ння варіації основних засобів та факторів | Фактичний розмір балансо-вого прибутку | Торгова площа | Середрьо-спискова чисельність | Обігові активи | Власний капітал |
| 01.05.98 | 10,20 | 705,00 | 133 | 504,60 | 622,80 |
| 01.06.98 | 10,70 | 705,00 | 133 | 510,20 | 623,00 |
| 01.07.98 | 11, 40 | 705,00 | 133 | 514,40 | 623,40 |
| 01.08.98 | 16,40 | 740,00 | 136 | 469,30 | 634,90 |
| 01.09.98 | 16,20 | 740,00 | 136 | 476,10 | 658,20 |
| 01.10.98 | 15,10 | 740,00 | 135 | 474,00 | 665,00 |
| 01.11.98 | 11,45 | 720,00 | 129 | 397,80 | 670,00 |
| 01.12.98 | 11,35 | 720,00 | 129 | 396,20 | 679,10 |
| 01.01.99 | 11,20 | 720,00 | 129 | 396,00 | 673,00 |
| 01.02.99 | 11,10 | 720,00 | 131 | 509,00 | 670,90 |
| 01.03.99 | 10,60 | 720,00 | 132 | 509,80 | 670,00 |
| 01.04.99 | 10,30 | 720,00 | 132 | 509,40 | 672,20 |
Лабораторна робота № 3.3
|
|
|
Тема: Імітаційне та оптимізаційне моделювання впливу факторів на зміну чистого прибутку торгівельного підприємства при різних
варіантах сценаріїв засобами Підбір параметра та Пошук рішення табличного процесора MS Excel
Мета: Оволодіти методикою проведення імітаційного та оптимізаційного моделювання засобами табличного процесора MS Excel
Зауваження: В роботі застосовується команда Підбір параметрів, яка викликається через пункт меню Данные
