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

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

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

Данный анализ вызывает особый интерес, как у инвесторов, так и у управляющих бизнесом. Его результаты несут особенную ценность в аналитике бизнес проектов. 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 с примером и формулами можно посмотреть, скачав файл с готовым примером.

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

Определение

В широком смысле, анализ чувствительности оценивает степень изменчивости выходного параметра к изменению одного из входных параметров при условии, что остальные входные параметры остаются неизменными. При анализе чувствительности инвестиционного проекта, как правило, оценивается воздействие изменения объема продаж, переменных затрат, постоянных затрат, ставки дисконтирования, ставки налога на прибыль и т.п. на его чистую приведенную стоимость (англ. Net Present Value, NPV). Помимо чистой приведенной стоимости проекта в качестве выходного параметра могут также использоваться внутренняя норма доходности (англ. Internal Rate of Return, IRR), дисконтированный срок окупаемости (англ. Discounted Payback Period) и т.п. Проведения этого вида анализа позволяет оценить устойчивость проекта к факторам риска.

Читать еще:  Как сделать abc анализ в excel формула?

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

  1. Рассчитывается значение выходного параметра (например, чистая приведенная стоимость) при базовом сценарии.
  2. Рассчитывается значение выходного параметра при изменении одного из входных параметров (остальные входные параметры остаются неизменными).
  3. Рассчитывается процентное изменение входного и выходного параметра относительно базового сценария.
  4. Рассчитывается степень чувствительности выходного параметра к изменению входного параметра путем деления процентного изменения выходного параметра на процентное изменение входного параметра.

В общем виде формулу оценки чувствительности можно записать следующим образом.

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

Пример расчета

Менеджмент компании «ХимТех ЛТД» проводит анализ чувствительности инвестиционного проекта, предполагающего запуск новой производственной линии. Реализация этого проекта предполагает осуществление первоначальной инвестиции в размере 500 000 у.е., посленалоговая стоимость привлекаемого капитала составляет 16%, а ставка налога на прибыль 30%. Основные показатели проекта для базового сценария представлены в таблице.

Амортизация оборудования включена в постоянные затраты и составляет 40 000 у.е. в год.

Проведем анализ чувствительности чистой приведенной стоимости инвестиционного проекта к изменению:

  • постоянных затрат;
  • объема продаж;
  • цены единицы продукции;
  • переменных затрат на единицу продукции.

Поэтапный расчет дисконтированного чистого денежного потока по годам приведен в таблице.

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

NPV = -500 000 + 131 034 + 136 891 + 160 164 + 137 686 + 111 030 = 176 805 у.е.

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

NPV = -500 000 + 128 017 + 134 238 + 157 810 + 135 579 + 109 113 = 164 757 у.е.

Процентное изменение NPV = (164 757 — 176 805) ÷ 176 805 × 100% = -6,81%

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

Чувствительность NPV = -6,81% ÷ 5% = -1,362

Это означает, что при увеличении постоянных затрат на 1% чистая приведенная стоимость проекта будет уменьшаться на 1,362%, и наоборот, при снижении постоянных затрат на 1% чистая приведенная стоимость проекта будет увеличиваться на 1,362%.

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

NPV = -500 000 + 138 879 + 144 902 + 169 246 + 145 573 + 117 545 = 216 145 у.е.

Процентное изменение NPV = (216 145 — 176 805) ÷ 176 805 × 100% = 22,25%

Чувствительность NPV = 22,25% ÷ 5% = 4,450

Таким образом, при росте объема продаж на 1% NPV проекта будет увеличиваться на 4,450%, и наоборот, при снижении объема продаж на 1% NPV проекта будет снижаться на 4,450%.

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

NPV = -500 000 + 152 155 + 157 491 + 183 170 + 157 896 + 128 277 = 278 989 у.е.

Процентное изменение NPV = (278 989 — 176 805) ÷ 176 805 × 100% = 57,79%

Чувствительность NPV = 57,79% ÷ 5% = 11,558

При росте цены единицы продукции на 1% NPV проекта будет увеличиваться на 11,558%, и наоборот, при снижении цены единицы продукции на 1% NPV проекта будет снижаться на 11,558%.

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

NPV = -500 000 + 117 759 + 124 301 + 146 240 + 125 363 + 100 298 = 113 961 у.е.

Процентное изменение NPV = (113 961 — 176 805) ÷ 176 805 × 100% = -35,54%

Чувствительность NPV = -35,54% ÷ 5% = -7,109

При росте переменных затрат на единицу продукции на 1% NPV проекта будет уменьшаться на 7,109%, и наоборот, при снижении переменных затрат на единицу продукции на 1% NPV проекта будет увеличиваться на 7,109%.

