Моделирование рисков методом Монте-Карло

В состав Gnumeric входит средство для моделирования по методу Монте-Карло. При использовании метода Монте-Карло используются выборки случайных чисел для решения задач, в которых течение времени не играет существенной роли. Другими словами, на очередную выборку не влияют предыдущие выборки.

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

В данной главе рассмотрен пример из официального руководства по Gnumeric.

9.1 Общее описание задачи

Одна из классических расчётных задач – задача о продавцах газет. Продавцы покупают газеты за 33 цента каждую и продают по 50 центов. Непроданные газеты идут на макулатуру по 5 центов за штуку. Газеты продаются распространителям пачками по 10 штук. Спрос на газеты может быть поделён на "замечательный", "нормальный" и "плохой" с вероятностями 0.35, 0.45 и 0.20 соответственно, причём текущий спрос не зависит от предыдущего дня. Задача продавца – определить оптимальное количество газет в ситуации, когда спрос не вполне известен, то есть добиться устойчивого дохода.

Уравнение для определения дневного дохода для продавца выглядит следующим образом:

Доход=[(Выручка) - (Себестоимость) + (Макулатура)]

Остаётся добавить, что количество закупленных от поставщика газет может изменяться от 40 до 100 включительно, а количество проданных газет также кратно 10.

9.2 Построение модели

Для построения модели в Gnumeric будем использовать два листа – лист "Доход" для вычисления дохода и лист "Таблицы спроса" для таблиц, требуемых для модельных наборов данных, задающих параметры спроса.

На листе "Доход" создадим таблицу расчёта дохода, как показано на рис. 9.1.

Таблицу для вычисления дохода начнём с девятой строки. У нас есть три переменные – выручка от продаж, себестоимость газет и стоимость макулатуры, для которых на каждую единицу товара заданы коэффициенты 0.5, 0.33 и 0.05 соответственно. Запишем эти коэффициенты в ячейки от B13 до D13. В ячейках от B12 до D12 запишем формулы для дохода от продаж, себестоимости и стоимости макулатуры, как показано в таблице 1. В ячейке E12 запишем формулу для вычисления прибыли.

Нужно заметить, что на этом этапе в некоторых ячейках появятся сообщения "N/A!" ("нет данных"). Как только модель будет построена полностью, эти сообщения исчезнут.

В ячейке B20 будет задаваться случайное количество проданных газет ("спрос"). Поскольку нельзя продать больше, чем закуплено у поставщика, выручка определяется количеством проданных газет, если закуплено больше, чем продано и ограничивается количеством закупленных газет, если спрос превышает это количество (функция min() при расчёте выручки).

Формула в ячейке D12 означает, что в макулатуру можно сдать только непроданные газеты, поэтому если всё продано (а также если спрос превышает количество закупленных газет), то количество макулатуры будет 0.

Начальное количество закупленных газет установим в 50.

Далее на листе "Таблицы спроса" сформируем модельные параметры спроса в соответствии с рис 9.29.4.

Термин "вероятность" в рассматриваемом примере означает значение функции плотности распределения, а "интегральная вероятность" – значение функции распределения.

На рис. 9.2 показаны плотность распределения и значения функции распределения уровней спроса, на рис 9.3 – функции плотности распределения вероятности продажи заданного количества газет для каждого из вариантов спроса, а на рис. 9.4 – вспомогательные функции распределения вероятностей спроса.

Дополнительными допущениями является то, что 40 газет будут проданы при любых условиях, а вот 100 – только при наиболее благоприятных обстоятельствах.

Теперь снова перейдём на лист "Доход" и продолжим ввод формул, нужных для работы модели. Адреса ячеек и соответствующие формулы показаны в таблице 9.2.

Случайное число в ячейке B17 определяет уровень (состояние) спроса, который выводится в ячейку C17. Случайное число в ячейке B18 определяет количества проданных газет для каждого уровня спроса (ячейки B24, C24 и D24). В соответствии с ранее заданным в C17 уровнем спроса в ячейке B20 получаем текущий спрос на газеты, из которого уже рассчитывается доход.

Итоговый вариант листа "Доход" должен выглядеть примерно так, как показано на рис. 9.5.

Нажимая на клавишу F9 ("Правка/Пересчитать" в главном меню) можем наблюдать за изменением чисел и, соответственно, за изменением дохода.

9.3 Использование модели

