Ako môžem vypočítať zlomovú analýzu v Exceli?
Analýza rentability je štúdia o tom, aké množstvo predaja alebo predaných jednotiek sa musí prerušiť aj po zahrnutí všetkých fixných a variabilných nákladov na chod podniku. Analýza rentability je v podnikovom plánovaní a podnikovom financovaní kritická, pretože predpoklady o nákladoch a potenciálnom predaji určujú, či je spoločnosť (alebo projekt) na dobrej ceste k ziskovosti.
Ako jednoduchý príklad sa pozrime na stojan na limonády. Bez ohľadu na to, koľko limonády predávate, stále musíte platiť rovnakú výšku nájomného každý mesiac. Nájomné je teda fixná cena. Porovnajte to so svojimi výdavkami na citróny: Nakúpite iba také množstvo ovocia, ktoré potrebujete na uspokojenie dopytu po limonádach, ktorý predpovedáte pre svoje podnikanie. V lete to môže byť viac citrónov; v zime menej citrónov. Vďaka tomu je nákup citrónov variabilný. Variabilné náklady sa zvyšujú úmerne k vašej firme.
Vzorec pre break-even analýzu
Z hľadiska vyššie uvedeného príkladu je možné dosiahnuť rovnomerné rozdelenie, keď:
Celkové fixné náklady + celkové variabilné náklady = príjmy
- Celkové fixné náklady sú zvyčajne známe; zahŕňajú veci ako nájomné, platy, verejné služby, úrokové náklady, odpisy a amortizácia.
- Celkové variabilné náklady sú ťažšie známe, sú však odhadovateľné a zahŕňajú veci ako priamy materiál, fakturovateľná práca, provízie a poplatky.
- Výnosy sú jednotková cena * počet predaných kusov
Na základe týchto informácií dokážeme vyriešiť ľubovoľnú časť hádanky algebraicky. Predtým, ako to dokážem, je potrebné spomenúť niekoľko bodov.
Prvým je, že vo vzorci existuje nezhoda, pokiaľ ide o najlepší ekvalizér. Štandardnou definíciou sú príjmy, ale problém s používaním výnosov je ten, že príslovie „Potrebujeme predať sumu X na pokrytie nákladov“ nezahŕňa dane, ktoré sú veľmi skutočné výdavky. Pri obchodnom plánovaní by ste mali vždy zvážiť, ako vyzerajú prevádzkové výnosy po zdanení, a toto opatrenie sa nazýva Čistý prevádzkový zisk po zdanení (NOPAT). Použitím NOPAT začleňujete náklady na všetky skutočné operácie vrátane účinkov daní. Všeobecne chápaná definícia však využíva príjmy, preto ju použijeme.
Druhým kritickým bodom je, že jadrom tohto vzorca je marža príspevkov, ktorá hrá rozhodujúcu úlohu pri prevádzkovej páke.
Tretia vec, ktorá stojí za zmienku, je, že každá časť rovnice - celkové fixné náklady, celkové variabilné náklady a celkové výnosy - sa môže vyjadriť ako „celkom“ alebo ako jednotková miera v závislosti od toho, aké konkrétne opatrenie na dosiahnutie rovnováhy požadujeme. Toto je podrobnejšie preskúmané v našom príklade programu Excel.
Konečný predaj
Pri pohľade na celkový predaj je meranie v dolároch ($), nie v jednotkách:
Konečný predaj = celkové fixné náklady / (marža príspevku)
Marža príspevku = 1 - (variabilné náklady / príjmy)
Upozorňujeme, že to môže byť buď na jednotku, alebo celkom, pretože je vyjadrené v percentách.
Rozdeľovacie jednotky
Keď sa pozrieme na celkový predaj, meranie je v jednotkách, nie v dolároch. V závislosti od údajov, ktoré máte, možno budete musieť preložiť celkové hodnoty dolára na hodnoty za jednotku:
Rozdeľovacie jednotky = celkové fixné náklady / (cena za jednotku - variabilné náklady za jednotku)
Konečná cena
Tu riešime cenu vzhľadom na známe fixné a variabilné náklady, ako aj odhadovaný počet predaných kusov. Všimnite si v prvých dvoch vzorcoch, že poznáme predajnú cenu a v podstate pochádzame z množstva predaného do rovnováhy. V tomto prípade však musíme odhadnúť počet predaných kusov (alebo celkový počet predaných kusov) a vzťahovať ich na funkciu predajnej ceny, ktorú riešime.
Percento variabilných nákladov na jednotku = celkové variabilné náklady / (celková premenná + celkové fixné náklady)
Celkové fixné náklady na jednotku = celkové fixné náklady / celkový počet jednotiek
Konečná cena = 1 / ((1 - Celkové variabilné náklady v percentách na jednotku) * (Celkové fixné náklady na jednotku))
V zásade možno všetky tieto vzorce považovať za formu analýzy doby návratnosti, s výnimkou toho, že „čas v rokoch“ je v skutočnosti to, ako dlho trvá generovanie požadovaného počtu predajov vo vyššie uvedených výpočtoch.
Break-even analýza v Exceli
Teraz, keď vieme, z čoho pozostáva zlomová analýza, môžeme ju začať modelovať v Exceli. Existuje niekoľko spôsobov, ako to dosiahnuť. Tieto dve najužitočnejšie sú vytvorením zlomovej kalkulačky alebo použitím funkcie Goal Seek, ktorá je vstavaným nástrojom Excel.
Kalkulačku demonštrujeme, pretože lepšie vyhovuje osvedčeným postupom finančného modelovania, v ktorých sa uvádza, že vzorce by sa mali rozdeliť a auditovať.
Vytvorením analýzy scenárov môžeme povedať programu Excel, aby počítal na základe jednotky. ( Poznámka: Ak sa tabuľka javí ako malá, kliknite pravým tlačidlom myši na obrázok a otvorte na novej karte s vyšším rozlíšením.)
Alebo na základe ceny:
Nakoniec môžeme ľahko vytvoriť maticu citlivosti, aby sme preskúmali, ako tieto faktory interagujú. Vzhľadom na rôzne štruktúry nákladov môžeme vidieť rozpätie rovnovážnych cien od 28 do 133 USD.