Как сделать анализ чувствительности в excel

Обновлено: 06.01.2025

Под анализом чувствительности понимают динамику изменений результата в зависимости от изменений ключевых параметров. То есть что мы получим на выходе модели, меняя переменные на входе.

Данный анализ вызывает особый интерес, как у инвесторов, так и у управляющих бизнесом. Его результаты несут особенную ценность в аналитике бизнес проектов. Excel позволяет анализировать чувствительность инвестиционных проектов, пользователям с базовыми знаниями в области финансов.

Метод анализа чувствительности

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

По своей сути метод анализа чувствительности – это метод перебора: в модель последовательно подставляются значения параметров. К примеру, мы хотим узнать, как изменится стоимость фирмы при изменении себестоимости продукции в пределах 60-80%.

Используется и обратный метод, когда результат модели на выходе «подгоняется» к изменению значений на входе.

Основные целевые измеримые показатели финансовой модели:

1. NPV (чистая приведенная стоимость). Основной показатель доходности инвестиционного объекта. Рассчитывается как разность общей суммы дисконтированных доходов и размера самой инвестиции. Представляет собой прогнозную оценку экономического потенциала предприятия в случае принятия проекта.
2. IRR (внутренняя норма доходности или прибыли). Показывает максимальное требование к годовой прибыли на вложенные деньги. Сколько инвестор может заложить в свои расчеты, чтобы проект стал привлекательным. Если внутренняя норма рентабельности выше, чем ожидаемый доход на капитал, то можно говорить об эффективности инвестиций.
3. ROI/ROR (коэффициент рентабельности/окупаемости инвестиций). Рассчитывается как отношение общей прибыли (с учетом коэффициента дисконтирования) к начальной инвестиции.
4. DPI (дисконтированный индекс доходности/прибыльности). Рассчитывается как отношение чистой приведенной стоимости к начальным инвестициям. Если показатель больше 1, вложение капитала можно считать эффективным.

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

Анализ чувствительности инвестиционного проекта в Excel

Задача – проанализировать основные показатели эффективности инвестиционного проекта. Для примера возьмем условные цифры.

Начинаем заполнять таблицу для анализа чувствительности инвестиционного проекта:

1. Рассчитаем денежный поток. Так как у нас динамический диапазон, понадобится функция СМЕЩ. При расчете учитываем ликвидационную стоимость (в нашем примере – 0, неизвестна). Расчет будем производить «без дат». То есть они не повлияют на результаты. Денежный поток в «нулевом» периоде равняется предынвестиционным вложениям. В последующих периодах: .
2. Для расчета срока окупаемости инвестиционного проекта (РР) создаем дополнительный столбец. В инвестиционный период будут суммироваться все дополнительные инвестиции за вычетом прибыли от суммы вложенных финансовых средств. Формула для «нулевого» периода: =СУММЕСЛИ(G7:G17;" 0;G8;0). Где Н7 – это прибыль предыдущего периода (значение в ячейке выше). G8 – денежный поток в данном периоде (значение ячейки слева).
3. Теперь найдем, когда проект начнет приносить прибыль. Или точку безубыточности: =ЕСЛИ(H7>=0;$C7;""), где Н7 – это прибыль в текущем периоде (значение ячейки слева). С7 – это номер текущего периода (первый столбец).
4. Найдем рентабельность инвестиций. Это отношение прибыли в текущем периоде к предынвестиционным вложениям. Формула в Excel: =СУММ($H$7;H8)/-$H$7.
5. Рассчитаем коэффициент дисконтирования. Формула для нашего примера (где даты не учитываются): =1/(1+$B$1)^C7. В1 – ячейка с процентным выражением ставки дисконтирования. С7 – номер периода.
6. Найдем дисконтированную (приведенную) стоимость. Это произведение значения денежного потока в текущем периоде и коэффициента дисконтирования. Формула: =G7*K7.
7. Найдем индекс рентабельности (или дисконтированный индекс рентабельности). Аббревиатура – PI. Это отношение дисконтированной стоимости к начальным вложениям. Формула в Excel: =L8/-$G$7.
8. Найдем внутреннюю норму прибыли (IRR). Если даты не учитываются (как в нашем примере), воспользуемся встроенной функцией ВСД. Функция: =ВСД(G7:G17). Если даты учитываются, то подойдет функция ЧИСТВНДОХ. Посчитаем РР – срок окупаемости проекта. Для этой цели используем вложенные функции: . Или возьмем данные из таблицы.

• срок проекта – 10 лет;
• чистый дисконтированный доход (NPV) – 107228р. (без учета даты платежей, принимая все периоды равными);
• для нахождения данного значения возможно использование встроенных функций ЧПС и ПС (для аннуитетных платежей);
• дисконтированный индекс рентабельности (PI) – 1,54;
• рентабельность инвестиций (ROR) – 25%;
• внутренняя норма доходности (IRR) – 21%;
• срок окупаемости (РР) – 4 года.

Можно еще найти среднегодовую чистую (за вычетом оттоков) прибыль без учета инвестиций и процентной ставки: =(E18+СУММ(F7:F17))/C20. Где Е18 – сумма притоков денежных средств, диапазон F7:F17 – оттоки; С20 – срок инвестиционного проекта.

Таблицу Excel с примером и формулами можно посмотреть, скачав файл с готовым примером.

Финансовые модели, использующие росписи и условные переменные, не поддаются преобразованию в уравнения

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

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

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

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

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

Основными целевыми измеримыми результатами финансовой модели являются, как мы разобрали ранее, сумма 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:

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

Для анализа полученного оптимального решения в MS Excel предусмотрены три типа отчетов: отчет по результатам, устойчивости и пределам.

Проведем анализ чувствительности задачи (2.1). Для этого необходимо после запуска в Excel задачи на решениев окне Результаты поиска решения выделить с помощью мыши три типа отчетов: Результаты, Устойчивость и Пределы (рис. 3.1).

Рис. 3.1. Типы отчетов

Отчет по результатам.Отчет по результатам состоит из трех таблиц (рис. 3.2):

1) таблица 1 содержит информацию о целевой функции;

