Черная пятница! Скидка на подписку 40% 8 (800) 505-89-86
Журнал "Финансовый директор"

Как оценить эффективность инвестиционного проекта в условиях неопределенности с помощью Excel

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

Как задать исходные данные для оценки инвестиционного проекта с несколькими сценариями развития

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

Исходным данным модели – денежным потокам проекта по периодам и сценариям (на рисунке 1 диапазон ячеек «F4:M7») присвоено имя «Исх_Данные». Так с ними будет проще работать. Чтобы задать имя диапазону, нужно его выделить, а затем в поле «Имя» (находится слева от строки ввода формул и показывает название выбранной ячейки) ввести желаемое наименование и нажать клавишу ввода. Следует задавать уникальные имена и не использовать в них пробелов.

Кстати, в модели присвоены специальные имена и другим элементам. «М_1» – имя ячейки (оно может быть любым), содержащей наименование сценария «В». Оно используется для поиска совпадений и вывода соответствующих строк из массива данных «Исх_Данные». А столбец с кодами сценариев («Е4:Е7») получил название «Сценарии».

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

Рисунок 1. Сценарии развития инвестиционного проекта

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

Предположим, что в рассматриваемом примере аналитики компании решили, что сценарий «В» вероятнее сценария «А», а «А» в свою очередь – правдоподобнее «С». В сумме вероятности всех вариантов должны составлять единицу.

Для корректности дальнейших расчетов сценарии нужно расположить в порядке убывания вероятности их наступления. Иными словами, если аналитик считает, что сценарий «B» более вероятен, чем сценарий «С», соответствующий ему денежный поток должен быть размещен над потоком, относящимся к варианту «С». Проще всего такую операцию выполнить с помощью специального режима, который называется «Формулы массива». Необходимо выделить диапазон значений «F12:M12», написать в окне ввода формул выражение:

«=СМЕЩ(Исх_Данные;ПОИСКПОЗ(М_1;Сценарии;0)-1;0;1;ЧИСЛСТОЛБ(Исх_Данные))»,

после чего нажать сочетание клавиш Ctrl + Shift + Enter (см. рисунок 2. Размещение сценариев по убыванию вероятностей реализации).

Рисунок 2. Размещение сценариев по убыванию вероятностей реализации

Формула выполняется синхронно на всем диапазоне, где она была задана. Оператор «СМЕЩ()» подставляет нужную строку из исходной таблицы, номер которой соответствует порядковому номеру сценария в колонке «Сценарии». Аналогичную операцию нужно провести для остальных базовых сценариев. Чтобы облегчить выбор нужного сценария, можно установить для ячеек «Е12:Е15» правило проверки допустимых значений. Для этого нужно выделить необходимый диапазон, затем в группе меню «Данные» на вкладке «Работа с данными» выбрать команду «Проверка данных». В появившемся окне следует выбрать тип данных «Список», в поле источника данных записать «=Сценарии», нажать «ОК» (см. рисунок 3. Выбор сценария из списка).

Рисунок 3. Выбор сценария из списка

Это действие создаст раскрывающийся список сценариев (см. рисунок 4. Раскрывающийся список сценариев).

Рисунок 4. Раскрывающийся список сценариев

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

Рисунок 5. Расчет достоверных денежных эквивалентов

В формуле используются значения вероятностей из диапазона ячеек «С12:С15». Это переменные, изменяя которые можно повлиять на величину итогового показателя достоверного денежного эквивалента.

Задача заключается в поиске такого распределения вероятностей, которое находит максимум достоверных денежных эквивалентов в рамках наложенных ограничений. Такую операцию проще всего выполнить при помощи надстройки «Поиск решения», которая расположена в разделе меню «Данные». Если она изначально неактивна, ее следует подключить. Включение производится в меню «Параметры Excel» – «Надстройки» (см. рисунок 6. Настройка параметров Excel и рисунок 7. Активация надстройки «Поиск решения»).

Рисунок 6. Настройка параметров Excel

Рисунок 7. Активация надстройки «Поиск решения»

Для корректной работы надстройки в ее параметрах нужно выбрать режим «Квадратичная оценка» (см. рисунок 8. Параметры надстройки «Поиск решения»).

Рисунок 8. Параметры надстройки «Поиск решения»

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

Рисунок 9. Модель оптимизации достоверных денежных эквивалентов

В окно целевой функции нужно поставить сумму достоверных денежных эквивалентов за все периоды (ячейка «N19»), а предельным значением установить ее максимум. Оптимальное значение ищется за счет изменения ячеек «С12:С15», на которые накладывается набор дополнительных ограничений, а именно:

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

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

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

Как рассчитать чистую приведенную стоимость проекта в условиях неопределенности с помощью Excel

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

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

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

"Финансовый директор" - единственный профессиональный ресурс по управлению финансами компании. Материалы подготовлены финансовыми директорами и экспертами. Пройдите короткую регистрацию и получите доступ

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
или войти через соц сети
Зарегистрироваться

Зарегистрируйтесь или войдите на сайт:

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
или войдите через соц.сети
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
Простите, что прерываем чтение

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
или войдите через соц сети
Зарегистрироваться
×

Мы подобрали для вас книги:

Как решать нерешаемые задачи, посмотрев проблему с другой стороны Управление финансовой службой по KPI Как не потерять на налогах: проверенные способы
Скачать бесплатно Скачать бесплатно Скачать бесплатно
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.