Новая задача для Excel: расчет тарифа на контрактное производство

5024
Родин Николай
заместитель финансового директора ООО «Брянскспиртпром»
Если компания получила сторонний заказ на производство, ключевой задачей становится определение стоимости услуг. При этом очень важно понимать, как это делать, а также уметь детализировать и группировать затраты для формирования коммерческого предложения. Форму анализа можно подготовить, используя общеизвестные формулы и функции Excel.

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

1. Снижение себестоимости единицы продукции собственного производства, а следовательно, повышение рентабельности.

2. Повышение финансовой устойчивости предприятия.

3. Более полная загрузка производственной мощности. Особенно актуально, если использование минимальных мощностей определено законодательно. Так, например, в результате постоянного давления государства на алкогольную отрасль через ежегодный рост акцизов (в 2013 году по сравнению с 2012-м увеличение ставки составило более 30%, в 2014 году рост акциза по сравнению с нынешним годом по проекту составит 25%) многие производственные предприятия закрываются. Причина — снижение общей емкости рынка (в себестоимости готового продукта акциз составляет не менее 60% от минимальной отпускной цены завода).

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

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

Задача финансовой службы в данном случае — проводить полную аналитику по определению стоимости услуг по контрактному размещению производства. Для ее решения в ООО «БрянскСпиртПром» разработали форму анализа стоимости услуг. В ее основу была положена калькуляция себестоимости продукции по статьям расходов и их аналитическая группировка в бухгалтерском учете, применяемом на предприятии. Рассмотрим подробнее, что было сделано и какие результаты получены.

Определение косвенных и накладных расходов

На определение тарифа влияют только косвенные и накладные расходы, так как это расходы нашего предприятия. А вот, например, комплектующие, которые используются при производстве стороннего заказа, поставляются поставщиком и их стоимость полностью зависит от него. Естественно, в конечную цену отгрузки произведенного продукта ложатся все расходы, но стоимость комплектующих уже определена заказчиком, и ему интересны только те расходы, за которые ему и предлагают разместить заказ-то есть только тариф на производство.

Итак. Все расходы (косвенные, накладные и прочие) были выгружены из базы «1С» в таблицу Excel в виде оборотно-сальдовых ведомостей с детализацией по статьям — в разрезе счетов бухгалтерского учета (счета 20, 25, 26, 44, 91) и принятой аналитики на предприятии — по каждому счету свои статьи расходов (см. табл. 1). Исходные данные выгружались. Отмечу два важных момента.

  • В качестве исходной информации для анализа могут выступать и данные анализа счета, карточки счета — опять же все зависит от поставленного учета и аналитики на предприятии. У нас бухгалтерский и управленческий учет ведется в «1С:8», которая дает широкие возможности для проведения анализа, и сбор данных для проведения исследования по расходам занимает незначительное время.
  • Количество статей расходов зависит от размеров предприятия, сложности и технологии производства, поставленного бухгалтерского учета и аналитики в организации и многого другого. На нашем предприятии общее количество статей превышает 100 наименований. И это при условии того, что все сырье и комплектующие объединены в две крупные статьи «сырье» и «комплектующие» (эти расходы прямые и ложатся сразу на выпускаемый товар). А задача в нашем случае, напомню, — это распределение косвенных и накладных расходов для определения тарифа по выпуску сторонней продукции.

ТАБЛИЦА 1. Основные статьи затрат в разрезе счетов бухгалтерского учета

Статья Счет бухгалтерского учета янв. фев. .....
Сырье 20      
Комплектующие 20      
Зарплата 20      
Налоги с зарплаты 20      
<...> 20      
Аренда 25      
Газ 25      
ГСМ 25      
Зарплата 25      
<...> 25      
Аренда 44      
Зарплата 44      
Командировочные 44      
<...> 44      
Комиссия, марки 91      
Комиссия, спирт 91      
% по кредитам 91      
<...> 91      
    0,00 0,00 0,00

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

Определение влияния изменений объемов производства на статьи расходов

Взяв помесячные данные по расходам и объемам производства за последние два года, мы применили, я считаю, наиболее эффективные и понятные инструменты для определения зависимости одного показателя от другого.

1. Корреляционный анализ — метод обработки статистических данных, с помощью которого измеряется теснота и направление связи между двумя или более переменными. В таблице 2 показаны критерии определения тесноты и направления связи. В нашем случае коэффициент корреляции (Кк) показывает, насколько сильно расходы зависят от объемов производства.

ТАБЛИЦА 2. Оценка тесноты и направления связи между двумя или более переменными

Теснота связи Обратная связь
(увеличение фактора влечет
снижение результата, и наоборот)
Прямая связь
(увеличение фактора влечет
увеличение затрат)
Слабая От –0 до –0,3 От 0 до 0,3
Средняя От –0,3 до –0,7 От 0,3 до 0,7
Сильная От –0,7 до –1,0 От 0,7 до 1,0

Применение именно корреляционного анализа объясняется тем, что он помогает автоматизировать выявление связей результатов и факторов при наличии большого числа факторов. Альтернативой корреляционному анализу в нашем случае может выступить интуиция исполнителя при отнесении расходов на постоянные и переменные (как часто и происходит в действительности), но при таком количестве факторов это займет определенное время, и возможны погрешности из-за незнания нюансов расходов и их особенностей. А корреляционный анализ — это точный анализ, основанный на выявлении математической зависимости факторов и результатов.

Расчет коэффициента корреляции (Кк) делается в таблице Excel по формуле:

Кк = коррел (диапазон результата; диапазон фактора)

где результат — Y (расходы на производство), фактор — X (объемы производства).

2. Затем в этой же таблице Excel мы применили формулу коэффициента детерминации (Кд) — определяется, как квадрат коэффициента корреляции Кк2 и в нашем случае помогает понять, какая часть изменения расходов (результат, наша цель) вызвана изменением объемов производства (фактор, дополнительная загрузка за счет контрактного розлива).

Применение коэффициента детерминации объясняется простотой его понимания и восприятия с точки зрения результата: если значение коэффициента более 50 процентов, то расходы существенно зависят от объемов производства, другими словами, показатель сразу дает ответ: «да» (50% и более) или «нет» (до 50%).

В результате:

  • к переменным расходам относились те, связь которых с объемом производства очень высокая: Кк > 0,7, Кд > 0,5;
  • к постоянным расходам относились те, коэффициент детерминации которых менее 50 процентов.

Для наглядности в таблице 3 представлена часть результатов анализа по коэффициентам корреляции (Коэф. Корр.) и детерминации (Коэф. Детер.) в ранжированном виде — от наиболее связанных с объемами производства до наименее связанных и зависимых. Так, мы видим, что наиболее связаны объемы производства с расходами на электроэнергию, газ, потери и пр. Именно эти расходы и следует относить к плановым для определения тарифа на розлив.

ТАБЛИЦА 3. Влияние изменений объемов производства на каждую статью расходов

Статья Cчет Вид Признак Янв. 2012 Июн. 2013 Коэф. корр. Коэф. детер.
Объем производства Х X Х
Электроэнергия 20 Накладные Y 1 1,00 0,99
Газ 20 Накладные Y 2 0,99 0,99
Потери 20 Основные 0,98 0,96
Сырье 20 Основные 0,97 0,94
Комплектующие 20 Основные 0,96 0,91
Компьютерная техника, ПО 44 Усл. перем. 0,87 0,75
Страхование 26 Усл. пост. 0,86 0,73
Налоги 44 Усл. перем. 0,86 0,73
Экспорт 44 Усл. перем. 0,86 0,73
Прочие 91 Усл. перем. 0,84 0,70
Амортизация 44 Усл. пост. 0,83 0,70
Связь 25 Накладные 0,83 0,69
Обеспечение нормальных условий труда 44 Усл. перем. 0,82 0,66
Обеспечение нормальных условий труда 25 Накладные 0,81 0,66
Газ 25 Накладные 0,79 0,62
Прочие 25 Накладные 0,76 0,58
Канцелярия 26 Усл. пост. 0,76 0,57
Обучение 25 Накладные 0,74 0,54
Электроэнергия 25 Накладные Уn –0,73 0,53

Определение тарифа по услугам стороннего розлива

Результаты анализа по коэффициентам корреляции и детерминации мы укрупненно сгруппировали по следующим статьям расходов:

  • основные производственные расходы (счет 20) — ложатся только на выпускаемый конкретный вид продукции;
  • постоянные производственные и цеховые расходы (счета 20, 25) — распределяются на весь ассортимент исходя из структуры производства;
  • постоянные непроизводственные расходы (счета 26, 44, 91);
  • переменные непроизводственные расходы (счета 26, 44, 91).

