Как организовать ДДС в Excel или Google Таблицах

Один журнал операций

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

Справочник статей

Статьи задаются один раз в отдельном списке и выбираются при вводе из выпадающего меню. Это защищает от разночтений вроде «Аренда», «аренда офиса» и «Аренда помещения» в одном отчёте. Типичные группы: поступления от клиентов, прочие поступления, закупки, зарплата и аренда, налоги, обслуживание.

Счета, кассы и карты

Каждый счёт, касса и корпоративная карта — отдельная позиция в справочнике. Тогда отчёт покажет и общий остаток, и остаток по каждому счёту. Это важно, если деньги лежат в разных банках или часть оборота идёт наличными.

Внутренние переводы между своими счетами не должны попадать в поступления и выплаты — иначе обороты задвоятся. Для них заводится отдельный тип операции «перемещение».

План и факт

Плановые операции вносятся в тот же журнал с пометкой «план». Формулы считают плановый и фактический остаток и отклонение по каждой статье. Плановая часть ДДС — это и есть БДДС.

Прогноз остатка и кассовый разрыв

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

Частые ошибки

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

Когда таблицы достаточно

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

Пошаговый пример: от выписки до отчёта

Начните с периода, за который можно получить полную банковскую выписку. Для учебного примера возьмём октябрь и начальный остаток 10 000 BYN. Первого октября покупатель перечислил 8 000 BYN, второго числа оплачена аренда 6 000 BYN, третьего — поставщик на 14 000 BYN. Поступления записываются со знаком плюс, выплаты — со знаком минус. Период в отчёте задаётся датами, а не текстом «октябрь»: это позволяет использовать один и тот же журнал несколько лет.

При таком наборе операций расчёт даёт минус 2 000 BYN: 10 000 плюс 8 000 минус 20 000. Это намеренно проблемный учебный сценарий. Реальная выписка не должна показывать необъяснимый отрицательный остаток, если по счёту нет овердрафта. Если таблица расходится с банком, сначала проверьте полноту платежей, начальный остаток и источник финансирования. Нельзя просто исправить итоговую ячейку вручную, чтобы получить красивую цифру: следующая загрузка снова обнаружит расхождение.

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

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

Каждый платёж должен иметь устойчивый идентификатор. При импорте выписки это может быть номер банковской операции вместе с датой и счётом. Номер строки Excel для этого неудобен: после сортировки он меняется. Идентификатор нужен, чтобы повторная загрузка файла не создала дубликат. Для начала можно проверять составной ключ из счёта, даты, суммы и назначения; совпадения требуют ручного просмотра, потому что два одинаковых платежа в один день бывают настоящими.

Проверьте типы данных до построения отчёта. Дата должна быть датой таблицы, сумма — числом, а не строкой с символом валюты и пробелами. Число, записанное как текст, может отображаться правильно, но не попадать в суммирование. Для импорта из CSV отдельно настройте десятичный разделитель и кодировку. При регулярной обработке удобно сделать эти преобразования в Power Query и сохранять их как последовательность шагов.

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

Как учесть перевод между банком и кассой

Перевод 2 000 BYN из банка в кассу состоит из двух записей: минус 2 000 на банковском счёте и плюс 2 000 в кассе. Для обеих устанавливается тип «Перемещение». В консолидированном ДДС они исключаются из оборотов, а в сверке каждого счёта учитываются. Если отразить только одну сторону, общий остаток изменится без внешнего поступления или выплаты. Если назвать стороны обычными поступлением и расходом, остаток может сойтись, но обороты окажутся завышены.

Комиссию за перевод записывают самостоятельной выплатой: она действительно уменьшает деньги компании. При разных валютах одного знака суммы недостаточно. Храните валюту, сумму в валюте счёта и выбранное правило пересчёта; консолидировать BYN и USD простым сложением нельзя. Учебный файл работает только с BYN и не рассчитывает курсовые разницы. Если нужны несколько валют, сначала определите методику с финансовым специалистом, затем добавляйте формулы.

Регламент: кто вносит данные и кто сверяет

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

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

Демо-файлы и проверка расчётов

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

Скачать учебный отчёт ДДС в Excel (.xlsx)

Скачать демонстрационный платёжный календарь (.xlsx)

Частые вопросы

Можно ли смешивать план и факт?

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

Почему сумма не попадает в СУММЕСЛИМН?

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

Можно ли открыть пример в Google Таблицах?

Да, импортируйте XLSX и проверьте даты и пересчёт. Учебный файл использует обычные SUMIFS и арифметику. После импорта сравните поступления 8 000, выплаты 20 000 и остаток −2 000 BYN с контрольными значениями.

Где посмотреть связанные примеры

Платёжный календарь: описание учебного Excel-файла

Сопоставить ДДС с отчётом о прибыли и убытках

Источники и справка

Microsoft: синтаксис и критерии SUMIFS / СУММЕСЛИМН

Google: SUMIFS в Google Таблицах

IFRS Foundation: IAS 7, классификация денежных потоков (учебный файл не является отчётностью по МСФО)

Формулы: как повторить расчёт в Excel

В листе Operations дата находится в A, тип операции в C, статус в E, подписанная сумма в F. В Report A2 — начало периода, B2 — начало следующего периода, C2 — остаток на начало. Следующие формулы используют русские названия функций и разделитель «;». В файле формулы сохранены в стандартном английском формате XLSX: Excel локализует их при открытии.

=СУММЕСЛИМН(Operations!F2:F1000;Operations!A2:A1000;">="&A2;Operations!A2:A1000;"<"&B2;Operations!C2:C1000;"Поступление";Operations!E2:E1000;"Факт")

Это поступления D2. Для выплат E2 замените «Поступление» на «Выплата» и поставьте минус перед СУММЕСЛИМН: исходные выплаты записаны отрицательными числами. Конечный остаток F2 считается из начального остатка, поступлений и выплат:

=C2+D2-E2

Правый предел периода исключён: так операция первого ноября не попадёт в октябрь даже при наличии времени в ячейке даты. Нельзя менять условие на «меньше или равно первому ноября». Если журнал станет длиннее тысячи строк, одновременно расширьте все диапазоны, иначе новые платежи останутся за пределами отчёта. В Google Таблицах СУММЕСЛИМН может называться SUMIFS в зависимости от настройки языка функций; логика условий остаётся той же.