Инструкция: создание управленческих отчётов с помощью «Google Таблиц»

На примере отчёта о движении денежных средств (ДДС) мы покажем, как организовать отчётность в «Google Таблицах» так, чтобы настройка не занимала много времени.

Для кого инструкция

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

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

Все, кто занимается аналитикой или подготовкой управленческих отчетов, знают, что это системная работа. А это означает, что потенциально трудозатраты могут быть минимизированы.

Начнём с основ бизнес-анализа и работы с «Google Таблицами»

Разбираться, как это сделать, будем на примере реального кейса. Рассмотрим некоторые основы формул в «Google Таблицах», организационную составляющую по работе с данными и бизнес-анализа.

Чтобы вникнуть и разобраться, потребуется примерно час.

Чтобы отчётность занимала минимум времени, нужно организовать её как модель. Модель — это статичная форма, которая меняется при изменении параметров или исходных данных. Забегая вперед, сразу покажем, как выглядит конечная цель — создание модели отчёта.

По строкам выводятся движения денег на каждый месяц года. Год регулируется в верхней части выбором из выпадающего списка. Также можно посмотреть в разрезе направления и типы оплаты.

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

Вводные данные

Каждая из нижеперечисленных сущностей является обособленной, за каждую отвечают отдельные пользователи.

Одно юрлицо, два расчётных счёта, касса, наличные и карта физлица.

Приводим в порядок справочники

Статьи движения денег

У нас есть два решения:

  • Простое — во всех направлениях используются одни справочники ДДС.
  • Сложное — для каждого направления вводятся статьи ДДС, свойственные операционной деятельности.

Поскольку все направления смежны, выберем первый вариант и введём единые справочники статей ДДС.

Шаг первый

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

Нормальным количеством будет считаться 30–40 самостоятельных групп. Если вы один раз за период покупали покрышки для авто, то не стоит создавать для этой операции отдельную группу, отнесите текущий расход, например, к группе «Расходы на обслуживание авто».

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

Шаг второй

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

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

Список групп после работы с терминологией превращается в статьи движения денег. Теперь можно проектировать на их основе финансовый учёт и форму отчёта.

Статьи движения денег будут основой для отчета:

Счета

Чтобы выводить в отчёте остатки средств на дату, нужно каждой операции присвоить наименование счета или кассы. В учётных системах, которые перечислены в таблице вводных данных, они добавляются как справочники и присваиваются каждой операции.

Также можно будет анализировать отчёт о движении денег в разрезе определённого счета или кассы.

Подготовка таблицы

Теперь определим структуру таблицы, где будут отчёт и данные, на основе которых она строится. По сценарию решаемого кейса, учёт финансов двух интернет-магазинов ведётся в системе «Мой склад», а для барбершопа и салона красоты используется YClients. Есть возможность делать выгрузки данных за период.

Готовим область для данных из системы «Мой склад»

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

Вот так выглядит часть выписки из системы «Мой склад»:

Вот такой вид примет лист, содержащий данные из системы «Мой склад», который будет служить основой для модели отчетности:

Готовим область для данных из системы YClients

Аналогичные действия необходимо проделать для выгрузки из YClients. Ниже представлена часть выгрузки из системы:

Только важно учесть, что выгрузка из системы «Мой склад» была единой для двух интернет-магазинов, а в YClients для каждой точки есть свой личный кабинет и свои выгрузки. Чтобы разделять финансы разных заведений, добавляем одноименный столбец в начале таблицы:

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

Шаг первый

Выделяем диапазон ячеек, где хотим видеть выпадающий список.

Шаг второй

В меню на вкладке «Данные» выбираем пункт «Проверка данных».

Шаг третий

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

Указываем диапазон на листе справочника, который нам понадобится для аналогичных справочных данных.

Шаг четвёртый

Получаем результат.

Создаём форму отчёта о движении денежных средств

В классическом варианте отчёт о движении денег показывает поступления и события по категориям за выбранный период:

В текущем кейсе мы разберём вариант реализации отчёта в динамике:

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

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

Самое время открыть таблицу с примером, чтобы разобраться в тонкостях. Посмотрите на вид формы отчета, она расположена на листе Cash Flow.

Теперь разберёмся в данных, которые имеются

