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

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

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

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

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

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

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

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

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

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



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

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

Входные данные:

Входные данные.

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

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

Подведем итоги:

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

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

Срок инвестиционного проекта.

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

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


en ru