Калькуляция себестоимости продукции в Excel — пример расчета, формулы и готовый шаблон
Калькуляция себестоимости в Excel подходит, пока номенклатура укладывается в несколько десятков позиций: формулы прозрачны, данные перед глазами, не нужно покупать отдельную программу.
В статье разберем структуру таблицы, покажем числовой пример расчета на конкретном изделии и дадим шаблон, который можно скачать и заполнить своими цифрами.
учета №1 - Складолог
Перенесем склад бесплатно из Excel в программу
Что входит в себестоимость
Себестоимость — это сумма всех затрат на создание и реализацию продукта в денежном выражении. Сюда попадают не только материалы, но и зарплата рабочих, амортизация оборудования, электроэнергия, аренда, логистика, упаковка. Точный расчет показывает нижнюю границу цены: ниже нее бизнес работает в убыток.
Различают два основных вида. Производственная (цеховая) себестоимость включает только затраты на изготовление: материалы, зарплату производственного персонала, содержание оборудования. Полная (коммерческая) добавляет к ней управленческие расходы, рекламу, доставку и прочие издержки до момента продажи. Для ценообразования нужна именно полная.
-
87%
видят результат в первый месяц -
31%
среднее сокращение издержек -
42%рост скорости операций
-
18%увеличение оборота
При планировании работают с плановой себестоимостью, которая опирается на нормативы и прогнозные цены. Фактическая формируется по итогам периода и может заметно отличаться, если подорожало сырье или случились простои.
Что не включается в калькуляцию
В себестоимость конкретной партии не входят капитальные вложения (покупка нового цеха или линии), выплата дивидендов, погашение кредитов, уплата штрафов. Эти траты отражаются в других финансовых отчетах компании.
Этапы расчета себестоимости
Пошаговый план помогает ничего не пропустить.
Первый шаг — определить объект калькуляции: одна единица товара, партия, заказ или весь выпуск за месяц. Второй — собрать прямые затраты: сырье и материалы по спецификации, зарплату производственного персонала, расходы на работу оборудования. Третий — распределить косвенные затраты (аренда, коммунальные платежи, зарплата управленцев) между продуктами по выбранной базе: пропорционально материалам, человеко-часам или машино-часам. Четвертый — сложить прямые и распределенные косвенные, получив производственную себестоимость. Пятый — добавить коммерческие расходы: упаковку, доставку, рекламу, работу отдела продаж. Получится полная себестоимость. Последний шаг — сверить цифры с первичными документами: накладными, табелями, счетами.
Структура таблицы в Excel
Рабочая книга обычно состоит из нескольких связанных листов. На листе «Нормы» указывают, сколько каждого материала расходуется на единицу товара. На листе «Цены» ведут актуальный прайс поставщиков. Лист «Трудозатраты» содержит тарифные ставки и нормы времени. Итоговый лист «Калькуляция» ссылается на эти данные через формулы, и при изменении цены сырья себестоимость пересчитывается автоматически.
Типовая форма калькуляции содержит следующие статьи: сырье и материалы, возвратные отходы (вычитаются), покупные комплектующие, топливо и энергия на технологические цели, основная и дополнительная заработная плата рабочих, страховые взносы, общепроизводственные расходы, общехозяйственные расходы, потери от брака, коммерческие расходы. Каждая строка заполняется либо вручную, либо формулой со ссылкой на соответствующий лист.
Формулы для калькуляции в Excel
Расчет строится на базовых операциях, но их правильная организация экономит часы работы. Затраты на один материал считаются умножением нормы расхода на цену:
=B2*C2
где B2 — норма расхода на единицу продукции (например, 2,5 кг), а C2 — цена за килограмм.
Суммарные прямые затраты собираются функцией СУММ:
=СУММ(D2:D8)
Для подтягивания цены материала из справочника на другом листе используют ВПР:
=ВПР(A2;Цены!A:B;2;0)
Распределение косвенных расходов пропорционально зарплате основных рабочих выглядит так:
=Косвенные_итого*(D6/СУММ_ЗП_всех_продуктов)
где D6 — зарплата рабочих на данный продукт, а знаменатель — общая зарплата по всем продуктам. Выбор базы распределения (зарплата, машино-часы, стоимость материалов) должен отражать реальную причинно-следственную связь между расходами и продуктом, иначе себестоимость одного изделия окажется завышенной, а другого — заниженной.
Числовой пример расчета
Рассмотрим калькуляцию себестоимости на примере изделия «Полка настенная из массива». Объем выпуска — 200 штук в месяц. Данные взяты условные, но пропорции типичны для мелкосерийного мебельного производства.
Лист «Нормы и цены материалов»
| Материал | Ед. изм. | Норма на 1 шт. | Цена за ед., руб. | Сумма на 1 шт., руб. | Формула в Excel |
|---|---|---|---|---|---|
| Доска сосна сухая | м3 | 0,008 | 14 000 | 112,00 | =B2*C2 |
| Лак акриловый | л | 0,15 | 680 | 102,00 | =B3*C3 |
| Шурупы, крепеж | комплект | 1 | 45 | 45,00 | =B4*C4 |
| Наждачная бумага | лист | 0,5 | 38 | 19,00 | =B5*C5 |
| Упаковка (картон, пленка) | комплект | 1 | 32 | 32,00 | =B6*C6 |
| Итого материалы | 310,00 | =СУММ(D2:D6) | |||
Калькуляционный лист (на 1 изделие)
| N | Статья затрат | Сумма, руб. | Формула / источник |
|---|---|---|---|
| 1 | Сырье и материалы | 310,00 | =СУММ(Нормы!D2:D6) |
| 2 | Возвратные отходы (вычитаются) | -15,50 | =D2*5% (обрезки доски) |
| 3 | Основная зарплата рабочих | 180,00 | 1,5 н/ч * 120 руб./ч |
| 4 | Дополнительная зарплата (отпускные, больничные) | 27,00 | =D4*15% |
| 5 | Страховые взносы | 62,10 | =(D4+D5)*30% |
| 6 | Электроэнергия на технологические цели | 24,00 | 3 кВт*ч * 8 руб. |
| 7 | Общепроизводственные расходы | 90,00 | =D4*50% (база — осн. зарплата) |
| 8 | Общехозяйственные расходы | 54,00 | =D4*30% |
| Производственная себестоимость | 731,60 | =СУММ(D2:D9) | |
| 9 | Коммерческие расходы (доставка, реклама) | 43,90 | =D10*6% |
| Полная себестоимость | 775,50 | =D10+D11 |
При отпускной цене 1 290 руб. прибыль на единицу составит 514,50 руб., рентабельность продукции — 66,3%. Если поставщик поднимет цену на доску с 14 000 до 16 000 руб. за кубометр, себестоимость вырастет на 16 руб. и составит 791,50 руб. В шаблоне достаточно поменять одну ячейку на листе «Цены», и итог пересчитается автоматически.
Пропорции косвенных расходов (50% и 30% от основной зарплаты) взяты для примера. На практике их считают по факту: делят общую сумму общепроизводственных расходов цеха за месяц на суммарный фонд оплаты труда основных рабочих и получают коэффициент, который подставляют в таблицу.
Методы учета запасов и их влияние на себестоимость
Когда одинаковый материал поступает разными партиями по разным ценам, метод списания напрямую влияет на итоговую цифру себестоимости.
По себестоимости каждой единицы
Применяется для штучного дорогого производства: ювелирные изделия, судостроение, индивидуальные заказы. Затраты аккумулируются по конкретному изделию. В Excel под каждый заказ заводят отдельный лист.
Метод средней себестоимости
Самый распространенный способ. Стоимость остатков материала на складе делится на количество, и по этой средней цене происходит списание в производство. В Excel среднюю пересчитывают после каждого нового поступления. Подробнее о том, как организовать данные прихода для такого расчета, разобрано в статье учет прихода и расхода товаров в Excel.
Метод FIFO
Расшифровывается как «First In, First Out» — первый поступил, первый списан. Материалы уходят в производство в порядке поступления, то есть сначала по старым ценам. В условиях роста цен это занижает себестоимость и увеличивает налог на прибыль. Для реализации в Excel нужен подробный журнал партий с датой, количеством и ценой, а также формулы на базе СУММПРОИЗВ или макрос для корректного списания. Это заметно сложнее, чем метод средней.
Когда Excel справляется, а когда нет
Таблица работает хорошо, пока номенклатура материалов не превышает нескольких десятков позиций, с файлом работает один-два человека, а операций по приходу-расходу набирается не больше 30-50 в день. В этих условиях Excel дает все, что нужно: гибкость формул, наглядность, нулевую стоимость.
Проблемы начинаются при масштабировании. Если над файлом работают несколько человек одновременно, версии расходятся и правки теряются. Данные о фактическом расходе материалов приходится переносить из складских документов вручную, и этот ручной ввод — главный источник ошибок. Случайное удаление формулы или опечатка в цене может исказить себестоимость всей линейки, а заметят это, только когда квартальный отчет не сойдется.
Если вы тратите больше времени на поддержание таблиц, чем на анализ результатов, стоит посмотреть на признаки того, что Excel перестал справляться. Специализированные системы, такие как Складолог, автоматически списывают материалы по нормам на конкретный заказ, фиксируют трудозатраты через табели и передают данные в модуль учета. Калькуляция себестоимости происходит в реальном времени, без ручного переноса цифр из одной таблицы в другую.
Практические рекомендации
Начинать расчет себестоимости в Excel — нормальный и правильный подход. Построение собственной таблицы дает понимание логики процесса, которое пригодится даже после перехода на любую программу. Несколько советов, которые сберегут время:
- Закрепите ячейки с формулами через защиту листа. Оставьте открытыми только поля для ввода данных, чтобы случайный клик не сломал расчет.
- Храните нормы расхода и цены на отдельных листах. Тогда при обновлении прайса достаточно поменять одну ячейку, а не искать все упоминания материала по файлу.
- Проверяйте формулы на крайних значениях. Подставьте ноль в количество, отрицательное число в возвратные отходы — убедитесь, что итог не уходит в абсурд.
- Ведите журнал версий. Перед каждым крупным обновлением сохраняйте копию файла с датой в названии.
- Если нужно не только считать себестоимость, но и вести складской учет целиком, загляните в руководство как вести склад в Excel, там разобрана связка справочника, журналов движения и отчетов.
Ссылки на источники и исследования
- Документация Microsoft Office по работе с формулами в Excel
