Сколько же денег нужно отдать.

 

Ответ на этот вопрос на самом деле не слишком сложен. Надо только вспомнить, что «Кредит», по сути, такой же проект, как всякий другой и так же характеризуется финансовыми потоками.

Поэтому ничто не мешает, как и для всех остальных проектов, подсчитать 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 млрд, но вычисленные более очевидным способом.

 

 

 

Решение для задания «d».

 

Кажется, что усложнить задачу для автоматического подбора и поддерживаемых проектов 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 млрд.

 

Итоговые файлы.

 

  

Самостоятельный разбор материала.