Практична робота №3 на тему Розробка кількісних ймовірнісних моделей засобами табличного процесора MS Excel, НУДПСУ, Національний університет державної податкової служби України

ПРАКТИЧНА РОБОТА №3
Тема: „Розробка кількісних ймовірнісних моделей засобами табличного процесора MS Excel”

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

Час виконання роботи – 4 години

ХІД РОБОТИ

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

Моделі прийняття рішень можна класифікувати на три класи, причому кожний клас визначається своїми припущеннями про поведінку природи. Це класи моделей прийняття рішень:

• в умовах визначеності,

• в умовах ризику,

• в умовах невизначеності.

Прийняття рішень в умовах визначеності

Цей клас моделей відповідає ситуації, коли відомо, в якому стані знаходитиметься природа після прийняття нами рішення. Цю умову можна інтерпретувати і так, що природа може знаходитися тільки в одному-єдиному стані. Припустимо, вранці ви вирішуєте, чи узяти вам парасольку, якщо ви упевнені, що в другій половині дня (під час повернення з навчання чи роботи) обов’язково буде дощ. В таблиці платежів для цієї моделі (табл. 3.1) рішенню не брати парасольку відповідає платіж -30 грн. (вартість чищення одягу, якщо ви потрапите під дощ). Знак мінус тут вказує на те, що "виграш" в грі проти природи буде негативним, тобто таку суму ви втратите при виборі цього рішення. Звичайно ж, оптимальним рішенням буде узяти парасольку.

Таблиця 3.1.

Таблиця платежів для прикладу з парасолькою

Прийняття рішень в умовах ризику

Відсутність визначеності відносно майбутніх подій - характерна риса багатьох (але не всіх) управлінських моделей прийняття рішень. Уявіть фінансового віце-президента страхової компанії, який при прийнятті рішень точно знає тільки те, що зміни на фінансовому ринку обов’язково будуть. Очевидно, що багато моделей реальних ситуацій характеризуються відсутністю визначеності. В кількісних моделях з невизначеністю можна "боротися" різними способами. Наприклад, у якийсь спосіб можна побудувати оцінку для даних, що відносяться до майбутніх подій. В багатьох моделях, що описують виробничі ситуації, звичайно перше обмеження описує виробничі можливості (це може бути робочий час персоналу, потужності устаткування і т.п.), які будуть доступні протягом розрахункового періоду часу. Ці можливості, звичайно, залежать від чинників, які можуть виявитися в майбутньому, і які наперед важко врахувати. Але виробничий план складається сьогодні, а не заднім числом, тому менеджер просто зобов’язаний оцінити майбутні виробничі можливості.

Визначення ризику

Теорія прийняття рішень пропонує свій підхід до моделей з неповною визначеністю. Цей підхід називається прийняттям  рішень в умовах ризику. Тут термін ризик має цілком певне і чітко окреслене значення. В класі моделей прийняття рішень в умовах ризику розглядається декілька станів природи, і ми можемо зробити припущення про ймовірність настання кожного можливого стану природи. Нехай, наприклад, є n (n > 1) станів природи, і p_j - оцінка ймовірності настання події j. В загальному випадку значення ймовірності p, оцінюється на підставі яких-небудь статистичних даних за минулі періоди часу, де зафіксовані прояви події j протягом часу спостереження. Наприклад, якщо протягом останніх 1000 днів ми зафіксували 200 дощових днів, то оцінкою ймовірності дощу в даний день буде число 0,20 (=200/1000). Якщо статистичні дані відсутні або недоступні, або якщо менеджер з якоїсь причини не може спрогнозувати їх на майбутнє, то він все одно повинен оцінити цю ймовірність, нехай навіть суб’єктивно.

Нагадаємо, що очікуване значення будь-якої випадкової величини обчислюється як зважене середнє всіх можливих значень цієї випадкової величини, де вага є  ймовірністю прийняття випадковою величиною даних значень. Оскільки результат прийняття того або іншого рішення залежить від станів природи, очікуваний результат, пов’язаний з рішенням i, обчислюється як сума по всіх можливих станах j добутків платежу r_ij (результат від рішення i при j-ому стані природи) і ймовірності p_j, (ймовірність стану природи j). Таким чином, R_i - очікуваний результат від прийняття  рішення i, обчислюється за  формулою.

Менеджмент повинен завжди обирати те рішення, яке забезпечує максимальний результат. Іншими словами, рішення і буде оптимальним, якщо буде виконуватись умова, для всіх значень і.

