Личный кейс: как я избавился от кассовых разрывов с помощью платёжного календаря

Автор – Олег Таран.

В кейсе делюсь опытом создания платёжного календаря в Google Таблице, который помогает прогнозировать кассовые разрывы и оперативно управлять финансовыми потоками.

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

Статистика за прошлые периоды, Google Таблица и мозги.

Создадим файл в бесплатной Google Таблице. Назовём его Кейс «Платёжный календарь».

«Лист1» переименуем в «Операции» и создадим в нём ключевые столбцы: «Дата», «Статья», «Наименование», «Сумма», «Контрагент», «Статус».

На столбец «Дата» назначим проверку данных, чтобы по щелчку выпадал встроенный календарь и нам было удобно заносить даты (не набирать их на клавиатуре, а выбирать щелчком в календаре).

Тоже самое проделаем с «Статья». Свяжем её с нужным диапазоном из листа «Справочник», который создадим.

Кто не знает, связка будет работать так, как показано на скриншоте ниже:

А по календарю так:

Без этого, конечно, можно обойтись, но если вы хотите автоматизацию ввода, со всеми вытекающими – экономия времени, исключение ошибок ввода и удовольствие…, тогда желательно один раз над этим потрудиться, чтобы потом бесконечно долго наслаждаться.

Продолжаем колдовать. Сейчас я нахожусь на «Сумма». «Неписаные» правила управленческого и финансового учёта гласят, что платежи (расходы) должны всегда указываться со знаком минус. В идеале, если они ещё и будут подсвечены другим цветом, например – красным. Давайте это сделаем с помощью условного форматирования:

Теперь введём первую планируемую операцию. Выглядеть она будет так, как на скриншоте:

Набирать информацию очень просто, исключена возможность сделать ошибку, потому что у нас где нужно стоит защита от дурака в виде проверки данных. Теперь нам остаётся только набрать планируемые поступления и платежи на следующий период (например, месяц). По сути это всё, что нужно будет делать регулярно. Если вы это делаете сами. Или же поручить ответственному исполнителю, который будет заносить данные в листе «Операции», который (исполнитель и лист) может быть вообще в отдельном файле на другом компе, а комп в другой стране и т.д.

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

Создадим заготовку листа «Платёжный календарь». Она выглядит так:

Наверху, что-то наподобие дашборда для управления исходными данными. Например, «Статус». С помощью проверки данных мы выбираем из ниспадающего списка статус «План» или «Факт» или оставляем ячейку пустой (тогда берутся все значения). Таким образом, мы будем нашим будущим функциям говорить, какие данные должны попасть в выборку – плановые, фактические или все. Дальше – период. Мы можем с помощью встроенных календарей задать диапазон с – по, например, с 01.02.2020 по 28.02.2020 года. Так наши формулы поймут, данные за какой период нам нужны и возьмут именно их.

Дальше у меня идёт «Выручка». Выручку можно заносить в листе «Операции» также, как и платежи, но бывает в общепите, когда ты не знаешь как будет поступать выручка по дням, в течение следующего месяца. Тогда можно поступить следующим образом. Взять сумму выручки предыдущего месяца или любое другое прогнозное значение и поставить его в эту ячейку. Именно это значение выручки будет браться за основу и участвовать в расчётах. Чуть позже вы увидите, как я реализовал схему.

Спускаемся ещё ниже. Заготовим следующие позиции: «Остаток на начало», «Остаток на конец», «Поступления» и «Платежи».

Ещё ниже «Денежный поток» и «Денежный поток нарастающим итогом».

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

В ячейки «Поступления» введу формулу, как на скрине ниже. Она смотрит на ячейку 13-й строки и если она пустая, то ничего не выводит, а если там будет значение, то возьмёт выручку из ячейки B3 и поделит её на количество дней в планируемом месяце, в которых будут, как мы предположили в листе «Операции», происходить платёжные операции. Так посчитается выручка этого дня и значение ляжет в ячейку. При таком подходе, у меня не будет дней в календаре с пустыми значениями. Такой подход не засоряет экран длинными таблицами с пустыми ячейками – компактность.

В ячейке Платежи будет похожая формула. Она соберёт сумму всех платежей за этот день, которые появятся автоматически из листа «Операции».

С остатками я думаю и так всё понятно. Остаток на начало периода стартовый (ячейка подкрашена) мы вводим вручную. Его мы знаем на начало месяца, ну или в крайнем случае – прогнозируем. Остаток на конец месяца суммирует остаток на начало, поступления и платежи. Мы помним, что платежи у нас заносятся со знаком минус.

Формула денежного потока тоже простая – это сумма поступлений и платежей за день, а нарастающим итогом суммирует текущий день и предыдущий.

Теперь самое главное. С помощью запроса Query мы будем работать с массивом данных на листе "Операции". Делаем запрос. Его синтаксис не так прост, как у функций. В рамках этого кейса не буду о нём говорить. Запрос будет заходить на лист "Операции", брать весь массив данных и выводить нужные нам значения по дням периода. Мы ничего не вводим. Запрос сам это делает за нас! Сейчас я его введу и данные сами заполнятся на листе "Платёжный календарь". Посмотрите, как это выглядит на видео или на скриншоте ниже:

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

Давайте подытожим.

Наша система состоит из трёх листов. Лист "Справочник" – в него заносим переменные значения, всегда можно дополнить новыми. Лист "Операции" – он берёт значения из справочника и в нём мы отражаем все планируемые и фактические операции нашего бизнеса. Лист "Платёжный календарь" – в нём задаём только исходные параметры по статусу, периоду и начальному остатку, всё остальное он считает сам. Мы лишь анализируем сценарий и если необходимо, вносим корректировки в листе "Операции". Корректировки могут быть по датам, их можно двигать в случае необходимости, чтобы убрать кассовый разрыв, который появится, если платежей будет больше суммы поступлений и остатка. Например, как показано на скрине:

Мы видим отрицательные остатки, которые подсвечиваются красным. Это значит, что при таком сценарии будет кассовый разрыв. Нам нужно убрать или передвинуть платежи в эти дни, либо увеличить поступления. В платёжном календаре можно строить различные сценарии. Для этого их нужно добавить в лист "Операции" и в дашборд листа "Платёжный календарь".

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

Календарь простой, как видите, но гибкий – конструктор. Его можно использовать, как шаблон в своём бизнесе. Он поможет проиграть различные сценарии, спрогнозировать кассовый разрыв и подготовиться к нему должным образом. Можно сделать и другие сложные варианты, но я сторонник простоты. Она эффективнее и надёжнее.

Ну вот, собственного говоря и всё. Заполните заявку, если хотите такой шаблон для себя.

Смотрите мои предыдущие кейсы:

1. Как построить отчёт о прибылях и убытках, если есть только банковская выписка

2. Как я ушёл от платных сервисов и собрал своими руками простую систему учёта заявок и сделок

Напишите, если есть идеи, что разобрать в следующий раз. Если нет, тоже напишите:)

Автор – Олег Таран, канал и чат в Telegram.