Лабораторная работа № 6. Анализ чувствительности критериев эффективности

Анализ чувствительности критериев эффективности.

Электронная таблица Excel позволяет легко решать проблемы перебора вариантов типа «что будет, если». Для этого используются специальные средства – Таблица подстановки, Сценарии.

Пример 6.1. На основании исходных данных инвестиционного проекта проведите анализ чувствительности критерия NPV от влияния следующих параметров: «переменные затраты»; «объем выпуска»; «переменные затраты» и «объем выпуска» с использованием средства Таблица подстановки.

1. Подготовьте шаблон, согласно таблице 6.1. Присвойте имена ячейкам: B3 – Q, B4 – P, B5 – EV и т.д.

Таблица 6.1
	A	B	C	D
	Анализ чувствительности критериев эффективности
	Объем выпуска, Q	200,00	Начальные инвестиции, I0	2000,00
	Цена за штуку, P	50,00	Постоянные затраты, EC	500,00
	Переменные затраты, EV	30,00	Амортизация, A	100,00
	Норма дисконта, r	0,10	Остаточная стоимость, S	200,00
	Срок проекта, T	5,00	Налог на прибыль, TAX	0,60
	Чистые платежи, NCF =	1460,00		NPV =
			Значения варьируемого параметра (например, EV)	3658,73

В качестве постоянного чистого платежа, задайте в ячейке B9 таблицы формулу NCF = (Q * (P - EV) - EC - A) * (1- TAX) + A. Вычислите в ячейке D10 критерий чистой приведенной стоимости с использованием функции ПС():

NPV = ПC(ставка;кпер;-платежи) + S /(1 + r) ^T - I₀.

После ввода формулы ячейке D10 получится результат NPV = 3658,73.

2. Выберите в качестве параметра, влияние которого необходимо исследовать, показатель «переменные затраты». Заполните столбец (блок ячеек C11: C26) изменяемыми переменными затратами EV = 25, 26, … 40, исчерпав, тем самым, весь диапазон изменений показателя. Выполните следующую последовательность действий.

1. Выделите блок ячеек C10: D26.

2. Выберите из темы Данные главного меню пункт Таблица подстановки. На экране появится окно диалога (рис. 6.1).

Рис. 6.1.

3. Установите курсор в поле Подставлять значения по строкам в: и ввести имя ячейки, содержащий значения параметра «переменные затраты», на которую ссылается формула NPV в ячейке D10 (например, В5).

4. Закройте окно диалога, нажав кнопку ОК.

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

Таблица подстановки является средством автоматизации построения функции одной переменной. Для проведения анализа чувствительности критерия эффективности по другому показателю нужно заменить колонку С его значениями и повторить действия

2. Исследуйте влияние на NPV изменений показателя «объем выпуска» самостоятельно.

3. Проведите одновременный анализ зависимости NPV по двум показателям: от «переменных затрат» и «объема выпуска», формируя функцию двух переменных.

Введите значения «переменных затрат» в ячейках столбца D под ячейкой D10, содержащей формулу NPV, а значения “объем выпуска” в строку 10 справа от ячейки D10.

При вызове диалогового окна, в полях «Подставлять значения по строкам в:» укажите B5 (имя начальной ячейки, на которую ссылается формула ячейки D10) и «Подставлять значения по столбцам в:», укажите имя начальной ячейки – B3, на которую ссылается формула ячейки D10. Результатом работы средства Таблица подстановок будет матрица значений NPV.

4. Результаты применения средства Таблица подстановок отобразите графически с помощью Мастера диаграмм Excel (пиктограмма главного меню). На основании построенных диаграмм проанализируйете условия оптимальности исследуемого критерия эффективности инвестирования.

Пример 6.2. На основании исходных данных инвестиционного проекта (используйте шаблон таблицы 6.1) проведите анализ чувствительности критерия NPV с использованием сценариев от влияния параметров, возможные значения которые представлены в таблице 6.2.

Таблица 6.2
Сценарии	Вероятный - 0,5	Наихудший - 0,25	Наилучший - 0,25
Объем выпуска, Q	200,00	150,00	300,00
Цена за штуку, P	50,00	40,00	55,00
Переменные затраты, EV	30,00	35,00	25,00
Норма дисконта, r	0,10	0,15	0,08
Срок проекта, T	5,00	7,00	5,00

Первый сценарий.

1. Выделите блок ячеек, которые будут использоваться в качестве изменяемых (в данном примере блок B3: B7).

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

3. Введите имя сценария, например Вероятный – 0,5 (см. рис. 6.2). В поле Изменяемые ячейки автоматически будет поставлен выделенный на первом шаге блок. В противном случае в это поле необходимо ввести координаты входного блока – B3: B7. Поле Примечание заполняется по усмотрению пользователя.

4. Нажмите кнопку «ОК». На экране появится диалоговое окно Значения ячеек сценария (см. рис. 6.3), содержащее данные выделенного ранее блока B3: B7. Нажмите кнопку «ОК». Снова появится окно Диспетчер сценариев.

Рис. 6.2.

Рис. 6.3.

Полученные результаты могут быть использованы для проведения дальнейшего анализа – оценки вероятностного распределения значений критерия NPV. В окне Диспетчер сценариев выберите кнопку Отчет, Структура.

В таблице 6.3 показана преобразованная структура: удалены лишние строки и столбцы, изменено имя Структура сценария на Анализ рисков, в блок ячеек B4: D4 введены соответствующие значения априорных вероятностей.

Таблица 6.3
	A	B	C	D
	Анализ рисков
	Сценарии	Вероятный	Наихудший	Наилучший
	Вероятности	0,5	0,25	0,25
	Объем выпуска, Q	200,00	150,00	300,00
	Цена за штуку, P	50,00	40,00	55,00
	Переменные затраты, EV	30,00	35,00	25,00
	Норма дисконта, r	0,10	0,15	0,08
	Срок проекта, T	5,00	7,00	5,00
	NPV	3 658,73	-1 259,15	11 950,89
	Средняя NPV, M (NPV)	4502,30
	Квадраты разностей	711611,20	33194293,33	20270736,42
	Стандартное отклонение, сигма	4673,62
	Коэффициент вариации, CV	1,04
	Pr (NPV £ 0)	0,17
	Pr (NPV £ 0 ,5 Среднее)	0,32
	Pr (NPV > максимума)	0,06

В таблице 6.3 ячейке B13 присвоено имя «Среднее» и проведены вычисления:

- среднее значение NPV, M (NPV) =СУММПРОИЗВ(B4: D4; B11: D11) Þ 4502,30;

- квадраты разностей, =(NPV -Среднее)^ 2;

- стандартное отклонение, s = КОРЕНЬ(СУММПРОИЗВ(B4: D4; B14: D14);

- коэффициент вариации, CV = s /Среднее.

На основании данных вычислений определены вероятности того, что (см. табл.):

- NPV будет иметь нулевое или отрицательное значение, т.е.

Pr (NPV £ 0) = НОРМРАСПР(0;Среднее;Отклонение; 1) Þ 0,17;

- NPV будет меньше ожидаемой (средней) на 50%, т.е.

Pr (NPV £ 0,5 Среднее) =НОРМРАСПР(0,5 *Среднее;Среднее;Отклонение; 1) Þ 0,32;

- NPV будет больше значения наиболее благоприятного исхода, т.е.

Pr (NPV > максимума) = 1 – НОРМРАСП(D11; Среднее; Отклонение; 1) Þ 0,06.

Здесь использовалась статистическая функция, возвращающая значение нормальной функции распределения и имеющая формат

НОРМРАСП (x;среднее;стандартное_отклонение;интегральный), где

x – исследуемое значение случайной величины;

среднее – среднее значение случайной величины;

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

интегральный – 0 или 1.

В зависимости от заданного параметра «интегральный» – 0 (ложь) или 1 (истина) – она возвращает плотность распределения вероятности w (x) нормальной случайной величины X или значение кумулятивной функции распределения вероятности Pr (X £ x) = .

Полученные результаты свидетельствуют о наличии риска для данного проекта.

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