Кадр из кинофильма "Чапаев". Реж. братья Васильевы

Термин «Анализ чувствительности» для неопытных аналитиков регулярно становится камнем преткновения. Часто начинающие аналитики даже не могут понять, о чем их просят. Снисходительного отношения от задающих этот вопрос легко избежать, если знать, что под анализом чувствительности подразумевается динамика изменения результата модели на выходе в зависимости от изменения ключевых переменных модели на входе. Целью анализа чувствительности является определение характера зависимости результата модели от переменных и пороговых величин переменных, при которых выводы модели больше не поддерживаются. 

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

- Василий Иванович, а ты армией командовать могёшь? 
- Могу. 
- А фронтом? 
- Могу, Петька, могу. 
- А всеми вооружёнными силами Республики? 
- Малость подучиться, смогу и вооружёнными силами. 
- Ну, а... в мировом масштабе, Василий Иванович, совладаешь? 
- Нет, не сумею, языков не знаю.

Герой классического кинофильма С. Эйзенштейна в данном случае выстроил в уме модель своих полководческих талантов, определил ее критические переменные (опыт, формальное образование, коммуникативные навыки) и проделал комплексный анализ чувствительности ко всем переменным, определив критическую (уровень владения иностранными языками снижает качество коммуникации «в мировом масштабе» до неприемлемого уровня) и некритическую (на должность главкома Республики недостаточно формального образования). 

Основными целевыми измеримыми результатами финансовой модели являются, как мы разобрали ранее, сумма NPV и PV(gr), выражающая целевую стоимость фирмы, и IRR, выражающий имплицитную доходность денежного потока. Они, как правило, и являются теми результатами, в отношении которых проводится анализ чувствительности. Разумеется, чувствительность любых других численных расчетных показателей также определена и может быть выражена количественно. При необходимости возможно, например, анализировать чувствительность кумулятивного операционного денежного потока, расходного бюджета, времени достижения операционной самоокупаемости и так далее. Можно также сделать производные показатели и анализировать чувствительность к ним. 

Анализ чувствительности можно проводить по любому числу переменных. Фактически инструментарий Excel дает аналитику непосредственный выбор из одной или двух переменных, для анализа чувствительности в пространстве большего числа измерений надо разрабатывать собственную схему или устанавливать коммерческий модуль разработки третьей стороны.

Предположим, что мы хотим понять, как на стоимость фирмы влияет запланированная цена единицы продукта фирмы и себестоимость продукта, при прочих равных условиях. В модели, разумеется, содержатся количественное значение и алгоритмы расчета цены и себестоимости – допустим, цена одной единицы 100 денежных единиц, а себестоимость – 75% от выручки. Но насколько быть уверенным в этом значении и что, если мы определили его ошибочно? Анализ чувствительности отвечает на этот вопрос: мы можем оценить, как меняется стоимость фирмы при изменении цены продукта в границах от, предположим, 50 до 150 и себестоимости от 65% до 85%.

Введем также производный параметр – нас будет интересовать не просто стоимость фирмы, но ее влияние на мультипликатор доходности для доли инвестора. Предположим, что инвестор ожидает доходность индивидуальной инвестиции в диверсифицированном портфеле за 5 периодов не менее чем x10 в дополнение к возврату стоимости собственного капитала на уровне, допустим, 15% (о роли мультипликаторов и диверсификации см. раздел «Портфель венчурного фонда: Какие стартапы нужны профессиональным инвесторам»).

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

Для создания матрицы чувствительности нет необходимости многократно менять параметры модели и переносить их в модель – эта задача выполняется встроенной функцией Data Table, выполняющей перебор автоматически. Data Table считывает переменные из ряда и колонки (или, в случае анализа по только одной переменной, из ряда либо колонки), подставляет их в заданные ячейки и выводит матрицу результатов формулы, стоящей в верхней левой ячейке. Ни к какой другой формуле Data Table адресоваться не умеет – таково встроенное ограничение этой функции. Поэтому целесообразно помещать во главу матрицы не сам расчетный параметр, а вызов его значения, – это позволит не смешивать анализ чувствительности с расчетами и при необходимости вынести его и на другой лист. 

Последовательность создания матрицы инструментом Data Table следующая: 

  1. Поместить в верхнюю левую ячейку будущей ячейки вызов целевого значения модели (в нашем случае =B32);
  2. Поместить по горизонтали от вызова целевого значения модели ряд значений первой переменной, которые вы хотите перебрать в модели (в нашем случае, фактор себестоимости отложен по горизонтали);
  3. Поместить по вертикали от вызова целевого значения модели ряд значений второй переменной, которые вы хотите перебрать в модели (в нашем случае, цена единицы продукта отложена по вертикали);
  4. Если вы проводите анализ только по одной переменной, вы ограничиваетесь либо пунктом 3, либо пунктом 4. Последовательность переменных не важна, выбирать, какую из них откладывать по горизонтали, а какую по вертикали, имеет смысл только с учетом числа шагов каждой переменной – по вертикали их помещается больше;
  5. Выделить весь массив будущей матрицы чувствительности;
  6. Вызвать функцию командой меню Data-Table или выделенной иконкой на панели или ленте;
  7. Ввести в первое окно диалога «переменную ряда» – то есть ту ячейку, откуда модель, а не таблица данных, считывает переменную «фактор себестоимости» (в нашем случае, B4) 
  8. Ввести вo второе окно диалога «переменную колонки» – то есть ту ячейку, откуда модель, а не таблица данных, считывает переменную «фактор себестоимости» (в нашем случае, B4) 
  9. Если вы проводите анализ только по одной переменной, вводите адрес только для той переменной модели, ряд переменных значений которой была вами отложена по горизонтали – для горизонтального ряда в окно «ряд», для вертикальной колонки в окно «колонка»
  10. Нажмите OK. Выделенное пространство будет заполнено значениями целевого показателя модели, рассчитанными для данной пары значений переменных при прочих равных (при расчете по одной переменной, вы получите ряд значений целевого показателя для значений одной переменной при прочих равных). В нашем примере, значение 6.38 в ячейке С37 означает, что мультипликатор доли инвестора при цене продукта 50 и себестоимости в 65% от продаж составит 6.38. 

Обратим внимание, что в матрице использована переменная цветная заливка, которая распределилась по кривой, после сглаживания напоминающей гиперболу. Это «граница чувствительности» – линия, разделяющая зоны, где значения переменных указывают на возможность одобрить решение, и зона, где значения переменных указывают на то, чтобы решение отклонить. Мы использовали здесь команду «Условное форматирование», позволяющей изменить стиль ячейки в зависимости от того, отвечает ли ее содержание заданному критерию В данном случае, мы сравниваем значение ячейки с значением именованного массива mult, содержащего целевое значение инвестиционного мультипликатора, по следующему алгоритму: 

Отклонения от целевого значения мультипликатора более чем на 1 в большую сторону отмечаются ЗЕЛЕНОЙ заливкой – это пространство, где решение можно уверенно принять Отклонения от целевого значения мультипликатора не более чем на ±1 отмечаются ЖЕЛТОЙ заливкой – это пространство, где могут возникнуть колебания, стоит или не стоит принимать решение Отклонения от целевого значения мультипликатора более чем на 1 в меньшую сторону отмечаются КРАСНОЙ заливкой – это пространство, где решение можно уверенно отклонить.

Настройка цвета выполняется диалогом Format-Conditional Formatting: 

Цвет не имеет другого значения, кроме как повысить наглядность анализа чувствительности, проведя визуальную границу между «да» и «нет».