Имитационное моделирование неопределенных процессов

ЛЕКЦИЯ 9 Имитационное моделирование неопределенных процессов Это её одна интересная и полезная функция MS Excel, используемая  в экономическом моделировании для оценки вероятности неопределенных событий, таких, например, как эффективности инвестиций, прогнозирования чистой прибыли,  рисков, колебаний валют и т.д.  Метод Монте – Карло позволяет «проигрывать» различные ситуации и может использоваться как составная часть процесса принятия решения.  Excel включает все инструменты для построения имитационной модели на основе функций рабочего листа и технологии анализа «ЧТО-ЕСЛИ». Рассмотрим следующий пример. Пример 1.  Предприятие связи закупило поздравительные почтовые открытки в количестве 2300 шт. по цене 5,5 руб. за штуку. Цена реализации составляет 12 рублей. Не проданные вовремя  открытки продают по цене 1 руб. Предположим, что спрос описывается дискретной случайной величиной. Сколько открыток нужно заказать для получения максимальной прибыли?   Порядок действий:   Смоделируем многократно возможные объемы заказов (в нашем примере для 20 случаев) и определим, какой из объемов заказов принесет максимальную прибыль.   1. Заполним лист «Монте-Карло» исходными данными в соответствии с рисунком 148. В диапазоне D3:E6 введем границы диапазонов случайных величин (D3:D6) и интервалов возможного спроса (Е3:Е6).   Рисунок 1 – Лист с исходными данными Случайные числа от 0 до 0,1 соответствуют спросу 10000, от 0,1 до 0,45 – 20000 и т.д. Присвоим имена диапазонам и ячейкам для простоты понимания формул, которые будут использоваться в расчетах (рисунок 2). Рисунок 2 – Окно Диспетчер имен 2. В ячейку В6 вставим функцию СЛЧИСЛО(), возвращающую с равной вероятностью значение из интервала от 0 до 1. Поскольку функция будет заново генерировать случайное число при каждом обновлении рабочего листа, то вид рабочего будет постоянно изменяться при вводе новых данных. Случайное число будем использовать для определения величины спроса при очередном варианте расчета (диапазон D3:E6, рисунок 1). 3. В ячейку В3 введем функцию вертикального просмотра для определения величины спроса при сгенерированном значении вероятности. Пример заполнения окна функции приведен на рисунке 3. 4. В ячейку В12 введем формулу для расчета выручки. Выбираем минимальное среди значений спроса и заказа и умножаем его на цену продажи открытки. Учитываем в формуле не проданные открытки, которые реализуются по сниженной цене с помощью функции ЕСЛИ(): =МИН(Спрос;Заказано)*Цена_продажи+ЕСЛИ(Спрос<Заказано;(Заказано-Спрос)*Цена_со_скидкой;0). 5. В ячейке В13 рассчитаем общую сумму затрат по формуле: =Заказано*Цена_закупки. 6. В ячейку В14 введем формулу расчета прибыли:  =Выручка-Затраты. Вид рабочего листа с формулами приведен на рисунке 4. Результаты расчета основных показателей представлены  на рисунке 5. Рисунок 3 – Вид окно функции ВПР() Рисунок 4 – Вид листа с формулами Рисунок 5 – Результаты расчета основных показателей 7. Подсчитаем сумму прибыли при каждой величине заказа для 20 испытаний.  В диапазоне А17:Е37 создадим таблицу чувствительности с двумя переменными (рисунок 6). Для этого заполним диапазон А18:А37 номерами испытаний, а диапазон В17:Е17 – возможными объемами заказов.  В ячейку А17 введем ссылку на формулу для расчета прибыли. Выделим диапазон таблицы чувствительности (А17:А37) и выполним команду Таблица данных… Заполним окно таблицы данных: o В поле Подставлять значения по столбцам в: введем адрес ячейки с суммой заказа (В5). o В поле Подставлять значения по строкам в: укажем ссылку на любую свободную ячейку, например, D8 (рисунок 6). После нажатия кнопки ОК таблица будет заполнена расчетными значениями. Рисунок 6 – Окно таблицы данных Поясним, как Excel выполняет расчеты таблицы чувствительности. В расчетах значений в столбце В таблицы чувствительности Excel будет использовать значение из ячейки В5, равное 10000. Для расчета значения в ячейке, например, В18 в свободную ячейку вводится значение входного столбца таблицы данных, равное 1 и в ячейке В6 генерируется случайное число. Рассчитанная сумма прибыли записывается в ячейку В18. Далее значение ячейки входного столбца таблицы 2 подставляются в свободную ячейку, а в ячейке В19 вычисляется прибыль, величина которой определяется сгенерированным числом. 8. Вычислим среднее значение смоделированной прибыли с помощью функции СРЗНАЧ().  В ячейках В38:Е38 вставим соответственно функции  =СРЗНАЧ(B18:B37), =СРЗНАЧ(C18:C37), =СРЗНАЧ(D18:D37), =СРЗНАЧ(E18:E37). В диапазоне В39:Е39 рассчитаем стандартное отклонение по выборке с помощью функции СТАНДОТКЛОН.В() с указанием соответствующих диапазонов выборок (рисунок 7). Рисунок 7 – Формулы расчета доверительного интервала Каждая функция вводится  только в первую ячейку диапазона, а в остальные ячейки копируется путем перетаскивания мышью. Нажатием клавиши для всех объемов заказов моделируются 20 итераций разных уровней спроса. На рисунке 9 приведены результаты моделирования с использованием метода Монте-Карла при текущем случайном значении ячейки В6. Искомой величиной заказа будет являться та, которая будет давать наибольшую расчетную прибыль. В нашем примере, заказ, обеспечивающий максимальную прибыль равен 60000. Если заказ будет составлять 10000, то прибыль минимальна, но всегда постоянна (см. рисунок 8), а стандартное отклонение равно 0. В этом случае спрос превышает предложение, все открытки будут распроданы и полностью отсутствует риск превышения суммы затрат над суммой выручки. 6. Определим, в какой интервал с вероятностью 95% попадает истинное значение средней прибыли, т.е. доверительный интервал для средней прибыли с доверительной вероятностью 95%. Доверительный интервал вычисляется по формуле: Средняя_прибыль±1,96*Станд.отклон/ В ячейки Е12 и Н13 введем расчетные формулы для нижней и верхней границы доверительного интервала для средней прибыли при разных объемах заказов (рисунок 8): Рисунок 8 – Формулы расчета доверительного интервала На рисунке 9 в диапазоне E12:H13 представлены результаты вычислений границ доверительного интервала. Таким образом, с 95% уверенностью можно говорить, что средняя прибыль от продажи открыток будет находиться в пределах соответствующих нижних и верхних границ для каждой величины заказа. Рисунок 9 – Результаты моделирования