Имитационное моделирование с применением функций MS Excel

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

Тогда для имитации значений требуемой переменной можно воспользоваться математическими функциями СЛЧИС () или СЛУЧМЕЖДУ().

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

Функция СЛУЧМЕЖДУ (нижн_граница; верхн_граница)

Эта функция позволяет получить случайное число из заданного интервала.

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

Рисунок 6 Имитация.

Первая часть листа (блок ячеек А1:Е7) предназначен для ввода диапазонов изменений ключевых переменных, значения которых будут генерироваться в процессе проведения эксперимента. В ячейке В7 задается общее число имитаций (экспериментов). Формула, заданная в ячейке Е7 вычисляет номер последней строки выходного блока, в который будут помещены полученные значения.

Вторая часть листа (блок ячеек А9:Е11) предназначена для проведения имитации. Формулы в ячейках А10:С11 генерируют значения для соответствующих переменных с учетом заданных в ячейках В3:С5 диапазонов их изменений.

Формулы в ячейках D10:Е11 вычисляют величину потока платежей и его чистую текущую стоимость соответственно. При этом значения постоянных переменных берутся из листа Результаты анализа.

Лист «Результаты анализа» (Рисунок 7. Результаты анализа.), кроме значений постоянных переменных, содержит также функции, вычисляющие параметры распределения изменяемых (Q,V,P) и результирующих (NCF,NPV) переменных и вероятности различных событий. Общий вид листа показан на рис.

Рисунок 7. Результаты анализа.

Формулы листа содержат ряд функций, поэтому приведем необходимые пояснения.

Функция СРЗНАЧ()

Вычисляет среднее арифметическое значений, заданных в списке аргументов. Помимо чисел в расчете могут участвовать текст и логические значения, такие как ИСТИНА и ЛОЖЬ.

Синтаксис

СРЗНАЧ(значение1,значение2,…)

Значение1, значение2,… – это от 1 до 30 ячеек, интервалов ячеек или значений, для которых вычисляется среднее.

Функция ДИСПР()

Вычисляет дисперсию для генеральной совокупности.

Синтаксис

Функция СТАНДОТКЛОНП()

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

Синтаксис

СТАНДОТКЛОНП(число1; число2; …)

Число1, число2, … – это от 1 до 30 числовых аргументов, соответствующих генеральной совокупности. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

• Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если текст и логические значения игнорироваться не должны, следует использовать функцию рабочего листа СТАНДОТКЛОНА.

Замечания

• СТАНДОТКЛОНП предполагает, что аргументы образуют всю генеральную совокупность. Если данные являются только выборкой из генеральной совокупности, то стандартное отклонение следует вычислять с использованием функции СТАНДОТКЛОН.
• Для больших выборок СТАНДОТКЛОН и СТАНДОТКЛОНП возвращают примерно равные значения.
• Стандартное отклонение вычисляется с использованием «смещенного» или «n» метода.

Функции МИН() и МАКС() вычисляют минимальное и максимальное значения для массива данных из блока ячеек, указанного в качестве их аргумента. Функция СЧЕТЕСЛИ() осуществляет подсчет количества ячеек в указанном блоке, значения которых удовлетворяют заданному условию. Функция имеет следующий формат СЧЕТЕСЛИ (блок; «условие»). В данном случае заданная в ячейке F13 эта функция подсчитывает количество отрицательных значений NPV ,содержащихся в блоке ячеек ЧСС.

Функция СУММЕСЛИ() суммирует значения ячеек в указанном блоке, если они удовлетворяют заданному условию. Функция имеет следующий формат: СУММЕСЛИ (блок; «условие»). В данном случае заданные в ячейках F14,F15 функции подсчитывают суммы отрицательных и положительных значений NPV ,содержащихся в блоке ЧСС.

Две последние формулы (ячейки Е18 и F18) предназначены для проведения вероятностного анализа распределения NPV . В нашем примере мы исходим из предположения о независимости и равномерном распределении ключевых переменных Q,V,P . Однако какое распределение при этом будет иметь результирующая величина – показатель NPV - заранее определить нельзя.

Одно из возможных решений этой проблемы – попытаться аппроксимировать неизвестное распределение каким-либо известным. При этом в качестве приближения удобнее всего использовать нормальное распределение. Это связано с тем, что в соответствии с центральной предельной теоремой теории вероятностей при выполнении определенных условий сумма большого числа случайных величин имеет распределение, приблизительно соответствующее нормальному. В прикладном анализе для целей аппроксимации широко применяется частный случай нормального распределения – так называемое стандартное нормальное распределение.Математическое ожидание стандартно распределенной случайной величины Е равно 0: М(Е) =0. График этого распределения симметричен относительно оси ординат и оно характеризуется всего одним параметром – стандартным отклонением s , равным 1.

Приведение случайной переменной Е к стандартно распределенной величине Z осуществляется с помощью, так называемой нормализации – вычитания средней и последующего деления на стандартное отклонение:

Величина Z выражается в количестве стандартных отклонений. Для вычисления вероятностей по значению нормализованной величины Z используются функции НОРМАЛИЗАЦИЯ() и НОРМСТРАСП()

Функция НОРМАЛИЗАЦИЯ (х; среднее; станд_откл)

