Практична робота №17 на тему Імітаційне та оптимізаційне моделювання впливу факторів на зміну чистого прибутку підприємства

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

Тема: Імітаційне та оптимізаційне моделювання впливу факторів на зміну чистого прибутку торгівельного підприємства при різних варіантах сценаріїв засобами  Підбір параметра та Пошук рішення табличного процесора MS Excel

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

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

Розв’язати задачу:змоделювати вплив окремих факторів (ціни реалізації, обсягу товарообороту, середньої торгівельної надбавка та витрат обігу) на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис.грн. Використати засіб Підбір параметра. Необхідні дані (значення економічних показників підприємства) наведені в таблиці 8. Побудувати результативну таблицю та розробити три варіанти сценарію: оптимістичний, песимістичний та реалістичний, кожен з яких зображує реальні зміни показників, які впливають на прибуток підприємства.

Таблиця 8.

Спочатку проаналізуйте вплив ціни реалізації на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. Змінити назву робочого аркуша з "Лист1" на "Імітаційне моделювання", а назву робочого аркуша "Лист2"   на "Оптимізаційне моделювання".
На робочому аркуші Імітаційне моделювання побудувати  таблицю показану на Рис. 6, значення та формули для розрахунків взяти з таблиці 8.

Таблиці надати заголовок: Вплив ціни реалізації на зміну чистого прибутку торгівельного підприємства.

Встановити курсор в комірці В8 і змінити ціну реалізації, за допомогою команди Підбір параметра встановивши значення чистого прибутку 60 тис.грн встановити в комірці В18. У діалоговому вікні команди, слід зазначити абсолютні адреси комірок та значення.

Після натискання в кнопки ОК, програма Підбір параметра змінить значення ціни реалізації з 5,00 грн. на 5,18 грн. в комірці В8 для встановлення значення чистого прибутку в 60 тис. грн. та запише в таблицю результати своєї роботи (таблиця 9).

Таблиця 9.

Вплив ціни реалізації на зміну чистого прибутку торговельного підприємства

Далі проводимо дослідження, яким чином впливає обсяг товарообороту на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. Побудуйте таблицю початкових значень економічних показників, аналогічну, таблиці 8, починаючи з 23 рядка. При створенні таблиці використовувалися числові дані та формули (у  комірці В28 записана формула =В26*В27/100; у комірці В26 - значення 5000; у комірці В31 - формула =В32*В26/100; у комірці В33 - формула =В28-В29; у комірці В35 - формула =В33*(100-В34)/100). Також зверху таблиці ввести заголовок: Вплив обсягу товарообороту на зміну чистого прибутку торгівельного підприємства. Результати розрахунків приведені в таблиці 10.

Таблиця 10.

Вплив обсягу товарообороту на зміну чистого прибутку торговельного підприємства

Встановити курсор в комірці В26 і активізувати команду Підбір параметра. Встановити курсор в комірці В35, значення чистого прибутку 60 тис.грн, відповідно змінивши обсяг товарообороту (комірка В26). У діалоговому вікні зазначаються абсолютні адреси комірок та значення.

Після натискання кнопки ОК програма Підбір параметра змінить значення обсягу товарообороту з 5000 грн. на 5181 грн. в комірці В8 для встановлення значення чистого прибутку в 60 тис. грн. та запише в таблицю результати своєї роботи (таблиця 11).

Таблиця 11.

Вплив обсягу товарообороту на зміну чистого прибутку торговельного підприємства

Далі необхідно дослідити, яким чином впливає середня торговельна надбавка на зміну чистого прибутку торговельного підприємства від 42 до 60 тис.грн. Побудуйте таблицю початкових значень економічних показників, аналогічну першій таблиці 8, починаючи з 40 рядка. При створенні таблиці використовуйте числові дані та формули (у  комірці В43 записана формула =В41*В42; у комірці В45 - формула =В43*В44/100; у комірці В46 - формула =В47+В48; у комірці В48 - формула =В49*В43/100; у комірці В50 - формула =В45-В46; у комірці В52 - формула =В50*(100-В51)/100). Таблиці надати заголовок: Вплив середньої торгівельної надбавки на зміну чистого прибутку торговельного підприємства. Результати розрахунків приведені в таблиці 12.

Таблиця 12.

Вплив середньої торгівельної надбавки на зміну чистого прибутку торговельного підприємства

Необхідно встановити курсор на комірку В44 та викликати команду Підбір параметра, для того щоб встановити в комірці В52 значення чистого прибутку 60 тис. грн, відповідно змінивши значення середньої торговельної надбавки (адреса комірки В44). У діалоговому вікні слід зазначити абсолютні адреси комірок та значення.

