Расчет точки безубыточности в excel

Что показывает точка безубыточности?

Точка безубыточности (комбинация ее компонентов) показывает отчетный период, по итогам которого предприятие принесло прибыль. В зависимости от дальнейшей динамики продаж и объема выпуска товаров фирма может увеличивать прибыль, либо, наоборот, снижать ее и не обеспечивать, таким образом, достижения точки безубыточности. То есть точка безубыточности — это динамический показатель. Но успешное предприятие, однажды достигнув его, как правило, удерживается на нем в дальнейшем.

Сроки достижения точки безубыточности бизнес-проекта — важнейший показатель для предпринимателя, инвестора, партнера, кредитора. Любой из них ожидает скорейшего достижения того момента, когда бизнес начнет приносить прибыль, а также рассчитывает на то, что фирма будет в дальнейшем развиваться при позитивной динамике выручки и объема выпускаемых товаров в сочетании с оптимальными издержками.

Построение графика

Теперь, когда у нас есть таблица с рассчитанной точкой безубыточности, построить график на базе полученных данных не составит особого труда. В данном случае подойдет диаграмма с 2 линиями, одна из которых будет показывать затраты, а вторая – доходы. Точка безубыточности на графике – это пересечение данных линий.

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

  1. Зажав клавишу Ctrl на клавиатуре поочередно выделяем три диапазона данных, которые нам нужны для построения графика: значения 1, 4 и 5 столбцов (вместе с шапками).
  2. Переключаемся во вкладку “Вставка”, находим группу инструментов “Диаграммы” и жмем кнопку “Вставить точечную или пузырьковую диаграмму”.
  3. В открывшемся перечне разновидностей графиков выбираем вариант “Точечная с гладкими кривыми и маркерами”.
  4. График был автоматически сформирован и вставлен на лист.
  5. Осталось внести финальные правки:
    • Дважды щелкнув по названию графика можно отредактировать его.
    • Щелкаем по любой цифре оси X, после чего вокруг шкалы должна появиться рамка. Затем кликом правой кнопкой мыши по этой же цифре вызываем контекстное меню и выбираем в нем пункт “Формат оси” (или можно просто дважды щелкнуть по любой цифре шкалы).
    • В открывшемся вспомогательном окне “Формат оси” указываем основное деление, равное цифре 1 (шаг роста объема производства), после чего закрываем окно.
    • При желании аналогичным образом меняется шкала оси Y.
    • Для большей наглядности график можно слегка растянуть, потянув за любой из его углов. При этом нужно зажать клавишу Shift, если требуется сохранить пропорции.
    • Также, с помощью зажатой левой кнопки мыши можно переместить график в любое удобное место на листе, чтобы он не перекрывал таблицу. Тянуть нужно за любые белые участки вокруг графика.

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

Что такое точка безубыточности

Точка безубыточности в английской аббревиатуре – BEP (break-evenpoint), для удобства будем пользоваться этим обозначением. Принимая истину, что прибыль – это разница между доходами TR (totalrevenue) и расходами TC (totalcost), BEP можно определить как момент нулевой прибыли. BEP может иметь денежное или натуральное выражение. Показатель этот нужно знать, чтобы сориентироваться в объемах продаж для выхода к нулевой отметке. В BEP расходы всегда меньше доходов. Если точка перейдена, говорят о доходах и, соответственно, до ее достижения – об убытках.

О BEP компании нужно знать, чтобы делать обоснованные выводы о ее финансовой стабильности. При увеличении значения BEP можно быть уверенным, что существуют проблемы с прибылью. Изменение значения происходит при росте предприятия с сопутствующим увеличением товарооборота, при выходе на другую сеть продаж, при изменении цен и налаживанием сети.

Значение BEP необходимо знать для:

  • Определения перспективности инвестирования проект с учетом конкретного объема продаж.
  • Выявления проблем компании в связи с временными изменениями BEP.
  • Расчета взаимозависимости объема продаж и цены производимого продукта.
  • Выяснения возможного снижения выручки без угрозы убытков, если фактически полученная прибыль превышает расчетную.

Этапы расчета точки безубыточности по А.Д. Шеремету

Отечественный экономист А.Д.  Шеремет выделил основные 3-и этапа для определения точки безубыточности предприятия.

  1. Сбор необходимой информации для проведения анализа. Оценка уровня объема производства, реализации продукции, прибыли и убытков.
  2. Вычисление размера переменных и постоянных издержек, определение точки безубыточности и зоны безопасности.
  3. Оценка необходимого уровня продаж/производства для обеспечения финансовой устойчивости предприятия.

Задачей предприятия становится определение нижней границы своей финансовой устойчивости и создание возможностей для увеличения зоны безопасности.

Где нужны подобные расчеты

Показатель «точка безубыточности» востребован в экономической практике для решения следующих задач:

  1. Вычисление оптимальной цены за изделие.
  2. Расчет объема затрат, при котором фирма еще остается конкурентоспособной.
  3. Составление плана реализации товара.
  4. Нахождение объема выпуска продукции, при котором появится рентабельность.
  5. Анализ финансового состояния и платежеспособности предприятия.
  6. Нахождение минимального объема изготовления.

Готовые расчеты и шаблоны для анализа выхода предприятия на безубыточность:

Результаты подобных расчетов востребованы как внутренними, так и внешними пользователями. Безубыточность учитывается при принятии управленческих решений, дает представление о финансовом состоянии фирмы. Применение подобной модели – способ оценки критического уровня производственного объема и реализации товаров и услуг.

Основные показатели для вычислений

Расчет ВЕР базируется на информации затратных счетов производства

Здесь важно разделить их на постоянные и переменные. К постоянным относят расходы, величина которых из месяца в месяц не меняется и не реагирует на колебания объема выпуска и продаж

Это могут быть:

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

В состав переменных затрат входят:

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

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

Аналитический метод

Существует несколько способов определения критического объема. Аналитическим методом, то есть через формулу, тоже может быть найдена точка безубыточности. График в этом случае не требуется.

Прибыль = Выручка – (Постоянные расходы + Переменные расходы * Объем)

Определение безубыточности выполняется при условии, что прибыль равна нулю. Выручка является произведением объема продаж на цену. Получается новое выражение:

0 = Объем*Цена – (Постоянные расходы + Переменные * Объем),

После элементарных математических процедур на выходе получается формула:

Объем = Постоянные издержки / (Цена – Переменные издержки).

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

Практическое использование точки безубыточности

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

Основные цели использования модели:

  • Расчет цены продукции.
  • Определение объема выпуска, обеспечивающего рентабельность предприятия.
  • Определение уровня платежеспособности и финансовой надежности. Чем выпуск продукции находится дальше от точки безубыточности, тем запас финансовой прочности выше.
  • Инвесторами и кредиторами — оценка эффективности развития и платежеспособности фирмы.

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

​ количественных или денежных​ На графике точка​ самостоятельно, следует в​ этого показателя следует​Расчет может быть произведен​ будет являться точкой​ столбца​ отображены в поле.​ под названием​ располагаться количество единиц​1​.​

​ объемы производства, при​ все затраты предприятия​ Безубыточность учитывается при​ «Значения» делаем ссылку​ при котором наступит​

Формула точки безубыточности в Excel

​ единицах.​ безубыточности находится в​ первую очередь постараться​

  1. ​ учитывать постоянные издержки​ двумя способами: в​ безубыточности. Проведение подобных​«Количество товаров»​
  2. ​После того, как указанные​«Изменение ряда»​ товара, а по​до​

​В четвертом столбце расположена​ которых достигается точка​ условно можно разделить​ принятии управленческих решений,​

​ на соответствующий столбец​ точка безубыточности.​

​Чем ниже точка безубыточности​ том месте, где​ вникнуть в теорию​ (TFC), выручка (R)​ денежном выражении и​ расчетов является базовым​. Делаем это тем​

​ манипуляции были проведены,​. В нем мы​ оси​16​ общая сумма расходов.​ безубыточности.​ на постоянные и​

​ дает представление о​

​ с данными.​Для решения этих задач​

​ объема производства и​

  1. ​ выручка пересекает линию​ и осмыслить, какие​ и общие переменные​ в натуральном. Этот​ при организации и​
  2. ​ же способом, который​ жмем на кнопку​ должны указать координаты​

​Y​. Последующие столбцы заполняются​ Она составляет сумму​

  1. ​Рассчитаем этот показатель при​ переменные. Первая группа​
  2. ​ финансовом состоянии фирмы.​Диапазон подписей горизонтальной оси​ составляем следующую таблицу​
  3. ​ продаж, тем выше​ общих издержек. Экономический​ факторы влияют на​
  4. ​ издержки (TVC). Разница​ показатель в денежном​ планировании деятельности любого​
  5. ​ мы рассматривали при​«OK»​ размещения данных, на​

​денежные суммы.​ по тому алгоритму,​ ячеек соответствующей строки​ помощи инструментов программы​

​ не зависит от​ Применение подобной модели​ – «Объем производства».​ с формулами:​ платежеспособность и финансовая​ смысл точки безубыточности​ данное значение.​ между выручкой и​ выражении имеет второе​ предприятия.​

Как построить график точки безубыточности в Excel

​, размещенную в нижней​ основе которых будет​Переходим во вкладку​

​ строиться один из​

​«Вставка»​ выше.​

​ столбца. В пятом​ построим график, на​ носит неизменный характер.​ критического уровня производственного​Немного изменим график (макет​ объема производимой продукции.​Существует 2 способа расчета​

Где нужны подобные расчеты

​ которой прибыль равна​ отобразить количество проданных​ маржинальный доход (MR).​ рентабельности – общая​

  1. ​Любой бизнесмен старается определить,​В поле​
  2. ​После этого автоматически происходит​ графиков. Для начала​. Жмем на значок​
  3. ​Как видим, точка безубыточности​
  4. ​ столбце расположен общий​ котором отметим точку​ Сюда можно включить​
  5. ​ объема и реализации​ диаграммы).​
  6. ​Общие затраты – это​

​ точки безубыточности в​ нулю или выручка​ товаров. Выручка предприятия​

  • ​Используя эти показатели, надо​ выручка предприятия, которая​
  • ​ когда его компания​«Значения Y»​
  • ​ возврат к окну​ построим график, в​
  • ​«Точечная»​ достигается на​
  • ​ доход. Он рассчитывается​ безубыточности. Для проведения​

​ объем заработной платы​ товаров и услуг.​Такая демонстрация позволяет увидеть,​ сумма переменных и​ Excel:​ способная покрыть все​ отражается по оси​ определить коэффициент маржинального​ покрывает общие издержки.​ престанет приносит убытки,​, точно так же​ выбора источника данных.​

exceltable.com>

​ котором отображались бы​

  • Excel замена точки на запятую в excel
  • Как в excel посчитать количество ячеек с одинаковым текстом
  • Как в xl посчитать сумму в столбце в excel
  • Как в excel посчитать среднее время
  • В excel посчитать количество ячеек
  • Как в excel посчитать столбик
  • В excel заменить точку на запятую
  • Как в excel посчитать количество заполненных ячеек
  • Excel посчитать количество месяцев между датами
  • Как в excel посчитать отклонение в процентах
  • В excel посчитать количество заполненных ячеек
  • Excel посчитать уникальные значения в столбце

Ограничения и условия построения

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

  • Линейная зависимость между выпуском и затратами.
  • Весь произведенный объем равен реализуемому. Отсутствуют запасы готовой продукции.
  • Цены на товары не меняются, как и переменные расходы.
  • Отсутствие капитальных расходов, связанных с приобретением оборудования и началом производства.
  • Принимается конкретный временной период, на протяжении которого размер постоянных издержек не меняется.

Из-за перечисленных условий точка безубыточности, пример нахождения которой был рассмотрен, считается теоретической величиной в проекции классической модели. На практике расчеты при многономенклатурном производстве гораздо сложнее.

Формула расчета показателя

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

  1. Постоянные затраты в пределах заданного объема производства остаются неизменными (этот уровень называется релевантным). Это касается также переменных затрат и цены;
  2. Выпуск продукции и себестоимость готовых изделий увеличиваются или уменьшаются линейно (прямо пропорционально);
  3. Производственная мощность на протяжении заданного интервала расчетов постоянна;
  4. Номенклатура продукции не меняется;
  5. Влияние размера запасов несущественно. То есть величина незавершенного производства имеет незначительные колебания и вся произведенная продукция отпускается покупателю.

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

