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

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«МАГНИТОГОРСКИЙ ГОСУДАРСТВЕННЫЙ

ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ им. Г.И. НОСОВА»

Кафедра финансов и бухгалтерского учёта

КУРСОВАЯ РАБОТА

по дисциплине: «Информационные системы в экономике»

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

Исполнитель: Ольхина Екатерина Павловна, студентка 4 курса,

группа ФФК-07

Руководитель: Данилов Г.В, к.э.н., доцент кафедры Финансов и бухгалтерского учёта

Работа допущена к защите «___» _________ 20   г.

Работа защищена «___» __________ 20   г.  с оценкой

Магнитогорск 2012

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«МАГНИТОГОРСКИЙ ГОСУДАРСТВЕННЫЙ

ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ им. Г.И. НОСОВА»

Кафедра финансов и бухгалтерского учёта

ЗАДАНИЕ НА КУРСОВОЙ ПРОЕКТ (РАБОТУ)

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

Студенту Ольхиной Екатерине Павловне

     Вопросы, подлежащие раскрытию в работе:

      - понятие электронных таблиц Excel;

      - понятие моделирования рисков инвестиционных проектов;

      - краткая характеристика инвестиционного проекта по производству фоторамок;

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

Срок сдачи «___» __________ 2011  г.

Руководитель: ________________________/_________________________

                                       (подпись)                       (расшифровка подписи)

Задание получил: ________________________/ __________________________

                                           (подпись)                    (расшифровка подписи)

Магнитогорск 2012

Содержание

                                                                                                                                                                     Стр.

Введение                                                                                                                  4                        

1. ПОНЯТИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ И МОДЕЛИРОВАНИЕ

РИСКОВ ИНВЕСТИЦИОННЫХ ПРОЕКТОВ       

1.1 Понятие электронных таблиц                                                                          5

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

2. КРАТКАЯ ХАРАКТЕРИСТИКА ИНВЕСТИЦИОННОГО ПРОЕКТА

ПО ПРОИЗВОДСТВУ ФОТОРАМОК                                                                14

3. ИМИТАЦИОННЫЙ АНАЛИЗ РИСКОВ ИНВЕСТЦИОННОГО ПРОЕКТА ПО ПРОИЗВОДСТВУ ФОТОРАМОК                                                               

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

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

Заключение                                                                                                             42                                           

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

Введение

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

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

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

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

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

     Для этого необходимо решить следующие задачи:

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

2)рассмотреть краткую характеристику инвестиционного проекта по производству фоторамок;

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

     В качестве информационной базы предполагается использование следующих источников: И.Я. Лукасевич "Анализ финансовых операций", Д.Жаров «Финансовое моделирование в Excel» и другие.

     1. ПОНЯТИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ И МОДЕЛИРОВАНИЕ РИСКОВ ИНВЕСТИЦИОННЫХ ПРОЕКТОВ       

     1.1 Понятие электронных таблиц

     Одной из самых продуктивных идей в области компьютерных информационных технологий стала идея электронной таблицы. Многие фирмы разработчики программного обеспечения для ПК создали свои версии табличных процессоров - прикладных программ, предназначенных для работы с электронными таблицами. Из них наибольшую известность приобрели Excel фирмы Microsoft, Lotus 1-2-3 фирмы Lotus Development, Supercalc фирмы Computer Associates. [1, с.206]

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

     Рабочим полем табличного процессора является экран дисплея, на котором электронная таблица представляется в виде матрицы. ЭТ, подобно шахматной доске, разделена на клетки, которые принято называть ячейками таблицы. Строки и столбцы таблицы имеют обозначения. Чаще всего строки имеют числовую нумерацию, а столбцы - буквенные (буквы латинского алфавита) обозначения. Как и на шахматной доске, каждая клетка имеет свое имя (адрес), состоящее из имени столбца и номера строки, например: А1, С13, F24 и т. п.

     На рисунке 1.1 представлен вид окна электронной таблицы Excel.

Рисунок 1.1 – Вид окна электронной таблицы Excel

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

     Программа Excel входит в офисный пакет программ Microsoft Office и предназначена для подготовки и обработки электронных таблиц под управлением операционной оболочки Windows.

     Рассмотрим коротко основные версии Excel для Windows.

 1)Excel 2 

      Исходная версия Excel для Windows — Excel 2 — появилась в конце 1987 года. Эта  версия программы носила название Excel 2, поскольку первая версия была разработана для Macintosh. В то время Windows еще не была широко распространена. Поэтому к Excel  прилагалась оперативная версия Windows — операционная система, обладавшая функциями,  достаточными для работы в Excel. По сегодняшним стандартам эта версия Excel кажется  недоработанной. 

