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

Excel

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

Точка безубыточности – это такой объем производства товаров или услуг (здесь и далее в тексте в качестве примера будет использоваться производство товаров), при котором прибыль равна нулю, т потеря одновременно. Это означает, что если вы производите больше, чем объем, соответствующий точке безубыточности, будет излишек. Если вы уменьшите громкость, это приведет к потерям.

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

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

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

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

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

Существует два способа расчета точки безубыточности в Excel:

  1. В денежном выражении: (выручка * постоянные затраты) / (выручка — переменные затраты).
  2. В натуральных единицах: постоянные затраты / (цена — средние переменные затраты).

Обратите внимание на следующее! Переменные затраты берутся на единицу продукции (не в сумме).

Чтобы найти безубыточность, нужно знать:

Постоянные затраты (не зависящие от производственного процесса или реализации) — арендные платежи, налоги, зарплата менеджеров, арендные платежи и так далее

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

Цена продажи единицы.

Введите данные в таблицу Excel:

Задания:

  1. Найдите объем производства, при котором предприятие будет получать чистую прибыль. Установите зависимость между этими параметрами.
  2. Рассчитайте объем реализации товара, при котором наступит точка безубыточности.

Для решения этих задач составим следующую таблицу формул:

  1. Переменные затраты зависят от объема выпускаемой продукции.
  2. Общие затраты представляют собой сумму переменных и постоянных затрат.
  3. Выручка – это произведение объема производства и цены товара.
  4. Предельный доход – это общий доход без переменных затрат.
  5. Чистая прибыль – это доход без учета постоянных и переменных производственных затрат.

Давайте заполним таблицу и посмотрим, какой продукции компания поставит «плюс».

С 13-го производства чистый результат был положительным. А в точке безубыточности он равен нулю. Объем производства 12 единиц товара. А доход от продаж 120 000 руб.

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

Чтобы наглядно продемонстрировать экономическое и финансовое состояние компании, нарисуем график:

  1. Определитесь с внешним видом диаграммы и нажмите кнопку «Выбрать данные».
  2. Для демонстрации нам понадобятся столбцы «Общая стоимость», «Выручка», «Чистая прибыль». Это элементы легенды — «Ряды». Вводим «имя строки» вручную. А в строке «Значения» создаем ссылку на соответствующий столбец данных.
  3. Выбор меток по горизонтальной оси — «Объем производства».

Получаем такой график:

Немного изменим график (разметку карты).

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

Читайте также: Поиск решения в Экселе

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

В Microsoft Office Excel очень удобно рассчитывать точку безубыточности. Между всеми данными легко установить необходимые формулы и построить таблицу.

Порядок составления таблицы

Во-первых, необходимо установить стоимостные и ценовые показатели. Предположим, что постоянные затраты 180 руб., переменные 60 руб., цена 1 ед товара 100 руб.

Таблица безубыточности

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

Точка безубыточности в Excel

Значение в столбцах будет следующим:

  • Объем производства заполняем сами, в нашем случае берем интервал от 0 до 20 штук;
  • Постоянные затраты =$D$3;
  • Переменные затраты =A9*$D$4;
  • Валовые (общие) затраты = B9 + C9;
  • Доход (доход) = А9*$Д$5;
  • Предельный доход = Е9-С9;
  • Чистый результат (убыток) = E9-C9-B9.

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

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

С 5-й производственной единицы чистый результат был положительным. До этого доходы не покрывали общие (суммарные) издержки производства. В данном случае прибыль равна 20 руб., то есть, формально говоря, это не совсем корректная точка безубыточности. Точное значение объема при нулевой прибыли можно рассчитать:

Пример формулы

Это означает, что точка безубыточности математически рассчитана при объеме производства 4,5 единицы. Но экономист учитывает 5 шт и величина дохода 480 руб считается точкой безубыточности, так как произвести и продать 4,5 шт товара невозможно.

Добавим в таблицу еще 2 столбца с расчетом запаса прочности (запаса прочности, запаса прочности) в деньгах и в процентах (KBden и KB%). Этот показатель указывает на возможный размер уменьшения дохода или выпуска до точки безубыточности. То есть насколько бизнес далек от критического объема.

Рассчитывается по формулам:

формула-6

  • Факт (план) — фактический или планируемый доход;
  • Wtb — доход в точке безубыточности.

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

  • Запас прочности в рублях. = Е9-$Е$14;
  • Запас прочности в % = Н10/Е10*100 (расчет ведется исходя из объема производства 1 шт., т.к деление на ноль запрещено).

Запасом прочности считается запас прочности выше 30%. В нашем примере производство и продажа 8 наименований и более означает стабильное финансовое положение компании.

Итоговая таблица будет выглядеть так:

Таблица расчета

Алгоритм построения графика

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

График точки безубыточности

Точка пересечения выручки и валовых затрат является точкой безубыточности. Ему соответствует значение чистой прибыли 0 (в нашем примере 20 рублей при количестве продукции 5 штук) по горизонтали и минимально необходимое значение выручки для покрытия общих затрат по вертикали.

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