Читать еще:  Как сделать инверсию в excel?

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

Результаты анализа чувствительности инвестиционного проекта из приведенного выше примера можно визуализировать следующим образом.

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

Финансы в Excel

Мастер проектов. Предварительная оценка

Программа Мастер проектов. Предварительная оценка. Версия 5 — это совместная разработка интернет-проекта Финансы в Excel и Консультационной группы «Воронов и Максимов». Программа предназначенную для быстрой оценки эффективности инвестиционных проектов на стадии предварительных исследований.

Рабочий файл программы представляет собой книгу электронных таблиц с набором диаграмм. Функционирует в среде Microsoft Excel версии 2000 и более поздних, как в среде Windows, так и под управлением MacOS (Excel for Mac 2011 и старше).

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

  • Экспресс-анализ инвестиционных альтернатив.
  • Комплексная оценка эффективности инвестиций на основании минимума необходимой исходной информации.
  • Предварительная экспертиза проектов.

Программа не предназначена для оценки финансовой состоятельности проектов.

В программе Мастер проектов. Предварительная оценка достигнуто уникальное сочетание оперативности и точности оценки инвестиционной привлекательности проектов и коммерческих предложений. В частности:

  • Полный цикл анализа занимает от 5 до 20 минут, включая подготовку и распечатку краткого отчета-резюме, иллюстрированного диаграммами.
  • Четко структурированный блок подготовки исходных данных состоит из пяти ключевых разделов. Таким образом, окончательный результат может быть получен на основании всего лишь нескольких цифр, описывающих проект.
  • Расхождение с результатами более детального анализа в большинстве случаев не превышает 10-15 процентов.

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

Показатели, рассчитываемые без учета стоимости капитала:

  • Прибыльность продаж.
  • Простая норма прибыли на инвестиции.
  • Простой срок окупаемости инвестиций.
  • Точка безубыточности.
  • Точка платежеспособности.

Показатели, рассчитываемые с учетом стоимости капитала:

  • Чистая современная ценность инвестиций (NPV).
  • Индекс доходности инвестиций (PI).
  • Дисконтированный срок окупаемости инвестиций.
  • Внутренняя ставка доходности инвестиций (IRR).
  • Максимальная ставка кредита.

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

  • Объем продаж.
  • Цены на продукцию.
  • Постоянные инвестиционные затраты.
  • Переменные производственные затраты.
  • Постоянные производственные затраты.

Все расчеты выполняются в одной валюте и в постоянных ценах.

Установка программы

Демонстрационная и коммерческая версии программы работают в операционных системах Windows и MacOS, с предварительно установленным Microsoft Excel (2000-2016 для Windows или Excel for Mac 2011).

В разделе Загрузки доступнен для скачивания файл демонстрационной версии программы:

  • mptr5.demo.xls — рабочий файл в формате Excel

Демонстрационная версия работает в всех версиях Excel 2000 и старше. При желании можно сохранить файл в формате XLSM (Excel 2007 с поддержкой макросов).

Скачайте файл на персональный компьютер откройте его в Excel.

Программа не использует надстройку ExcelFin.AddIn2, вся автоматизация работы поддерживается на уровне макросов VBA, встроенных непосредственно в рабочую книгу. Эта надстройка может быть использована для подключения языковых файлов ресурсов.

Запуск программы

Запуск программу осуществляется через открытие файла mptr5.xls (или mptr5.demo.xls) в среде Excel. В дальнейшем файл может быть сохранен с любым другим именем.

Демонстрационная версия программы защищена паролем на уровне книги Excel и некоторых рабочих листов. Запрещено добавление и удаление строк в справочники ресурсов и спецификаций, редактирование ии форматирование при этом доступно в полной мере. Кроме того, главные расчетные формулы скрыты от просмотра. Полная коммерческая версия поставляется без защиты.

После успешного запуска программы откроется Excel-файл программы. Откроется основной лист с информацией о версии программы и авторских правах.

Для нормальной работы программы необходимо подключить макросы Excel.

Состав файла

Программа представляет собой файл формата Excel с набором макросов (программой на VBA).

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

Файл включает один рабочий лист и три диаграммы:

  • MAIN — главный лист модели.
  • DIAG1 — диаграмма.
  • DIAG2 — диаграмма.
  • DIAG3 — диаграмма.

На листе MAIN данные сгруппированы в отдельные блоки-таблицы. Для отображения итогов таблиц можно использовать страндартное интерфейсное средство показа и скрытия строк — кнопки «+/-» в блоке структуры листа.

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

Читать еще:  Как сделать числовой формат в excel?

Главный лист (MAIN)

