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

Для расчёта хватает четырёх чисел за один и тот же период: постоянные расходы, цена единицы, переменные расходы на единицу и план продаж. Если считаете выручкой, вместо цены и переменных на единицу нужны выручка и переменные расходы за месяц.

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

Разложите числа по ячейкам, чтобы формулы читались: B1 — постоянные расходы, B2 — цена, B3 — переменные расходы на единицу, B4 — план продаж. Дальше формулы ссылаются на эти ячейки, и при изменении любого числа всё пересчитывается.

Маржинальный доход с единицы: =B2-B3. Это сумма, которая остаётся с каждой продажи на покрытие постоянных расходов.

Доля маржинального дохода: =(B2-B3)/B2 — формат «процент».

Точка безубыточности в штуках: =B1/(B2-B3), округление вверх до целого — =ОКРУГЛВВЕРХ(B1/(B2-B3);0), потому что продать часть единицы нельзя.

Точка безубыточности в рублях: =B1/((B2-B3)/B2) или =B1/(1-B3/B2).

Прибыль при плане: =B4*(B2-B3)-B1. Запас финансовой прочности в процентах: =(B4-B1/(B2-B3))/B4 — на сколько процентов могут упасть продажи до нуля прибыли.

Если считаете выручкой, всё проще: доля переменных расходов = переменные за месяц / выручка за месяц, точка безубыточности = постоянные расходы / (1 − эта доля).

График строится по диапазону объёмов продаж, а не по одной точке. Сделайте столбец объёмов от нуля до значения выше плана: например, шаг 10 % от точки безубыточности, 12–15 строк.

Рядом три столбца: выручка = объём × цена; переменные расходы = объём × переменные на единицу; суммарные расходы = постоянные + переменные. Четвёртым столбцом можно добавить прибыль = выручка − суммарные расходы.

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

Чтобы точка читалась точнее, добавьте на диаграмму вертикальную линию на объёме безубыточности: отдельный ряд из двух точек — (точка, 0) и (точка, выручка в точке). И подпись с формулой =ТЕКСТ(точка;"0") — она обновится вместе с расчётом.

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