Практические занятия 2, 3

Имитационное моделирование: анализ данных

1 Цель занятия:

1.1 Изучение технологии реализации имитационного моделирования с помощью табличного процессора.

1.2 Формирование навыков работы по организации расчетно-аналитичес-кой работы.

2 Литература:

2.1 Строгалев В.П., Толкачева И.О. Имитационное моделирование. - МГТУ им. Баумана, 2008.

2.2 Боев В.Д., Сыпченко Р.П.. Компьютерное моделирование. - ИНТУИТ.РУ, 2010.

2.3 Афонин В.В., Федосин С.А. Моделирование систем БИНОМ: Лаборатория знаний, Интернет-университет информационных технологий - ИНТУИТ.ру, 2010.

3 Теоретические сведения:

Имитационное моделирование (simulation) является одним из мощнейших методов анализа экономических систем.

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

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

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

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

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

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

Рассмотрим технологию применения имитационного моделирования для анализа рисков инвестиционных проектов в среде ППП EXCEL.

4 Задание:

Моделирование рисков инвестиционных проектов

Имитационное моделирование представляет собой серию численных экспериментов призванных получить эмпирические оценки степени влияния различных факторов (исходных величин) на некоторые зависящие от них результаты (показатели).

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

1) установить взаимосвязи между исходными и выходными показателями в виде математического уравнения или неравенства;

2) задать законы распределения вероятностей для ключевых параметров модели;

3) провести компьютерную имитацию значений ключевых параметров модели;

4) рассчитать основные характеристики распределений исходных и выходных показателей;

5) провести анализ полученных результатов и принять решение.

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

Осуществим имитационное моделирование анализа рисков инвестицион-ного проекта на основании данных примера.

Пример:

Фирма рассматривает инвестиционный проект по производству продукта "А". В процессе предварительного анализа экспертами были выявлены три ключевых параметра проекта и определены возможные границы их изменений (Таблица 2.1). Прочие параметры проекта считаются постоянными величинами (Таблица 2.2).

Таблица 2.1 - Ключевые параметры проекта по производству продукта "А"

Сценарий	Показатели
Наихудший	Наилучший	Вероятный
Объем выпуска - Q
Цена за штуку - P
Переменные затраты - V

Таблица 2.2 - Неизменяемые параметры проекта по производству продукта "А"

Показатели	Наиболее вероятное значение
Постоянные затраты - F
Амортизация - A
Налог на прибыль - T	60%
Норма дисконта - r	10%
Срок проекта - n
Начальные инвестиции - I₀

Первым этапом анализа согласно сформулированному выше алгоритму является определение зависимости результирующего показателя от исходных. При этом в качестве результирующего показателя обычно выступает один из критериев эффективности: NPV, IRR, PI.

Предположим, что используемым критерием является чистая современная стоимость проекта NPV:

(2.1)

где NCFt - величина чистого потока платежей в периоде t.

По условиям примера, значения нормы дисконта r и первоначального объема инвестиций I0 известны и считаются постоянными в течении срока реализации проекта (Таблица 2.2).

По условиям примера ключевыми варьируемыми параметрами являются: переменные расходы V, объем выпуска Q и цена P. Диапазоны возможных изменений варьируемых показателей приведены в таблице 2.1. При этом будем исходить из предположения, что все ключевые переменные имеют равномерное распределение вероятностей.

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

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

Проведение имитационных экспериментов в среде ППП EXCEL можно осуществить двумя способами - с помощью встроенных функций и путем использования инструмента "Генератор случайных чисел" дополнения "Анализ данных" (Analysis ToolPack). Для сравнения ниже рассматриваются оба способа. При этом основное внимание уделено технологии проведения имитационных экспериментов и последующего анализа результатов с использованием инструмента "Генератор случайных чисел".

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

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

Таблица 2.3 Математические функции для генерации случайных чисел

Наименование функции	Формат функции
Оригинальная версия	Локализованная версия
RAND	СЛЧИС	СЛЧИС() – не имеет аргументов
RANDBETWEEN	СЛУЧМЕЖДУ	СЛУЧМЕЖДУ(нижн_граница; верхн_граница)

Функция СЛЧИС()

Функция СЛЧИС() возвращает равномерно распределенное случайное число E, большее, либо равное 0 и меньшее 1, т.е.: 0 <= E < 1. Вместе с тем, путем несложных преобразований, с ее помощью можно получить любое случайное вещественное число. Например, чтобы получить случайное число между a и b, достаточно задать в любой ячейке электронной таблицы следующую формулу:

=СЛЧИС()*(b-a)+a.

Эта функция не имеет аргументов.

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

Функция позволяет получить случайное число из заданного интервала. При этом тип возвращаемого числа (т.е. вещественное или целое) зависит от типа заданных аргументов.

В качестве примера, сгенерируем случайное значение для переменной Q (объем выпуска продукта). Согласно таблице 2.1, эта переменная принимает значения из диапазона 150 - 300.

Введите в любую ячейку электронной таблицы формулу:

=СЛУЧМЕЖДУ(150; 300) (Результат: 210).

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

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

1) выбрать в главном меню тему "Сервис";

2) выбрать пункт "Параметры" подпункт "Вычисления";

3) установить флажок "Вручную" и нажать кнопку "ОК".

Приступаем к разработке шаблона. С целью упрощения и повышения наглядности анализа выделим для его проведения в рабочей книге ППП EXCEL два листа.

Первый лист - "Имитация", предназначен для построения генеральной совокупности (Рисунок 2.1). Определенные в данном листе формулы и собственные имена ячеек приведены в таблицах 2.4 и 2.5.

Рисунок 2.1 - Лист "Имитация"

Таблица 2.4 - Формулы листа "Имитация"

Ячейка	Формула
Е7	=B7+10-2
A10	=СЛУЧМЕЖДУ($B$3;$C$3)
A11	=СЛУЧМЕЖДУ($B$3;$C$3)
B10	=СЛУЧМЕЖДУ($B$4;$C$4)
B11	=СЛУЧМЕЖДУ($B$4;$C$4)
C10	=СЛУЧМЕЖДУ($B$5;$C$5)
C11	=СЛУЧМЕЖДУ($B$5;$C$5)
D10	=(B10(C10-A10)-Пост_расх-Аморт)(1-Налог)+Аморт
D11	=(B11(C11-A11)-Пост_расх-Аморт)(1-Налог)+Аморт
E10	=ПЗ(Норма;Срок;-D10)-Нач_инвест
E11	=ПЗ(Норма;Срок;-D11)-Нач_инвест

Таблица 2.5 - Имена ячеек листа "Имитация"

Адрес ячейки	Имя	Комментарии
Блок A10:A11	Перем_расх	Переменные расходы
Блок B10:B11	Количество	Объем выпуска
Блок C10:C11	Цена	Цена изделия
Блок D10:D11	Поступления	Поступления от проекта NCFt
Блок E10:E11	ЧСС	Чистая современная стоимость NPV

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

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

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

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

Таблица 2.6 - Формулы листа "Результаты анализа"

Ячейка	Формула
B8	=СРЗНАЧ(Перем_расх)
B9	=СТАНДОТКЛОНП(Перем_расх)
B10	=B9/B8
B11	=МИН(Перем_расх)
B12	=МАКС(Перем_расх)
C8	=СРЗНАЧ(Количество)
C9	=СТАНДОТКЛОНП(Количество)
C10	=C9/C8
C11	=МИН(Количество)
C12	=МАКС(Количество)
D8	=СРЗНАЧ(Цена)
D9	=СТАНДОТКЛОНП(Цена)
D10	=D9/D8
D11	=МИН(Цена)
D12	=МАКС(Цена)
E8	=СРЗНАЧ(Поступления)
E9	=СТАНДОТКЛОНП(Поступления)
E10	=E9/E8
E11	=МИН(Поступления)
E12	=МАКС(Поступления)
F8	=СРЗНАЧ(ЧСС)
F9	=СТАНДОТКЛОНП(ЧСС)
F10	=F9/F8
F11	=МИН(ЧСС)
F12	=МАКС(ЧСС)
F13	=СЧЁТЕСЛИ(ЧСС;"<0")
F14	=СУММЕСЛИ(ЧСС;"<0")
F15	=СУММЕСЛИ(ЧСС;">0")
Е18	=НОРМАЛИЗАЦИЯ(D18;$F$8;$F$9)
F18	=НОРМСТРАСП(E18)

