Балансовая модель Excel

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

Что такое и зачем нужна балансовая модель?

Данная статья не характерна для данного сайта, однако затрагивает интересную тему, которой пришлось в свое время заниматься.

Вспомогательным производством называется часть производственного процесса, направленная на обеспечение работы основного производства. Продукция и услуги таких подразделений потребляются преимущественно цехами и службами самого предприятия. Таким образом, если учет прямых расходов основного производства, как правило, прозрачен, то затраты вспомогательных подразделений необходимо распределять между видами продукции основного производства, что зачастую является неоднозначным и запутанным процессом.

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

Методом, позволяющим наиболее объективно распределить затраты по встречным услугам является так называемый метод балансовой модели, то есть решение системы балансовых уравнений, в результате чего определяются совокупные затраты каждого подразделения.

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

Пример построения балансовой модели в Excel

Рассмотрим порядок разработки балансовой модели в Excel и расчета себестоимости реализации услуг на примере предприятия ООО «Генератор». Предприятие состоит из 3 подразделений: основное производство – энергоблок и 2 вспомогательных подразделения – транспортный отдел и ремонтный цех, при этом:

• энергоблок, являясь основным производством, полностью поставляет электроэнергию на сторону;

• транспортный отдел перевозит собственных сотрудников, сотрудников энергоблока и ремонтного цеха, а также осуществляет перевозки сторонних лиц;

• ремонтный цех обслуживает транспортный отдел, энергоблок, а также собственное оборудование, не оказывая сторонних услуг.

Таким образом, предприятие оказывает 2 вида услуг: основной – поставка электроэнергии и дополнительный – автотранспортные услуги, себестоимость которых необходимо определить. При этом на предприятии присутствуют встречно оказываемые услуги: транспортный цех перевозит сотрудников ремонтного цеха, а ремонтный цех, в свою очередь, ремонтирует транспортный цех. Также и ремонтный и транспортный цеха частично оказывают услуги сами себе.

Шаг 1. Подготовка исходных данных для балансовой модели

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

План производства продукции предлагается организовать в виде таблицы 1, где каждое подразделение представлено в 2 роляхв качестве производителя и в качестве потребителя услуг.

Например, транспортный цех за период оказал услуг в объеме 600 маш-часов энергоблоку, 50 маш-часов – перевозил собственных сотрудников, 360 маш-часов – сотрудников ремонтного цеха и в объеме 1000 маш-часов оказывал услуги на сторону. По остальным подразделениям подход аналогичен.

Таблица 1. План производства и реализации продукции ООО «Генератор» на июнь 2018 года.

Балансовая модель Excel

В таблице 2 представлены прямые затраты подразделений, т.е. плановый бюджет предприятия на июнь 2018 года.

Таблица 2. Плановый бюджет ООО «Генератор» на июнь 2018 года, рублей.

Балансовая модель Excel

Производственные взаимосвязи подразделений схематично представлены на рисунке 1.

Рисунок 1. Схема плана производства и реализации продукции ООО «Генератор» на июнь 2018 года.

Балансовая модель Excel схема

Шаг 2. Составление балансовой модели предприятия.

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

Затраты каждого подразделения в выпуске продукции другого подразделения можно выразить через долю его совокупных затрат. Например, транспортный цех около 30% своего времени (600/2010 маш-час) возил сотрудников энергоблока, а ремонтный цех 57% времени (1000/1760 чел-ч) ремонтировал энергоблок. 

Следовательно, совокупные затраты энергоблока будут равны сумме 30% совокупных затрат транспортного цеха, 57% совокупных затрат ремонтного цеха и 700 000 рублей прямых затрат. Аналогичным образом анализируется потребление каждого МВЗ, в результате чего получается следующая система уравнений (балансовая модель), которую необходимо решить относительно совокупных затрат:

X1 = 0,3 X2 + 0,57 X3+700 00

X2 = 0 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 года, %.

Балансовая модель Excel

Преобразования над уравнением в матричной записи аналогичны обычному уравнению, таким образом, получим:

Х – АХ=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).

Балансовая модель Excel

В таблице 5 в ячейках С39:E41 сформируем матрицу E-A, то есть вычтем из единичной матрицы E диапазон со структурой оказанных услуг С27:E32 таблицы 3.

Таблица 5. Расчет матрицы E-A.

Балансовая модель Excel

Рассчитаем так называемую обратную матрицу (E-A)-1. Для этого выделим диапазон С43:E45 таблицы 6, введем формулу =МОБР(C39:E41) и нажмем Ctrl+Shift+Enter (команда активирует формулы массивов).

Таблица 6. Расчет обратной матрицы (E-A)-1 и совокупных расходов.

Балансовая модель Excel

На последнем шаге рассчитаем совокупные расходы, т.е. выполним действие 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. Итоговые результаты расчета

Балансовая модель Excel

Как можно заметить, общая себестоимость сторонней реализации электроэнергии и транспортных услуг составляет 1 500 тыс. рублей, что соответствует сумме прямых затрат предприятия, следовательно, затраты по всем внутренним услугам были распределены.

Делением совокупных расходов на объем выпуска МВЗ получаем себестоимость единицы оказания услуг, которая составляет 1,231 /600=2,1 (руб/кВт-ч) по энергоблоку и 268,4/1=268,4 (руб/маш-ч) по транспортному цеху.

 

Балансовая модель решена!

При 2-3 подразделениях, оказывающих услуги, построить и решить систему балансовых уравнений можно вручную. Однако, каждое дополнительное МВЗ, участвующее в распределении, будет добавлять по 1 уравнению в систему. Таким образом, для более сложных взаимодействий подразделений необходима автоматизация, как минимум в Excel. На описанном принципе успешно работают модели, распределяющие затраты сотен МВЗ.

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

Решение уравнений в Excel

Ниже справочно приведен полный вид балансовой модели в Excel:

С отображением чисел:

Балансовая модель Excel
Балансовая модель Excel

С отображением формул:

Балансовая модель Excel формулы
Балансовая модель Excel
Share on vk
Share on odnoklassniki
Share on facebook
Share on twitter
Share on email
Share on whatsapp
Share on telegram
Share on print

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Пролистать наверх