Лабораторная работа № 8. Анализ рисков инвестиционного проекта с использованием инструмента Анализ данных

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

Статистический анализ результатов имитационного моделирования инвестиционных рисков.

Корреляционный анализ данных.

Задача 8.1. Необходимо определить степень тесноты взаимосвязей между полученными в таблице 7.3 имитационными данными (блок ячеек А13: E512): переменные затраты - EV, объем произведенной и реализованной продукции - Q, поступления (чистые платежи) - NCF, чистая приведенная стоимость - NPV.

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

Cor (X, Y) = Cov (X, Y)/s_xs_y,

определяемый через стандартные отклонения данных и их коэффициент ковариации

Cov (X, Y) = M {[ X - M (X)][ Y - M (Y)]}.

Решение.

1. Откройте сохраненную ранее под именем Имитация книгу Excel (таблица 7.3 ) на листе Имитация.

Внимание! Если строка A1 таблицы имела объединенные ячейки (например, с названием Исходные условия эксперимента), обязательно удалите их и перепишите название в одну ячейку!

2. Выберите в главном меню тему Сервис, пункт Анализ данных.

3. В появившемся диалоговом окне Анализ данных выберите из списка «Инструменты анализа» пункт Корреляция.

4. В появившемся диалоговом окне Корреляция заполните поля: Входной интервал – А13: E512; Группирование – по столбцам; Метки в первой строке – флажок 4; Параметры вывода – Новый рабочий лист - Корреляция.

5. Удалите в книге лист Результаты анализа и сохраните ее под новым именем Статистика.

Вид полученных на новом рабочем листе Корреляция результатов после элементарного форматирования показан в таблице 8.1.

Таблица 8.1
	A	B	C	D	E	F
		Переменные, EV	Объем, Q	Цена, P	Поступления, NCF	Чист. пр. ст., NPV
	Переменные, EV
	Объем, Q	-0,08
	Цена, P	0,04	-0,02
	Поступления, NCF	-0,42	0,63	0,62
	Чист. пр. ст., NPV	-0,42	0,63	0,62	1
	Корреляция	Имитация	Лист 3	3 4

Как следует из результатов корреляционного анализа, переменные EV, Q и P являются независимыми, т.к. их коэффициенты корреляции близки к 0. NPV напрямую зависят от NCF. Между NCF, NPV и Q, P существует корреляционная зависимость средней степени. Умеренная обратная (отрицательная) корреляционная зависимость существует между NCF, NPV и EV.

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

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

Задача 8.2. Необходимо определить статистические характеристики полученных в таблице 7.3 имитационных данных (блок ячеек А13: E512): переменных затрат - EV, объема произведенной и реализованной продукции - Q, поступлений (чистые платежи) - NCF, чистой приведенной стоимости - NPV.

Решение.

1. Откройте сохраненную ранее под именем Статистика книгу Excel на листе Имитация и выберите в главном меню тему Сервис, пункт Анализ данных. В появившемся диалоговом окне Анализ данных выберите из списка «Инструменты анализа» пункт Описательная статистика.

2. В появившемся диалоговом окне Описательная статистика укажите в соответствующих аргументах: Входной интервал – A12: E512; Группирование – по столбцам; Метки в первой строке – 4; Параметры вывода – новый рабочий лист; Итоговая статистика – 4; Уровень надежности – 95 %.

После нажатия кнопки [ОК] новый лист книги Статистика будетзаполнен вычисленными характеристиками имитационных данных. Эти характеристики (после несложного форматирования и присвоения листу имени Статистика) показаны в таблице 8.2.

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

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

Мода – наиболее вероятное значение случайной величины. Если мода отсутствует, то Excel возвращает сообщение об ошибке (#Н/Д).

Эксцесс (e) – характеристика остроконечности (при положительном значении) или пологости (при отрицательном значении) распределения по сравнению с нормальной кривой.

Асимметричность (коэффициент асимметрии или скоса – s) – характеристика смещения распределения относительно математического ожидания (вправо – при положительном значении).

Для оценок значимостей коэффициента асимметрии и величины эксцесса рассчитывают их стандартные ошибки

s_s = [ 6 (n - 1)/(n + 1)(n + 3)]^1/2,

s_e = [ 24n (n - 2)(n - 3)/(n - 1)²(n + 3)(n + 5)]^1/2,

где n – число значений случайной величины.

Таблица 8.2
	A	B	C	D	E	F
		Переменные, EV	Объем, Q	Цена, P	Поступления, NCF	Чист. пр. ст., NPV
	Среднее	30,148	210,036	49,012	1447,703	3487,934
	Стандартная ошибка	0,149	2,596	0,247	31,949	121,112
	Медиана	30,189	206,768	49,232	1380,257	3232,260
	Мода	34,343	214,232	45,736	#Н/Д	#Н/Д
	Стандартное отклонение	3,336	58,050	5,530	714,401	2708,142
	Дисперсия выборки	11,128	3369,782	30,579	510368,855	7334033,276
	Эксцесс	0,311	-0,475	-0,201	0,735	0,735
	Асимметрич-ность	0,090	0,095	0,033	0,718	0,718
	Интервал	22,703	307,002	30,728	4317,748	16367,662
	Минимум	19,767	50,734	33,977	62,605	-1762,679
	Максимум	42,470	357,736	64,704	4380,353	14604,983
	Сумма	15074,232	105017,822	24506,050	723851,582	1743967,001
	Счет	500,000	500,000	500,000	500,000	500,000
	Уровень надежности (95,0 %)	0,293	5,101	0,486	62,771	237,952
	Статистика	Корр-ция	Им-ция	3 4

Если отношение соответствующих величин к их стандартным ошибкам меньше трех (s / s_s < 3 и e / s_e < 3), то они считаются несущественными, а их наличие объясняется воздействием случайных факторов. В противном случае как асимметрия, так и эксцесс статистически значимы.

Например, в рассматриваемом случае (n = 500) для NPV получим

s / s_s = 0,718/0,108 @ 6,648 > 3,

что свидетельствует о статистической значимости асимметрии или правосторонней скошенности распределения NPV.

Интервал – разность между максимальным и минимальным значениями случайной величины.

Счет – число значений в заданном интервале.

Сумма – сумма случайных величин в заданном интервале.

Уровень надежности – величина доверительного интервала для математического ожидания, соответствующая заданному уровню надежности (95 %). Так, например, с вероятностью 0,95 величина математического ожидания NPV попадает в интервал 3487,934 ± 237,952.

Следует заметить, что коэффициент асимметрии, величину эксцесса, доверительный интервал возможно вычислить с помощью функций СКОС(), ЭКСЦЕСС() и ДОВЕРИТ() мастера функций.

Для наглядности построим гистограмму распределения случайных величин NPV. Предварительно заметим, что интервал 500 значений NPV в диапазоне

(минимум = - 1762,679 ¸ максимум = 14604,983)

составляет величину 16367,662. Следовательно шаг составляет 16367,662/500 @ 33. Для построения боле гладкой гистограммы необходимо выбрать ее шаг @ 330 и выполнить следующие действия.

1. На листе Имитация книги Статистика введите в ячейку G13 округленное минимальное значение NPV @ - 1763.

2. С помощью инструмента Прогрессия (пункта Заполнить темы Правка главного меню) постройте арифметическую прогрессию (расположение - по столбцам) с шагом 330 и предельным значением 14605. В результате будет заполнен блок ячеек G13: G62.

3. Выберите в главном меню тему Сервис, пункт Анализ данных. В появившемся диалоговом окне Анализ данных выберите из списка «Инструменты анализа» пункт Гистограмма. В качестве входного интервала укажите блок ячеек E13: E512, а в качестве выходного интервала – блок ячеек G13: G62. В параметрах вывода укажите новый рабочий лист и вывод графика.

Обратите внимание, что полученная гистограмма наглядно свидетельствует о скошенности распределения NPV вправо относительно своего среднего значения, равного 3487,934.

Литература

1. Краснов А.Е., Сагинов Ю.Л., Кузнецова Ю.Г., Феоктистова Н.А.

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

– М.: МГУТУ им. К.Г. Разумовского, 2012. – 76 с.

2. Краснов А.Е., Сагинов Ю.Л., Кузнецова Ю.Г., Феоктистова Н.А.

Основы информационных технологий управления финансами, производством и бизнесом. Учебно-практическое пособие для аспирантов/ соискателей, бакалавров и магистров, обучающихся по техническим, технологическим и экономическим направлениям подготовки. – М.: МГУТУ им. К.Г. Разумовского, 2012. – 48 с.

Телефоны кафедры Информационных технологий

МГУТУ им. К.Г. Разумовского

(факс) 8(495) 670-66-00; 8(495) 678-25-34;

Email – [email protected]

Сайт кафедры – kafedrait.com

______________________________________________________

Краснов Андрей Евгеньевич, Сагинов Юрий Леонидович,

Феоктистова Наталия Андреевна

Информационные технологии управления

финансами, производством и бизнесом

Лабораторный практикум

Тираж: ___экз., заказ № ____