Практична робота №3 Імітаційне та оптимізаційне моделювання
« Назад
Практична робота №3Тема: Імітаційне та оптимізаційне моделювання впливу факторів на зміну чистого прибутку торгівельного підприємства при різних варіантах сценаріїв засобами Подбор параметра та Поиск решения табличного процесора MS Excel. Мета: Оволодіти методикою проведення імітаційного та оптимізаційного моделювання засобами табличного процесора MS Excel.
Порядок виконання роботи
Завдання. Змоделювати вплив окремих факторів (ціни реалізації, обсягу товарообороту, середньої торгівельної надбавка та витрат обігу) на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис.грн. Використати засіб "Підбір параметра". Необхідні дані (значення економічних показників підприємства) наведені в таблиці 3.1. Побудувати результативну таблицю та розробити три варіанти сценарію: оптимістичний, песимістичний та реалістичний, кожен з яких зображує реальні зміни показників, які впливають на прибуток підприємства. Таблиця 3.1.
Хід виконання 1. Спочатку проаналізуйте вплив ціни реалізації на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. Змінити назву робочого аркуша з "Лист1" на "Імітаційне моделювання", а назву робочого аркуша "Лист2" на "Оптимізаційне моделювання". На робочому аркуші "Імітаційне моделювання" побудувати таблицю (в діапазоні А6:В18) значення та формули для розрахунків взяти з таблиці 3.1. Таблиці надати заголовок: "Вплив ціни реалізації на зміну чистого прибутку торгівельного підприємства". В результаті отримаємо таблицю 3.2. Таблиця 3.2.
2. Встановити курсор в комірці В8 і змінити ціну реалізації, за допомогою команди Подбор параметра встановивши значення чистого прибутку 60 тис.грн встановити в комірці В18. У діалоговому вікні команди, слід зазначити абсолютні адреси комірок та значення, які представлені на Рис. 3.1. Рис. 3.1 - Подбор параметра На малюнку 3.2. показано як викликати вікно Подбор параметра.
Рис. 3.2 - Як викликати вікно Подбор параметра Після натискання в кнопки ОК, програма Подбор параметра, змінить значення ціни реалізації з 5,00 грн. на 5,18 грн. в комірці В8 для встановлення значення чистого прибутку в 60 тис. грн. та запише в таблицю результати своєї роботи (таблиця 3.3. та рис 3.3.) Таблиця 3.3. Вплив ціни реалізації на зміну чистого прибутку торговельного підприємства
Рис. 3.3 - Вплив ціни реалізації на зміну чистого прибутку торговельного підприємства 3. Далі проводимо дослідження, яким чином впливає обсяг товарообороту на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. Побудуйте таблицю початкових значень економічних показників, аналогічну, таблиці 3.1, починаючи з 23 рядка. При створенні таблиці використовувалися числові дані та формули (у комірці В28 записана формула =В26*В27/100; у комірці В26 - значення 5000; у комірці В31 - формула =В32*В26/100; у комірці В33 - формула =В28-В29; у комірці В35 - формула =В33*(100-В34)/100). Також зверху таблиці ввести заголовок: "Вплив обсягу товарообороту на зміну чистого прибутку торгівельного підприємства". Результати розрахунків приведені в таблиці 3.4. Таблиця 3.4. Вплив обсягу товарообороту на зміну чистого прибутку торговельного підприємства
4. Встановити курсор в комірці В26 і активізувати команду Подбор параметра Встановити курсор в комірці В35, значення чистого прибутку 60 тис.грн, відповідно змінивши обсяг товарообороту (комірка В26). У діалоговому вікні зазначаються абсолютні адреси комірок та значення подані на рис. 3.4. Рис. 3.4 – Підбір параметра Після натискання кнопки ОК програма Подбор параметра змінить значення обсягу товарообороту з 5000 грн. на 5181 грн. в комірці В8 для встановлення значення чистого прибутку в 60 тис. грн. та запише в таблицю результати своєї роботи (таблиця 3.5.). Таблиця 3.5 Вплив обсягу товарообороту на зміну чистого прибутку торговельного підприємства
5. Далі необхідно дослідити, яким чином впливає середня торговельна надбавка на зміну чистого прибутку торговельного підприємства від 42 до 60 тис.грн. Побудуйте таблицю початкових значень економічних показників, аналогічну першій таблиці, починаючи з 40 рядка. При створенні таблиці використовуйте числові дані та формули (у комірці В43 записана формула =В41*В42; у комірці В45 - формула =В43*В44/100; у комірці В46 - формула =В47+В48; у комірці В48 - формула =В49*В43/100; у комірці В50 - формула =В45-В46; у комірці В52 - формула =В50*(100-В51)/100). Таблиці надати заголовок: "Вплив середньої торгівельної надбавки на зміну чистого прибутку торговельного підприємства". Результати розрахунків наведені в таблиці 3.6. Таблиця 3.6 Вплив середньої торгівельної надбавки на зміну чистого прибутку торговельного підприємства
6. Необхідно встановити курсор на комірку В44 та викликати команду Подбор параметра, для того щоб встановити в комірці В52 значення чистого прибутку 60 тис. грн, відповідно змінивши значення середньої торговельної надбавки (адреса комірки В44). У діалоговому вікні слід зазначити абсолютні адреси комірок та значення показані на рис. 3.5. Рис. 3.5 - Програма Подбор параметра Після натискання кнопки ОК, програма Подбор параметра змінить значення середньої торгівельної надбавки з 25,00% на 25,51 % в комірці В52 для встановлення значення чистого прибутку в 60 тис. грн. та запише в таблицю 3.7. результати своєї роботи. Таблиця 3.7. Вплив середньої торгівельної надбавки на зміну чистого прибутку торговельного підприємства
7. Далі проводимо дослідження, яким чином впливають витрати обігу на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. Для цього побудуйте таблицю початкових значень економічних показників, аналогічну таблиці 3.1, починаючи з 57 рядка. При створенні таблиці використовувалися числові дані та формули (у комірці В60 записана формула =В58*В59; у комірці В62 - формула =В60*В61/100; у комірці В63 - значення 1190; у комірці В65 - формула =В66*В60/100; у комірці В67 - формула =В62-В63; у комірці В69 - формула =В67*(100-В68)/100). Також зверху таблиці ввести заголовок: "Вплив витрат обігу на зміну чистого прибутку торгівельного підприємства". Результати розрахунків представлені в таблиці 3.8. Таблиця 3.8. Вплив витрат обігу на зміну чистого прибутку торговельного підприємства
8. Встановити курсор на комірку В63 та викликати команду Подбор параметра для того щоб встановити в комірці В69 значення чистого прибутку 60 тис.грн., відповідно змінивши значення середньої торгівельної надбавки (адреса комірки В63. У діалоговому вікні слід зазначити абсолютні адреси комірок та значення показані на Рис. 3.6. Рис. 3.6 - Вплив витрат обігу на зміну чистого прибутку торговельного підприємства Після натискання ОК програма Подбор параметра змінить значення витрат обігу з 1190 на 1164 в комірці В63 для встановлення значення чистого прибутку в 60 тис.грн. та запише в таблицю 3.9 результати своєї роботи. Таблиця 3.9. Вплив витрат обігу на зміну чистого прибутку торговельного підприємства
9. Враховуючи попередні розрахунки та результати програми Подбор параметра, можна зробити висновок, що для збільшення чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. необхідно вибрати одне з наступних рішень: - збільшити ціну реалізації з 5,00 грн. до 5,18 грн.; - збільшити обсяг товарообороту з 5000 до 5181 тис. грн.; - збільшити середню торговельну надбавку на 0,51 % (25,51-25,00=0,51); - зменшити витрати обігу з 1190 до 1164. 10. Для дослідження впливу всіх факторів одночасно (ціна реалізації, обсяг товарообігу, середня торгівельна надбавка, витрати обігу) на зміну чистого прибутку підприємства від 42 до 60 тис. грн. проведемо оптимізаційне моделювання за допомогою програми Поиск решения (пункт меню Данные). Перейти на робочий аркуш з назвою "Оптимізаційне моделювання" та створити таблицю анологічну таблиці 3.1.(рис. 3.7.). Ввести заголовок: "Вплив множини факторів на зміну чистого прибутку торговельного підприємства".
Рис. 3.7 - Вплив множини факторів на зміну чистого прибутку торговельного підприємства 11. Завантажимо програму Поиск решения. В діалогове вікно програми (Рис. 3.8.) ввести цільову функцію - чистий прибуток (комірка $В$18); керовані змінні - середня ціна реалізації (комірка $В$8), обсяг товарообороту (комірка $В$9), середня торгівельна надбавка (комірка $В$10), витрати обігу (комірка $В$12); поставити умови: рівень змінних витрат £ 10.8 ($В$15£10.8), прибуток від реалізації ³ 60 ($В$16³60).
Рис. 3.8 – Параметри пошуку рішень Для додавання обмежень необхідно натиснути кнопку Добавить, та у вікні конструктора обмежень (рис.3.9) ввести відповідні абсолютні адреси комірок, значення, оператори порівняння. Це вікно має вигляд показаний на Рис. 3.9. Рис. 3.9 – Додавання обмежень Програма Поиск решения буде виконувати оптимізацію при наступних параметрах (їх можна переглянути або змінити, натиснувши кнопку Параметри). На екрані з’явиться вікно Параметры поиска решения (рис.3.10).
Рис. 3.10 - Вікно Параметры поиска решения Після заповнення діалогового вікна Поиск решения слід натиснути кнопку Найти решение. Через декілька секунд з’явиться вікно Результаты поиска решения (рис. 3.11.), в якому вибрати опції Отчены, та натиснути кнопку ОК. Рис. 3.11 - Вікно Результаты поиска решения Після зазначених операцій значення факторів - керованих змінних - в таблицях дещо змінилося. Так, середня торгівельна надбавка та середня ціна реалізації залишилися незмінними (25 % та 5 грн, відповідно); а, обсяг товарообороту збільшився до 5102,86 та змінні витрати обігу зменшилися до 551,1. Результати оптимізації представлені в таблиці 3.10. Таблиця 3.10. Вплив множини факторів (ціна реалізації, обсяг товарообороту, середня торгівельна надбавка, витрати обігу) на зміну чистого прибутку торговельного підприємства
Програма Поиск решения видає результати проведеної оптимізації у вигляді трьох звітів: звіт по результатах (рис. 3.12.), звіт по стійкості (рис. 3.13.) та звіт по межах (рис. 3.14.), які автоматично розташовуються на окремих робочих аркушах. Видача звітів відбувається при їх замовленні у вікні Результаты поиска решения.
Рис. 3.12 – Звіт по результатам
Рис. 3.13 – Звіт по стійкості
Рис. 3.14 – Звіт по межах 12. Самостійно провести імітаційне та оптимізаційне моделювання впливу факторів (ціна реалізації, обсяг товарообігу, середня торгівельна надбавка, витрати обігу) на зміну чистого прибутку торговельного підприємства від 42 до 80 тис. грн., а також при ставці податку на прибуток відповідно нормам поточного року. Оформити звіт, зробити висновки і відповісти на контрольні питання. Контрольні питання1. Що таке моделювання? 2. Що таке модель? Вкажіть види моделювання? 3. В чому полягає суть імітаційного і оптимізаційного моделювання? 4. Які засоби MS Excel дають можливість проводити моделювання? 5. Пояснити принципи роботи команди Подбор параметра. 6. Пояснити принципи роботи команди Поиск решения. З повагою ІЦ "KURSOVIKS"! |
