Мы разработаем симуляцию Монте-Карло с использованием 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%.

Цели:

образовательные: изучение численного метода Монте–Карло.

развивающие:

  • научить анализировать при нахождении общего, частного в понятиях информатики и ЭТ;
  • научить рассуждать;
  • составлять алгоритм задач;
  • уметь составлять формулы.

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

Ход урока

I. Оргмомент.

Цель нашего урока – это знакомство с функцией случайного числа и применением метода Монте–Карло в электронных таблицах.

II. Усвоение новых знаний.

В математике для решения задач часто требуются математические модели. Одна из таких задач – вычисление площадей. Конечно для простейших фигур (прямоугольников, многоугольников, кругов) вычисление площади не составляет труда: надо в известные формулы подставить исходные данные. А как быть если фигура имеет сложные формы? Итак, задача: Дана фигура сложной формы. Вычислить её площадь.

Можно предложить разные модели для этой задачи. Например, в 6-м классе вас учили использовать палетку: на фигуру накладывается клетчатая прозрачная бумага или плёнка (палетка), и подсчитывается количество квадратиков, попавших в фигуру. В этой модели предполагается, что чем меньше клетки, тем точнее будет результат, независимо от того, каким образом наложить палетку на фигуру.

Можно придумать “физическую” модель, скопировать фигуру на картон, аккуратно вырезать её, взвесить и поделить на вес единичного квадрата из этого же картона.

В 11-м классе вы познакомитесь ещё с одним способом нахождения площадей фигур: с помощью интегралов.

Однако все эти модели трудно поддаются расчётам на ЭВМ. Мы попробуем построить математическую модель, которая позволит эффективно применять ЭВМ для решения задач на нахождение площадей, объемов и тому подобное.

Поместим данную фигуру в квадрат. Будем наугад (как говорят математики, случайным образом) бросать точки в этот квадрат. Естественно, что чем больше площадь фигуры, тем чаще в неё будут попадать точки. Представьте себе квадратный дворик и в нем детскую круглую площадку. Каждому ясно, что во время снегопада количество снежинок, попавших на детскую площадку, пропорционально её площади. Таким образом, можно сделать допущение: при большом числе точек, наугад выбранных внутри квадрата, доля точек, содержащихся в данной фигуре, приближенно равна отношению площади квадрата.

Такой метод приближенного нахождения площадей фигур носит название метода Монте–Карло (по названию города, где расположена знаменитая рулетка, которую можно рассматривать как “генератор” случайных чисел).

Только случайность поможет нам найти площадь фигуры методом Монте–Карло.

В Exсel имеется возможность проводить моделирование с использованием случайных чисел.

Функция СЛЧИС() (без аргументов) генерирует случайное число в диапазоне от 0 до 1. Совокупность этих чисел равномерно распределена на отрезке . При нажатии функциональной клавиши F9 (пересчет) в ячейках, содержащих формулу с функцией СЛЧИС , генерируется новое случайное число.

Показываю на ЭВМ (увеличив размер шрифта).

Вводим в ячейку формулу =СЛЧИС() и нажимаю F9 . В ячейках изменяется выводимое число.

Вопрос: Как изменить формулу, чтобы диапазон расширился от 0 до 10?

Ответ: Нужно умножить на 10, то есть =СЛЧИС()*10 .

Вопрос: Как изменить формулу, чтобы диапазон расширился от 2 до 3?

Ответ: Нужно сложить с числом 2, то есть =СЛЧИС()+2 .

Вопрос: Как изменить формулу, чтобы диапазон лежал на промежутке ?

Ответ: =(10–5)*СЛЧИС()+5 .

Вопрос: Как изменить формулу, чтобы диапазон лежал на промежутке ?

Ответ: Нужно записать формулу следующего вида =(b–a)*СЛЧИС()+a .

III. Проверка понимания материала. (Раздаю тесты.)

Тест на функцию генератор случайных чисел.

Вариант 1

Вопрос 1.

  1. =СРЗНАЧ(A1: A5) .
  2. =СЧЕТ(А1: А4) .
  3. =ЕСЛИ(В1>В2; 1; 0) .
  4. =(В – А)*СЛЧИС()+А .

Вопрос 2. Дана формула = СЛЧИС()* 1.4+3.2 .

  1. [ 0; 3,2 ].
  2. [ 1,4; 3,2 ].
  3. [ 3,2; 4,6 ].
  4. [ 0; 4,6 ].

Вопрос 3. Дана формула = СЛЧИС()* 50 .