Смотрим выгрузку из системы «Мой склад»

  1. В выгрузке есть поле «Счет организации». В нашем случае это касса — место, откуда выбывают и куда поступают деньги. Изначально мы определились, что работаем с двумя расчетными счетами в банках, кассой для наличных и картой физлица.
  2. Поле «Статья расходов» — это и есть статья движения денег, о которой мы говорили раньше. Но выгрузка имеет одну особенность: некоторые строки в столбце статьи расходов пустые. Если обратить внимание на поле «Тип документа», то можно заметить, что нет статьи расходов у входящих платежей и приходных ордеров. Поскольку мы работаем над отчётом о движении денег, нам интересны входящие платежи. Входящие платежи будем считать выручкой от реализации.
  3. Поле «Проект» показывает принадлежность транзакции к одному из интернет-магазинов. Для нашей модели оно будет ключевым, поскольку по нему мы будем анализировать отдельные направления деятельности.
  4. Суммы будем брать из полей «Расход» и «Приход». Чтобы брать информацию из правильного поля, зададим соответствующую проверку в формулах.

Открываем лист YСlients и выделяем аналогичные поля

  1. Если обратить внимание на поле «Касса», то можно заметить, что оно содержит только два значения: основная касса и расчетный счет. В дальнейшем расчетный счет привяжем к одному из счетов в справочнике.
  2. Поле «Назначение» содержит статьи движения денег, которые нам нужны.
  3. Первый столбец поля «Заведение» показывает принадлежность транзакций к одному из заведений. Для нашей модели оно будет ключевым, поскольку по нему мы будем анализировать отдельные направления деятельности.
  4. Суммы будем брать из одноименного поля.

Заполняем отчёт о движении денежных средств

Мы подготовили листы для исходных данных, справочника и формы отчета. Следующая задача — создать модель. То есть нужно сделать так, чтобы форма отчета обновлялась автоматически при изменении информации на листах с данными.

Чтобы это реализовать, нужно разобраться в несложных формулах. И первым делом мы проведем обзор необходимых нам формул на примере упрощенных кейсов.

Формула СУММЕСЛИМН для моделей отчётов

Разберем на примере легкого кейса немного теории по «Google Таблицам». Переходите на лист «Данные», где расположен массив с информацией.

Лист «Отчет» — укрупненная модель отчёта статьи. Мы специально упростили, чтобы показать основы «Google Таблиц», которые понадобятся для реализации задачи в целом.

Лист «Данные» содержит информацию учёта финансов. Неважно, вели учет в таблице или скопировали из системы, мы будем использовать формулы, чтобы на их основе сделать отчет.

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

Информация на листе «Данные» представляет собой множество финансовых операций. Каждая из них имеет поля: «Дата», «Статья ДДС», «Сумма».

Начнём с упрощённой версии отчёта и выведем суммы расходов по категориям.

Как работает формула СУММЕСЛИМН

Первым делом расскажем про её вид.

Все аргументы должны разделяться знаком ;

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

Дублируем ссылку на таблицу ниже:

Таблица доступна только для просмотра. Сохраните копию на свой «Google Диск»: Файл → Создать копию.

Шаг первый

