ПРОЕКТ РАЗРАБОТЧИКОВ HELPEXCEL

Как не тратить время на подготовку управленческих отчетов?

О чем статья?

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

Для кого?

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

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

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

Начнем с основ бизнес-анализа и
работы с Гугл Таблицами

Смотреть таблицы

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

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

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

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

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

cash flow, бизнес-аналитика, отчетность, создание гугл таблицы,

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

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

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

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

выпадающий календарь в гугл таблицах, мой склад, yclients, учет, управленческая отчетность

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

По сути у нас есть два решения:

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

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

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

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

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

Шаг 1

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

Шаг 2

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

как правильно задать внешний вил таблице

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

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

Cчета

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

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

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

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

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

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

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

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

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

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

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

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

Как добавить выпадающий список?

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

Шаг 2. В меню на вкладке данные выбираем пункт "Проверка данных":

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

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

Шаг 4. Получаем результат:

Бесплатная консультация

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

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

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

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

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

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

Самое время открыть таблицу с примером, чтобы разобраться в тонкостях:

https://docs.google.com/spreadsheets/d/1KM0CfrEFRlqzXU0-QzMm3o9rmOl8G9cOZmsbEs5WZq8/edit#gid=507134231

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

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

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

Далее открываем лист Yclients и выделяем аналогичные поля

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

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

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

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

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

Лист "Отчет" - укрупненная модель отчета статьи. Мы специально упростили, чтобы показать основы Гугл Таблиц, которые понадобятся нам для реализации задачи в целом.

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

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

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

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

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

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

=СУММЕСЛИМН( диапазон суммирования; диапазон критериев 1; критерий 1; диапазон критериев 2; критерий 2; … ; диапазон критериев N; критерий N)

Диапазон суммирования

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

Диапазон критериев

Столбец ячеек, который содержит критерии для суммирования

Критерий

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

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

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

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

https://docs.google.com/spreadsheets/d/1KM0CfrEFRlqzXU0-QzMm3o9rmOl8G9cOZmsbEs5WZq8/edit#gid=356953174

Таблица доступна только для просмотра. Сохраните копию на свой Гугл диск.
В меню сверху: Файл -> Создать копию

Заполним простой отчет

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

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

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

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

Шаг 5. Закрываем скобку и нажимаем на клавиатуре ENTER:

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы запретить изменение координаты по столбцам, нужно поставить знак $ перед координатой столбца. (Пример: =$A1)
Чтобы запретить изменение координаты по строкам и столбцам, нужно поставить знак $ перед координатой строки и столбца. (Пример: =$A$1)
Учитывая вышеописанное, формула примет следующий вид:

=СУММЕСЛИМН('Данные'!$D:$D;
'Данные'!$C:$C;$C14;'Данные'!$E:$E;D$13)

Воспользуйтесь формулой только для самопроверки. Важно отработать материал, чтобы понять суть и дальше создавать аналогичные модели для своего бизнеса. Материал несложный, но требуется вникнуть в суть и сделать тестовый пример.
Если у вас возникают сложности, не стесняйтесь задавать нам вопросы в Телеграм-чате:
https://t.me/joinchat/DS-MqEuJ6evMRJa0ey-NQA

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

Шаг 6. Заполняем поле "Итоги" и получаем искомый результат:

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

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

Шаг 1. На листе "Данные" создаем колонку F и называем ее "Год":

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

Шаг 3. Переходим к отчету.
В самой первой ячейке отчета D14 добавляем в формулу в качестве диапазона критериев столбец, где мы проставили формулу ГОД, а в качестве критерия ссылаемся на ячейку, где должен быть выбран год в верхней части отчета. И сразу же работаем с адресацией ячеек, проставляя знак $ в нужных местах, следуя логике описанной выше. Формула примет вид:
=СУММЕСЛИМН('Данные'!$D:$D;
'Данные'!$C:$C;$C14;'Данные'!$E:$E;D$13;
'Данные'!$F:$F;$F$11)

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

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

Шаг 1. Выделяем ячейку, в меню сверху выбираем: Данные -> Проверка данных

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

Шаг 3. В поле "Правила" выбираем пункт "Значения из списка" :

Шаг 4. И в соседнем окне вводим несколько значений через запятую и нажимаем "Сохранить" :

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

Бонус

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

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

Всем, кто проделал вышеизложенный материал, мы абсолютно БЕСПЛАТНО поможем разобраться в его применении на свою бизнес-модель.

Пишите нам в чат, будем помогать разбираться!
https://t.me/joinchat/DS-MqEuJ6evMRJa0ey-NQA

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

=СУММЕСЛИМН1() + СУММЕСЛИМН2() … СУММЕСЛИМНN()

Онлайн диаграмма Ганта GanttPRO позволяет руководителям и командам вести проекты из различных сфер. Быстро. Просто. Эффективно

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

Подробнее

На этом всё!

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

Telegram-канал

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

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

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

Написать в WhatsApp