Как в Excel контролировать изменение себестоимости
Скачайте Excel-модель, подставьте свои данные и выясните, почему изменилась себестоимость в сравнении с планом или прошлым периодом.
Иветта Новикова
финансовый менеджер ООО «ТЕТ-а-ТЕТ»
Контролировать себестоимость продукции, расход сырья и потери, получать аналитику по каждому отдельному товару поможет специальная модель в Excelxlsx. Чтобы ее создать, определитесь с исходными данными; утвердите единый формат расчетов и представления информации; выберите контролируемые показатели и согласуйте методику их вычисления.
Какие данные подготовить для контроля себестоимости готовой продукции в Excel
Чтобы рассчитать себестоимость продукции в Excel, нужны:
- нормативы расхода сырья и материалов на единицу продукции, нормы потерь и брака;
- фактический расход сырья и материалов в производстве за отчетный период;
- плановый и фактический объем выпуска;
- плановые и фактические затраты на производство;
- плановые и фактические цены на сырье и материалы для производства;
- отпускная цена на готовую продукцию.
Стоимость сырья и материалов. Информацию о закупках – цены, валюта – объедините в отдельный лист Excel. За плановые значения возьмите данные прошлого периода или прогнозы по предварительным договоренностям о поставках.
Если компания закупает одно сырье у нескольких поставщиков, то цену укажите, как принимаете к учету: по первой поставке, последней или среднюю за период. Если стоимость сырья – в иностранной валюте, на этом же листе уточните вид валюты, ее курс по плану из бюджета закупок и по факту на день закупки, смотрите рисунок 1. Эту часть таблицы можно заполнять автоматически, если увязать с отчетом о закупках или учетной системой.
Рисунок 1. Стоимость сырья и материалов Скачатьxlsx
Нормы расхода сырья и материалов. Плановые нормы расходов возьмите из спецификаций, технологических карт или фактических данных прошлого периода.
Перечислите на новом листе Excel наименования готовой продукции, сырья и материалов, нормы расхода и потери в натуральном и стоимостном выражении. Плановые данные зафиксируйте на длительный период, фактические – заполняйте по результатам выпуска продуктов по аналогии с плановыми, смотрите рисунок 2.
Чтобы увязать стоимостные показатели c закупочными ценами, воспользуйтесь функцией СУММЕСЛИ() вместе со ссылками на страницу с ценами.
Рисунок 2. Нормы расхода сырья и материалов Скачатьxlsx
Объем выпуска. Данные об объемах выпуска скопируйте из производственной программы или учетной системы – в натуральных единицах по каждому наименованию, смотрите рисунок 3. Продукты вводите вручную или создайте выпадающий список из «Справочника ГП».
Рисунок 3. Выпуск готовой продукции Скачатьxlsx
Затраты на производство. Затраты на производство возьмите из бюджета расходов на производство готовой продукции и учетной системы, смотрите рисунок 4. В зависимости от целей анализа себестоимости расходы можно разбить по статьям: аренда и коммунальные платежи, зарплата производственного персонала и т. д., либо показать общую сумму.
Рисунок 4. Затраты на производство Скачатьxlsx
Цены на готовую продукцию. Цены скопируйте из учетной системы, ценовой политики или бюджета продаж. При гибкой ценовой политике из ценовой матрицы можно взять среднюю цену для каждой позиции. Или же минимальную и максимальную – так оцените наименьшую и наибольшую рентабельность продаж каждого наименования.
Рисунок 5. Цены на готовую продукцию Скачатьxlsx
Как рассчитать себестоимость готовой продукции в Excel
В Excel нужно рассчитывать себестоимость по правилам калькуляции в управленческом учете.
В книге Excel с исходными данными для расчета себестоимости на новом листе или на листе с данными о производственном выпуске добавьте столбцы:
- расход сырья и материалов в стоимостном выражении для всего выпуска;
- прочие прямые расходы;
- полная себестоимость выпуска;
- цена за единицу и стоимость выпуска;
- маржинальная прибыль выпуска и одной единицы готовой продукции;
- себестоимость одной единицы;
- рентабельность готовой продукции.
Рисунок 6. Себестоимость и рентабельность готовой продукции Скачатьxlsx
Расход сырья и материалов в стоимостном выражении. В ячейку, где нужно указать плановый расход сырья (в примере на рисунке 6 – E6), введите формулу:
СУММЕСЛИ(Рецептура!A:A;B6;Рецептура!H:H)*C6, где:
СУММЕСЛИ(Рецептура!A:A;B6;Рецептура!H:H)*C6, где:
- Рецептура!A:A – список наименований готовой продукции на листе с содержанием нормативов расходов, смотрите рисунок 2;
- B6 – наименование готовой продукции, которую планировалось выпустить в соответствии с производственной программой;
- Рецептура!H:H – ссылка на столбец с нормами расходов на упаковку в рублях;
- C6 – объем выпуска этого наименования готовой продукции по плану.
Эта формула найдет все сырье, соответствующее выделенной позиции готовой продукции, умножит сумму расходов на одну единицу на объем выпуска в натуральных показателях. Так получится итого расходов на выпуск одной номенклатурной позиции. Если заменить в формуле «Рецептура!H:H» на «Рецептура!M:M», а «C6» на «D6», получите фактический расход сырья и материалов на весь выпуск.
Прочие прямые расходы. Если есть возможность отнести прочие прямые расходы на каждый вид готовой продукции, укажите эти суммы (в столбцах G и H на рисунке 6). Иначе прочие прямые расходы распределите по объемам выпуска.
Полная себестоимость выпуска. Рассчитайте сумму по столбцам для плановой (на рисунке 6 – E и G) и фактической (E и G) себестоимости.
Цена за единицу и стоимость выпуска. Для всего столбца «Цена» (на рисунке 6 это столбец K) запишите формулу СУММЕСЛИ(), с помощью которой на текущий лист перенесите данные с листа «Справочник ГП». Стоимость выпуска равна произведению цены на единицу и объема выпуска.
Маржинальная прибыль выпуска и одной единицы готовой продукции. Маржинальная прибыль всего выпуска – разница между стоимостью и себестоимостью выпуска. Разделив эту сумму на объем выпуска, получите маржинальную прибыль на единицу продукции.
Себестоимость одной единицы. Разделите полную плановую и фактическую себестоимость выпуска на плановый и фактический объем выпуска соответственно (смотрите столбцы P и Q на рисунке 6).
Рентабельность готовой продукции.
Как контролировать изменения в себестоимости готовой продукции в Excel
Себестоимость готовой продукции растет или снижается, если производство нарушает нормы сырья и материалов, увеличивает или сокращает производственные потери, если покупают сырье подороже или подешевле, если не удается выдержать производственную программу или если меняются прочие производственные расходы.
В модели для расчета и анализа себестоимости есть таблица, где можно отследить план-факт отклонения себестоимости, маржинальной прибыли, рентабельности для каждого продукта, смотрите рисунок 6. Остается дополнить ее инструментом для подробного анализа конкретной позиции.
На рисунке 7 модель с шаблоном для детального анализа. При выборе наименования из списка шаблон автоматически заполняется подробной информацией о выпуске этой продукции.
Шаблон модели рассчитывает для каждого сырья и материала:
- расход в производство согласно плану и факту, отклонения на единицу продукции и весь выпуск в натуральном и стоимостном выражении;
- изменение нормы потерь – рост или снижение;
- валюту закупки и ее изменение: рост или снижение;
- закупочную стоимость на единицу продукции по плану и по факту, план-факт отклонение;
- влияние отклонения фактической цены на сырье от плановой;
- влияние отклонения фактического расхода материалов от плана.
На этом же листе можно привести общие данные: отклонения от производственной программы в натуральном выражении, себестоимость единицы и выпуска выбранной продукции.
Рисунок 7. Анализ себестоимости готовой продукции по наименованиям Скачатьxlsx
Чтобы проанализировать себестоимость выпуска, можно воспользоваться формой, как на рисунке 8. Для нее достаточно уже собранных исходных данных. С ее помощью можно точнее оценить, стали ли причиной отклонений себестоимости прямые расходы и какие именно. Например, если за счет переработок выросли расходы на персонал и коммунальные платежи или в связи со сменой поставщика повысились затраты на транспортировку.
Рисунок 8. Анализ себестоимости всего выпуска готовой продукции Скачатьxlsx
