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

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

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

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

.

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

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

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

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

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



Показатели для расчета точки безубыточности 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% и таким способом вычисляется размер дохода:

    10. = C7/(1-СУММ(B3:B6)

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

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


en ru