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

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

Таким образом, мы хотим смоделировать несколько вариантов сценариев. Необходимо узнать каким будет срок окупаемости нашего проекта при Ставке дисконтирования, равной 9%, 12% и 14% соответственно. Безусловно, мы можем попеременно вручную подставить эти три значения и понять, каков будет результат – это будет достаточно рутинно, трудоемко и с большим риском совершить ошибку. Excel предлагает немного автоматизировать этот процесс.

После того, как модель создана и определен входной и выходной параметр (именно выходной параметр, а не параметры – Excel позволяет варьировать только 1 выходным параметром) для проведения сценарного анализа, необходимо проделать следующие действия:

1. Необходимо зайти в пункт меню «Tools» -> «Scenarios».
2. Откроется окно Менеджера сценариев, при помощи которого мы сможем описать все возможные сценарии развития.
   
3. Используя кнопку, добавляем новые сценарии. В первое поле «Scenario name» — вводим краткое описание сценария, например «Ставка дисконтирования — 9%». Далее, во втором поле «Changing cells» — вводим непосредственно адрес ячейки, куда Excel подставит необходимое значение – входной параметр модели. Перебор сценариев будет осуществляться именно через эту ячейку. Комментарий можно смело проигнорировать.
4. После того, как мы определили входной параметр, необходимо задать значение для этого параметра в рамках данного сценария. В новой форме «Scenario Values» Excel предлагает определить это значение для указанной ранее ячейки (например, если мы хотим подставить 9%, то просто вводим 0,1).

Описанную процедуру необходимо проделать столько раз, сколько сценариев предполагается вами. После того, как вы описали все необходимые сценарии, можно построить сводный отчет, который собственно и покажет все возможные варианты. Для этого вам необходимо снова войти в «Менеджер сценариев» («Tools» -> «Scenarios») и там нажать на кнопку.

Перед вами откроется форма «Scenario Summary», используя которую вы сможете указать выходной параметр и тип вывода итогового отчета. Наиболее удобная форма – это первый тип отчета, выводимый в табличном виде на отдельный лист («Scenario summary»). При нажатии на кнопку «OK», Excel автоматически сгенерирует новый лист «Scenario Summary», на котором сформирует таблицу с результатами итеративной подстановки значений.

Описанные выше шаги относились к подстановке одного параметра: один параметра на входе, один параметр на выходе для каждого сценария.

Excel также позволяет создавать сценарии, описывая несколько входных параметров. Выходной параметр, будет по-прежнему единственным. Для этого в окне добавления сценариев (пункт 3 из описания выше) необходимо в поле «Changing cells» ввести несколько адресов ячеек (например, используя клавишу Ctrl).

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

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

Описанная последовательность действий построения сводной формы для целей сценарного анализа крайне проста, и с ней сможет справиться даже начинающий пользователь. Если вы владеете основами VBA для Excel, то вы сможете написать небольшую пользовательскую процедуру, с помощью которой будет возможен анализ множества входных и выходных параметров. Использую цикл, можно итеративно подставлять значения во входные параметры и извлекать в отдельный массив выходные. Особенно удобно будет анализировать получившийся массив данных, если построить график, например, по двум осям которого будут входные параметры (например, Ставка дисконтирования и Темп роста выручки), а на пересечении будет отражено значение соответствующего совокупного NPV.