Если в компании не внедрена автоматизация управленческого учета (ERP, 1С УП, БИТ ФИНАНС и т.д.), то единственным источником для калькуляции затрат остается бухгалтерский учет (далее – БУ), и, в подавляющем большинстве случаев, это 1С БУХ. Для целей калькуляции доходов и расходов в рамках управленческого учета (далее – УУ) полный перенос информации из БУ не всегда корректен. Так, есть ряд расходов и доходов, которые необходимо исключить или наоборот добавить (технические операции) в УУ; иногда бывает необходимо добавить дополнительную аналитику и доп. реквизиты.
В любом случае процесс переноса данных из БУ в УУ всегда трудоемок, а в классическом случае, — когда экономист формирует бюджеты из оборотно-сальдовых ведомостей — еще и крайне негибок, так как любое изменение данных в БУ влечет за собой повторный сбор данных.
В данной статья мы предлагаем применить инструмент Power Query (далее – PQ) для сбора фактических данных и для его частичной автоматизации. Конечно, роль экономиста, пресловутый человеческий фактор, останется превалирующей, но сам процесс будет занимать гораздо меньше времени. Точность учета при этом вырастет.
Т.к. в большинстве случаев экономисты далеки от программирования, и написание любого кода для них становится проблемой, предлагается использование только стандартных функций интерфейса PQ.
Задача
Упростить процесс калькуляции затрат и учета доходов для целей управленческого учета из 1С БУХ
Источник данных
Минимальной единицей информации в БУ является бухгалтерская проводка, ее предлагается взять за основу для формирования УУ, т.к. чем больше информации, тем более качественную аналитику мы сможем предоставить.
Для формирования УУ мы можем пойти двумя путями. Сложный – заново разносить все расходы, исходя из Управленческой учетной политики. Простой – пользоваться уже разнесенными в рамках БУ затратами.
Простой способ предусматривает ряд упрощений, т.к. в БУ есть своя специфика, которая не совсем подходит для целей УУ. Так, например, списание ТМЦ с 10 счета бухгалтер может осуществлять не в момент фактического расходования данного ТМЦ, а (для оптимизации налогообложения) раньше или позже. Но упрощенный способ менее сложен, поэтому в данной статье будет описан именно он.
Для калькуляции затрат нам понадобятся следующие счета - 20,25,26,44,91.2, для формирования доходов – 90.1, 91.1. Разные счета содержат разную аналитику, поэтому для каждого счета будет индивидуальная обработка, после чего все счета будут объединены в единый файл. В качестве примера рассмотрим условное юридическое лицо ООО «Ромашка», в котором применяются 20 и 90.1 счета.
Стандартный отчёт в 1С Бухгалтерии, формирующий данные в разрезе проводок называется «карточка счета», с ним и будем работать.
Отчет имеет следующий вид.
Сразу виден ряд сложностей для обработки данных стандартными методами: во-первых, объединенные столбцы, во-вторых, в каждой ячейке с аналитикой данные разделены переводом строки.
Хотя форма отчета стандартная, ее наполнение может быть индивидуальным — в зависимости от вида той аналитики, которую использует бухгалтерия. В данном примере используется аналитика «подразделение, проект, статья затрат, контрагент, договор с контрагентом, документ расчета, описание вида работ».
Аналитика различается также в зависимости от корреспонденции счетов (дт20 кт60, дт20 кт70, дт20 кт10 и т.д.), так при корреспонденции дт20 кт10 в столбце «Аналитика Кт» не будет контрагента и договора, но появится наименование ТМЦ. Это добавляет дополнительные трудности. Подход к решению подобных трудностей будет показан далее.
Калькуляция затрат
Для того, чтобы загрузить таблицу в PQ, необходимо преобразовать ее в «Умную таблицу». Для этого выделяем всю таблицу и преобразовываем ее в «Умную таблицу» /Вставка - Таблица/. На этом этапе сразу можно удалить ненужные строки – сальдо на начало, счет, а также итоги внизу таблицы.
Шаг №1 Загружаем таблицу в запрос PQ /Данные - Из таблицы /. В зависимости от версии Excel, меню может несколько отличаться от представленного.
Основное меню PQ имеет следующий вид:
В правой части находятся параметры запроса, в которых отражаются все этапы работы с данными.
Первый шаг – источник; в нем указан абсолютный адрес рассматриваемой таблицы
= Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]Шаг №2 Удаляем все ненужные столбцы. Нажимаем на шапку столбца правой кнопкой мыши и «удалить».
Шаг №3 Разделяем Столбцы «Документ», «Аналитика Дт» и «Аналитика Кт» по разделителю – «перевод строки» /Главная - Разделить столбец по разделителю/
Далее выбираем опцию «пользовательский», убираем все символы в ячейке ниже, как указано на скриншоте (в примере автоматически проставлена точка), ставим отметку (галочку) /Разделить с помощью специального символа - Перевод строки/. Появится символ перевода строки #(lf).
Подтверждаем: «ОК»; в данном примере столбец разделился на 2 части – это тип операции и краткое ее описание. Ту же операцию проводит с остальными столбцами. В итоге вместо трех столбцов появилось девять.
Шаг №4 Переименовываем столбцы в соответствии со смыслом содержащейся в них информации. Наименование столбцов лучше сразу записывать в нижнем регистре, это пригодится при слиянии всех запросов в последующих шагах.
Шаг №5. В столбце «Аналитика Кт», в зависимости от счета по Кредиту, аналитика разная. Так, при корреспонденции с счетами 60.01 и 71.01, аналитика - контрагент идет по столбцу «Аналитика Кт.2», а в случае счетов 70, 76.10 — по столбцу «Аналитике Кт.1». Исправим эту ситуацию применив функцию If…Else — аналог условного действия ЕСЛИ в Excel. Для этого создаем новый столбец по условию /Добавление столбца - Условный столбец/. Также можно данную команду прописать в опции «Настраиваемый столбец» или в расширенном редакторе. Поскольку условие простое, то подойдет встроенный интерфейс.
В запросе присваиваем столбцу имя «контрагент» и заносим условие: если значение в столбце «Кредит» содержит 70, то вставляем данные из столбца «Аналитика Кт.1» Отметим, что в качестве иллюстрации выбрана операция «содержит», а не «равно», чтобы предусмотреть случай, когда бухгалтерия использует субсчета; это дает возможность включить в условие весь счет – 10.1,10.2,10.5 и т.д. Если значение в столбце «Кредит» равно 76.10, то вставляется значение из столбца «Аналитика Кт.1», в остальных случаях — значение из столбца «Аналитика Кт.2». В результате появляется новый столбец «Контрагент» с объединёнными значениями из двух столбцов по указанному условию.
Шаг №6 Приводим столбцы в нужный формат и в нужный порядок, и затем переименовываем столбцы, не переименованные ранее.
На этом обработка карточки счета закончена. Переименовываем запрос (например, «реестр») для будущего сбора данных из папки и выбираем опцию – Закрыть и загрузить.
Такие же манипуляции проводятся с остальными карточками счетов, загруженных в отдельные файлы.
Шаг №7. Отметим некоторые нюансы в работе с карточкой счета – 90.01. По этому субсчету все суммы проходят с НДС. Так как БДР считается без НДС, необходимо вычесть сумму НДС из данных.
Можно воспользоваться тем, что ставка НДС фигурирует в столбце «Аналитика Кт». Поскольку в разных случаях возможны ее различные значения — 20%, 10%, 0% или «без НДС», это нужно сразу прописать в условии аналогично примеру со столбцом «Контрагент». После разделения столбца «Аналитика Кт» по разделителю, функция подробно была разобрана ранее, добавляем условный столбец, в котором прописываем условие: если столбец содержит 0 или Без НДС, то заносится 1, в противном случае — значение ставки НДС из столбца «Аналитика Кт». Учет этого условия позволяет совершать арифметические действия с данными.
Далее создаем новый столбец /Добавить столбец - Настраиваемый столбец/
И прописываем следующее условие:
if [Пользовательская]=0.2 or [Пользовательская]=0.1
then [сумма]/(1+[Пользовательская])
else [сумма]Это означает: если значение в нашем столбце равно 0,2 или 0,1, то значение в столбце «сумма» делим на 1+ наш столбец, в остальных случаях ставим данные из столбца «сумма». Применение данной формулы позволяет получить сумму без НДС.
На этом обработка карточек счетов заканчивается.
Консолидация запросов и добавление необходимой аналитики
После обработки всех необходимых для анализа карточек счетов, необходимо объединить их в один файл и добавить, при необходимости, дополнительную аналитику.
Шаг №1 – создаем пустой файл Excel и в нем создаем умную таблицу, в которой прописываем путь к папке с карточками счетов. Таблице и столбцу даем произвольные имена, в примере это «путь_папка» и «путь», соответственно.
Далее создаем пустой запрос / Данные - Получить данные - Из других источников - Пустой запрос/ и прописываем в расширенном редакторе или в командной строке следующие условия:
= Folder.Files(Excel.CurrentWorkbook(){[Name="путь_папка"]}[Content]{0}[путь]) 
В результате появится список всех файлов, находящихся в указанной папке.
Этот шаг можно упростить, если нет необходимости менять адрес папки с карточками счетов и достаточно абсолютного адреса / Данные - Получить данные - Из файла - Из папки/. В открывшемся окне выбираем опцию «преобразовать»; после этого в открывшемся запросе вместо пути, который показан выше, будет находиться абсолютный путь к папке:
= Folder.Files("D:\Текучка\статья\карточки счетов")Его также можно менять, но уже в самом запросе.
Шаг №2 -добавляем настраиваемый столбец /Добавить столбец - Настраиваемый столбец/, и прописываем команду: Excel.Workbook([Content]), данная функция возвращает содержимое книги Excel. Разворачиваем полученный столбец нажатием кнопки в шапке столбца.
Фильтруем столбец «…Kind», выбрав тип Table (чтобы выбрать только умную таблицу из наших файлов) и фильтр по столбцу «…Name» по названию таблиц; в примере это «реестр»
Далее удаляем все столбцы, кроме столбца «…Data» и разворачиваем его, убрав галочку «Использовать исходное имя столбца как префикс», чтобы название столбцов было как в карточках счетов. Если возникла необходимость в информации об источнике данных , например для более простого поиска ошибок в исходниках, можно оставить столбец «Name», который содержит имя исходных файлов.
На этом этапе как раз и проявляется важность совпадения названий столбцов: если какие-то столбцы будут различаться в наименовании, они не будут объединены, а будут добавлены как отдельные столбцы в конце запроса. Этот принцип также верен и в том случае, когда какой-нибудь столбец есть в одной таблице, но его нет в другом.
На этом можно остановится, если аналитика БУ достаточна для формирования факта БДР. Но может возникнуть необходимость в корректировке или дополнении данных. Делать ручную замену в этом случае нецелесообразно, т.к. при первом же обновлении запроса все корректировки будут потеряны. Мы предлагаем использовать импровизированные справочники для корректировки данных. Как пример, заменим статьи затрат из БУ, на статьи для УУ.
Шаг №3. Создаем новую умную таблицу, в одном столбце которой указываем ту статью из карточки счета, которую хотим заменить; во втором столбце указываем эталон этой статьи. В примере статья УУ – это эталон, статья БУ – данные из карточек счета.
Добавляем таблицу в PQ, как делали это ранее. В меню «запросы» слева появится еще один запрос.
Теперь нам необходимо эти запросы объединить. Для этого в запросе с данными (в примере – «свод) мы выбираем команду «объединить запросы» / Главная - Объединить запросы - Объединить запросы/. В открывшемся окне выбираем запрос со статьями и выбираем столбцы, по которым будет происходить слияние (в примере: запрос «статья» - столбец «статья БУ», запрос «свод» - столбец «статья»), другие параметры оставляем по умолчанию и подтверждаем «ок».
В результате добавился новый столбец, который разворачиваем и выбираем опцию «показать только столбец с эталонными данными» (в примере – «статья УУ»). В столбце появились данные в тех строках, в которых мы хотели заменить статьи. Далее необходимо заменить данные в столбце «статья». Проще всего это сделать, создав условный столбец. Для этого прописываем условие: если в столбце «статья УУ» нет значений (в PQ отсунствует значений отображается как «null»), то подставляем значения из столбца «статья»; в остальных случаях подставляем значения из столбца «статья УУ», используя команду:
if [статья УУ] = null
then [статья]
else [статья УУ]Далее удаляем лишние столбцы и сохраняем результат.
На выходе мы получаем автоматическую замену данных при любых добавлениях и изменениях в исходниках. Также в таблицу-справочник мы можем вносить новые эталонные статьи по мере необходимости. По такому принципу можно добавлять, менять или удалять любые данные: подстановка статьи по контрагенту (например – выделить внутригрупповые расходы), содержанию или проекту, замена одного контрагента на другого, добавление аналитики и т.д. В дальнейшем экономисту необходимо просто контролировать появление новых данных для корректировки данных и обновлять справочники. Все остальное программа будет делать сама.
Заключение
В результате всех манипуляций на выходе мы получаем готовую базу данных по всем доходам и расходам компании с детализацией вплоть до бухгалтерской проводки и со всей необходимой аналитикой, которая будет автоматически обновляется при добавлении данных.
Из этой базы данных можно получать информацию как в виде сводных таблиц, так и в виде сложных отчетов с графиками и гистограммами. Количеством данных мы практически не ограничено. Такая схема работает как с одной организацией, так и с десятью. Возможно через объединение запросов добавлять планы и проводить план-фактный анализ, PQ прекрасно справляется с таким объемом информации. Но на этом уровне возникают уже сложности другого порядка: сложность выявления и обработки ошибок специалистом, недостаточность аналитики в БУ (с ростом данных, ее подстановка уже будет вызывать трудности) и т.д. Они решаются уже другими средствами — как техническими (через внедрение полноценной автоматизации), так и организационными (через унификацию Управленческой и Бухгалтерской учетной политики к единому знаменателю, исправление справочников и т.д.).
