Графік беззбитковості в 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
Графік будується на даних. І саме тут більшість робить першу помилку — намагаються намалювати діаграму «з голови», без нормальної таблиці. Так не треба.
Структура таблиці проста. Потрібні три стовпці:
| Обсяг продажів (од.) | Виручка (грн) | Загальні витрати (грн) |
|---|---|---|
| 0 | 0 | 50 000 |
| 100 | 25 000 | 60 000 |
| 200 | 50 000 | 70 000 |
| 300 | 75 000 | 80 000 |
| 334 | 83 500 | 83 400 |
| 400 | 100 000 | 90 000 |
| 500 | 125 000 | 100 000 |
Стовпець 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 містить:
- Аркуш «Параметри» — блок введення вхідних даних (FC, VC, ціна). Жодних формул у цих клітинках — лише цифри.
- Аркуш «Розрахунок» — автоматично сформована таблиця обсяг/виручка/витрати на 20–30 рядків.
- Аркуш «Графік» — діаграма, що оновлюється при зміні параметрів.
- Аркуш «Сценарії» — три колонки для порівняння оптимістичного, базового та песимістичного прогнозів.
Якщо читаєте статті про формування бюджету в 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 із бюджетуванням і БДДС — наступний крок у побудові фінансової моделі малого бізнесу.
Поширені запитання
Що таке маржинальний внесок і навіщо він потрібен для BEP?
Маржинальний внесок (contribution margin) — різниця між ціною та змінними витратами на одиницю товару. Саме він показує, скільки кожна продана одиниця «платить» за покриття постійних витрат. Формула BEP будується на ньому безпосередньо: BEP = FC / CM. Якщо CM малий — точка беззбитковості далеко.
Чи можна будувати BEP для кількох продуктів одночасно?
Так, але складніше. Потрібна середньозважена маржа по асортименту. В Excel це робиться через SUMPRODUCT за частками продажів кожного продукту. Для малого бізнесу з 2–3 SKU — достатньо окремих аркушів для кожного продукту.
Як часто перераховувати точку беззбитковості?
При будь-якій зміні ціни, структури витрат або постачальників. На практиці — щонайменше раз на квартал. Якщо бізнес сезонний — перед кожним сезоном.
Чи працює шаблон у Google Sheets?
Так. Усі формули — SUM, базова арифметика — ідентичні. Тип діаграми називається «Лінійна діаграма». Єдина відмінність: форматування осей дещо інше, але логіка та сама.
Що робити, якщо точка беззбитковості виходить за межі реального ринку?
Це сигнал: бізнес-модель не працює за поточних параметрів. Треба або знизити FC, або підняти ціну, або скоротити змінні витрати. Excel-модель якраз дозволяє грати з параметрами і бачити результат миттєво.