2) таблица 2 содержит информацию о значениях переменных, полученных в результате решения задачи;

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

Для ограничений в столбце Формула приведены зависимости, которые были введены в диалоговое окно Поиск решения; в столбце Значение приведены величины использованного ресурса.

Рис. 3.2. Лист отчета по результатам

Если ресурс используется полностью (то есть ресурс дефицитный), то в графе Статуссоответствующее ограничение указывается как «связанное»; при неполном использовании ресурса (то есть ресурс недефицитный) в этой графе указывается «не связан.».

Для граничных условий (строки 24-27 на рис. 3.2) в графе Разница показана разность между значением переменной в найденном оптимальном решении и заданным для нее граничным условием.

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

Так, если на ресурс наложено ограничение типа

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

Если на ресурс наложено ограничение типа

, то в графе Разница дается количество ресурса, которое не используется при реализации оптимального решения.

Отчет по устойчивости. Отчет по устойчивости состоит из двух таблиц (рис.3.3).

Таблица 1 содержит информацию, относящуюся к переменным:

результирующие значения переменных;

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

коэффициенты целевой функции;

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

Рис. 3.3. Отчет по устойчивости

Таблица 2 (рис. 3.3) содержит информацию, относящуюся к ограничениям:

величина использованных ресурсовв колонке Результ. значение;

теневые цены, т.е. двойственные оценки, которые показывают, как изменится целевая функция при изменении ресурсов на единицу. Теневая цена рассчитывается только для дефицитных ресурсов;

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

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

Рис. 3.4. Отчет по пределам

Двойственная задача линейного программирования

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

Инвестиции

Зачем нужен анализ чувствительности проекта?

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

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

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

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

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

Формула чувствительности проекта

Чувствительность проекта можно оценить по следующей формуле.

Например, если при увеличении цены на 20% чистый дисконтированный доход увеличивается на 250%, то чувствительность проекта 12,5. Это означает, что при изменении цены на 1% NPV увеличится на 12,5%.

Алгоритм проведения анализа

Для проведения анализа необходимо выполнение следующих шагов:

Пример анализа чувствительности проекта

Оценить чувствительность проекта к изменению: а) уровня цен; б) уровня средних переменных затрат; в) изменению уровня общих постоянных затрат. Если единоразовые первоначальные инвестиции по проекту составили 800 000 руб. Горизонт расчета 3 года. Ожидаемые показатели деятельности турфирмы не изменяются по годам и составляют: объем реализации турфирмы – 145 путевок при среднем чеке 25 000 руб.; переменные издержки на единицу продукции – 14 200 руб.; общие постоянные издержки за год – 1 024 000 тыс. руб. Ставка дисконтирования 15%.

1. Для начала необходимо определить значение NPV без учета влияния факторов:

2. Далее необходимо определить, какой будет чистый дисконтированный доход, если, например, цена увеличится на 20%. Все остальные факторы не меняются.

3. Аналогично рассчитываются значения NPV для других отклонений факторов. Например, при изменении цены на -20%, -10%, +10%, +20%.

4. Далее аналогично определяется влияние других факторов на NPV.

а) чувствительность к изменению уровня цен

Из таблицы видно, что при увеличении цены всего на 20%, чистый дисконтированный доход проекта увеличивается на 378%. Аналогично снижение цены на 20% вызывает падение NPV на 378%. Таким образом, проект очень чувствителен к изменению цены. И инвестору необходимо уделять большое внимание этой проблеме.

б) чувствительность к изменению уровня средних переменных затрат

в) чувствительность к изменению уровня общих постоянных затрат

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

График чувствительности проекта

На основе данных предыдущего примера, проведем графический анализ чувствительности NPV проекта к изменению факторов.

График чувствительности NPV к отклонениям влияющих факторов

Из графика видно, что проект наиболее чувствителен к изменению цены. При уменьшении цены всего на 5% проект уходит в отрицательную область, то есть NPV < 0.

Следующим по чувствительности фактором в данном примере являются средние переменные затраты. При их увеличении на 10% инвестиционный проект становится убыточным.

Заключение

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

Ниже, нажав на кнопку, можно проверить, насколько хорошо Вы разбираетесь в

Читайте также:

  
• 1с 8 стартер настройка
  
• Как изменить количество знаков после запятой на графике в excel
  
• К какому ппп можно отнести ms excel
  
• Переплата по ндс как отразить в 1с
  
• Как использовать visual studio без регистрации