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

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

Инструкция по использованию таблицы (пример расчета)

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

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

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

При расчете предполагается, что выбран 1 период, например месяц. Пусть Постоянные издержки фирмы за период = 8 000 тыс. руб. Переменные издержки на 1 ед. продукции = 100 тыс. руб., Цена = 170 тыс. руб.
Выполним расчеты для разных объемов производств: от 0 до 200 ед. продукции (можно представить объем производства в % от 0 до 100%). Предполагается, что объем производства также осуществлен за выбранный период.

В расчетах предполагается, что объем производства равен объему реализации.
Из таблицы очевидно, что точка безубыточности близка к 60% загрузки мощностей — в этом случае суммарные издержки (20 000) примерно равны выручке (20 400). Более точные вычисления дают 57,1% или 114,3 ед. продукции. Умножив количество продукции на цену получим, что объем производства при котором прибыль равна 0 достигается при 19 428,6 тыс. руб.

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

Стоимость 1500 300 руб.

Василий Жданов, Иван Жданов

— к.э.н.
— лауреаты государственной премии за комплекс монографий в сфере экономики и управления предприятиями авиационной промышленности на базе информационных технологий
— спикеры в бизнес-школах
— основатели финансового блога finzz.ru
— авторы книги «Финансовый анализ предприятия с помощью коэффициентов и моделей».

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

Заказать таблицу Excel

Сразу после оплаты, ссылка на скачивание Excel-таблицы по расчету точки безубыточности будет выслана вам на email, который вы указали при оформлении заказа и уже через несколько минут вы сможете приступить к изучению и внедрению материалов.

Стоимость 1500 300 руб.

Не дошел заказ? Или есть вопрос? Пожалуйста напишите на почту
(служба поддержки и заботы): amina.supkhonkulova@mail.ru
Также проверьте папку Спам, возможно письмо с ссылкой на скачивание попало туда.

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

В бизнесе часто возникает необходимость определения размера дохода необходимого для достижения чистой прибыли равной нулю, чтобы выйти из минусов на 0. Другими словами, выйти на точку безубыточности – показатель BEP (на английском Break-Even Point). Так вычисляемый размер дохода называется еще порогом рентабельности. Чтобы его определить, необходимо оценить постоянные расходы и процентную часть переменных расходов. На основе эти данных можно вычислить размер дохода устанавливающий порог рентабельности BEP.

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

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

Показатели использование для расчета порога рентабельности (числа напротив пустых ячеек столбца B) вычисляются следующим образом:

1. Формула расчета операционной прибыли.
2. Формула расчета чистой прибыли после вычета переменных издержек.
3. Расчет валовой прибыли.

Два вида переменных расходов, изображенных на рисунке:

1. Себестоимость продаж (стоимость реализованных товаров COGS).
2. Стоимость продажи.

Вычисляются через умножение значения дохода на процентное значение расходов.

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

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

1. В ячейке C17 содержащую значение – чистая прибыль, введите значение 0.
2. В столбце C в ячейках содержащих постоянные расходы введите согласно с описанием в столбце … соответствующие им показатели.
3. В ячейке B6 введите размер премии в процентах, выплаченной фирмой (в данном примере 8%).
4. В ячейке B3 введите процентное значение вычисленное путем вычитания от единицы ожидаемой валовой прибыли в процентах. В данном примере фирма ожидает валовую прибыль в размере 60%. Поэтому в ячейке B3 находится только результат вычисления формулы 40%: =1-C4/C2. Не стоит эту формулу вводить в ячейку B3, чтобы не зациклить ссылки в других формулах, достаточно ввести только результат вычисления.
5. В ячейке C12 введите формулу для расчета операционной прибыли. Операционная прибыль должна быть равна сумме позиции «Расходы на проценты» и «Другие затраты и расходы». Как показано на рисунке, если оцененные расходы на проценты будут равны 465, а другие затраты и расходы равны 1368, операционная прибыль должна быть равна 1833, чтобы чистая прибыль была равна 0.
6. В ячейке C7 формулу для вычисления чистой прибыли после вычитания переменных издержек. Это сумма операционной прибыли и постоянных операционных расходов. Данное значение будет использовано для вычисления дохода.
7. В C6 введите формулу для вычисления расходов продаж. Пока не будет еще введен результат вычисления формулы расчета дохода, результат этой формулы будет равен нулю. После введения формулы вычисления доходов в ячейке появится правильное значение.
8. В C3 введите формулу для вычисления стоимости реализованных товаров (себестоимость продаж COGS). Подобно как в случае с формулой для вычисления расходов продажи, пока не будет рассчитанная прибыль, формула будет возвращать значение 0.
9. На конец в ячейке С2 введите формулу для вычисления дохода. Доход вычисляется путем деления позиции «Чистая прибыль после вычета переменных издержек» на значение, возникшее после вычитания от единицы суммы переменных расходов в процентах. На рисунке в данном примере переменные расходы составляют 48% – от значения дохода (40%+8%). Показатель ячейки C7 следует разделить на число, полученное после вычитания 48% от 100% и таким способом вычисляется размер дохода:

В результате расчетов точка безубыточности составляет – 16 935$.

Если валовая прибыль в процентах для данной фирмы составляет 60%, будут выплаченные премии в размере 8% от дохода, а принятые постоянные расходы будут детально оцениваться. Чтобы фирма преодолела порог рентабельности, она должна будет выйти на доход в размере 16 935$.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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