Завдання 2. Розробіть табличну кількісну ймовірнісну модель газетного кіоску засобами табличного процесора MSExcel.

Модель діяльності газетного кіоску

 Власник газетного кіоску може купити газету Фельєтон по 40 копійок за кожний екземпляр і продати по 75  коп. Але, звичайно, він повинен закупити газети до того, як знатиме, скільки реально він їх продасть. Якщо він закупить газет більше, ніж зможе продати, то він зазнає збитків, рівних вартості непроданих газет. Якщо він закупить дуже мало газет, то він втратить потенційних покупців сьогодні і, можливо, в майбутньому (незадоволений покупець може перестати купувати в цьому газетному кіоску свою улюблену газету). Припустимо, що майбутні втрати (тобто упущену вигоду) можна узагальнено оцінити в 50 копійок на одного незадоволеного покупця. Власник на основі статистичних даних оцінив ймовірність попиту на газету таким чином:

р₀= р(попит =0) =0,1;

р₁= р(попит =1) =0,3;

р₂= р(попит =2) =0,4;

р₃= р(попит =3) =0,2.

2.1. Створіть документ MSExcelз ім’ям Практ_3.xls.

2.2. На робочому аркуші Модель газетного кіоску введіть у комірки вихідні та розрахункові дані у таблиці платежів відповідності до даних на Рис. 1.

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

Платіж = 75 х (Кількість проданих газет) - 40 х (Кількість закуплених газет) - 50 х (Незадоволений попит).

Тут 75 коп - ціна проданої газети, 40 коп - закупівельна вартість газети і 50 коп - вартість втрати покупця (упущена вигода).

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

75 х 0 - 40 х 0 - 50 х Попит = -50 х Попит.

Якщо закуплений один екземпляр газети, а попиту немає, то ця газета не продана, незадоволений попит рівний 0, а платіж обчислюється як

75 х 0 -40 х 1 - 50 х 0 = -40,

що відповідає значенню першого платежу в другому рядку таблиці платежів. Але якщо закуплений один екземпляр газети і попит на газету не нульовою, то цей екземпляр газети обов’язково буде проданий, а незадоволений попит буде на 1 менше самого попиту. В цьому випадку платіж обчислюється як

75 х 1 - 40 х 1 - 50 х (Попит - 1)= 85 - 50 х Попит.

Так само обчислюються інші значення платежів.

2.3. Виконайте обчислення очікуваного результату (платежу) у комірках G7:G10.

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

2.4. Зробіть висновки стосовно прийняття оптимального рішення.

Завдання 3. Побудуйте графіки профілів ризику для моделі газетного кіоску.

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

3.1. Побудуйте гістограми платежів  для кожного рішення, як на Рис. 3.3-6.

Для цього спочатку створіть таблицю відповідності платежів і їх ймовірностей як на Рис. 3.3-4. Використайте автозаповнення для стовпця Платежі. Далі побудуйте гістограми – графіки профілів ризиків як на Рис.  3.5-3.8.

З графіків  видно, що всі чотири можливі виходи для "Рішення 0" менше або рівні нулю (тобто при цьому рішенні можливі лише одні збитки). В "Рішення 1" три з чотирьох виходів від’ємні, а в "Рішення 2" і "Рішення 3’’ відєємна половина виходів. З профілів ризиків також видно, що в "Рішення 2" найбільшу можливу ймовірність (0,4) має другий за величиною позитивний платіж (70 коп.).  Отже саме це рішення буде оптимальним. Звичайно, вся ця інформація представлена в початковій таблиці платежів, але часто буває корисним представити цю інформацію у вигляді аналогової моделі – діаграми.

Завдання 4. Виконайте аналіз чутливості оптимального рішення стосовно упущеної вигоди.

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

Один із способів аналізу чутливості полягає в завданні різних значень упущеної вигоди, перерахунку таблиці платежів, повторному обчисленні очікуваних платежів і виборі на їх основі нового оптимального рішення.

За допомогою таблиць підстановки Excel можна легко створити таблицю розрахунку очікуваних платежів для кожного рішення залежно від величини упущеної вигоди. В тій же робочій книзі  створимо робочий аркуш з ім’ям Аналіз чутливості. Значення упущеної вигоди змінюватимемо від 0 до 150 центів з кроком в 5 коп. Для створення таблиці підстановки виконайте наступні дії.

1. Скопіюйте дані з робочого аркуша Модель газетного кіоску на аркуш Аналіз чутливості.

