text
Открыть свежий номер журнала>> / Подписка за 22 308 13 990 руб! >> ✆ 8 (800) 550-07-98
Журнал

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы получить итоговое значение чистой приведенной стоимости проекта, можно воспользоваться функциями Excel.

Достоверный денежный эквивалент, найденный с помощью надстройки «Поиск решения», не учитывает риски общесистемного характера. Поэтому денежный поток должен быть обязательно скорректирован с учетом ставки дисконтирования (для рассматриваемого примера это 12%). Следует отметить, что эта категория рисков должна учитываться именно на финальном этапе, а не при прогнозировании денежных потоков базовых вариантов. В противном случае они будут учтены дважды, что повлечет за собой ошибочное снижение общей величины чистой приведенной стоимости проекта.

Затем необходимо ввести сумму начальных инвестиций (в примере первоначальные вложения составили 300 тыс. руб.). Теперь полученных параметров достаточно для определения показателей эффективности проекта.

Определить чистую приведенную стоимость проекта можно двумя способами: воспользоваться специальной функцией «ЧПС()» в Excel либо дисконтировать достоверные денежные эквиваленты отдельно для каждого периода (см. рисунок 5. Расчет достоверных денежных эквивалентов) и посчитать их сумму.

Для расчета чистой приведенной стоимости проекта с помощью встроенной функции Excel необходимо к сумме инвестиций, взятой со знаком минус, прибавить значение функции «ЧПС()». Аргументами этой функции служат ставка дисконтирования и вычисленные ранее достоверные денежные эквиваленты.

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

Рекомендации по теме

Школа

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

Записаться

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

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

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

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

Это бесплатно и займет меньше минуты!

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

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

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

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

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

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

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

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