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

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

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


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

В целом результаты практически любого коммерческого проекта можно упрощенно представить следующим образом:

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

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

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

P * X (доходы) — FC — VC * X (общие расходы) = 0, где

P — цена продажи 1 единицы продукта, X — объем продукции, FC — постоянные затраты, VC — переменные затраты на 1 единицу продукта.

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

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

Таким образом, в натуральном эквиваленте формула записывается как:

X = FC / (P — VC),

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

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

Соответственно, для получения денежного эквивалента, нужно полученный объем продукции умножить на цену продукта (P * X).

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


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

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

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


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

Выделим диапазон данных с доходами и расходами (B9:L11), переходим на вкладку Вставка -> Диаграмма -> Точечная и выбираем точечную диаграмму с прямыми отрезками (также можно построить с помощью стандартного линейного графика):


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


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


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

Спасибо за внимание!
Если у вас остались вопросы — жду ваших комментариев.

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

Итак, что же такое точка безубыточности? Вот что нам говорит экономический букварь:

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

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

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

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

Постоянные издержки — издержки, которые практически неизменны на протяжении всего расчётного периода.

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

  • Фонд оплаты труда.
  • Социальные отчисления.
  • Аренда помещения (если помещение не в собственности, естессно).
  • Коммунальные услуги и охрана.
  • Связь (телефон, хостинг, интернет).
  • Реклама.
  • Прочие раходы.

Таким образом, на данном этапе мы можем сделать 2 очень важных вывода:

  1. С увеличением объёма производства мы увеличиваем расходы исключительно за счёт переменных издержек.
  2. С увеличением объёма производства величина постоянных издержек на единицу продукции снижается. Это один из элементов т.н. эффекта масштаба.

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

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

На изображении отчётливо видно, что уровень постоянных издержек неизменен с течением времени, переменные же, напротив, планомерно растут. Таким образом, точка отсчёта совокупных издержек (переменные + постоянные) берёт своё начало с величины постоянных издержек и устремляется всё выше и выше по мере увеличения объёма производства. Также на графике имеется прямая выручки, описывающая динамику поступления денежного потока. И в месте пересечения этих двух прямых (совокупных издержек и выручки) образуется вожделенная точка безубыточности. Всё просто.

Итак, формула расчёта точки безубыточности:

ТБ = ПоИ / (Ц — ПеИ)

  • ТБ — точка безубыточности,
  • Ц — цена единицы продукции/услуги,
  • ПоИ — постоянные издержки,
  • ПеИ — переменные издержки.

По результатам расчёта мы узнаем объём безубыточного сбыта в количественном выражении. Если же нам нужна ТБ в денежном эквиваленте, то достаточно полученное значение умножить на цену. Вот и все дела. А, если говорить по-простому, то то, что мы видим в знаменателе (разница между ценой и переменными издержками), то это ни что иное, как грязная прибыль (маржа) с каждой единицы продукции, которая идёт на покрытие постоянных издержек. А главная задача бизнеса — это как можно быстрее погасить постоянные издержки этой самой маржой и перейти к накоплению прибыли. А чем больше потенциальная прибыль на данном рынке, тем выше запас финансовой прочности предприятия. Иными словами:

ЗФП = ПО — ТБ

  • ЗФП — запас финансовой прочности,
  • ПО — потенциальный оборот,
  • ТБ — точка безубыточности.

Вот, собственно, и всё. Для определения своей первой точки безубыточности этой информации вам должно хватить с головой. В своей деятельности мне частенько приходилось браться за калькулятор и производить подобные расчёты. Затем я немного облегчил свой труд и завёл таблицу в программе Excel, где уже есть все необходимые формулы и расчёт превратился в банальную операцию сродни похода в магазин за хлебом. Файл для расчёта я прилагаю, пользуйтесь сколько влезет! Думаю, он и вам высвободит немалое количество времени. При этом на всё про всё у вас уйдёт не более 5 минут, да и наглядный график точки безубыточности в Excel вы тоже получите.

А теперь давайте рассчитаем точку безубыточности на конкретном примере, воспользовавшись предложенным инструментом. Предположим, у нас с вами есть магазин женской одежды и мы выходим на рынок небольшого регионального города. Наша первоочередная задача — посчитать точку безубыточности и на основе получившейся зависимости, скорректировать цену (средний чек), найти оптимальное соотношение ЦЕНА/КОЛИЧЕСТВО. Итак, поехали:

  1. Открываем файл (скачать).
  2. Вносим примерный усреднённый уровень цены. На основании нашего прайса (а, быть может, выгрузки из 1С или чего-то подобного) вносим 3 000 руб.

2. Внимательно заполняем элементы постоянных издержек. Наш с вами магазин нуждается в 223 000 руб. ежемесячно.

3. Тоже самое проделываем с переменными. В нашем примере закупочная цена единицы товара равна примерно 1 450 руб.

4. Итог: точка безубыточности магазина одежды равна 144 шт. (или 144 * 3000 руб. = 432 000 руб.) Теперь осталось проанализировать полученные результаты и поиграть со входящими параметрами, подобрав, тем самым, наиболее оптимальное соотношение. Например, вас не устраивает получившийся объём необходимого сбыта, вам он кажется слишком большим в ваших реалиях и это продиктовано, скажем, небольшой пропускной способности вашего магазина или конверсией в покупку. В этом случае вы можете запросто уменьшить это количество за счёт небольшого увеличения среднего чека. Верно и обратное: вам не нравится завышенная цена… Смело снижайте её за счёт более выгодного места или делайте ставку на продвижение. Думаю, вы поняли.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для наглядной демонстрации экономического и финансового состояния предприятия составим график:

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

Получаем график вида:

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

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

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

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

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

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

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