Учёт доходов и расходов ИП в Excel: журнал, формулы и контроль денег
Строим проверяемый управленческий файл: единый журнал операций, доходы и расходы по месяцам, остатки по счетам, план-факт и дашборд без ручного переписывания итогов.
1. Что должен показывать управленческий учёт ИП
Полезная таблица доходов и расходов должна отвечать не только на вопрос «сколько заработал ИП за месяц». Из неё должно быть понятно, откуда пришли деньги, на что они ушли, какой денежный результат получился, сколько средств осталось на счетах и какие статьи расходов растут быстрее выручки. Тогда файл становится инструментом для решений, а не архивом банковских операций.
Важно сразу разделить управленческий и официальный налоговый учёт. В этой статье речь идёт о внутренней модели для контроля бизнеса. Правила признания доходов и расходов для конкретного налогового режима, КУДиР, деклараций и первичных документов могут отличаться. Не переносите управленческую формулу автоматически в налоговую отчётность без проверки применимых требований.
Для малого бизнеса достаточно дисциплины в трёх вещах: каждая операция записана один раз, категории выбираются из единого справочника, а итоги считаются формулами из исходного журнала. Если месячный результат можно изменить ручной правкой одной итоговой ячейки, модель уже трудно проверить.
2. Рекомендуемая структура книги: пять листов
Практичную книгу удобно разделить на пять листов. «Операции» — единый журнал поступлений и выплат. «Справочники» — категории, счета, проекты и контрагенты. «Месяцы» — доходы, расходы и результат по периодам. «Счета» — расчёт остатков по банку, карте и кассе. «Дашборд» — несколько ключевых показателей и крупнейшие статьи расходов.
В журнале используйте поля: Дата, Тип, Категория, Счёт, Контрагент, Проект или направление, Сумма, Статус и Комментарий. Одна строка — одна операция. Сумму удобно хранить положительным числом, а направление задавать полем «Доход» или «Расход»: так знак не зависит от того, как пользователь ввёл число.
Превратите журнал в таблицу Excel через Ctrl+T и назовите её, например, Операции. Для Типа, Категории и Счёта настройте выпадающие списки. Структурированные ссылки автоматически охватывают новые строки, поэтому отчёт за следующий месяц не потребует вручную расширять диапазоны.
3. Реальные формулы доходов, расходов и результата
Пусть в B1 стоит первый день отчётного месяца. Доход за месяц можно получить формулой =СУММЕСЛИМН(Операции[Сумма];Операции[Тип];"Доход";Операции[Дата];">="&B$1;Операции[Дата];"<"&КОНМЕСЯЦА(B$1;0)+1). Для расходов замените критерий «Доход» на «Расход». Такой диапазон дат надёжнее сравнения с текстовым названием месяца.
Если доход находится в B3, а расход в B4, денежный результат месяца равен =B3-B4. Маржу денежного результата можно вывести как =ЕСЛИОШИБКА((B3-B4)/B3;0). Например, при поступлениях 650 000 ₽ и выплатах 510 000 ₽ результат составляет 140 000 ₽, или 21,54% от поступлений.
Расход по конкретной категории считается дополнительным условием. Например, реклама: =СУММЕСЛИМН(Операции[Сумма];Операции[Тип];"Расход";Операции[Категория];$A7;Операции[Дата];">="&B$1;Операции[Дата];"<"&КОНМЕСЯЦА(B$1;0)+1). Эту формулу можно протянуть по списку категорий и месяцам, получив проверяемую матрицу расходов.
4. Остатки по счетам и сверка с банком
При нескольких счетах общий результат месяца ещё не показывает, где лежат деньги. На листе «Счета» храните стартовый остаток каждого счёта и рассчитывайте поступления и выплаты через СУММЕСЛИМН с дополнительным условием по полю Счёт. Формула логики проста: Конечный остаток = Начальный остаток + Доходы − Расходы.
Например, на расчётном счёте было 180 000 ₽. За месяц на него поступило 500 000 ₽ и с него выплачено 420 000 ₽. Расчётный остаток равен 260 000 ₽. Если банковская выписка показывает 257 500 ₽, расхождение 2 500 ₽ нужно найти в журнале, а не исправлять итог вручную.
Перевод между своими счетами не является доходом или расходом бизнеса. Его лучше хранить отдельным типом «Перевод» с исходным и целевым счётом либо двумя связанными техническими строками, которые исключаются из управленческих доходов и расходов. Иначе перевод 100 000 ₽ с одного собственного счёта на другой искусственно увеличит оборот на 200 000 ₽.
Создать учёт доходов и расходов
Опишите счета, категории, период и нужные отчёты — SmartXLSX соберёт персональный XLSX с формулами и структурой под ваш бизнес.
Открыть инструмент →5. Практический пример месяца с цифрами
Предположим, ИП за сентябрь получил 720 000 ₽ от продаж и ещё 30 000 ₽ возврата займа от контрагента, который в управленческой модели помечен отдельно и не считается операционной выручкой. Операционные поступления для анализа продаж — 720 000 ₽. Расходы: закупка товара 280 000 ₽, реклама 90 000 ₽, логистика 55 000 ₽, сервисы 25 000 ₽, аренда 60 000 ₽ и прочие операционные выплаты 40 000 ₽. Всего — 550 000 ₽.
Денежный операционный результат равен 720 000−550 000=170 000 ₽. Доля рекламы в операционных поступлениях — 90 000/720 000=12,5%, логистики — около 7,64%. Если в августе реклама составляла 8% при сопоставимой выручке, рост до 12,5% уже повод проверить стоимость привлечения и эффективность канала.
Допустим, на начало сентября на всех счетах было 210 000 ₽. Помимо операционных выплат ИП купил оборудование за 120 000 ₽. Тогда конечный денежный остаток с учётом всех денежных потоков: 210 000+750 000−550 000−120 000=290 000 ₽, если возврат займа 30 000 ₽ действительно поступил. Этот пример показывает, почему управленческий результат по основной деятельности и изменение денег на счетах полезно видеть раздельно.
6. План-факт и контроль расходов по категориям
Добавьте к месячному отчёту план. Пусть на рекламу запланировано 70 000 ₽, а фактически потрачено 90 000 ₽. Абсолютное отклонение: =Факт-План, то есть +20 000 ₽. Процентное отклонение: =ЕСЛИ(План=0;"—";(Факт-План)/План), в примере +28,57%. Для расхода положительное отклонение означает перерасход, поэтому подпись показателя должна быть однозначной.
Смотрите не только проценты. Перерасход 100% по статье 2 000 ₽ обычно менее важен, чем +8% по закупкам на 300 000 ₽. На дашборде сначала показывайте крупнейшие отклонения в рублях, затем доли категорий и только потом небольшие процентные изменения.
Для регулярного контроля полезно хранить комментарий к значимому отклонению: причина, действие и ответственный. Например: «Реклама +20 000 ₽ из-за теста нового канала; оценить продажи до 10 октября». Тогда таблица фиксирует не только факт проблемы, но и управленческое решение.
7. Как не смешать доходность бизнеса и движение денег
Таблица поступлений и выплат показывает Cash Flow, но не всегда прибыль. Если в сентябре закуплено товара на 280 000 ₽, часть этого товара может остаться на складе и будет продана позже. Для полноценной оценки прибыли понадобится P&L с себестоимостью именно проданных товаров, а не всех оплаченных закупок месяца.
То же относится к авансам, кредитам, займам, покупке оборудования и личным выводам собственника. Они двигают деньги, но имеют разный экономический смысл. Добавьте поле «Группа потока» или отдельную управленческую категорию, чтобы не считать кредит доходом от бизнеса, а покупку оборудования обычным ежемесячным операционным расходом.
Если главная задача — заранее увидеть нехватку денег, используйте платёжный календарь с будущими датами. Если нужно понять прибыльность — P&L. Журнал ИП может быть исходной базой для обоих отчётов, но итоговые показатели должны отвечать на разные вопросы.
8. Типичные ошибки и контрольные проверки
Первая ошибка — несколько операций в одной строке. Вторая — даты и суммы в текстовом формате. Третья — свободный ввод категорий: «Реклама», «реклама» и «Реклама Яндекс» начинают жить как разные статьи. Четвёртая — удаление старой операции вместо корректирующей записи или документированного исправления. Пятая — включение внутренних переводов в доходы и расходы.
Добавьте контрольные проверки: сумма доходов и расходов по категориям совпадает с общими итогами; нет пустого Типа и Категории; все счета существуют в справочнике; нет отрицательных сумм при модели с отдельным полем Тип; конечный расчётный остаток сверяется с фактическим остатком банка и кассы.
Отдельно ищите дубли после импорта выписки. Простой ключ можно собрать из даты, суммы, счёта и идентификатора операции банка. Если одна банковская строка загружена дважды, красивый дашборд не спасёт отчёт: сначала должна быть чистой исходная база.
9. Ежемесячный регламент и полезный дашборд
В конце месяца загрузите или внесите недостающие операции, разнесите новые категории, сверите остатки со счетами и только после этого закрывайте период. Не переписывайте старые итоги под желаемый результат. Если обнаружена ошибка, исправьте исходную строку или внесите понятную корректировку — все отчёты должны пересчитаться автоматически.
На дашборде достаточно 6–8 сигналов: операционные поступления, выплаты, денежный результат, остаток денег, крупнейшая статья расходов, доля рекламы или другой важной статьи, отклонение от плана и динамика результата за 6–12 месяцев. Каждая карточка должна вести к данным, из которых она рассчитана.
Если файл нужно собрать быстро, используйте инструмент SmartXLSX для учёта доходов и расходов ИП. Для следующего уровня контроля полезно связать этот журнал с бюджетом компании, Cash Flow и платёжным календарём: исходные операции остаются едиными, а каждый отчёт отвечает на свою управленческую задачу.
Частые вопросы
Можно ли вести доходы и расходы ИП по нескольким счетам?
Да. Добавьте поле «Счёт» в журнал и считайте поступления, выплаты и остаток через СУММЕСЛИМН с условием по счёту. Внутренние переводы между своими счетами исключайте из доходов и расходов бизнеса.
Как посчитать результат за месяц в Excel?
Суммируйте доходные и расходные операции за диапазон дат через СУММЕСЛИМН, затем вычтите расходы из доходов. Для управленческого анализа отдельно учитывайте операции, которые двигают деньги, но не являются обычной выручкой или операционным расходом.
Нужно ли делать отдельный лист на каждый месяц?
Нет. Надёжнее вести один непрерывный журнал операций и строить месячные отчёты по диапазонам дат. Так новые периоды не требуют копирования формул и легче сравниваются между собой.
Как учитывать перевод между своими счетами?
Не относите его к доходу или расходу. Используйте отдельный тип «Перевод» и исключайте такие строки из операционных итогов, иначе внутреннее перемещение искусственно увеличит оборот.
Заменяет ли такая таблица бухгалтерский или налоговый учёт?
Нет. Это инструмент управленческого контроля. Требования конкретного налогового режима, КУДиР, деклараций и первичных документов нужно соблюдать отдельно.
Чем эта таблица отличается от P&L и Cash Flow?
Журнал фиксирует операции и может служить исходной базой. Cash Flow объясняет движение денег и остаток, а P&L оценивает доходы, расходы и прибыль по экономическому смыслу периода; эти показатели не обязаны совпадать.