Если бюджет ведется в Excel, то необходимость заполнения фактических расходов является ежемесячной задачей.
Источниками для заполнения фактических расходов являются данные бухгалтерского учета, а именно обороты счетов 26 и 44. В нашем расчетном файле листы с данными счетов 26 и 44 будут иметь одинаковый вид. Такой.
Список статей БУ должен быть максимально полным, чтобы избежать ежемесячного добавления новых строк (статей). Данные за месяц добавляются в таблицу при помощи функции ВПР.
Как это делается. Создаем новый файл Excel. В бухгалтерской программе, как правило 1С, формируем ОСВ за соответствующий месяц и копируем в отчет в этот пустой файл. Если в отчете нарушились форматы не важно, нам важны только статьи и суммы.
Статьи в расчетном файле, назовем его Бюджет, должны назваться точно так же как и в ОСВ, поэтому первоначально максимально полный список можно скопировать из ОСВ за предыдущий год, так как в годовой ОСВ используются практически все статьи.
Если на предприятии много статей бухгалтерского учета и справочники часто меняются, то лучше выводить статьи с кодом. В моей практике были случаи, когда названия статьей одинаковые, а коды разные, в итоге расходы разносятся на две разные статьи и отследить это трудно, особенно, когда документ создается копированием.
Перенос данных будет выглядеть следующим образом.
Лист с формулами более крупно
Файл с ОСВ никак назвать не будем, т.к. он временный, в данном примере он называется "Книга 2"
Как работает формула ВПР(C3;[Книга2]Лист1!$A$10:$E$14;5;0). Сначала (желтый маркер на рисунке) идет ссылка на ячейку, в которой содержится наименование статьи, которое будем искать в ОСВ. Потом (синий маркер) идет ссылка на область в которой содержаться нужные нам данные, ссылка на область обязательно должна содержать знаки $, т.к. если без них, то при копировании формулы область будет смещаться и данные будут разноситься неправильно. Первый столбец обязательно должен содержать наименование статьей - это принцип работы формулы. Зеленым маркером выделен номер столбца, в котором находятся нужные нам данные (кредитовый оборот). Если данные заполняются до закрытия счетов то ссылка будет на столбец 4 - дебетовый оборот. Последний, невыделанный элемент - 0, показывает на то, что наименование статьи в файле Бюджет и файле с ОСВ должны полностью совпадать, поэтому важно, чтобы в файле Бюджет статьи назывались так же как бухгалтерском учета. С синтаксисом формулы ВПР можно познакомится в справке по Excel.
Когда все формулу заполнены, получается такая таблица
Строки "Данные ОСВ" и "расхождение" для первой проверки. Цель этой проверки - удостовериться, что все данные ОСВ попали в файл Бюджет.
Основное расхождение на данном этапе - статья из ОСВ не попадает в файл Бюджет, т.к. этой статьи в списке статей нет.
Например, если ОСВ будет выглядеть так
Статьи "095 Транспортные расходы" и "108 Командировочные расходы" в файл Бюджет не попадут.
Статья "108 Командировочные расходы" - не является ошибкой бухгалтерии, ее просто нет в нашем списке. В этом случает ее просто нужно добавить в таблицу в файле Бюджет. Наименование лучше скопировать, чтобы избежать ошибок ввода.
Статья "095 Транспортные расходы" - это ошибка бухгалтерии, эта статья "старая", она больше не используется в учете и была выбрана ошибочно или был скопирован старый документ, а статью не исправили. В этом случае бухгалтерия должна исправить статью на "105 Транспортные расходы". ОСВ потом измениться и можно будет занести правильные данные.
Если в учете используется много статей и сразу не видно, какая статья не попала, можно использовать обратную проверку, когда в файл ОСВ подтягиваются данные из файла Бюджет. На рисунке формула со ссылкой на файл Бюджет выделена желтым, она соответствует ячейке F10. В столбце G (Кредит), вставлена проверочная формула (F-E). Ошибка Н/Д показывает, что таких данных в таблице с расходами нет.
Этот же метод поиска отклонений можно использовать: 1) если отчетность формируется до закрытия расходных счетов и данные могут меняться; 2) произошли изменения задним числом, после закрытия периода.
После того, как все суммы будет разнесены, а итоги проверены, нужно будет "избавиться он формул ВПР". Для этого в столбце январь нужно выделить данные, содержащие формулы и вставить как значение.
Можно также воспользоваться диалогом Данные/Изменить связи/ Разорвать связь. Этот способ более надежный.
Зачем разрывать связи: 1) ОСВ может меняться (расходы могут разноситься постепенно, исправляться ошибки, корректироваться прошлые периоды) и нам важно закрепить соответствия именно на момент формирования данных (для сверки лучше сохранить ОСВ отдельно); 2) файл с ОСВ у нас временный (у него даже нет названия), если мы его удалим, то пропадут все данные; 3) лишние связи с другими книгами делают файл "тяжелым" и замедляют его работу.
Таким образом, формулу ВПР мы используем каждый раз, когда исправляются данные ОСВ и когда будет заполняться данные следующего месяца.
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке
