Office

Общие сведения о моделировании Монте Карло в Excel

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Эта статья была адаптирована к анализу данных Microsoft Excel и бизнес-моделированию с помощью Уэйн L. Уинстон.

  • Кто использует Монте Карло моделирования?

  • Что происходит при вводе = Rand () в ячейку?

  • Как можно смоделировать значения дискретной случайной переменной?

  • Как можно смоделировать значения обычной случайной переменной?

  • Как компания поздравительной открытки может определить, сколько карт нужно создать?

Мы хотели бы точно оценить вероятность неопределенных событий. Например, какова вероятность того, что у нового продукта для денежных потоков будет положительная чистая текущая стоимость (ЧПС)? Какой фактор риска для нашего портфеля капиталовложений? Эмуляция Карло Монте позволяет моделировать ситуации, которые представляют неопределенность и затем воспроизводить их на компьютере в тысячах моментов.

Примечание:  Эмуляция Карло Name Монте — это имитация компьютера, выполняемая во время 1930s и 1940s, чтобы оценить вероятность того, что реакция на цепочку, необходимая для Atom бомб, детонате, будет успешно работать. ФисиЦистс, вовлеченные в эту работу, – это большие вентиляторы азартных игр, поэтому они применяют эмуляцию имени кода Монте Карло.

