Как сделать анализ безубыточности в Excel: полное руководство
Анализ безубыточности определяет уровень продаж, при котором общая выручка равна общим затратам, то есть бизнес не приносит ни прибыли, ни убытков. Чтобы рассчитать его в Excel, введите постоянные затраты, цену за единицу и переменные затраты на единицу. Разделите постоянные затраты на разницу между ценой и переменными затратами, а затем постройте график зависимости выручки от общих затрат.
В этом руководстве рассматриваются формулы, необходимые исходные данные, три шага в Excel и практический пример. В нем также показано, как использовать Goal Seek и таблицы данных, чтобы проверить, что изменится при изменении цен или затрат.
Что такое анализ безубыточности
US Small Business Administration определяет точку безубыточности как «точку, в которой общие затраты и общая выручка равны». Ниже этой точки каждая продажа все еще не позволяет бизнесу покрыть свои расходы. Выше нее каждая продажа приносит прибыль.
SBA относит анализ безубыточности к числу причин для расчета стартовых затрат, наряду с оценкой прибыли и получением кредитов. Кредиторы и инвесторы часто запрашивают его, поскольку он показывает, сколько бизнес должен продать, прежде чем перестанет нести убытки.
Анализ безубыточности отвечает на три практических вопроса. Сколько единиц товара нам нужно продать? Какую выручку это принесет? И насколько чувствителен ответ к изменениям цен и затрат?
Он также служит быстрой проверкой новой идеи. Прежде чем браться за новый продукт или открывать новую точку, оцените три исходных параметра. Затем спросите себя, реалистичен ли требуемый объем продаж для вашего рынка.
Формулы безубыточности
SBA предлагает два варианты формулы безубыточности.
В натуральном выражении (в единицах):
Точка безубыточности (в единицах) = постоянные затраты / (цена продажи за единицу - переменные затраты на единицу)
В денежном выражении:
Точка безубыточности (в деньгах) = постоянные затраты / маржинальная прибыль
SBA объясняет второй термин как «разницу между ценой продукта и затратами на его производство». Для формулы в денежном выражении эта маржа рассчитывается как коэффициент: цена минус переменные затраты, деленные на цену.
Это различие имеет значение для электронных таблиц. Маржинальная прибыль на единицу продукции — это сумма в денежном выражении, например, $3 для продукта стоимостью $5. Коэффициент маржинальной прибыли — это процент, например, 60%. Используйте денежное выражение для формулы в единицах и коэффициент для формулы в денежном выражении.
SBA также устанавливает ограничение: «Этот анализ безубыточности основан на предположении об одном продукте или услуге». В следующем разделе рассказывается, что делать, если продуктов несколько.
Что вам понадобится перед началом
В основе любого анализа безубыточности лежат три исходных параметра. Правильное разделение затрат важнее, чем сама работа в Excel.
| Исходные данные | Что это значит | Примеры |
|---|---|---|
| Постоянные затраты | Затраты, которые остаются неизменными независимо от объема продаж | Аренда, зарплата, страхование, подписки на ПО |
| Переменные затраты на единицу | Затраты, которые растут с каждой проданной единицей | Материалы, упаковка, комиссии за платежи, доставка |
| Цена за единицу | Сумма, которую клиент платит за одну единицу | Прейскурантная цена или средняя цена продажи с учетом скидок |
Используйте один и тот же период времени для всех исходных данных. Если арендная плата начисляется ежемесячно, результатом будет ежемесячная точка безубыточности. Смешивание годовой зарплаты с ежемесячной арендой даст бессмысленную цифру.
Переменные затраты на единицу — это показатель, который чаще всего берут наугад. Вместо этого оцените его на основе исторических данных. Возьмите общие переменные затраты за прошлый квартал и разделите на количество единиц, проданных в том же квартале. Если результат сильно меняется от квартала к кварталу, используйте среднее значение за несколько периодов.
Некоторые затраты являются смешанными. Тарифный план мобильной связи с базовой платой и платой за использование имеет постоянную и переменную части. Разделите их, а не гадайте, к какой категории они относятся.
Как сделать анализ безубыточности в Excel
Три шага ниже позволят построить рабочую модель безубыточности и график. В них используются стандартные формулы, которые также работают в Google Sheets.
Шаг 1: Настройте исходные данные
Откройте пустой лист и подпишите ячейки от A1 до A3 как Постоянные затраты, Цена за единицу и Переменные затраты на единицу. Введите значения в ячейки от B1 до B3.
Держите исходные данные в отдельных ячейках, никогда не вводите их вручную в формулы. Таким образом, все последующие расчеты будут обновляться при изменении входных данных, и вы сможете тестировать различные сценарии, редактируя всего одну ячейку.
Выделите ячейки ввода светлым цветом заливки. Это подскажет любому, кто откроет файл, какие числа разрешено изменять.
Шаг 2: Рассчитайте точку безубыточности
Добавьте расчеты в строки с 5 по 8.
- В ячейке A5 введите Contribution margin per unit, а в B5 введите
=B2-B3. - В ячейке A6 введите Break-even units, а в B6 введите
=ROUNDUP(B1/B5,0). - В ячейке A7 введите Contribution margin ratio, а в B7 введите
=B5/B2. - В ячейке A8 введите Break-even sales, а в B8 введите
=B1/B7.
Функция ROUNDUP имеет важное значение в ячейке B6. Вы не можете продать дробную часть единицы товара, а округление в меньшую сторону приведет к тому, что вы немного не дотянете до безубыточности.
Убедитесь, что значение в B8 примерно равно произведению B6 на цену. Если это не так, значит, один из исходных параметров введен не в ту ячейку.
Шаг 3: Постройте график безубыточности
Под расчетами постройте небольшую таблицу. В столбце D перечислите объемы продаж в единицах от 0 и выше с равным шагом, например, 0, 500 и 1,000. Продолжайте заполнение за пределами вашей точки безубыточности. В столбце E рассчитайте выручку с помощью формулы =D11*$B$2. В столбце F рассчитайте общие затраты с помощью формулы =$B$1+D11*$B$3.
Выделите столбцы от D до F и вставьте диаграмму. Лучше всего подойдет точечная диаграмма с прямыми линиями, поскольку она воспринимает столбец единиц как полноценную числовую ось.
Линия выручки начинается с нуля и круто уходит вверх. Линия общих затрат начинается на уровне постоянных затрат и растет медленнее. Точка их пересечения — это точка безубыточности. Добавьте туда подпись данных, чтобы читателям не приходилось определять ее на глаз. Закрасьте область справа от точки пересечения светлым цветом, чтобы показать зону прибыли.
Практический пример
У кофейной тележки постоянные затраты составляют $6,000 в месяц. Она продает кофе по цене $5.00 за чашку, а себестоимость каждой чашки (зерна, молоко, стаканчики и комиссия за эквайринг) составляет $2.00.
| Расчет | Результат |
|---|---|
| Маржинальная прибыль на единицу | $5.00 - $2.00 = $3.00 |
| Точка безубыточности в единицах | $6,000 / $3.00 = 2,000 чашек |
| Коэффициент маржинальной прибыли | $3.00 / $5.00 = 60% |
| Точка безубыточности в деньгах | $6,000 / 0.60 = $10,000 |
Тележке необходимо продавать 2,000 чашек в месяц, что эквивалентно $10,000 выручки, чтобы покрыть свои расходы. Каждая чашка, проданная сверх этого объема, приносит $3.00 прибыли.
Целевая прибыль рассчитывается по той же логике. Чтобы зарабатывать $3,000 в месяц, добавьте эту сумму к постоянным затратам: $9,000 разделить на $3.00 — получится 3,000 чашек.
Запас прочности показывает, насколько велик ваш запас хода. Если тележка планирует продать 2,600 чашек, продажи могут упасть на 600 чашек, прежде чем бизнес начнет нести убытки. Это составляет около 23% от ожидаемого объема продаж.
Использование Goal Seek для поиска безубыточной цены
Иногда задача ставится наоборот. Вы знаете свой объем продаж и хотите узнать, при какой цене выйдете в ноль.
Страница поддержки Microsoft описывает этот сценарий просто. Вы знаете результат, который хотите получить по формуле, но не знаете исходное значение, которое к нему приводит. Функция Goal Seek находит это значение путем изменения одной ячейки.
Добавьте ячейку прибыли, например B9, с формулой =B2*C1-B1-B3*C1, где в C1 указан ваш ожидаемый объем продаж. Затем выполните шаги, описанные Microsoft. На вкладке Данные в группе Прогноз выберите What-If Analysis, затем Goal Seek. Установите в ячейке B9 значение 0, изменяя значение ячейки B2.
Excel будет корректировать цену до тех пор, пока прибыль не станет точно равна нулю. При объеме продаж 1,500 чашек в месяц кофейной тележке потребуется цена $6.00, чтобы выйти на безубыточность.
Microsoft отмечает одно ограничение: «Goal Seek работает только с одной переменной величиной». Для решения задач с несколькими переменными одновременно компания указывает на надстройку Solver.
Чувствительность: что если изменятся цены или затраты?
Один лишь показатель безубыточности скрывает, насколько хрупким может быть положение дел. Таблица чувствительности показывает результат для целого диапазона исходных данных.
На примере кофейной тележки небольшое изменение цены или затрат заметно сдвигает точку безубыточности:
| Сценарий | Маржинальная прибыль | Точка безубыточности в единицах |
|---|---|---|
| Базовый сценарий: цена $5.00, затраты $2.00 | $3.00 | 2,000 |
| Цена вырастает до $5.50 | $3.50 | 1,715 |
| Переменные затраты вырастают до $2.50 | $2.50 | 2,400 |
| Постоянные затраты вырастают до $7,500 | $3.00 | 2,500 |
Функция Data Table в Excel, доступная в меню What-If Analysis, позволяет построить такую сетку автоматически. Поместите диапазон цен в один столбец, а диапазон переменных затрат — в одну строку. Затем укажите для таблицы ячейку с точкой безубыточности в единицах.
Сравните изменения одинакового размера, чтобы увидеть, какой исходный параметр имеет наибольшее значение. В этом примере изменение на 50 центов добавляет 400 чашек к точке безубыточности, если оно касается переменных затрат, но снижает ее всего на 285 чашек, если оно заложено в цену. Вот почему переменные затраты часто оказываются первыми, о снижении которых стоит договариваться.
Анализ безубыточности для нескольких продуктов
Формула SBA предполагает один продукт. Большинство компаний продают несколько видов продукции, каждый из которых имеет свою маржу.
Обычно используют средневзвешенную маржинальную прибыль. Оцените долю продаж каждого продукта, умножьте маржу каждого продукта на его долю и сложите результаты. Разделите постоянные затраты на этот средневзвешенный показатель, чтобы получить точку безубыточности в единицах для всего ассортимента.
Вот краткий пример. Продукт А имеет маржу $3 и составляет 60% продаж в штуках. Продукт Б имеет маржу $5 и составляет 40%. Средневзвешенная маржа равна $3 умножить на 0.6 плюс $5 умножить на 0.4, то есть $3.80. При постоянных затратах в размере $7,600 точка безубыточности составляет 2,000 единиц: 1,200 единиц продукта А и 800 единиц продукта Б.
SBA отмечает, что вы также можете проводить расчеты индивидуально для каждого продукта, если продажи меняются от месяца к месяцу. И добавляет предостережение, о котором стоит помнить: точка безубыточности — это ориентировочный показатель для планирования и оценки жизнеспособности бизнеса кредиторами, и она не призвана заменить детальный бухгалтерский учет.
Если структура продаж меняется, вместе с ней меняется и точка безубыточности. Сдвиг в сторону низкомаржинальных продуктов повышает точку безубыточности, даже если общий объем продаж остается стабильным.
Как представить анализ безубыточности
Большинству читателей нужны три цифры и один график. Начните с точки безубыточности в единицах, точки безубыточности в деньгах и запаса прочности. Поместите график прямо под ними, отметив точку пересечения.
Затем покажите таблицу чувствительности. Она отвечает на вопрос, который каждый кредитор и руководитель задает следующим: что произойдет, если затраты вырастут или продажи окажутся ниже ожидаемых?
Держите исходные данные на виду. Краткий список постоянных затрат, цена и переменные затраты на единицу позволят читателю за минуту проверить вашу логику. SBA отмечает, что точка безубыточности является важным расчетом в бизнес-плане, поэтому будьте готовы к тому, что ее будут изучать очень внимательно.
Типичные ошибки
- Смешивание временных периодов. Ежемесячная аренда в сочетании с годовой зарплатой дает бессмысленный результат. Приведите все к одному периоду.
- Использование прейскурантной цены, когда клиенты платят меньше. Если скидки — обычное дело, используйте среднюю фактически полученную цену.
- Игнорирование мелких переменных затрат. Комиссии за эквайринг, упаковка и доставка суммируются на каждую единицу товара и меняют итоговый результат.
- Игнорирование производственных мощностей. Постоянные затраты часто резко возрастают при определенных объемах — например, когда требуется вторая смена или более просторное помещение. Пересчитывайте показатели на каждом этапе.
- Округление в меньшую сторону. Дробный результат означает, что вам нужна следующая целая единица товара, а не предыдущая.
Как сделать это быстрее с помощью ИИ
Метод электронных таблиц отлично работает, когда разделение затрат уже понятно. Самая медленная часть — это обычно подготовка данных, поскольку затраты содержатся в выгрузке из главной книги или в стопке счетов-фактур.
Рабочее пространство с ИИ может взять эту сортировку на себя. Загрузите выгрузку затрат в Powerdrill Bloom и попросите его классифицировать каждую строку как постоянную или переменную, а затем рассчитать точку безубыточности в единицах и деньгах. Запросите таблицу чувствительности и график в том же запросе.
Проверьте классификацию, прежде чем доверять результату. Смешанные затраты, такие как коммунальные услуги, часто требуют экспертной оценки, которую можете дать только вы.
Для более широкого планирования на страницах генератора анализа чувствительности и финансового моделирования с ИИ представлены смежные модели. Наше руководство по отчету об исполнении бюджета (план-факт) показывает, как отслеживать, оказываются ли реальные результаты выше или ниже плана. Если вас интересуют не общие суммы, а сроки, отчет о движении денежных средств покажет, когда именно поступают денежные средства.
Если у вас готова выгрузка затрат, вы можете попробовать Powerdrill Bloom и сравнить полученный результат безубыточности с вашей собственной электронной таблицей.
Часто задаваемые вопросы
Что такое анализ безубыточности?
Анализ безубыточности рассчитывает уровень продаж, при котором общая выручка равна общим затратам. В этой точке бизнес не приносит ни прибыли, ни убытков. Он обычно используется в бизнес-планах и заявках на кредит.
Как рассчитать точку безубыточности в единицах?
Разделите постоянные затраты на маржинальную прибыль на единицу продукции, которая представляет собой цену продажи минус переменные затраты на единицу. При постоянных затратах в размере $6,000 и марже в $3 на единицу точка безубыточности составляет 2,000 единиц.
Как сделать анализ безубыточности в Excel?
Введите постоянные затраты, цену и переменные затраты в отдельные ячейки. Рассчитайте маржу как цену минус переменные затраты, а затем разделите постоянные затраты на полученное значение. Постройте график зависимости выручки и общих затрат от объема продаж в единицах и найдите точку пересечения.
В чем разница между маржинальной прибылью и коэффициентом маржинальной прибыли?
Маржинальная прибыль на единицу — это сумма в денежном выражении: цена минус переменные затраты. Коэффициент — это отношение этой суммы к цене, выраженное в процентах. Используйте денежное выражение для расчета точки безубыточности в единицах, а коэффициент — для расчета в денежном выражении.
Почему важен анализ безубыточности?
Он показывает, сколько бизнес должен продать, прежде чем перестанет нести убытки. Он также выявляет, какой исходный параметр (например, цена, переменные затраты или аренда) сильнее всего сдвигает этот порог. SBA относит его к числу причин для расчета стартовых затрат.
Источники: US Small Business Administration, «Расчет стартовых затрат» · Microsoft Support, «Использование Goal Seek». В практическом примере используются иллюстративные показатели.