Таблица 2.7 - Имена ячеек листа "Результаты анализа"

Адрес ячейки	Имя	Комментарии
B2	Нач_инвест	Начальные инвестиции
B3	Пост_расх	Постоянные расходы
B4	Аморт	Амортизация
D2	Норма	Норма дисконта
D3	Налог	Ставка налога на прибыль
D4	Срок	Срок реализации прока

Рисунок 2.2 - Лист "Результаты анализа"

Функции МИН() и МАКС() вычисляют минимальное и максимальное значение для массива данных из блока ячеек, указанного в качестве их аргумента. Имена и диапазоны этих блоков приведены в таблице 2.7.

Функция СЧЕТЕСЛИ() осуществляет подсчет количества ячеек в указанном блоке, значения которых удовлетворяют заданному условию.

Механизм действия функции СУММЕСЛИ() аналогичен функции СЧЕТЕСЛИ(). Отличие заключается лишь в том, что эта функция суммирует значения ячеек в указанном блоке, если они удовлетворяют заданному условию.

Две последние формулы (ячейки Е18 и F18) предназначены для проведения вероятностного анализа распределения NPV.

В рассматриваемом примере исходим из предположения о независимости и равномерном распределении ключевых переменных Q, V, P. Однако какое распределение при этом будет иметь результатная величина - показатель NPV, заранее определить нельзя.

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

В прикладном анализе для целей аппроксимации широко применяется частный случай нормального распределения. Математическое ожидание стандартно распределенной случайной величины Е равно 0: M(E) = 0. График этого распределения симметричен относительно оси ординат и оно характеризуется всего одним параметром - стандартным отклонением σ, равным 1.

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

(2.2)

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

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

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

Эта функция возвращает нормализованное значение Z величины x, на основании которого затем вычисляется искомая вероятность p(E < x). Она реализует соотношение (2.2). Функция требует задания трех аргументов:

х - нормализуемое значение;

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

станд_откл - стандартное отклонение.

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

Функция НОРМСТРАСП(Z)

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

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

1) ввести значения постоянных переменных (Таблица 2.2) в ячейки В2:В4 и D2:D4 листа "Результаты анализа";

2) ввести значения диапазонов изменений ключевых переменных (Таблица 2.1) в ячейки В3:С5 листа "Имитация";

3) задать в ячейке В7 требуемое число экспериментов, например - 500;

4) установить курсор в ячейку А11 и вставить необходимое число строк в шаблон (номер последней строки будет вычислен в Е7 и равняется 498);

5) скопировать формулы блока А10:Е10 требуемое количество раз;

6) перейти к листу "Результаты анализа" и проанализировать полученные результаты.

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

Рисунок 2.3 - Окно диалога "Переход"

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

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

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

1) выделите и скопируйте в буфер блок ячеек А10.Е10;

2) нажмите комбинацию клавиш [CTRL] + [SHIFT] + [↓ ];

3) нажмите клавишу [ENTER];

4) нажмите клавишу [F9].

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

Фрагмент результатов имитации, полученных автором, приведен на рисунке 2.4.

Рисунок 2.4 - Результаты имитации

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

Рисунок 2.5 - Результаты анализа

По результатам имитационного анализа риск проекта значительно ниже. Величина ожидаемой NPV меньше результата предыдущего анализа (3361,96 и 4502,30 соответственно). Однако величина стандартного отклонения также существенно ниже (2271,31 и 4673,62) и не превышает значения NPV. Коэффициент вариации (0,68) меньше 1, таким образом, риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 7%. Еще больший оптимизм внушают результаты анализа распределения чистых поступлений от проекта NCF. Величина стандартного отклонения здесь составляет всего 42% от среднего значения. Таким образом, с вероятностью более 90% можно утверждать, что поступления от проекта будут положительными величинами.

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

Имитация с инструментом "Генератор случайных чисел"

Инструмент «Генератор случайных чисел» предназначен для автомати-ческой генерации множества данных (генеральной совокупности) заданного объема, элементы которого характеризуются определенным распределением вероятностей. При этом могут быть использованы распределения: равномерное, нормальное, Бернулли, Пуассона, биномиальное, модельное и дискретное. Применение инструмента "Генератор случайных чисел", как и большинства используемых в этой работе функций, требует установки специального дополнения "Пакет анализа".

Для применения этого инструмента изменим условия примера, определив вероятности для каждого сценария развития событий следующим образом (Таблица 2.8). Предположим, что ключевые переменные нормально распределены. Количество имитаций - 500.

Таблица 2.8 - Вероятностные сценарии реализации проекта

Сценарий Показатели	Наихудший P = 0.25	Наилучший P = 0.25	Вероятный P = 0.5
Объем выпуска - Q
Цена за штуку - P
Переменные затраты - V

Сформируем шаблон. Выделим в рабочей книге два листа: "Имитация" и "Результаты анализа". Формирование шаблона начнем с листа "Результаты анализа" (Рисунок 2.6).

Рисунок 2.6 - Лист "Результаты анализа" (шаблон II)

Формулы для расчета вероятностей приведены в таблице 2.9.

Таблица 2.9 - Формулы листа "Результаты анализа" (шаблон II)

Ячейка	Формула
В17	=НОРМРАСП(0;B8;B9;1)
В18	=НОРМРАСП(B11;B8;B9;1)
В19	=НОРМРАСП(B12;B8;B9;1)-НОРМРАСП(B8+B9;B8;B9;1)
В20	=НОРМРАСП(B8;B8;B9;1)-НОРМРАСП(B8-B9;B8;B9;1)
С17	=НОРМРАСП(0;C8;C9;1)
С18	=НОРМРАСП(C11;C8;C9;1)
С19	=НОРМРАСП(C12;C8;C9;1)-НОРМРАСП(C8+C9;C8;C9;1)
С20	=НОРМРАСП(C8;C8;C9;1)-НОРМРАСП(C8-C9;C8;C9;1)
D17	=НОРМРАСП(0;D8;D9;1)
D18	=НОРМРАСП(D11;D8;D9;1)
D19	=НОРМРАСП(D12;D8;D9;1)-НОРМРАСП(D8+D9;D8;D9;1)
D20	=НОРМРАСП(D8;D8;D9;1)-НОРМРАСП(D8-D9;D8;D9;1)
E17	=НОРМРАСП(0;E8;E9;1)
E18	=НОРМРАСП(E11;E8;E9;1)
E19	=НОРМРАСП(E12;E8;E9;1)-НОРМРАСП(E8+E9;E8;E9;1)
E20	=НОРМРАСП(E8;E8;E9;1)-НОРМРАСП(E8-E9;E8;E9;1)
F17	=НОРМРАСП(0;F8;F9;1)
F18	=НОРМРАСП(F11;F8;F9;1)
F19	=НОРМРАСП(F12;F8;F9;1)-НОРМРАСП(F8+F9;F8;F9;1)
F20	=НОРМРАСП(F8;F8;F9;1)-НОРМРАСП(F8-F9;F8;F9;1)

Используемые в нем собственные имена ячеек взяты из аналогичного листа предыдущего шаблона (см. таблицу 2.7).

Для быстрого формирования нового листа "Результаты анализа" выполните следующие действия:

1) загрузите предыдущий шаблон SIMUL_1.XLT и сохраните его под другим именем, например - SIMUL_2.XLT;

2) удалите лист "Имитация". Для этого установите указатель мыши на ярлычок этого листа и нажмите правую кнопку. Результатом выполнения этих действий будет появления списка операций в виде контекстного меню. Выберите операцию "Удалить". Подтвердите свое решение нажатием кнопки "ОК" в появившемся диалоговом окне;

