Комплект таблиц для фин.-эконом. расчетов в Excel

Финансовые экономические расчеты в Excel


1. Платежный календарь
2. Себе­стоимость и доход­ность. Мульти­валютная версия
3. Окупаемость инвести­цион­ных проектов
4. Финансовый анализ в форматах МСФО или РСБУ
5. Точка безубыточности и рентабельность продаж при большом ассортименте

 Посмотрите подробнее…

Расчет себестоимости продукции в Excel

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

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

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

Расчет себестоимости включает затраты по следующим статьям:

  • Сырье и основные материалы.
  • Вспомогательные материалы.
  • Топливо, энергия на технологические цели.
  • Полуфабрикаты собственного производства.
  • Покупные изделия: комплектующие, полуфабрикаты и т. д.
  • Оплата работы сторонних организаций.
  • Возвратные отходы (вычитаются).
  • Оплата труда производственных рабочих. Основная заработная плата (оклад, тариф), дополнительная заработная плата (надбавки и т.п.) производственного персонала.
  • Начисления на зарплату: платежи в пенсионный фонд и ФОМС — фонд обязательного медицинского страхования.
  • Подготовка и освоение производства.
  • Эксплуатация производственных машин и оборудования.
  • Цеховые (общепроизводственные) расходы.
  • Общехозяйственные расходы, в том числе на содержание аппарата управления.
  • Потери от брака.
  • Коммерческие расходы.
Посмотрите Excel таблицу
«Расчет себестоимости продукции»
Полная себестоимость, Директ-костинг,
Маржинальная прибыль, Рентабельность


В состав общепроизводственных (цеховых) расходов включают:

  1. Расходы на содержание и эксплуатацию оборудования:
    • амортизация оборудования и внутризаводского транспорта;
    • издержки на эксплуатацию оборудования в виде смазочных, охлаждающих и других вспомогательных материалов;
    • заработная плата рабочих, обслуживающих оборудование, начисления на зарплату: платежи в пенсионный фонд, ФОМС — фонд обязательного медицинского страхования;
    • электроэнергия, тепло, пар, сжатой воздух, другие виды энергии;
    • стоимость услуг вспомогательных производств;
    • внутризаводское перемещение сырья, материалов, полуфабрикатов, комплектующих;
    • ремонт оборудования, техосмотры.
  2. Цеховые расходы на управление:
    • зарплата аппарата управления цехов и начисления на зарплату: платежи в пенсионный фонд, ФОМС;
    • амортизация зданий, сооружений, инструментов, содержание и ремонт зданий и сооружений;
    • обеспечение условий труда и техники безопасности;
    • спецодежда, спецобувь, другие аналогичные расходы.

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

  1. Административно-управленческие:
    • зарплата работников аппарата управления предприятием и начисления на зарплату: платежи в пенсионный фонд, ФОМС;
    • материально-техническое и транспортное обслуживание деятельности работников аппарата управления;
    • расходы на командировки;
    • содержание охраны.
  2. Общехозяйственные:
    • амортизация основных средств и нематериальных активов общезаводского характера;
    • ремонт зданий, сооружений;
    • содержание и обслуживание технических средств управления, например, вычислительных центров, узлов связи;
    • коммунальные услуги: освещение, отопление, водоснабжение;
    • оплата аудиторских, информационных, консультационных услуг;
    • оплата услуг банка;
    • расходы по поиску, набору, обучение кадров;
    • природоохранные: расходы на содержание очистных сооружений, на уничтожение экологически опасных отходов, природоохранные платежи;
    • налоги и обязательные платежи: страхование, транспортный и земельный налоги.

Коммерческие расходы — это затраты, связанные с продажей товаров, продукции, услуг. В расчет включают следующие затраты:

  • на фасовку и упаковку;
  • на аренду и содержание складских и торговых помещений;
  • на охрану и хранение товаров;
  • на доставку в пункт отправления (на станцию, в порт);
  • на погрузку в транспортные средства;
  • на оплату труда продавцов компании производителя;
  • на комиссионные платеж партнерам и посредникам;
  • на маркетинг и рекламу;
  • представительские расходы;
  • иные расходы, связанные с реализацией товаров, продукции, услуг.

Расчет себестоимости в Excel

Можно выполнить расчет в Excel, если производимая продукция (оказываемая услуга) средней сложности, то есть, номенклатура используемых материалов и комплектующих не очень велика, например, десятки наименований.

Плюсы такого решения:

  • Excel используется повсеместно, все умеют с ним работать.
  • Все вычисления прозрачны, формулы можно посмотреть, и при необходимости изменить.
  • В Excel легко сделать результаты расчетов более наглядными — построить нужные графики (диаграммы).

Рассмотрим план разработки такой таблицы Excel на примере расчета себестоимости нормативным методом:

  • Для расчета себестоимости каждого вида продукции (изделия) логично отвести отдельный лист.
  • На листе изделия создать отдельные таблицы для расчета каждого вида прямых затрат: материалов, комплектующих, энергии, заработной платы и т.д. В дальнейшем это позволит вычислить суммарную стоимость материалов, комплектующих и т.п. по всем изделиям.
  • Для каждого материала, для каждого наименования комплектующих, энергии и др. надо указать единицу измерения, цену за единицу, норматив расхода. Предварительно надо задать план выпуска (размер партии). Это позволит рассчитать стоимость на всю партию.
    Примерно так, как на рисунке ниже. План выпуска (партия) 1000 единиц изделия.

Расчет себестоимости продукции в Excel. Прямые затраты

  • Суммируя прямые затраты получаем прямую себестоимость (себестоимость директ костинг).
  • Далее вычисляем производственную и полную себестоимость, распределяя косвенные затраты пропорционально некоторой базе распределения. Сводим все в более компактную таблицу.

Расчет себестоимости продукции в Excel. Косвенные затраты

  • Целью любого бизнеса является получение прибыли, поэтому логично не останавливаться на расчете себестоимости, а рассчитать выручку, прибыль, маржинальную прибыль, точку безубыточности, рентабельность и т.п. Предварительно, конечно, надо задать цену изделия. Цену можно задать двумя способами:
    1. Полная себестоимость + наценка.
    2. Опираясь на рыночные цены.
    В нашем случае цена 6618.61 = Полная себестоимость + 20%.

Расчет себестоимости продукции в Excel. Доходность

  • Далее, можно построить нужные нам графики, например такие:

Расчет себестоимости продукции в Excel. Структура себестоимости

  • Кроме листов для расчета себестоимости отдельных изделий должен быть итоговый лист для ввода общепроизводственных и общехозяйственных расходов. Удобнее всего, если он будет первым.

Расчет себестоимости продукции в Excel. Общепроизводственные, общехозяйственных расходы

  • На этом же листе удобно рассчитать суммарные показатели по всем изделиям

Расчет себестоимости продукции в Excel. Суммарные показатели по всем изделиям

Посмотрите Excel таблицу
«Расчет себестоимости продукции»
Полная себестоимость, Директ-костинг,
Маржинальная прибыль, Рентабельность


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

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

В подходе «Одно изделие – Один лист» есть свои плюсы и минусы.

Плюсы:

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

Минусы:

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

Видим, что минус всего один, но он очень существенный. Формулы придется править вручную, это утомительно и чревато ошибками, или придется написать макрос, который будет вставлять/удалять листы и править формулы. А это сложная задача, требующая глубокогознания Excel и серьезных навыков программирования.

Есть еще один способ — купить готовую Excel таблицу расчета себестоимости продукции.

Плюсы:

  • Таблица уже готова и проверена сотнями людей. Вы получаете работающую таблицу, заполненную данными, которые служат примером расчета и облегчают начало работы с таблицей. Получаете огромную экономию времени и сил.
  • Это обычная таблица Excel, надо просто ввести данные.
  • Если вы сами собираетесь сделать такую таблицу, вы уверены, что справитесь?
  • Если вы хотите заказать разработку, вы уверены, что сможете сделать постановку задачи и написать техническое задание? Кроме того, это значительно дольше и дороже чем купить уже готовую таблицу.
  • Автор таблицы работает официально как ИП с 2011 года, у него большой опыт разработки таблиц финансово-экономических расчетов в Excel. Раньше работал программистом в банках и коммерческих организациях.
  • Таблица рассчитывает все нужные показатели и строит диаграммы с учетом многочисленных отзывов и пожеланий пользователей.
  • Таблица содержит два листа подробных пояснений. Автор отвечает на все вопросы по эл.почте, по телефону, в ВК.
  • Получить таблицу можно в течение нескольких минут, есть несколько способов оплаты, в том числе на расчетный счет ИП в банке. Процесс оплаты и получения таблиц полностью автоматизирован.
Посмотрите Excel таблицу
«Расчет себестоимости продукции»
Полная себестоимость, Директ-костинг,
Маржинальная прибыль, Рентабельность