Диаграмма с ключевыми показателями

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

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

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

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

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

Помнить! При определении экономического нулевого момента берутся идеальные числа и суммы.

определение точки безубыточности — идеальная модель развития организации; в действительности результаты могут измениться из-за непредвиденного увеличения затрат или снижения спроса. Рассмотрим допущения, которые применяются при расчете:

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

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

Согласно теории экономиста А. Д. Шеремета, нулевая точка должна определяться в три приема. Исследователь считает, что организациям нужна информация об этом показателе, чтобы оставаться в безопасной зоне и максимально расширять ее. Давайте посмотрим на шаги, которые вывел Шеремет:

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

Первый вариант расчета: знаем затраты и объем продаж

Модифицируя формулу нулевой точки, мы рассчитываем цену товара, установив которую можно будет добиться нейтрального значения. Для начала расчета необходимо получить данные о безвозвратных убытках организации, себестоимости товаров и плановых продажах. Формула записывается так: P = (FC + VC(X)) / X. VC(X) означает, что нужно умножить себестоимость на количество проданного товара. Результаты в виде таблицы будут выглядеть примерно так:

точка-безубыточности-в-excel-инструкция-по-нахождению-точки-безубыточности-в-excel

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

Второй вариант расчета: знаем цену и затраты

Самый популярный способ узнать расчет точки безубыточности, его используют в организациях с большим производством. Необходимо выяснить, какое количество реализованного товара принесет организации нулевые убытки и прибыль. Для определения этого числа используется формула натурального эквивалента точки безубыточности: X = FC/(P — VC).

Известными данными являются постоянные и переменные затраты, а также установленная цена товара. Для определения денежного эквивалента цена товара умножается на полученный объем продаж в единицах товара. Таблица в этом случае выглядит так:

точка-безубыточности-в-excel-инструкция-по-нахождению-точки-безубыточности-в-excel

Третий вариант расчета: для сферы услуг и торговли

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

Целевая рентабельность – это частота получения наценок при реализации товара. Для расчета необходимой суммы выручки (S) необходимо знать величину (R) и информацию о постоянных затратах (FC). Выручка – целевой объем продаж в рублях. Формула: S = FC/R.

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

точка-безубыточности-в-excel-инструкция-по-нахождению-точки-безубыточности-в-excel

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

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

точка-безубыточности-в-excel-инструкция-по-нахождению-точки-безубыточности-в-excel

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

точка-безубыточности-в-excel-инструкция-по-нахождению-точки-безубыточности-в-excel

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

  • переменные затраты = объем производства * постоянные затраты;
  • общие затраты = постоянные + переменные;
  • выручка = объем производства * общие затраты;
  • предельный доход = доход — переменные затраты;
  • чистая прибыль/убыток = выручка – общие затраты.

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

точка-безубыточности-в-excel-инструкция-по-нахождению-точки-безубыточности-в-excel

Если ни одна из строк не заканчивается нулем в результате, вам нужно сделать еще несколько расчетов, чтобы узнать значение запаса прочности/запаса в процентах и ​​в деньгах. Это значение показывает, насколько далеко компания находится от точки безубыточности. Создайте еще два столбца в таблице.

По формуле запаса прочности в денежном выражении нужно из каждого значения дохода вычесть положительное его значение, ближайшее к нулю. В упрощенном виде это записывается так: КБден = Vфакт (фактический доход) — Втб (доход в точке безопасности).

Чтобы узнать процент надежности, следует разделить значение денежного запаса прочности на сумму фактического дохода и полученное число умножить на 100: Кб% = (Кбден/Факт) * 100%. Базовую точку можно определить более точно по краю безопасности, как показано на изображении ниже:

точка-безубыточности-в-excel-инструкция-по-нахождению-точки-безубыточности-в-excel

Порядок расчета математическим методом

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

Пример формулы

Для расчета точки безубыточности в денежном выражении необходимо знать количество проданных арбузов в месяц и сумму переменных затрат на этот объем:

  • Q в месяц = ​​36000/250 = 144 арбуза,
  • Зпер за объем в месяц = ​​130 * 144 = 18720 руб.

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

3 формулы расчета

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

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

Кроме того, определяем величину запаса прочности:

2 формулы

Уровень выше 30% считается приемлемым, а это значит, что бизнес правильно спланирован.

Порядок расчета графическим методом

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

Завершенный точечный график безубыточности

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

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