В следующих пяти главах вы увидите примеры того, как можно использовать Excel для выполнения эмуляции Монте Карло.

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

  • Общие моторы, Проктор и Гамбле, Пфизер, Бристол-МЕРС Скуибб и ели Лилли с помощью эмуляции, чтобы оценить как среднее возвращаемое значение, так и степень риска для новых продуктов. По GM эти сведения используются Генеральный директор для определения продуктов, которые поставляются на рынке.

  • Функция GM использует эмуляцию для таких действий, как прогнозирование чистого дохода для Организации, прогнозирование затрат на структуру и покупку и определение ее сусцептибилити с учетом различных видов риска (например, изменения процентной ставки и колебаний курсов валют).

  • Лилли использует эмуляцию для определения оптимальной мощности завода для каждого лекарства.

  • Проктор и Гамбле использует эмуляцию для моделирования и оптимизации внешнего Exchange.

  • Сеарс использует эмуляцию, чтобы определить, сколько единиц каждого товара нужно заказать от поставщиков (например, число пар дока траусерс, которые должны быть заказаны в этом году.

  • Компании с водои лекарствами используют эмуляцию для значения "реальные параметры", например значение параметра для развертывания, контракта или отсрочки проекта.

  • В финансовых планировщиках используется модель Монте Карло, чтобы определить оптимальные стратегии капиталовложений для их выбытия клиентов.

Когда вы вводите формулу = Rand () в ячейку, вы получаете число, которое одинаково может принимать значения от 0 до 1. Таким образом, около 25% времени вы должны получить число меньше или равное 0,25; около 10% времени вы должны получить число, которое не менее 0,90, и т. д. Чтобы продемонстрировать, как работает функция RAND, Взгляните на файл Ранддемо. xlsx, показанный на рисунке 60-1.

Изображение книги

Примечание:  Когда вы открываете файл Ранддемо. xlsx, вы не увидите одинаковые случайные числа, показанные на рисунке 60-1. Функция RAND всегда автоматически пересчитывает числа, создаваемые при открытии листа, или при вводе новых данных на лист.

Сначала скопируйте из ячейки C3 в C4: C402 формулу = Rand (). Затем назовите диапазон C3: C402 Data. Затем в столбце F можно отследить среднее арифметическое случайных чисел 400 (ячейка F2) и использовать функцию СЧЁТЕСЛИ для определения дробей между 0 и 0,25, 0,25 и 0,50, 0,50 и 0,75, а также 0,75 и 1. При нажатии клавиши F9 автоматически рассчитываются случайные числа. Обратите внимание, что среднее значение числа 400 всегда равно приблизительно 0,5, а около 25 процентов результатов — с интервалами в 0,25. Эти результаты соответствуют определению случайного числа. Также обратите внимание на то, что значения, созданные функцией СЛЧИС в разных ячейках, не зависят. Например, если случайное число, созданное в ячейке 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

Чтобы продемонстрировать имитацию спроса, Взгляните на файл Дискретесим. 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 формулу ВПР (C3, Lookup, 2). Эта формула гарантирует, что любое случайное число, меньшее 0,10, порождает требование 10 000, любое случайное число между 0,10 и 0,45 генерирует требование 20 000 и т. д. В диапазоне ячеек F8: F11 используйте функцию СЧЁТЕСЛИ, чтобы определить доли итераций 400, в результате чего потребуются все требования. При нажатии клавиши F9 для пересчета случайных чисел смоделированные вероятности будут близкы к предполагаемым вероятности спроса.

При вводе в любую ячейку формулы НОРМОБР (Rand (); MU; Сигма)будет создано имитируемое значение обычной случайной переменной со средним и стандартным отклонением " Сигма". Эта процедура показана в файле Нормалсим. xlsx, показанном на рисунке 60-3.

Изображение книги

Предположим, нужно смоделировать 400 пробных версий или итераций для обычной случайной переменной со средним 40 000 и стандартным отклонением 10 000. (Вы можете ввести эти значения в ячейки E1 и E2, а затем назовите эти ячейки соответственно.) При копировании формулы = Rand () из C4 в C5: C403 генерирует 400 различными случайными числами. Копирование из B4 в B5: B403 формула НОРМОБР (C4; среднее, сигма) формирует 400 разных пробных значений из обычной случайной переменной со средним 40 000 и стандартным отклонением 10 000. При нажатии клавиши F9 для пересчета случайных чисел среднее остается близким к 40 000, а стандартное отклонение — в 10 000.

По сути, для случайного числа xформула НОРМОБР (p; MU; Сигма) формирует p-ую процентиль обычной случайной переменной со средним значением " среднее" и стандартным отклонением " Сигма". Например, случайное число 0,77 в ячейке C4 (см. рис. 60-3) генерируется в ячейке B4 около 77th процентиль обычной случайной переменной со средним числом 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. Данные для этого раздела можно найти в файле любимая. xlsx, показанном на рисунке 60-4. Вы назначаете названия диапазонов в ячейках B1: B11 с ячейками C1: C11. Диапазон ячеек G3: H6 назначается подстановкой имени. Параметры цены продажи и цены введены в ячейки C4: C6.

Изображение книги

В ячейке C1 вы можете ввести количество для пробной продукции (40 000 в этом примере). Затем создайте случайное число в ячейке C2 с помощью формулы = Rand (). Как описано выше, вы моделируете спрос на карточку в ячейке C3 с помощью формулы ВПР (СЛЧИС, Lookup, 2). (В формуле ВПР — имя ячейки, присвоенное ячейке C3, а не функция RAND.)

Количество проданных единиц измерения — это меньший из наших производственных объемов и спроса. В ячейке C8 вы вычисляете наш доход с помощью формулы min (изготовленные, требования) * унит_прице. В ячейке C9 рассчитываются итоговые затраты на производство с помощью формулы, созданной * унит_прод_кост.

Если мы создаем больше карт, чем по запросу, число единиц осталось больше, чем равно "производство", а не по требованию. в противном случае никакие единицы не остались. Мы вычисляем стоимость реализации в ячейке C10 с помощью формулы унит_дисп_кост * если (продуцед_гт_деманд, изготовлено — Demand, 0). Наконец, в ячейке C11 мы вычисляем нашу прибыль как доход — тотал_вар_кост-тотал_диспосинг_кост.

Мы хотели бы эффективно нажать клавишу F9 много времени (например, 1000) для каждого производственного количества и в соответствии с предполагаемой прибылью для каждого количества. Это ситуация, из-за которой таблица данных, на которую поступило двустороннее, поступает на нашу помощь. (Подробные сведения о таблицах данных см. в главе 15 "анализ чувствительности с таблицами данных".) Таблица данных, используемая в этом примере, показана на рисунке 60-5.

Изображение книги

В поле диапазон ячеек A16: A1015 введите числа 1 – 1000 (в соответствии с нашими 1000). Один из простых способов создать эти значения — начать с ввода 1 в ячейку A16. Выделите ячейку, а затем на вкладке Главная в группе Редактирование нажмите кнопку заливКа и выберите пункт ряды , чтобы открыть диалоговое окно ряд . В диалоговом окне ряд , показанном на рисунке 60-6, введите значение шага 1 и значение остановки 1000. В области ряды выберите параметр столбцы , а затем нажмите кнопку ОК. Числа 1 – 1000 будут введены в столбец A, начиная с ячейки A16.

Изображение книги

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

Теперь мы можем заставить Excel моделировать 1000 итераций спроса для каждого производственного количества. Выберите диапазон таблицы (A15: E1014), а затем в группе Работа с данными на вкладке Данные нажмите кнопку Анализ гипотетических вариантов и выберите пункт Таблица данных. Чтобы настроить двустороннюю таблицу данных, выберите в качестве ячейки ввода строк поле производственное количество (Ячейка C1) и выберите любую пустую ячейку (в качестве ячейки ввода столбца будет выбрана ячейка I14). После нажатия кнопки ОК программа Excel эмулирует значения спроса на 1000 для каждого количества заказов.

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

Скопировав из ячейки B13 в C13: E13 формулу СРЗНАЧ (B16: B1015), мы вычисляют среднюю величину моделирования прибыли для каждого производственного количества. Скопировав из ячейки B14 в C14: E14 формулу СТАНДОТКЛОН (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% для среднего выводящихся данных моделирования вычисляется по следующей формуле:

Изображение книги

В ячейке J11 вычисляется наименьшее значение интервала, установленного в 95 процента, для доходности, при создании календарей 40 000 с помощью формулы D13 — 1.96 * D14/Sqrt (1000). В ячейке J12 можно вычислить верхнюю границу для нашего 95 процента доверительного интервала с формулой D13 + 1.96 * D14/Sqrt (1000). Эти вычисления показаны на рисунке 60-7.

Изображение книги

Мы 95%! убедитесь в том, что наши средние прибыли при заказе 40 000 календарей находятся между $56 687 и $62 589.

  1. ГМК дилер считает, что спрос на делегаты 2005 обычно распространяется со средним 200 и стандартным отклонением 30. Стоимость приема представителем — $25 000, а он продает представителю по $40 000. Половина всех делегатов, не проданных по полной цене, может быть продана для $30 000. Он рассматривает заказ 200, 220, 240, 260, 280 или 300 представителей. Сколько нужно для этого заказа?

  2. Небольшой автотрассе пытается определить количество копий журнала пользователей , которые они должны заказать на каждую неделю. Они считают, что их спрос на сотрудников регулируется следующей дискретной переменной случайной величины.

    Подключения

    Вероятность

    15

    0,10

    20

    0,20

    24

    0,30

    30

    0,25

    35

    0,15

  3. Автотрассе оплачивает $1,00 для каждой копии людей и продает их в $1,95. Каждая нереализованная копия может быть возвращена для $0,50. Сколько копий пользователей должен заказать заказ на хранение?

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×