Jak mohu vypočítat zlomovou analýzu v Excelu?

Analýza rentability je studie o tom, jaké množství prodejů nebo prodaných jednotek je třeba rozbít, a to i po zahrnutí všech fixních a variabilních nákladů na provozování podnikání. Analýza rentability je v podnikovém plánování a podnikovém financování zásadní, protože předpoklady o nákladech a potenciálním prodeji určují, zda je společnost (nebo projekt) na cestě k ziskovosti.

Jako jednoduchý příklad si vezměme limonádu. Bez ohledu na to, kolik limonády prodáváte, stále musíte platit stejnou částku nájemného každý měsíc. Nájemné je tedy fixní cena. Srovnejte to se svými výdaji na citrony: Koupíte pouze množství ovoce, které potřebujete k uspokojení poptávky po limonádě, kterou pro své podnikání předpovídáte. V létě to může být více citronů; v zimě méně citronů. Díky tomu je nákup citronu variabilní. Proměnlivé náklady rostou úměrně vašemu podnikání.

Vzorec pro zlomovou analýzu

S ohledem na výše uvedený příklad dochází ke zlomení, když:

Celkové fixní náklady + celkové variabilní náklady = výnosy

• Celkové fixní náklady jsou obvykle známy; zahrnují věci jako nájemné, platy, pomůcky, úrokové náklady, odpisy a amortizace.
• Celkové variabilní náklady jsou těžší znát, ale jsou odhadnutelné a zahrnují věci jako přímý materiál, fakturovatelná práce, provize a poplatky.
• Výnosy jsou jednotková cena * počet prodaných jednotek

S touto informací můžeme vyřešit jakoukoli část hádanky algebraicky. Předtím, než to ilustruji, je třeba zmínit několik bodů.

První je, že ve vzorci existuje neshoda ohledně toho, co je nejlepší ekvalizér. Standardní definice je výnos, ale problém s používáním výnosů spočívá v tom, že slovy „potřebujeme prodat částku X na pokrytí nákladů“ jsou vyloučeny daně, které jsou velmi skutečnými výdaji. Při podnikatelském plánování byste měli vždy zvážit, jak vypadá provozní příjem po zdanění, a toto opatření se nazývá Čistý provozní zisk po zdanění (NOPAT). Používáním NOPAT začleňujete náklady na všechny skutečné operace, včetně dopadu daní. V široce chápané definici se však používají příjmy, proto ji použijeme.

Druhým kritickým bodem je, že jádrem tohoto vzorce je marže příspěvku, která hraje rozhodující roli v provozním pákovém efektu.

Třetí věcí, která stojí za zmínku, je to, že každá část rovnice - celkové fixní náklady, celkové variabilní náklady a celkové výnosy - může být vyjádřena jako „celkem“ nebo jako měření na jednotku v závislosti na tom, jaké konkrétní opatření pro dosažení rovnováhy požadujeme. Toto je podrobněji prozkoumáno v našem příkladu aplikace Excel.

Průlomový prodej

Při pohledu na celkový prodej je měření v dolarech ($), nikoli v jednotkách:

Konečný prodej = celkové fixní náklady / (marže příspěvku)

Marža příspěvku = 1 - (variabilní náklady / výnosy)

Pamatujte, že to může být buď na jednotku, nebo celkem, protože je vyjádřeno v procentech.

Break-Even Units

Při pohledu na celkový prodej je měření v jednotkách, nikoli v dolarech. V závislosti na údajích, které máte, možná budete muset převést celkové hodnoty dolaru na jednotkové hodnoty:

Break-even jednotky = celkové fixní náklady / (cena za jednotku - variabilní náklady na jednotku)

Break-Even Price

Zde řešíme cenu vzhledem ke známým fixním a variabilním nákladům a odhadovanému počtu prodaných jednotek. Všimněte si, že v prvních dvou vzorcích známe prodejní cenu, a v podstatě odvozujeme množství prodávané do rovnovážného stavu. V tomto případě však musíme odhadnout počet prodaných jednotek (nebo celkové prodané množství) a vztahovat se k tomu v závislosti na prodejní ceně, kterou řešíme.

Procentuální variabilní náklady na jednotku = celkové variabilní náklady / (celková proměnná + celkové fixní náklady)

Celkové fixní náklady na jednotku = celkové fixní náklady / celkový počet jednotek

Break-Even Price = 1 / ((1 - Celkové variabilní náklady procento na jednotku) * (Celkové fixní náklady na jednotku))

V zásadě lze všechny tyto vzorce považovat za formu analýzy doby návratnosti, s tou výjimkou, že „doba v letech“ je ve skutečnosti, jak dlouho trvá generování požadovaného počtu prodejů ve výše uvedených výpočtech.

Break-even analýza v Excelu

Nyní, když víme, z čeho se skládá zlomová analýza, můžeme ji začít modelovat v Excelu. Existuje mnoho způsobů, jak toho dosáhnout. Nejužitečnější jsou vytvoření kalkulačky rozdělení nebo pomocí hledání cíle, což je vestavěný nástroj Excel.

Představujeme kalkulačku, protože lépe odpovídá osvědčeným postupům finančního modelování a uvádí, že vzorce by měly být rozděleny a kontrolovatelné.

Vytvořením analýzy scénářů můžeme dát Excelu, aby počítal na základě jednotky. ( Poznámka: Pokud se tabulka zdá malá, klikněte pravým tlačítkem na obrázek a otevřete na nové kartě pro vyšší rozlišení.)

Nebo na základě ceny:

Nakonec můžeme snadno vytvořit matici citlivosti, abychom prozkoumali, jak tyto faktory interagují. Vzhledem k různým nákladovým strukturám můžeme vidět rozmezí rovnovážných cen od 28 do 133 USD.