Что такое и зачем нужна балансовая модель?
Данная статья не характерна для данного сайта, однако затрагивает интересную тему, которой пришлось в свое время заниматься.
Вспомогательным производством называется часть производственного процесса, направленная на обеспечение работы основного производства. Продукция и услуги таких подразделений потребляются преимущественно цехами и службами самого предприятия. Таким образом, если учет прямых расходов основного производства, как правило, прозрачен, то затраты вспомогательных подразделений необходимо распределять между видами продукции основного производства, что зачастую является неоднозначным и запутанным процессом.
Основная сложность в расчете себестоимости вспомогательных производств заключается в учете встречных услуг, т.е. услуг, которые были оказаны вспомогательными цехами друг другу. Причем данная задача многократно усложняется, если предприятие производит несколько видов продукции и имеет несколько вспомогательных цехов.
Методом, позволяющим наиболее объективно распределить затраты по встречным услугам является так называемый метод балансовой модели, то есть решение системы балансовых уравнений, в результате чего определяются совокупные затраты каждого подразделения.
На крупных предприятиях составление и решение системы балансовых уравнений, как правило, осуществляется специализированным ПО. Однако в небольших компаниях или в качестве временной меры до полноценной автоматизации бизнеса балансовую модель можно составить в Excel, причем производительность данного метода очень высока: в Excel с легкостью распределяются затраты десятков и даже сотен подразделений.
Пример построения балансовой модели в Excel
Рассмотрим порядок разработки балансовой модели в Excel и расчета себестоимости реализации услуг на примере предприятия ООО «Генератор». Предприятие состоит из 3 подразделений: основное производство – энергоблок и 2 вспомогательных подразделения – транспортный отдел и ремонтный цех, при этом:
• энергоблок, являясь основным производством, полностью поставляет электроэнергию на сторону;
• транспортный отдел перевозит собственных сотрудников, сотрудников энергоблока и ремонтного цеха, а также осуществляет перевозки сторонних лиц;
• ремонтный цех обслуживает транспортный отдел, энергоблок, а также собственное оборудование, не оказывая сторонних услуг.
Таким образом, предприятие оказывает 2 вида услуг: основной – поставка электроэнергии и дополнительный – автотранспортные услуги, себестоимость которых необходимо определить. При этом на предприятии присутствуют встречно оказываемые услуги: транспортный цех перевозит сотрудников ремонтного цеха, а ремонтный цех, в свою очередь, ремонтирует транспортный цех. Также и ремонтный и транспортный цеха частично оказывают услуги сами себе.
Шаг 1. Подготовка исходных данных для балансовой модели
Для решения задачи необходима информация о прямых (собственных) затратах всех подразделений, а также план производства продукции.
План производства продукции предлагается организовать в виде таблицы 1, где каждое подразделение представлено в 2 ролях – в качестве производителя и в качестве потребителя услуг.
Например, транспортный цех за период оказал услуг в объеме 600 маш-часов энергоблоку, 50 маш-часов – перевозил собственных сотрудников, 360 маш-часов – сотрудников ремонтного цеха и в объеме 1000 маш-часов оказывал услуги на сторону. По остальным подразделениям подход аналогичен.
Таблица 1. План производства и реализации продукции ООО «Генератор» на июнь 2018 года.
В таблице 2 представлены прямые затраты подразделений, т.е. плановый бюджет предприятия на июнь 2018 года.
Таблица 2. Плановый бюджет ООО «Генератор» на июнь 2018 года, рублей.
Производственные взаимосвязи подразделений схематично представлены на рисунке 1.
Рисунок 1. Схема плана производства и реализации продукции ООО «Генератор» на июнь 2018 года.
Шаг 2. Составление балансовой модели предприятия.
Как было сказано выше, для грамотного расчета себестоимости реализованных услуг недостаточно информации о прямых затратах – необходимо вычислить полную себестоимость каждого подразделения (совокупные расходы), т.е. сумму прямых затрат и стоимости потребленных внутренних услуг.
Затраты каждого подразделения в выпуске продукции другого подразделения можно выразить через долю его совокупных затрат. Например, транспортный цех около 30% своего времени (600/2010 маш-час) возил сотрудников энергоблока, а ремонтный цех 57% времени (1000/1760 чел-ч) ремонтировал энергоблок.
Следовательно, совокупные затраты энергоблока будут равны сумме 30% совокупных затрат транспортного цеха, 57% совокупных затрат ремонтного цеха и 700 000 рублей прямых затрат. Аналогичным образом анализируется потребление каждого МВЗ, в результате чего получается следующая система уравнений (балансовая модель), которую необходимо решить относительно совокупных затрат:
X1 = 0,3 X2 + 0,57 X3+700 000
X2 = 0,02 X2 + 0,35 X3+300 000
X3 = 0,18 X2 + 0,09 X3+500 000,
где
X1,2,3 – совокупные затраты энергоблока, транспортного и ремонтного цехов соответственно.
Делением совокупных затрат на объем выпуска мы получим реальную себестоимость единицы реализованной продукции.
Шаг 3. Решение балансовой модели средствами Excel
В рамках настоящей статьи не будет подробно рассматриваться теория решения систем алгебраических уравнений (СЛАУ), мы покажем, как быстро решить данную задачу в Excel при помощи специальных функций по работе с матрицами.
Систему балансовых уравнений можно записать в матричном виде следующим образом:
Х=АХ+Y,
где
X – матрица совокупных затрат;
А – матрица долей производства/потребления продукции;
Y – матрица прямых затрат.
Матрица долей производства и потребления (матрица А) соответствует диапазону C27:E29 таблицы 3.
Таблица 3. Структура производства и потребления продукции ООО «Генератор» в июне 2018 года, %.
Преобразования над уравнением в матричной записи аналогичны обычному уравнению, таким образом, получим:
Х – АХ=Y
X(1-A)=Y
X=Y(1-A)-1
X=Y(E-A)-1,
где
E – единичная матрица,
(E-A)-1 – обратная матрица.
Введем в диапазоне С35:E37 единицы по диагонали (единичная матрица E) согласно таблице 4.
Таблица 4. Единичная матрица (E).
В таблице 5 в ячейках С39:E41 сформируем матрицу E-A, то есть вычтем из единичной матрицы E диапазон со структурой оказанных услуг С27:E32 таблицы 3.
Таблица 5. Расчет матрицы E-A.
Рассчитаем так называемую обратную матрицу (E-A)-1. Для этого выделим диапазон С43:E45 таблицы 6, введем формулу =МОБР(C39:E41) и нажмем Ctrl+Shift+Enter (команда активирует формулы массивов).
Таблица 6. Расчет обратной матрицы (E-A)-1 и совокупных расходов.
На последнем шаге рассчитаем совокупные расходы, т.е. выполним действие Y(E-A)-1. Для этого воспользуемся функцией по перемножению матриц: выделим диапазон С47:С49, введем формулу =МУМНОЖ(C43:E45;G4:G6), где G4:G6 – диапазон с прямыми затратами и также нажмем Ctrl+Shift+Enter.
В результате получаем совокупные затраты энергоблока, транспортного и ремонтного цехов в сумме 1231,6 , 539,5 и 652,2 тыс рублей соответственно.
Шаг 4. Определение себестоимости реализованной продукции
На последнем шаге вычислим полную себестоимость реализации электроэнергии и транспортных услуг ООО «Генератор» в июне 2018 года.
Так как энергоблок не оказывал внутренних услуг, себестоимость реализации электроэнергии будет равняться совокупным затратам энергоблока, т.е. 1 231,6 тыс рублей.
По транспортному цеху 49,75% объема оказанных услуг приходится на сторонних лиц, следовательно, полная себестоимость реализации транспортных услуг составит 539,5*49,75%=268,4 тыс. рублей.
На выходе получается следующая таблица.
Таблица 7. Итоговые результаты расчета
Как можно заметить, общая себестоимость сторонней реализации электроэнергии и транспортных услуг составляет 1 500 тыс. рублей, что соответствует сумме прямых затрат предприятия, следовательно, затраты по всем внутренним услугам были распределены.
Делением совокупных расходов на объем выпуска МВЗ получаем себестоимость единицы оказания услуг, которая составляет 1,231 /600=2,1 (руб/кВт-ч) по энергоблоку и 268,4/1=268,4 (руб/маш-ч) по транспортному цеху.
Балансовая модель решена!
При 2-3 подразделениях, оказывающих услуги, построить и решить систему балансовых уравнений можно вручную. Однако, каждое дополнительное МВЗ, участвующее в распределении, будет добавлять по 1 уравнению в систему. Таким образом, для более сложных взаимодействий подразделений необходима автоматизация, как минимум в Excel. На описанном принципе успешно работают модели, распределяющие затраты сотен МВЗ.
По работе мне иногда приходилось решать подобные задачи, поэтому функционал для автоматической разработки балансовых уравнений был включен в нашу надстройку PowerQuick:
Ниже справочно приведен полный вид балансовой модели в Excel:
С отображением чисел:
С отображением формул:
