Лабораторная работа №3 «Имитационное моделирование денежных потоков инвестиционного проекта в среде excel»

Цель работы: изучение встроенных функций табличного процессора EXCEL и надстройки «Генератор случайных чисел», используемых при имитационном моделировании и приобретение навыков проведения имитационных экспериментов в среде EXCEL.

1. Теоретическая часть

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

2. Возможности ms excel для построения имитационных моделей

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

3. Ход работы

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

Задание: финансово-аналитическая служба инвестиционного фонда «UkraineInvest» оценивает целесообразность вложения средств в инвестиционный проект с помощью моделирования финансовых потоков. Необходимо оценить денежные поступления в результате реализации проекта и чистую приведенную стоимость проекта при различных вариантах величины переменных расходов на выпуск продукции, объема производимой продукции, цены реализации, если распределение имеет вид:

1. равномерное;

2. нормальное.

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

Исходные данные:

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

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

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

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

Адрес ячейки	Имя	Значение	Комментарии
B2	Нач_инвест.	2000	Начальные инвестиции
B3	Пост_расх.	500	Постоянные расходы
В4	Аморт	100	Амортизация
D2	Норма	0,1	Норма дисконта
D3	Налог	0,6	Ставка налога на прибыль
D4	Срок	5	Срок реализации проекта

Таблица 18 – Вариация значений исходных листа «Имитация»

Показатель	Минимум	Максимум
Перемен. расходы	25	35
Количество	150	300
Цена	40	55

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

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

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

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

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

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

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

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

Эта функция не имеет аргументов. Если в ЭТ установлен режим автоматических вычислений, принятый по умолчанию, то возвращаемый функцией результат будет изменяться всякий раз, когда происходит ввод или корректировка данных. В режиме ручных вычислений пересчет всей ЭТ осуществляется только после нажатия клавиши [F9].

Настройка режима управления вычислениями производится установкой соответствующего флажка в подпункте "Вычисления" пункта "Параметры" темы "Сервис" главного меню.

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

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

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

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

Введите в любую ячейку ЭТ формулу:

=СЛУЧМЕЖДУ(150; 300)

(Результат: 210) .

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

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

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

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

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

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

Первый лист – "Имитация", предназначен для построения генеральной совокупности (рис. 16). Определенные в данном листе формулы и собственные имена ячеек приведены в табл. 18 и 19.

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

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

Ячейка	Формула
Е7	=B7+10-2

Для каждой переменной (Q – количество производимой продукции, V – величина переменных расходов, P – цена реализации продукции) необходимо задать с помощью функции СЛУЧМЕЖДУ(перем_1; перем_2) их моделируемые значения.

Величина денежных поступлений в результате реализации проекта вычисляется по формуле:

NCFt = (Q(P-V) – Пост_расх – Аморт)(1-Налог) + Аморт.

Величина чистой приведенной стоимости проекта вычисляется с помощью встроенной функции ПС по формуле:

ПС(Норма; Срок; - NCFt) – Нач_инвест.

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

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

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

Для вычисления параметров распределения переменных величин используются следующие функции:

СРЗНАЧ (число1; число2;…) - возвращает среднее арифметическое для генеральной совокупности.

СТАНДОТКЛОНП (число1; число2;…) - вычисляет стандартное отклонение по генеральной совокупности. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.

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

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

Ячейка	Формула
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)

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

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

=СЧЕТЕСЛИ(блок; "условие").

В данном случае, заданная в ячейке F13, эта функция осуществляет подсчет количества отрицательных значений NPV, содержащихся в блоке ячеек ЧСС.

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

=СУММЕСЛИ(блок; "условие").

В данном случае, заданные в ячейках F14.F15, функции осуществляет подсчет суммы отрицательных (ячейка F14) и положительных (ячейка F14) значений NPV, содержащихся в блоке ЧСС. Смысл этих расчетов будет объяснен позже.

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

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

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

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

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

, (1)

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

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

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

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

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

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

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

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

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

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

Нетрудно заметить, что эти функции следует использовать в тандеме. При этом наиболее эффективным и компактным способом их задания является указание функции НОРМАЛИЗАЦИЯ() в качестве аргумента функции – НОРМСТРАСП(), т.е.:

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

С целью повышения наглядности, в проектируемом шаблоне функции заданы раздельно (ячейки Е18 и F18).

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

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

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

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

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

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

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

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

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

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

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

Для перехода к нужному участку электронной таблицы достаточно указать в поле "Ссылка" адрес или имя соответствующей ячейки (блока). В данном случае, таким адресом будет любая ячейка последней вставляемой строки, номер которой вычислен в ячейке Е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. Фрагмент результатов имитации, полученных автором, приведен на рис. 20 . Соответствующие проведенному эксперименту результаты анализа приведены на рис. 21.

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

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

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

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

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

На рис. 6 приведен график распределения значений ключевых параметров V, P и Q, построенный на основании 75 имитаций.

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

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

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

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

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

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