/

Метод монте карло в excel пример. Моделирование методом Монте-Карло в Crystal Ball для Excel

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

Инструмент Кем разработан Описание
@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 упоминавшейся книги Дугласа Хаббарда

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

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

Глава 2. Примеры использования метода Монте-Карло 8

2.1 Простейший пример использования метода Монте-Карло 8

2.2 Вычисление числа Пи методом Монте-Карло 8

2.2.1 Постановка задачи для нахождения числа Пи методом Монте-Карло 10

2.2.2 Листинг программы для нахождения числа Пи методом Монте-Карло 10

2.3 Решение задачи аналитически и методом Монте-Карло 12

Глава 3. Генерация случайных чисел 17

Заключение 20

Список литературы 21

Введение

Методы Монте-Карло – это общее название группы методов для решения различных задач с помощью случайных последовательностей. Эти методы (как и вся теория вероятностей) выросли из попыток людей улучшить свои шансы в азартных играх. Этим объясняется и тот факт, что название этой группе методов дал город Монте-Карло – столица европейского игорного бизнеса (казино), где играют в рулетку – одно из простейших устройств для получения случайных чисел, на использовании которых основан этот метод.

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

Глава 1. Предыстория и определение метода Монте-Карло

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

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

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

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

Основными недостатками аналитических методов являются:

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

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

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

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

    Они чрезвычайно громоздки. Объем промежуточной информации трудно вместить даже в память современного компьютера.

    Оценка погрешности решения представляет намного более трудную процедуру, чем сам процесс решения. Зачастую она просто невозможна.

Метод статистических испытаний свободен от всех этих недостатков.

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

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

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

М(Х)= A .

Практически же поступают так: производят N испытаний, в результате которых получают N возможных значений X, вычисляют их среднее арифметическое и принимают его в качестве оценки (приближенного значения) A ’ искомого числа A .

Как правило, составляется программа для осуществления одного случайного испытания. Погрешность вычислений, как правило, пропорциональна , где D – некоторая постоянная.

Это значит, что N должно быть велико, поэтому метод существенно опирается на возможности ЭВМ. Ясно, что добиться таким путем высокой точности невозможно. Это один из недостатков метода. Во многих задачах удается значительно увеличить точность, выбрав способ расчета, которому соответствует значительно меньшее D .

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

Отыскание возможных значений случайной величины Х (моделирование) называют «разыгрыванием случайной величины».

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

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

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

Преимущество метода Монте-Карло состоит в том, что он способен “сработать” там, где не справляются другие методы.

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

Приведем примеры задач, решаемых методом Монте-Карло:

      расчет системы массового обслуживания;

      расчет качества и надежности изделий;

      теория передачи сообщений;

      вычисление определенного интеграла;

      задачи вычислительной математики;

      задачи нейтронной физики и другие.

Глава 2. Примеры использования метода Монте-Карло

2.1 Простейший пример использования метода Монте-Карло

Предположим, что нам нужно определить площадь плоской фигуры, расположенной внутри единичного квадрата, т.е. квадрата, сторона которого равна единице (рис. 1). Выберем внутри квадрата наугад N точек. Обозначим через M количество точек, попавших при этом внутрь фигуры. Тогда площадь фигуры приближенно равна отношению . Отсюда, чем больше N , тем больше точность такой оценки.

Рисунок 1. Площадь фигуры приближенно равна, отношению числа точек попавших в фигуру к числу всех точек.

2.2 Вычисление числа Пи методом Монте-Карло

Попробуем построить метод Монте-Карло для решения задачи о вычислении числа Пи. Для этого рассмотрим четверть круга единичного радиуса (рис. 2). Площадь круга равна
, очевидно, площадь четверти круга равна:

.

Зная, что радиус круга равен 1, получим:



X


Рисунок 2. Нахождение числа Пи методом Монте-Карло.

Площадь же всего единичного квадрата OABC равна 1. Будем случайным образом выбирать точки внутри квадрата OABC . Координаты точек должны быть,
и
. Теперь подсчитаем количество точек таких, что
, т.е. те точки, которые попадают внутрь круга.

