Выбор шага расчета — месяц, квартал или целый год — это то, с чего обычно начинаешь делать финансовую модель. И это очень важно, потому что выбрав изначально шаг расчета, изменить его впоследствии будет весьма проблематично.
Минэкономразвития в своих методиках подготовки инвестиционных обоснований (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, и так далее по своему вкусу
Я обычно использую в шапках таблиц финансовой модели только заголовок и номера периодов. Ну и если надо сделать итоговые таблицы по годам функцией СУММЕСЛИ(), то пригодится еще и строчка с годами.
Пример в табличке ниже. Там как раз ещё я показал, как в формулах надо использовать число месяцев в периодах, чтобы можно было автоматически переключаться с месяцев на кварталы. Но если присутствует сезонность, то лучше так не делать. Плюс при запланированных месячных или квартальных платежах по кредиту, например, такие переключения тоже до добра не доведут.
Скачать файл-пример календаря для финансовой модели (Яндекс.Диск).
Впервые статья была опубликована на моем сайте "Финансовые модели для оценки проектов"
Источники:
1. Приказ Минэкономразвития России N 741 от 14.12.2013 «Об утверждении методических указаний по подготовке стратегического и комплексного обоснований инвестиционного проекта, а также по оценке инвестиционных проектов, претендующих на финансирование за счет средств Фонда национального благосостояния и (или) пенсионных накоплений, находящихся в доверительном управлении государственной управляющей компании, на возвратной основе»
2. Приказ Минэкономразвития России № 894 от 30.11.2015 «Об утверждении методики оценки эффективности проекта государственно-частного партнерства, проекта муниципально-частного партнерства и определения их сравнительного преимущества»