В каком диапазоне будут получены числа.

  1. [ 0; 1 ].
  2. [ 0; 50 ].
  3. [ 1; 50 ].
  4. (0; 50).

Вопрос 4. Дана формула = (100 – 20)* СЛЧИС()+20 .

В каком диапазоне будут получены числа.

  1. [ 0; 20 ].
  2. [ 0; 100 ].
  3. [ 20; 100 ].
  4. [ 80; 100 ].

Вопрос 5.

Вопрос 6. Дана формула = СЛЧИС()+12 .

В каком диапазоне будут получены числа.

  1. [ 0; 12 ].
  2. [ 1; 12 ].
  3. [ 11; 13 ].
  4. [ 12; 13 ].

Вариант 2

Вопрос 1. Дана формула = СЛЧИС()* 30 .

В каком диапазоне будут получены числа.

  1. [ 0; 1 ].
  2. [ 0; 30 ].
  3. [ 1; 30 ].
  4. (0; 30) .

Вопрос 2. Дана формула = СЛЧИС()* 3.2+1.4 .

В каком диапазоне будут получены числа.

  1. [ 0; 1,4 ].
  2. [ 1,4; 3,2 ].
  3. [ 3,2; 4,6 ].
  4. [ 1,4; 4,6 ].

Вопрос 3. Выберите из предложенных выражений формулу, определяющую числа случайным образом:

  1. =СРЗНАЧ(B1: B5) .
  2. =ЕСЛИ(В1>В2; 1; 0) .
  3. =СЛЧИС()+А .
  4. =СЧЕТ(А1: А4) .

Вопрос 4. Дана формула = (50 – 10)* СЛЧИС()+10 .

В каком диапазоне будут получены числа.

  1. [ 0; 10 ].
  2. [ 0; 50 ].
  3. [ 10; 40 ].
  4. [ 10; 50 ].

Вопрос 5. Дана формула = 21+ СЛЧИС() .

В каком диапазоне будут получены числа.

  1. [ 0; 21 ].
  2. [ 1; 21 ].
  3. [ 21; 22 ].
  4. [ 21; 23 ].

Вопрос 6. Какую функциональную клавишу необходимо использовать для изменения выводимых случайных чисел.

Ответы.

Вариант 1 . 1.4, 2.3, 3.2, 4.3, 5.4, 6.4.

Вариант 2. 1.2, 2.4, 3.3, 4.4, 5.3, 6.3.

IV. Подготовка к практической работе.

Давайте вычислим число p методом Монте–Карло. Для этого вспомним формулу площади круга. Назовите её. Ответ: S = R 2 Посмотрите на рис. 1.

Пусть окружность вписана в квадрат со стороной а = 2. Скажите, пожалуйста, чему равен радиус окружности? (Ответ: 1). Тогда площадь круга чему будет равна? (Ответ: S = ).

Рассмотрим единичный квадрат, вершины которого имеют координаты (0,0), (1,0), (1,1), (0,1). В квадрат будем бросать точку со случайными координатами. Этот квадрат высекает из окружности единичного радиуса с центром в начале координат сектор, площадь которого составляет четверть площади окружности, то есть /4.

Вспомним уравнение окружности с центром в начале координат.

Вопрос: Назовите запись данного факта. Ответ: x 2 + y 2 = 1.

Если точка оказалась внутри сектора, то фиксируем “удачное попадание” единицей, если точка оказалась вне сектора, записываем нуль.

Значит, если x 2 + y 2 < = 1, то точка попадает в круг, иначе она вне круга. Это и есть математическое соотношение, позволяющее определить, лежит ли точка в фигуре. После многократных бросаний вычислим отношение числа удачных исходов к общему количеству бросаний. Это число умножим на 4. Получим приближение к числу p .

Компьютерная модель .

Организуем вычисления на рабочем листе.

В ячейки А1 и В1 поместим заголовки x и y. В ячейку А2 поместим формулу генератора случайного числа =СЛЧИС() и скопируем ее до ячейки В1001 .

В ячейку С2 введем формулу, которая описывает условие попадания или не попадания точек в сектор, то есть =Если(А2^2+B2^2 < = 1; 1; 0) cкопируем до С1001 .

В ячейку С1002 разместим формулу подсчета удачных исходов =СУММ(С2:С1001)/250 или a / 250 . Таблица сконструирована. Теперь проведем компьютерный эксперимент.

Теперь нажимая F9 в ячейке С1002 сменяют друг друга десятичные приближения (не слишком точные) числа .

A B C
1 x y попадание
2 =СЛЧИС() =СЛЧИС() =ЕСЛИ(A2^2+B2^2 <= 1; 1; 0)
3 =СЛЧИС() =СЛЧИС() =ЕСЛИ(A3^2+B3^2 <= 1; 1; 0)
… … …
1001 =СЛЧИС() =СЛЧИС() =ЕСЛИ(A1001^2+B1001^2 <= 1; 1; 0)
1002 =СУММ(С2:С1001)/250

V. Подведение итога.

Сегодня мы познакомились с методом Монте–Карло, провели компьютерный эксперимент и нашли практически значение числа ПИ.

Вернуться в Оглавление

ПРИМЕНЕНИЕ ТАБЛИЧНОГО ПРОЦЕССОРА 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%. (округлить в большую сторону до целого числа дней) . В качестве исходных данных, взять данные из лабораторной работы № .

СРСП 5 8

Тема:

■ Кто использует моделирование методом Монте-Карло?

■ Что произойдет, если я введу в какую-либо ячейку формулу =СЛЧИС() [- RANDQ ]?

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

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

■ На основе каких данных компания-производитель поздравительных от­крыток может определить, сколько открыток необходимо напечатать?

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

ПРИМЕЧАНИЕ Название «моделирование методом Монте-Карло» пришло к нам из в 1930-1940 гг., когда физики на компьютере моделировались ситуации для оценки вероятности того, что цепная реакция, необходимая для атомной бомбы, пройдет ус­пешно. Специалисты, участвовавшие в этой работе, были страстными поклонниками азартных игр, они и дали операциям моделирования название «Монте-Карло».

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

Основы моделирования методом Монте-Карло

Кто использует моделирование методом Монте-Карло?

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

■ Компании General Motors , Procter and Gamble и Eli Lilly применяют моде­лирование для оценки как средней доходности, так и риска, связанного с выпуском новых товаров. В General Motors эта информация помогает главному исполнительному директору Рику Ваггонеру (Rick Waggoner ) определять товары, выпуском которых стоит заняться.

■ General Motors применяет моделирование для таких операций, как про­гнозирование чистой прибыли корпорации, прогнозирование структур­ных затрат и затрат на приобретение, определение подверженности кор­порации различным видам рисков (например, изменению процентных ставок и колебаниям валютного курса).

■ Lilly применяет моделирование для определения оптимальной производ­ственной мощности, требуемой для производства каждого лекарства.

■ Компании с Wall Street применяют моделирование для оценки сложных финансовых показателей и суммы под риском (СПР) их инвестиционных портфелей.

■ Procter and Gamble применяет моделирование для примерной оценки и оптимального хеджирования (страхования) рисков, связанных с измене­нием курса иностранной валюты.

■ Sears применяет моделирование, чтобы оценить, сколько единиц каждого модельного ряда необходимо заказать у поставщиков - например, сколь­ко пар Dockers следует заказать в этом году.

■ Моделирование можно использовать для оценки «реальных возможнос­тей», например возможности развития, принятия обязательств или от­срочке проекта.

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

Что произойдет, если я введу в какую-либо ячейку формулу =СЛЧИС()?

Если вы введете в какую-либо ячейку формулу =СЛЧИС(), то получите чи-. которое с одинаковой вероятностью может принять значение в диапазоне от 0 до 1. Таким образом, в примерно 25% случаев вы получите число, мень­шее или равное 0,25; в 10% случаев - число не менее 0,90 и так далее. (рис. 1).

Рис. 1Демонстрация работы функции СЛЧИС (RAND )

Я скопировал из ячейки СЗ в С4:С402 формулу =СЛЧИС(). Диапазону СЗ:С402 я задал имя Данные. После этого в столбце F я вычислил среднее 400 случайных чисел (ячейка F 2) и с помощью функции СЧЁТЕСЛИ (COUNTIF определил долю чисел от 0 до 0,25, от 0,25 до 0,50, от 0,50 до 0,75 и от 0,75 до 1. Если вы нажмете клавишу F 9, случайные числа будут сгенерированы заново. Обратите внимание: среднее 400 случайных чисел всегда близко к 0,5 и при­мерно 25% результатов попадают в каждый интервал, равный 0,25. Эти резуль­таты согласуются с определением случайных чисел. Заметьте также, что значе­ния, генерируемые функцией СЛЧИС (RAND ) в разных ячейках, независим!: Например, если случайное число, сгенерированное в ячейке СЗ - большое (на­пример, 0,99), это ничего не скажет нам о величине других сгенерированные случайных чисел.

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

Предположим, спрос на календари определяется следующей дискретной слу­чайной величиной:

Спрос

Вероятность

10000

0,10

20000

0,35

40000

0,30

60000

0,25

Как нам заставить Excel многократно проиграть, или смоделировать, этот спрос на календари? Хитрость в том, чтобы связать каждое возможное значе ние функции СЛЧИС ( RAND ) с возможным спросом на календари. Следую­ щее с опоставление гарантирует, что спрос на 10000 штук реализуется в 10% случаев и так далее.

спрос Присвоенное случайное число

10000

Меньше 0,10

20000

Больше или равно 0,10 и меньше 0,45

40000

Больше или равно 0,45 и меньше 0,75

60000

Больше или равно 0,75

Чтобы посмотреть, как моделируется спрос, откройте рис. 2.


Рис. 2 Пример моделирования дискретной случайной величины

Основной принцип нашего моделирования - воспользоваться случай­ным числом для просмотра в диапазоне таблицы F 2: G 5 (ему дано имя поиск). Случайные числа, большие или равные 0 и меньшие 0,10, соответствуют спросу в 10000 штук; случайные числа, большие или равные 0,10 и меньшие 0,45 соответствуют спросу в 20000 штук; случайные числа, большие или равные XI0, и меньшие 0,75, соответствуют спросу в 40000 штук; случайные числа, большие или равные 0,75, соответствуют спросу в 60000 штук. Я сгенериро­вал 400 случайных чисел, скопировав из ячейки СЗ в С4:С402 формулу СЛЧИС() [ RAND ()]. Затем я сгенерировал 400 испытаний, или итераций, скопировав из ячейки ВЗ в В4:В402 формулу ВПР(СЗ;поиск;2). Эта формула гаран­тирует, что любое случайное число меньше 0,10 сгенерирует спрос, равный 10000 ; любое случайное число в диапазоне от 0,10 до 0,45 сгенерирует спрос, равный 20000 единицам и так далее. В диапазоне ячеек F 8: F 11 я с помощью функции СЧЁТЕСЛИ (COUNTIF ) определил долю каждого значения спроса в аших 400 итерациях. Обратите внимание: когда бы вы ни нажали клавишу F 9 для повторной генерации случайных чисел, моделируемые вероятности оказываются близки к нашим предполагаемым вероятностям спроса.

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

Введя в какую-либо ячейку формулу НОРМОБР(СЛЧИС();мю;сигма), вы делируете значение случайной величины с нормальным распределением, нее значение которой равно мю и стандартное отклонение - сигма. (рис. 3).


Рис. 3 Моделирование случайной величины с нормальным распределением

Предположим, нам требуется смоделировать 400 испытаний, или итераций, для случайной величины с нормальным распределением, среднее кото­рой равно 40000, а стандартное отклонение - 10000 (я ввел эти значения в ячейки Е1 и Е2, и задал им имена среднее и станд. откл. соответственно l Скопировав формулу =СЛЧИС() из ячейки С4 в С5:С403, я сгенерировал 400 разных случайных чисел. Скопировав из ячейки В4 в В5:В403 формулу НОРМОБР (С4 ;среднее;сигма), я сгенерировал 400 итераций для случайной ве­ личины с нормальным распределением, среднее которой равно 40000, а стан­дартное отклонение - 10000. Когда мы нажимаем клавишу F 9 для повторной генерации случайных чисел, среднее остается близким к 40000, а стандартнее отклонение - близким к 10000.

По сути, для случайного числа х формула НОРМОБР(р;мю;сигма) генерирует р-ю персентиль случайной величины с нормальным распределение^ среднее которой равно мю, а стандартное отклонение - сигма. Например, сп -чайное число 0,73 в ячейке В13 (рис. 58-3) генерирует примерно 73-ю персен­тиль случайной величины с нормальным распределением, среднее которой равно 40000, а стандартное отклонение равно 10000.

На основе каких данных компания-производитель поздравительных открыток может определить, сколько открыток необходимо напечатать?

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

Основы моделирования методом Монте-Карло

Спрос

Вероятность

10000

0,10

10000

0,35

40000

0,30

6 0000

0,25

Поздравительная открытка продается по цене $4,00, а переменные издер­жки на производство одной открытки составляют $1,50. Нереализованные открытки должны быть распроданы по цене $0,20 за штуку. Сколько открыток следует напечатать?

В сущности, мы моделируем каждый возможный объем производства (10000, 20000, 40000 и 60000 штук) множество раз (скажем, 1000 итераций). Затем мы определяем, какой объем обеспечивает максимальный средний до­ход для этих 1000 итераций. (рис. 4). Я назначил ячейкам С1:С11 имена диапазонов из ячеек 31:В11. Диапазону G 3: H 6 я назначил имя поиск. Наши параметры цены реали­зации и затрат указаны в ячейках С4:С6.


Рис. 4 Моделирование объема производства открыток ко Дню Св. Валентина

Пробный объем производства (в данном примере - 40000) в ячей­ку С1. Затем я сгенерировал случайное число в ячейке С2 с помощью форму-:ы =СЛЧИС(). Как я уже говорил, я моделирую спрос на открытку в ячейке 1: по формуле ВПР(случайное_число;понск;2) [в формуле ВПР ( VLOOKUP ) случайное_число - это имя, назначенное ячейке С2, а не функция СЛЧИС RAND )].

Число проданных открыток меньше нашего объема производства и спроса . В ячейке С8 я подсчитываю наш доход по формуле МИН (объем_производства;спрос) *цена_открытки. В ячейке С9 я вычисляю общие затраты на производство по формуле объем_производства*себестоимостъ_пр_ва_открытки.

Если мы производим открыток больше, чем нужно, число нереализованных открыток равно объему производства минус спрос; в противном случае -нереализованных открыток не будет. Мы вычисляем затраты на переработку в ячейке С10 по формуле =стоимостъ_при_распродаже*ЕСЛИ(объем_производ-опва>спрос;объем_производства-спрос;0). И, наконец, в ячейке СП мы вычи­сляем нашу прибыль по формуле =доход-общие_переменные_издержки-с щие_издержки_на_распродажу.

Нам требуется эффективный способ имитации многократного (скажи 1000 раз) нажатия клавиши F 9 и подсчета дохода для каждого объема производства. В этом случае нас спасет таблица подстановки с двумя переменными. Таблица подстановки, ис пользованная мной в данном примере, показана на рис. 5.


Рис. 5 Таблица подстановки с двумя переменными для моделирования объема производства поздравительных открыток

В диапазоне ячеек А16:А1015 я ввел числа от 1 до 1000 (соответствующие 1000 испытаний). Один из простых способов создать эти значения - ввести 1 в ячейку А16 и затем выбрать в меню Правка ( Edit ) команду Заполнить\Прог-рессия (Fill \ Series ). В поле Шаг ( Step value ) диалогового окна Прогрессия ( Series ) (рис. 58-6) введите 1, а в поле Предельное значение (Stop value ) - 1000. Установите переключатель по столбцам ( Columns ) и затем щелкните Столбец А, начиная с ячейки А16, будет заполнен числами от 1 до 1000.

Затем следует ввести возможные объемы производства (10000, 20000. 40000 и 60000 единиц) в ячейки В15:Е15. Мы хотим вычислить прибыль для каждого испытания (от 1 до 1000) и каждого объема производства. В верхней левой ячейке (А15) нашей таблицы подстановки мы ссылаемся на форм прибыли, которая задана в ячейке С11, вводя =С11.

Теперь все готово и мы можем заставить Excel моделировать 1000 итера­ций спроса для каждого объема производства. Выделите диапазон таблицы (А15:Е1014) и затем щелкните в меню Данные ( Data ) команду Таблица подстановки (Table ). Чтобы создать таблицу подстановки с двумя параметрами мы указываем в качестве ячейки для подстановки по строкам любую пустую ячейку (в данном случае - 114), а в качестве ячейки для подстановки по стол­бцам - объем производства (О). После того как вы щелкнете OK , Excel смо­делирует 1000 значений спроса для каждого объема производства.


Рис. 6 С помощью диалогового окна Прогрессия ( Series )и вставьте номера испытаний от 1 до 10ОО

Чтобы понять, почему это работает, рассмотрим значения, полученные в таблице подстановки (диапазон ячеек С16:С1015). Для каждой из этих ячеек Excel подставляет значение 20000 в ячейку С1. В С16 в пустую ячейку помеща­ется значение, подставляемое по строкам (1), и случайное число в ячейке С2 генерируется заново. После этого в ячейку С16 записывается соответствующее значение прибыли. Затем в пустую ячейку снова помещается значение, под­ставляемое по строкам (2), и случайное число в ячейке С2 генерируется за­ново. Соответствующее значение прибыли записывается в ячейку С17.

Скопировав из ячейки В13 в С13:Е13 формулу СРЗНАЧ(В16:В1015), мы подсчитаем среднюю прибыль для каждого объема производства. Скопировав формулу СТАНДОТКЛОН(В16:В1015) из ячейки В14 в диапазон С14:Е14, мы вычисляем стандартное отклонение прибыли для каждого объема производ­ства. При каждом нажатии клавиши F 9 для всех объемов производства модели­руются 1000 итераций спроса. Производство 40000 открыток всегда обеспечи­вает максимальную прибыль. Следовательно, ясно, что производство 40000 - правильное решение.

Влияние риска на наше решение. Если мы напечатаем 20000 открыток вместо 40000, наша ожидаемая прибыль упадет примерно на 22%, однако наш риск измеряемый стандартным отклонением прибыли) упадет практически на 3%. Следовательно, если риск для нас крайне неприемлем, печать 20000 от­крыток может оказаться правильным решением. Кстати, при печати 10000 от­крыток стандартное отклонение всегда равно нулю, поскольку мы в любом слу­чае продадим их, и ничего не останется.

ПРИМЕЧАНИЕ На этом листе я установил переключатель Вычисления ( Excelulation ) в положение автоматически кроме таблиц ( Automatic Except For Tables ) [см. вклад­ку Вычисления ( Excelulation ) диалогового окна Параметры ( Options )]. В результате таблица подстановки не будет пересчитывать значения, пока мы не нажмем клавишу F 9. Отличная идея, поскольку при большом объеме таблицы подстановки ваша работа замедлится, если Excel будет каждый раз пересчитывать значения при вводе новых данных в ячейки листа. Обратите внимание: в этом примере при каждом нажатии клавиши F 9 средняя прибыль изменяется. Это происходит потому, что каждом нажа­тии клавиши F 9 значения спроса для всех казанных объемов производства генериру­ются на основе новой последовательности из 1000 случайных чисел.

Доверительный интервал для средней прибыли. Естественный вопрос, возника­ющий в данной ситуации: «Для какого интервала значений мы можем быть уверены на 95%, что средняя прибыль верна?» Этот интервал называется 95-процентным доверительным интервалом для средней прибыли. Для среднего значения вывода любой операции моделирования 95-процентный доверитель­ный интервал вычисляется по формуле:

Средняя прибыль±

1,96*стандартное отклонение прибыли ■у ]число итераций

В ячейке J 11 я вычислил нижнюю границу 95-процентного доверитель­ного интервала для средней прибыли при производстве 40000 открыток, вос­пользовавшись формулой D 13- l ,96* D 14/ KOPEHb (1000). В ячейке J 12 я вычис­лил верхнюю границу 95-процентного доверительного интервала по формуле D 13+ l ,96* D 14/ KOPEHb (1000). Эти вычисления показаны на рис. 7.


Рис. 7 Девяностопятипроцентный доверительный интервал для средней прибыли при производстве 40000 открыток

Мы на 95% уверены, что средняя прибыль при производстве 40000 кален­дарей составит от $56578 до $62445.

Самостоятельно

1. Дилер General Motors Company считает, что спрос на модель « Envoy » вы­пуска 2005 г. будет распределен по нормальному закону со средним, рав­ным 200, и стандартным отклонением, равным 30. Его затраты на выпуск одной машины модели Envoy составляют $25000, и продает он ее по $40000. Половину всех нереализованных машин модели Envoy можно продать по $30000. В качестве возможного размера заказа дилер рассмат­ривает 200, 220, 240, 260, 280 и 300 машин модели Envoy . Сколько машин ей следует заказать?

Небольшой супермаркет пытается определить, сколько копий журнала «Реор1е» им следует заказывать каждую неделю. Они считают, что спрос на «Реор1е» в магазине регулируется следующей дискретной случайной величиной:

Спрос

Вероятность

0,10

0,20

0,30

0,25

0,15

Супермаркет покупает каждую копию «Реорк» за $1,00 и продает ее по $1,95. Каждую нереализованную копию «Реор1е» они могут вернуть за $0,50. Сколько копий журнала «Реор1е» следует заказать супермаркету?

В этой статье было адаптировать в Microsoft Excel анализа и моделирования бизнес по Wayne Winston l..

    Кто использует Monte Карло?

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

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

Примечание: Имя Monte Карло поступает из моделирования компьютера, выполненных в течение 1930-х и 1940-х годах, чтобы оценить вероятность того, что реакция цепочки, необходимых для механизм atom для detonate будет работать успешно. Physicists, участвующие в этой работы были большая вентиляторов азартных игр, поэтому предоставил моделирования Monte Carlo имя кода.

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

Кто использует Monte Карло?

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

    Общие моторов, Proctor и Gamble, Pfizer, Squibb бристольский Сидоров и Eli Lilly использовать моделирование для оценки среднее возврата и фактору риска степень новых продуктов. В GM эта информация используется генеральный Директор, чтобы определить, какие продукты поставляются на рынок.

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

    Для определения оптимального завода емкость для каждого фармацевтическая Lilly использует моделирование.

    Proctor и Gamble использует моделирование для моделирования и оптимально живая изгородь риск чужой exchange.

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

    Olive Oil и фармацевтическая компании используют моделирование в значение «реальные параметры», например значение параметра развертывания, контракт или отложить проекта.

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

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

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

На рисунке 60-1 демонстрации функция СЛЧИС

Примечание: При открытии файла Randdemo.xlsx не появляется же случайные числа показан на рисунке 60-1. Функция RAND всегда автоматически пересчитывает числами, которые он приводит к возникновению ошибки при открытии листа или при вводе новой информации в лист.

Во-первых скопируйте в ячейке C3 C4:C402 формулы =RAND() . Задайте имя диапазона 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), он сообщает нам ничего о значениях других случайные числа создаваемых.

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

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

Требование

Вероятность

Как добавить Excel воспроизвести или имитировать, это требование календарей много раз? Для этого достаточно будет связана с возможных запросу календарей для каждого возможного значения функция RAND. Следующие назначения гарантирует возникать 10 процентов времени требование 10 000 и т. д.

Чтобы продемонстрировать моделирование запросу, просмотрите файл Discretesim.xlsx, показанный на рисунке 60-2 на следующей странице.

На рисунке 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) , вы создадите имитацию значение Обычный случайная величина возникли среднюю "среднее" и стандартным отклонением "стандартное_откл" . В этой процедуре показано в файле Normalsim.xlsx, показанный на рисунке 60-3.

На рисунке 60-3 имитация обычный случайная величина

Предположим, что нам нужно смоделировать 400 число_испытаний или итераций в обычном случайная величина с среднее 40 000 и стандартное отклонение равно 10 000. (Можно введите следующие значения в ячейки E1 и E2 и присвойте имя этих ячеек в виду и сигм .) Копирование формулы =RAND() с C4 C5:C403 создает 400 различные случайные числа. При копировании из B4 B5:B403 NORMINV(C4,mean,sigma) формула создает 400 различные значения пробной версии из обычный случайная величина с среднее 40 000 и стандартное отклонение равно 10 000. Когда мы нажмите клавишу F9, чтобы пересчитать случайных чисел, среднее остается близко 40 000 и стандартным отклонением близко 10 000.

По сути случайное число x формулы NORMINV(p,mu,sigma) создает p ю процентиль обычный случайная величина с среднюю "среднее" и "стандартное_откл" стандартное отклонение. Например случайное число в ячейке C4 0,77 (просмотреть рисунке 60-3) приводит к возникновению ошибки в ячейке B4 примерно 77th процентиль обычный случайная величина с среднее 40 000 и стандартное отклонение равно 10 000.

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

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

Требование

Вероятность

Поздравительная открытка продаваемых для $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 ячеек.

Моделирование карточки 60 рис Валентина

Для ввода количества пробной производства (40 000 в данном примере) в ячейке C1. Теперь создайте случайное число в ячейке C2 с формулы =RAND() . Как было описано выше имитировать потребность в карточку в ячейке C3 с формулы VLOOKUP(rand,lookup,2) . (В формулу ВПР, функция rand является ячейку имя, присвоенное ячейке C3, функция RAND не.)

Число проданных единиц меньше количества производства и запросу. В ячейках C8 вычисления нашим дохода с формулой MIN (производимый, запросу) * unit_price . В ячейке C9 вычислить стоимость общее производства с использованием формулы произведено * unit_prod_cost .

Если мы получаем больше карт, чем в запросу, количество единиц оставшиеся производства равно минус запросу; в противном случае остались без единицы. Наш стоимости реализации в ячейку C10 с формулой, мы можем рассчитать unit_disp_cost * IF (произведено > запросу, подготовленные - запросу, 0) . И, наконец в ячейке C11, мы можем рассчитать наша прибыль как выручки - total_var_cost-total_disposing_cost .

Мы предлагаем эффективный способ нажмите клавишу F9, сколько раз (например, 1000) для каждого количества производства и перечень наша ожидаемые прибыль для каждого количества. Эта ситуация входит в котором двумерная таблица данных приходит нашей помощь. (Читайте в статье главе 15 «Чувствительности анализа с помощью таблицы данных,» подробные сведения о таблицах данных). Таблица данных, используемые в этом примере показан на рисунке 60-5.

На рисунке 60-5 двумерная таблица данных для имитации поздравительных открыток

В диапазоне ячеек A16:A1015 введите номера 1 – 1000 (соответствующий нашей число_испытаний 1000). Простой способ создать эти значения - начать путем ввода в ячейке A16 1 . Выделите ячейку, а затем на вкладке Главная в группе " Редактирование ", нажмите кнопку заполнить и выберите ряд , чтобы отобразить диалоговое окно ряда . В диалоговом окне рядов , показанный на рисунке 60-6 введите значение шага 1 и остановить значение 1000. В области В серии выберите параметр столбцы и нажмите кнопку ОК . Номера 1 – 1000 будут введены в столбце открывающая в ячейке A16.

60-рис используется ряд диалоговое окно для заполнения пробная версия число от 1 до 1000

Далее мы введите нашей количества возможных производства (10 000, 20 000, 40 000, 60 000) в ячейках B15:E15. Нам нужно рассчитать прибыль для каждого номера пробной версии (от 1 до 1000) и каждого количества производства. Мы ссылаются формулы для прибыли (вычисляемые в ячейку C11) в левую верхнюю ячейку нашей таблицы данных (A15), введя = C11 .

Мы теперь готовы обмана Excel в имитации итераций 1000 спроса для каждого количества производства. Выделите диапазон ячеек таблицы (A15:E1014) и нажмите кнопку Анализ What, если в группе Работа с данными на вкладке "данные", затем выберите таблица данных. Чтобы настроить двумерная таблица данных, выберите нашей количества производства (ячейка C1) как ячейки ввода строки и выберите любую пустую ячейку (мы выбрали ячейку I14) в качестве входных данных ячейки столбца. После нажатия кнопки ОК, Excel имитирует 1000 запросу значения для каждого количество заказа.

Чтобы понять, почему это работает, рассмотрите возможность помещены в таблице данных в диапазоне ячеек C16:C1015 значения. Для каждого из этих ячеек Excel будет использовать значение 20 000 в ячейке C1. В C16 значение столбца Подставлять значения по строкам 1 помещается в пустую ячейку и случайное число в ячейке C2 пересчитывается. Выберите соответствующий отчет о прибылях указан в ячейке C16. Затем входного значения ячейки столбца 2 помещается в пустую ячейку и еще раз пересчитывает случайное число в ячейке C2. В ячейке C17 вводится соответствующих profit.

Путем копирования ячейки 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 карточек, будут всегда продается некоторые из них без любой leftovers.

Примечание: В этой книге пересчета присвоено Автоматически, кроме таблиц . (Воспользуйтесь командой вычислений в группе вычисления на вкладке "формулы"). Это обеспечит нашей таблице данных пересчитываются Если мы нажмите клавишу F9, которая лучше поскольку большие объемы данных таблицы замедлится свою работу, если он будет пересчитываться всякий раз вы введете слова на лист. Обратите внимание, что в этом примере при нажатии клавиши F9, среднюю прибыль изменится. Это происходит потому, что каждый раз при нажатии клавиши F9, другой последовательности 1000 случайных чисел используется для создания требования количество каждого заказа.

Доверительный интервал для означает прибыли Естественные вопрос в этом случае: в каких интервал адаптация и убедиться, что ИСТИНА среднюю прибыль будет находиться в интервале 95%? Этот интервал называется 95 процентов доверительный интервал для среднего profit . 95 процентов доверительный интервал для среднего выходные данные моделирование вычисляется по следующей формуле:

    Продавец GMC считает нормально распределенным потребность делегатов 2005 с 200 средним и стандартным отклонением 30. Получение о представителе его стоимость составляет $25000 и он продаваемых о представителе за 40 000 рублей. Половина всех делегатов, не проданных по полной цены могут быть проданы за 30 000. Он учет порядком 200, 220, 240, 260, 280 или 300 делегатов. Сколько должны он заказать?

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

    Требование

    Вероятность

  1. Супермаркет выплатой $1,00 для каждой копии людей и продаваемых для $1.95. Каждой Непроданное копии могут быть возвращены для $0,50. Сколько копий Пользователи должны хранилище порядке?

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте