Расчёт себестоимости продукции в Excel скачать: образец с калькуляцией

Расчёт себестоимости продукции в Excel — это таблица, которая по нормам расхода и ценам ресурсов считает прямые и косвенные затраты, полную себестоимость партии и себестоимость одного изделия, а на отдельном листе сравнивает план с фактом по статьям. В шаблоне пять листов: инструкция, калькуляция, сводка, план-факт и справочники. Скачать файл можно ниже, заполнив имя и телефон.

Отправляя форму, вы соглашаетесь на обработку персональных данных. Телефон нужен, чтобы прислать файл и, если будет вопрос по расчёту, ответить на него. Никаких рассылок.

Коротко

Шаблон считает себестоимость по двум путям: прямые затраты — по норме расхода и цене ресурса, косвенные — суммой на объём выпуска. Итоги делятся на прямые и косвенные по группе статьи, полная себестоимость получается сложением, а себестоимость единицы — делением на объём выпуска. Отдельный лист сравнивает план с фактом по каждой статье и показывает отклонение в рублях и процентах. Нормы, цены, ставки взносов и база распределения косвенных — ваши: шаблон считает арифметику, но не подтверждает нормы и не знает рыночную цену сбыта.

Что внутри шаблона

Пять листов, каждый отвечает за своё. Калькуляция — то, что заполняется; остальное считается и показывает результат.

• Калькуляция (заполняется): изделие, объём выпуска и период, дальше строки по статьям затрат — статья, наименование, единица измерения, норма расхода на изделие, цена ресурса, сумма на единицу и сумма на весь объём. Для косвенных расходов есть отдельная колонка: сумма на объём вводится напрямую, без нормы. Внизу — итоги прямых, косвенных и полной себестоимости, а также доля прямых затрат. Расчёт рассчитан на 50 строк.
• Сводка: полная себестоимость партии, себестоимость единицы, структура себестоимости по 14 статьям с долями, а также цена и маржа при заданной наценке и ставке НДС.
• План-факт: план подтягивается из сводки по каждой статье, факт вносится вручную. Считаются отклонение в рублях и процентах, вывод текстом («перерасход», «экономия», «совпадение»), а отклонение больше пяти процентов подсвечивается.
• Справочники: 14 статей затрат с группой — «Прямые» или «Косвенные», и 17 единиц измерения. Из этих списков собираются выпадающие поля на листе «Калькуляция».
• Инструкция: шесть шагов заполнения и список того, что нужно проверить перед использованием — нормы, цены, ставки, база распределения.

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

Формулы, которые уже прописаны

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

• Сумма на единицу продукции. Норма расхода × цена ресурса. Если нормы нет, а сумма на объём внесена — делится на объём выпуска. Так в одной таблице уживаются прямые затраты, которые нормируются, и косвенные, которые задаются суммой.
• Сумма на весь объём. Сумма на единицу × объём выпуска. Объём — отдельная ячейка, и от неё зависят все суммы по партии.
• Группа статьи. Подтягивается из справочника по названию статьи. От группы зависит, попадёт затрата в прямые или в косвенные, — и это единственное место, где задаётся такое разделение.
• Итоги прямых и косвенных затрат. Функция СУММЕСЛИ по колонке «Группа»: разделение пересчитывается само, если у статьи поменять группу.
• Полная себестоимость. Прямые плюс косвенные — отдельно на единицу продукции и на весь объём.
• Доля статьи в себестоимости. Сумма на единицу делится на полную себестоимость единицы. По долям сразу видно, какие статьи определяют себестоимость, а какие на неё почти не влияют.
• Цена и маржа. Цена без НДС = себестоимость × (1 + наценка), НДС считается от неё, маржа — это доля прибыли в цене без НДС. Меняется одна ячейка с наценкой, остальное пересчитывается.
• План-факт по статьям. Отклонение = факт минус план; процент отклонения считается от плана; вывод формируется текстом. Отклонение больше пяти процентов подсвечивается красным, экономия — зелёным.

Отдельно про подсветку и про колонку «План в зачёт». Подсветка нормы и цены — не украшение: строка, где норма внесена, а цена забыта, даёт заниженную себестоимость, и заметить это на глаз почти невозможно. А «план в зачёт» — это план только тех статей, по которым факт уже внесён: без этой оговорки итоговое отклонение показывало бы экономию по несобранным данным.

Как заполнить шаблон

1. Заполните шапку. Укажите изделие, объём выпуска партии и период. Объём — ключевая ячейка: от неё считается сумма на весь объём и себестоимость единицы.
2. Внесите прямые затраты по норме. Для материалов, комплектующих и оплаты труда заполните норму расхода на одно изделие и цену ресурса. Сумма на единицу и на объём посчитаются сами.
3. Внесите косвенные затраты суммой. Амортизацию, общепроизводственные, общехозяйственные и коммерческие расходы удобнее ставить суммой на объём — для этого есть отдельная колонка. Норма для таких строк не нужна.
4. Проверьте статьи и группу. Статья выбирается из выпадающего списка, группа подтягивается из справочника. Строка без выбранной статьи в итоги не попадёт — это самая частая причина того, что сумма по статьям не сходится с полной себестоимостью.
5. Посмотрите сводку. Проверьте полную себестоимость, себестоимость единицы и структуру: не выглядит ли какая-то статья подозрительно большой или маленькой. Дальше поставьте свою наценку и посмотрите, что останется в цене.
6. Ведите план-факт. По итогам месяца внесите фактическую сумму по статьям и посмотрите отклонения. Регулярный план-факт по статьям полезнее, чем одна точная калькуляция раз в год.

Что важно понимать про границы шаблона

Честно о том, чего шаблон не делает, — чтобы не было разочарования:

• Он не заменяет бухгалтерский и налоговый учёт. Это управленческий расчёт для структуры затрат и себестоимости единицы, без проводок и без требований учётной политики.
• Он не подтверждает ваши нормы расхода. Норму задаёт технология: норму расхода материала даёт конструктор или технолог, норму времени — нормировщик. Шаблон считает то, что вы в него вписали.
• Цены ресурсов — на дату расчёта. Калькуляция, собранная на прошлогодних ценах, даёт правдоподобную и неверную себестоимость.
• База распределения косвенных расходов — ваш выбор. В шаблоне косвенные вносятся суммой на объём. Если вам нужно распределение процентом от прямых затрат или от оплаты труда, формулу нужно поставить самому, и от выбранной базы себестоимость разных изделий изменится по-разному.
• Он не знает рыночную цену. Наценка и маржа — это арифметика, а не проверка спроса: шаблон покажет, что останется в цене при выбранной наценке, и ничего не скажет о том, купит ли продукцию рынок.
• Для большой номенклатуры нужна система, а не таблица. Когда изделий десятки и калькуляция меняется каждый месяц, учёт себестоимости ведут в учётной системе, а таблица остаётся для отдельных расчётов. Про то, как это выглядит на производстве, у нас есть отдельный разбор в разделе ниже.

Четыре ошибки, из-за которых калькуляция расходится с фактом

• Нормы из прошлогодней калькуляции. Самый частый источник расхождения: нормы устарели после замены материала или оснастки, а цены изменились после повышения у поставщика. Проверять обе части — и норму, и цену.
• Косвенные внесены суммой и не пересмотрены при смене объёма. Постоянные расходы на единицу падают с ростом выпуска, поэтому в калькуляции на новый объём их нужно пересматривать, иначе себестоимость единицы будет искажена.
• Строка без выбранной статьи. Формула не понимает, куда отнести затрату, и строка выпадает из итогов. Признак — сумма по статьям на листе «Сводка» не совпадает с полной себестоимостью.
• Норма без цены. Сумма не считается вовсе, и себестоимость выглядит ниже реальной. Для этого и нужна подсветка: она показывает пары «норма есть, цена не заполнена» и наоборот.

Частые вопросы

Что делать дальше

Калькуляция закрывает арифметику, но не отвечает на вопрос «почему себестоимость выросла именно на этой статье и что с этим делать». Обычно расхождение видно не в расчёте, а в производстве: нормы расхода нарушаются на конкретном участке, брак списывается без разбора причин, а фактические затраты собираются раз в месяц и уже постфактум. Про то, как это выглядит и что с этим делают, у нас разобрано отдельно: контроль качества на производстве, диспетчеризация производства и планирование производства в 1С:ERP. Если выбираете программное обеспечение под производство, пригодится обзор пяти решений с ИИ для производства, а общий отраслевой контекст собран в разделе ИИ в производстве. И если вы работаете в стройке, а не в производстве, — есть шаблон сметы строительства в Excel с расчётом стоимости работ.