Платёжный календарь в Excel: как заранее видеть кассовый разрыв

Что должен показывать платёжный календарь

Главная задача календаря — не учёт прошлого, а управление будущей ликвидностью. Он должен отвечать на четыре вопроса: сколько денег доступно сейчас, какие поступления и выплаты ожидаются, какой минимальный остаток получится на горизонте планирования и в какую дату может возникнуть дефицит.

Прибыль здесь не заменяет деньги. Компания может быть прибыльной по P&L, но не иметь средств на зарплату из-за отсрочки оплаты клиента или крупной закупки. Поэтому в календаре важна именно дата движения денег. Счёт, выставленный клиенту сегодня с оплатой через 30 дней, не увеличивает сегодняшний остаток.

Практичный горизонт для малого бизнеса — 4–13 недель. Ближайшие дни планируйте детально, а дальние недели можно укрупнять. По мере приближения даты платежи уточняются.

Структура файла: 4 листа вместо одной таблицы

Рабочую книгу удобно разделить на четыре листа. «Платежи» — единый реестр будущих и фактических операций. «Справочники» — статьи, контрагенты, счета и статусы. «Календарь» — итог по дням или неделям. «Контроль» — минимальный остаток, ближайшая дата риска и крупные платежи.

В реестре используйте колонки: ID, плановая дата, фактическая дата, тип операции, контрагент, статья, счёт, поступление, выплата, статус, вероятность, ответственный и комментарий. Одна строка должна соответствовать одному платежу. Не создавайте отдельный лист для каждого месяца — единый реестр проще фильтровать и агрегировать.

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

Реальные формулы Excel для прогноза остатка

Предположим, на листе «Платежи» плановая дата находится в B, поступление — H, выплата — I, статус — J. На листе «Календарь» дата дня находится в A5, а начальный остаток перед первым днём — в B4.

• Поступления за день: =СУММЕСЛИМН(Платежи!$H:$H;Платежи!$B:$B;$A5;Платежи!$J:$J;"<>Отменено")
• Выплаты за день: =СУММЕСЛИМН(Платежи!$I:$I;Платежи!$B:$B;$A5;Платежи!$J:$J;"<>Отменено")
• Чистый поток: =C5-D5
• Остаток на конец дня: =B4+C5-D5
• Следующий начальный остаток: =E5

Для недельного календаря вместо точного совпадения даты используйте две границы периода в СУММЕСЛИМН: «>=» начала недели и «<=» конца. Если данных много, превратите реестр в таблицу Excel через Ctrl+T и используйте структурированные ссылки.

Контрольная формула должна подтверждать баланс: ОстатокКонец=ОстатокНачало+Поступления-Выплаты. Если расчётный фактический остаток не совпадает с банком и кассой, сначала найдите пропущенную или задвоенную операцию.

Практический пример: календарь на 4 недели

На начало недели у компании 500 000 ₽. В первую неделю ожидается 900 000 ₽ от клиентов. Выплаты: поставщикам 620 000 ₽, зарплата 240 000 ₽ и аренда 80 000 ₽. Чистый поток недели равен 900 000 − 940 000 = −40 000 ₽, остаток — 460 000 ₽.

Во вторую неделю поступления составят 650 000 ₽, выплаты — 780 000 ₽. Остаток снизится ещё на 130 000 ₽ до 330 000 ₽. В третью неделю клиент должен перечислить 700 000 ₽, но дата пока не подтверждена. При этом обязательные выплаты составляют 520 000 ₽.

Если включить ожидаемые 700 000 ₽, прогноз покажет 510 000 ₽ на конец третьей недели. Но подтверждённый сценарий без этой оплаты даст 330 000 − 520 000 = −190 000 ₽. Значит, реальный риск — не «плохая прибыль», а зависимость от одного неподтверждённого поступления. Руководитель видит размер потенциального дефицита заранее и может ускорить оплату, перенести некритичный платёж или подготовить резерв.

В четвёртую неделю при поступлениях 800 000 ₽ и выплатах 450 000 ₽ ликвидность восстанавливается. Поэтому календарь важен именно как последовательность: положительный итог месяца не исключает отрицательного остатка внутри месяца.

Статусы и вероятность: не считайте обещание деньгами

Разделите операции минимум на «План», «Подтверждено», «Оплачено» и «Отменено». Для поступлений особенно важно отличать договорённость от подтверждённой даты. Иначе крупный ожидаемый платёж искусственно скрывает кассовый разрыв.

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

Проценты вероятности используйте осторожно. Поступление 1 млн ₽ с вероятностью 50% не означает, что банк перечислит 500 000 ₽. Взвешенная сумма полезна для портфельного прогноза, но для обязательной зарплаты безопаснее сценарный подход: «оплата пришла / не пришла».

Как подсвечивать кассовый разрыв до нуля

Нулевой остаток — слишком поздний сигнал. Задайте минимальный резерв, например 250 000 ₽. Если прогнозный остаток ниже резерва, показывайте предупреждение; если ниже нуля — критический статус. Формула может выглядеть так: =ЕСЛИ(E5<0;"Критично";ЕСЛИ(E5<$B$2;"Ниже резерва";"Норма")), где B2 содержит установленный резерв.

На листе «Контроль» достаточно четырёх KPI: деньги сейчас, минимальный прогнозный остаток, ближайшая дата ниже резерва и максимальный отрицательный остаток. Последний показатель показывает ориентировочную потребность в дополнительной ликвидности.

Если нужен более широкий анализ фактических потоков по операционной, инвестиционной и финансовой деятельности, используйте соседнее руководство по Cash Flow и ОДДС в Excel.

Как обновлять календарь без ручного хаоса

Ежедневно или несколько раз в неделю переносите исполненные операции в статус «Оплачено», фиксируйте фактическую дату и сверяйте остаток с банком. Затем уточняйте ближайшие 7–14 дней: подтверждайте даты клиентов, счета поставщиков, налоги и зарплату. Дальний горизонт обновляйте менее детально.

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

Для конкретных клиентских счетов и задолженности полезен отдельный учёт оплат в Excel; платёжный календарь должен получать из него ожидаемые даты, а не заменять всю работу с дебиторкой.

Типичные ошибки платёжного календаря

Первая ошибка — считать выставленный счёт гарантированным поступлением. Вторая — смешивать план и факт без статуса. Третья — учитывать перевод между своими счетами как новый денежный поток. Четвёртая — смотреть только итог месяца и пропускать отрицательный остаток внутри периода.

Пятая ошибка — планировать только ближайшую неделю: налоги, аренда или закупка следующего месяца появляются слишком поздно. Шестая — не сверять расчётный остаток с банком. Седьмая — перезаписывать исходные даты без истории, из-за чего невозможно понять качество прогноза.

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

Какие решения принимать по календарю

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

Для руководителя полезно раз в неделю разбирать три списка: крупнейшие выплаты ближайших 14 дней, неподтверждённые поступления и даты минимального остатка. Это переводит таблицу из «учёта цифр» в конкретный план действий.