Пусть всего было испытано N точек, и из них M попало в круг. Рассмотрим отношение количества точек, попавших в круг, к общему количеству точек (M /N ). Очевидно, что чем больше случайных точек мы испытаем, тем это отношение будет ближе к отношению площадей четверти круга и квадрата. Таким образом, имеем, что, для достаточно больших N , верно равенство:

.

Из полученного равенства:

.

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

2.2.1 Постановка задачи для нахождения числа Пи методом Монте-Карло

Для проверки формулы , была написана программа в среде программирования Турбо Паскаль. В программе нужно ввести число K – количество испытаний и число N – количество испытываемых точек. Для координат точек (X, Y) используется генератор случайных чисел. Результаты всех испытаний усредняются.

2.2.2 Листинг программы для нахождения числа Пи методом Монте-Карло

K, {количество испытаний}

N, {количество точек}

i, j: word; {для циклов}

s, {сумма всех Пи}

P: real; {среднеарифметическое значение Пи}

{функция возвращает число Пи}

FUNCTION raschet: real;

x, y: word; {координаты точек}

M: word; {число точек попавших в окружность}

for i:=1 to N do

x:=random(2); {x, y – случайные числа}

if sqr(x)+sqr(y)<=1 then inc(M); {точка с координатами x, y попала в круг}

raschet:=4*M/N; {из формулы }

write("Введите количество испытаний: ");

write("Введите количество испытываемых точек: ");

for j:=1 to K do s:=s+raschet;

writeln("Число Пи, рассчитанное методом Монте-Карло равно:");

writeln("Точное число Пи равно:");

writeln(Pi:1:6);

Итак, с помощью этой программы была проверена верность формулы . В результате получилось число Пи равное: 3.000808 , при количестве испытаний 500 раз с количеством точек 5000. Точное число Пи равно: 3.141593 .

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

2.3 Решение задачи аналитически и методом Монте-Карло

Рассмотрим задачу:

Система контроля качества продукции состоит из трех приборов. Вероятность безотказной работы каждого из них в течение времени Т равна 5/6. Приборы выходят из строя независимо друг от друга. При отказе хотя бы одного прибора вся система перестает работать. Найти вероятность
того, что система откажет за время Т.

Аналитическое решение.

Событие А – выход из строя хотя бы одного из трех приборов за время Т и событие – ни один из трех приборов не выйдет из строя за время Т, противоположные. Вероятность
– искомая вероятность. Отсюда:

Теперь решим задачу методом Монте-Карло.

Напомним, что при использовании данного метода возможны два подхода: либо непосредственно проводят эксперименты, либо имитируют их другими экспериментами, имеющими с исходными одинаковую вероятностную структуру. В условиях данной задачи «натуральный» эксперимент – наблюдение за работой системы в течение времени Т. Многократное повторение этого эксперимента может оказаться трудноосуществимым или просто невозможным. Заменим этот эксперимент другим.

Для определения того, выйдет или не выйдет из строя за время Т отдельный прибор, будем подбрасывать игральную кость. Если выпадет одно очко, то будем считать, что прибор вышел из строя; если два, три, четыре, пять, шесть очков, то будем считать, что прибор работал безотказно. Вероятность того, что выпадет одно очко, так же как и вероятность выхода прибора из строя, равна 1/6, а вероятность того, что выпадет любое другое число очков, как и вероятность безотказной работы прибора, равна 5/6.

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

Повторим испытание, состоящее в подбрасывании трех игральных костей, много раз подряд и найдем отношение числа M – отказов системы к общему числу N – проведенных испытаний. Вероятность отказа будет равна:

.

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

Данная программа рассчитана на любые подобные задачи. В конце расчетов программа выдает два ответа. Первый – полученный методом Монте-Карло по формуле . Второй – полученный аналитическим методом по формуле .

В программе нужно ввести: B – количество приборов; вероятность в виде дроби; N – количество проведенных опытов.

B, {количество приборов}

S, D: byte; {вероятность P(A)=S/D}

N, {количество опытов}

i, j, {для циклов}

summa: word; {суммарное число отказов}

P_M, P_A: real; {полученная вероятность}

{функция возвращает количество отказов за одно испытание}

FUNCTION otkaz: word;

for i:=1 to B do

R:=random(D+1)+1; {случайное число >=1 и <=D}

if R<=D-S then inc(o); {выпал "отказ"}

write("Введите количество приборов: ");

writeln("Введите вероятность безотказной работы (в виде дроби):");

write(" числитель – ");

write(" знаменатель – ");

readln(D); {т.е. P=S/D}

write("Введите количество опытов: ");

{расчет методом Монте-Карло}

for j:=1 to N do summa:=summa+otkaz;

{расчет аналитическим методом}

for i:=1 to B-1 do P_A:=P_A*S/D; {возведение в степень}

writeln("* * * Ответ * * *");

writeln("Методом Монте-Карло: ", P_M:1:6);

writeln("Аналитическим методом: ", P_A:1:6);

Итак, проверив формулу с помощью своей программы со значениями: количество приборов – 3; вероятность безотказной работы ; количество опытов – 50000, я получил два ответа. Решение задачи методом Монте-Карло – 0.429420 . Решение задачи аналитическим методом – 0.421296 . Отсюда вывод – вероятность, полученная разными методами сходна.

Глава 3. Генерация случайных чисел

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

Итак, дадим определение этих чисел. Обозначим через R непрерывную случайную величину, распределенную равномерно в интервале (0, 1).

Случайными числами называют возможные значения r j непрерывной случайной величины R, распределенной равномерно в интервале (0, 1).

В действительности пользуются не равномерно распределенной случайной величиной R , возможные значения которой имеют бесконечное число десятичных знаков, а квазиравномерной случайной величиной R’, возможные значения которой имеют конечное число знаков. В результате замены R на R ’ разыгрываемая величина имеет не точно, а приближенно заданное распределение.

Случайная величина R’ обладает свойством: вероятность попадания ее в любой интервал, принадлежащий интервалу (0; 1) равна длине этого интервала.

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

Исчерпание этой последовательности при большом числе циклов Монте-Карло или размере системы снижает ее фактический размер до:

N – размер системы (количество частиц);

P – период последовательности псевдослучайных чисел;

k – количество случайных чисел, используемых для определения состояния одной частицы;

n – суммарное количество циклов Монте-Карло, необходимое для стабилизации системы и расчета ее характеристик.

Например, при моделировании системы Изинга, состоящей из 2000 частиц требуется, как правило, не менее 500 циклов МК, т.е. необходимо не менее 10 5 случайных чисел. Если используемый генератор является 16-ти разрядным и не может произвести последовательность, состоящую из более чем 2 16 (65536) псевдослучайных чисел, то фактический размер системы по формуле будет порядка 1000 частиц.

С играми ситуация еще более трагическая: например, колода из 52 карт может быть упорядочена 52! способами. Это примерно 8e67 или 2 226 . Значит для того, чтобы в процессе игры мог возникнуть любой расклад, создателю полноценной карточной игры типа «21» необходим 256 разрядный генератор случайных чисел. Если колода состоит из 36 карт, то соответствующие числа равны 4e41 и 2 138 , т.е. без суперкомпьютера опять не обойдешься. В карточной игре «преферанс» количество вариантов раздач равно 32!/10! или 2 96 , что тоже не мало. Несмотря на несравнимость этих чисел с реальными возможностями 32-х разрядного процессора, необходимо, конечно, использовать его возможности максимально, ведь только так можно приблизиться к разнообразию реальности.

Заключение

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

Задачи, решаемые методом Монте-Карло: расчет системы массового обслуживания; расчет качества и надежности изделий; теория передачи сообщений; вычисление определенного интеграла; задачи вычислительной математики; задачи нейтронной физики; моделирования дискретных и непрерывных случайных величин; моделирования случайных процессов и полей; вычисления многомерных интегралов и другие.

Список литературы

    И.М.Соболь «Метод Монте-Карло», М., 1985

    Интернет-ресурс «Предыстория и определение метода Монте-Карло» /GIS/Learning/Monte-Carlo_2/Page01.htm

    /~gene/probset/prob13.koi8.html

    Интернет-ресурс «Метод Монте-Карло» /Exponenta_Ru/educat/systemat/boziev/13.asp.htm

    Интернет-ресурс «Вундеркинд» /2001/leto/stend/Vynderkind.htm

    Интернет-ресурс «Метод Монте-Карло» /docs/TViMS/NP/lekziitv/lekziya17.htm


Документ

Предыдущих главах настоящей работы. Такая модификация позволила сделать метод Монте -Карло более... 78 до 0,95. Пример одной из таких связей... точками (при использовании метода Монте -Карло ). Основным недостатком первого метода является недостаточная...

  • Потапов виктор николаевич разработка радиометрических систем и методов полевых и дистанционных измерений радиоактивного загрязнения

    Автореферат диссертации

    ... использованием метода Монте -Карло для условий реальной геометрии спектрометрического измерения. Метод Монте -Карло ... расчетов. Глава III. Спектрометрические методы определения... разделе 4.2 приведены примеры использования прибора при измерениях...

  • Глава 11 эконометрические информационные технологии

    Документ

    Итоговой процедуры можно рассчитать (см. примеры в главе 13). В результате итоговую процедуру нельзя... использовании метода сценариев (см. главу 12). При имитационном моделировании часто используется метод статистических испытаний (Монте -Карло ...

    • ЗАКАЗАТЬ РЕШЕНИЕ ЗАДАЧ МЕТОДОМ МОНТЕ-КАРЛО
    Один из самых прикладных методов статистической оценки риска. К нему нужно отнестись с большим участием. В данной статье будет рассмотрен пример имитационного моделирования с использованием данного подхода.

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

    В процессе работы нам понадобится «генератор случайных чисел» из MS Excel и функция «Описательная статистика».

    Оценка риска инвестиционного проекта

    Есть следующие условия задачи:

    Таким образом, нам нужно оценить три периода – за три года. Запишем все исходные данные в таблицу. Значения, полученные в ячейках D5-X5, имеют формулу для вычисления или есть в условиях задачи. Вы, как экономист, с формулами должны быть знакомы. Обратите внимание на заголовок, выделенный красным цветом на рисунке ниже – «Имитационная модель NCF1». Это говорит о том, что мы имитируем первый год, а всего их будет три на разных листах в MS Excel. На новый лист переключиться внизу окна программы.


    Теперь в MS Excel переключаемся на «Данные» и выбираем пункт «Анализ данных».

    В появившемся окне выбираем «Генерация случайных чисел». Выполняем генерацию с параметрами, продемонстрированными на картинке ниже, для пункта «Кол-во пользователей».


    Параметры будут отталкиваться от среднего значения 250, оно есть в ожидаемых значениях в нашей таблице. Нужно выполнить 1000 генераций. Если вы знакомы со статистикой, то понимаете, что большее количество испытаний даёт более точную оценку. Используя метод Монте-Карло, можно имитировать и 10 000 значений для большей точности.

    После мы имитируем все стохастические, то есть, меняющиеся значения по аналогии, как показано выше. Копируем формулы переменных или констант из ячеек D7-X7 под «Результаты имитации» с учетом имитированных значений. Получаем следующий результат.

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

    Теперь делаем то же самое, но для имитационной модели NCF2.


    Это второй год работы проекта. Как видим, под «СКО» процентные соотношения увеличились. Об этом говорится в условии задачи, что налоги и зарплата должны расти каждый год.

    Повторяем это действие в третий раз, увеличивая налоги и зарплаты, как говорит условие.

    Наибольшую важность в оценке инвестиционного проекта имеет параметр NCF – чистый денежный поток. Копируем все значения NCF на четвертый лист с каждой из трёх предыдущих страниц.


    Формула для расчета NPV есть вверху картинки. Используем её. Теперь точно так же заходим в «Данные», жмём на «Анализ данных» и выбираем там «Описательная статистика». Вот, что в появившемся окне вам нужно указать.


    Во входном интервале выбирается 1000 полученных значений NPV. Выходной интервал можете выбрать произвольно. На выходе у вас будет таблица со статистическими данными.

    Вы, как экономист, должны понимать, о чем говорит каждое значение, если нет, то нужно прочитать отдельную статью или главу учебника. Наша статья о том, метод Монте-Карло применяется с использованием функций MS Excel.

    Заключение

    Генерация случайных чисел – наше всё. Именно в оценке того, к чему может привести случайность, заключается статистический метод Монте-Карло. Это работает не только в экономике, но и везде, где есть случайность. Можете посмотреть, как это делается, применительно к зоологии в видео ниже.

    СРСП 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е» следует заказать супермаркету?



    Поделиться