Как сделать отчет о результатах в Excel?

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

Решение представляет собой таблицу для учета финансов и отчет о движении средств, который строится автоматически. К таблице можно подключить Телеграм-бота и дать доступ сотрудникам.

Каким компаниям будет полезно?

Платежи и поступления денег совершаются вне офиса

За совершение финансовых операций отвечает несколько сотрудников

Существует несколько филиалов и счетов

Поможем настроить таблицу под ваш бизнес и проведем аудит управленческой отчетности бесплатно

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

На самом деле все очень просто. Для настройки нужен компьютер. Настройка состоит из двух частей:

• Настройка таблицы
• Подключение Телеграм-бота

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

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

3. Небольшой обзор На листе «Данные» ведется учет. Информация записывается через диалоговое окно, которое открывается нажатием кнопки «Приход/Расход».

Тут пользователь выбирает или вводит необходимые данные

Шаг 4. Авторизация Наверное вы уже попробовали нажать на кнопку «Приход/расход», и вместо окна, описанного в предыдущем шаге, у вас открылось следующее.

Шаг 5. Обзор отчета В шаге 3 мы рассказали о том как вводить данные. Теперь поговорим о самом главном — отчет о движении денежных средств. Он находится на листе «ДДС» и строится автоматически на основании информации из листа «Данные»

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

Шаг 6. Настройка справочников На листе «Настройки» находятся справочники. Тут вы можете ввести статьи ДДС и направления, актуальные для своей деятельности.

После редактирования справочников нажимайте кнопку сохранения

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

Шаг 8. Настройка прав доступа В правом верхнем углу таблицы находится кнопка

Далее нужно отправить номер телефона, следуя командам чат-бота.

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

В появившемся окне вводим номер телефона в указанном формате и нажимаем кнопку «отправить».

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

Оттуда копируем уникальный ключ и передаем его пользователю бота:

Пользователь, которому передали ключ, заходит в чат-бот и отправляет его сообщением чат-боту

Появляется сообщение о успешной регистрации. Теперь можно полноценно пользоваться сервисом

Как внедрить финансовый учет в свой бизнес?

Финансы — важная часть системы управленческого учета. Правильная организация определяет детализацию анализа деятельности компании в целом

Чек лист внедрения финансового учета:

1. Формируем статьи движения денег (ДДС)
2. Определяем источники возникновения финансовых операций и центры финансовой ответственности
3. Проектируем форму отчета о движении денежных средств и его детализацию
4. Выбираем инструмент, вводим вышеописанные настройки и начинаем пользоваться

Чек лист внедрения финансового учета:

1. Формируем статьи движения денег (ДДС)
2. Определяем источники возникновения финансовых операций и центры финансовой ответственности
3. Проектируем форму отчета о движении денежных средств и его детализацию
4. Выбираем инструмент, вводим вышеописанные настройки и начинаем пользоваться

Как работает финансовый учет?

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

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

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

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

Как сформировать статьи движения денежных средств?

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

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

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

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

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

Что делать если у вас несколько направлений деятельности?

Стандартизация Когда у вас разные виды деятельности удобнее всего стандартизировать статьи ДДС в каждой из них. Таким образом вы вы пользуетесь одними справочниками и так проще всего анализировать деятельность. Если направления слишком разные и не возможно пользоваться на 100% одинаковыми статьями, то на уровне политики учета вводите для каждого направления свои статьи ДДС.

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

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

Источник: https://helpexcel.pro/instruction

10 отчетов за 10 минут в Excel на OLAP-кубах

Типовые отчеты, которые входят в наши модули готовых решений Business Intelligence (BI) позволяют ответить на 99% самых популярных запросов пользователей.

• Но при желании, аналитики или любые другие пользователи могут делать свои отчеты в Excel на OLAP-кубах.
• Благодаря тому, что в основе каждого нашего модуля Бизнес – аналитики (BI) лежит OLAP-куб, подключившись к которому пользователи легко могут построить любой отчет в Excel с помощью Сводных таблиц.
• Также построенные в Excel отчеты на OLAP-кубах можно сохранить, и при повторном открытии они автоматически будут обновляться актуальными данными из корпоративной системы Бизнес – аналитики (BI).

Примеры отчетов, построенных на OLAP–кубах:

Описание отчетов:

1. Отчет по продажам в рублях и единицах:

• период: за текущий год
• показатели: факт продаж
• детализация: столбцы/квартал, месяц
• детализация: строки/каналы продаж, клиенты, бренды, товары

2. Отчет по структуре продаж в рублях и единицах (фильтр):

• период: за текущий год
• показатели: факт продаж, факт продаж в процентах
• детализация: столбцы/квартал, месяц
• детализация: строки/каналы продаж, бренды

3. Отчет по выполнению плана продаж в рублях и единицах (фильтр):

• период: за текущий год
• показатели: план продаж, факт продаж, выполнение плана
• детализация: столбцы/квартал, месяц
• детализация: строки/каналы продаж, клиенты, категории, товары

4. Отчет по приросту продаж в рублях и единицах (фильтр):

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

5. Отчет по приросту продаж по времени в рублях и единицах (фильтр):

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

6. Отчет по дебиторской задолженности:

• период: на текущую дату
• показатели: сумма документа, общая дебиторская задолженность, текущая дебиторская задолженность, просроченная дебиторская задолженность по периодам – до 15 дней, до 30 дней, до 60 дней, до 90 дней, до 180 дней, до 365 дней и более, структура дебиторской задолженности
• детализация: строки/каналы продаж, контрагент, дата оплаты документа, дата документа, документ

7. Отчет динамика дебиторской задолженности:

• период: за текущий год
• показатели: общая дебиторская задолженность, текущая дебиторская задолженность, просроченная дебиторская задолженность, структура дебиторской задолженности
• детализация: строки/год, неделя, контрагент

8. Отчет платежи приход по дате документа:

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

9. Отчет платежи приход по дате платежа:

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

10. Отчет по товарным запасам:

• период: на текущую дату
• показатели: остаток товара, остаточный срок годности в процентах (по DSO), остаток товара по срокам годности — Срок 0%, Срок 10-30%, Срок 40-60%, Срок 70-90%, Срок 99%
• детализация: строки/категория, товар, статус, склад, дата документа прихода

Источник: http://biweb.ru/olap_report_excel.html

Три шага к управленческой отчетности в Excel

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

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

Затраты на поддержку системы в будущем, как и риск ошибок, будут минимальными.

Используйте пошаговые руководства:

Рассмотрим поэтапно, как создается управленческая отчетность в Excel . Забегая вперед, скажем, что для работы нам понадобятся функции ВПР, СУММЕСЛИ, а также с инструменты «Автофильтр», «Проверка данных» и «Сводные таблицы».

Единые справочники для управленческой отчетности в Excel

Управленческая отчетность в Excel начинается с разработки первого блока — справочников. Они помогут эффективно увязать между собой формы для ввода данных и расчета различных показателей, а также позволят оперативно вносить изменения в аналитические разрезы во всей модели.

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

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

Во втором случае вероятность ошибок выше, поэтому лучше создать справочники двух видов:

• общие, с одинаковым набором значений (элементов) для всех бизнес-единиц (например, справочники «Затраты» будут содержать такие значения, как материалы, оплата труда, отчисления, прочие расходы);
• специфичные, содержащие разные наборы значений признака, для каждой бизнес-единицы – свой набор (пример — справочник «Виды продукции»).

Структура общего справочника предельно проста — всего две графы. В одной — порядковый номер строки, в другой – значение аналитики (см. рис. 1).

Рисунок 1 Общий справочник для управленческой отчетности в Excel

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

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

На рисунке 3 приведен пример специфичного справочника видов продуктов для условной группы компаний «Век».

Рисунок 3 Специфичный справочник для управленческой отчетности в Excel

В ее составе две бизнес-единицы – Уральский и Центральный металлургические заводы (УМЗ и ЦМЗ соответственно). Первый завод производит чугун и сляб и передает его на переработку на второй завод (выпускает металлические балки и швеллеры).

В справочнике видов продукции перечислены все производимые продукты (чугун, сляб, балка, швеллер), для каждого из них указано ответственное за его производство предприятие (УМЗ или ЦМЗ), присвоен порядковый номер в целом по ГК «Век» и в рамках бизнес-единицы, назначен специальный код (объединением полей «Наименование бизнес-единицы» и «Порядковый номер продукта в бизнес-единице»). Например, швеллеры выпускает Центральный металлургический завод – соответственно, в строке, где указана эта продукция, приводятся название бизнес-единицы «ЦМЗ», ее номер (2), далее порядковый номер вида «Швеллер» во всем множестве продуктов, производимых на ЦМЗ (их всего два, этому присвоен «2»), и, наконец, код продукта – «ЦМЗ2», где 2 – номер продукта в рамках ЦМЗ.

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

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

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

Формы для ввода данных управленческой отчетности в Excel

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

Аналитические формы – это шаблоны операционных отчетов по определенным показателям (выручка, затраты, объем производства и т. д.) в одном или нескольких аналитических разрезах. В них бизнес-единицы вносят свои данные. Мастер-формы – традиционные финансовые отчеты, например, как отчет о прибылях и убытках. В них данные попадают из аналитических форм.

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

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

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

Имеет смысл предусмотреть фильтрацию форм по признаку «Бизнес-единица». Вот как это можно сделать: создать титульный лист (непосредственно в файле Excel, содержащем аналитически формы). На нем указать отчетный период, версию (если необходимо), а также разместить раскрывающийся список с перечнем бизнес-единиц. Этот список создается из справочника «Бизнес-единицы» так:

• в справочнике выделяется диапазон ячеек с названиями бизнес-единиц, ему присваивается уникальное имя (например, «Спр_БЕ») с помощью команды меню Вставка – Имя – Присвоить;
• далее выполняется команда меню Данные – Проверка. В появившемся диалоговом окне нужно выбрать тип данных «Список», в поле «Источник» указать заданное ранее имя диапазона («=Спр_БЕ»).

