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

     Обратим внимание на то, что для расчета стандартных отклонений используются формулы-массивы. Для формирования блока формул достаточно определить их для ячеек В7:В8 и затем скопировать в блок С7:D8.

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

     Ячейки D13:E13 содержат уже знакомые нам формулы для расчета величины потока платежей NCF и его чистой современной стоимости NPV.

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

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

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

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

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

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

Рисунок 3.8 - Выбор инструмента "Генерация случайных чисел"

Рисунок 3.9 – Заполнение полей окна "Генерация случайных чисел"

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

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

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

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

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

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

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

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

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

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

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

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

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

1.      Выделим и скопируем в буфер ячейку D13.

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

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

4.      Нажмём клавишу [ENTER].

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

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

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

     Результаты проведенного имитационного эксперимента отличаются от предыдущих. Величина ожидаемой NPV равна 260283,808  при стандартном отклонении 37760,836.  Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 27%. Общее число отрицательных значений NPV в выборке составляет 137 из 500. Таким образом с вероятностью около 75% можно утверждать, что чистая современная стоимость проекта будет больше 0. При этом вероятность того, что величина NPV окажется больше чем М(NPV) +   , равна 15,86% (ячейка F19). Вероятность попадания значения NPV в интервал [М(NPV) -  ; М(NPV)] равна 34%.

     Таким образом, проведя анализ обоими способами, можно сказать, что при использовании Генератора случайных чисел был получен более пессимистичный результат. Но в целом можно сказать, что риск по данному проекту составляет около 25%, что является достаточно существенным для инвестора.

Заключение

     В данной курсовой работе была рассмотрена тема «Имитационное моделирование рисков инвестиционных проектов с применением функций EXCEL».

     В соответствии с выбранной темой в работе представлено три раздела.

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

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

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

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

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

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

     Также подробно был описана методика проведения анализа с помощью встроенных функций Excel. При это были использованы такие функции, как МАКС(), МИН(), НОРМСТРАСП(), НОРМАЛИЗАЦИЯ(), СЧЁТЕСЛИ(), СТАНДОТКЛОНП() и другие.

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

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

     Также был непосредственно проведён имитационный анализ рисков инвестиционного проекта.

      При первом способе были получены следующие данные: по результатам имитационного анализа риск проекта получился достаточно низким. Величина стандартного отклонения не превышает значения NPV. Коэффициент вариации (0,76) меньше 1, таким образом, риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 10%. Еще больший оптимизм внушают результаты анализа распределения чистых поступлений от проекта NCF. Величина стандартного отклонения здесь составляет всего 59% от среднего значения. Таким образом, с вероятностью 90% можно утверждать, что поступления от проекта будут положительными величинами.

      При использовании Генератора случайных чисел  результаты получились несколько иные: Величина ожидаемой NPV равна 260283,808  при стандартном отклонении 37760,836.  Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 27%. Общее число отрицательных значений NPV в выборке составляет 137 из 500. Таким образом с вероятностью около 75% можно утверждать, что чистая современная стоимость проекта будет больше 0. При этом вероятность того, что величина NPV окажется больше чем М(NPV) +   , равна 15,86% (ячейка F19). Вероятность попадания значения NPV в интервал [М(NPV) -  ; М(NPV)] равна 34%.

      Рассмотрев эти два метода, можно сделать выводы, что второй является более точным, так как учитывает вероятности наступления событий. Поэтому делаем вывод о том, что риск по данному проекту составляет 25%.

     Таким образом, в данной курсовой работе для достижения поставленной цели были решены следующие задачи:

1) дано определение понятию электронных таблиц и моделирования рисков инвестиционных проектов;

2)дана краткая характеристика инвестиционному проекту;

3) проведён имитационный анализ рисков конкретного инвестиционного проекта на примере  производства фоторамок в среде Excel.

Список использованных источников

1.                  1. В.К. Душин Теоретические основы информационных процессов и систем Москва, 2008г., Изд: "Дашков и К", 347 стр

2. Лукасевич И.Я. - Анализ финансовых операций. Методы, модели, техника вычислений. Издательство: М.: Финансы, ЮНИТИ, 2007 г, 402 с.