Вступ до моделювання Монте-Карло в Excel

• Хто використовує симуляцію Монте-Карло?

• Що відбувається, коли ви вводите =RAND() у клітинку?

• Як імітувати значення дискретної випадкової змінної?

• Як імітувати значення звичайної випадкової змінної?

• Як компанія може визначити, скільки листівок виготовити?

Ми б хотіли точно оцінити ймовірності невизначених подій. Наприклад, яка ймовірність того, що грошові потоки нового продукту матимуть додатну чисту поточну вартість (NPV)? Який фактор ризику для нашого інвестиційного портфеля? Моделювання Монте-Карло дозволяє нам моделювати ситуації, які представляють невизначеність, а потім грати їх на комп'ютері тисячі разів.

У наступних п'яти розділах ви побачите приклади того, як можна використовувати Excel для виконання симуляцій Монте-Карло.

Багато компаній використовують моделювання Монте-Карло як важливу частину процесу прийняття рішень. Ось кілька прикладів.

• General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb і Eli Lilly використовують моделювання, щоб оцінити як середню віддачу, так і фактор ризику нових продуктів. У GM ця інформація використовується генеральним директором для визначення, які продукти виходять на ринок.

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

• Lilly використовує моделювання для визначення оптимальної потужності рослин для кожного препарату.

• Proctor і Gamble використовують моделювання для моделювання і оптимального хеджування валютного ризику.

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

• Нафтові та наркобарони використовують моделювання для оцінки "реальних варіантів", таких як вартість варіанту розширення, контракту або відкладення проекту.

• Фінансові планувальники використовують моделювання Монте-Карло для визначення оптимальних інвестиційних стратегій виходу своїх клієнтів на пенсію.

Коли ви вводите формулу =RAND() у клітинку, ви отримуєте число, яке однаково може вважатися будь-яким значенням від 0 до 1. Таким чином, близько 25 відсотків часу, ви повинні отримати число менше або дорівнює 0,25; приблизно 10 відсотків часу ви повинні отримати число, принаймні 0,90 тощо. Щоб продемонструвати, як працює функція RAND, перегляньте Randdemo.xlsx файлу, що відображається на рисунку 60-1.

Спочатку скопіюйте з клітинки C3 до C4:C402 формулу =RAND(). Потім введіть ім'я діапазону C3:C402 Data. Потім у стовпці F можна відстежувати середнє значення 400 випадкових чисел (клітинка F2) і використовувати функцію COUNTIF, щоб визначити дроби від 0 до 0,25, 0,25 і 0,50, 0,50 і 0,75 і 0,75 і 1. Якщо натиснути клавішу F9, випадкові числа переобчислюються. Зверніть увагу, що середнє значення 400 чисел завжди дорівнює приблизно 0,5, і що близько 25 відсотків результатів мають інтервал 0,25. Ці результати узгоджуються з визначенням випадкового числа. Також зверніть увагу, що значення, створені RAND у різних клітинках, незалежні. Наприклад, якщо випадкове число, створене в клітинці C3, – це велике число (наприклад, 0,99), це нічого не говорить про значення інших створених випадкових чисел.

Припустімо, вимога до календаря регулюється такою дискретною випадковою змінною:

Попит	імовірність
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Як програма Excel може відтворюватися або імітувати таку вимогу до календарів багато разів? Хитрість полягає в тому, щоб пов'язати кожне можливе значення функції RAND з можливим попитом на календарі. Наведене нижче призначення гарантує, що вимога в 10 000 буде виконуватися 10 відсотків часу тощо.

Попит	Призначено випадкове число
10 000	Менше 0,10
20 000	Більше або дорівнює 0,10 і менше 0,45
40,000	Більше або дорівнює 0,45 і менше 0,75
60 000	Більше або дорівнює 0,75

Щоб продемонструвати моделювання попиту, перегляньте Discretesim.xlsx файлу, показаний на рисунку 60-2 на наступній сторінці.

Ключем до нашої симуляції є використання випадкового числа для ініціювання підстановки з діапазону таблиці F2:G5 ( підстановка). Випадкові числа, більші або рівні 0 і менші за 0,10, дадуть попит на 10 000; випадкові числа, більші або рівні 0,10 і менші за 0,45, дадуть попит 20 000; випадкові числа, більші або рівні 0,45 і менші за 0,75, дадуть попит 40 000; та випадкові числа, більші або рівні 0,75, дадуть попит на 60 000. Щоб створити 400 випадкових чисел, скопіюйте з C3 до C4:C402 формулу RAND(). Потім ви створюєте 400 ознайомлювальних версій або ітерацій із вимогою календаря, скопіювавши з B3 до B4:B402 формулу VLOOKUP(C3,lookup,2). Ця формула гарантує, що будь-яке випадкове число, менше за 0,10, створює попит 10 000, будь-яке випадкове число від 0,10 до 0,45 створює попит 20 000 тощо. У діапазоні клітинок F8:F11 використовуйте функцію COUNTIF, щоб визначити частку наших 400 ітерацій, які приносять кожен попит. Якщо натиснути клавішу F9, щоб переобчислити випадкові числа, імітовані ймовірності близькі до очікуваних ймовірностей попиту.

Якщо ввести в будь-якій клітинці формулу NORMINV(rand(),mu,sigma), буде створено імітоване значення звичайної випадкової змінної із середнім му та стандартним відхиленням сигми. Цю процедуру показано на Normalsim.xlsx файлу, показаному на рисунку 60-3.

Припустімо, ми хочемо імітувати 400 випробувань або ітерацій для звичайної випадкової змінної із середнім значенням 40 000 та стандартним відхиленням 10 000. (Ці значення можна вводити в клітинках E1 та E2 та називати ці клітинки середніми та сигмами відповідно.) Якщо скопіювати формулу =RAND() із клітинки C4 до C5:C403, буде створено 400 різних випадкових чисел. Під час копіювання з B4 до B5:B403 формула NORMINV(C4;середнє;сигма) створює 400 різних пробних значень зі звичайної випадкової змінної із середнім значенням 40 000 та стандартним відхиленням 10 000. Коли ми натискаємо клавішу F9, щоб переобчислити випадкові числа, середнє значення залишається близьким до 40 000, а стандартне відхилення близьке до 10 000.

По суті, для випадкового числа x формула NORMINV(p,mu,sigma) генерує p-йпроцентиль звичайної випадкової змінної з середнім му і стандартною сигмою відхилення. Наприклад, випадкове число 0,77 у клітинці C4 (див. Рисунок 60-3) створює в клітинці B4 приблизно 77-й процентиль звичайної випадкової змінної із середнім значенням 40 000 і стандартним відхиленням 10 000.

У цьому розділі ви побачите, як моделювання Монте-Карло можна використовувати як інструмент для прийняття рішень. Припустімо, що вимога до листівки до Дня Святого Валентина регулюється такою дискретною випадковою змінною:

Попит	імовірність
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Вітальна листівка продається за $ 4,00, а змінна вартість виробництва кожної листівки становить $ 1,50. Залишкові картки слід утилізувати за ціною 0,20 доларів США за картку. Скільки карток потрібно надрукувати?

Зазвичай ми імітуємо кожну можливу кількість виробництва (10 000, 20 000, 40 000 або 60 000) багато разів (наприклад, 1000 ітерацій). Потім ми визначаємо, яка кількість замовлень дає максимальний середній прибуток за 1000 ітерацій. Дані цього розділу можна знайти в Valentine.xlsx файлу, як показано на рисунку 60–4. Імена діапазонів у клітинках B1:B11 призначаються клітинкам C1:C11. Діапазону клітинок G3:H6 призначено підстановку імен. У клітинках C4:C6 вводяться наші параметри збуту та витрат.

