Если основанным источником данных для управленческого учета являются данные бухгалтерского учета, то в качестве источника можно использовать оборотно-сальдовую ведомость (ОСВ).
Расчетный файл, в котором будет вестись управленческий учет, назовем «Бюджет»
Для удобной работы с затратами, быстрого поиска ошибок, анализа отклонений необходимо сделать лист, на котором будет собираться все плановые и фактические расходы по месяцам. Назовем этот лист «Свод»
Лист «Свод» может иметь следующий вид.
Лист «Свод» в файле "Бюджет" должен заполнятся автоматически, если статья управленческого и бухгалтерского учета имеет однозначное соответствие и с применением дополнительных расчетов на отдельном листе, если статью бухгалтерского учета нужно распределить между разными ЦФО или по подстатьям.
Чтобы данные из ОСВ при помощи формул попадали на лист «Свод» в файл «Бюджет» добавляет листы, на которых в течение года будем собирать данные по затратным счетам. Возьмем для примера счета 26 и 44.
Лист по счету 26 с данными ОСВ выглядит так, по счету 44 он выглядит аналогично, только состав статей немого другой.
Первоначально для формирования шаблона по счетам можно использовать данные ОСВ за год, если состав статей не менялся, если статьи новые, то лучше заносить их по мере возникновения расходов.
Блок, начиная со столбца «Е» - это просто данные ОСВ. Для удобства в работе ОСВ из бухгалтерской программы нужно выгрузить в отдельный файл и при помощи формулы ВПР перенести данные в файл «Бюджет»
Выгрузка в Excel данных ОСВ по счету 26 может выглядеть следующим образом
Формула ВПР для переноса данных из ОСВ в файл бюджет
Формула ВПР (в ячейке G3)
=ВПР(E3;[Книга1]Лист1!$A$9:$F$16;6;0)
Чтобы формула работала, наименования статей в файле «Бюджет» и ОСВ должны быть одинаковыми (как в ОСВ). Если в файле «Бюджет» нет статьи, то строку с этой статьей необходимо добавить, наименование лучше скопировать.
В моем примере данные из ОСВ находятся в «Книге1» - это временный файл, его можно не сохранять. Даже если его необходимо сохранить, как источник информации, то связь между книгами, после копирования данных лучше разорвать.
Разрываем вязи так
Ссылка [Книга1]Лист1!$A$9:$F$16 должна содержать весь диапазон, в котором содержаться нужные данные, начиная со столбца с наименованиями. (На рисунке диапазон выделен красным)
=ВПР(E3;[Книга1]Лист1!$A$9:$F$16;6;0) – «6» - это номер столбца в котором находятся данные которые нужно перенести в файл «Бюджет» - суммы расходов за февраль.
«0» - обязательное логическое значение, указывающее на то, что наименования статей в двух списках при поиске должны совпадать полностью.
Блок таблицы с данными из ОСВ от A до D нужен чтобы обеспечить соответствие между статьями бухгалтерского и управленческого учета и автоматически переносить данные с листов «сч. 26», «сч. 44» на лист «Свод».
В столбцы от A до D на листах с данными ОСВ добавляем информацию по статьям и ЦФО управленческого учета. Коды статей, наименования ЦФО на листах с данными ОСВ и листе «Свод» должны быть одинаковыми.
На лист «Свод» для информации и облегчения поиска можно добавить столбец с кодами статей бухгалтерского учета.
После заполнения столбцов, обеспечивающих соответствие статей, можно использовать формулу СУММЕСЛИ. Формулы для разнесения расходов выглядят так
Формула СУММЕСЛИ('сч. 26'!$A$3:$A$10;'свод '!A3;'сч. 26'!$E$3:$E$10) включает в себя следующие части:
'сч. 26'!$A$3:$A$10 - ссылка на диапазон с критериями - кодами статей на листе с данными ОСВ. В данном случае на лист ОСВ с данными по сч. 26. Формула содержит знак доллара, чтобы ее можно было копировать в другую строку.
'свод '!A3 - это ссылка на код стати, по которой нам нужны суммы расходов;
'сч. 26'!$E$3:$E$10 - ссылка на столбец с суммами расходов на листе с ОСВ.
Если расходы по статье отражаются только по одному счету, то СУММЕСЛИ ссылается только на один лист (зеленая строка на рисунке), если расходы распределяются между счетами, т.е. используются данные с двух листов "сч. 26" и "сч. 44", то используется формула, выделенная синим цветом (СУММЕСЛИ суммируются).
Если одной статье управленческого учета соответствует несколько статей бухгалтерского учета, то для расчетов используется дополнительный лист «Распределение»
Данные на лист распределение заносятся вручную. Если распределение требует сложных расчетов, то лучше их проводить в отдельном файле, а в файл "Бюджет" заносить итоговые значения.
На лист "Свод" данные с листа "Распределение" попадают при помощи простых ссылок (более сложные формулы не рекомендую, так как в процессе работы с файлом источник данных сложно искать)
Лист "Распределение" может выглядеть так
Серые строки – это проверочные строки. Общая сумма по статье сравнивается с суммами, распределенными между статьями и ЦФО, если распределено правильное, то результат должен быть равен 0.
После того, как все расходы из ОСВ будут перенесены на лист "Свод", суммы расходов при помощи простых ссылок переносятся на листы с бюджетами по ЦФО.
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке
