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

График безубыточности в Excel строится за 10–15 минут — если знать структуру данных и правильный тип диаграммы. Две линии, одна точка пересечения — и вы видите, сколько нужно продать, чтобы выйти в ноль.

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

# Как рассчитать точку безубыточности: формула с примером

BEP в единицах = Постоянные затраты / (Цена — Переменные затраты на единицу)

Это базовая формула. Всё остальное — её производные.

Разберём на конкретном примере. Допустим, вы производите блокноты. Постоянные затраты (аренда, зарплата администратора, интернет) — 50 000 грн в месяц. Цена блокнота — 250 грн. Переменные затраты на один блокнот (бумага, обложка, упаковка) — 100 грн.

Маржинальный вклад на единицу: 250 − 100 = 150 грн.

BEP = 50 000 / 150 = 334 блокнота в месяц.

Если продаёте меньше 334 — работаете в убыток. Больше — начинаете зарабатывать. И это не абстракция, это порог, ниже которого не стоит опускать план продаж.

# BEP в денежном выражении

Иногда удобнее считать не в штуках, а в гривнях:

BEP₍грн₎ = Постоянные затраты / Коэффициент маржинальности

Коэффициент маржинальности = (Цена − VC) / Цена = 150 / 250 = 0,6

BEP₍грн₎ = 50 000 / 0,6 = 83 333 грн выручки в месяц.

Обе формулы нужны. В штуках — для планирования производства. В деньгах — для оценки выручки и переговоров с инвесторами.

# Как построить таблицу данных для графика в Excel

График строится на данных. И вот тут большинство делает первую ошибку — пытаются нарисовать диаграмму «из головы», без нормальной таблицы. Не надо так.

Структура таблицы простая. Вам нужны три столбца:

Столбец A — объём продаж. Начинайте с 0, идите шагом 50–100 единиц до значения, вдвое превышающего вашу расчётную BEP.

Столбец B — выручка. Формула в Excel: =A2*$G$2 (где G2 — цена). Протяните вниз.

Столбец C — общие затраты. Формула: =$G$3 + A2*$G$4 (G3 — постоянные затраты, G4 — переменные на ед.). Протяните вниз.

Вот и всё. Таблица готова. Именно она — основа графика.

# Вынесите входные параметры в отдельный блок

Вообще-то, это обязательный шаг, а не опциональный. Создайте отдельную зону на листе:

• G1 = “Цена”
• G2 = 250
• G3 = “Постоянные затраты”
• G4 = 50000
• G5 = “Переменные затраты/ед.”
• G6 = 100

Тогда при изменении параметров весь график пересчитается автоматически. Не нужно лезть в каждую ячейку — меняете одну цифру, всё обновляется.

# Пошаговое построение графика безубыточности в Excel

Итак, таблица есть. Теперь — сам график.

Шаг 1. Выделите все три столбца данных (A, B, C) — с заголовками.

Шаг 2. Перейдите во вкладку Вставка → Диаграммы → График → выберите «График с маркерами» или просто «График». НЕ выбирайте гистограмму или круговую — они здесь бесполезны.

Шаг 3. Excel построит диаграмму с двумя линиями. По умолчанию ось X будет показывать порядковые номера строк, а не объём продаж. Это нужно исправить.

Шаг 4. Щёлкните правой кнопкой по диаграмме → Выбрать данные → в разделе «Подписи горизонтальной оси» нажмите Изменить → выделите диапазон из столбца A (объём продаж).

Шаг 5. Подпишите серии данных. Двойной клик по линии → «Формат ряда данных» → проверьте, что линии называются «Выручка» и «Общие затраты».

Шаг 6. Добавьте заголовок диаграммы — «График безубыточности». Подпишите оси: X — «Объём продаж (ед.)», Y — «Сумма (грн)».

Шаг 7. Найдите точку пересечения линий визуально. Именно она — ваша BEP. Можно добавить вертикальную пунктирную линию через «Вставка → Фигуры», но это уже косметика.

Честно — всё это занимает минут 7-8, если данные уже готовы. Ещё 2-3 минуты на форматирование.

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

Это не обязательно, но удобно. Добавьте ещё один ряд данных — одну точку с координатами (334; 83500). Выберите для него тип «Точечная диаграмма» → в параметрах подписей данных выберите «Значение из ячеек» и укажите ячейку с текстом «BEP: 334 ед.».

# Готовый шаблон Excel: что в нём и как скачать

Шаблон, о котором мы говорим, — это не просто таблица с формулами. Хороший шаблон BEP включает:

1. Лист «Параметры» — блок ввода исходных данных (FC, VC, цена). Никаких формул в этих ячейках — только цифры.
2. Лист «Расчёт» — автоматически генерируемая таблица объём/выручка/затраты на 20-30 строк.
3. Лист «График» — диаграмма, которая обновляется при изменении параметров.
4. Лист «Сценарии» — три колонки для сравнения оптимистичного, базового и пессимистичного прогнозов.

Если читаете статьи о формировании бюджета в Excel — принцип тот же: параметры отдельно, расчёты отдельно, визуализация отдельно.

Чтобы использовать шаблон с нуля — достаточно заменить три цифры в блоке параметров. Всё остальное Excel пересчитает сам.

# Как интерпретировать график и что делать с результатом

График построен. Две линии пересекаются — и что дальше?

Левее точки пересечения — зона убытков. Выручка (синяя линия) ниже затрат (оранжевая). Правее — зона прибыли. Чем дальше вправо от BEP — тем больше маржа.

Но вот что важно понять: сам по себе BEP не скажет, хорошо или плохо. Его нужно сравнивать с реальным или плановым объёмом продаж.

Если ваш средний месячный объём — 500 единиц, а BEP — 334, вы работаете с запасом прочности около 33%. Это нормально. Если же вы продаёте 360 единиц, а BEP — 334 — вы на грани. Один плохой месяц и уходите в минус.

# Запас финансовой прочности

Формула проста: (Факт − BEP) / Факт × 100%

При 500 ед. продаж и BEP 334: (500 − 334) / 500 × 100% = 33,2%.

Это называется margin of safety. Нормой считается 20–30% и выше. Ниже 15% — тревожная зона.

Для сравнения разных бизнес-идей и их устойчивости этот показатель — один из ключевых при первичной оценке.

# Частые ошибки при расчёте BEP в Excel

Ошибок немного, но каждая из них искажает картину. Разберём самые типичные.

Ошибка 1: смешивают постоянные и переменные затраты. Зарплата производственных рабочих — переменная (зависит от объёма). Зарплата бухгалтера — постоянная. Перепутать их — значит получить неверный BEP. Всегда проверяйте: меняется ли эта статья при изменении объёма производства?

Ошибка 2: не учитывают налоги и НДС. Если работаете на общей системе — НДС «съедает» часть выручки. Считайте BEP от чистой выручки (без НДС), иначе завысите точку.

Ошибка 3: строят график без нулевого значения по оси X. Если таблица начинается с 100 ед., а не с 0 — линия затрат не покажет постоянные расходы при нулевом объёме. А это важная точка — она показывает, сколько вы теряете, если вообще ничего не продаёте.

Ошибка 4: используют среднюю цену по всему ассортименту без весов. Если у вас несколько продуктов с разной маржой — средняя цена даёт ложную картину. Либо считайте BEP по каждому продукту отдельно, либо используйте взвешенную маржу.

И, вообще-то, самая частая ошибка — пересчитывать BEP раз в год. Изменились цены у поставщика? Пересчитайте. Подняли аренду? Пересчитайте. Модель в Excel для того и нужна.

# Break-even в реальных бизнес-сценариях: три примера

# Пример 1: Кофейня

Постоянные затраты: 80 000 грн (аренда, зарплата 2 сотрудников, коммуналка). Средний чек: 120 грн. Переменные затраты на чашку: 40 грн. BEP = 80 000 / (120 − 40) = 1 000 чашек в месяц, или примерно 33 чашки в день.

Нормальный трафик для точки в спальном районе — 60–80 чашек в день. Запас прочности достаточный.

# Пример 2: Онлайн-курс

Здесь интересно. Постоянные затраты минимальные — платформа, реклама, 15 000 грн/мес. Переменные на студента — почти ноль (разве что сертификат и поддержка): 200 грн. Цена курса: 3 000 грн. BEP = 15 000 / (3 000 − 200) = 6 студентов в месяц.

Вот почему онлайн-образование так привлекательно с точки зрения экономики. BEP — в пределах досягаемости даже при минимальном маркетинге. Читайте подробнее об экономике онлайн-заработка.

# Пример 3: Производство одежды

Постоянные затраты: 200 000 грн. Переменные на единицу: 800 грн. Цена: 1 500 грн. BEP = 200 000 / (1 500 − 800) = 286 единиц в месяц.

Это уже серьёзный объём. Если мощность цеха — 400 ед./мес, компания имеет запас прочности 29%. Приемлемо. Если мощность 300 ед. — компания почти на нуле и любой сбой сделает месяц убыточным.

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

График безубыточности — это не сложная финансовая модель. Это 10 минут работы в Excel и ясный ответ на вопрос: сколько нужно продать, чтобы не работать в минус. А дальше уже можно думать о прибыли.

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

Читайте дальше: как связать BEP с бюджетированием и БДДС — следующий шаг в построении финансовой модели малого бизнеса.