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

Рубрика: 7. Полезняшки Excel

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

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

Скачать статью в формате Word2007 Анализ чувствительности

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

Комментарии: 10 комментариев

Очень познавательно и интересно. Но меня не спасет к сожалению.

меня тоже

Отличная статья! Просто и понятно написано! Здорово!

Не здорово. анализ чувствительности проводиться к нескольким параметрам. Пример: определяются доли статьи затрат в общем объеме затрат, выбираются несколько статей наиболее значимых и по изменению параметров (например цены на сырье) статьи проводиться анализ. в выводах — реальные предположения, пример: при росте цен на сырье (энергоносители, или снижения цен на реализцию продукции и т.д.) на 2,3,4,5,10 (кому как угодно) и т.д. % риск не получения доходов увеличивается на конкретную сумму …

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

Построение дерева решений — см. заметку Управление в условиях риска и неопределенности.
Методу Монте-Карло у меня посвящено несколько заметок: Использование метода Монте-Карло для расчета риска, Моделирование методом Монте-Карло в Crystal Ball для Excel.
А вот анализ сценариев я пока не планирую изучать… Почему? См. критику этого метода в заметке Нассим Талеб. О секретах устойчивости.

Дополнение от 8 июня 2014. Я всё же прочитал Матс Линдгрен, Ханс Бандхольд. Сценарное планирование: связь между будущим и стратегией

Масон, для короткой статьи вполне достойно. Описанные вами инструменты мы сделали в надстройке к Excel — http://www.eds-plus.ru/eva.html. В ней реализованы:
— Анализ чувствительности (с ранжированием наиболее значимых параметров, как выше писал Дмитрий);
— Сценарный подход (c вычислением VaR — value at risk);
— Метод Монте-Карло;
— Подбор распределения.

Смотрите, версия на сайте лежит бесплатная.

Спасибо!

Сергей, вопрос по поводу анализа «что если» и конкретно таблиц данных: это работает видимо только если все данные и расчеты находятся в пределах одного листа (я перенесла таблицы данных в Вашем примере на новый лист, ссылки перенаправила правильно, но выходит ошибка «невозможность ссылки на ячейку ввода») Как решить этот вопрос? У меня финансовая модель инвест проекта на 15 разных листах в одной книге и формулы на всех листах ссылаются друг на друга. Как провести анализ чувствительности? С помощью таблиц данных не получается пока, только макросами. Помогите пожалуйста!!

Ирина, у меня тоже не получилось перенести Таблицу данных на другой лист, но… мне кажется в этом нет особой нужды. Вы пишите, что «…модель инвест проекта на 15 разных листах в одной книге и формулы на всех листах ссылаются друг на друга», но… вы ведь делаете анализ чувствительности в каждой конкретной Таблице данных только по одной из этих формул. Вот и расположите Таблицу данных на том же листе, что и анализируемая формула. Таким образом, у Вас будет много Таблиц данных на разных листах, а собрать вместе Вы их сможете либо с помощью ссылок на эти Таблицы, либо путем построения на одном листе диаграмм, ссылающихся на эти Таблицы… Успехов!


Прокомментировать