Загальні відомості про Monte Carlo моделювання в Excel

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

У цій статті створеної Microsoft Excel аналіз і моделювання бізнес , Уейн Уїнстон л. Відвідайте Microsoft навчального , щоб дізнатися більше про цю книгу.

  • Хто користується Monte Carlo моделювання?

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

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

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

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

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

Примітка.: Назва Монте Карло моделювання походить від комп'ютерного моделювання, які виконуються під час 30-х і 40-х років оцінити ймовірність того, що мережа реакції, потрібні для дитинЁ підірвати зручний успішно. Фізиків, пов'язані з цією функцією були великі вентиляторів азартними іграми, тому їх дав моделювання ім'я коду Монте-Карло.

У наступні сім розділів ви побачите приклади використання Excel для виконання Монте-Карло.

Багато компаній використовувати Monte Carlo моделювання важлива їх процес прийняття рішень. Нижче наведено кілька прикладів.

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

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

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

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

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

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

  • За допомогою Monte Carlo моделювання фінансового планування визначити оптимальний інвестиційних стратегій на пенсійні рахунки своїх клієнтів.

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

Book Image
Фігура 60-1, демонструючи функції RAND

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

По-перше, скопіюйте клітинки C3 C4:C402 формули =RAND(). Назвіть діапазон C3:C402 даних. Потім у стовпці 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 на наступній сторінці.

Book Image
Фігура 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 створює вимогу 10000, будь-який випадкове число між 0.10 та 0.45 створює вимогу 20 000 і так далі. У діапазоні F8:F11 за допомогою функції COUNTIF для визначення дріб наші 400 ітерацій отримуючи кожного замовлення. Якщо ми натисніть клавішу F9, щоб повторно обчислити випадкових чисел, зімітуйте ймовірності є закрити, щоб наші очікуваною вимогу ймовірності.

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

Book Image
Рисунок 60 3 моделювання звичайний випадкова змінна

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

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

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

Вимога

Імовірність

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

The greeting card sells for $4.00, and the variable cost of producing each card is $1.50. Залишилися картки має реалізації вартістю 0,20 $ за картки. Можна друкувати скільки карт?

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

Book Image
Фігура 60-4 дня Святого Валентина картки моделювання

Ви можете ввести виробництва кількість (40 000 у цьому прикладі) у клітинці C1. Потім створіть випадкове число у клітинці 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 Очікуваний прибуток для кожного кількість. У цій ситуація одну, у якому двосторонній дані таблиці входить до нас на допомогу. (See Chapter 15, "Sensitivity Analysis with Data Tables," for details about data tables.) Таблиця даних, які використовуються в цьому прикладі показано на малюнку 60-5.

Book Image
Рисунок 60-5 двосторонній даних таблиці для вітальних листівок моделювання

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

Book Image
Рисунок 60-6 за допомогою ряду діалоговому вікні заповнити ознайомлювальну версію число від 1 до 1000

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

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

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

Шляхом копіювання клітинки B13 C13:E13 формули AVERAGE(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 карток, ми завжди продавати всі без будь-якого leftovers.

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

Довірчий інтервал для означає звіт про прибутки     Природним поставити запитання в цій ситуації є, на які інтервал ми 95 відсотків, що в діапазоні true означає звіт про прибутки? Цей інтервал називається 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
Рисунок 60-7 95% довірчий інтервал для означає звіт про прибутки під впорядковані 40 000 календарів

Ми працюємо 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

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

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

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×