Анализ чувствительности в excel пример таблица данных

Факторный и дисперсионный анализ в Excel с автоматизацией подсчетов

Дисперсионный анализ в Excel

Цель подобного анализа – разделить изменчивость величины на три компонента:

1. Изменчивость в результате влияния других значений.
2. Изменения из-за взаимосвязи влияющих на него значений.
3. Случайные изменения.

Выполним дисперсионный анализ через надстройку Excel «Анализ данных». Если она не включена, ее можно подключить в параметрах.
Начальная таблица должна соответствовать двум правилам: на каждую величину приходится один столбец, и данные в нем располагаются по возрастанию или по убыванию. Необходимо проверить влияние уровня образования на поведение в конфликте.

19

1. Находим во вкладке «Данные» инструмент «Анализ данных» и открываем его окно. В списке нужно выбрать однофакторный дисперсионный анализ.

20

2. Заполняем строки диалогового окна. Входной интервал – все ячейки без учета шапки и номеров. Группируем по столбцам. Выводим результаты на новый лист.

21

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

Факторный анализ в Excel: пример

Проанализируем взаимосвязь данных в сфере продаж – необходимо выявить популярные и непопулярные товары. Начальная информация:

22

1. Нужно выяснить, на какие товары больше всего вырос спрос в течение второго месяца. Составляем новую таблицу для определения роста и снижения спроса. Рост рассчитывается по этой формуле: =ЕСЛИ((Спрос 2-Спрос 1)>0; Спрос 2- Спрос 1;0). Формула снижения: =ЕСЛИ(Рост=0; Спрос 1- Спрос 2;0).

23

2. Подсчитаем рост спроса на товары в процентах: =ЕСЛИ(Рост/Итог 2 =0;Снижение/Итог 2; Рост/Итог 2).

24

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

25

Двухфакторный дисперсионный анализ в Excel

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

26

1. Открываем «Анализ данных», в списке нужно найти двухфакторный дисперсионный анализ без повторений.
2. Входной интервал – ячейки, где содержатся данные (без шапки). Выводим результаты на новый лист и жмем «ОК».

27

Показатель F больше, чем F-критическое – это означает, что пол влияет на скорость реакции на звук.

28

Чувствительность – проект

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

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

Анализ чувствительности проекта ( sensitivity analysis) в планировании инвестиций состоит в проверке того, будет ли проект по-прежнему выгодным, если некоторые основные его переменные факторы окажутся не такими, как запланировано. Удобным инструментом, который широко применяется для проведения анализа чувствительности прогнозов в инвестиционных проектах, является компьютерная программа для табличных вычислений. Речь идет о программах Excel, Lotus 123 или Quattro Pro ( см. табл. 6.3) Табл. Строки с первой по пятую показывают первоначальные предположения, которые использовались для составления прогноза. Формулы выражены в виде переменных в ячейках В2 – В5 таким образом, что если введенные значения изменяются, то пересчитывается вся таблица. Итак, величина, введенная в ячейку ВЗ, обозначает объем продаж продукции в единицах. Вначале в ней указано 4000 компьютеров.  

Анализ чувствительности проекта ( sensitivity analysis) – в планировании инвестиций состоит в проверке того, будет ли проект по-прежнему выгодным, если некоторые основные его переменные факторы окажутся не такими, как запланировано.  

Диаграммы чувствительности проекта к изменению указанных исходных параметров приведены на рис. 13.7. Подобные диаграммы имеют вид лучей, расходящихся от точки, соответствующей базовому значению переменного параметра. Из этого рисунка видно, что для рассматриваемого примера зависимость ЧДД от всех переменных параметров близка к линейной, в то время как для ВНД она имеет отчетливо нелинейный характер. В целом данный проект характеризуется малой чувствительностью к изменению расходов на рекультивацию, но достаточно чувствителен к изменению капитальных вложений и годовой прибыли.  

Зависимость нормы рентабельности от цены.| Зависимость нормы рентабельности от изменения объема сбыта.

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

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

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

