ПРОЕКТ РАЗРАБОТЧИКОВ БИЗНЕС АНАЛИТИКИ

Создание управленческих отчетов в Google Таблицах

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

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

Финансовый менеджер;

Коммерческий директор;

Бухгалтер.

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

Не хотите разбираться в непонятных формулах?

Настройте учет с помощью бесплатных шаблонов.

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

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

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

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

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

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

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

Приводим в порядок справочники
Статьи движения денег

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

Простое — во всех направлениях используются одни справочники ДДС.

Сложное — для каждого направления вводятся статьи ДДС, свойственные операционной деятельности.

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

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

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

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

Шаг первый

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

Например, так, как показано на рисунке, группировать нельзя:

Шаг второй

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

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

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

Счета

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

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

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

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

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

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

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

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

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

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

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

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

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

Шаг первый

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

Шаг второй

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

Шаг третий

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

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

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

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

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

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

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

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

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

Теперь разберёмся в данных,
которые имеются.
Выгрузка из системы «Мой склад»

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

Поле «Статья расходов» — это и есть статья движения денег, о которой мы говорили раньше. Но выгрузка имеет одну особенность: некоторые строки в столбце статьи расходов пустые. Если обратить внимание на поле «Тип документа», то можно заметить, что нет статьи расходов у входящих платежей и приходных ордеров. Поскольку мы работаем над отчетом о движении денег, нам интересны входящие платежи. Входящие платежи будем считать выручкой от реализации.

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

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

Открываем лист YClients и выделяем аналогичные поля.

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

Поле «Назначение» содержит статьи движения денег, которые нам нужны.

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

Суммы будем брать из одноименного поля.

Сэкономьте время и организуйте учет в компании на индивидуальной консультации с нашим специалистом.

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

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

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

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

Разберем на примере легкого кейса немного теории по 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)

Воспользуйтесь формулой только для самопроверки.

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

Учитывая вышеописанное, формула примет следующий вид: =СУММЕСЛИМН('Данные'!$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 )

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

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

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

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

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

Шаг первый

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

Шаг второй

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

Шаг третий

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

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

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

Шаг пятый

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

Базовые решения для вашего бизнеса
Удобные системы оптимизации с учетом особенностей вашей отрасли.
На этом все!

Еще больше полезных материалов вы можете найти в нашем Telegram-канале:

Хотите обсудить свой проект?

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

Для обсуждения вашей задачи напишите нам в WhatsApp. Проведем аудит и предложим решение.