В Автоматизация анализа чувствительности

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

с одним входом — для анализа влияния одного показателя; •

с двумя входами — для анализа влияния двух показателей одновременно.

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

Прежде всего спроектируем шаблон для ввода исходных данных. Предлагаемый вариант такого шаблона приведен на рис. 5.1. При этом были определены следующие имена и формулы (табл. 5.3 и табл. 5.4).

Таблица 5.3. Имена ячеек шаблона Адрес ячейки Имя Комментарии В2 Количество Объем выпуска продукции (штук) ВЗ Цена Цена за единицу В4 Перем_расх Переменные затраты на единицу продукции В5 Норма Норма дисконта В6 Срок Продолжительность проекта (лет) В9 Платежи Величина чистых поступлений D2 Нач инвест Начальные инвестиции D3 Посгг_расх Норма дисконта D4 Аморт Амортизационные отчисления D5 Ост стоим Стоимость актива на конец операции D6 Налог Ставка налога Т~

Анализ чувствительности NPV 0,00 G J0 ООО 0,00 0,00

0,00 0,00 0,00 0.J0 0,0"

Количество (с; Цена (Р)

Переменные расходы (V) Норма дмсконта г Срок реализации (л)

Начальные инвестиции (/) Постоянные расходы (R Амортизация (AJ Остаточная стоимость (SJ Hw-ior (Г) Значения NPV

0,00

Чистые платежи (NCFt) 1L

0,00

Значения варьируемого

параметра 11

12

13

U 15 йП

*4Ян?Г1 f (1иСт2/"ЛистЗі 4 Л.

Таблица 5.4. Формулы шаблона Ячейка Формула В9 =(Количество*(Цена—Перем_расх) — Пост_расх - Аморт)*(1-Налог) +Аморт D10 =ПЗ(Норма;Срок;-Платежи) + ПЗ(Норма;Срок; 0; - Ост стоим) -Нач инвест

Как следует из табл. 5.4, для решения задачи используются всего две формулы. Первая задана в ячейке В9. Она служит для вычисления чистого платежа NCF, и реализует соотношение:

NCF = [Q(P -V) - F- А]( 1 - Т) + А . (5.5)

Нетрудно заметить, что выражение (5.5) является числителем первого слагаемого из соотношения (5.4). Поскольку в данном случае поток платежей представляет собой аннуитет (согласно методике проведения анализа исходные показатели считаются одинаковыми для всех периодов), формула для вычисления критерия NPV задана в ячейке D10 с использованием функции П3{). Напомним, что эта функция вычисляет современную величину аннуитета — PV.

Завершите формирование шаблона и сохраните его на магнитном диске под именем RISKTAB. XLT. Приступаем к анализу.

Заполните шаблон наиболее вероятными значениями исходных показателей (т.е. данными, приведенными в последней графе табл. 5.2). После ввода данных ячейки В9 и D10 будут содержать расчетные значения периодического платежа и ожидаемой NPV (рис. 5.2). I

і

5

J J3 L Анализ чувствительности NPV Количество (Q) 700,НО Начальные инвестиции (Г) 2000,0 3 Цена (Р) 50.00 Постоянные расходы (F] 500,00 4 Переменные расчоды (V) 3Q,dO Амортизация (А). 100,00 5 Н^рмя дисконта г 0.10 Остаточная стоимость (S) 2П0.0Р 6 7 Срок реализации (л) 5,Ь0 Налог (Г) 0,60

в Значения NPV

9

1460,00

Чистые платежи (NCFt) 111

3658,73

a

Значения варьируемого параг,' тоа 12 В

w| Ц ?] f інсті I Писг? (Д адгЗ Чисх4 Чисг5 \ IrcrS |J

Рис. 5.2. Шаблон после ввода исходных данных

Теперь необходимо выбрать параметр, влияние которого будет подвергнуто анализу.

Выделить блок ячеек СЮ .D15. 2.

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

Установить курсор в поле Ячейка ввода столбца28 и ввести имя ячейки, содержащей входной параметр (ячейка ВЗ)29.

1

4 Закрыть окно диалога, нажав кнопку [ОК].

Полученная в результате выполнения указанных действий таблица приведена на рис. 5.4. ? аЬшща подстановки FP Подставлять значения по стдлбцаг* % 1 1 Подстав nfp-к знл'" инггстцо «Г" J ж ...

Рис. 5.3. Окно диалога

В 1

Анализ чувствительности NPV 2ullfl,QP

Ю0.00 100,0Ь 200,00 0,60

Кол^нпство (Q) '90.00

(Р 50,п I

Переменные расходы (V) 30J ?! Норме дисконта г 0,10 Срок реализации (її) 5Д

б 7 В

На-, пьнье инвестиции [/) Постоянные расходы jF) Амортизация (А) 0~таточняя ^тоимг.сть (S) Налог (Т) Значения WPV

9

1460,00;

Чистые платежи (АШ) 10

3658,73 2142,4І 626,10 ?Я90.21 -2406,53 -3922,Й4

л

,13

і»

ЩШІ

Значения варьируемого параме tpa 45 40 35 30 25 Листі

- • I

±1ҐІ Рис. 5.4. Анализ чувствительности NPVк изменению цены

Приведем необходимые пояснения к п. 1—4. Результирующая таблица (рис. 5.4) построена таким образом, что введенные в блок С11.С15 значения цены автоматически подставляются в ячейку ВЗ, которая служит входным параметром. Вычисляемые по формуле ячейки D10 значения NPV заполняют следующие за ней пусгые ячейки колонки D (в нашем примере — блок D12 .D15) в соответствии с данными блока значений входного параметров (блок СИ. С15).

Обратите внимание на следующее: •

ячейки, содержащие варьируемые значения и результаты вычислений, должны занимать соседние колонки или строки; •

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

Для проведения анализа по другому показателю необходимо ввести диапазон требуемых значений в ячейки колонки С (начиная с СИ), выделить соответствующий блок ячеек колонок С и D (начиная с СЮ) и указать в окне диалога адрес или имя ячейки — входного параметра. На рис. 5.5—5.6 приведены результаты количественного и графического анализа чувствительности NPV к изменениям объемов выпуска. В качестве входного параметра в окне диалога была указана ячейка В2. ? А рг : 4 J з • 1 , ! С і I В tea І; Анализ чувствительности NPV а

а

f

,6 | Количество (0} Ценз

Переменные расходы (V) Норма дисконта г Срок реализации (л) 20400 эи.00

30.00 0.1(1 5,00 hi ічальньїе инвестиции (0 Постоянные расход^- (F1 Амортизация (А) Остаточная стоимости VSJ Чал or IT) 2000 JO 5ІІШ,00 ЮЛ 00 200.00 0.60 в

$ Чистые платежи (N0Ft) 1160,00 Значения NPV Ш W

В\ 13

14

15

J6 на\*мия варьируемого парімепрз Ж 260 220 180 140 10Г 60 36^8 Я t691,jfi 5478,31 4265,26 3052,21 1839,16 626,10 -586.95

«I , *

* - 4 л«яі яшжшывтаашши Рис. 5.5. Анализ чувствительности NPV к изменению объемов выпуска

Построенная по результатам анализа чувствительности диаграмма (блок ячеек C11.D17) позволяет даже визуально определить примерный объем выпуска продукта (около 80 штук), обеспечивающий при прочих равных условиях безубыточность проекта.

Из результатов анализа по двум параметрам следует вывод, что NPV проекта более чувствительна к изменениям цены, чем объемов выпуска. При неизменных значениях остальных показателей падение цены менее чем на 20% приведет к отрицательной величине чистой современной стоимости проекта (см. рис. 5.4), тогда как снижение объемов выпуска с 300 до 100 единиц при прочих равных условиях все еще обеспечивает положительную величину NPV{рис. 5.5).

Завершая рассмотрение данного метода, отметим его преимущества и недостатки.

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

Вместе с тем данный метод обладает и рядом недостатков, наиболее существенные из них: •

жесткая детерминированность используемых моделей для связи ключевых переменных; •

не позволяет получить вероятностные оценки возможных отклонений исходных и результирующих показателей; •

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

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