Лучевая диаграмма значений чистого дисконтированного дохода при изменении исходных параметров проекта освоения месторождения Сопка Кварцевая ( базовый вариант. / – цены на золото. 2 – капитальных затрат. 3 – эксплуатационных затрат.

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

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

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

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

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

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

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

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

1. Рассчитаем валютный поток. Потому что у нас динамический спектр, пригодится функция СМЕЩ. При расчете учитываем ликвидационную стоимость (в нашем примере – 0, неведома). Расчет будем создавать «без дат». Другими словами они не воздействую на результаты. Валютный поток в «нулевом» периоде приравнивается предынвестиционным вложениям. В следующих периодах: .
2. Для расчета срока окупаемости вкладывательного проекта (РР) создаем доп столбец. В вкладывательный период будут суммироваться все доп инвестиции за вычетом прибыли от суммы вложенных денежных средств. Формула для «нулевого» периода: =СУММЕСЛИ(G7:G17;» 0;G8;0). Где Н7 – это прибыль предшествующего периода (значение в ячейке выше). G8 – валютный поток в данном периоде (значение ячейки слева).
3. Сейчас найдем, когда проект начнет приносить прибыль. Либо точку безубыточности: =ЕСЛИ(H7>=0;$C7;»»), где Н7 – это прибыль в текущем периоде (значение ячейки слева). С7 – это номер текущего периода (1-ый столбец).
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 позволяет определить критические точки, от которых напрямую зависит жизнеспособность бизнес-идеи. Среди специалистов такая процедура всё чаще называется однофакторной. Все данные для анализа отбираются заранее, за счёт чего можно оценить эффективность проекта

Несмотря на то что процедура имеет множество преимуществ, важно помнить и о недостатках:

1. Анализ не позволяет измерить корреляцию между элементами.
2. Такой способ расчёта пока считается экспериментальным, из-за чего аналитики, в итоге, могут увидеть совершенно индивидуальные данные чувствительности.

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

1. На первом этапе нужно определиться с цифрой, относительно которой будут осуществляться все расчёты чувствительности. Чаще всего в формуле используется NPV. В редких случаях аналитик может задействовать норму прибыли IRR.
2. После этого выбираются элементы, не внушающие уверенности. Например, внушительные финансовые траты на капстроительство, вложения в оборотные средства. Помимо этого, эксперт, определяя чувствительность, может рассматривать и чисто рыночные факторы — стоимость, объем реализации.
3. На этом этапе устанавливаются максимальные цифровые значения рискованных зон с обеих сторон.
4. Только когда все подготовительные работы для анализа выполнены, можно приступать к расчёту DPI, IRR и чувствительности.
5. На финальном этапе эксперт занимается построением графика чувствительности. В документации подробно описываются зависимости для каждой составляющей, за счёт чего можно наглядно выявить критичные точки в разработанном инвестпроекте.

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

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

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

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

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

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

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

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

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

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

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

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

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

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



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

Признаком качественно выполненного инвестиционного проекта является наличие анализа чувствительности параметров модели. Как результирующий итог модели (например, внутренняя норма доходности – IRR или объем инвестиций), поведет себя при том или ином изменении исходных посылок? Это не единственная область, где анализ чувствительности востребован.

Если итог получен в результате сложных вычислений, то влияние отдельных параметров очень удобно оценивать с помощью анализа «что-если». Рассмотрим последовательность действий для использования этого механизма.

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

Таблица 3.1. Нормы доходности инвестиционного проекта http://baguzin.ru/wp/?p=276 – Анализ чувствительности в Excel

Внутренняя норма доходности

Как повлияет на доходность проекта, снижение расходов на 3%? или увеличение на 5%? Как изменится доходность проекта при росте месячного дохода на 2% или при уменьшении месячного дохода на 8%?

Применив анализ «что-если», точнее одну из опций этого анализа – «таблицу данных»:

Таблица 3.2. Таблица данных http://baguzin.ru/wp/?p=276 – Анализ чувствительности в Excel

Разместим на листе ячейку с итоговой формулой. В нашем случае это ячейка F6, содержащая формулу: =ЧИСТВНДОХ(B2:B37;C2:C37)

Таблица 3.3. Введение формул в ячейку http://baguzin.ru/wp/?p=276 – Анализ чувствительности в Excel

На одну ячейку левее, то есть в ячейку Е6, введем название параметра, изменения которого мы будем изучать. В нашем примере «Рост инвестиций» (уменьшение инвестиций соответствует отрицательному проценту).

Под этим названием введите значения параметра. В нашем примере это значения от -10% до 10% в ячейках Е7:Е17.

Выделяем диапазон, который включает итоговую формулу (F6), заголовок (Е6) и значения параметра (Е7:Е17). В нашем примере диапазон Е6:F17.

Выбираем вкладку Формулы. В меню Анализ «что-если» – Таблица данных.

Таблица 3.4. Создание таблицы данных

В открывшемся меню в поле Подставлять значения по строке в: выбираем ячейку, в которой содержится значение параметра, использовавшееся при расчете итоговой формулы (F6). В нашем примере надо сослаться на ячейку F2. На самом деле ячейка F6 не ссылается на F2, но зато ячейка F6 ссылается на ячейки В2:В7. А ячейки В2:В7, в свою очередь, ссылаются на F2. То есть, такого рода процедура позволяет анализировать любой параметр, который на каком-то этапе влияет на значение.

В ячейках F7:F17 появятся значения доходности при уменьшении увеличении инвестиций ± 10%.

Таблица 3.5. Построение графика чувствительности нормы доходности

Аналогично обрабатываются данные для получения графика чувствительности внутренней нормы доходности от роста / уменьшения доходов по проекту. Поскольку доходы планируются не столь точно, как расходы, диапазон расширяем до ± 40%

Расчет точки безубыточности

Анализ чувствительности обычно состоит из нескольких этапов:

1. Выбор ключевого показателя эффективности инвестиций, в качестве которого может выступать внутренняя норма доходности (IRR) или чистый дисконтированный доход (NPV).

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

3. Установление верхних и нижних (предельных и номинальных) значений этих неопределенных факторов.

4. Расчет ключевого показателя для всех выбранных предельных значений факторов.

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

Вернуться к перечню инструкций по составлению бизнес-плана

450 человек изучает этот бизнес сегодня.

За 30 дней этим бизнесом интересовались 6921 раз.

Сохраните статью, чтобы внимательно изучить материал

Стандарты оформления титульного листа Бизнес-плана

Шаг шестой: анализ рынка

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

Как выбрать идеальный бизнес

С чего начать бизнес? – с идеи. 90% решений, которые вы ищите, уже существуют! А все что может принести пользу вашему бизнесу достойно применения. Как сказал Эдисон “Если хотите придумать отличные идеи, знайте, лучшие из них вы можете позаим…

Экология офиса или работа, которая нас убивает

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

Как написать инвестиционный меморандум

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

Карьерный тупик или новые горизонты?

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

Шаг одиннадцатый: Экологическая и нормативная информация

Если вы открываете компанию, связанную с производством товаров, то в своем бизнес-плане вам необходимо представить описание экологического аспекта вашего бизнеса и список нормативных документов, разрешающих производство, с указанием срока их действия…

Инвестиционная психотерапия

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

Скрытые факторы в ценообразовании

Когда мы смотрим на ценники в магазинах или интернете, выбирая тот или иной товар , иногда, у нас возникает вопрос, а почему то или иное изделие стоит дороже или дешевле, чем аналог?

Шаг седьмой: производственный план

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

По каким критериям можно судить об успешном развитии бизнеса

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

10 главных способов произвести впечатление на инвестора

Продолжаем публикацию переводов статей известных западных бизнес-консультантов. На этот раз представляем вашему вниманию перевод статьи Гая Кавасаки «Вперед за золотом».

Дополнительные факторы влияния

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

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

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

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

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

Роль в анализе безубыточности

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

Для чего это нужно делать? Каждому предпринимателю необходимо знать граничный показатель объема произведенной продукции, при неудачной реализации которого компания наверняка не потеряет оборотные средства, поэтому высчитывается некая точка безубыточности. Таким образом, оценка эффективности проекта сужается до единственного показателя, указывающего на условия, при которых совокупность всех постоянных и переменных издержек равна полученным доходам. Иными словами – в данной точке предприниматель не получает никакой прибыли, но и полученные убытки его не разоряют, поскольку полностью покрываются заработанными средствами.

Точка безубыточности выражается в двух эквивалентах – количественном, указывающем на необходимый объем производства, и денежном, который равен полученному доходу.

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

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

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

В процессе проигрывания сценария выбирают несколько проектов, которые должны обладать следующими положениями: 

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

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

Оценка восприимчивости проходит в несколько этапов:

1. Выбрать переменную, которая станет основой расчетных данных (дисконтированная прибыль в чистом виде или основной доход).
2. Определить дополнительные элементы (инвестиция в оборотном активе, стоимость ремонтных работ, объем проданного товара и его стоимость).
3. Числовой предел основных компонентов, который устанавливается с обоих участков.
4. Рассчитать дисконтированную прибыль в чистом виде и норму прибыльности, учитывая каждую переменную.
5. Построение графической плоскости. При этом учитывается каждая переменная, и визуально оцениваются критические точки в программе инвестиций.

Жизненный цикл инвестиционного проекта

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

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

Всего у каждого проекта можно выделить три этапа:

• Перед тем, как инвестор осуществит инвестиции, необходимо разработать бизнес – план. Он содержит подробное описание и обоснование планируемых действий, какие мероприятия, в какие сроки будут проходить и что в итоге получится. Какая выгода будет получена в итоге реализации проекта, также будет закреплено в бизнес – плане. Кроме этого, на данном этапе составляются все необходимые сметы. Стоит отметить, что все расчеты производятся на основе проведенных исследований, в том числе и маркетинговых. Все расходы, понесенные на этом этапе, впоследствии будут входить в состав амортизационных отчислений
• Непосредственно инвестирование. На этом этапе приобретается необходимое оборудование, недвижимость, начинаются строительные работы. Передумать делать инвестиции на данном этапе нельзя, поскольку инвестор в этом случае понесет колоссальные денежные потери
• Третий этап – эксплуатационный. На нем происходит возврат вложений, скорректированных на понесенные эксплуатационные расходы. От продолжительности этапа напрямую зависит доход от инвестиций

Каждый проект проходит такой жизненный цикл.

Анализ чувствительности в excel пример таблица данных

Признаком качественно выполненного инвестиционного проекта является наличие анализа чувствительности параметров модели. Как результирующий итог модели (например, внутренняя норма доходности – IRR или объем инвестиций), поведет себя при том или ином изменении исходных посылок? Понятно, что это не единственная область, где анализ чувствительности востребован…

Если итог получен в результате сложных вычислений, то влияние отдельных параметров очень удобно оценивать с помощью анализа «что–если». Рассмотрим последовательность действий для использования этого механизма.

Допустим, вам надо провести анализ чувствительности внутренней нормы доходности следующего инвестиционного проекта (см. также Excel-файл):

Ответы легко получить, применив анализ «что–если», точнее одну из опций этого анализа – «таблицу данных»:

1. Разместите на листе ячейку с итоговой формулой. В нашем случае это ячейка F6, содержащая формулу: =ЧИСТВНДОХ(B2:B37;C2:C37)

1. На одну ячейку левее, то есть в ячейку Е6, введите название параметра, изменения которого мы будем изучать. В нашем примере «Рост инвестиций» (уменьшение инвестиций соответствует отрицательному проценту).
2. Под этим названием введите значения параметра. В нашем примере это значения от -10% до 10% в ячейках Е7:Е17.
3. Выделите диапазон, который включает итоговую формулу (F6), заголовок (Е6) и значения параметра (Е7:Е17). В нашем примере диапазон Е6:F17.
4. Выберите вкладку Формулы. Пройдите по меню Анализ «что–если»  Таблица данных…

1. В открывшемся меню в поле Подставлять значения по строке в: выберите ячейку, в которой содержится значение параметра, использовавшееся при расчете итоговой формулы (F6). В нашем примере надо сослаться на ячейку F2. На самом деле ячейка F6 не ссылается на F2, но зато ячейка F6 ссылается на ячейки В2:В7. А ячейки В2:В7, в свою очередь, ссылаются на F2. То есть, такого рода процедура позволяет анализировать любой параметр, который на каком-то этапе влияет на значение в итоговой формуле (F6).
2. В ячейках F7:F17 появятся значения доходности при уменьшении / увеличении инвестиций ± 10%. Строим график для презентации руководству! 

1. Аналогично обрабатываем данные для получения графика чувствительности внутренней нормы доходности от роста / уменьшения доходов по проекту. Поскольку доходы планируются не столь точно, как расходы, диапазон расширяем до ± 40%

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

И еще, помните, что в результате создания таблицы данных вы получаете формулу массива. Например, в ячейках F7:F17 отражаются формулы в фигурных скобках. Не пытайтесь изменять формулы в отдельно взятых ячейках! Хлопот не оберетесь… 

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

Менеджмент компании «ХимТех ЛТД» проводит анализ чувствительности инвестиционного проекта, предполагающего запуск новой производственной линии. Реализация этого проекта предполагает осуществление первоначальной инвестиции в размере 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%.

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