Автор – Олег Таран
В кейсе рассказываю, как собрал своими руками систему бюджетирования для своего ИП, которая автоматически формирует отчёт о финансовых результатах, отчёт о движении денег и платёжный календарь.
Задача
Есть маленький бизнес. Нужно построить систему бюджетирования в Google Таблице, которая будет автоматически формировать нужные мне планы и отчёты: о финансовых результатах, о движении денег и платёжный календарь.
Дано
Мозги и Google Таблицы.
Решение
Создадим файл в Google Таблице. Назовём его "Система бюджетирования".
Пусть первый лист будет "Справочник". Его основная задача подставлять данные в нужные ячейки основной базы данных, чтобы не вводить их вручную. Это влияет на скорость и комфорт работы с базой данных и исключает ошибки ввода.
В моём примере справочник сформирован как последовательность столбцов. Под каждым столбцом данные, которые будут подтягиваться в выпадающем списке основной базы данных.
Вторым листом будет лист "Данные" – та самая основная база данных. Выглядит он так, как показано на скриншоте. Почти в каждом столбце стоит проверка данных, которая ссылается либо на нужные диапазоны листа "Справочник", либо на правило "Дата" – разрешает вставлять только значения дат, "Сумма" – только числовые значения. Другими словами нельзя в столбец "Дата" написать текст, можно только дату и т.д.
Лист "Данные" формируем по правилу построения баз данных, который гласит – один столбец – один тип данных. Столбец "Дата" – только даты. Столбец "Статус" – только статусы и т.д. Если нам нужна новая аналитика (столбец), мы просто добавим новый столбец. Единожды сформировав базу данных, мы можем наполнять её строками до бесконечности (пока не исчерпаем возможности таблиц).
Для примера, введу условные данные. Остановлюсь чуть подробнее на столбце "Результат", который имеет два значения: Доход и Расход. По сути, это не что иное, как начисление дохода или расхода (по кассовому методу или по методу начисления, смотря какой метод вы выбрали). Для простоты я выбрал кассовый метод. Он подходит для микро предприятий. Я присваиваю этой операции результат – Доход. Все строки, с присвоенными значениями доход или расход будет автоматически использовать отчёт о финансовых результатах, который строится на данных типа доход и расход и выводит между ними разницу – финансовый результат (прибыль или убыток).
А вот займ относится чисто к финансовой операции и ячейку в столбце "Результат" мы оставим пустой. Это значит, что он не попадёт в отчёт о финансовых результатах.
А куда же он попадёт? Для этого у нас есть следующая аналитика (столбец) под названием "Деньги". У него два значения: – поступления и – платёж. Эта аналитика отправляет данные в отчёт о движении денег и платёжный календарь. Займ – это движение денег. Получение займа – поступление денег на расчётный счёт или в кассу. Возврат займа – платёж с расчётного счёта или из кассы на погашение займа. Пока всё понятно и просто.
Расходные операции вводятся со знаком "минус" и с помощью условного форматирования автоматически подсвечиваются красным цветом. Визуально так удобно. Кому не удобно, можно отключить.
Таким образом, шаг за шагом, мы планируем операции, которые являются либо доходом, расходом и/или поступлением и/или платежом денег. Одна операция может быть доходом и поступлением, или расходом и платежом. А может и не быть. Например, если вы ведёте учёт по методу начисления, то у вас это будут две операции. Первая – вы начислите доход или расход за период, в котором он состоялся (есть документы). Вторая – проведёте движение денег (поступление или платёж), по факту их движения. Надеюсь, понятно написал.
И последняя аналитика в примере – "Источник". Так я назвал технический столбец, который имеет значения "Нал" или "Безнал". Так я смогу учитывать наличные и/или безналичные движения денег. Ещё можно добавить "Банк" и т.д., но не стал усложнять пример.
Ну вот в принципе и всё. Я планирую операции на следующий период, например, месяц или квартал, полугодие, год. Как угодно далеко или близко.
Фактические значения могу записывать новыми строками ниже или же менять статус на существующих. Это как мне удобно. Учёт то мой, веду для себя, чтобы было легко и быстро принимать управленческие решения.
Ну а дальше, я один раз создаю любое количество отчётов с помощью волшебной функции Query. Она будет захватывать весь массив данных на листе "Данные" и показывать только то и так, как я ей сказал (указал операторами). Вот так будет выглядеть срез или отчёт о финансовых результатах. Этот срез располагается на отдельном листе под названием "P&L".
Он работает только от одного запроса, который написан с помощью функции Query в ячейке A8.
Выше я сделал удобную для меня надстройку, в которой могу выбирать нужный мне период, проект и статус (план-факт). Эти выборки учитывает запрос Query и выводит только эти данные. Если в них ничего не стоит, то он выводит всё. Если я укажу конкретный проект или статус "План", то выведет только эти данные, другие опустит.
Чуть ниже обычная формула суммирования для открытого диапазона, которая подбирает данные ниже, сколько бы их там не было, суммирует их и выводит результат в нужную ячейку. Причём к ней я добавил ещё пару удобных функций, они помогают отслеживать пустые ячейки и запрещают выводить результат – это нужно для того, чтобы лист не пестрил не нужными данными.
По такому же принципу построен следующий отчёт и лист "CF" – отчёт о движении денежных средств.
Только надстройка в нём другая, заточенная под денежный поток. Здесь остаток денег на начало периода (Остаток вх.) и на конец периода (Остаток исх.), который рассчитывается как сумма остатка на начало периода и денежного потока. Денежный поток рассчитывается исходя из данных, которые автоматически формирует запрос Query из листа "Данные".
Таким образом, сколько бы у меня не было данных на листе "Данные", мой отчёт не требует вмешательства или перенастройки. Я лишь задаю критерии и получаю результат.
Ну и наконец, третий срез – "PC" (Платёжный календарь). Он абсолютно идентичен отчёту Cash-FLow, за той лишь разницей, что строится он автоматически по дням. С его помощью можно прогнозировать кассовые разрывы внутри месяца.
В отчёте о движении денег этого можно не заметить. Он формирует помесячную динамику. А вот платёжный календарь потому и нужен, что позволяет это увидеть. Мне просто нужно задать временной интервал от 01.02.2020 до 29.02.2020 и всё – остальное за меня сделают запрос и функции с формулами.
Мне остаётся лишь исправно вносить операции. Чем детальнее я буду это делать, тем детальнее будут мои автоматические срезы – отчёты.
Если понадобится дополнительный анализ, например, срез по контрагентам и статьям, то я всегда с помощью нового среза и волшебного запроса Query или комбинации из штатных функций, смогу его построить и пользоваться.
Вот, собственно говоря и всё. Свою задачу система выполняет. Я лично или мой помощник (менеджер) вводим данные, которые выводятся автоматически для принятия решений. В любой момент я могу систему наращивать в ширину и глубину, возможности «Google Таблиц» это позволяют. Таблицу, разумеется, можно переделать под себя, сохранив или улучшив подход.
Буду рад, если кого-то она натолкнёт на мысли о собственном учёте своими руками и он сделает нечто-то подобное или лучше.
Ну а кто захочет внедрить такое же моими руками, заполняйте заявку. Сделаю всё за вас.
Смотрите мои предыдущие кейсы:
Напишите, если есть идеи, что разобрать в следующий раз. Если нет, тоже напишите:)
Автор – Олег Таран, канал и чат в Telegram.