Понятно, что наблюдать за изменениями чисел в ячейках ЭТ при пересчёте очень увлекательно, но не очень продуктивно. Инструмент моделирования в Gnumeric ("Сервис/Моделирование..." в главном меню) позволяет автоматически проделать большое число пересчётов и проанализировать получаемые результаты.

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

Вкладка "Переменные" (рис. 9.6) обеспечивает определение диапазонов входных и выходных данных. Для рассматриваемого примера эти диапазоны должны быть определены в соответствии с рис. 9.6.

На вкладке "Параметры" (рис. 9.7) настраивается режим вычислений – определяется количество вариантов модели ("Rounds"), количество пересчётов (итераций) для каждого варианта и предел времени, в течение которого проводятся вычисления (в секундах). Предел времени нужен для предотвращения слишком длительной загрузки вычислительной системы. Если модель очень сложна или составлена некорректно, вычисления прекратятся по достижении этого предела времени.

Использование вариантов модели ("Rounds") будет рассмотрено позднее.

Вкладка "Вывод" (рис. 9.8) является традиционной для Gnumeric и позволяет задать расположение результатов работы программы. В нашем случае целесообразно использовать вариант "Новый лист".

После нажатия на кнопку "ОК" в диалоге "Моделирование рисков" диалоговое окно не закрывается, и на вкладке "Итог" можно увидеть, что появились какие-то результаты (рис. 9.9).

В области "Сводка результатов:" вкладки "Итог" можно увидеть значения переменных, полученные в результате выполнения указанного в области "Итог моделирования" количества итераций. Изучать эти значения в области вкладки диалогового окна не очень удобно, поэтому имеет смысл закрыть диалог и посмотреть результаты на появившемся в книге ЭТ листе "Отчёт о моделировании (1)" (рис. 9.10).

В правой части этого листа имеется ещё несколько статистических параметров, полученных в результате вычислений. Их можно получить, проделав все описанные выше действия, а назначение этих параметров (на английском языке) описано в оригинальном руководстве по Gnumeric, поэтому здесь они не приводятся.

Полученный результат можно интерпретировать следующим образом: "Если продавец будет покупать 50 газет, то его доход будет варьироваться от 4 до 8,5 у.е. и в среднем будет составлять 7,825 у.е. Доход будет не менее 4 у.е. в самых неблагоприятных условиях, но не более 8,5 у.е. – в самых благоприятных".

Теперь можно проделать аналогичные действия, установив количество закупаемых от поставщика газет (ячейка B16 на листе "Доход") в 50, 60 и т. д. Однако процесс перебора количества закупаемых газет тоже можно автоматизировать. Для этого в Gnumeric имеется функция SIMTABLE(), находящаяся в категории "Случайные числа".

Для того, чтобы просчитать модель с различными количествами закупаемых газет, запишем в ячейку B16 формулу =simtable(50;60;70;80;90) (крайние случаи с количествами 40 и 100 брать не будем).

Поскольку в этом случае имеется 5 вариантов модели, то на вкладке настройки режима вычислений диалога "Моделирование рисков" требуется изменить значение "Last round #:" в соответствии с количеством аргументов функции SIMTABLE() (рис. 9.11).

Следует заметить, что номера вариантов совпадают с номерами аргументов функции SIMTABLE(). Поэтому, если хочется посмотреть, что будет при количествах закупленных газет от 60 до 80, то в качестве параметра "First round #:" нужно поставить 2, а для "Last round #:" – 4.

После нажатия на кнопку "ОК" на вкладке "Итог" окажутся активными кнопки "Next Sim." и "Prev. Sim" (рис. 9.12), которые позволяют в области "Сводка результатов" видеть результаты по каждому варианту модели (при каждом значении количества закупленных газет из набора значений, определённых в качестве аргументов функции SIMTABLE()).

Полная сводка результатов отображается на листе "Отчёт о моделировании (1)" (рис. 9.13), на котором поместился только фрагмент сводки результатов, поэтому основные результаты приведены в таблице 3.

Из таблицы 3 видно, что закупка 60 газет даёт наиболее надёжный доход – средний доход максимален и никогда не получается убытка.

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

Очевидно, что такой результат получается при параметрах модели, заданных на листе "Таблицы спроса". При других вероятностях и распределениях вероятностей результаты также будут отличаться.

В заключение этой главы хотелось бы заметить, что программные средства для экономического моделирования стоят тысячи долларов США даже при условии их использования в образовательных целях (а для использования в коммерческих целях – значительно дороже). Поэтому использование Gnumeric (да и вообще свободного программного обеспечения) и для такого рода задач может оказаться привлекательным.