2)Excel 3 

     В 1990 году компания Microsoft выпустила Excel 3 для Windows. Эта версия обладала  более совершенными инструментами и внешним видом. В Excel 3 появились панели  инструментов, средства рисования, режим структуры рабочей книги, надстройки, трехмерные  диаграммы, функция совместного редактирования документов и многое другое. 

3)Excel 4

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

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

4)Excel 5 

     В начале 1994 года на рынке появилась Excel 5. В этой версии было огромное количество новых средств, включая многолистные книги и новый макроязык Visual Basic for Application (VBA). Как и предшествующая версия, Excel 5 получала наилучшие отзывы во всех отраслевых изданиях. 

5) Excel 95 

    Excel 95 (также известная как Excel 7) выпущена летом 1995 года. Внешне эта версия  напоминала предыдущую (в Excel 95 появилось лишь несколько новых средств). Однако появление этой версии все же имело большое значение, поскольку в Excel 95 впервые был использован более  современный 32-битовый код. В Excel 95 и Excel 5 используется один и тот же формат файлов.

6) Excel 97 

     Excel 97 (также известная как Excel 8) значительно усовершенствована по сравнению с предыдущими версиями. Изменился внешний вид панелей инструментов и меню, справочная система теперь организована на качественно новом уровне, количество строк рабочей книги было увеличено в четыре раза. Если вы занимаетесь программированием на макроуровне, то, вероятно, заметили, что среда программирования Excel (VBA) значительно  усовершенствована. В Excel 97 появился новый формат файлов, а так же увеличен рабочий лист до 65536 строк и 256 столбцов.
7)Excel 2000 

     Excel 2000 (также известная как Excel 9) появилась в июне 1999 года. Эта версия  характеризовалась незначительным расширением возможностей. Немаловажным преимуществом новой версии стала возможность использования HTML в качестве  универсального формата файлов. В Excel 2000 конечно же поддерживался и стандартный двоичный формат файлов, совместимый с Excel 97. 

8) Excel 2002

 — это на самом деле Excel 10. В действительности,  Excel 2002 — восьмая версия Excel для Windows.

     Эту версию программы Excel 2002 выпустили в июне 2001 года.

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

     Многие из этих версий Excel имели несколько выпусков. Например, компания Microsoft создала два сервисных пакета для Excel 97 (SR-1 и SR-2). Эти выпуски помогли решить многие проблемы, возникшие при эксплуатации рассматриваемого приложения. 

9)Excel 2003

     11-ая версия.

     Самая популярная версия программы. Наилучшие сочетания функционала и интерфейса. Неудивительно, что многие используют её до сих пор.
10) Excel 2007

      Версия 12.

     Эта версия вышла в продажу в июле 2006-го года. Релиз отличался от уже привычного нам интерфейса Excel радикально. Появилась лента (Ribbon) и панель быстрого доступа. Кроме того функционал Excel расширился на несколько новых формул, таких как СУММЕСЛИМН().
     Революционным так же явилось решение разработчиков увеличить рабочий лист до 1 048 576 строк и 16 384 столбцов, а так же применение новых (четырёхбуквенных) обозначений расширения файлов.

11) Excel 2010

     Руководители MS решили не присваивать 13-й номер очередной версии, поэтому номер этой версии14-й.

     В октябре 2009-го года началось бесплатное распространение бета версий очередного релиза. Из интересных нововведений это Sparkliness (микрографики в ячейке), Slides (срезы сводной таблицы) и надстройка PoverPivot, для работы с 100 000 000-и строк

     Программа Excel относится к основным офисным компьютерным технологиям обработки числовых данных.

     Документом Excel является файл с произвольным именем и расширением XLSX. Такой файл *.xlsx называется рабочей книгой (Work Book). В каждом файле *.xlsx может размещаться от 1 до 255 электронных таблиц, каждая из которых называется рабочим листом (Sheet).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

     Предположим, что используемым критерием является чистая современная стоимость проекта NPV, рассчитываемая по формуле 1.1. [4, с.209]

 (1.1)

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

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

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

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

     2. КРАТКАЯ ХАРАКТЕРИСТИКА ИНВЕСТИЦИОННОГО ПРОЕКТА ПО ПРОИЗВОДСТВУ ФОТОРАМОК                                                                                             

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

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

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

     3. ИМИТАЦИОННЫЙ АНАЛИЗ РИСКОВ ИНВЕСТЦИОННОГО ПРОЕКТА ПО  ПРОИЗВОДСТВУ ФОТОРАМОК                                                                                                                                                                                     

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

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

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

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