Простой расчёт точки безубыточности в Excel с построением наглядного графика

В примере описан простой расчёт точки безубыточности в Excel для одного вида продукции с построением наглядного графика (подробнее об анализе точки безубыточности смотрите в соответствующей статье). Такой метод называется также CVP-анализ.

Скачать расчёт точки безубыточности в Excel: Tochka-bezubitka

�?сходные данные:

Предприятие производит и сразу же продаёт металлические лестницы. Стоимость лестницы на рынке составляет 30 000 рублей. На изготовление одной лестницы уходит 4 рабочих дня. Затраты на изготовление лестницы составляют: стоимость металла – 10000 рублей, сдельная оплата рабочего-сварщика – 1800 рублей, электродов и прочих материалов – 500 рублей. Общепроизводственные расходы составляют: аренда павильона – 20000/месяц, оклад одного рабочего-сварщика – 10000 рублей, оплата труда вспомогательного персонала (грузчик, уборщик) — 20000 рублей. На предприятии трудятся 5 сварщиков.

Руководство компании считает, что при объёме продаж в 20 лестниц беспокоиться не о чем.

Задание:

  • рассчитать точку безубыточности;
  • рассчитать запас прочности,
  • построить график точки безубыточности,
  • оценить предположение руководства о достаточности заданного объёма продаж.

Ответ:

Компания работает не в убыток при объёме продаж в 17 лестниц. Запас прочности составляет всего 3 лестницы. Вывод: запас прочности слишком мал и заданный объём продаж – достаточно большой риск.

Описание примера

Скачайте прилагаемый файл Tochka-bezubitka, откройте лист �?сходные данные и расчёт.

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

В разделе Расчёт точки безубыточности и запаса прочности сначала рассчитывается уровень безубыточности с помощью формулы =ОКРУГЛВВЕРХ(C21/(C6-C13);0). В формуле сумма постоянных затрат делится на разницу между выручкой и переменными затратами на единицу продаж, после чего, поскольку лестницы измеряются целыми числами, искомое значение округляется до целого числа с помощью функции ОКРУГЛВВЕРХ. Запас прочности рассчитывается как разница между целевым объёмом продаж и уровнем безубыточности.

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

На листе Вспомогательная таблица рассчитать все значения затрат в зависимости от объёма продаж, объём продаж меняется от 0 до 25 (максимальный объём продаж).
На листе �?сходные данные и расчёт добавить диаграмму (меню Вставка – Диаграммы), обратите внимание, что тип диаграммы должен быть Точечная с прямыми отрезками, а не обычная Гистограмма.
Щёлкнуть правой кнопкой мыши на диаграмме, нажать Выбрать данные…, выделить диапазон B3:F29 листа Вспомогательная таблица.
Линия переменных затрат на графике не добавляет полезной информации, поэтому её можно убрать: правой кнопкой мыши на диаграмме, нажать Выбрать данные…, слева выделить Переменные затраты и нажать Удалить. Должна получиться такая диаграмма:

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

  1. На листе �?сходные данные и расчёт добавить вспомогательную таблицу:
  2. Эта таблица задаёт координаты двух вертикальных линий от 0 до максимального объёма продаж.
  3. Снова щёлкнуть правой кнопкой мыши на диаграмме, нажать Выбрать данные…, в открывшемся окне Выбор источника данных нажать Добавить, откроется окошко �?зменение ряда.
  4. В строке �?мя ряда выбрать ячейку Е33, в строке Значения Х – диапазон F33:G33, в строке Значения Y – диапазон F34:G34, нажать ОК:
  5. Аналогично добавить ряд для целевого уровня: нажать Добавить, �?мя ряда — выбрать ячейку Е35, в строке Значения Х – диапазон F35:G35, в строке в строке Значения Y – диапазон F36:G36, нажать ОК.
  6. Ещё раз ОК, на диаграмме появятся две вертикальные линии.

Планирование безубыточности на предприятии

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

Последовательные этапы планирования безубыточности:

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

Из внешних факторов принимается во внимание доля рынка, подконтрольная компании, деятельность конкурентов, изменение потребительского спроса, политическая и экономическая ситуация в стране и т.д;

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

