Вернуться в Оглавление
ПРИМЕНЕНИЕ ТАБЛИЧНОГО ПРОЦЕССОРА MS EXCEL ДЛЯ ИЗУЧЕНИЯ МЕТОДА МОНТЕ-КАРЛО
Для моделирования различных физических, экономических и других процессов широко распространены методы, называемые методами Монте-Карло. В их основе лежит метод статистических испытаний. Суть его состоит в том, что результат испытания ставится в зависимость от значения некоторой случайной величины, распределенной по заданному закону. Поэтому результат каждого отдельного испытания носит случайный характер.
Особенность метода состоит в том, что он гарантирует высокое качество статистических оценок только при весьма большом числе испытаний, которое невозможно выполнить без помощи компьютера.
Табличные процессоры не очень удобны для проведения расчетов Монте-Карло, однако с их использованием можно достаточно просто проиллюстрировать основные особенности этого метода.
Применение метода Монте-Карло для вычисления площади круга
Рассмотрим применение этого метода для вычисления площади круга заданного радиуса. Данная задача хорошо иллюстрирует возможности метода. Пусть круг имеет радиус R = 1 (рис. 1). Уравнение соответствующей окружности имеет вид: (x – 1)+ (y – 1)= 1. (1)
Для решения задачи методом Монте-Карло впишем круг в квадрат. Вершины квадрата будут иметь координаты (0,0), (2,0), (0,2), (2,2). Любая точка внутри квадрата или на его границе должна удовлетворять неравенствам 0 < x < 2 и 0 < y < 2 . При случайном заполнении квадрата точками, координаты которых распределены равномерно в этих интервалах, часть точек будет попадать внутрь круга. Если выборка состоит из n наблюдений и m точек попали внутрь круга или на окружность, то оценку площади круга S можно получить из
соотношения
S = S m / n (2)
где S – площадь квадрата, в который вписан круг.
В Excel с помощью функции СЛЧИС() можно получать равномерно распределенные случайные числа в диапазоне от 0 до 1. Для получения значений x и y в нужном диапазоне следует вводить формулы =2*СЛЧИС().
Число точек, попавших внутрь круга или на окружность, можно подсчитать, использовать функцию ЕСЛИ. Если координаты x и y таковы, что
(x – 1) + (y – 1) ≤ 1 , тогда функция будет возвращать 1, иначе 0. Тогда число m в формуле (2) для площади круга определится как сумма всех значений, возвращаемых функцией ЕСЛИ, а число n равно числу испытаний, которое можно подсчитать с помощью функции СЧЕТ. Только при большом числе испытаний можно получить близкое к точному значение равное π /4 =0, 7854.
Поэтому нужными формулами необходимо заполнить сразу большое число строк, например 500. Так будет выглядеть электронная таблица в режиме отображения формул:
| А | В | С | D | |
| Х | У | =СУММ(С3:С502) | =C1/C2 | |
| =СЧЁТ(С3:С502) | ||||
| =2*СЛЧИС() | =2*СЛЧИС() | =ЕСЛИ(А3^2+B3^2<=1;1;0) | ||
| … | … | … | … | |
| =2*СЛЧИС() | =2*СЛЧИС() | =ЕСЛИ(А502^2+В502^2<=1;1;0) |
В ячейке D1 будет находиться результат – площадь фигуры.
Вычисляя отношение m/n при нарастающем числе испытаний, можно сделать выводы, справедливые для любого статистического эксперимента независимо от природы и типа моделируемой системы:
С увеличением продолжительности наблюдения отклонение измеряемой
величины от ее точного значения уменьшается;
Существует предел, за которым увеличение продолжительности модели уже
не дает существенного повышения точности результата.
ЗАДАНИЕ
В соответствии с вариантом, методом Монте – Карло определить площадь фигур (см. рис. 1), и сравнить полученный результат с результатом, вычисленным по формуле.
| № варианта | |||||
| Фигура | Левая часть круга | Правая часть круга | Нижняя часть круга | Верхняя часть круга | Левая верхняя часть |
| № варианта | |||||
| Фигура | Левая верхняя часть круга | Правая верхняя часть круга | Правая нижняя часть круга | Левый верхний квадрант квадрата | Левый нижний квадрант квадрата |
| № варианта | |||||
| Фигура | Правый верхний квадрант квадрата | Правый нижний квадрант квадрата | Левый верхний треугольник | Правый верхний треугольник | Левый нижний треугольник |
| № варианта | |||||
| Фигура | Правый нижний треугольник | Верхняя половина квадрата | Нижняя половина квадрата | Левая половина квадрата | Правая половина квадрата |
Применительно к управлению Проектами, использование метода Монте – Карло позволяет нам оценить риск невыполнения проекта в срок или риск не уложиться в бюджет Проекта.
Рассмотрим сетевой график из лабораторной работы № и возьмем работы, формирующие критический путь.
| Работа | t о (i,j) | t нв (i,j) | t п (i, j): | t̄(i,j) |
| 0,1 | ||||
| 1,4 | ||||
| 4,5 | ||||
| 5,6 |
Длина критического пути равна 10 дням. Однако, учитывая, что каждая работа имеет оптимистическую и пессимистическую оценки длительности, встает вопрос, а какова вероятность выполнения Проекта за 10 дней или, например, за 12 дней?
Моделирование методом Монте-Карло – это способ решения подобных задач. Необходимо случайным образом выбрать в указанных интервалах (от t о (i,j) до t п (i, j)) длительностей работ значения, и рассчитать длительность Проекта. Одни результаты превысят 10 дней (или 12 дней), а другие окажутся меньше. Процент реализаций, не превышающих 10 дней (12 дней), и будет искомой вероятностью.
Для моделирования надо знать форму кривой распределения. Для разных величин больше подходят кривые одной формы, чем другой. Мы будем использовать кривую нормального (гауссова) распределения. Это колоколообразная кривая, на которой большинство возможных значений результатов группируются в центральной части графика и лишь немногие, менее вероятные, распределяются, сходя на нет к его краям (рис. 1).
Вот как выглядит нормальное распределение:
Рис.1. Нормальное распределение
Особенности:
Значения, располагающиеся в центральной части графика, более вероятны, чем значения по его краям;
Распределение симметрично; медиана находится точно посредине между верхней и нижней границами 90%-ного доверительного интервала (CI);
«хвосты» графика бесконечны; значения за пределами 90%-ного доверительного интервала маловероятны, но все же возможны.
Для построения нормального распределения в Excel можно воспользоваться функцией =НОРМРАСП(Х; Среднее; Стандартное_откл; Интегральная),
где Х – значение, для которого строится нормальное распределение;
Среднее – среднее арифметическое распределения; в нашем случае = 0;
Стандартное_откл – стандартное отклонение распределения; в нашем случае = 1;
Интегральная – логическое значение, определяющее форму функции; если аргумент «интегральная» имеет значение ИСТИНА, функция НОРМРАСП возвращает интегральную функцию распределения; если этот аргумент имеет значение ЛОЖЬ, возвращается функция плотности распределения; в нашем случае = ЛОЖЬ.
С нормальным распределением связано такое понятие, как стандартное отклонение. Рисунок 1 показывает, что в одном 90%-ном доверительном интервале насчитывается 3,29 стандартного отклонения.
В нашем примере создадим в электронной таблице генератор случайных чисел для каждого интервала значений (т.е. для каждой работы). Начнем с первой работы.
Воспользуемся формулой Excel: =НОРМОБР(вероятность;среднее;стандартное_откл),
где вероятность – вероятность, соответствующая нормальному распределению;
среднее – среднее арифметическое распределения;
стандартное_откл – стандартное отклонение распределения.
В нашем случае:
Среднее (медиана) = (Верхняя граница 90%-ного CI + Нижняя граница 90%-ного СI)/2 = (3+2)/2;
Стандартное отклонение = (Верхняя граница 90%-ного CI – Нижняя граница 90%-ного СI)/3,29 = (3-2)/3,29.
Таким образом, формула имеет вид:
НОРМОБР(СЛЧИС();(3+2)/2;(3-2)/3,29),
где СЛЧИС – функция, генерирующая случайные числа в диапазоне от 0 до 1;
(3+2)/2 – среднее арифметическое диапазона MS;
(3-2)/3,29 – стандартное отклонение.
На рис. 2 представлен вариант исходных данных в Excel для данной задачи.
Рис. 2. Исходные данные для решения задачи
На рис. 3 представлена та же таблица в виде формул.
Рис.3. Таблица Excel с формулами
Предполагая, что количество экспериментов равно 100, заполним формулами 100 строчек – с 3 по 102.
Учитывая, что суммарная длина пути лежит в диапазоне от 7 до 14, а нам надо определить вероятность события, что мы выполним Проект за 10 (или 12) дней, разобьем весь диапазон на следующие отрезки: 7 и менее дней, от 7 до 10 дней, от 10 до 12 дней, от 12 до 14 дней, 14 и более дней. Формулы для подсчета попадания испытания в соответствующий интервал занесем в столбцы H,I,J,K,L.
Результаты представлены на рис. 4, а формулы для подсчета результатов и диаграмма, иллюстрирующая их, представлены на рис. 5.
Рис. 4. Результаты расчетов
Рис. 5. Формулы для подсчета результатов и диаграмма
Итак, по результатам работы можно сделать вывод, что Проект с вероятностью 36% мы закончим за 10 дней и с вероятностью 89% (36%+53%) за 12 дней.
ЗАДАНИЕ
Рассчитать вероятность завершения Проекта (в соответствии с выбранным вариантом) за время t кр и за время, большее, чем t кр на 10%. (округлить в большую сторону до целого числа дней) . В качестве исходных данных, взять данные из лабораторной работы № .
Не так давно я прочитал замечательную книгу Дугласа Хаббарда . В кратком конспекте книги я обещал, что одному из разделов – Оценка риска: введение в моделирование методом Монте-Карло – я посвящу отдельную заметку. Да всё как-то не складывалось. И вот недавно я стал более внимательно изучать методы управления валютными рисками. В материалах, посвященных этой тематике, часто упоминается моделирование методом Монте-Карло. Так что обещанный материал перед вами.
Приведу простой пример моделирования методом Монте-Карло для тех, кто никогда не работал с ним ранее, но имеет определенное представление об использовании электронных таблиц Excel.
Предположим, что вы хотите арендовать новый станок. Стоимость годовой аренды станка 400 000 дол., и договор нужно подписать на несколько лет. Поэтому, даже не достигнув , вы всё равно не сможете сразу вернуть станок. Вы собираетесь подписать договор, думая, что современное оборудование позволит сэкономить на трудозатратах и стоимости сырья и материалов, а также считаете, что материально-техническое обслуживание нового станка обойдется дешевле.
Скачать заметку в формате , примеры в формате
Ваши калиброванные специалисты по оценке дали следующие интервалы значений ожидаемой экономии и годового объема производства:
Годовая экономия составит: (MS + LS + RMS) х PL
Конечно, этот пример слишком прост, чтобы быть реалистичным. Объем производства каждый год меняется, какие-то затраты снизятся, когда рабочие окончательно освоят новый станок, и т.д. Но мы в этом примере намеренно пожертвовали реализмом ради простоты.
Если мы возьмем медиану (среднее) каждого из интервалов значений, то получим годовую экономию: (15 + 3 + 6) х 25 000 = 600 000 (дол.)
Похоже, что мы не только добились безубыточности, но и получили кое-какую прибыль, но не забывайте – существуют неопределенности. Как же оценить рискованность этих инвестиций? Давайте, прежде всего, определим, что такое риск в данном контексте. Чтобы получить риск, мы должны наметить будущие результаты с присущими им неопределенностями, причем какие-то из них – с вероятностью понести ущерб, поддающийся количественному определению. Один из способов взглянуть на риск – представить вероятность того, что мы не добьемся безубыточности, то есть что наша экономия окажется меньше годовой стоимости аренды станка. Чем больше нам не хватит на покрытие расходов на аренду, тем больше мы потеряем. Сумма 600 000 дол. – это медиана интервала. Как определить реальный интервал значений и рассчитать по нему вероятность того, что мы не достигнем точки безубыточности?
Поскольку точные данные отсутствуют, нельзя выполнить простые расчеты для ответа на вопрос, сможем ли мы добиться требуемой экономии. Есть методы, позволяющие при определенных условиях найти интервал значений результирующего параметра по диапазонам значений исходных данных, но для большинства проблем из реальной жизни такие условия, как правило, не существуют. Как только мы начинаем суммировать и умножать разные типы распределений, задача обычно превращается в то, что математики называют неразрешимой или не имеющей решения обычными математическими методами проблемой. Поэтому взамен мы пользуемся методом прямого подбора возможных вариантов, ставшим возможным благодаря появлению компьютеров. Из имеющихся интервалов мы выбираем наугад множество (тысячи) точных значений исходных параметров и рассчитываем множество точных значений искомого показателя.
Моделирование методом Монте-Карло – превосходный способ решения подобных проблем. Мы должны лишь случайным образом выбрать в указанных интервалах значения, подставить их в формулу для расчета годовой экономии и рассчитать итог. Одни результаты превысят рассчитанную нами медиану 600 000 дол., а другие окажутся ниже. Некоторые будут даже ниже требуемых для безубыточности 400 000 дол.
Вы легко сможете осуществить моделирование методом Монте-Карло на персональном компьютере с помощью программы Excel, но для этого понадобится чуть больше информации, чем 90%-ный доверительный интервал. Необходимо знать форму кривой распределения. Для разных величин больше подходят кривые одной формы, чем другой. В случае 90%-ного доверительного интервала обычно используется кривая нормального (гауссова) распределения. Это хорошо знакомая всем колоколообразная кривая, на которой большинство возможных значений результатов группируются в центральной части графика и лишь немногие, менее вероятные, распределяются, сходя на нет к его краям (рис. 1).
Вот как выглядит нормальное распределение:
Рис.1. Нормальное распределение. По оси абсцисс число сигм.
Особенности:
- значения, располагающиеся в центральной части графика, более вероятны, чем значения по его краям;
- распределение симметрично; медиана находится точно посредине между верхней и нижней границами 90%-ного доверительного интервала (CI);
- «хвосты» графика бесконечны; значения за пределами 90%-ного доверительного интервала маловероятны, но все же возможны.
Для построения нормального распределения в Excel можно воспользоваться функцией =НОРМРАСП(Х; Среднее; Стандартное_откл; Интегральная), где
Х – значение, для которого строится нормальное распределение;
Среднее – среднее арифметическое распределения; в нашем случае = 0;
Стандартное_откл – стандартное отклонение распределения; в нашем случае = 1;
Интегральная – логическое значение, определяющее форму функции; если аргумент «интегральная» имеет значение ИСТИНА, функция НОРМРАСП возвращает интегральную функцию распределения; если этот аргумент имеет значение ЛОЖЬ, возвращается функция плотности распределения; в нашем случае = ЛОЖЬ.
Говоря о нормальном распределении, необходимо упомянуть о таком связанном с ним понятии, как стандартное отклонение. Очевидно, не все обладают интуитивным пониманием, что это такое, но поскольку стандартное отклонение можно заменить числом, рассчитанным по 90%-ному доверительному интервалу (смысл которого интуитивно понимают многие), я не буду здесь подробно на нем останавливаться. Рисунок 1 показывает, что в одном 90%-ном доверительном интервале насчитывается 3,29 стандартного отклонения, поэтому нам просто нужно будет сделать преобразование.
В нашем случае следует создать в электронной таблице генератор случайных чисел для каждого интервала значений. Начнем, например, с MS – экономии на материально-техническом обслуживании. Воспользуемся формулой Excel: =НОРМОБР(вероятность;среднее;стандартное_откл), где
Вероятность – вероятность, соответствующая нормальному распределению;
Среднее – среднее арифметическое распределения;
Стандартное_откл – стандартное отклонение распределения.
В нашем случае:
Среднее (медиана) = (Верхняя граница 90%-ного CI + Нижняя граница 90%-ного СI)/2;
Стандартное отклонение = (Верхняя граница 90%-ного CI – Нижняя граница 90%-ного СI)/3,29.
Для параметра MS формула имеет вид: =НОРМОБР(СЛЧИС();15;(20-10)/3,29), где
СЛЧИС – функция, генерирующая случайные числа в диапазоне от 0 до 1;
15 – среднее арифметическое диапазона MS;
(20-10)/3,29 = 3,04 – стандартное отклонение; напомню, что смысл стандартного отклонения в следующем: в интервал 3,29*Стандарт_откл, расположенный симметрично относительного среднего, попадает 90% всех значений случайной величины (в нашем случае MS)
Распределение величины экономии на материально-техническом обслуживании для 100 случайных нормально распределенных значений:
Рис. 2. Вероятность распределения MS по диапазонам значений; о том, как построить такое распределение с помощью сводной таблицы см.
Поскольку мы использовали «лишь» 100 случайных значений, распределение получилось не таким уж и симметричным. Тем не менее, около 90% значений попали в диапазон экономии на MS от 10 до 20 долл. (если быть точным, то 91%).
Построим таблицу на основе доверительных интервалов параметров MS, LS, RMS и PL (рис. 3). Два последних столбца показывают результаты расчетов на основе данных других столбцов. В столбце «Общая экономия» показана годовая экономия, рассчитанная для каждой строки. Например, в случае реализации сценария 1 общая экономия составит (14,3 + 5,8 + 4,3) х 23 471 = 570 834 долл. Столбец «Достигается ли безубыточность?» вам на самом деле не нужен. Я включил его просто для информативности. Создадим в Excel 10 000 строк-сценариев.
Рис. 3. Расчет сценариев методом Монте-Карло в Excel
Чтобы оценить полученные результаты, можно использовать, например, сводную таблицу, которая позволяет подсчитать число сценариев в каждом 100-тысячном диапазоне. Затем вы строите график, отображающий результаты расчета (рис. 4). Этот график показывает, какая доля из 10 000 сценариев будут иметь годовую экономию в том или ином интервале значений. Например, около 3% сценариев дадут годовую экономию более 1М дол.
Рис. 4. Распределение общей экономии по диапазонам значений. По оси абсцисс отложены 100-тысячные диапазоны размера экономии, а по оси ординат доля сценариев, приходящихся на указанный диапазон
Из всех полученных значений годовой экономии примерно 15% будут меньше 400К дол. Это означает, что вероятность ущерба составляет 15%. Данное число и представляет содержательную оценку риска. Но риск не всегда сводится к возможности отрицательной доходности инвестиций. Оценивая размеры вещи, мы определяем ее высоту, массу, обхват и т.д. Точно так же существуют и несколько полезных показателей риска. Дальнейший анализ показывает: есть 4%-ная вероятность того, что завод вместо экономии будет терять ежегодно по 100К дол. Однако полное отсутствие доходов практически исключено. Вот что подразумевается под анализом риска – мы должны уметь рассчитывать вероятности ущерба разного масштаба. Если вы действительно измеряете риск, то должны делать именно это.
В некоторых ситуациях можно пойти более коротким путем. Если все распределения значений, с которыми мы работаем, будут нормальными и нам надо просто сложить интервалы этих значений (например, интервалы затрат и выгод) или вычесть их друг из друга, то можно обойтись и без моделирования методом Монте-Карло. Когда необходимо суммировать три вида экономии из нашего примера, следует провести простой расчет. Чтобы получить искомый интервал, используйте шесть шагов, перечисленных ниже:
1) вычтите среднее значение каждого интервала значений из его верхней границы; для экономии на материально-техническом обслуживании 20 – 15 = 5 (дол.), для экономии на трудозатратах – 5 дол. и для экономии на сырье и материалах – 3 дол.;
2) возведите в квадрат результаты первого шага 5 2 = 25 (дол.) и т.д.;
3) суммируйте результаты второго шага 25 + 25 + 9 = 59 (дол.);
4) извлеките квадратный корень из полученной суммы: получится 7,7 дол.;
5) сложите все средние значения: 15 + 3 + 6 = 24 (дол.);
6) прибавьте к сумме средних значений результат шага 4 и получите верхнюю границу диапазона: 24 + 7,7 = 31,7 дол.; вычтите из суммы средних значений результат шага 4 и получите нижнюю границу диапазона 24 – 7,7 = 16,3 дол.
Таким образом, 90%-ный доверительный интервал для суммы трех 90%-ных доверительных интервалов по каждому виду экономии составляет 16,3–31,7 дол.
Мы использовали следующее свойство: размах суммарного интервала равен квадратному корню из суммы квадратов размахов отдельных интервалов .
Иногда нечто похожее делают, суммируя все «оптимистические» значения верхней границы и «пессимистические» значения нижней границы интервала. В данном случае мы получили бы на основе наших трех 90%-ных доверительных интервалов суммарный интервал 11–37 дол. Этот интервал несколько шире, чем 16,3–31,7 дол. Когда такие расчеты выполняются при обосновании проекта с десятками переменных, расширение интервала становится чрезмерным, чтобы его игнорировать. Брать самые «оптимистические» значения для верхней границы и «пессимистические» для нижней – все равно что думать: бросив несколько игральных костей, мы во всех случаях получим только «1» или только «6». На самом же деле выпадет некое сочетание низких и высоких значений. Чрезмерное расширение интервала – распространенная ошибка, которая, несомненно, часто приводит к принятию необоснованных решений. В то же время описанный мной простой метод прекрасно работает, когда у нас есть несколько 90%-ных доверительных интервалов, которые необходимо суммировать.
Однако наша цель не только суммировать интервалы, но и умножить их на объем производства, значения которого также даны в виде диапазона. Простой метод суммирования годится только для вычитания или сложения интервалов значений.
Моделирование методом Монте-Карло требуется и тогда, когда не все распределения являются нормальными. Хотя другие типы распределений не входят в предмет данной книги, упомянем о двух из них - равномерном и бинарном (рис. 5, 6).
Рис. 5. Равномерное распределение (не идеальное, а построенное с помощью функции СЛЧИС в Excel)
Особенности:
- вероятность всех значений одинакова;
- распределение симметрично, без перекосов; медиана находится точно посредине между верхней и нижней границами интервала;
- значения за пределами интервала невозможны.
Для построения данного распределения в Excel была использована формула: СЛЧИС()*(UB – LB) + LB, где UB – верхняя граница; LB – нижняя граница; с последующим разбиением всех значений на диапазоны с помощью сводной таблицы.
Рис. 6. Бинарное распределение (распределение Бернулли)
Особенности:
- возможны только два значения;
- существует единственная вероятность одного значения (в данном случае 60%); вероятность другого значения равна единице минус вероятность первого значения
Для построения случайного распределения данного вида в Excel использовалась функция: =ЕСЛИ(СЛЧИС()<Р;1;0), где Р - вероятность выпадения «1»; вероятность выпадения «0» равна 1–Р; с последующим разбиением всех значений на два значения с помощью сводной таблицы.
Метод впервые использовал математик Станислав Улам (см. ).
Дуглас Хаббард далее перечисляет несколько программ, предназначенных для моделирования методом Монте-Карло. Среди них и Crystal Ball компании Decisioneering, Inc, Денвер, штат Колорадо. Книга на английском языке была издана в 2007 г. Сейчас же эта программа принадлежит уже Oracle . Демо-версия программы доступна для скачивания с сайта компании. О ее возможностях мы и погорим .
См. главу 5 упоминавшейся книги Дугласа Хаббарда
Здесь Дуглас Хаббард под размахом понимает разность между верхней границей 90%-ного доверительного интервала и средним значением этого интервала (или между средним значением и нижней границей, так как распределение симметрично). Обычно под размахом понимают разность между верхней и нижней границами.
Мы разработаем симуляцию Монте-Карло с использованием Microsoft Excel и игра в кости. Моделирование Монте-Карло - математический численный метод, который использует случайные ничьи для выполнения вычислений и сложных проблем. Сегодня он широко используется и играет ключевую роль в различных областях, таких как финансы, физика, химия, экономика и многие другие.
Моделирование Монте-Карло
Метод Монте-Карло был изобретен Николаем Метрополисом в 1947 году и направлен на решение сложных проблем с использованием случайных и вероятностных методов. Термин «Монте-Карло» происходит от административного района Монако, широко известного как место, где европейские элиты играют в азартные игры. Мы используем метод Монте-Карло, когда проблема слишком сложна и сложна при непосредственном вычислении. Большое количество итераций позволяет моделировать нормальное распределение.
Метод моделирования методом Монте-Карло вычисляет вероятности для интегралов и решает уравнения в частных производных, тем самым вводя статистический подход к риску в вероятностном решении. Несмотря на то, что существует множество современных статистических инструментов для создания симуляций Монте-Карло, проще моделировать нормальный закон и единообразный закон с использованием Microsoft Excel и обходить математические основы.
Для моделирования Монте-Карло мы выделяем ряд ключевых переменных, которые контролируют и описывают результат эксперимента и назначают распределение вероятности после выполнения большого количества случайных выборок. Давайте возьмем игру в кости как модель.
Игра в кости
Вот как игра в кости играется:
Игрок бросает три кости, которые имеют 6 сторон 3 раза.
Если общее количество 3 бросков составляет 7 или 11, игрок выигрывает.
Если общее количество 3 бросков: 3, 4, 5, 16, 17 или 18, проигрыватель проигрывает.
Если общий результат - любой другой результат, игрок снова играет и повторно свертывает штамп.
Когда игрок снова бросает кубик, игра продолжается таким же образом, за исключением того, что игрок выигрывает, когда сумма равна сумме, определенной в первом раунде.
Шаг 1: События прокатки в кости
Сначала мы разрабатываем ряд данных с результатами каждого из 3 кубиков для 50 рулонов. Для этого предлагается использовать функцию «RANDBETWEEN (1. 6)». Таким образом, каждый раз, когда мы нажимаем F9, мы генерируем новый набор результатов каротажа. Ячейка «Результат» - это сумма итогов трех рулонов.
Шаг 2: Диапазон результатов
Затем нам нужно разработать ряд данных для определения возможных результатов для первого раунда и последующих раундов. Ниже приведен диапазон данных с тремя столбцами.В первом столбце у нас есть числа от 1 до 18. Эти цифры представляют собой возможные результаты после того, как катятся кости 3 раза: максимум составляет 3 * 6 = 18. Вы заметите, что для ячеек 1 и 2 результаты N / A, так как невозможно получить 1 или 2, используя 3 кости. Минимальное значение равно 3.
Во втором столбце включены возможные выводы после первого раунда. Как указано в первоначальном заявлении, либо игрок выигрывает (выигрывает), либо проигрывает (проигрывает), либо повторяет его (Re-roll), в зависимости от результата (всего 3 кубика).
В третьей колонке регистрируются возможные выводы для последующих раундов. Мы можем достичь этих результатов, используя функцию «If. «Это гарантирует, что если полученный результат будет эквивалентен результату, полученному в первом раунде, мы выиграем, иначе мы будем следовать первоначальным правилам первоначальной игры, чтобы определить, будем ли мы повторно бросать кости.
Шаг 3: Выводы
На этом этапе мы определяем результат 50 кубиков. Первый вывод можно получить с помощью индексной функции. Эта функция выполняет поиск возможных результатов первого раунда, вывод, соответствующий полученному результату. Например, при получении 6, как это имеет место на рисунке ниже, мы снова играем.
Можно получить результаты других рулонов кости, используя функцию «Or» и функцию индекса, вложенную в функцию «If». Эта функция сообщает Excel: «Если предыдущий результат -« Выиграть или проиграть », перестаньте бросать кости, потому что как только мы выиграли или проиграли, мы закончили. В противном случае мы переходим к столбцу следующих возможных выводов, и мы определяем вывод результата.
Шаг 4: Количество рулонов кости
Теперь мы определяем количество бросков кубиков, необходимых до проигрыша или выигрыша. Для этого мы можем использовать функцию «Countif», которая требует, чтобы Excel подсчитывал результаты «Re-Roll» и добавлял номер 1 к ней. Он добавляет один, потому что у нас есть один дополнительный раунд, и мы получаем окончательный результат (выигрываем или проигрываем).
Шаг 5: Моделирование
Мы разрабатываем диапазон для отслеживания результатов различных симуляций. Для этого мы создадим три столбца. В первом столбце одна из приведенных цифр - 5 000. Во второй колонке мы будем искать результат после 50 кубиков. В третьем столбце, в заголовке столбца, мы будем искать количество бросков кубиков, прежде чем получить окончательный статус (выиграть или проиграть).
Затем мы создадим таблицу анализа чувствительности с использованием данных характеристик или таблицы данных таблицы (эта чувствительность будет вставлена во вторую таблицу и в третьи столбцы). В этом анализе чувствительности номера событий 1 - 5, 000 должны быть вставлены в ячейку A1 файла. Фактически, можно было выбрать любую пустую ячейку. Идея состоит в том, чтобы просто произвести перерасчет каждый раз и таким образом получить новые броски кубиков (результаты новых симуляций), не повредив формулы на месте.
Шаг 6: Вероятность
Мы можем, наконец, вычислить вероятности выигрыша и проигрыша. Мы делаем это с помощью функции «Countif».Формула подсчитывает количество «выигрышей» и «проиграет», а затем делит на общее количество событий, 5, 000, чтобы получить соответствующую долю одного и другого. Наконец, мы видим, что вероятность получить выигрыш составляет 73. 2%, а результат Lose - 26,8%.
Существует немало программ для моделирования методом Монте-Карло. С их обзором можно ознакомиться, например, в книге
| Инструмент | Кем разработан | Описание |
| @Risk | Palisade Corporation, Итака, штат Нью-Йорк | Достаточно совершенный инструмент для работы на основе Excel; описывает большое число распределений; широкая база пользователей, предоставляется техническая поддержка |
| AIE | Hubbard Decision Research, Глен-Эллин, штат Иллинойс | Набор макросов на основе Excel; также позволяет рассчитывать стоимость информации и оптимальный портфель; подчеркивает приоритетность методологии над инструментарием; предоставляются консалтинговые услуги по практическим вопросам внедрения |
| Crystal Ball | Decisioneering, Inc, Денвер, штат Колорадо | Еще один инструмент на базе Excel. Продукт, успешно конкурирующий с @Risk. Много пользователей, предоставляется техническая поддержка |
| Risk Solver Engine | Frontline Systems, Инклин-Вилладж, штат Невада | Уникальная платформа разработки на базе Excel, позволяющая выполнять моделирование методом Монте-Карло с беспрецедентной скоростью. Поддерживает форматы SIP и SLURPs, необходимые для управления вероятностями |
| SAS | SAS Corporation, Роли, штат Северная Каролина | Пакет программ высшей степени сложности, используемый многими профессиональными статистиками и далеко выходящий за рамки метода Монте-Карло |
| SPSS | SPSS Inc., Чикаго, штат Иллинойс | Также выходит за пределы метода Монте-Карло; весьма популярен среди ученых |
| XLSim | Профессор Стэнфордского университета Сэм Сэвидж, AnalyCorp | Недорогой пакет программ, предназначенный для легкого изучения, удобен в применении. Сэвидж проводит в организациях семинары по методу Монте-Карло |
Книга написана американским автором и вышла в США в 2007 г. Программа Crystal Ball, упомянутая в таблице сейчас принадлежит уже Oracle . Демо-версия программы доступна для скачивания с сайта компании. Описание базовых функциональных возможностей Crystal Ball я нашел на сайте Финансовое моделирование, бюджетирование, планирование .
Скачайте и установите Crystal Ball на ПК. Прежде чем запустить программу закройте все окна Excel. Запустите Crystal Ball. Сначала откроется Excel, а затем в нем появится закладка Crystal Ball (рис. 1).
Рис. 1. Запуск Crystal Ball сначала открывает Excel, а затем появляется закладка Crystal Ball
Воспользуемся примером Хаббарда, рассмотренным , и на его основе изучим основы работы в программе Crystal Ball.
Предположим, что вы хотите арендовать новый станок. Стоимость годовой аренды станка 400 000 дол., и договор нужно подписать на несколько лет. Поэтому, даже не достигнув , вы всё равно не сможете сразу вернуть станок. Вы собираетесь подписать договор, думая, что современное оборудование позволит сэкономить на трудозатратах и стоимости сырья и материалов, а также считаете, что материально-техническое обслуживание нового станка обойдется дешевле.
Ваши калиброванные специалисты по оценке дали следующие интервалы значений ожидаемой экономии и годового объема производства (в таблице приведены 90%-ные доверительные интервалы):
Шаг. 1. Формирование модели. Разместим исходные данные на листе Excel. Они будут включать названия параметров и их средние значения, а также формулу для расчета годовой экономии (рис. 2)
Рис. 2. Исходные данные
Таким образом, суть нашей модели – расчет годовой экономии от использования нового станка. Годовая экономия (зависимая переменная) есть функция трех видов экономии и объема производства (итого, четырех влияющих переменных).
Шаг. 2. Задание параметров распределения влияющих переменных. Встаньте в ячейку В2 и на вкладке Crystal Ball щелкните Define Assumption. В открывшемся окне выберите Normal и нажмите Ok
Рис. 3. Выбор нормального распределения для первого параметра «Экономия на материально-техническом обслуживании»
Задайте среднее значение – Mean и стандартное отклонение – Std. Dev. (рис. 4). Поскольку исходные данные сформулированы в терминах 90%-ного доверительного интервала (CI), формулы для расчета следующие:
Среднее (Mean) = (Верхняя граница 90%-ного CI + Нижняя граница 90%-ного С I)/2;
Стандартное отклонение (Std. Dev.) = (Верхняя граница 90%-ного CI – Нижняя граница 90%-ного С I)/3,29
а наша таблица, приспособленная для работы в Crystal Ball примет вид:
| Параметр | Границы 90%-ного доверительного интервала | Среднее | Стандартное отклонение |
| экономия на материально-техническом обслуживании | от 10 до 20 дол. на единицу продукции | 15 | 3,04 |
| экономия на трудозатратах | от «–2» до 8 дол. на единицу продукции | 3 | 3,04 |
| экономия на сырье и материалах | от 3 до 9 дол. на единицу продукции | 6 | 1,82 |
| объем производства | от 15 000 до 35 000 единиц продукции в год | 25 000 | 6 079 |
| годовая экономия | (MS + LS + RMS) х PL |
Рис. 4. Выбор параметров нормального распределения
Последовательно вставая курсором в ячейки В3:В5 выберите вид и параметры распределения для всех четырех влияющих переменных. После задания параметров ячейки окрашиваются в зеленый цвет.
Шаг 3. Выбор зависимой переменной. Встаньте в ячейку В6, содержащую формулу расчета годовой экономии, и щелкните Define Forecast. В открывшемся окне в поле «Units» укажите ссылку на ячейку (рис. 5).
Рис. 5. Выбор зависимой переменной
Шаг. 4. Выбор условий моделирования. Этот шаг не является обязательным, так как система предложит параметры моделирования по умолчанию. Учитывая, что наша модель довольно простая, можно увеличить число итераций (по умолчанию оно равно 1000). Щелкните Run Preferences, и выберите 10 000 (рис. 6). Чем больше итераций, тем надежней результаты моделирования!
Рис. 6. Выбор числа итераций
Шаг. 5. Запуск моделирования. Щелкните Start, и наслаждайте результатом вашего первого моделирования в Crystal Ball 🙂 После 10 000 итераций программа выведет результаты в графическом виде (рис. 7).
Рис. 7. Результаты моделирования – распределение годовой экономии
В будущем вы всегда можете увидеть результаты моделирования, если щелкните View Charts (рис. 8)
Рис. 8. Вывод диаграммы с результатами моделирования на экран монитора
Вы также можете создать отчет о моделировании (в отдельном файле Excel), если щелкните на Create Report (рис. 9).
Рис. 9. Фрагмент отчета.
Обратите внимание на величину стандартного отклонения прогнозного значения «Годовая экономия». Вспомним, что среднее значение и стандартное отклонение однозначно задают верхнюю и нижнюю границы 90%-ного доверительного интервала, и вычислим эти границы:
Нижняя граница = среднее – стандартное отклонение * 3,29 / 2 = 600 127 – 189 495 * 3,29 /2 = 288 408
Верхняя граница = среднее + стандартное отклонение * 3,29 / 2 = 600 127 + 189 495 * 3,29 /2 = 911 846
Видно, что не весь 90%-ный доверительный интервал «Годовой экономии» превышает точку безубыточности – 400 000 долл. То есть, существует вероятность того, что точка безубыточности достигнута не будет…
Заметим, что моделирование в Crystal Ball дало те же результаты, что и моделирование в Excel с помощью функции СЛЧИС (рис. 10).
Рис. 10. Результаты моделирования в Excel с помощью функции СЛЧИС
См. главу 5 упоминавшейся книги Дугласа Хаббарда
Любая инвестиция нуждается в тщательных расчетах. Иначе инвестор рискует потерять вложенные средства.
На первый взгляд, бизнес прибыльный и привлекательный для инвестирования. Но это только первое впечатление. Необходим скрупулезный анализ инвестиционного проекта. И сделать это можно самостоятельно с помощью Excel, без привлечения дорогостоящих специалистов и экспертов по управлению инвестиционными портфелями.
Расчет инвестиционного проекта в Excel
Инвестор вкладывает деньги в готовое предприятие. Тогда ему необходимо оценить эффективность работы (доходность, надежность). Либо в новое дело – все расчеты проводятся на основе данных, полученных в ходе изучения рынка (инфраструктуры, доходов населения, уровня инфляции и т.д.).
Рассмотрим создание бизнеса с нуля. Рассчитаем прибыльность предприятия с помощью формул Excel. Для примера будем брать условные товары и цифры. Важно понять принцип, а подставить можно любые данные.
Итак, у нас есть идея открыть небольшой магазин. Определимся с затратами. Они бывают
- постоянными (нельзя рассчитать на единицу товара);
- переменными (можно рассчитать на единицу товара).
Первоначальные вложения – 300 000 рублей. Деньги расходуются на оформление предпринимательства, оборудование помещения, закупку первой партии товара и т.д.
Составляем таблицу с постоянными затратами:
* Статьи расходов индивидуальны. Но принцип составления - понятен.
По такому же принципу составляем отдельно таблицу с переменными затратами:
Для нахождения цены продажи использовали формулу: =B4*(1+C4/100).
Следующий этап – прогнозируем объем продаж, выручку и прибыль. Это самый ответственный этап при составлении инвестиционного проекта.
Объем продаж условный. В реальной жизни эти цифры – результат анализа доходов населения, востребованности товаров, уровня инфляции, сезона, места нахождения торговой точки и т.д.
Для подсчета выручки использовалась формула: =СУММПРОИЗВ(B3:B6;Лист2!$D$4:$D$7). Где первый массив – объемы продаж; второй массив – цены реализации.
Выручка минус переменные затраты: =B7-СУММПРОИЗВ(B3:B6;Лист2!$B$4:$B$7).
Прибыль до уплаты налогов: =B8-Лист1!$B$14 (выручка без переменных и постоянных затрат).
Налоги ЕНВД: =Лист1!A10*1800*0,15*3 (1800 – базовая доходность по виду деятельности, 3 – количество месяцев, С12 – площадь помещения).
Чистая прибыль: прибыль – налоги.
Оценка инвестиционного проекта в Excel
Рассчитывают 4 основных показателя:
- чистый приведенный эффект (ЧПЭ, NPV);
- индекс рентабельности инвестиций (ИРИ, PI);
- внутреннюю норму доходности (ВНД, IRR);
- дисконтированный срок окупаемости (ДСО, DPP).
Для примера возьмем следующий вариант инвестиций:
Сначала дисконтируем каждый положительный элемент денежного потока.
Создадим новый столбец. Введем формулу вида: = положительный элемент денежного потока / (1 + ставка дисконтирования)^ степень, равная периоду.
Теперь рассчитаем чистый приведенный эффект:
Найдем индекс рентабельности инвестиций. Для этого нужно разделить чистую приведенную стоимость (ЧПС) на объем инвестированных средств (со знаком «+»):
Результат – 1,90.
Посчитаем IRR инвестиционного проекта в Excel. Напомним формулу:
ВНД = ΣДП t / (1 + ВНР) t = И.
ДП t – положительные элементы денежного потока, которые нужно продисконтировать по такой ставке, чтобы чистый приведенный эффект равнялся нулю. Внутренняя норма доходности – такая ставка дисконтирования, при которой выпадает равенство вида:
ΣДП t / (1 + ВНР) t – И = 0,
Воспользуемся инструментом «Анализ «Что-Если»»:
Ставка дисконтирования равняется 0,41. Следовательно, внутренняя норма доходности составила 41%.
Моделирование рисков инвестиционных проектов в Excel
Используем метод имитационного моделирования Монте-Карло. Задача – воспроизвести развитие бизнеса на основе результатов анализа известных элементов и взаимосвязей между ними.
Продемонстрируем моделирование рисков на простейшем примере. Составим условный шаблон с данными:
Ячейки, которые содержат формулы ниже подписаны своими значениями соответственно.
Прогнозируемые показатели – цена услуги и количество пользователей. Под этими данными делаем запись «Результаты имитации». На вкладке «Данные» нажимаем «Анализ данных» (если там нет инструмента придется подключить настройку). В открывшемся окне выбираем «Генерация случайных чисел».
Заполняем параметры следующим образом:
Нам нужно смоделировать ситуацию на основе распределений разного типа.
Для генерации количества пользователей воспользуемся функцией СЛУЧМЕЖДУ. Нижняя граница (при самом плохом варианте событий) – 1 пользователь. Верхняя граница (при самом хорошем варианте развития бизнеса) – 50 покупателей услуги.
Скопируем полученные значения и формулы на весь диапазон. Для переменных затрат тоже сделаем генерацию случайных чисел. Получим эмпирическое распределение показателей эффективности проекта.
Чтобы оценить риски, нужно сделать экономико-статистический анализ. Снова воспользуемся инструментом «Анализ данных». Выбираем «Описательная статистика».
Программа выдает результат (по столбцу «Коэффициент эффективности»):
Можно делать выводы и принимать окончательное решение.
