29 просмотров

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Статья в тему:  Как организовать прибыльный бизнес по производству жалюзи. Основные покупатели жалюзи

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

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

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

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

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

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

  • Постоянные затраты;
  • Переменные затраты на единицу продукции;
  • Цена реализации единицы продукции.
Статья в тему:  Чем отличаются туроператоры от турфирм. Турагент - это кто такой и как с ним связаться? Понятия «туроператор» и «турагентство»

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

    Строим новую таблицу, основываясь на таблице-исходнике. Первая колонка новой таблицы – это количество товаров (или партий), изготовленных предприятием. То есть, номер строки будет обозначать количество изготовленных товаров. Во втором столбце находится величина постоянных затрат. Она у нас во всех строках будет равна 25000. В третьем столбце – общая сумма переменных затрат. Эта величина для каждой строки будет равна произведению количество товаров, то есть, содержимого соответствующей ячейки первого столбца, на 2000 рублей.

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

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

Для наглядности заполняем 16 строк. В первом столбце будет число товаров (или партий) от 1 до 16. Последующие столбцы заполняются по тому алгоритму, который был указан выше.

Статья в тему:  Доставка товаров по городу через такси. Срочная доставка сигарет

Создание графика

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

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

Перед нами открывается пустая область диаграммы. Следует заполнить её данными. Для этого кликаем правой кнопкой мыши по области. В активировавшемся меню выбираем позицию «Выбрать данные…».

Происходит запуск окна выбора источника данных. В его левой части есть блок «Элементы легенды (ряды)». Жмем на кнопку «Добавить», которая размещена в указанном блоке.

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

В поле «Значения X» указываем координаты данных, расположенных в столбце «Количество товаров». Для этого устанавливаем курсор в данное поле, а затем, произведя зажим левой кнопки мыши, выделяем соответствующий столбец таблицы на листе. Как видим, после указанных действий его координаты отобразятся в окне изменения ряда.

Статья в тему:  Доработка программы 1с. Преимущества доработанной конфигурации

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

После того, как указанные манипуляции были проведены, жмем на кнопку «OK», размещенную в нижней части окна.

После этого автоматически происходит возврат к окну выбора источника данных. В нем также нужно нажать на кнопку «OK».

Как видим, вслед за этим на листе отобразится график общих затрат предприятия.

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

Снова запускается окно выбора источника данных, в котором опять нужно нажать на кнопку «Добавить».

Открывается небольшое окошко изменения ряда. В поле «Имя ряда» на этот раз пишем «Общий доход».

В поле «Значения X» следует внести координаты столбца «Количество товаров». Делаем это тем же способом, который мы рассматривали при построении линии общих затрат.

В поле «Значения Y», точно так же указываем координаты столбца «Общий доход».

После выполнения этих действий жмем на кнопку «OK».

Окно выбора источника данных закрываем, нажав на кнопку «OK».

Статья в тему:  Мой личный кабинет онлайм. Личный кабинет OnLime
  • После этого линия общего дохода отобразится на плоскости листа. Именно точка пересечения линий общего дохода и общих затрат будет являться точкой безубыточности.
  • Таким образом, мы достигли целей создания данного графика.

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

    Помимо этой статьи, на сайте еще 12429 инструкций.
    Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

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

    Статья в тему:  Мтс большой пинг. Как уменьшить высокий пинг до сервера или игры

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

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

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

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

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

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

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

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

    Применяя ТБУ для планирования, желательно соблюдение следующих факторов:

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

    Расчет по формуле в денежном выражении

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

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

    Существует два основных метода

    Обычный

    ВЕР = (TR* FC) / (TR – VC)

    Выручка (TR), надеюсь большинство понимают, что она равняется произведению цены (P) и объёма (Q): TR = Р*Q

    Через маржинальный доход

    Маржинальный доход (MR) это разница между выручкой (TR) и переменными затратами (VC), в нем не учитываются постоянные издержки.

    Статья в тему:  Чем занимается омон. Бойцы вологодского омона приоткрыли тайну подготовки спецподразделения

    Если он рассчитывать на одну единицу продукции, то используется переменная AVC. Она равна AVC=VC/Q и цена, соответственно MR на единицу продукции= P-AVC.

    Далее нам понадобится значение коэффициента маржинального дохода к выручке KMR, для этого делим первое на второе. KMR=MR/TR

    Если для единицы товара то:

    КМР= MR на единицу продукции/P

    Используя полученные значения, вычисляем точку безубыточности по формуле: BEP = FC / KMR

    Важно: Вне зависимости от варианта формулы полученный результат будет один.

    Расчет по формуле в натуральном выражении

    Расчет по формуле в натуральном выражении

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

    Для подсчета в натуральном выражении формула выглядит так: BEP = FC/(P-AVC)

    Пример:

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

    • Постоянные расходы: 15 000 рублей
    • Переменные расходы: 10 000 рублей;
    • Стоимость одной канистры: 60 рублей;
    • Переменные расходы на единицу: 30 рублей;
    • Выручка (TR): 20 000 рублей.

    Теперь, когда у нас есть входные значения, рассчитаем оба вида ТБУ:

    В деньгах: (20000*15000) / (20000-10000) = 30000

    В количественном измерении: 15000/(60-30)=500 штук.

    Наше мини-производство покроет все расходы при продаже незамерзающей жидкости на 30000 рублей или после выпуска 500 канистр.

    Статья в тему:  Делаем правильно: замена батарей газосваркой. Замена батарей отопления на сварке Замена батарей отопления газосваркой сварщики

    Запас прочности

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

    Из реальной выручки вычитается ТБУ

    Значение кромки в денежном выражении делим на выручку и умножаем на 100%.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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