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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дата добавления: 2015-07-15