Далее эти статьи расходов распределили в таблице Excel через общеизвестные формулы и функции программы («ВПР» и «СУММЕСЛИ»). Их применение позволяет сразу сгруппировать статьи расходов в одну большую группу переменных расходов, которые следует относить на тариф по розливу.

На сайте журнала «Финансовый директор» вы найдете форму, которая использовалась для расчета на услуги розлива. В таблице 4 представлена окончательная форма при определении тарифа.

ТАБЛИЦА 4. Аналитика расчета тарифа по оказанию услуг по розливу

Статья Cчет Вид Хар-ка  Янв.12 Кк  Кд 
1. Основные сырье и комплектующие 20 ОС Переменные =суммесли() 0,97 0,94
2. Производственные и цеховые расходы 20, 25 ПР Постоянные =суммесли() 0,77 0,55
3. Непроизводственные постоянные 26, 44, 91 НПпос Постоянные =суммесли() 0,10 0,01
4. Непроизводственные переменные 26, 44, 91 НПпер Переменные =суммесли() 0,9 0,81
5. Объем выпуска        …    
6. Розлив, производственные расходы       =2/5    
7. Розлив, непроизводственные переменные расходы       =4/5    
8. Экономия на пост. расходах за счет увеличения объемов       =(6 + 7) : (5 + 9) – 6 + 7) : (5)    
ИТОГО РОЗЛИВ       =6 + 7 + 8    
9. Прирост производства Х          

Цифры, указанные в формулах в таблице 4, например =2/5, — это номера показателей в таблице («2» — производственные и цеховые расходы, «5» — объем выпуска и т. д.). То есть производственные расходы (№ 2 в таблице) мы делим на объем выпуска (№ 5 в таблице). Следовательно, тариф на розлив определяется путем суммирования:

  • производственных расходов на розлив на единицу продукции (6) — всегда положительное значение;
  • непроизводственных переменных расходов на единицу продукции (7) — всегда положительное значение;
  • экономии на постоянных расходах за счет увеличения объемов производства при привлечении сторонних заказов на производство продукции. Экономия получается при росте производства, так как при увеличении объемов выпуска продукции постоянные расходы на единицу продукции снижаются (8). Таким образом, экономия — это отрицательное число, которое снижает тариф на розлив. В нашем случае тариф складывался из следующих расходов:
  • постоянных производственных и цеховых расходов в расчете на единицу продукции (в нашем случае на одну условную бутылку емкости 0,5 л);
  • переменных непроизводственных расходов в расчете на единицу продукции.

Результаты анализа

Руководство предприятия получило четкую картину цены коммерческого предложения по привлечению стороннего производства и, что важно, увидело реальную выгоду такого сотрудничества. Менее чем за год розлив по контракту:

  • позволил нам увеличить загрузку производства сверх минимально разрешенной законодательством нормы;
  • снизить себестоимость единицы продукции собственного производства.

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

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

Все описанные таблицы Excel можно скачать по ссылке в конце статьи Контрактное размещение производства: как рассчитать тариф в электронной версии журнала «Финансовый директор». 

 

ДОСЬЕ КОМПАНИИ
ООО «БрянскСпиртПром», Брянск

Производственное предприятие регионального значения. Основано в 2008 году. Основной вид деятельности: производство алкогольной продукции. На сегодняшний момент ассортиментная линейка «БрянскСпиртПром» насчитывает более 50 наименований продукции: водки, горькие и сладкие настойки, бальзамы. Основной регион сбыта продукции — Брянская область.



Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Живое общение с редакцией

А еще...


Опрос

Вы планируете менять работу в новом году?

  • Да, планирую 36%
  • Подумываю об этом 26.4%
  • Нет, пока никаких перемен 28%
  • Это секрет! 9.6%
Другие опросы

Рассылка



Вас заинтересует

© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


  • Мы в соцсетях
×
Чтобы скачать файл, пожалуйста, зарегистрируйтесь

Сайт журнала «Финансовый директор» - это профессиональный ресурс для сотрудников финансовых служб и профессиональных управленцев.

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Зарегистрируйтесь на сайте,
чтобы продолжить чтение статьи

Еще Вы сможете бесплатно:
Скачать надстройку для Excel. Узнайте риск налоговой проверки в вашей компании
Прочитать книгу «Я – финансовый директор. Секреты профессии» (раздел «Книги»)

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль