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

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

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

Шаблон включает три рабочих листа:

  • БДР – бюджет доходов и расходов;
  • Факт – оборотно-сальдовая ведомость, экспортированная из учетной системы и адаптированная для сбора данных;
  • Условия – вспомогательный лист, задающий правила сопоставления аналитических признаков плановых и фактических данных.

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

Исходная форма бюджета доходов и расходов

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

рисунок 1. Форма бюджета доходов и расходов

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

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

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

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

Адаптация оборотно-сальдовой ведомости

В примере источником фактических данных служат две оборотно-сальдовые ведомости за месяц – по счетам 20 «Основное производство» и 90 «Продажи». Обе сформированы в «1С:Бухгалтерия 8.1». Все что нужно сделать – сохранить их в формате Excel, выделить таблицы, исключая заголовки и итоговые строки, и перенести их на лист «Факт» (см. рис. 2). Стоит отметить, что для предлагаемого решения пригодятся и «оборотки», подготовленные в любой другой бухгалтерской программе. Главное, чтобы у последней присутствовала функция выгрузки (сохранения) данных в Excel.

рисунок 2. Обработка оборотно-сальдовой ведомости

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

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

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

Стоит отметить, что оборотно-сальдовые ведомости по различным счетам могут располагаться как на одном листе файла, так и на различных. В зависимости от их расположения необходимо будет изменить и формулы на листе «БДР».

Перенос данных в бюджетную модель

В соответствии с шаблоном фактические данные об исполнении бюджета доходов и расходов вносятся на место плановых в БДР на момент сбора факта (см. рис. 1). Иначе говоря, по завершении определенного месяца план по нему заменяется фактом. Если в компании есть необходимость в план-факт анализе, форму бюджета понадобится дополнить соответствующими столбцами (факт за месяц, отклонение от плана за месяц в абсолютном и относительном выражении, отклонения ожидаемого факта за год от планового значения) и перенастроить формулы в шаблоне.

Для переноса данных из оборотно-сальдовой ведомости в БДР используется формула вида «=СУММЕСЛИМН(Факт!$I$9:$I$62;Факт!$A$9:$A$62;МЕСЯЦ(G$6);Факт!$B$9:$B$62;$B8;Факт!$C$9:$C$62;БДР!$E8)», где СУММЕСЛИМН – функция Excel, суммирующая ячейки в диапазоне, удовлетворяющие заданным критериям; Факт!$I$9:$I$62 – диапазон суммирования (для дебетовых и кредитовых оборотов номер столбца будет отличаться); Факт!$A$9:$A$62 и МЕСЯЦ(G$6) – первый критерий (из ведомости будут выбраны только данные за январь); Факт!$B$9:$B$62 и $B8 – второй критерий (выборка только по заказу № 100); Факт!$C$9:$C$62 и БДР!$E8 – третий критерий (только статья «Выручка»).

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

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

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

Вложенные файлы

Доступно только подписчикам
  • Шаблон сбора фактических данных из оборотно-сальдовой ведомости и инструкция по работе с ним.rar


Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

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

Живое общение с редакцией

А еще...


Рассылка




© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


  • Мы в соцсетях
×
Зарегистрируйтесь на сайте,
чтобы скачать образец документа

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Зарегистрируйтесь на сайте,
чтобы продолжить чтение статьи

Еще Вы сможете бесплатно:
Скачать надстройку для Excel. Узнайте риск налоговой проверки в вашей компании Прочитать книгу «Запасной финансовый выход» (раздел «Книги»)

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль