Личный кейс: как построить отчёт о прибылях и убытках, если есть только банковская выписка?

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

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

Банковская выписка.

Заходим в личный кабинет интернет-банка. Находим раздел "Выписка". Устанавливаем необходимый период с – по. Скачиваем в формате Excel. Документ улетает на диск компьютера. Открываем его и видим примерно следующее:

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

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

Теперь самое время привести данные в формат, пригодный для анализа. Применяем принцип "базы данных" – один столбец отвечает за одну аналитику, другой за другую. Два столбца "списания" и "зачисления" нам не нужны, вместо двух сделаем один под названием "сумма". Для этого ставим фильтр на ячейку "Зачисления", убираем галочку с "пустые" и нажимаем "ок".

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

Поменяем название столбца "списание" на "сумма". Отлично. Теперь у нас данные в формате "базы данных". Лишних столбцов нет. Поступления со знаком плюс, списания со знаком минус.

Двигаем дальше. Добавим ещё одну аналитику "результат". Она будет иметь только два значения "Расход" или "Доход". Для этого в ячейке E1 напишем название "результат" и под ней введёт логическую формулу =ЕСЛИ(C2<0;"Расход";"Доход"), как показано на скриншоте ниже. Она посмотрит в столбец "сумма" и если там отрицательное значение, поставит в ячейку значение "Расход", в противном случае "Доход". Протянем формулу вниз, до самого конца базы данных.

Следующий шаг – нам нужно распределить все операции по статьям "дохода" и "расхода". Для этого создадим ещё один лист в книге, дадим ему название "справочник", а первому листу "база данных". На листе "справочник" в ячейке A1 напишем "Контрагент", а в ячейке B1 "Статья". Это будут названия аналитик в нашем справочнике.

Теперь, самое время подтянуть всех контрагентов из базы данных в наш справочник. Для этого используем формулу Unique – она обратится к листу "база данных" и автоматически подтянет уникальные значения всех контрагентов в наш справочник. Формула выглядит так =UNIQUE('База данных'!B2:B) – если словами, то мы даём команду, обратиться в лист "база данных", в диапазон данных B2:B, потому что именно в этом диапазоне данных хранятся данных о всех контрагентах и вывести только уникальные значения.

Начиная с B2 потому что нам не нужен заголовок, заканчивая просто B (без цифры), потому что это открытый диапазон, он отвечает за то, чтобы следить за новыми данными, если они будут появляться, чтобы моментально их отразить в нужном месте, в нашем случае – в справочнике. Результат действия функции показан на скриншоте ниже. Мы получили список всех контрагентов.

Теперь нужно напротив каждого контрагента написать статью, к которой он относится. Здесь нужно поработать ручками. Не думаю, что этих контрагентов будет десятки тысяч, как правило их до 100, поэтому можно потерпеть. Отлично. Теперь ваш справочник готов. Можно перейти на лист "база данных" и добавить ещё одну аналитику "статья". В ячейку F1 (под заголовком "статья") введём ещё одну мега крутую функцию ВПР. Формула будет выглядеть так =ВПР(B2;'Справочник'!$A$2:$B;2;0). Если словами, то мы даём команду посмотреть в нужную ячейку столбца "контрагент" на листе "база данных", найти в листе "справочник" такое же значение и подставить в ячейку "статья" наименование статьи, которая соответствует этому контрагенту. Протяните формулу до низа базы данных. Нужные значения появятся.

Теперь, можно сказать, что база данных готова. Выглядеть она будет так.

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

Создадим ещё один лист для отчёта о прибылях и убытках. Назовём его "Отчёт". Для красоты эксперимента, в ячейке A1 дадим ему название "Отчёт о прибылях и убытках".

Теперь самое сложное и поэтому интересное. Используем мощнейшую функцию QUERY.

Выглядит она так =QUERY('База данных'!A:F;"";1). Если словами, то мы даём команду зайти на лист "база данных", выбрать все данные в массив, сделать с ними то, что указано в кавычках после точки с запятой и показать результат с заголовком – последний аргумент единица, означает – вывести заголовок. Я ввёл необходимые команды в кавычках и функция мгновенно выдала результат. Смотрите на скриншот ниже – это произошло автоматически. Сформировался нужный мне массив данных в той последовательности и структуре, которая мне нужна.

Теперь осталась последняя манипуляция. Посчитать финансовый результат. Для этого используем зарезервированную строку выше массива данных. Я делаю так, потому что внизу могут автоматически добавляться новые данные и чтобы каждый раз не следить за этим, лучше один раз сделать это наверху и забыть. Введём простую формулу суммирования =СУММ(C6:C) и протянем её вправо до конца таблицы. Результаты появляются мгновенно.

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

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

Не ругайте меня за то, что я не раскрыл то, что было в кавычках, потому что это отдельная и большая тема. В ней можно разобраться самостоятельно с помощью справки google или пройти соответствующие онлайн-курсы или заказать готовые шаблоны у тех, кто их делает на заказ. Да, я тоже это делаю, можно обращаться.

Напишите, пожалуйста, был ли вам полезен мой кейс. Планировать ли в дальнейшем здесь время от времени описывать новые свои кейсы или разбирать ваши. Пишите!

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