Введение в моделирование Монте-Карло в Excel

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

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

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

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

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

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

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

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

• General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb и Eli Lilly используют имитацию для оценки как средней прибыли, так и фактора риска новых продуктов. В GM эта информация используется генеральным директором для определения продуктов, поступающих на рынок.

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

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

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

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

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

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

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

Сначала скопируйте из ячейки 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. Эти результаты согласуются с определением случайного числа. Также обратите внимание, что значения, создаваемые 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 на следующей странице.

Ключом к моделированию является использование случайного числа для запуска подстановки из табличного диапазона 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 используйте функцию СЧЁТЕСЛИ, чтобы определить долю из 400 итераций, возвращающих каждое требование. При нажатии клавиши F9 для пересчета случайных чисел смоделированные вероятности близки к предполагаемым вероятностям спроса.

Если в любой ячейке ввести формулу NORMINV(rand(),mu,sigma), вы создадим имитированное значение обычной случайной переменной, имеющей среднее значение mu и стандартное отклонение сигмы. Эта процедура показана на Normalsim.xlsx файла, показанном на рис. 60-3.

Предположим, мы хотим имитировать 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,sigma) создает 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.

В ячейку C1 можно ввести пробное количество (40 000 в этом примере). Затем создайте случайное число в ячейке C2 с формулой =RAND(). Как описано ранее, вы имитируете спрос на карта в ячейке C3 с помощью формулы VLOOKUP(rand,lookup,2). (В формуле VLOOKUP rand — это имя ячейки, назначенное ячейке C3, а не функция RAND.)

Количество проданных единиц является меньше нашего количества и спроса на продукцию. В ячейке C8 вы вычисляете наш доход с помощью формулы MIN(производится,спрос)*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 формулу 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 карт, мы всегда будем продавать их все без остатков.

Доверительный интервал для средней прибыли      Естественный вопрос, который следует задать в этой ситуации, заключается в том, в какой интервал мы 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 до $ 62589.

1. Дилер GMC считает, что спрос на 2005 Envoys будет обычно распределяться со средним значением 200 и стандартным отклонением в 30. Его стоимость получения посланника составляет $ 25000, и он продает посланника за $ 40000. Половина всех посланников, не проданных по полной цене, может быть продана за $ 30000. Он рассматривает вопрос о заказе 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 долл. США. Сколько копий Люди заказ на хранение?