Возьмем простую модель, которая есть почти в любой компании: прибыль от продукта на следующий год. Все исходные данные в ней — оценки. Объем продаж, цена, затраты могут оказаться и лучше, и хуже. Обычная модель Excel показывает один результат и молчит о том, насколько он надежен. Метод Монте-Карло отвечает именно на этот вопрос.
Шаг 1. Обычная модель
Модель из четырех исходных чисел и одной формулы:
| Показатель | Значение |
|---|---|
| Объем продаж, шт. | 10 000 |
| Цена, руб./шт. | 1 000 |
| Переменные затраты, руб./шт. | 600 |
| Постоянные затраты, руб. | 2 800 000 |
| Прибыль = объем × (цена − переменные затраты) − постоянные затраты | 1 200 000 |
Модель говорит: прибыль 1,2 млн руб. Это и есть план. Но каждое число в таблице — лишь наиболее вероятное значение.
Шаг 2. Опишите неопределенность тремя оценками
Для каждого исходного показателя спросите у того, кто его оценивал, три числа: минимум, наиболее вероятное значение и максимум. Начинайте с крайних значений, а не с наиболее вероятного: так диапазоны получаются честнее (об этом — в статье об экспертной оценке рисков).
| Показатель | Минимум | Наиболее вероятно | Максимум |
|---|---|---|---|
| Объем продаж, шт. | 8 000 | 10 000 | 13 000 |
| Цена, руб./шт. | 900 | 1 000 | 1 050 |
| Переменные затраты, руб./шт. | 560 | 600 | 700 |
| Постоянные затраты, млн руб. | 2,6 | 2,8 | 3,3 |
Обратите внимание: диапазоны несимметричны. Цене есть куда падать (скидки крупным клиентам), но почти некуда расти. Затраты, наоборот, чаще растут, чем снижаются. Это типичная картина, и она сильно влияет на результат.
Шаг 3. Замените числа случайными формулами
Три оценки превращаем в распределение вероятностей. Для экспертных оценок удобно PERT-распределение. В обычном Excel его можно получить через бета-распределение:
=БЕТА.ОБР(СЛЧИС(); 1+4*(Вероятно-Мин)/(Макс-Мин); 1+4*(Макс-Вероятно)/(Макс-Мин); Мин; Макс)
Для объема продаж с оценками 8 000, 10 000 и 13 000 это =БЕТА.ОБР(СЛЧИС();2,6;3,4;8000;13000). При каждом пересчете (клавиша F9) функция СЛЧИС дает новое случайное число от 0 до 1, а БЕТА.ОБР превращает его в правдоподобное значение объема: чаще около 10 000, реже у краев диапазона. Формула прибыли не меняется, она просто считает от новых исходных данных.
Шаг 4. Пересчитайте модель 5000 раз
Один пересчет — один сценарий. Чтобы увидеть картину целиком, нужны тысячи сценариев. В Excel для этого есть два способа.
- Таблица данных. В столбец впишите номера итераций от 1 до 5000, в ячейку над соседним столбцом — ссылку на прибыль. Выделите оба столбца и выберите «Данные → Анализ "что если" → Таблица данных», в поле «Подставлять значения по строкам в» укажите любую пустую ячейку. Excel пересчитает модель для каждой строки, и в каждой строке окажется своя случайная прибыль.
- Строка на итерацию. Скопируйте случайные формулы и формулу прибыли в 5000 строк. Так сделано в файле к статье: способ нагляднее, хотя и объемнее.
Совет: в «Параметрах вычислений» включите режим «Автоматически, кроме таблиц данных», иначе Excel будет пересчитывать 5000 сценариев после каждой правки.
Шаг 5. Посчитайте статистику
Столбец из 5000 значений прибыли — это и есть результат симуляции. Его описывают несколькими формулами:
| Что считаем | Формула Excel |
|---|---|
| Среднее значение | =СРЗНАЧ(E2:E5001) |
| P10 и P90 (диапазон, в который результат попадает с вероятностью 80 %) | =ПРОЦЕНТИЛЬ.ВКЛ(E2:E5001;0,1), …;0,9) |
| Вероятность не выполнить план 1,2 млн руб. | =СЧЁТЕСЛИ(E2:E5001;"<1200000")/5000 |
| Вероятность убытка | =СЧЁТЕСЛИ(E2:E5001;"<0")/5000 |
Гистограмму строит «Вставка → Диаграммы → Гистограмма» прямо по столбцу результатов.
Что показал расчет
Результаты нашего прогона:
- План выполняется только в 37 % сценариев. В 63 % случаев прибыль окажется ниже 1,2 млн руб.
- Средняя прибыль — 1,03 млн руб., на 14 % меньше плана. Причина — несимметричные диапазоны: неблагоприятные отклонения больше благоприятных, а план собран из наиболее вероятных значений.
- С вероятностью 80 % прибыль будет от 0,34 до 1,75 млн руб. (P10 и P90).
- Вероятность убытка — около 2 %. Проект почти наверняка прибыльный, но план по прибыли амбициозен.
Если повторить расчет, цифры немного изменятся: в каждом прогоне свои случайные числа. При 5000 итерациях разница обычно в пределах одного-двух процентных пунктов.
Какие факторы важнее всего
Симуляция показывает и то, от чего зависит результат. Отберите сценарии, в которых фактор оказался в нижних 10 % своего диапазона, и сценарии, где он в верхних 10 %, и сравните среднюю прибыль. Получится торнадо-диаграмма:
Сильнее всего результат зависит от объема продаж, затем от цены и переменных затрат. Постоянные затраты влияют меньше всего. Значит, уточнять прогноз и снижать риск стоит в первую очередь в продажах: предварительные договоры с клиентами дадут больше, чем экономия на аренде.
Где обычный Excel перестает справляться
Для учебной модели из четырех переменных формул Excel хватает. На рабочих моделях появляются проблемы:
- Скорость. Модель на несколько листов, пересчитанная 5000 раз через таблицу данных, считается минутами, а иногда и часами.
- Корреляции. В жизни цена и объем продаж связаны: скидка увеличивает объем. Встроенными функциями такую связь задать трудно, а без нее диапазон результата искажается (см. статью о корреляции рисков).
- Воспроизводимость. СЛЧИС нельзя зафиксировать, поэтому каждый прогон дает немного другие цифры. Для отчета перед инвестиционным комитетом это неудобно.
- Набор распределений. В Excel около двух десятков обратных функций распределений, а в реальных задачах нужны и другие, и подбор распределения по данным.
- Проверка. Формулы с БЕТА.ОБР(СЛЧИС();…) тяжело читать и легко испортить при копировании.
Тот же пример в МоделРиск
МоделРиск — надстройка Excel, которая берет эту работу на себя. Модель остается той же, меняются только ячейки с исходными данными:
Объем: =VoseInput("Объем продаж")+VosePERT(8000;10000;13000)
Цена: =VoseInput("Цена")+VosePERT(900;1000;1050)
Прибыль: =VoseOutput("Прибыль")+C3*(C4-C5)-C6
Таблица данных и тысячи строк не нужны: кнопка «Старт» на ленте запускает симуляцию, а окно результатов показывает гистограмму, кумулятивную кривую, статистику и торнадо-диаграмму. Нужные цифры можно вывести прямо в ячейки функциями =VoseSimMean(C7), =VoseSimPercentile(C7;0,9) и =VoseSimProbability(C7;1200000).
В файле к статье оба варианта модели лежат рядом: лист на формулах Excel работает без надстроек, лист с функциями МоделРиск — в пробной версии.
Файлы к статье
Частые вопросы
Сколько итераций нужно для симуляции Монте-Карло в Excel?
Для среднего значения и диапазона P10–P90 обычно достаточно 1000–5000 итераций. Для хвостов распределения (P99 и дальше) нужны десятки тысяч. Проверка простая: запустите симуляцию дважды; если ключевые показатели заметно отличаются, итераций мало.
Можно ли сделать Монте-Карло в Excel без надстроек?
Да. Случайные значения дают функции СЛЧИС, БЕТА.ОБР, НОРМ.ОБР и другие обратные функции распределений, а многократный пересчет — таблица данных или отдельная строка на каждую итерацию. Ограничения начинаются с ростом модели: медленный пересчет, нет корреляций, анализа чувствительности и воспроизводимых результатов.
Почему среднее значение по симуляции меньше плана?
План обычно собирают из наиболее вероятных значений. Если диапазоны несимметричны (затраты чаще растут, чем падают, а цена чаще снижается, чем растет), среднее результата смещается в худшую сторону. В примере план — 1,2 млн руб., а среднее по 5000 сценариям — 1,03 млн руб.
Какое распределение выбрать, если есть только минимум, наиболее вероятное значение и максимум?
Чаще всего PERT-распределение: оно гладкое и меньше, чем треугольное, переоценивает крайние значения. Подробности — в статье о треугольном и PERT-распределении.
МоделРиск
МоделРиск — надстройка Excel для анализа рисков методом Монте-Карло. Пробная версия на 30 дней без ограничений.