Главным листом программы Мастер проектов. Предварительная оценка является лист MAIN.
На этом листе расположены семь основных таблиц модели:

  1. Информация о проекте
  2. Информация для настройки модели
  3. Исходная информация
  4. Промежуточные результаты
  5. Оценка проекта без учета стоимости капитала
  6. Оценка проекта с учетом стоимости капитала
  7. Анализ чувствительности

Кроме того, на этом же листе находится большой блок «Служебная информация», расположенный под последней таблицей. В дистрибутивном файле этот блок является скрытым.

Все ячейки листа MAIN могут быть разделены на два типа:

  • Ячейки ввода исходных данных — светло-зеленый фон.
  • Ячейки, содержание которых в общем случае не предполагает вмешательства пользователя — белый фон.

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

В таблице I рабочего файла программы вводится информация о выполеняемом расчете:

  • Организация
  • Автор проекта
  • Название, версия проекта
  • Дополнительное описание

В таблице II настраиваются параметры, влияющие на алгоритм расчета:

  • Денежная единица — расчетная валюта
  • Интервал планирования — месяц, квартал, полугодие, год или другой. Для последнего возможен ввод произвольной Продолжительности интервала в днях.
  • Количество интервалов планирования — настройка автоматически изменяет Горизонт исследования*

Исходные данные

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

  • Постоянные инвестиционные затраты.
  • Выручка от реализации.
  • Переменные производственные затраты.
  • Постоянные производственные затраты.
  • Потребность в чистом оборотном капитале.

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

Некоторые исходные данные могут вводиться в нескольких строках. Для настройки количества таких справочников имеются специальные ячейки в столбце «C». Настраиваются следующие параметры проекта:

  • Количество видов оборудования
  • Количество видов продукции
  • Количество видов переменных затрат
  • Количество видов прочих постоянных производственных затрат

Во всех настраиваемых параметрах допускается изменять наименование элемента в столбце «В».

Результаты

Результаты расчета инвестиционного проекта сгруппированы в трех таблицах:

IV. Промежуточные результаты
V. Оценка проекта без учета стоимости капитала
VI. Оценка проекта с учетом стоимости капитала

Ставка дисконтирования для оценки инвестиций с учетом стоимости капитала вводится в соответствующую ячейку таблицы VI.

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

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

Выполнение анализа чувствительности в программе производится в двух ракурсах — для построения графиков зависимости показателей эффективности инвестиций от ключевых исходных параметров проекта и для расчета критических значений последних.

Условие для анализа чувствительности выбирается из списка в соответствующей ячейки таблицы VII.

  • Дисконтированный срок окупаемости инвестиций равен горизонту исследования
  • Чистая современная ценность инвестиций равна нулю

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

Кроме того, вызов макроса выполнения анализа чувствительности может осуществляться с помощью кнопки √ , в правой части заголовка таблицы VII.*

Вызов функции пользовательского интерфейса програмы инициирует пересчет таблиц подстановки данных — специального инструмента пакета Microsoft Excel, используемого для автоматизации серии единообразных расчетов при изменении одного или двух исходных параметров.
Сами таблицы подстановки данных, используемых в модели для выполнения анализа чувствительности, расположены в блоке «Служебная информация» под последней расчетной таблицей. В дистрибутивной копии модели этот блок скрыт.

В состав программы Мастер проектов. Предварительная оценка входят три диаграммы, расположенные каждая на отдельном листе. Название листа состоит из слова «DIAG» и порядкового номера диаграммы:

  1. «Чистые денежные потоки».
  2. «Зависимость чистой современной ценности инвестиций от ставки сравнения» или
    «Зависимость дисконтированного срока окупаемости инвестиций от ставки сравнения».
  3. «Зависимость чистой современной ценности инвестиций от ключевых исходных параметров проекта» или
    «Зависимость дисконтированного срока окупаемости инвестиций от ключевых исходных параметров проекта».

Название второй и третьей диаграммы зависит от выбранного условия анализа чувствительности.

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

Об авторах

Консультационная группа «Воронов и Максимов» создана в 1996 году специалистами, имеющими значительный опыт работы в консалтинговом бизнесами, авторами-разработчиками известных в России программных продуктов.
Основные направления деятельности группы — консалтинг в сфере финансового менеджмента, разработка корпоративных информационных и аналитических систем, обучение современным методам управления предприятиями.
Группой разработаны уникальные компьютерные программы: для финансового анализа и планирования — «МАСТЕР ФИНАНСОВ», для планирования и оценки инвестиционных проектов — «МАСТЕР ПРОЕКТОВ», а также ряд эксклюзивных систем различного профиля.

* В демонстрационной версии программы эта операция отключена.

Ссылка на основную публикацию
Adblock
detector