Управление встречами, планами, бюджеты — это просто с помощью Microsoft 365.

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

Эта статья была адаптирована на основе анализа данных и бизнес-моделирования в Microsoft Excel от Wayne L. Winston.

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

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

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

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

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

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

Примечание:  Моделирование по имени Монте-Карло происходит из моделирования компьютеров, которые выполнялись в 30-40-е и 30-е гг. для оценки вероятности успешной реакции на цепочку, необходимую для ампилации. Этот проект был большим поклонником и был очень большим поклонником, поэтому они предоставили моделирование с кодовым именем Монте-Карло.

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

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

  • Для оценки средней прибыли и фактора риска для новых продуктов можно использовать функции "Общее моделирование", "Проктор" и "Сиреневый", "Pfizer", "Bristol-Myers Squibb" и "Илья Литов". В gm эта информация используется директором для определения продуктов, которые должны выйти на рынок.

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

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

  • Для моделирования и оптимального хэджирования риска внешнего обмена используется моделирование и моделирование.

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

  • Компании, работающие с альтернативами, используют моделирование, чтобы получить значение "реальных вариантов", например значение параметра для расширения, срывов или отложенных проектов.

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

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

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

Примечание:  При Randdemo.xlsx файле случайные числа, показанные на рисунке 60-1, будут не одинаковы. Функция СЛЧИС всегда автоматически пересчитывает числа, которые она генерирует при открытие или при входе в него новых данных.

Сначала скопируйте формулу =СЛДД()из ячейки C3 в ячейку C4:C402. Затем назовем диапазон C3:C402 Data (ДанныеC3:C402). Затем в столбце F можно отследить среднее из 400 случайных чисел (ячейка F2) и использовать функцию СЧЁТЕФ для определения дробей от 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

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

Если ввести в ячейку формулу НОРМОРВ(слдд(),му,сигма),будет сгенерировано имитация обычной случайной переменной со значением "му" и стандартным отклонением сигмы. Эта процедура показана в файле Normalsim.xlsx рисунке 60-3.

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

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

По сути, для случайного числа xформула НОРМОРВ(p;му;сигма) генерирует 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.

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

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

Количество проданных товаров меньше количества и потребности в продукции. В ячейке C8 вычисляется доход с помощью формулы МИН(произведено;потребность)*unit_price. В ячейке C9 вычисляются общие производственные затраты с помощью формулы, произведенной*unit_prod_cost.

Если мы выпускаем больше карточек, чем это нужно, количество остатарных единиц равно производственному минусу. в противном случае единицы не будут остаться. Затраты на удаление в ячейке C10 вычисляются с помощью формулы unit_disp_cost*ЕСЛИ(произведенная>запроса;произведена-потребность;0). Наконец, в ячейке C11 мы вычисляем прибыль как доход — total_var_cost-total_disposing_cost.

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

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

В диапазоне ячеок A16:A1015 введите числа от 1 до 1000 (соответствует 1000 пробных испытаний). Чтобы создать эти значения, просто введите 1 в ячейку A16. Выберите ячейку, а затем на вкладке "Главная" в группе "Редактирование" нажмите кнопку "Заполнить" и выберите "Ряд", чтобы отобразить диалоговое окно "Ряд". В диалоговом окне "Ряд", показанном на рисунке 60-6, введите шаг 1 и значение остановки 1000. В области "Ряд" выберите параметр "Столбцы" и нажмите кнопку "ОК". Числа от 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) в качестве ячейки ввода столбца. Нажав кнопку "ОК", Excel имитирует 1000 значений запроса для каждого количества заказов.

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

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

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

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

  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 долларов США. Сколько копий людей должно храниться в магазине?

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

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

Нужна дополнительная помощь?

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

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

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

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

×