Выделяем ячейку D5 таблицы отчета и вводим =СУММЕСЛИМН(.

Шаг второй

Указываем диапазон суммирования. Для этого при активной формуле перемещаемся на лист «Данные» и выделяем столбец D:

Шаг третий

Указываем диапазон критериев. В нашем случае это столбец «Статья ДДС» на листе «Данные»:

Шаг четвёртый

Указываем критерий. Чтобы сделать формулу универсальной, нужно сослаться на наименование статьи ДДС в форме отчета. Таким образом мы ищем указанное значение в таблице с данными и суммируем все значения, равные критерию.

Шаг пятый

Закрываем скобку и нажимаем на клавиатуре «Enter»:

Шаг шестой

Протягиваем формулу и проверяем полученные значения:

Шаг седьмой

Поле «Итого» заполняем формулой СУММ, которая объединяет значение из вышележащих ячеек.

Заполним отчёт посложнее

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

На листе «Данные» столбец В содержит даты. Наша задача — получить из ячейки с датой порядковый номер месяца. Для этого будем использовать одноименную формулу МЕСЯЦ.

Шаг первый

На листе «Данные» правее таблички добавляем столбец и называем его «Месяц».

Шаг второй

В ячейке Е4 вводим формулу =МЕСЯЦ( и в качестве единственного аргумента ссылаемся на ячейку с датой В4. Закрываем скобку, протягиваем формулу вниз и получаем искомые значения.

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

Шаг первый

В ячейке D14 вводим формулу СУММЕСЛИМН по аналогии с простым отчётом, который мы разобрали выше:

Шаг второй

Вводим второй диапазон критериев. Выделяем столбец Е листа «Данные»:

Шаг третий

Задаём в качестве критерия ссылку на форму отчёта:

Шаг четвёртый

Теперь по сценарию предыдущих примеров нужно протянуть формулу, но результаты вычислений будут неверными:

Шаг пятый

Нужно поработать с адресацией ячеек. Ссылка на ячейку имеет формат =A1, где А — координата столбца, 1 — координата строки.

Если написать в любой ячейку формулу вида =А1 и протянуть ее вниз, то можно заметить, что в нижележащих ячейках будет =А2, =А3 и так далее. Чтобы запретить изменение ссылки по строкам, нужно поставить знак $ перед координатой строки. Пример: =A$1.

Чтобы запретить изменение координаты по столбцам, нужно поставить знак $ перед координатой столбца. Пример: =$A1.

Чтобы запретить изменение координаты по строкам и столбцам, нужно поставить знак $ перед координатой строки и столбца. Пример: =$A$1.

Учитывая вышеописанное, формула примет следующий вид: =СУММЕСЛИМН('Данные'!$D:$D;'Данные'!$C:$C;$C14;'Данные'!$E:$E;D$13)

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

Протягиваем формулу на всю таблицу, проверяем результат:

Шаг шестой

Заполняем поле «Итоги» и получаем искомый результат:

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

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

Шаг первый

На листе «Данные» создаем колонку F и называем её «Год».

Шаг второй

В ячейке F4 вводим формулу =ГОД(. Затем ссылаемся на ячейку с датой в той же строке и протягиваем формулу до конца таблицы:

Шаг третий

Переходим к отчету. В самой первой ячейке отчета D14 добавляем в формулу в качестве диапазона критериев столбец, где мы проставили формулу ГОД, а в качестве критерия ссылаемся на ячейку, где должен быть выбран год в верхней части отчета.

И сразу же работаем с адресацией ячеек, проставляя знак $ в нужных местах, следуя логике описанной выше.

Формула примет вид: =СУММЕСЛИМН('Данные'!$D:$D;'Данные'!$C:$C;$C14;'Данные'!$E:$E;D$13; 'Данные'!$F:$F;$F$11 )

Протягиваем её на весь отчет и получаем результат:

Теперь сделаем выпадающий список в ячейке для выбора года.

Шаг первый

Выделяем ячейку, в меню сверху выбираем: «Данные» → «Проверка данных».

Шаг второй

Откроется диалоговое окно:

Шаг третий

В поле «Правила» выбираем пункт «Значения из списка»:

Шаг четвёртый

В соседнем окне вводим несколько значений через запятую и нажимаем «Сохранить»:

Шаг пятый

Теперь данные в отчёте меняются в зависимости от выбранного года:

Бонусы

Выше мы разобрали упрощенный и очень наглядный пример кейса. Для нашей исходной задачи принцип аналогичен.

Всем, кто дошёл до этого этапа, большое спасибо за внимание. Материал будет полезен, если вы составляете управленческие отчеты. Аналогичным образом можно реализовать задачи, связанные с бизнес-анализом.

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

Для закрепления материала завершите кейс самостоятельно. Инструменты и подход к решению аналогичен изложенному материалу. Только учтите, что листа с данными два. В таком случае нужно прописать формулу СУММЕСЛИМН к обоим листам, итоговая формула для отчета примет вид: =СУММЕСЛИМН1() + СУММЕСЛИМН2() … СУММЕСЛИМНN()

А также делимся записью мастер-класса, где мы пошагово рассмотрели похожую задачу.

Заходите к нам на сайт. Там есть бесплатный курс по работе с таблицами, шаблон для финансового учета и еще пара полезных статей.

Успехов!

0
26 комментариев
Написать комментарий...
Вера Вернадская

Почему бы не использовать Power BI?

Ответить
Развернуть ветку
Семен Смирнов

После Google Sheets предложить использовать Power Bi это как после самоката предложить управлять самолетом

Ответить
Развернуть ветку
Артём Медведев
Автор

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

Ответить
Развернуть ветку
Артём Медведев
Автор

Как один из вариантов. Под некоторые задачи подходит. Собственно есть множество решений проблемы с управленческой отчетностью.

Ответить
Развернуть ветку
Stas Gladkikh

Ударьте меня, но структура и логика статей ДДС в вашей таблице - каша из ДДС и ОПУ.
-Финрез - это сколько я заработал за период Х или сколько у меня денег на момент Х?
- План-факт ДДС тоже не самое логичное действо.

Надеюсь вы поймете адекватную критику.
Я аж зарегистрировался ради комментария!

Ответить
Развернуть ветку
Константин Сорокин

Что такое ОПУ?

Ответить
Развернуть ветку
Юрий Б.

Огнетушитель порошковый унифицированный

Ответить
Развернуть ветку
Stas Gladkikh

Константин, да, в целом, то же самое, что и БДР (Термин расшифрует Юрий. У него лучше получается)

Ответить
Развернуть ветку
Артём Медведев
Автор

Хороший и уместный комментарий. Тут есть два основных факта:
1. Это пример правильной организации модели отчетности. Мы объясняем принцип построения, а все тонкости по показателям и формам отчетности создаются уже под особенности бизнеса и под потребности.
2. Финрез это некий остаток в кассе на конец периода. Реальность малого бизнеса такова, что многим этот показатель интересен в первую очередь. Именно поэтому и отчёт ддс немного похож на опиу. Это я могу рассуждать по практике реализации подобных проектов, некоторые заказчики просят выводить кассовую прибыль.

Ответить
Развернуть ветку
Stas Gladkikh

Артем, мысль ясна.
Беда малых (и не только) бизнесменов в том, что кэш в кассе (на основании неких сведенных, прости Илон, табличек) они считаю финрезом (само собой забывая про ДЗ-КЗ, как минимум).
А по факту, если их деятельность разложить, выясняется, что они работают в минус ну и прочее прочее.

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

Спасибо за статью, интересно читать чужие мысли в этом направлении.
Ну и работа была проделана большая.

Ответить
Развернуть ветку
Артём Медведев
Автор

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

Ответить
Развернуть ветку
Vladislav Kurilo

Отличный материал, спасибо!

Ответить
Развернуть ветку
Артём Медведев
Автор

Скоро еще сделаем статью. Много есть полезных инструментов, которые почему то не используются из-за того, что они кажутся сложными.

Ответить
Развернуть ветку
Семен Смирнов

Слишком большая статья для описания режима data validation и функции sumifs в google sheets :)

Ответить
Развернуть ветку
Евгений Круглов

Вот если бы они это сделали на MS Access)))

