Метод линейной оптимизации

Кейс Инвестиционная компания

1

Инвестиционная компания «Карло и сыновья» рассматривает одиннадцать инвестиционных проектов, закодированных буквами греческого алфавита:

Финансовые потоки проекта Альфа Финансовые потоки проекта Бета Финансовые потоки проекта Гамма Финансовые потоки проекта Дельта Финансовые потоки проекта Эпсилон Финансовые потоки проекта Дзета Финансовые потоки проекта Эта Финансовые потоки проекта Тета Финансовые потоки проекта Иота Финансовые потоки проекта Каппа Финансовые потоки проекта Ламбда

Компания желает вложить капитал в наиболее выгодные проекты. Горизонт планирования - 10 лет. Для 10-го года финансовые потоки включают не только, собственно, cash flow 10-го года, но и терминальную стоимость проекта. Возможности инвестирования: сейчас - до 9,5 млрд. рублей 1 год - до 6,2 млрд. рублей 2 год - до 5,7 млрд. рублей Далее эта группа проектов может финансироваться только за счет внутренних ресурсов, т.е. суммарный номинальный финансовый поток должен быть положительным.
Годовые финансовые показатели: коэффициент инфляции - 3% скорость устойчивого роста - 2% ставка дисконтирования для отрасли - 23%
Условия инвестирования: - любой проект требует инвестиций сейчас и еще в течение от 1 до 3 лет; - любой проект либо финансируется полностью, либо не финансируется совсем;

скрепка
  1. Выберите проекты, которые следует финансировать. Все ли финансовые средства могут быть инвестированы? Что будет, если стремиться инвестировать как можно больше имеющихся средств?
  2. Как можно увеличить суммарный NPV для данной группы проектов?

2

Расчет финансовых характеристик. Основные формулы.

Финансовый поток (Сash Flow) вычисляется как:

CF n = EBIT n * ( 1 - tax ) + Амортизация - CAPEX n - Δ NWC n

где CF n - Cash Flow - финансовый поток. Суммарный финансовый поток за период n , положительный или отрицательный;
tax - ставка налога на прибыль;
CAPEX n - Capital Expenditure - капитальные затраты за период n ;
Δ NWC n - Net Working Capital - увеличение Чистого Оборотного Капитала за период n ;
EBIT n - прибыль до уплаты налогов и процентов по кредитам;

EBIT = EBITDA - Depreciation

где Depreciation - Амортизация;
EBITDA - Earnings before interest, taxes, depreciation and amortization - Валовая прибыль

EBITDA = Revenue - ( COGS + S&GA )

где Revenue - Выручка;
COGS + S&GA - операционные, рекламные и административные расходы.

NOPAT = EBIT - Налоги = EBIT * ( 1 - tax )

Чистая Приведенная Стоимость инвестиций (Net Present Value) рассчитывается по формуле:

NPV = I 0 + CF 1 / ( 1 + k ) 1 + CF 2 / ( 1 + k ) 2 + CF 3 / ( 1 + k ) 3 + ... + CF n / ( 1 + k ) n

где I 0 - начальная Инвестиция;
k - ставка дисконтирования, обычно принимается равной средневзвешенной стоимости капитала WACC ( Weighted Average Cost of Capital)
CF n - финансовый поток за период номер n, положительный или отрицательный
либо с помощью стандартной функции MS Excel:

NPV = ЧПС ( k; CF 1 … CF n ) + I 0

Внутренняя Ставка Доходности инвестиций (Internal Rate of Return) рассчитывается как ставка дисконтирования, при которой NPV обращается в ноль.
IRR вычисляется из уравнения:

0 = I 0 + CF 1 / ( 1 + IRR ) 1 + CF 2 / ( 1 + IRR ) 2 + CF 3 / ( 1 + IRR ) 3 + ... + CF n / ( 1 + IRR ) n

либо с помощью стандартной функции MS Excel:

IRR = ВСД ( CF 0 … CF n ; [предполагаемое IRR] )

Модифицированная Внутренняя Ставка Доходности инвестиций (Modified Internal Rate of Return) рассчитывается путем уравнивания всех инвестиций (отрицательных CF ), дисконтированных по ставке дисконтирования k к начальному моменту ( PV ), и всех доходов (положительных CF ), приведенных к конечному периоду, по ставке реинвестирования f (Net Terminal Value), деленных на ( 1 + MIRR ) n ( где n – номер последнего финансового периода в проекте).

MIRR = ( - NTV / PV ) 1/n - 1

где NTV = Формула для вычисления NTV = ( CF 1 * ( 1 + f ) n-1 + CF 2 * ( 1 + f ) n-2 +...+ CF n-1 * ( 1 + f ) 1 + CF n

PV = Формула для вычисления PV = I 0 + I 1 / ( 1 + k ) 1 + I 2 / ( 1 + k ) 2 +...+ I n / ( 1 + k ) n

либо с помощью стандартной функции MS Excel:

MIRR = МВСД ( CF 0 … CF n ; k; f)

MIRR призвана устранить два недостатка критерия IRR: неоднозначность вычисления и подразумеваемое реинвестирование денежных потоков по ставке k.

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

NPV = I 0 + CF 1 / ( 1 + k ) 1 + CF 2 / ( 1 + k ) 2 + CF 3 / ( 1 + k ) 3 +...+ CF j / ( 1 + k ) j +...+ CF ∞ / ( 1 + k ) ∞

В действительности никакой необходимости в суммировании бесконечного числа слагаемых нет, так как можно вычислить сумму геометрической прогрессии по стандартной формуле S = b 1 / ( 1 - q ). Правда, сами финансовые потоки в этой перспективе тоже не определены. Их оценка обычно принимается равной финансовому потоку на горизонте планирования, умноженному на ( 1 + g ) m (где g так называемая скорость устойчивого роста (из Dividend Discount Model) отрасли или экономики страны в целом, а m - степень номера года после горизонта планирования). В результате терминальную стоимость проекта (Terminal Value, TV ), отнесенную ко времени после n -го года, можно рассчитать по формулам, как:

NPV = I 0 + CF 1 / ( 1 + k ) 1 + CF 2 / ( 1 + k ) 2 + CF 3 / ( 1 + k ) 3 +...+ CF n / ( 1 + k ) n + CF n * ( 1 + g ) / ( 1 + k ) n+1 + CF n * ( 1 + g ) 2 / ( 1 + k ) n+2 + CF n * ( 1 + g ) 3 / ( 1 + k ) n+3 +...

или

NPV = I 0 + CF 1 / ( 1 + k ) 1 + CF 2 / ( 1 + k ) 2 + CF 3 / ( 1 + k ) 3 +...+ CF n / ( 1 + k ) n + CF n / ( 1 + k ) n * ( 1 + g ) / ( 1 + k) * ( 1 + ( 1 + g )/ ( 1 + k ) + ( 1 + g ) 2 / ( 1 + k ) 2 + ( 1 + g ) 3 / ( 1 + k ) 3 +...)

обозначим

TV = CF n / ( 1 + k ) n * ( 1 + g ) / ( 1 + k) * ( 1 + ( 1 + g )/ ( 1 + k ) + ( 1 + g ) 2 / ( 1 + k ) 2 + ( 1 + g ) 3 / ( 1 + k ) 3 +...) = CF n * ( 1 + g )/ ( k - g )

или

TV = CF n * ( 1 + g ) / ( WACC - g )

где g - скорость устойчивого роста за пределами прогнозируемого периода;
k - ставка дисконтирования;
WACC - средневзвешенная стоимость капитала.

Тогда

NPV = I 0 + CF 1 / ( 1 + k ) 1 + CF 2 / ( 1 + k ) 2 + CF 3 / ( 1 + k ) 3 +...+ CF n / ( 1 + k ) n + TV n / ( 1 + k ) n

Так как терминальную стоимость проекта TV n принято определять как

TV n = CF n * ( 1 + g ) / ( k – g ) , то оказывается, что коэффициент дисконтирования ее соответствует последнему году прогнозируемого периода n, а не n+1. Из-за этого в перечне финансовых потоков последний финансовый поток обычно объединяют с терминальной стоимостью

NPV = I 0 + CF 1 / ( 1 + k ) 1 + CF 2 / ( 1 + k ) 2 + CF 3 / ( 1 + k ) 3 +...+ (CF n + TV n ) / ( 1 + k ) n

или

NPV = I 0 + CF 1 / ( 1 + k ) 1 + CF 2 / ( 1 + k ) 2 + CF 3 / ( 1 + k ) 3 +...+ ( CF n * ( 1 + k )/ ( k - g ))/ ( 1 + k ) n

3

Решение кейса: Инвестиционная компания

Организация данных и формулы

Организация данных на листе Excel
Формулы в рабочей книге Инвестиционная компания
Результат произвольгого выбора трех проектов
Данные в Поиске решения
Полученное решение

4

Можно ли подкрепить выбор оптимального решения другими показателями?

Найдем внутреннюю ставку доходности IRR и рентабельность инвестиций ROI для каждого проекта:

Показатели IRR и ROI для всех проектов
Как видно, ни один из этих показателей не является окончательным критерием выбора проектов.

5

Как можно увеличить суммарный NPV для данной группы проектов?

Возможные варианты:

1. Перенос неиспользуемых остатков средств (а только в начальном периоде их почти 2 млрд.: 9,5-7,6 = 1,9) на следующие периоды.

2. Перенос начала некоторых ресурсоемких проектов на более позднее время (1-й, 2-й, 3-й год).

3. Заимствование денег извне в те периоды, когда они требуются для финансирования дополнительных проектов.

скрепка
  1. Рассмотрите возможность переноса остатков бюджетных средств на следующий год. Изменится ли выбор проектов для финансирования? Как изменится максимальная суммарная чистая приведенная стоимость?
  2. Рассмотрите возможность сдвига проектов во времени начала. Есть ли выигрыш по NPV?
  3. Рассмотрите возможность использования кредита по ставке 30% в объеме до примерно 6 млрд. для поддержки проекта Дзета. Как рассчитать необходимые заемные средства и график выплаты долга с процентами?
  4. Рассмотрите возможность использования кредита по ставке 30% в объеме до 10 млрд на произвольные проекты. Как изменится максимальная суммарная чистая приведенная стоимость? Каков оптимальный план инвестиций и заимствования?

Перенос неиспользуемых остатков средств

Как один из вариантов анализа «что-если» запланируем перенос остатков денежных средств на следующий год.
При этом имеется ввиду, что недорасходованные средства не могут лежать без движения, а тоже приносят доход. Доходность может быть разной, в зависимости от условий.
Но без конкретизации условий обычно предполагают, что эти деньги будут в течение года работать под 23% годовых. Поэтому строку со старыми бюджетными ограничениями B18:M18 скопируем ниже, в строку 19 , а вместо нее будем вычислять новый бюджет с учетом переноса денежных средств.
Перенос остатков средств в формуле
В С18 – нулевом периоде – переносить нечего, поэтому пишем =С19. В D18 напишем =D19 + (C18-C17)*(1+$O$3) , т.е. то, что может выделить компания в текущем периоде плюс остаток с предыдущего периода, умноженный на годовой рост.
Будем считать, что эта формула справедлива для всех остальных лет, поскольку начиная с 4-го года проектам все равно инвестиции не требуются.
Задание «Поиску решения» менять не требуется: ограничение останется прежним
$C$17:SM$17 >= $C$18:$M$18
После оптимизации оказывается, что будет поддержано на один проект меньше - 5. Но суммарная NPV группы проектов вырастет до 6,66 млрд.

Результат оптимизации при переносе остатков средств
Еще раз отметим, что существенное улучшение результата, это, конечно, аргумент в пользу переноса денежных средств. Но не факт, что в компании такое предложение будет поддержано.

Решение о сдвиге финансирования проектов на 1 - 2 года

Здесь приведен вариант со сдвигом на 1 и 2 года. При этом набор проектов дублируется и сдвигается на 1-й – 11-й годы (голубые проекты), и еще раз дублируется и сдвигается на 2-й – 12-й годы (зеленые проекты).
Теперь выбор проектов будет проводиться среди 33 проектов, а не среди 11. Собственно, все остальное не меняется. Так как при сдвиге в будущее NPV проектов уменьшается (сравните, например, NPV проекта Альфа в обычном, голубом и зеленом вариантах), то максимизация NPV группы проектов будет приводить к преимущественному выбору более раннего варианта проектов.
Дополнительная необходимая поправка модели нужна для того, чтобы запретить выбор нескольких вариантов одного и того же проекта. Поэтому в оранжевой области справа считаются сумма переменных для каждого проекта по трем вариантам ( =Q5+Q16+Q27 для проекта Альфа и т. д.).
Организация данных при сдвиге проектов В «Поиске решения» потребуем, чтобы оранжевые ячейки X5:X15 были меньше или равны 1.
Данные в поиске решения при сдвиге проектов
В этом решении тоже получается более высокий суммарный NPV=6.3 и поддерживаются 6 проектов, но 2 проекта: Тета и Иота предложено начать через 2 года.

Использование кредитных средств

Для учета возможного кредита добавим в начальный вариант задачи в строке 16 комментарий «Кредит», а в столбцы «C-M» будем писать заимствованные и отданные деньги.
Для удобства расчета модифицируем формулу расчета номинального финансового потока в строке 17. Нужно учесть в общем потоке кредитные средства. В ячейке С17 допишем в формулу
=СУММПРОИЗВ($O$5:$O$15;C5:C15)
финансовый поток по кредиту:
=СУММПРОИЗВ($O$5:$O$15;C5:C15)+С16
и протянем формулу до M17 . Заменим «вручную» переменную O10 проекта Дзета на 1 – решаем финансировать проект.
Учет кредита при финансировании проектов
Очевидно, что в первом году до баланса не хватает 1,4 млрд., во втором 3,80, а в третьем 0,84 млрд. Добавим и эти числа в строку «Кредит».
Обратите внимание, что дефицит исчез и наши финансовые потоки сбалансировались с бюджетными ограничениями. Дальше кредит нужно возвращать. Очевидно, что свободные 1,99 млрд. 3-его года нужно сразу отдать.
Как отдавать кредит
Но сколько отдать в следующем году? Как убедиться, что кредит возвращен и проценты по нему полностью уплачены?
Сколько же денег нужно отдать?
Ответ на этот вопрос на самом деле не слишком сложен. Надо только вспомнить, что «Кредит», по сути, такой же проект, как всякий другой и так же характеризуется финансовыми потоками.
Вычисление того, сколько денег придется отдать при выплате кредита
Поэтому ничто не мешает, как и для всех остальных проектов, подсчитать IRR кредита.
Для этого просто потянем формулу в столбце «S» еще на строку вниз. В ячейке S16 возникнет ошибка, так как мы не вернули даже тех денег, которые взяли.
Подберите число в ячейке G16 так, чтобы IRR кредита оказалась равной 30%. Получим результат, показанный на картинке. Т.е. заняв 6,04 млрд руб., мы должны будем вернуть банку 13,16 млрд.
Выгодно ли это?
На самом деле, пока это неизвестно. Потому что после выбора проекта Дзета к суммарной NPV≈4,92 прибавилось 2,1 млрд и… все. А кредит? Как учесть расходы по обслуживанию кредита в целевой функции?
Фактически нужно добавить к целевой функции величину NPV кредита. Т.е. сначала протянем формулу NPV в столбце R до R16 , а затем добавим ее к целевой функции О17 :
=СУММПРОИЗВ(O5:O15;R5:R15)+R16 .
Вот теперь все учтено и все равно суммарная NPV=6,115 млрд, существенно выше, чем была без проекта Дзета и кредита. Если имеются сомнения в осмысленности операции вычисления NPV для проекта «Кредит», можно вычислить NPV для всей группы проектов, финансовую сводку потоков которой содержит строка 17 «Номинальные ФП».
Выгоден кредит?
В самом деле, мы в этой строке аккуратно учли все финансовые потоки, которыми оперируем, управляя данной группой проектов, включая финансовые потоки по кредиту. Так что, протянув формулу NPV столбца R до ячейки R17 , получим сразу NPV суперпроекта из 7 обычных проектов и кредитного проекта. Видно, что это все те же 6,115 млрд, но вычисленные более очевидным способом.

А если привлечь больше денег?

Кажется, что усложнить задачу для автоматического подбора и поддерживаемых проектов O5:O15, и занимаемых и выплачиваемых сумм С16:M16 – очень легко. Ведь, по существу, все есть – есть живая книга Excel, которая пересчитывает все необходимые параметры при изменении плана. Однако именно в данном случае имеется проблема. Функции ЧПС() и ВСД(), которые мы используем при расчете изменяющегося NPV кредита и выплаченных процентов, не годятся для оптимизации, так как не являются математическими и выдают ошибки при некорректных параметрах финансовых потоков. Их надо как-то заменить. Для их замены придется ввести прямой расчет суммарной NPV группы проектов + кредит по ставке 23% (строка 20) и прямой расчет NPV кредита по ставке 30% (строка 21).
Оптимизация при учете кредита. Формулы
Ячейка O20 будет новой целевой функцией, а ячейка O21 должна оставаться равной нулю, чтобы процент по кредиту составил заданные 30%, так как IRR – это ставка дисконтирования, при которой NPV обращается в ноль. И если финансовые потоки по кредиту удастся подобрать так, чтобы O21 стала нулем, то это и будет означать использование кредита по заданной ставке B22. Старый расчет с помощью функций ЧПС() и ВСД() в этом случае можно не убирать, поскольку он в оптимизации больше не используется.
Параметры поиска решения при учете кредита
Результат оптимизации:
Найденное оптимальное решение
Объясните результат, интерпретируя финансовые потоки по кредиту. Учитывая, что отрицательный финансовый поток означает движение денег от нас, первые три потока по кредиту: -7.3, -1.9, -1.4 демонстрируют план одолжить кому-то деньги, а не занять. На самом деле вполне ожидаемый результат. Ведь без специальных оговорок возможность занять деньги под 30% годовых показывает возможность одолжить деньги под 30%. А у нас 8 проектов из 11 имеют более низкую доходность. Так что «Поиск решения» советует поддержать 3 проекта с доходностью 30% и выше, а оставшиеся деньги отдать в рост. Что выглядит разумно. В этой ситуации «Поиску решения» надо как-то запретить одалживать деньги. Для этого добавим два условия:
Параметры поиска решения при учете запрещения самим одалживать деньги
Т.е., во-первых, требуем, чтобы ячейки G16:M16 <=0 – с четвертого года нам точно не нужны деньги для инвестиций, так что мы можем их только отдавать. И, во-вторых, имеет смысл потребовать, чтобы в первые два-три периода времени ФП по кредиту были больше или равны нулю – можем занимать, но не давать в кредит свободные средства.
Найденное оптимальное решение при запрете одалживать деньги
Тогда получим требуемое решение с суммарным кредитом 8,45 млрд.