Однако приведенный выше расчет имеет следующие недостатки:

  • Не учитываются сезонные колебания и возможные колебания спроса;
  • Конкуренция на рынке может усилиться за счет появления передовых технологий, новых маркетинговых движений;
  • Цены на товары могут измениться;
  • Для постоянных и «крупных» покупателей возможно предоставление скидок.

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

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

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

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

  1. Анализ текущего состояния компании и продаж. Выявляются сильные и слабые стороны и определяются пути снижения затрат с учетом внутренних и внешних факторов. Оценивается работа со службами снабжения и сбыта, уровень управления в компании, рациональность производственного процесса. Из внешних факторов учитываются доля рынка, контролируемая фирмой, деятельность конкурентов, изменение потребительского спроса, политическая и экономическая ситуация в стране и др;
  2. Прогноз будущих цен на выпускаемую продукцию с учетом оценки всех факторов из пункта 1. Планируется область допустимой маржи. Исследуются альтернативные варианты выхода на новые рынки или реструктуризации компании для выпуска аналогичной продукции в случае неблагоприятной ситуации на текущем рынке;
  3. Рассчитываются постоянные, переменные затраты и издержки производства. Объем незавершенного производства планируется на всех этапах производства. Формируется потребность в основных и оборотных капиталах и источники их приобретения. Любые дополнительные расходы по кредитам, лизингу и другим обязательствам также включаются в себестоимость продукции;
  4. Вычисляется нулевая точка. Определено необходимое значение запаса прочности. Чем нестабильнее внешние факторы, тем больше должен быть запас прочности. Затем рассчитываются объемы производства и реализации товаров на уровне запаса прочности;
  5. Планирование ценовой политики компании. Цены устанавливаются на товары, которые позволят достичь желаемого объема продаж. Еще раз пересчитываются точка безубыточности и запас прочности. При необходимости пункты 3 и 4 повторяются для поиска резервов снижения затрат для достижения требуемых значений запаса прочности;
  6. Принятие окончательного плана безубыточности и продаж по периодам. Данные проверяются точкой критического объема.
  7. Контроль безубыточности, разделенный на несколько составляющих: контроль всех статей расходов, общей себестоимости, плана продаж, поступления платежей от клиентов и т д. В компании всегда должно быть понимание того, насколько текущая финансовая ситуация соответствует запланированному уровню безубыточности.

Пример расчета для магазина

На примере магазина, торгующего несколькими видами товаров, рассмотрим вариант решения многотоварной задачи. Это музыкальные инструменты и сопутствующие товары: электрогитара (А), бас-гитара (Б), усилитель звука (С), акустическая гитара (Г). Магазин имеет фиксированные затраты, а также индивидуальные переменные затраты для каждого типа продукта. Они закупаются у различных поставщиков и обеспечивают собственный доход.

Исходные данные следующие:

 Продукт Доход от реализации товаров, тыс руб Индивидуальные переменные затраты, тыс руб Постоянные затраты, тыс руб
ОДИН 370 160 400
Б 310 140
В 240 115
Г 70 40
Общий 990 455 400

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

Рабочая формула

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

Формула 1

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

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

После расчета Kz per для всего цеха средний нулевой балл будет равен:

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

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

Сначала магазин будет их продавать, и общая предельная выручка (210 + 170 = 380 тыс руб.) почти покроет постоянные затраты (400 тыс руб.). Остальные 20 тыс руб будут получены от реализации товара Б. Точка безубыточности равна сумме доходов от всех перечисленных продаж:

Оптимистичный расчет

Самый пессимистичный прогноз продаж — это маржинальный возрастающий порядок. Первоначально будут проданы товары D, C и B. Предельный доход от них (125 + 30 + 170 = 325 тыс руб.) не сможет покрыть постоянные затраты магазина (400 тыс руб.). Оставшаяся сумма в размере 75 тыс руб будет получена от реализации товара А. Точка безубыточности будет равна:

Пессимистический расчет

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

Кроме того, рассчитаем запас прочности в денежном выражении и в процентах от средней точки безубыточности:

Рассчитать безубыточность

Даже если магазин прибыльный, запас прочности ниже 30%. Пути улучшения финансовых результатов заключаются в снижении переменных затрат и увеличении продаж товаров D и C. Также необходимо более детально проверить постоянные затраты. Может быть, есть резервы для их снижения.

Пример расчета для предприятия

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

Исходные данные следующие:

Индекс Стоимость, р.
Розничная цена за 1 флакон растворителя 140
Переменные затраты на 1 л растворителя (затраты на сырье, воду, тару, заработную плату рабочих, вспомогательные материалы для производства) 80
Постоянные затраты (заработная плата менеджеров, амортизация, налоги, содержание здания) 170 000
Доход 450 000
Объем (фактический) 3000

Расчет следующий:

Пример расчета

Полученное значение очень близко к реальному объему (3000 шт.).

Кроме того, рассчитываем запас прочности в штуках (по формуле, аналогичной денежной) и в процентах:

Расчет безубыточности по формуле

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

Преимущества и недостатки модели точки безубыточности

Основным преимуществом этой модели является ее простота. Три способа определить точку безубыточности под силу каждому, у кого на устройстве есть Microsoft Excel. Проблема в том, что модель условна и ограничена. На практике могут произойти неожиданные изменения одного из показателей, из-за которых результаты расчетов могут быть признаны бесполезными. Если спрос на продукцию нестабилен, заранее определить точный объем продаж невозможно. На него влияют и другие факторы — например, качество работы отдела маркетинга.

Оцените статью
Блог о Microsoft Word