Точка безубыточности в Excel: сколько штук продать, чтобы выйти в ноль

Новый товар, аренда точки, скидка в акции — у каждого решения есть порог, ниже которого бизнес работает в минус. Справка Microsoft советует для обратного расчёта от итога команду, которая работает только с одним входным значением^[1], но здесь достаточно и обычной формулы: порог выражается через три числа. Цена, переменные затраты на единицу и постоянные затраты за период — больше ничего не нужно: точка безубыточности в Excel получается из них одной формулой.

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

Ключевые факты: точка безубыточности в Excel

• Порог в штуках равен постоянным затратам, делённым на маржинальный доход с единицы: в примере 450 000 на 510 дают 882,35^[7].
• При цене 1200 и переменных затратах 690 доля маржи составляет 42,5%, а порог в рублях — 1 058 823,53^[7].
• Команда обратного расчёта меняет только одно входное значение, поэтому цену и объём одновременно ею не подобрать^[1].
• Справка Microsoft для округления вверх указывает функцию ОКРУГЛВВЕРХ — так дробный порог гарантированно покрыт^[4].
• Порог в рублях в отличие от порога в штуках подходит магазину с сотней товаров, где известна только средняя доля маржи.

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

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

Безубыточность считается за период, и порог всегда относится к тому же отрезку времени, что и затраты: если постоянные затраты месячные, то и точка — продажи за месяц. Переменные затраты берут на одну единицу, а не на весь объём. Перепутать эти единицы — самая частая ошибка: формула не выдаст ошибку, но ответ окажется в разы больше или меньше. Ошибка появится в другом случае: если цена равна переменным затратам, знаменатель обнуляется, и формула вернёт #ДЕЛ/0!^[7] — порога нет, потому что каждая продажа ничего не приносит.

Расчёт предполагает, что цена и переменные затраты на единицу не меняются с объёмом. Если поставщик даёт скидку от партии, одна формула уже не описывает всю шкалу, и модель придётся разбить на диапазоны объёмов.

Разобравшись с формулами, переходим к примеру — на нём видно, зачем округлять ответ.

Пошаговый пример: сколько штук продать, чтобы выйти в ноль

Точка безубыточности в этом примере показывает, сколько изделий нужно продать за месяц при цене 1200 и постоянных затратах 450 000. Исходные данные стоят в B1:B4, формулы — ниже.

1. Введите цену, переменные и постоянные затраты и план в B1:B4.
2. В B5 введите =B3/(B1-B2) — получится 882,35 штуки.
3. В B6 округлите ответ вверх: =ОКРУГЛВВЕРХ(B5;0) вернёт 883^[7]; второй аргумент 0 означает число знаков после запятой.
4. Проверьте точку: подставьте 883 в формулу прибыли — получится 330, а при 882 прибыль ещё отрицательная.
5. В B7 посчитайте прибыль по плану: при 1100 штуках — 111 000^[7].

Дробная точка безубыточности в Excel — обычное дело: маржинальный доход 510 не делит 450 000 нацело. Округлять её по арифметике нельзя: справка Microsoft для округления вверх отводит ОКРУГЛВВЕРХ^[4], и только так порог гарантированно покрыт. Запас прочности плана — около 19,8%: настолько продажи могут упасть, прежде чем месяц уйдёт в минус^[7]. Соберите эту модель в уроке тренажёра: урок меняет цену и затраты и проверяет каждую формулу.

Однако точка в штуках удобна не всегда: для магазина с сотней товаров нужен порог в рублях.

В штуках или в рублях: чем отличаются две формулы

Точка безубыточности в штуках отличается от точки в рублях тем, что первая делит постоянные затраты на маржу с единицы, а вторая — на долю маржи в цене. В примере доля маржи — 0,425, то есть 42,5% каждой продажи идёт на покрытие постоянных затрат^[7].

Обе формулы дают одну и ту же точку, только в разных единицах: 882,35 штуки по цене 1200 — это 1 058 823,53 рубля выручки^[7]. Такой ответ удобнее для магазина и услуг, где «штука» не определена, а средняя доля маржи известна из отчёта.

Цена влияет на порог сильнее всего. При той же модели точка при цене 1100 поднимается до 1097,56 штуки, а при цене 1300 опускается до 737,70^[7]. Такой ряд удобно получить таблицей с одной переменной: справка Microsoft описывает её как инструмент, где все формулы ссылаются на одну входную ячейку^[2].

Именно поэтому ответ стоит показать и картинкой — на графике порог виден без формул.

График безубыточности на практике: выручка и затраты на одной диаграмме

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

1. Заполните объёмы и протяните формулы выручки и затрат — знак доллара держит ссылки на цену и затраты.
2. Выделите D1:F5 и нажмите Alt+F1: по справке Microsoft это создаёт встроенную диаграмму по текущей области^[5].
3. Смените тип на точечную диаграмму с линиями или на график, чтобы по горизонтали шли объёмы.
4. Проверьте, что линии пересекаются между 800 и 1200 штуками: при 800 затраты 1 002 000 выше выручки 960 000, при 1200 выручка 1 440 000 уже выше затрат 1 278 000^[7].

Зона левее пересечения — убыток, правее — прибыль. Если нужен отдельный лист под диаграмму для отчёта, вместо Alt+F1 нажмите F11^[5]. Когда меняется цена в B1, обе линии перестраиваются сами, и пересечение сдвигается. Пересечение на графике читается без расчёта: подпишите пересечение вручную или добавьте вертикальную линию на объёме 882,35, чтобы руководитель увидел его сразу. Шаг объёмов выбирайте так, чтобы пересечение попало внутрь таблицы, а не за её край: если линии не встретились, добавьте строки с большими объёмами.

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

Когда точка безубыточности в Excel не подойдёт

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

• Если товаров несколько и маржа у них разная, точка в штуках не имеет смысла — считайте порог в рублях по средней доле маржи.
• Когда переменные затраты на единицу падают с объёмом, одна формула занизит или завысит порог — разбейте объёмы на диапазоны.
• Если постоянные затраты указаны за год, а продажи за месяц, ответ окажется в 12 раз больше реального — приведите периоды к одному.
• При желании найти сразу цену и объём одной командой обратного расчёта ничего не выйдет: она меняет только одно входное значение^[1].
• Если прибыль в модели при найденном объёме не равна нулю, формула собрана с ошибкой — проверьте скобки и ссылки.

Обратный расчёт и таблица с переменной есть в Microsoft 365, Excel 2024 и Excel 2021, включая версии для Mac^[3]. В Google Таблицах похожий обратный расчёт делают дополнением Goal Seek с английским интерфейсом^[6], а саму формулу можно перенести без изменений.