Себестоимость продукции формула расчета excel
Содержание
версия 4.1 от 21 февраля 2018
Таблица предназначена для расчета себестоимости и рентабельности производства товаров и оказания услуг.
Расчеты выполняются по общепринятым в мировой практике методикам, поэтому таблица не зависит от национального законодательства.
- Прямую, производственную, полную себестоимость
- Себестоимость по методу Директ-костинг (Direct Costs)
- Выручку, прибыль, рентабельность
- Маржинальную прибыль и коэффициент маржинальной прибыли
- Точку безубыточности
Суммарно и по каждому изделию считает
- прямую, производственную и полную себестоимость
- выручку, прибыль, наценку, рентабельность продаж
- маржинальную прибыль, коэффициент маржинальной прибыли
- точку безубыточности, запас прочности, операционный рычаг
Строит графики — 12 суммарных и по 16 графиков на каждое изделие
В расчете себестоимости самый сложный и трудоемкий момент — правильный расчет полной себестоимости с учетом косвенных затрат. Правда, есть метод директ-костинг в котором косвенные затраты не учитываются, но даже при расчете этим методом нужно сделать очень много расчетов. И, надо отметить, директ-костинг подходит не во всех случаях.
Кроме того, если часть материалов и комплектующих закупается за валюту, придется регулярно пересчитывать рублевые цены, и как следствие – себестоимость.
Ситуация еще более усложняется, если Вы хотите оптимизировать ассортимент выпускаемых изделий — подобрать объем выпуска для каждого изделия, чтобы максимально увеличить прибыль. В этом случае Вам предстоит проделать еще больше расчетов.
Надо еще учесть, что человек больше приспособлен не для расчетов, а для анализа, сравнения и оценки вариантов, для принятия решений. Лучше всего расчеты доверить компьютеру, а анализ результатов расчетов предоставить человеку. И совсем хорошо — получить эти результаты в наглядном виде, в виде диаграмм.
Я предлагаю Вам мощную, удобную, наглядную Excel-таблицу для расчета себестоимости и рентабельности продукции. Это уже четвертый вариант, учитывающий отзывы покупателей. Моя таблица полностью избавляет Вас от утомительных расчетов и дает сразу готовый результат — себестоимость, доходность, точку безубыточности и графики, показывающие все эти величины.
Таблица построена следующим образом:
- Отдельный лист для каждого изделия на котором производятся все расчеты касающиеся изделия.
- Суммарный лист со списком изделий, итоговыми показателями по каждому изделию и общими суммарными показателями.
- Лист настроек содержащий списки единиц измерения, списки и курсы валют, списки материалов и т.п.
Новое в версии 4.1:
При изменении имени листа Excel не исправляет автоматически соответствующую гиперссылку и она перестает работать. То есть, если после создания листа нового изделия изменить имя листа, гиперссылка на лист изделия в списке изделий переставала работать. Теперь гиперссылки на листы изделий можно исправить, нажав кнопку "Исправить гиперссылки изделий".
Новое в версии 4.0 от 12.04.2016:
- Можно добавлять новые изделия (услуги) как копию любого существующего изделия (услуги). До этого можно было копировать только первое изделие. Новшество будет очень полезным, если есть несколько групп сходных изделий. Теперь можно создавать лист нового изделия как копию изделия наиболее близкого по составу и технологии.
- Наименование изделия (услуги) в списке изделий теперь служит гиперссылкой. Можно сразу перейти на лист соответствующего изделия, просто кликнув по его названию в списке.
Новое в предыдущих версиях:
версия 3.0 от 13.10.2015
Заполнение элементов затрат из справочников (списков). Затраты на материалы выбираются из справочника материалов, затраты на комплектующие из справочника комплектующих и т.п. При этом сразу подставляются цена и валюта.
Такое заполнение комплексно решает три проблемы:
- Облегчает и существенно ускоряет ввод затрат, не надо по многу раз вводить, например, одни и те же материалы.
- Абсолютно исключает ошибки при вводе наименований и цен.
- Делает ненужным при изменении цены искать материал или комплектующее с изменившейся ценой по всем изделиям. Во всех изделиях, где используется этот материал, его цена сразу изменится автоматически.
По моим оценкам, скорость заполнения таблицы повышается в 5-7 раз. Это без учета времени на поиск ошибок вроде таких: ввели цену 101 USD вместо 10.1 USD или 10 USD вместо 10 RUB.
А время, которое тратится на поиск затрат с изменившейся ценой, просто сокращается до нуля.
версия 2.0 от 03.09.2014
В связи с резкими изменениями курсов валют во второй половине 2014 года меня попросили добавить возможность:
- Расчета затрат в нескольких валютах.
- Расчета итогов в рублях и одной из устойчивых валют.
Теперь расчеты выполняются по следующей схеме:
- Для каждой позиции расходов задается сумма или цена в валюте и валюта.
- Таблица пересчитывает сумму или цену в основную валюту (ОВ), в исходном варианте таблицы это рубли.
- Далее расчеты ведутся в основной валюте.
- Общие итоги и результаты некоторых промежуточных вычислений пересчитываются в выбранную валюту итогов (ВИ).
- Таким образом, получаются итоги в основной валюте и в еще одной выбранной валюте (в исходном варианте таблицы в долларах).
Можно использовать в расчетах до 10 валют одновременно. Наименования валют и курсы можно изменять.
Описание таблицы
- Список и курсы валют.
- Список материалов, комплектующих, видов топлива и энергия на технологические цели и т.п. Указываете цены и валюту.
- Косвенные затраты для всего предприятия по видам (ОПР, Общехозяйственные и т.д.) и подвидам.
- Прямые затраты для каждого изделия по видам (материалы, комплектующие и т.д.) и подвидам.
- План выпуска (объем партии) для каждого изделия.
- Цену для каждого изделия. Цена может задаваться двумя способами: фиксированное значение или полная себестоимость + наценка.
Таблица рассчитывает на одно изделие, на партию и суммарно по предприятию
- Сумму косвенны затрат по видам (общепроизводственные, общехозяйственные, расходы на продажу и т.д.)
- Сумму прямых затрат по видам (материалы, комплектующие и т.д.)
- Доли видов прямых затрат в технологической (прямой) себестоимости (доля материалов в технологической себестоимости, доля комплектующих в технологической себестоимости и т.д.)
- Доли видов прямых затрат в полной себестоимости (доля материалов в полной себестоимости, доля комплектующих в полной себестоимости и т.д.)
- Технологическую (прямую) себестоимость
- Производственную себестоимость
- Полную себестоимость
- Выручку
- Прибыль
- Маржинальную прибыль
- Наценку
- Рентабельность продаж
- Коэффициент маржинальной прибыли
- Точку безубыточности
- Запас прочности
- Операционный рычаг
- Долю косвенных расходов приходящихся на изделие
- Сумму косвенных расходов приходящихся на изделие
- Два листа с пояснениями.
- Лист справочников (списков) валют, материалов и т.п.
- Лист с суммарными расчетами по всем изделиям. На этом листе можно добавлять и удалять изделия соответствующими кнопками.
- Лист с суммарными графиками
- Листы с подробными данными по каждому изделию, по одному листу на изделие. В исходном состоянии таблица включает 5 изделий. Новые листы изделий добавляются и удаляются кнопками на листе с суммарными расчетами.
Скриншоты (изображения) листов ниже.
Листы изделий содержат расчеты и графики. 16 графиков затрат, себестоимости, доходности в расчете на одно изделие и на партию, в основной валюте и валюте итогов.
Изделия и соответствующие листы добавляютя и удаляютя кнопками, размещенными на суммарном листе. При этом все необходимые формулы автоматически корректируются.
В исходном состоянии таблица включает 5 изделий. Последнее пятое изделие является разовым заказом не приводящим к увеличению косвенных затрат и при расчете полной себестоимости учитываются только прямые затраты, что отражается на его доходности и точке безубыточности. В качестве базы распределения косвенных затрат принята технологическая себестоимость (сумма прямых затрат) партии изделия. Как изменить базу распределения написано в таблице. Посмотрите подробнее в Демо-версии таблицы
Таблицы работают на Excel для Windows (2007, 2010, 2013, 2016). На ОpenOffice не работают. В настройках Windows должен быть установлен язык системы (язык программ не поддерживающих юникод) — русский. Сейчас я переделываю таблицы для других языков и для работы под Mac. Чтобы получать сообщения о выходе новых версий подпишитесь на рассылку внизу страницы.
Ниже показаны лист таблицы с суммарными расчетами, лист с графиками суммарных расчетов и лист первого изделия. Листы изделий со второго по пятый аналогичны листу первого изделия и не показаны в целях экономии места. Таблица содержит еще 2 листа с подробными инструкциями.
Листы защищены от случайных изменений. Ячейки для ввода (не защищенные) помечены фоном . При желании Вы можете снять защиту, как это сделать написано на первом листе с инструкциями.
Лист со справочниками
Валюты, единицы измерения, материалы и т.п. Можно добавлять строки, как это правильно сделать сказано на этом же листе.
Лист с суммарными вычислениями
Вводим косвенные затраты. Можно добавлять строки, как это правильно сделать сказано на этом же листе.
Таблица считает суммарные данные по всем изделиям.
Таблица считает итоговые значения себестоимости и доходности по каждому изделию.
Лист Первого изделия
Вводим наименование, план выпуска (объем партии) и цену. Цену можно задать двумя способами: цена реализации – НДС или полная себестоимость + наценка.
Вводим прямые затраты: материалы, комплектующие, энергия, зарплата, начисления, прочие пропорциональные объему, другие относимые на всю партию. Можно добавлять строки, как это правильно сделать сказано на этом же листе.
Таблица считает косвенные затраты, приходящиеся на одно изделие и на партию, себестоимость и доходность.
Как получить таблицу
Если Вам нужен инструмент для быстрого расчета себестоимости и доходности, инструмент, который позволит не утонуть в расчетах и сосредоточиться на анализе — вы можете получить описанную выше Excel-таблицу «Расчет себестоимости и рентабельности продукции (услуг)», заплатив 2995 руб. одним из указанных ниже способов.
Думаю, что описанная таблица в мультивалютном варианте нужна отнюдь не всем. Кому-то нужен просто расчет в одной валюте.
Поэтому таблица продается в двух вариантах.
- Вариант Мультивалютный 2995 руб.
- Вариант Одновалютный 2495 руб.
Я знаю о чем многие подумали: хорошо бы узнать мнение покупателей этой таблицы.
- Доставка в течение нескольких часов после прихода платежа. Сроки прохождения платежа для различных способов оплаты см. ниже.
- Все таблицы заполнены данными, которые служат примером и помогают понять логику работы.
- Таблицы снабжены подробными пояснениями по заполнению и работе.
- Отвечаю на любые вопросы по работе с таблицами. Конечно, предполагается что вы знакомы с предметной областью и имеете базовые навыки работы с Excel.
При выходе новой версии:
- Стоимость обновления зависит от того, насколько увеличивается функциональность новой версии таблицы.
- Сообщения о новых версиях получают только подписчики рассылки. Форма подписки внизу страницы.
Советую заказать таблицу прямо сейчас, потому что цена на нее периодически повышается, в октябре 2014 года она стоила всего 695 руб.
Таблица «Расчет себестоимости и рентабельности продукции (услуг)»
Вариант Мультивалютный: 2995 руб.
Вариант Одновалютный: 2495 руб.
Таблицы работают на Excel для Windows (2007, 2010, 2013, 2016). На ОpenOffice не работают. В настройках Windows должен быть установлен язык системы (язык программ не поддерживающих юникод) — русский. Сейчас я переделываю таблицы для других языков и для работы под Mac. Чтобы получать сообщения о выходе новых версий подпишитесь на рассылку внизу страницы.
Оформить заказ Одновалютный
Оформить заказ Мультивалютный
Телефон для вопросов оплаты и доставки +7 927 055 9473
почта v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru)
- Robokassa
- Карты Visa, MasterCard, Maestro, Мир
- Интернет-клиент Альфа-Банк, Русский Стандарт, Промсвязьбанк и др.
- Терминалы
- Салоны связи Евросеть, Связной
- Электронные деньги Яндекс.Деньги, QIWI, WebMoney, Wallet One (W1), Элекснет
- Банк
на расчетный счет Прохоров Владимир Викторович (ИП), ИНН 645400330452,
р/с 40802810711010053426, Филиал «Бизнес» ПАО «Совкомбанк» г. Москва,
К/с 30101810045250000058, БИК 044525058.
Обратите внимание, что указание формы собственнсти (ИП) является обязательным.
После оформления заказа можно распечатать счет для юр. лиц
или квитанцию по форме ПД-4 для физ. лиц.
Также можно получить счет, написав на v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru).
При платежах не из России в назначении платежа укажите код валютной операции
Например, назначение платежа:
Где VO — заглавные латинские буквы, 10100 — цифры. Фигурные скобки обязательны. Пробелы внутри фигурных скобок не допускаются.
Cсылка на скачивание таблиц будет отправлена на указанный в заказе e-Mail после поступления денег. Если письма долго нет, проверьте папку СПАМ.
Для способа 1 (Robokassa) письмо со ссылкой отправляется автоматически, сразу после поступления денег. Обычное время поступления денег — минуты.
Для способа 2 (Банк) письмо со ссылкой отправляется после поступления платежа на расчетный счет. Время поступления денег 1-2 рабочих дня. Юридическим лицам и ИП высылаю pdf-копии накладной по форме Торг 12. Оригинал накладной высылается по запросу, в запросе укажите, пожалуйста, почтовый адрес.
Если нужны еще какие-либо документы, прошу согласовать этот вопрос ДО ОПЛАТЫ.
Дальнейшая переписка по поводу полученных таблиц
Дальнейшая переписка по поводу полученных таблиц (вопросы по работе с таблицами, обновления и т.п.) происходит через адрес, на который были отправлены таблицы. При переписке по эл.почте сохраняйте, пожалуйста, историю переписки, т.е. включайте в письмо все предыдущие письма и ответы на них. При этом помещайте Ваше сообщение вверху (перед историей).
Оформить заказ Одновалютный
Оформить заказ Мультивалютный
Владимир Прохоров, 21 февраля 2018
P.S.
Если вы хотите получить рабочую версию одной таблицы и демо-версии всех остальных моих таблиц, подпишитесь на ознакомительную серию из 9 писем с кратким описанием таблиц
Кроме этого, прошу учесть, что извещения об акциях скидок и выходе новых версий получают только подписчики. Никаких специальных объявлений на сайте не делается.
Некоторые письма рассылки будут содержать предложения с ограниченным сроком, поэтому рекомендую подписаться на тот адрес, который вы регулярно просматриваете.
Не рекомендую подписываться на корпоративные почтовые ящики, т.к. есть вероятность что почтовый сервер посчитает рассылку спамом. Т.е. не вы, а системный администратор будет решать какие письма вы можете получать.
В современных условиях осуществления хозяйственной деятельности на предприятиях различной отраслевой принадлежности актуальной остается проблема снижения затрат на производство продукции, увеличения прибыли и рентабельности производства. В связи с этим ответственным участком бухгалтерского учета на предприятии является расчет, калькуляция себестоимости.
Понятие и что входит в себестоимость
Под себестоимостью продукции понимается совокупность всех произведенных хозяйствующим субъектом на ее производство затрат. В состав затрат, включаемых в себестоимость продукции, включаются:
- сумма затраченного при производстве продукции сырья или материалов;
- сумма начисленной производственным рабочим, занятым производством продукции, заработной платы (как основной, так и дополнительной);
- начисленные суммы отчислений во внебюджетные фонды с суммы заработной платы производственным рабочим, занятым производством продукции;
- суммы израсходованных при производстве определенного вида продукции топлива и электроэнергии;
- сумма расходов на освоение и подготовку производства новых видов продукции;
- суммы общепроизводственных и общехозяйственных расходов, относимых на определенный вид продукции в соответствии с рассчитанным коэффициентом;
- затраты, которые были произведены на упаковку, транспортировку готовой продукции определенного вида, и прочие затраты.
Для расчёта себестоимости продукции необходимо осуществить сложение всех затрат, связанных с его производством, сбытом.
Себестоимость: формула
Необходимо отметить, что можно произвести расчет следующих видов себестоимости:
При расчете производственной себестоимости в ее состав включаются все затраты на производство продукции, за исключением расходов на продажу (коммерческих расходов).
Для подсчета полной себестоимости рассчитанный показатель производственной себестоимости увеличивается на сумму коммерческих расходов (расходов на продажу).
Себестоимость продукции — формула расчета (1) производственной себестоимости:
С/С производственная = М + П – В + Э + Т + ЗПос + ЗПдоп + Отч + РПОП + ПБ + ПР + ОПР + ОХР, (1)
где М – затраты на сырье и материалы;
П – затраты на полуфабрикаты;
В – сумма возвратных отходов;
Э – затраты на электроэнергию;
Т – затраты на топливо;
ЗПосн – затраты на выплаты основной заработной платы рабочих, занятых производством продукции;
ЗПдоп – затраты на выплаты дополнительной заработной платы рабочих, занятых производством продукции;
Отч – сумма отчислений в фонды, которые являются внебюджетными, на основную и дополнительную заработную плату производственных рабочих;
РПОП – сумма расходов на подготовку и освоение производства;
ПБ – сумма потерь от брака;
ПР – сумма прочих затрат;
ОПР – часть общепроизводственных расходов;
ОХР – часть общехозяйственных расходов.
Полная себестоимость рассчитывается по формуле 2:
СС полная = СС производственная + РК, (2)
где СС производственная – производственная себестоимость;
РК – расходы коммерческие.
Расчет себестоимости продукции на производстве: пример
Рассмотрим пример расчета показателя себестоимости продукции на основе исходных данных, представленных в таблице 1.
Таблица 1. Исходные данные для определения себестоимости продукции, тыс. руб.
| Показатель | Март 2017 года | Апрель 2017 года |
|---|---|---|
| 1. Сырье и материалы | 456356 | 480679 |
| 2. Покупные полуфабрикаты | 127568 | 187654 |
| 3. Возвратные отходы | 20679 | 21754 |
| 4. Затраты на электроэнергию на технологические цели | 4580 | 4860 |
| 5. Затраты на топливо на технологические цели | 2467 | 2070 |
| 6. Основная заработная плата производственных рабочих | 34578 | 35560 |
| 7. Дополнительная заработная плата производственных рабочих | 11098 | 10655 |
| 8. Отчисления во внебюджетные фонды на сумму основной и дополнительной заработной плат производственных рабочих | 13795 | 13957 |
| 9. Расходы на освоение и подготовку производства новых видов продукции | 3560 | 3890 |
| 10. Общепроизводственные расходы | 6777 | 7132 |
| 11. Общехозяйственные расходы | 7907 | 7698 |
| 12. Расходы на продажу (коммерческие расходы) | 3540 | 4135 |
| 13. Производственная себестоимость (1+ 2 –3 + 4 + 5 + 6 + 7 + 8 + 9 +10 +11) | 648007 | 732401 |
| 14. Полная себестоимость (13+12) | 651547 | 736536 |
Рассчитанная полная себестоимость (показатель 14) отражает сумму всех затрат на весь объем выпуска продукции. При условии производства предприятием в марте 2017 года 560 тыс. шт., а в апреле 550 тыс. шт. полная себестоимость одной единицы продукции будет составлять:
- март 2017 года: 651547 / 560 = 1163,47 руб.;
- апрель 2017 года: 736536 / 550 = 1339,15 руб.
Калькуляция себестоимости
По окончании отчетного периода производится подсчет в денежном выражении затрат на весь выпуск определенного вида продукции или одной единицы определенного вида продукции в особых формах документов, возможно использование возможностей программного обеспечения. В этом случае осуществляется процесс калькулирования себестоимости продукции.
Калькуляция себестоимости продукции – определение расходов в денежном эквиваленте, приходящихся на единицу товаров, работ либо услуг. В расчет включаются прямые и косвенные затраты. Прямые – расходы на материалы, зарплата рабочих и т.п. Косвенные расходы: плановая прибыль, транспорт и др.
Подробно рассматривать калькуляционные статьи мы не будем. Автоматизируем процесс расчета плановой себестоимости продукции с помощью формул Excel. Наша задача – составить таблицу средствами Excel так, чтобы при подстановке данных автоматически считалась себестоимость товаров, работ, услуг.
Калькуляция себестоимости товаров в торговле
Учиться считать себестоимость лучше начинать со сферы торговли. Здесь меньше статей затрат. По сути – закупочная цена, выставленная поставщиком; транспортные расходы на доставку товара на склад; пошлина и таможенные сборы, если мы ввозим товар из-за рубежа.
Для расчета заполним следующую таблицу:
Берем некую группу товаров. И по каждой из них рассчитаем себестоимость. Последний столбец – плановый коэффициент себестоимости – покажет уровень затрат, который понесет компания на доставку продукции.
- Транспортные расходы, как сообщил отдел логистики, составят 5% от закупочной цены.
- Размер пошлины будет отличаться по разным группам товаров. Для товара 1 и 4 – 5%. Для товара 2 и 3 – 10%. Чтобы было удобнее проставлять проценты, отсортируем данные по столбцу «Наименование товара».
- Для вычисления используем формулу: закупочная цена + транспортные расходы в денежном выражении + пошлина в денежном выражении.
- Формула для расчета планового коэффициента – себестоимость в денежном выражении / закупочную цену.
Уровень затрат на доставку товаров 1 и 4 составит 10%, 2 и 3 – 15%.
Формулы расчета плановой себестоимости продукции в Excel
Каждая компания рассчитывает плановую себестоимость по-своему. Ведь предприятия несут разные затраты в зависимости от вида деятельности. Любая калькуляция должна содержать расшифровку расходов на материалы и заработную плату.
Расчет плановой себестоимости начинается с определения стоимости используемого сырья и материалов, которые нужны для производства товаров (которые непосредственно участвуют в технологическом процессе). Затраты на сырье включаются в себестоимость по утвержденным на предприятии нормам минус технологические потери. Эти данные можно взять в технологическом или производственном отделе.
Отразим нормы расхода сырья в таблице Excel:
Здесь нам удалось автоматизировать только один столбец – расход с учетом технологических потерь. Формула: =E3+E3*F3.
Обратите внимание! Для столбца «Потери» мы выставили процентный формат. Только в таком случае программа посчитает правильно. Нумерацию строк начинаем выше шапки. Если данные перепутаются, их можно восстановить по номерам.
Зная нормы, можем рассчитать стоимость материалов (расчет ведется для тысячи единиц товара):
В этой таблице вручную придется заполнить только одну графу – «Цена». Все остальные столбцы ссылаются на данные листа «Нормы». В графе «Стоимость» работает формула: =D3*E3.
Следующая статья прямых затрат – зарплата производственных рабочих. Учитывается основная заработная плата и дополнительная. По каким принципам начисляется зарплата (сдельная, повременная, от выработки), можно узнать в бухгалтерии.
В нашем примере расчет заработной платы ведется по нормам выработки: сколько должен сделать работник определенной квалификации за единицу рабочего времени.
Данные для вычислений таковы:
Расценка рассчитывается по формуле: =C3*D3.
Теперь мы можем посчитать основную зарплату рабочих:
Чтобы заполнить первые два столбца, не считая номер по порядку, мы связали данные этой таблицы с данными предыдущей. Формула для начисления премии: =C3*30%. Основной зарплаты – =C3+D3.
Дополнительная заработная плата – это все выплаты, положенные по закону, но не связанные с производственным процессом (отпуска, вознаграждения за выслугу лет и т.п.).
Другие данные для расчета калькуляции себестоимости мы сразу внесли в таблицу:
В столбце «Расчет показателя» указано, откуда мы берем данные. Если мы ссылаемся на другие таблицы, то используем оттуда итоговые суммы.
Шаблон расчета себестоимости продукта с формулами:
Для расчета калькуляции себестоимости упаковок взяты условные показатели амортизации ОС, процентов дополнительной зарплаты и налогов, обязательных страховых взносов.