Загальні відомості про імітацію Монте-Карло в Excel

Цю статтю було пристосовано з аналізу даних Microsoft Excel і бізнес-моделювання Уейн л. Уїнстона.

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

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

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

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

  • Як можна визначити кількість карток у листівці?

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

Примітка.:  Моделювання імені Монте-Карло походить від комп'ютерного моделювання, що виконується протягом 1930-х і 1940, щоб оцінити ймовірність того, що ланцюгова реакція, необхідна для бомби Atom, щоб підірвати роботу, успішно працюватиме. Фізикам, які беруть участь у цій роботі, були великі шанувальники азартних ігор, тому вони надали симуляторну назву Монте-Карло.

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

Для багатьох компаній використовується симуляція Монте-Карло як важлива частина процесу прийняття рішень. Нижче наведено кілька прикладів.

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

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

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

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

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

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

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

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

Book Image

Примітка.:  Під час відкриття файлу Rxdemo. XLSX ви не бачитимете ті самі випадкові числа, наведені на рисунку 60-1. Функція RAND завжди автоматично переобчислює числа, які він генерує, коли аркуш відкривається, або коли на аркуші вводиться нова інформація.

Спочатку скопіюйте з неї в 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 в різних клітинках, незалежні. Наприклад, якщо випадкове число, створене у клітинках, – це велике число (наприклад, 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

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

Book Image

Ключем до нашого моделювання є використання випадкових чисел для ініціювання підстановки з діапазону таблиць 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), ви будете генерувати імітована величина звичайної випадкової змінної, що має середню Сигма" MU " та "стандартне відхилення". Ця процедура ілюструється у файлі Northsim. XLSX, що відображається на рисунку 60-3.

Book Image

Припустімо, ми хочемо моделювати 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, Сигма) генерує р-й процентиль звичайної випадкової змінної, що має середнє значення MU та стандартне відхилення Сигма. Наприклад, випадкове число 0,77 у клітинках C4 (див. фігуру 60-3) генерує у клітинках B4 приблизно 73-й процентиль звичайної випадкової змінної з середнім значенням 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 ітерацій. Дані для цього розділу можна знайти в файлі Валентина. XLSX, що відображається на рисунку 60-4. Ви призначите імена діапазонів у клітинках B1: B11 to клітинки C1: C11. Діапазон для діапазону (G3): h6 призначено для пошукуімені. Наші параметри ціни та вартості збуту вводяться в клітинки C4: C6.

Book Image

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

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

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

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

Book Image

У діапазоні А16: A1015 (1000), у полі число 1 – 1000 (відповідно до наших ознайомлювальних випробувань). Один простий спосіб створити ці значення – це почати з введення 1 у стільниковому А16. Виділіть вміст, а потім на вкладці основне у групі редагування натисніть кнопку заливката виберіть пункт ряди , щоб відобразити діалогове вікно ряди . У діалоговому вікні " ряди ", що відображається на рисунку 60-6, введіть значення кроку 1 і значення "зупинити 1000". В області ряди виберіть параметр стовпці , а потім натисніть кнопку OK. Числа 1 – 1000 будуть введені у стовпець, починаючи з А16.

Book Image

Далі ми введемо наші можливі виробничі кількості (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 буде використано значення 20 000 у клітинці C1. У C16 значення клітинки "вхід у стовпець" в пустій клітинці, а випадкове число в клітинці C2 буде повторно розташовано. Після цього відповідний прибуток записується в C16 у стільниковому полі. Після цього значення параметра «вхідні» клітинки стовпця буде розташовано в пустій клітинці, а випадкове число у клітинці C2 знову обчислюється. Відповідний прибуток вводиться у стільниковій c17.

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

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

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

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

Book Image

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

Book Image

Ми 95 відсотків впевнені, що наша середня прибуток, коли замовлення на 40 000 календарів становить від $56 687 до $62 589.

  1. У дилера GMC ви побачите, що попит на 2005 посланників зазвичай поширюється на середнє значення 200 та стандартне відхилення від 30. Його вартість отримання Посланника – $25 000, і він продає посланцю за $40 000. Половина всіх посланців, які не продавалися за повною ціною, можна продати за $30 000. Він розглядає замовлення 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. Кількість копій людей , які мають порядок зберігання?

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

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

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

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

×