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

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

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

Рис. 3.7. Вікно програми "Підбір параметра"

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

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

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

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

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

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

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

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

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

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

 

Рис. 3.8. Вікно програми " Пошук рішення ".

 

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

Рис. 3.9. Вікно режиму для додавання певних обмежень.

 

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

Рис. 3.9. Вікно режиму " Параметри пошуку рішення ".

 

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

 

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

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

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

Рис. 3.10.Вікно з вхідними даними.

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

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

Рис. 3.11.Вікно програми "Підбір параметра".

 

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

Таблиця 3.11.

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

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

Рис. 3.7. Вікно програми "Підбір параметра"

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

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

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

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

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

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

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

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

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

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

 

Рис. 3.8. Вікно програми " Пошук рішення ".

 

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

Рис. 3.9. Вікно режиму для додавання певних обмежень.

 

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

Рис. 3.9. Вікно режиму " Параметри пошуку рішення ".

 

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

 

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

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

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

 

 

Таблиця 3.10.

Показник	Позначення комірки	Базовий розмір
Обсяг реалізації	В7
Середня ціна реалізації, грн	В8
Обсяг товарообороту	В9	=В7*В8
Середня торгівельна надбавка, %	В10
Валовий дохід, тис.грн	В11	=В9*В10/100
Витрати обігу, %	В12	=В13+В14
Постійні	В13
Змінні	В14	=В15*В9/100
Рівень змінних витрат, %	В15	10.8
Прибуток від реалізації, тис.грн	В16	=В11-В12
Ставка податку на прибуток,%	В17
Чистий прибуток, тис.грн	В18	=В16*(100-В17)/100

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

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

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

Рис. 3.10.Вікно з вхідними даними.

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

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

Рис. 3.11.Вікно програми "Підбір параметра".

 

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

Таблиця 3.11.