2. Введіть значення 0 (початкове значення упущеної вигоди) в комірку А16.

3. Знову клацніть кнопкою миші на комірці А16 і виконайте команду Правка®Заполнить®Прогрессия.

4. У діалоговому вікні Прогрессия, що відкрилося, клацніть на перемикачі Расположение  По столбцах, введіть значення 5 в полі Шаг і значення 150 в полі Граничное значение. Потім клацніть на кнопці ОК.

5. У комірку В15 введіть формулу =G7, яка дасть значення очікуваного платежу для вирішення, при якому газета не закупляється зовсім (рішення 0). В комірки С15:Е15 введіть формули =G8, =G9 і =G7, які дадуть значення очікуваних платежів для рішень 1, 2 і 3 відповідно.

6. Виділіть діапазон А15:Е46 і виконайте команду Данные®Таблица подстановки. В діалоговому вікні Таблица подстановки в полі Подставлять значения по строкам  введіть $В$3 (можна клацнути на комірці В3 і натиснути клавішу F4), як показано на Рис. 9. Цим ви говорите Excel, що значення, введені в стовпці А, слід підставити в комірку ВЗ (по одному значенню за один раз), перерахувати формули в діапазоні F7:F10 і обчислені значення поставити в стовпці В, С і D відповідно.

Отримаємо таблицю підстановки, зображену на Рис. 10.

Результати, представлені в таблиці підстановки, можна подати у графічному вигляді. Для цього використаємо майстер побудови діаграм Excel.

1. Виділіть діапазон з даними, які ви хочете відобразити на діаграмі. В даному випадку це діапазон В16:Е46.

2. Клацніть на кнопці Майстер діаграм, яка знаходиться на стандартній панелі інструментів. Послідовно відкриється ряд діалогових вікон майстра діаграм, які допоможуть побудувати потрібну вам діаграму.

3. У першому діалоговому вікні Майстер діаграм слід вказати бажаний тип діаграми. Клацніть в списку Тип на типі Графік і далі виберіть підтип діаграми, наприклад Графік з маркерами. Потім клацніть на кнопці Далі.

4. В наступному вікні Майстер діаграм буде показаний зразок вашого графіка. В цьому вікні перейдіть на вкладку Ряд і в полі Підпису осі X введіть „Аналіз чутливості”!$А$16:$А$46 (або виділіть цей діапазон на робочому аркуші). Далі в списку Ряд по черзі виділяйте Ряд1, Ряд2, РядЗ і Ряд4, при цьому в полі Ім’я вводите відповідно Рішення 0, Рішення 1, Рішення 2 і Рішення 3, як на Рис.  11.  Потім клацніть на кнопці Далі.

5. У наступному діалоговому вікні введіть заголовки для діаграми і для осей X і У. Клацніть на кнопці Далі.

6. У останньому діалоговому вікні Майстер діаграм вкажіть, що ви хочете помістити діаграму на поточному активному аркуші (вибір за замовчуванням) і клацніть на кнопці Готово.

7. На робочому листі Аналіз чутливості з’явиться графік, показаний на Рис.  3.12.

Як бачимо, найменш чутливим є рішення 2 та рішення 3, але рішення 2 забезпечує більший очікуваний платіж, а тому саме воно є оптимальним.

Завдання 5. Захистіть виконану роботу.

Питання до захисту практичної роботи

1. Які різновиди моделей прийняття рішень існують?

2. Яку особливість мають детерміновані моделі?

3. Яку особливість мають моделі прийняття рішень в умовах ризику?

4. Який підхід пропонує теорія прийняття рішень в умовах невизначеності?

5. Як можна визначати ймовірність події на основі статистичних даних?

6. Як обчислювались платежі у моделі газетного кіоску?

7. Як обчислювався очікуваний результат прийняття рішення?

8. Як знаходилось оптимальне рішення у моделі газетного кіоску?

9. Як вмконується множення із застосуванням функції СУММПРОИЗВ?

10. Як побудувати графік профілю ризику для заданого рішення?

11. Як застосувати аналіз профілів ризику для визначення оптимального рішення?

12. З якою метою виконувався аналіз чутливості оптимального рішення до змін вхідної змінної моделі упущена вигода?

13. Який засіб MS Excel використовувався для аналізу чутливості у моделі газетного кіоску?

14. Як будувалась графічна модель аналізу чутливості?

15. Чому при розробці даної моделі не можна було використата засіб Поиск решения?

З повагою ІЦ “KURSOVIKS”!