Після натискання кнопки ОК, програма Підбір параметра змінить значення середньої торгівельної надбавки з 25,00% на 25,51 % в комірці В52 для встановлення значення чистого прибутку в 60 тис. грн. та запише в таблицю 13. результати своєї роботи.

Таблиця 13.

Вплив середньої торгівельної надбавки на зміну чистого прибутку торговельного підприємства

Далі проводимо дослідження, яким чином впливають витрати обігу на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. Для цього побудуйте таблицю початкових значень економічних показників, аналогічну таблиці 8, починаючи з 57 рядка. При створенні таблиці використовувалися числові дані та формули (у  комірці В60 записана формула =В58*В59; у комірці В62 - формула =В60*В61/100; у комірці В63 - значення 1190; у комірці В65 - формула =В66*В60/100; у комірці В67 - формула =В62-В63; у комірці В69 - формула =В67*(100-В68)/100).  Також зверху таблиці ввести заголовок: Вплив витрат обігу на зміну чистого прибутку торгівельного підприємства. Результати розрахунків представлені в таблиці 14.

Таблиця 14.

Вплив витрат обігу на зміну чистого прибутку торговельного підприємства

Встановити курсор на комірку В63 та викликати команду Підбір параметра, для того щоб встановити в комірці В69 значення чистого прибутку 60 тис.грн., відповідно змінивши значення середньої торгівельної надбавки (адреса комірки В63. У діалоговому вікні слід зазначити абсолютні адреси комірок та значення.

Після натискання ОК програма Підбір параметра змінить значення витрат обігу з 1190 на  1164 в комірці В63 для встановлення значення чистого прибутку в 60 тис.грн. та запише в таблицю 15 результати своєї роботи.

Таблиця 15.

Вплив витрат обігу на зміну чистого прибутку торговельного підприємства

Враховуючи попередні розрахунки та результати програми Підбір параметра, можна зробити висновок, що для збільшення чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. необхідно вибрати одне з наступних рішень:

• збільшити ціну реалізації з 5,00 грн. до 5,18 грн.;

• збільшити обсяг товарообороту з 5000 до 5181 тис. грн.;

• збільшити середню торговельну надбавку на 0,51 % (25,51-25,00=0,51);

• зменшити витрати обігу з 1190 до 1164.

Для дослідження впливу всіх факторів одночасно (ціна реалізації, обсяг товарообігу, середня торгівельна надбавка, витрати обігу) на зміну чистого прибутку підприємства від 42 до 60 тис. грн. проведемо оптимізаційне моделювання за допомогою програми Пошук рішення. Перейти на робочий аркуш з назвою Оптимізаційне моделювання та створити таблицю (рис.11). Ввести заголовок: Вплив множини факторів на зміну чистого прибутку торговельного підприємства.

Завантажимо програму Пошук рішення . В діалогове вікно програми (Рис. 12) ввести цільову функцію - чистий прибуток (комірка $В$18); керовані змінні - середня ціна реалізації (комірка $В$8), обсяг товарообороту (комірка $В$9), середня торгівельна надбавка (комірка $В$10), витрати обігу (комірка $В$12); поставити умови: рівень змінних витрат £ 10.8 ($В$15£10.8), прибуток від реалізації ³ 60 ($В$16³60).

Для додавання обмежень необхідно натиснути кнопку Додати, та у вікні конструктора обмежень (рис. 13) ввести відповідні абсолютні адреси комірок, значення, оператори порівняння.

Програма Пошук рішення буде виконувати оптимізацію при наступних параметрах (їх можна переглянути або змінити, натиснувши кнопку Параметри). На екрані з’явиться вікно Параметри пошуку рішення.

Після заповнення діалогового вікна Пошук рішення слід натиснути кнопку Виконати. Через декілька секунд з’явиться вікно Результати пошуку рішення (рис. 15), в якому вибрати опцію Зберегти шукане рішення, та натиснути кнопку ОК.

Після зазначених операцій значення факторів - керованих змінних - в таблицях дещо змінилося. Так, середня торгівельна надбавка та середня ціна реалізації залишилися незмінними (25 % та 5 грн, відповідно); а, обсяг товарообороту збільшився до 5102,86 та змінні витрати обігу зменшилися до 551,1. Результати оптимізації представлені в таблиці 16.

Таблиця 16.

Вплив множини факторів (ціна реалізації, обсяг товарообороту, середня торгівельна надбавка, витрати обігу) на зміну чистого прибутку торговельного підприємства

Програма Пошук рішення видає результати проведеної оптимізації у вигляді трьох звітів: звіт по результатах (рис. 16.), звіт по стійкості (рис. 17) та звіт по межах (рис. 18.), які автоматично розташовуються на окремих робочих аркушах. Видача звітів відбувається при їх замовленні у вікні Результати пошуку рішення.

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

Зберегти результати на сервері.

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