У клітинку C1 можна ввести кількість ознайомлювальної версії (40 000 у цьому прикладі). Потім створіть випадкове число в клітинці C2 за допомогою формули =RAND(). Як описано вище, ви імітуєте вимогу до картки в клітинці C3 за допомогою формули VLOOKUP(rand,lookup,2). (У формулі VLOOKUP rand – це ім'я клітинки, призначене клітинці C3, а не функції RAND.)

Кількість проданих одиниць товару – це менша кількість нашого виробництва та попит. У клітинці C8 ви обчислюєте наш дохід за допомогою формули MIN(вироблено,попит)*unit_price. У клітинці C9 обчислюється загальна вартість виробництва з формулою, виробленою*unit_prod_cost.

Якщо ми виробляємо більше карт, ніж користуються попитом, кількість одиниць, що залишилися поверх, дорівнює виробництву мінус попит; в іншому разі не залишилося одиниць. Ми обчислюємо вартість утилізації в клітинці C10 за допомогою формули unit_disp_cost*IF(виробляється>попит,вироблено–попит;0). Нарешті, у клітинці C11 ми обчислюємо прибуток як дохід – total_var_cost-total_disposing_cost.

Ми хотіли б ефективний спосіб натиснути F9 багато разів (наприклад, 1000) для кожної кількості виробництва і tally наш очікуваний прибуток для кожної кількості. Ця ситуація, в якій двостороння таблиця даних приходить до нашого порятунку. (Докладні відомості про таблиці даних див. в розділі 15 "Аналіз конфіденційності з таблицями даних"). Таблиця даних, яка використовується в цьому прикладі, відображається на рисунку 60–5.

У діапазоні клітинок A16:A1015 введіть числа від 1 до 1000 (відповідно до наших 1000 ознайомлювальних версій). Один із простих способів створити ці значення – почати з введення числа 1 у клітинці A16. Виділіть клітинку, а потім на вкладці Основне в групі Редагування натисніть кнопку Заливка та виберіть пункт Ряди , щоб відкрити діалогове вікно Ряди . У діалоговому вікні Ряд , показаному на рисунку 60–6, введіть значення кроку 1 і stop-значення 1000. В області Series In (Ряди в) виберіть параметр Columns (Стовпці ) і натисніть кнопку OK. Числа від 1 до 1000 буде введено в стовпець A, починаючи з клітинки A16.

Далі ми введемо наші можливі виробничі кількості (10 000, 20 000, 40 000, 60 000) у клітинки B15:E15. Ми хочемо обчислити прибуток для кожного ознайомлювального номера (від 1 до 1000) і кожної виробничої кількості. Ми посилаємося на формулу отримання прибутку (обчислена в клітинці C11) у верхній лівій клітинці нашої таблиці даних (A15), ввівши =C11.

Тепер ми готові змусити Excel імітувати 1000 ітерацій попиту на кожну кількість виробництва. Виділіть діапазон таблиць (A15:E1014), а потім на вкладці Дані в групі Знаряддя даних натисніть кнопку Що робити, якщо аналіз, а потім виберіть таблиця даних. Щоб налаштувати двосторонню таблицю даних, виберіть нашу виробничу кількість (клітинку C1) як клітинку введення рядка та виберіть будь-яку пусту клітинку (клітинку I14) як клітинку введення стовпця. Після натискання кнопки OK Excel імітує 1000 значень попиту для кожної кількості замовлень.

Щоб зрозуміти, чому це працює, розгляньте значення, розміщені в таблиці даних у діапазоні клітинок C16:C1015. Для кожної з цих клітинок програма Excel використовуватиме в клітинці C1 значення 20 000. У клітинці C16 значення клітинки введення стовпця 1 поміщається в пусту клітинку, а випадкове число в клітинці C2 переобчислюється. Відповідний прибуток потім записується в клітинці C16. Потім введене значення клітинки стовпця 2 поміщається в пусту клітинку, а випадкове число в клітинці C2 знову переобчислюється. Відповідний прибуток вводиться в клітинку C17.

Скопіювавши з клітинки B13 до C13:E13 формулу AVERAGE(B16:B1015), ми обчислюємо середній імітований прибуток для кожної виробничої кількості. Скопіювавши з клітинки B14 до C14:E14 формулу STDEV(B16:B1015), ми обчислюємо стандартне відхилення імітованого прибутку для кожної кількості замовлень. Щоразу, коли натискаються клавіші F9, для кожної кількості замовлень імітуються 1000 ітерацій попиту. Виробництво 40 000 карт завжди дає найбільший очікуваний прибуток. Таким чином, виявляється, що виробництво 40000 карт є правильним рішенням.

Вплив ризику на наше рішення      Якщо ми випустили 20 000 замість 40 000 карт, наш очікуваний прибуток падає приблизно на 22 відсотки, але наш ризик (як вимірюється стандартним відхиленням прибутку) падає майже на 73 відсотки. Таким чином, якщо ми вкрай проти ризику, виробництво 20000 карт може бути правильним рішенням. До речі, виробництво 10000 карт завжди має стандартне відхилення 0 карт, тому що якщо ми виробляємо 10000 карт, ми завжди будемо продавати всі з них без будь-яких залишків.

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

У клітинці J11 слід обчислити нижнє обмеження для 95-відсоткового довірчого інтервалу для середнього прибутку, коли 40 000 календарів вироблено за формулою D13–1,96*D14/SQRT(1000). У клітинці J12 ви обчислюєте верхню межу для нашого 95-відсоткового довірчого інтервалу з формулою D13+1.96*D14/SQRT(1000). Ці обчислення відображаються на рисунку 60–7.

Ми 95 відсотків впевнені, що наш середній прибуток, коли 40000 календарів замовлені в межах від $ 56687 до $ 62589.

1. Дилер GMC вважає, що попит на посланців 2005 року буде нормально розподілений з середнім значенням 200 і стандартним відхиленням 30. Його вартість отримання посланника становить $ 25000, і він продає посланника за $ 40000. Половина всіх посланців, не проданих за повною ціною, можуть бути продані за $ 30000. Він розглядає можливість замовлення 200, 220, 240, 260, 280 або 300 посланців. Скільки він повинен замовити?

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

   Попит	імовірність
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Супермаркет платить $1,00 за кожну копію Люди і продає її за $1,95. Кожну непродану копію можна повернути за 0,50 $. Скільки копій Люди має замовити магазин?