Эта статья была адаптирована из книги Уэйна Л. Уинстона «Анализ данных и бизнес-моделирование Microsoft Excel ».
Обзор
- Кто использует моделирование по методу Монте-Карло?
- Что происходит при вводе функции =СЛЧИС () в ячейке?
- Как смоделировать значения дискретной случайной величины?
- Как можно имитировать значения нормальной случайной величины?
- Как компания, производящая поздравительные карт, может определить, сколько открыток производить?
Мы хотели бы точно оценить вероятности неопределенных событий. Например, какова вероятность того, что денежные потоки нового продукта будут иметь положительную чистую приведенную стоимость (ЧПС)? Каков фактор риска нашего инвестиционного портфеля? Моделирование по методу Монте-Карло позволяет нам моделировать ситуации, которые представляют неопределенность, а затем воспроизводить их на компьютере тысячи раз.
Примечание
Название «моделирование Монте-Карло » происходит от компьютерного моделирования, выполненного в 1930-х и 1940-х годах для оценки вероятности того, что цепная реакция, необходимая для взрыва атомной бомбы, будет успешно работать. Физики, участвовавшие в этой работе, были большими поклонниками азартных игр, поэтому дали симуляциям кодовое название Монте-Карло.
В следующих пяти главах вы увидите примеры того, как вы можете использовать Excel для выполнения моделирования по методу Монте-Карло.
Кто использует моделирование по методу Монте-Карло?
Многие компании используют моделирование по методу Монте-Карло как важную часть процесса принятия решений. Вот несколько примеров.
- General Motors, Proctor and Gamble, Pfizer Bristol-Myers Squibb и Eli Lilly используют моделирование для оценки как средней доходности, так и фактора риска новых продуктов. В GM эта информация используется генеральным директором для определения того, какие продукты выходят на рынок.
- GM использует моделирование для таких видов деятельности, как прогнозирование чистой прибыли корпорации, прогнозирование структурных и закупочных издержек, а также определение ее восприимчивости к различным видам риска (таким как изменения процентных ставок и колебания обменного курса).
- Lilly использует моделирование для определения оптимальной мощности завода для каждого препарата.
- Proctor and Gamble использует моделирование для моделирования и оптимального хеджирования валютных рисков.
- Sears использует моделирование, чтобы определить, сколько единиц каждой линейки продуктов следует заказать у поставщиков, например, количество пар брюк Dockers, которые должны быть заказаны в этом году.
- Нефтяные и фармацевтические компании используют моделирование для оценки «реальных опционов», таких как стоимость опциона на расширение, сокращение или отсрочку проекта.
- Специалисты по финансовому планированию используют моделирование по методу Монте-Карло для определения оптимальных инвестиционных стратегий для выхода своих клиентов на пенсию.
Что происходит при вводе функции =СЛЧИС () в ячейке?
При вводе формулы =СЛЧИС() в ячейке вы получите число, которое с одинаковой вероятностью примет любое значение от 0 до 1. Таким образом, примерно в 25 процентах случаев вы должны получить число, меньшее или равное 0,25; Примерно в 10 процентах случаев вы должны получить число, равное по крайней мере 0,90, и так далее. Чтобы продемонстрировать, как работает функция СЛУЧ, взгляните на Randdemo.xlsx файлов, показанный на рисунке 60-1.
Примечание
Когда вы откроете файл Randdemo.xlsx, вы не увидите те же случайные числа, что и на рисунке 60-1. Функция СЛЧИС всегда автоматически пересчитывает числа, которые она генерирует при открытии листа или при вводе в него новых данных.
Сначала скопируйте из ячейки C3 в C4:C402 формулу =СЛЧИС(). Затем назовите диапазон Данные 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. Эти результаты согласуются с определением случайного числа. Также обратите внимание, что значения, создаваемые функцией СЛЧИС в разных ячейках, являются независимыми. Например, если полученное в ячейке C3 случайное число является большим числом (например, 0,99), оно ничего не говорит о значениях других полученных случайных чисел.
Как смоделировать значения дискретной случайной величины?
Предположим, что потребность в календаре определяется следующей дискретной случайной величиной:
| Спрос | Вероятность |
|---|---|
| 10 000 | 0,10 |
| 20 000 | 0.35 |
| 40,000 | 0,3 |
| 60 000 | 0,25 |
Как мы можем заставить Excel воспроизводить или имитировать этот спрос на календари много раз? Хитрость заключается в том, чтобы связать каждое возможное значение функции СЛЧИС с возможным спросом на календари. Следующее назначение гарантирует, что потребность в 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;среднее;сигма) получает 400 различных значений испытаний из нормальной случайной величины со средним значением 40 000 и стандартным отклонением 10 000. При нажатии клавиши F9 для пересчета случайных чисел среднее значение остается близким к 40 000, а стандартное отклонение — около 10 000.
По существу, для случайного числа x формула НОРМИНВ(p,mu,сигма) генерирует p-юпроцентиль нормальной случайной величины со средним mu и сигма стандартного отклонения. Например, случайное число 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 с формулой =СЛЧИС(). Как описано ранее, вы моделируете спрос на карт в ячейке C3 с помощью формулы ВПР(rand,lookup,2). (В формуле ВПР СЛЧИС — это имя ячейки, назначенное ячейке C3, а не функция СЛЧИ.)
Количество проданных единиц меньше, чем наше количество продукции и спрос. В ячейке 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.
Копируя из ячейки 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-процентный доверительный интервал для среднего любого результата моделирования вычисляется по следующей формуле:
В ячейке J11 вычисляется нижний предел 95-процентного доверительного интервала средней прибыли при создании 40 000 календарей по формуле D13–1,96*D14/SQRT(1000). В ячейке J12 верхний предел 95-процентного доверительного интервала вычисляется по формуле D13+1,96*D14/КОРЕНЬ(1000). Эти расчеты показаны на рисунке 60-7.
Мы на 95 процентов уверены, что наша средняя прибыль при заказе 40 000 календарей составляет от 56 687 до 62 589 долларов.
Проблемы
Дилер GMC считает, что спрос на Envoys 2005 года будет нормально распределен со средним значением 200 и стандартным отклонением 30. Его стоимость получения посланника составляет 25 000 долларов, и он продает посланника за 40 000 долларов. Половина всех Посланников, не проданных по полной цене, может быть продана за 30 000 долларов. Он рассматривает возможность приказа о 200, 220, 240, 260, 280 или 300 посланниках. Сколько он должен заказать?
Небольшой супермаркет пытается определить, сколько экземпляров журнала People они должны заказывать каждую неделю. Они считают, что их спрос на People регулируется следующей дискретной случайной величиной:
Спрос Вероятность 15 0,10 20 0.20 25 0.30 30 0,25 35 0,15 Супермаркет платит 1,00 доллара за каждый экземпляр People и продает его за 1,95 доллара. Каждый непроданный экземпляр можно вернуть за 0,50 доллара. Сколько копий People должен заказать магазин?
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.