Исследуются альтернативные варианты сбыта на новые рынки или перестройка предприятия на выпуск схожих товаров в случае неблагоприятной ситуации на текущем рынке;

Рассчитываются постоянные, переменные затраты и себестоимость продукции. Планируется объем незавершенного производства на всех этапах производства. Формируется потребность в основных и оборотных средствах и источники их приобретения. Дополнительные возможные расходы по кредитам, лизингу и другим обязательствам также учитываются в издержках производства;

Выполняется расчет точки безубыточности. Определяется необходимая величина кромки безопасности. Чем более нестабильны внешние факторы, тем больше должен быть запас прочности. Далее рассчитываются объемы выпуска и продаж товаров на уровне кромки безопасности;

Планирование ценовой политики компании. Определяются цены на продукцию, которые позволят достичь необходимого объема продаж. Еще раз пересчитывается точка безубыточности и кромка безопасности. При необходимости пункты 3 и 4 повторяются с целью изыскания резервов снижения себестоимости для достижения необходимых значений по запасу прочности;

Принятие окончательного плана безубыточности и продаж с разделением по периодам. Производится утверждение данных по точке критического объема.

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

 Товар Выручка от продажи товара, тыс. р. Индивидуальные переменные затраты, тыс. р. Постоянные затраты, тыс. р.
А 370 160 400
Б 310 140
В 240 115
Г 70 40
Всего 990 455 400

Кз. пер. – коэффициент доли переменных затрат в выручке.

Товар Маржинальный доход, тыс. р. Доля маржинального дохода в выручке Кз. пер. (доля переменных затрат в выручке)
А 210 0,37 0,43
Б 170 0,55 0,45
В 125 0,52 0,48
Г 30 0,43 0,57
Всего 535 0,54 0,46

Теперь рассчитаем этот показатель по самому оптимистичному прогнозу. Его называют маржинальной упорядоченностью по убыванию. Из таблицы видно, что наиболее рентабельные товары – это А и Б.

Хотя магазин и работает прибылью, запас прочности ниже 30%. Пути улучшения финансовых показателей заключаются в уменьшении переменных затрат и увеличении продаж по товарам Г и В. А также необходимо более детально проверить постоянные затраты. Возможно, найдутся резервы их уменьшения.

Показатель Значение, р.
Розничная цена 1 бутылки растворителя 140
Переменные затраты на 1 литр растворителя (стоимость сырья, воды, тары, зарплата рабочих, вспомогательные материалы для производства) 80
Постоянные затраты (зарплата управленческому персоналу, амортизация, налоги, содержаний зданий) 170000
Выручка 450000
Объем (фактический ) 3000

Получившееся значение совсем недалеко от фактического объема (3000 шт.).

Таким образом, предприятие работает на грани безубыточности. Необходимы срочные меры по улучшению финансового положения: пересмотр структуры постоянных затрат, возможно зарплата управленческого персонала завышена. Стоит детально разобраться с издержками, формирующими переменные затраты. Первоочередное направление их снижения – поиск новых поставщиков сырья.

Точка безубыточности и практика ее использования

Анализ точки безубыточности используется в различных целях. Рассмотрим некоторые направления и цели использования данного показателя. В таблице ниже приводится цели возможного использования показателя точки безубыточности в экономической практике.

Пользователи Цель использования

Внутренние пользователи

Директор по развитию/продажам Расчет оптимальной цены за единицу товара, расчет уровня издержек, когда предприятие может быть еще конкурентоспособным. Расчет и составление плана продаж
Собственники/Акционеры Определение объема производства, при котором предприятие станет рентабельным
Финансовый аналитик Анализ финансового состояния предприятия и уровня его платежеспособности. Чем дальше предприятия от точки безубыточности, тем выше ее порог финансовой надежности
Директор производства Определение минимально необходимого объема производства продукции на предприятии

Внешние пользователи

Кредиторы Оценка уровня финансовой надежности и платежеспособности предприятия
Инвесторы Оценка эффективности развития предприятия
Государство Оценка устойчивого развития предприятия

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

Недостатки модели

Объем реализации равен объему производства и обе величины изменяются линейно. Не учитываются: поведение покупателей, новые конкуренты, сезонность выпуска, то есть все условия, влияющие на спрос

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

Ссылка на основную публикацию