3) перейдите в лист "Результаты анализа". Удалите строки 17-18. Откорректируйте заголовок электронной таблицы;

4) добавьте формулы из таблицы 2.9. Для этого введите соответствующие формулы в ячейки блока В17:В20 и скопируйте их в блок С17:F20. Введите соответствующие комментарии;

5) сверьте полученную таблицу с рисунком 2.6.

Перейдите к следующему листу и присвойте ему имя - "Имитация".

Рисунок 2.7 - Лист "Имитация" (шаблон II)

Первая часть этого листа (блок ячеек А1:Е10) предназначена для ввода исходных данных и расчета необходимых параметров их распределений. Напомним, что нормальное распределение случайной величины характери-зуется двумя параметрами - математическим ожиданием (средним) и стандарт-ным отклонением. Формулы расчета указанных параметров для ключевых переменных модели заданы в блоках ячеек В7:D7 и B8:D8 соответственно (см. таблицу 2.11). Для удобства определения формул и повышения их наглядности блоку ячеек Е3:Е5 присвоено имя "Вероятности" (см. таблицу 2.10).

Таблица 2.10 - Имена ячеек листа "Имитация" (шаблон II)

Адрес ячейки	Имя	Комментарии
Блок Е3:Е5	Вероятности	Вероятность значения параметра
Блок A13:A512	Перем_расх	Переменные расходы
Блок B13:B512	Количество	Объем выпуска
Блок C13:C512	Цена	Цена изделия
Блок D13:D512	Поступления	Поступления от проекта NCF
Блок E13:E512	ЧСС	Чистая современная стоимость NPV

Таблица 2.11 - Формулы листа "Имитация" (шаблон II)

Ячейка	Формула
В7	=СУММПРОИЗВ(B3:B5; Вероятности)
В8	{=КОРЕНЬ(СУММПРОИЗВ((B3:B5 - B7)^2; Вероятности))}
С7	=СУММПРОИЗВ(C3:C5; Вероятности)
С8	{=КОРЕНЬ(СУММПРОИЗВ((C3:C5 - C7)^2; Вероятности))}
D7	=СУММПРОИЗВ(D3:D5; Вероятности)
D8	{=КОРЕНЬ(СУММПРОИЗВ((D3:D5 - D7)^2; Вероятности))}
E10	=B10+13 –1
D13	=(B13(C13-A13)-Пост_расх-Аморт)(1-Налог)+Аморт
E13	=ПЗ(Норма; Срок; -D13) - Нач_инвест

Сформируйте элементы оформления листа "Имитация", определите необходимые имена для блоков ячеек (Таблица 2.10) и задайте требуемые формулы (Таблица 2.11). Сверьте полученную электронную таблицу с рисунком 2.7. Сохраните полученный шаблон под именем SIMUL_2.XLT.

Введите исходные значения постоянных переменных (Таблица 2.2) в ячейки В2:В4 и D2:D4 листа "Результаты анализа". Перейдите к листу "Имитация". Введите значения ключевых переменных и соответствующие вероятности (Таблица 2.8).

Полученная в результате электронная таблица должна иметь вид (Рисунок 2.8).

Рисунок 2.8 - Лист "Имитация" после ввода

исходных данных

Установите курсор в ячейку А13. Приступаем к проведению имитационного эксперимента.

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

2 Выберите из списка "Инструменты анализа" пункт "Генерация случайных чисел" и нажмите кнопку "ОК" (Рисунок 2.9).

3 На экране появится диалоговое окно "Генерация случайных чисел". Укажите в списке "Распределения" требуемый тип - "Нормальное". Заполните остальные поля изменившегося окна, согласно рисунку 2.10 и нажмите кнопку "ОК". Результатом будет заполнение блока ячеек А13:А512 (переменные расходы) сгенерированными случайными значениями.

Рисунок 2.9 - Выбор инструмента

"Генерация случайных чисел"

Рисунок 2.10 - Заполнение полей окна

"Генерация случайных чисел"

Первым заполняемым аргументом диалогового окна "Генерация случайных чисел" является поле "Число переменных". Оно задает количество колонок, в которых будут размещаться сгенерированные в соответствии с заданным законом распределения случайные величины. В нашем примере оно должно содержать 1, так как ранее мы отвели под значения переменной V (переменные расходы) одну колонку - "А". В случае, если указывается число больше 1, случайные величины будут размещены в соответствующем количестве соседних колонок, начиная с активной ячейки. Если это число не введено, то все колонки в выходном диапазоне будут заполнены.

Следующим обязательным аргументом для заполнения является содержимое поля "Число случайных чисел" (т.е. - количество имитаций). Согласно условиям примера оно должно быть равно.

Необходимый вид распределения задается путем соответствующего выбора из списка "Распределения". Выбранный тип распределения определяет внешний вид диалогового окна. В рассматриваемом примере выбор типа распределения "Нормальное" повлек за собой появление дополнительных аргументов - его параметров "Среднее" и "Стандартное отклонение", рассчитанных ранее для исследуемой переменной V в ячейках В7 и В8 листа "Имитация". Эти аргументы могут быть заданы только в виде констант. Использование адресов ячеек и собственных имен здесь не допускается!

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

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

· выходной блок ячеек на текущем листе - введите ссылку на левую верхнюю ячейку выходного диапазона, при этом его размер будет определен автоматически и в случае возможного наложения генерируемых значений на уже имеющиеся данные на экран будет выведено предупреждающее сообщение;

· новый рабочий лист - в рабочей книге будет открыт новый лист, содержащий результаты генерации случайных величин, начиная с ячейки A1;

· новая рабочая книга - будет открыта новая книга с результатами имитации на первом листе.

Для проведения дальнейшего анализа необходимо, чтобы случайные величины размещались в специально отведенные для них блоки ячеек. В частности для хранения 500 значений первой переменной ранее был отведен блок ячеек А13:А512. Поскольку для этого блока определено собственное имя - "Перем_расх", оно указано в качестве выходного диапазона.

Генерация значений остальных переменных Q и Р осуществляется аналогичным образом, путем выполнения шагов 1-3. Пример заполнения окна "Генерация случайных чисел" для переменной Q (количество) приведен на рисунке 2.11.

Рисунок 2.11 - Заполнение полей окна для переменной Q

Для получения генеральной совокупности значений потока платежей и их чистой современной стоимости необходимо скопировать формулы базовой строки (ячейки D13:E13) требуемое число раз (499).

Копирование осуществляется выполнением следующих действий.

1) выделите и скопируйте в буфер ячейку D13;

2) нажмите клавишу [F5]. На экране появится диалоговое окно "Переход";

3) укажите в поле "Ссылка" имя блока "Поступления" и нажмите кнопку "ОК". Результатом этих действий будет выделение заданного блока;

4) нажмите клавишу[ENTER];

5) в случае, если в ЭТ был установлен режим ручных вычислений, нажмите клавишу [F9].

Аналогичным образом копируется формула из ячейки Е13. При этом,
в поле "Ссылка" диалогового окна "Переход" необходимо указать имя
блока - "ЧСС". Вы также можете выбрать необходимое имя из списка "Перейти к".

Полученные результаты решения примера приведены на рисунках 2.12 - 2.13.

Рисунок 2.12 - Результаты имитационного

эксперимента (шаблон II)

Рисунок 2.13 - Результаты анализа (шаблон II)

Результаты проведенного имитационного эксперимента ненамного отличаются от предыдущих. Величина ожидаемой NPV равна 3412,14 при стандартном отклонении 2556,83. Коэффициент вариации (0,75) несколько выше, но меньше 1, таким образом, риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 9%. Общее число отрицательных значений NPV в выборке составляет 32 из 500. Таким образом, с вероятностью около 91% можно утверждать, что чистая современная стоимость проекта будет больше 0. При этом, вероятность того, что величина NPV окажется больше чем М(NPV) + σ, равна 16% (ячейка F19). Вероятность попадания значения NPV в интервал [М(NPV) - σ; М(NPV)] равна 34%.