Методичні вказівки до практичної роботи №4 - Імітаційне та оптимізаційне моделювання в Excel

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

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

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

ТЕОРЕТИЧНІ ВІДОМОСТІ

Імітаційне моделювання полягає у встановленні цільового значення результативного показника та розрахунку зміни кожного з показників-факторів окремо для його досягнення. Засобами табличного процесора MS Excel можливий розрахунок імітаційних моделей за допомогою програми "Підбір параметра" пункту меню Сервіс.

Ця програма працює так: користувач задає потрібний результат, вибирає змінюваний параметр формули і запускаєте програму пошуку значення параметра, при якому буде досягнутий вказаний результат.  

Оптимізаційні моделі дають змогу вирішувати широке коло питань планування економічної діяльності того чи іншого об’єкта господарювання,  знаходити найкращі оптимальні фінансові рішення, що відповідають певним обмеженням.   Вибір найдосконалішого рішення з низки альтеративних  використовується при вирішенні багатьох фінансово-економічних проблем: розрахунку оптимальної кількості працівників, оптимальних величин товарної та грошової мас, планування витрат та їх покриття тощо. Розробка оптимізаційних задач полягає в досягненні цільового показника заданого значення за умови зміни показників-факторів у певних межах, які враховують потенціальні можливості збільшення або зменшення тих чи інших показників фінансово-господарської діяльності. За допомогою оптимізаційної моделі економіст має можливість задати певні обмеження, в рамках яких підприємство реально здійснює свою господарську діяльність; визначити необхідні параметри своєї діяльності, що дозволить отримати бажаний результат. На відміну від імітаційної моделі, де в кожному окремому випадку змінюється лише один показник, дане моделювання дозволяє отримати бажаний результат, змінюючи весь комплекс показників-факторів. Саме тому програма "Пошук рішення" з пакету аналізу табличного процесора MS Excel є потужною допоміжною інформаційною технологією при плануванні та веденні фінансово-економічної діяльності на будь-яких об’єктах управління. В результаті роботи програми автоматично підбираються оптимальні значення всіх показників у межах заданих обмежень, що формують результативний показник. Користувач може задати режим, при якому отримані значення змінних будуть автоматично заноситися у таблицю і, крім того, може видати результати роботи програми у вигляді звітів.

Розв’язок задачі за допомогою програми "Пошук рішення" виконується у наступній послідовності:

• постановка задачі та створення математичних моделей;

• запис задачі у табличній формі, придатній для введення даних;

• введення даних в персональний комп’ютер та розв’язок задачі;

• економіко-математичний аналіз отриманого рішення.

Економіко-математична модель лише з деяким наближенням відображає реальні обставини. Тому після аналізу отриманого рішення ці обставини уточнюються, постановка задачі і модель належним чином коригуються, введені дані змінюються і програма знову запускається на виконання. Такий процес триває доти, поки не буде знайдена найбільш адекватна модель.

Задача, що розв’язується програмою "Пошук рішення",  у загальному вигляді формулюється так: знайти вектор Х=(Х₁, Х₂, ..., Х_n), який мінімізує або максимізує цільову функцію j(Х) при певних функціональних обмеженнях та прямих умовах. Для застосування програми "Пошук рішення" необхідно побудувати модель аналізованого процесу у вигляді таблиці з формулами, які відображають задані обмеження та цільову функцію. Від форми подання цих даних і структури табличної моделі значною мірою залежить трудомісткість та безпомилковість уведення даних у програму, а також придатність моделі для обґрунтування рішень, що приймаються на підставі отриманого результату.

Для запуску програми "Пошук рішення" виконується команда Сервіс-Пошук рішення.

Спочатку вводиться абсолютна адреса комірки з цільовою функцією, та зазначається значення, до якого вона повинна прямувати (мінімум, максимум або числове значення). Далі зазначається перелік абсолютних адрес комірок керованих змінних, які розділяються між собою крапкою з комою. Для додавання певних обмежень натискають кнопку Додати, та діалоговому вікні, що з’явилось на екрані (рис. 3.9.), послідовно, один за одним вводять обмеження.

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

Параметр Максимальний час служить для обмеження часу, що відпускається на пошук рішення задачі (за умовчанням цей параметр дорівнює 100 с). У разі необхідності користувачу дозволяється змінювати його до 32767 с. Параметр Точність служить для завдання точності, з якою визначається відповідність комірки, в якій міститься цільова функція, цільовому значенню або наближення її до зазначених меж. Параметр Припустиме відхилення служить для завдання допуску на відхилення від оптимального рішення, якщо множина значень комірки цільової функції обмежена множиною цілих чисел. Параметр Збіжність необхідний для припинення пошуку рішення і застосовується тільки до нелінійних задач, умовою слугує дріб з інтервалу від 0 до 1 (кращий збіг характеризує більша кількість десяткових знаків). Параметр Лінійна модель служить для прискорення пошуку рішення лінійної задачі оптимізації або лінійної апроксимації нелінійної задачі. Параметр Оцінка служить для вказівки методу екстраполяції (лінійна або квадратична), який використовується для отримання вихідних оцінок значень змінних у кожному одномірному пошуку. Параметр Метод служить для вибору алгоритму оптимізації за методами Ньютона або сполучених градієнтів для вказівки напрямку пошуку.

ЗАВДАННЯ ТА ПОРЯДОК ВИКОНАННЯ РОБОТИ

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

Використати засіб "Підбір параметра". Необхідні дані (значення економічних показників підприємства) наведені в таблиці 3.10. Побудувати результативну таблицю та розробити три варіанти сценарію: оптимістичний, песимістичний та реалістичний, кожен з яких зображує реальні зміни показників, які впливають на прибуток підприємства.

Таблиця 3.10.

Хід виконання

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

2. На робочому аркуші "Імітаційне моделювання" побудувати  таблицю показану на Рис. 3.10, значення та формули для розрахунків взяти з таблиці 3.10..

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

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

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

Таблиця 3.11.

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

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

Таблиця 3.12.

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

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

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

Таблиця 3.13.

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

Далі необхідно дослідити, яким чином впливає середня торговельна надбавка на зміну чистого прибутку торговельного підприємства від 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.14.

Таблиця 3.14.

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

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

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

Таблиця 3.15.

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

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

Таблиця 3.16.

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

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

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

Таблиця 3.17.

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

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

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

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

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

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

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

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

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

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

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

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

Таблиця 3.18.

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

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

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

2. Оформити звіт, зробити висновки і відповісти на контрольні питання.

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