Ячейке на титульном листе, где отображается наименование отчитывающейся бизнес-единицы, также можно присвоить имя (к примеру, «БЕ»). Это упростит организацию ссылок на эту ячейку в формах и отчетах. А теперь подробнее о принципах подготовки аналитических и мастер-форм.

Аналитическую форму для ввода данных, в зависимости от потребности в управленческой информации, можно представить в одной или двух аналитиках (например, просто «Расходы» и «Расходы по видам продукции»).

Причем значения этих признаков могут быть как заранее заданы и зафиксированы (назовем этот вариант аналитической формы шаблонной), так и выбираться пользователем из раскрывающегося списка (свободная форма).

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

Настроить такую форму под конкретную бизнес-единицу поможет инструмент «Автофильтр» (Данные – Фильтр – Автофильтр по столбцу «Бизнес-единица»), он скроет пустые строки, а также строки, относящиеся к другим подразделениям.

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

На рисунке 4 приведен пример формы «Отчет о прибыли по продуктам» для ввода фактических данных о понесенных расходах в детализации по видам продукции.

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

В первых колонках свободной аналитической формы – аналитические признаки, но их конкретные значения заранее не задаются. Пользователю предоставляется возможность самому выбрать нужные из раскрывающихся списков (см. рис. 2). Кстати, списки связываются с соответствующими справочниками инструментом «Проверка данных».

Рисунок 2 Свободная аналитическая форма

* Здесь и далее зеленым полем выделены поля для ввода данных бизнес-единиц.

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

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

Например, показатель «Выручка» мастер-формы «Отчет о прибылях и убытках» (ОПУ) рассчитывается по формуле: «=СУММЕСЛИ(’Форма_Прибыль по продуктам’!F:F;B4;’Форма_Прибыль по продуктам’!G : G)». Она позволяет выбрать из формы «Отчет о прибыли по продуктам» (см. рис.

4) все строки, в которых в графе «Статья» (столбец F) содержится слово «Выручка» (это кодовое слово указано в ОПУ в ячейке B4), а затем просуммировать по ним выручку (столбец G, где как раз и указаны значения этого показателя).

Рисунок 4 Шаблонная аналитическая форма

Поскольку формы в бизнес-единицах могут заполнять одновременно несколько человек, стоит предусмотреть возможность совместной работы над одним и тем же файлом (Сервис – Доступ к книге – Разрешить изменять файл нескольким пользователям. На вкладке «Подробнее» можно создать журнал изменений и задать порядок действий в случае противоречивых изменений файла).

Построение управленческого отчета в Excel

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

Главное, что в таких отчетах вручную не меняется ни аналитика (она задана справочниками), ни сами данные (их источник – формы), то есть риск ошибки из-за опечатки или неправильной ссылки отсутствует.

Этот подход обеспечивает гибкость отчетности, ведь пользователь может подготовить на основе всего пары форм практически любые отчеты*.

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

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

Тем более что с их помощью можно создавать как консолидированные отчеты, так и отчеты по одной или нескольким бизнес-единицам.

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

Если планируется задействовать несколько форм (главным образом, необходимо при консолидации), то для создания сводного отчета понадобится сделать дополнительную промежуточную таблицу – «База для консолидации» (в том же файле, где размещаются формы) и с помощью ссылок скопировать в нее содержимое консолидируемых форм. Пример такой базы представлен на рисунке 5.

Для построения консолидированной управленческой отчетности в Excel по всей группе компаний потребуется еще одна дополнительная таблица – «Сводная база» (создается в центральном офисе).

В нее ссылками последовательно переносятся данные из таблиц-баз консолидации всех бизнес-единиц, с запасом в несколько пустых строк (для исключения внутригрупповых оборотов соответствующие суммы отражаются со знаком «минус»). А уже затем к ней применяется инструмент «Сводные таблицы».

Кстати, в эту же базу из справочников можно перенести и дополнительные признаки аналитики, не задействованные в формах, но нужные при анализе (например, «Регион»).

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

Во-вторых, остальные ячейки, как и справочники, и отчеты, полезно защитить от редактирования. Сделать это можно с помощью команды меню Сервис – Защита – Защитить лист.

Но предварительно, чтобы дать возможность вносить значения на заблокированном для правки листе, нужно снять защиту с пустых ячеек, предназначенных для ввода данных (Правка – Перейти – Выделить – Пустые ячейки, а затем Формат – Ячейки– Защита, где снять галочку «Защищаемые ячейки»).

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

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

Этот недостаток сполна окупается минимальными затратами на поддержку при последующих изменениях и минимальным риском ошибок.

Подробнее о создании сводных таблиц для обработки данных читайте в статье Простое решение для безошибочной и быстрой консолидации бюджетов.

Источник: https://www.fd.ru/articles/37467-tri-shaga-k-upravlencheskoy-otchetnosti-v-excel