Настройка шага расчета в финансовой модели (пример)

Выбор шага расчета — месяц, квартал или целый год — это то, с чего обычно начинаешь делать финансовую модель. И это очень важно, потому что выбрав изначально шаг расчета, изменить его впоследствии будет весьма проблематично.

Минэкономразвития в своих методиках подготовки инвестиционных обоснований (1, 2) говорит о том, что шаг прогноза в финансовой модели для инвестиционной стадии должен составлять не более квартала, а для операционной стадии шаг прогноза должен составлять не более одного года. А в случае наличия месячной или квартальной сезонности — не более месяца/квартала соответственно. Те же положения повторены в рекомендациях ФРП и ВЭБ.РФ.

Со своей стороны никому не рекомендую в финансовой модели использовать переменный шаг расчета квартал/месяц. Это первый путь к ошибкам, где-то обязательно формула не дотянется или перетянется и вообще, разные формулы в строке — это всегда плохо. Если нет месячной сезонности можно остановиться и на квартальном шаге, но надо смотреть нет ли каких-либо месячных платежей, например, по кредитам, где квартальный шаг не позволит корректно рассчитать финансовые потоки. Плюс внимательно надо отнестись к последнему кварталу инвестиционной стадии, когда в одном периоде могут сойтись отрицательные потоки по инвестиционной деятельности и первые поступления от операционной. В этом случае потребность в денежных средствах будет рассчитана неверно (будет занижена). Я же всегда предпочитаю месячный шаг расчета, из месяца всегда просто сделать и квартал и год, а вот наоборот уже трудно.

Хочу поделится с вами настраиваемым календарем, в котором можно выбрать любой шаг расчета — месяц, квартал, полугодие или год. Ну и конечно, выбрать дату старта проекта, чтобы далее все даты в зависимости от избранного шага расчета заполнились автоматически.

Так выглядит поле ввода данных (выделено бледно-оранжевым) и блок расчета дат:

В ячейку C3 необходимо ввести дату в формате ДД.ММ.ГГ (28.09.21). При вводе появится подсказка по формату. Кроме того в ячейку добавлена проверка вводимого значения, текст или что-то иное не похожее на дату ввести не получится:

В ячейке C4 надо будет выбрать желаемый шаг расчета из выпадающего списка:

И в зависимости от даты страта проекта и выбранного шага расчета в ячейках C8:C11 будет рассчитан последний день периода — месяца, квартала или года. Который в зависимости от выбора шага при помощи неразлучной комбинации формул ИНДЕКС() и ПОИСКПОЗ() попадет в ячейку C7.

Ну и как вы понимаете, зная первый и последний месяц периода, а также собственно продолжительность периода, выстроить временную линию проекта труда уже не составит.

На минуту остановлюсь на ячейке С13 — в ней рассчитывается сколько месяцев с точностью до 0,1 получается в первом периоде. Далеко не всегда получается так, что проект стартует 1-го числа месяца или квартала. И если какие-то постоянные расходы у вас нормированы помесячно (аренда, зарплата, коммунальные и т.д. и т.п.), то точная доля поможет правильно сделать расчет.

Далее собственно располагается расчет календаря проекта (кликабельно):

Как всё устроено:

D15 — дата старта проекта (ввели в самом начале)
D16 — последний день первого периода — рассчитан автоматически на предыдущем этапе исходя из даты старта и выбранного шага
E15 — первый день второго периода = последний день первого + 1 день (D16+1)
E16 — последний день второго периода — используется значение из D16 (последний день первого периода) и функция ДАТАМЕС(), которая возвращает нам дату отстоящую на заданное число месяцев от выбранной. А число месяцев мы задали на этапе выбора шага (1, 3, 6 или 12). И чтобы у нас получился последний день месяца (или квартала), а не первый, надо еще отнять один день. В следующих месяцах аналогично.
в строках 17 и 18 рассчитывается число дней или месяцев в периоде. Будет нужно, если у вас какие-либо расходы или доходы нормированы в днях или месяцах соответственно
в строках 19:22 приводятся порядковые номера календарных месяцев, полугодий и года. Будет нужно для свёртки в итоговые таблицы, например, функцией СУММЕСЛИ()
в строке 23 просто порядковые номера периодов от 1 до конца горизонта расчета модели
в строке 24 формируется текстовый заголовок для использования в таблицах финансовой модели. В формуле можно изменить разделитель в ММ/ГГ с косой черты на точку, или поменять цифровое отображение месяца на буквенной (исправить ММ на МММ или даже ММММ), вместо кв. написать Q, и так далее по своему вкусу

Я обычно использую в шапках таблиц финансовой модели только заголовок и номера периодов. Ну и если надо сделать итоговые таблицы по годам функцией СУММЕСЛИ(), то пригодится еще и строчка с годами.

Пример в табличке ниже. Там как раз ещё я показал, как в формулах надо использовать число месяцев в периодах, чтобы можно было автоматически переключаться с месяцев на кварталы. Но если присутствует сезонность, то лучше так не делать. Плюс при запланированных месячных или квартальных платежах по кредиту, например, такие переключения тоже до добра не доведут.