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

Анализ чувствительности в Excel (анализ «что–если», таблицы данных)

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

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

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

Сумма

Дата

Разовый отток

-1 000 000р.

Инвестиции (отток)

-1 000 000р.

01.янв.08

Рост инвестиций

0%

Инвестиции (отток)

-1 000 000р.

31.янв.08

Разовый приток

250 000р.

Инвестиции (отток)

-1 000 000р.

01.мар.08

Рост доходов

0%

Инвестиции (отток)

-1 000 000р.

01.апр.08

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

Инвестиции (отток)

-1 000 000р.

01.май.08

16,5%

Инвестиции (отток)

-1 000 000р.

01.июн.08

Доходы (притоки)

250 000р.

01.июл.08

Доходы (притоки)

250 000р.

31.окт.10

Доходы (притоки)

250 000р.

01.дек.10

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

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

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

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

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

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

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

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

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

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

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

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

Как в экселе слелать график чуствителтности?

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

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

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

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

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