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

Для исследования чувствительности проекта к изменениям различных условий можно использовать такое средство MS Excel как сценарии. Будем исследовать влияние изменения цены, объема сбыта и уровня инфляции на эффективность проекта (в частности, на NPV, IRR и срок окупаемости с учетом инфляции).

К сожалению, на листе Сбыт не представлены значения прогноза инфляции, NPV, IRR и срока окупаемости, а все изменяемые ячейки сценария должны находиться на активном листе (т.е. на листе Сбыт). Чтобы значение прогноза инфляции присутствовало на листе, перейдите на лист Общие данные, вырежьте диапазон А11:С11 (кнопка Вырезать ) и вставьте в любом месте на листе Сбыт (теперь ячейка с именем Инфляция располагается на листе Сбыт). Чтобы не перемещать ячейки результатов NPV, IRR и срок окупаемости РР с листа Эффект на лист Сбыт, создадим копии этих ячеек на листе Сбыт. В ячейку В10 введите формулу =Эффект!B21, в В11 введите =Эффект!B27, в В12 введите =Эффект!B18. Ячейке В10 присвойте имя NPV, В11 – IRR, В12 – PP.

Перейдите на лист Сбыт и выполните команду СервисàСценарии… В окне Диспетчер сценариев нажмите кнопку Добавить… В окне Добавление сценария задайте Название сценария Базовый сценарий, Изменяемые ячейки C3;B6:F6;Инфляция и нажмите кнопку ОК. В окне Значения ячеек сценария указаны текущие значения изменяемых ячеек. Нажмите кнопку ОК.

Добавьте сценарий Рост цен на 10% (Изменяемые ячейки С3, Цена =130*1,1 или 143). Самостоятельно создайте сценарий Падение цен на 10%.

Добавьте сценарий Рост продаж на 10% (Изменяемые ячейки B6:F6, $B$6 =50000*1,1 или 55000, $С$6 =300000*1,1 или 330000 и т.д.). Самостоятельно создайте сценарии Падение продаж на 10%, Инфляция 15% и Инфляция 20%.

Для создания отчета по сценариям нажмите кнопку Отчет… в окне Диспетчер сценариев. В окне Отчет по сценарию укажите Тип отчета структура, Ячейки результата =$B$10:$B$12. Нажмите кнопку ОК. Проанализируйте созданный рабочий лист Структура сценария. Убедитесь, что наибольшее влияние на эффективность проекта имеет цена.

Самостоятельно создайте отчет по сценариям с использованием сводной таблицы.

E Чтобы получить удовлетворительный отчет с использованием сводной таблицы, необходимо предварительно удалить Базовый сценарий. После создания листа Сводная таблица по сценарию отредактируйте сводную таблицу. Для полей IRR и РР измените параметры поля – щелкните правой кнопкой мыши в нужном поле, выберите команду Параметры поля… и в окне Вычисление поля сводной таблицы задайте Операция Сумма, после чего нажмите кнопку ОК. Отформатируйте сводную таблицу так, чтобы она приняла следующий вид.

Рис. 5. Результирующий вид листа Сводная таблица по сценарию

E Обратите внимание, что в сводной таблице результаты рассчитаны при совокупном действии сценариев в отличие от листа Структура сценария.

Чтобы определить минимальную цену, при которой проект будет оставаться рентабельным, перейдите на лист Сбыт и выполните команду СервисàПодбор параметра… В окне Подбор параметра задайте значения согласно рис. 6. Нажмите кнопку ОК. Убедитесь, что при цене 127,53 руб. NPV=0 руб., IRR=15% (т.е. IRR равен ставке дисконта), а РР больше 5 лет.

Рис. 6. Окно Подбор параметра