Методичні вказівки до практичної роботи №14 - Імітаційне моделювання впливу факторів на зміну чистого прибутку

Лабораторна робота № 3

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

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

Завдання та порядок виконання роботи

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

Таблиця 3.1.

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

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

3. Оформити звіт та зробити висновки по роботі.

Теоретичні відомості

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

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

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

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

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

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

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

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

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

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

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

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

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

ПОРЯДОК РОЗВ'ЯЗКУ ЗАВДАНЬ ЛАБОРАТОРНОЇ РОБОТИ

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

При створенні таблиці використовувалися числові дані та формули (у  комірці В9 записана формула =В7*В8; у комірці В11 - формула =В9*В10/100; у комірці В14 - формула =В15*В9/100; у комірці В16 - формула =В11-В12; у комірці В12 - формула =В13+В14; у комірці В18 - формула =В16*(100-В17)/100). Також зверху таблиці введено заголовок: "Вплив ціни реалізації на зміну чистого прибутку торгівельного підприємства". Далі необхідно поставити курсор мишки на комірку В8 та викликати команду Сервіс-Підбір параметра. Дію цієї команди можна записати таким чином: встановити в комірці $В$18 значення чистого прибутку 60 тис. грн, відповідно змінивши ціну реалізації (адреса комірки $В$8). У діалоговому вікні, що з'явилося після дії команди, слід зазначити такі абсолютні адреси комірок та значення.

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

Після натискання в цьому вікні кнопки ОК, програма "Підбір параметра" запише в таблицю результати своєї роботи.

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

Далі необхідно поставити курсор мишки на комірку В26 та викликати команду Сервіс-Підбір параметра. Дію цієї команди можна записати таким чином: встановити в комірці $В$35 значення чистого прибутку 60 тис. грн., відповідно змінивши обсяг товарообороту (адреса комірки $В$26). У діалоговому вікні, що з'явилося після дії команди, слід зазначити такі абсолютні адреси комірок та значення.

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

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

Також необхідно дослідити, яким чином впливає середня торговельна надбавка на зміну чистого прибутку торговельного підприємства від 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).  Також зверху таблиці введено заголовок: "Вплив середньої торгівельної надбавки на зміну чистого прибутку торговельного підприємства".

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

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

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

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

Далі необхідно поставити курсор мишки на комірку В63 та викликати команду Сервіс-Підбір параметра. Дію цієї команди можна записати таким чином: встановити в комірці $В$69 значення чистого прибутку 60 тис. грн., відповідно змінивши значення середньої торгівельної надбавки (адреса комірки $В$63). У діалоговому вікні, що з'явилося після дії команди, слід зазначити такі абсолютні адреси комірок та значення.

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

Після натискання в цьому вікні кнопки ОК, програма "Підбір параметра" запише в таблицю результати своєї роботи.

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

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

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

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

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

Для дослідження впливу всіх факторів одночасно (ціна реалізації, обсяг товарообігу, середня торгівельна надбавка, витрати обігу) на зміну чистого прибутку торговельного підприємства від 42 до 60 тис. грн. проведемо оптимізаційне моделювання за допомогою програми "Пошук рішення". Перейдемо на робочий аркуш з назвою "Оптимізаційне моделювання" та створимо таблицю, використовуючи дані завдання лабораторної роботи. При створенні таблиці використовувалися числові дані та формули (у  комірці В9 записана формула =В7*В8; у комірці В11 - формула =В9*В10/100; у комірці В14 - формула =В15*В9/100; у комірці В16 - формула =В11-В12; у комірці В12 - формула =В13+В14; у комірці В18 - формула =В16*(100-В17)/100). Також зверху таблиці введено заголовок: "Вплив множини факторів (ціна реалізації, обсяг товарообігу, середня торгівельна надбавка, витрати обігу) на зміну чистого прибутку торговельного підприємства".

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

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

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

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

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

7. Висновки: в ході лабораторної роботи було проведено імітаційне та оптимізаційне моделювання зміни чистого прибутку торгівельного підприємства з 42 до 60 тис. грн. та впливу на цю зміну таких факторів, як середня ціна реалізації, середня торгівельна надбавка, обсяг товарообороту, витрати обігу. Найбільш доцільно керівництву підприємства врахувати результати оптимізації значень всіх зазначених факторів, які наведені в пункті 6.

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

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