Як можна обчислити аналіз беззбитковості в Excel?

Аналіз беззбитковості - це дослідження, яку кількість продажів чи проданих одиниць потрібно пробити навіть після включення всіх постійних та змінних витрат на ведення операцій бізнесу. Аналіз беззбитковості має вирішальне значення для бізнес-планування та корпоративних фінансів, оскільки припущення про витрати та потенційні продажі визначають, чи компанія (або проект) рухається до рентабельності.

Як простий приклад, візьмемо підставку з лимонадом. Незалежно від того, скільки лимонаду ви продаєте, вам все одно потрібно платити однакову суму оренди щомісяця. Отже, оренда - це фіксована вартість. Порівняйте це з вашими витратами на лимони: Ви купуватимете лише ту кількість фруктів, яка вам потрібна для задоволення попиту на лимонад, який ви прогнозуєте для свого бізнесу. Влітку це може бути більше лимонів; взимку менше лимонів. Таким чином, це робить покупки лимона змінною вартістю. Змінні витрати збільшуються пропорційно вашому бізнесу.

Формула для аналізу беззбитковості

Маючи на увазі наведений вище приклад, беззбитковість виникає, коли:

Загальні постійні витрати + Загальні змінні витрати = Виручка

• Загальні постійні витрати зазвичай відомі; до них відносяться такі речі, як орендна плата, зарплата, комунальні послуги, процентні витрати, амортизація та амортизація.
• Загальні змінні витрати важче знати, але вони підлягають оцінці та включають такі речі, як прямий матеріал, оплачувана праця, комісії та збори.
• Дохід - ціна одиниці * Кількість проданих одиниць

За допомогою цієї інформації ми можемо вирішити будь-який фрагмент головоломки алгебраїчно. Перш ніж проілюструвати це, слід згадати кілька моментів.

Перший полягає в тому, що всередині формули існує розбіжність щодо того, який найкращий еквалайзер. Стандартне визначення - це дохід, але проблема використання доходу полягає в тому, що сказання "нам потрібно продати X суму для покриття витрат" виключає податки, які є дуже реальними витратами. У бізнес-плануванні завжди слід враховувати, як виглядає операційний дохід після оподаткування, і цей захід називається Чистий прибуток від операційної діяльності після оподаткування (NOPAT). Використовуючи NOPAT, ви включаєте вартість усіх фактичних операцій, включаючи вплив податків. Однак широко зрозуміле визначення використовує дохід, тому саме це ми будемо використовувати.

Другий критичний момент полягає в тому, що в основі цієї формули лежить внесок, який відіграє критичну роль в операційному важелі.

Третє, що варто зауважити, - це те, що кожна частина рівняння - загальні постійні витрати, загальні змінні витрати та загальний дохід - може бути виражена як «Загальна сума» або як одиниця виміру залежно від того, який конкретний показник беззбитковості нам потрібен. Це детальніше вивчено на прикладі Excel.

Беззбиткові продажі

Якщо дивитися на загальний обсяг продажів, вимірювання виражається в доларах ($), а не одиницях:

Беззбиткові продажі = загальні постійні витрати / (маржа внеску)

Маржа вкладу = 1 - (Змінні витрати / доходи)

Зауважте, що це може бути як на одиницю, так і на загальну суму, оскільки це виражається у відсотках.

Одиниці беззбитковості

Якщо дивитися на загальний обсяг продажів, вимірювання проводиться в одиницях, а не в доларах. Залежно від даних, які у вас є, вам може знадобитися перевести загальні значення долара в одиницю значень:

Беззбиткові одиниці = загальні постійні витрати / (ціна за одиницю - змінна вартість за одиницю)

Ціна беззбитковості

Тут ми вирішуємо ціну, враховуючи відому постійну та змінну вартість, а також орієнтовну кількість проданих одиниць. Зауважте, що в перших двох формулах ми знаємо ціну продажу і по суті визначаємо кількість проданої беззбитковості. Але в цьому випадку нам потрібно оцінити як кількість проданих одиниць (або загальну кількість проданих), так і співвіднести це як функцію з ціною продажу, яку ми вирішуємо.

Відсоток змінних витрат на одиницю = Загальні змінні витрати / (Загальна змінна + Загальна кількість постійних витрат)

Загальна кількість постійних витрат на одиницю = Загальна кількість постійних витрат / Загальна кількість одиниць

Ціна беззбитковості = 1 / ((1 - Загальна кількість змінних витрат на відсоток) * (Загальна кількість постійних витрат на одиницю))

По суті, всі ці формули можна розглядати як форму аналізу періоду окупності, за винятком того, що "час у роках" є ефективним, скільки часу потрібно для отримання необхідної кількості продажів у вищезазначених розрахунках.

Аналіз беззбитковості в Excel

Тепер, коли ми знаємо, з чого складається аналіз беззбитковості, ми можемо почати моделювати його в Excel. Існує ряд способів цього досягти. Два найбільш корисні - це створити калькулятор беззбитковості або використовувати ціль пошуку, яка є вбудованим інструментом Excel.

Ми демонструємо калькулятор, оскільки він краще відповідає найкращим практикам фінансового моделювання, вказуючи, що формули повинні бути розбиті та перевірені.

Створюючи аналіз сценарію, ми можемо сказати Excel провести розрахунок на основі одиниці. ( Примітка. Якщо таблиця здається невеликою, клацніть зображення правою кнопкою миші та відкрийте в новій вкладці для більшої роздільної здатності.)

Або виходячи з ціни:

Нарешті, ми можемо легко побудувати матрицю чутливості, щоб дослідити, як взаємодіють ці фактори. Враховуючи різні структури витрат, ми можемо побачити діапазон цін беззбитковості від 28 до 133 доларів.