Технология имитационного моделирования в среде MS Excel

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

• Ввести значения постоянных переменных (табл. 2.) в ячейки В2, В4 и D2, D4 листа «Результаты анализа».
• Ввести значения диапазонов изменений ключевых переменных (табл. 1.) в ячейки В3, С5 листа «Имитация».
• Задать в ячейке В7 требуемое число экспериментов.
• Установить курсор в ячейку А11 и вставить необходимое число строк в шаблон (номер последней строки будет вычислен в Е7).
• Скопировать формулы блока А10, Е10 требуемое количество раз.
• Перейти к листу «Результаты анализа» и проанализировать полученные результаты.

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

На следующем  шаге необходимо вставить в шаблон нужное количество строк (18).

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

 

Рис. 4. Результаты имитации

 

Рис. 5. Результаты анализа

 

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

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

Нетрудно  заметить, что в целом, вариация значений всех трех параметров носит случайный  характер, что подтверждает принятую ранее гипотезу о их независимости. Для сравнения ниже приведен график распределений потока платежей NCF и  величины NPV (рис. 7).

 

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

 

 

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

 

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

 

 

4. Статистический анализ результатов имитации

 

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

имитационный  моделирование excel корреляция

4.1 Ковариация и корреляция

 

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

 

 (3)

 

где X, Y – множества значений случайных величин размерности m; M (X) – математическое ожидание случайной величины Х; M (Y) – математическое ожидание случайной величины Y.

Как следует  из (3), положительная ковариация наблюдается  в том случае, когда большим  значениям случайной величины Х  соответствуют большие значения случайной величины Y, т.е. между ними существует тесная прямая взаимосвязь. Соответственно отрицательная ковариация будет иметь место при соответствии малым значениям случайной величины Х больших значений случайной величины Y. При слабо выраженной зависимости значение показателя ковариации близко к 0.

Ковариация  зависит от единиц измерения исследуемых  величин, что ограничивает ее применение на практике. Более удобным для  использования в анализе является производный от нее показатель – коэффициент корреляции R, вычисляемый по формуле:

 

 (4)

 

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

Определение количественных характеристик для  оценки тесноты взаимосвязи между  случайными величинами в MS Excel может быть осуществлено двумя способами:

• с помощью статистических функций КОВАР () и КОРРЕЛ ();
• с помощью специальных инструментов статистического анализа.

Если  число исследуемых переменных больше 2, более удобным является использование  инструментов анализа.

4.2 Инструмент анализа данных «Корреляция»

 

Определим степень тесноты взаимосвязей между  переменными V, Q, P, NCF и NPV. При этом в  качестве меры будем использовать показатель корреляции R.

Выберем в главном меню тему «Сервис» пункт  «Анализ данных». Результатом выполнения этих действий будет появление диалогового  окна «Анализ данных», содержащего  список инструментов анализа.

Выберем из списка «Инструменты анализа» пункт  «Корреляция» и нажмем кнопку «ОК» (рис. 8). Результатом будет появление окна диалога инструмента «Корреляция».

Заполним  поля диалогового окна, как показано на рис. 9 и нажмем кнопку «ОК».

Вид полученной ЭТ после выполнения элементарных операций форматирования приведен на рис. 10.

 

Рис. 8. Список инструментов анализа (выбор пункта «Корреляция»)

 

Рис. 9. Заполнение окна диалога инструмента «Корреляция»

 

Рис. 10

 

4.3 Результаты корреляционного анализа

 

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

Как следует  из результатов корреляционного анализа, гипотеза о независимости распределений ключевых переменных V, Q, P в целом подтвердилась. Значения коэффициентов корреляции между переменными расходами V, количеством Q и ценой Р (ячейки В3. В4, С4) достаточно близки к 0.

В свою очередь  величина показателя NPV напрямую зависит  от величины потока платежей (R = 1). Кроме  того, существует корреляционная зависимость  средней степени между Q и NPV (R = 0,613), P и NPV (R = 0,513). Как и следовало ожидать, между величинами V и NPV существует умеренная обратная корреляционная зависимость (R = -0,475).

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

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

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

Для проверки гипотезы о нормальном распределении  случайной величины применяются  специальные статистические критерии: Колмогорова-Смирнова. В целом MS Excel позволяет быстро и эффективно осуществить расчет требуемого критерия и провести статистическую оценку гипотез.

Однако  в простейшем случае для этих целей  можно использовать такие характеристики распределения, как асимметрия (скос) и эксцесс. Напомним, что для нормального  распределения эти характеристики должны быть равны 0. На практике близкими к нулевым значениями можно пренебречь. Для вычисления коэффициента асимметрии и эксцесса в MS Excel реализованы специальные статистические функции – СКОС () и ЭКСЦЕСС ().

4.4 Инструмент анализа данных «Описательная статистика»

 

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

Определим параметры описательной статистики для переменных V, Q, P, NCF, NPV. Для этого  необходимо выполнить следующие  шаги.

Выберем в главном меню тему «Сервис» пункт  «Анализ данных». Результатом выполнения этих действий будет появление диалогового  окна «Анализ данных», содержащего  список инструментов анализа.

Выберем из списка «Инструменты анализа» пункт  «Описательная статистика» и  нажмем кнопку «ОК». Результатом будет  появление окна диалога инструмента  «Описательная статистика».

Заполним  поля диалогового окна, как показано на рис. 11 и нажмем кнопку «ОК».

Результатом выполнения указанных действий будет  формирование отдельного листа, содержащего  вычисленные характеристики описательной статистики для исследуемых переменных. Выполнив операции форматирования, можно  привести полученную ЭТ к более наглядному виду (рис. 12).

 

Рис. 11. Заполнение полей диалогового окна «Описательная статистика»

 

 

 

Рис. 12. Описательная статистика для исследуемых переменных

 

Многие  из приведенных в данной ЭТ характеристик  нам уже хорошо знакомы, а их значения уже определены с помощью соответствующих  функций на листе «Результаты  анализа». Поэтому рассмотрим лишь те из них, которые не упоминались  ранее.

Вторая  строка ЭТ содержит значения стандартных  ошибок e для средних величин распределений. Другими словами среднее или  ожидаемое значение случайной величины М(Е) определено с погрешностью ± e.

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

Как следует  из полученных результатов, данное условие  соблюдается для исходных переменных V, Q, P (значения медиан лежат в диапазоне  М (Е) ± e, т.е. – практически совпадают со средними). Однако для результатных переменных NCF, NPV значения медиан лежат ниже средних, что наводит на мысль о правосторонней асимметричности их распределений.

Мода  – наиболее вероятное значение случайной величины (наиболее часто встречающееся значение в интервале данных). Для симметричных распределений мода равна математическому ожиданию. Иногда мода может отсутствовать. В данном случае, в некоторых ячейках таблицы MS Excel вернул сообщение об ошибке. Таким образом, вычисление моды не представляется возможным.

Эксцесс характеризует остроконечность (положительное  значение) или пологость (отрицательное  значение) распределения по сравнению  с нормальной кривой. Теоретически, эксцесс нормального распределения  должен быть равен 0. Однако на практике для генеральных совокупностей  больших объемов его малыми значениями можно пренебречь.

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

Асимметричность (коэффициент асимметрии или скоса  – s) характеризует смещение распределения относительно математического ожидания. При положительном значении коэффициента распределение скошено вправо, т.е. его более длинная часть лежит правее центра (математического ожидания) и обратно. Для нормального распределения коэффициент асимметрии равен 0. На практике, его малыми значениями можно пренебречь.