Ответить
Развернуть ветку
Артём Медведев
Автор

Этот инструмент устарел) мы умеем подключать к таблицам базы данных и интегрировать их с системами учёта. Рекомендую посмотреть запись вебинара, где мы показывали наши кейсы.
А в Акцессе мы умеем делать аналогичные инструменты, с этого начинали два года назад. Когда посмотрите запись вебинара вы поймёте почему сейчас мы делаем проекты только на базе Гугл таблиц.

Вот запись:
https://www.youtube.com/watch?v=xDeC0mqR7kA&t=409s

Ответить
Развернуть ветку
Maxim Bugaev
проекты на базе гугл таблиц....

Как же я отстал от жизни))

Ответить
Развернуть ветку
Артём Медведев
Автор

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

Ответить
Развернуть ветку
Артём Медведев
Автор

Функции действительно элементарные, разделяю точку зрения) но к сожалению ими не пользуются и составляют отчетность очень примитивными способами, в результате тратится много времени. А длинная она потому, что мало знать функции для упрощения процесса. Что бы не тратить много времени на отчеты, нужно правильно организовать процесс с самого начала.

Ответить
Развернуть ветку
Аккаунт удален

Комментарий недоступен

Ответить
Развернуть ветку
Артём Медведев
Автор

Вот ссылка)

Ответить
Развернуть ветку
Аккаунт удален

Комментарий недоступен

Ответить
Развернуть ветку
Артём Медведев
Автор
Ответить
Развернуть ветку
Деньги

Привет! Не могли бы вы заменить скриншоты в материале на более качественные?

Ответить
Развернуть ветку
Артём Медведев
Автор

Заменим) что бы сэкономить время делали скриншоты из аналогичной статьи на нашем сайте. Получились скриншоты скриншотов)

Ответить
Развернуть ветку
Брагин Кирилл

ексель кунг фу

Ответить
Развернуть ветку
23 комментария
Раскрывать всегда