Эта функция вычисляет нормализованное значение величины Х, на основании которого затем вычисляется искомая вероятность. Функция требует задания трех аргументов:

Х – нормализуемое значение;

Среднее – математическое ожидание случайной величины Е;

Станд_откл – стандартное отклонение

Полученное значение Z является аргументом для следующей функции НОРМСТРАСП()

Функция НОРМСТРАСП (Z) Эта функция вычисляет стандартное нормальное распределение, т.е. вероятность того, что случайная нормализованная величина Е будет меньше или равна Х. Она имеет всего один аргумент, вычисляемый функцией НОРМАЛИЗАЦИЯ().

Эти функции необходимо использовать вместе.

НОРМРАСП (НОРМАЛИЗАЦИЯ (Х; среднее; станд_откл)).

Приступаем к имитационному эксперименту. Для его проведения необходимо выполнить следующие шаги:

1. Ввести значения постоянных переменных в ячейки В2.В4 и D2.D4 листа «Результаты анализа».

2. Ввести значения диапазонов изменений ключевых переменных в ячейки В3.С5 листа «Имитация».

3. Задать в ячейке В7 требуемое число экспериментов.

4. Скопировать формулы блока А10.Е10 требуемое количество раз.

5. Перейти к листу «Результаты анализа» и проанализировать полученные результаты.

Рассмотрим реализацию выделенных шагов более подробно. Выполнение первых трех пунктов не должно вызвать особых затруднений. Введите значения постоянных переменных в ячейки В2.В4 листа «Результаты анализа». Введите значения диапазонов изменений ключевых переменных в ячейки В3.С5 листа «Имитация». Укажите в ячейке В7 число проводимых экспериментов, например – 500. Установите табличный курсор в ячейку А11.

На следующем шаге необходимо вставить в шаблон нужное количество строк (500) . Однако выделение такого количества строк при помощи указателя мыши – достаточно трудоемкая операция. EXCEL предоставляет более эффективные процедуры для выполнения подобных операций. В частности, в данном случае можно воспользоваться операцией перехода, которую также удобно применять и для выделения больших диапазонов ячеек.

Нажмите функциональную клавишу [F5]. На экране появится окно диалога «Переход» (Рисунок 8).

Рисунок 8. Окно диалога «Переход»

Для перехода к нужному участку электронной таблицы достаточно указать в поле «Ссылка» адрес или имя соответствующей ячейки (блока). В данном случае, таким адресом будет любая ячейка последней вставляемой строки. Например, в качестве адреса перехода может быть указана ячейка А508.

Введите в поле «Ссылка» адрес: А508 и нажмите комбинацию клавиш [SHIFT] + [ENTER]. Результатом выполнения этих действий будет выделение блока А10.А508.

Теперь необходимо заполнить вставленные строки формулами блока ячеек А10.Е10.

Результатом выполнения этих действий будет заполнение блока А10.Е509 случайными значениями ключевых переменных V, Q, P и результатами вычислений величин NCF и NPV. Фрагмент возможных результатов имитации приведен в Таблице19. Результат имитации

Таблица 19 Результат имитации

Соответствующие проведенному эксперименту результаты анализа
приведены на рисунке 9.

Рисунок 9. Результат анализа

Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 7%. Еще больший оптимизм внушают результаты анализа распределения чистых поступлений от проекта NCF. Величина стандартного отклонения здесь составляет всего 42% от среднего значения. Таким образом с вероятностью более 90% можно утверждать, что поступления от проекта будут положительными величинами.

Сумма всех отрицательных значений NPV в полученной генеральной совокупности (ячейка F14) может быть интерпретирована как чистая стоимость неопределенности для инвестора в случае принятия проекта. Аналогично сумма всех положительных значений NPV (ячейка F15) может трактоваться как чистая стоимость неопределенности для инвестора в случае отклонения проекта. Несмотря на всю условность этих показателей, в целом они представляют собой индикаторы целесообразности проведения дальнейшего анализа.

Сумма всех отрицательных значений NPV в полученной генеральной совокупности (ячейка F14) может быть интерпретирована как чистая стоимость неопределенности для инвестора в случае принятия проекта. Аналогично сумма всех положительных значений NPV (ячейка F15) может трактоваться как чистая стоимость неопределенности для инвестора в случае отклонения проекта. Несмотря на всю условность этих показателей, в целом они представляют собой индикаторы целесообразности проведения дальнейшего анализа.

В данном случае они наглядно демонстрируют несоизмеримость суммы возможных убытков по отношению к общей сумме доходов (-11691,92 и 1692669,76 соответственно).

На практике одним из важнейших этапов анализа результатов имитационного эксперимента является исследование зависимостей между ключевыми параметрами. Методы оценки степени зависимости, а также технология ее автоматизации путем применения специальных инструментов ППП EXCEL, будут продемонстрированы ниже. Здесь же мы ограничимся визуальным (графическим) исследованием. На Рисунок 10 Распределение значений параметров V, P и Q приведен график распределения значений ключевых параметров V, P и Q, построенный на основании 75 имитаций.

Рисунок 10 Распределение значений параметров V, P и Q

Рисунок 11. Зависимость между NCF и NPV .

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

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