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

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

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

Формула точки безубыточности в 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. Нахождение минимального объема изготовления.

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

• скачать выше описанный пример расчета;
• скачать пример расчета точки безубыточности производства;
• скачать расчет точки безубыточности магазина;
• скачать форму по расчету точки Безубыточности;
• скачать примеры решения задачи по расчету ТБ.

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

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

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

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

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

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

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

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

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

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

• Постоянные затраты;
• Переменные затраты на единицу готового товара (шт., партий и т.д.);
• Стоимость продажи единицы товара.

Приступим к выполнению поставленной задачи.

1. На основе данных выше на нужно построить новую таблицу:

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

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

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

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

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

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

Заключение

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

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

1. Постоянные затраты (одна цифра, не зависит от объема производства);
2. Переменные затраты – прямо пропорциональны объему производства. Достаточно стоимости единицы товара, переменные рассчитаются автоматически.
3. Выручка, но, как и в случае с переменными затратами, нам достаточно цены за единицу.

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

Цена*Объем=Себестоимость единицы*Объем+Постоянные затраты
Объем*(Цена-Себестоимость)=Постоянные затраты
Объем = Постоянные затраты / (Цена – Себестоимость)

В денежном выражении = Цена * Постоянные затраты / (Цена – Себестоимость)
Итак, вот таблица, по которой мы и будем работать:

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

Теперь данные для графика готовы:

Обратите внимание, что хотя нам понадобятся только три кривые, я поставил сюда объем производства и кривую точки безубыточности с одной цифрой. Данные объема производства мне понадобятся для данных оси Х, а точка безубыточности нужна для наглядности.
Итак, выделяем данные четырех кривых (объем производства понадобится нам позже). Затем Вставка/Диаграммы/График/График с маркерами.

Диаграмма появится сразу здесь, но для лучшей наглядности мы сделаем следующее:
— Переместим ее на отдельный лист. Правая кнопка мыши на диаграмме/Переместить диаграмму/на отдельном листе.
— Поменяем подписи Оси Х – Правая кнопка мыши на диаграмме/Выбрать данные/Подписи горизонтальной оси/Изменить и выделяем ряд Объем производства (только цифры!)

— Выделяем точку безубыточности правой кнопкой/Формат ряда данных/Параметры маркера/Встроенный (точка и размер 10) плюс можно поменять цвет: Заливка маркера/Сплошная заливка/Цвет (черный)
В принципе, можно конечно поиграться с параметрами диаграммы, поставить название диаграммы, название оси, это можно сделать через вкладку Макет.

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

• один временной период, т.е. постоянные затраты даны один раз и не меняются, шкала Х – это не временная ось.
• затраты либо прямо пропорциональны объему производств, либо вообще не зависят от него. Но только сдавая экзамены на АССА F2, я столкнулся с описанием тех затрат, которые хоть и существуют, почему-то не упоминались в университете: ступенчатые постоянные затраты (stepped fixed costs) и смешанные (semi-variable costs).
• цена не меняется, как и себес на единицу.
• нет одноразовых (капитальных, например) затрат, например, в начале производства надо закупать оборудование, здания и т.д.
• что произвели, то и продали.

Пример для скачивания:
Скачать пример

Эксель Практик

«Глаза боятся, а руки делают»

P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс «Топ-10 инструментов Excel») и будьте в курсе новых событий.

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

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

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

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

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

Высчитать значения в нулевой точке можно с помощью этой формулы: P*X – FC – VC*X = 0. Значения переменных:

• P – стоимость продукта для покупателя;
• X – объем производимого;
• FC – постоянные издержки;
• VC – переменные затраты, которые несет компания при производстве единицы продукта.

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

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

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

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

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

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

Выведение точки безубыточности – идеальная модель развития организации, в реальности итоги могут меняться из-за непредвиденного роста затрат или падения спроса. Рассмотрим допущения, применимые в ходе подсчета:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Итоговая таблица принимает такой вид:

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

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

Чтобы узнать процент безопасности, следует разделить значение денежной кромки безопасности на объем фактической выручки и умножить получившееся число на 100: КБ% = (КБден/Вфакт)*100%. По кромке безопасности можно с большей точностью определить точку безубыточности, как это показано на изображении ниже:

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

На графике визуально отражается, в какой момент прибыль становится больше убытков. Чтобы составить его, воспользуемся инструментами Excel. Для начала необходимо выбрать вкладку «Вставка» и найти на ней пункт «Диаграммы». При нажатии на кнопку с этой надписью появится список шаблонов. Выбираем точечную диаграмму – их тоже несколько, нужна схема с кривыми без резких изгибов.

Далее определим, какие данные появятся на графике. После клика правой кнопкой мыши по белой области, где позже появится схема, появится меню – нужен пункт «Выбрать данные».

В окне выбора данных находим кнопку «Добавить» и кликаем по ней. Она находится в левой части.

На экране появится новое окно. Туда нужно внести диапазоны ячеек, в которых находятся данные для одной из ветвей графика. Назовем первый график «Общие затраты» – эту фразу нужно вписать в строку «Имя ряда».

Превратить данные в график можно следующим образом: нужно кликнуть по строке «Значения X», зажать верхнюю ячейку столбца и протянуть курсор вниз до конца. То же самое делаем со строкой «Значения Y». В первом случае нужно выделить столбец «Количество товаров», во втором – «Общие затраты». Когда все поля заполнены, можно нажимать «ОК».

Снова нажимаем «Добавить» в окне выбора данных – появится такое же окно, как и предыдущее. Теперь имя ряда – «Общий доход». К значениям X относятся данные в ячейках столбца «Количество товаров». Поле «Значения Y» нужно заполнить, выделив столбец «Общий доход».

Теперь можно нажать кнопку «ОК» в окне «Выбор источника данных», тем самым закрыв его. В области диаграммы появится график с пересекающимися линиями. Место пересечения – это точка безубыточности.

Где нужны подробные расчеты, практика использования

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

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

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

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

Заключение

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