Jak vypočítat zlomovou analýzu v aplikaci Excel

Excel má mnoho funkcí. Kromě běžných tabulek vám Excel může pomoci při různých obchodních výpočtech, mimo jiné při analýze zlomového bodu.

Jak vypočítat analýzu zvratu v aplikaci Excel

Analýza zvratu je studie o tom, jaké množství prodeje nebo prodaných jednotek vyžaduje podnik, aby splnil všechny své výdaje bez ohledu na zisky nebo ztráty. Dochází k ní po zahrnutí všech fixních a variabilních nákladů na provozování podniku (zdroj: Investopedia).

V tomto příspěvku se zaměřímeo tom, jak používat Excel k výpočtu analýzy zlomového bodu.

Co je to analýza rentability

Bod zvratu podnikání je tam, kde jsou objem výroby a objem prodeje zboží (nebo služeb) stejné. V tomto okamžiku může podnik pokrýt všechny své náklady. Z ekonomického hlediska je bod zvratu bodem indikátoru kritické situace, kdy jsou zisky a ztráty nulové. Obvykle je tento ukazatel vyjádřen v kvantitativních nebo peněžních jednotkách.

Čím nižší je bod zvratu, tím vyšší je finanční stabilita a solventnost firmy.

Analýza rentability je kritická v obchodním plánování a podnikových financích, protože předpoklady o nákladech a potenciálním prodeji určují, zda je společnost (nebo projekt) na dobré cestě k ziskovosti.

Analýza zvratu pomáhá organizacím / společnostem určit, kolik jednotek musí prodat, než pokryjí své variabilní náklady a část jejich fixních nákladů spojených s výrobou této jednotky.

Vzorec pro analýzu zlomové rovnováhy

Chcete-li najít rentabilitu, potřebujete vědět:

• Fixní náklady
• Variabilní náklady
• Prodejní cena za jednotku
• Příjmy

Bod zvratu nastane, když:

Celkové fixní náklady (TFC) + celkové variabilní náklady (TVC) = výnosy

• Celkové fixní náklady jsou známé položky, jako je nájemné, platy, veřejné služby, úrokové náklady, amortizace a odpisy.
• Celkové variabilní náklady zahrnují věci jako přímý materiál, provize, fakturovatelná práce a poplatky atd.
• Výnosem je jednotková cena * Počet prodaných jednotek.

Marže příspěvku

Klíčovou součástí výpočtu analýzy rentability je pochopení toho, jak velká marže nebo zisk se generuje z prodeje po odečtení variabilních nákladů na výrobu jednotek. Tomu se říká příspěvková marže. Tím pádem:

Marina příspěvku = prodejní cena - variabilní náklady

Vzorec bodu zlomu v aplikaci Excel

Bod zvratu můžete vypočítat s ohledem na dvě věci:

1. Peněžní ekvivalent: (výnosy * fixní náklady) / (výnosy - variabilní náklady).
2. Přírodní jednotky: fixní náklady / (cena - průměrné variabilní náklady).

S ohledem na to existuje řada způsobů, jak vypočítat bod zvratu v aplikaci Excel:

1. Vypočítejte rentabilní analýzu pomocí funkce Goal-Seek (vestavěný nástroj Excel)
2. Vypočítejte zlomovou analýzu pomocí vzorce
3. Vypočítejte rentabilní analýzu pomocí grafu

Vypočítejte rentabilní analýzu pomocí Goal-Seek

Případ : Předpokládejme, že chcete prodat nový produkt. Proměnlivé náklady na jednotku a celkové fixní náklady již znáte. Chcete předpovědět možné objemy prodeje a použít je k ocenění produktu. Tady je to, co musíte udělat.

1. Ulehčete stůl a vyplňte položky / data .
   
   
2. V aplikaci Excel zadejte správné vzorce pro výpočet výnosů, variabilních nákladů a zisku.
   1. Tržby = jednotková cena x prodaná jednotka
   2. Variabilní náklady = náklady na jednotku x prodaná jednotka
   3. Zisk = výnosy - variabilní náklady - fixní náklady
   4. Pro výpočet použijte tyto vzorce.
      
      
      
3. Ve svém excelovém dokumentu klikněte na Data> Analýza What-If> vyberte Hledání cíle.
   
   
4. Když otevřete dialogové okno Hledání cíle, postupujte takto:
   1. Uveďte Nastavit buňku jako buňka zisku je v tomto případě buňka B7
   2. Uveďte Hodnotit jako 0
   3. Uveďte Změnou buňky jako Buňka jednotkové ceny , v tomto případě je to buňka B1.
   4. Klikněte na ikonu OK knoflík
      
5. Zobrazí se dialogové okno Stav hledání cíle. Chcete-li to použít, klikněte na OK.
   
   

Goal Seek změní jednotkovou cenu ze 40 na 31 579 a čistý zisk se změní na 0. Pamatujte, že zisk v bodu zvratu je 0. Pokud tedy předpovídáte objem prodeje na 50, jednotková cena nemůže být nižší než 31 579 . Jinak utrpíte ztrátu.

Vypočítejte zlomovou analýzu v aplikaci Excel pomocí vzorce

Rovnováhu v bodě v aplikaci Excel můžete také vypočítat pomocí vzorce. Zde je postup:

1. Vytvořte snadnou tabulku a vyplňte položky / data. V tomto scénáři předpokládáme, že známe prodané jednotky, náklady na jednotku, fixní náklady a zisk.
   
   
2. Použijte vzorec pro výpočet chybějících položek / dat.
1. Zadejte vzorec = B6 / B2 + B4 do buňky B1 k výpočtu jednotkové ceny,
2. Zadejte vzorec = B 1 * B2 do buňky B3 pro výpočet výnosů,
3. Zadejte vzorec = B2 * B4 do buňky B5 pro výpočet variabilních nákladů.

Poznámka : pokud změníte jakoukoli hodnotu, například hodnotu prodané předpokládané jednotky nebo cenu za jednotku nebo fixní náklady, hodnota jednotkové ceny se automaticky změní.

Vypočítejte zlomovou analýzu pomocí grafu

Pokud jste již zaznamenali svá prodejní data, můžete bod zlomu vypočítat pomocí grafu v aplikaci Excel. Zde je postup:

1. Připravte prodejní stůl.V tomto případě předpokládáme, že již známe prodané jednotky, cenu za jednotku a fixní náklady a předpokládáme, že jsou opravené. Musíme provést analýzu rentability podle jednotkové ceny.
   
   
2. Dokončete výpočty tabulky pomocí vzorce
   
   
   1. Do buňky E2 zadejte vzorec = D2 * $ B $ 1 a poté přetáhněte jeho rukojeť automatického vyplňování dolů na rozsah E2: E13
   2. Do buňky F2 zadejte vzorec = D2 * $ B $ 1 + $ B $ 3 a poté přetáhněte jeho rukojeť automatického vyplňování dolů na rozsah F2: F13.
   3. Do buňky G2 zadejte vzorec = E2-F2 a poté přetáhněte jeho rukojeť automatického vyplňování dolů do rozsahu G2: G13.
3. Tento výpočet by vám měl poskytnout zdrojová data rentabilního grafu.
   
   
4. V tabulce aplikace Excel vyberte Sloupec výnosů , Náklady colum n a Sloupec zisku současně a poté klikněte Vložit > Vložit řádek nebo Plošný graf > Čára . Tím se vytvoří spojnicový graf.
   
   
5. Dále klikněte pravým tlačítkem na graf. V místní nabídce klikněte na Vyberte Data.
   
   
6. V Vyberte zdroj dat v dialogovém okně proveďte následující:
1. V Legend Entries (Series) V části vyberte jednu ze sérií podle potřeby. V tomto případě vybereme Příjmy série
2. Klikněte na tlačítko Upravit v Horizontální (kategorie) osa Štítky sekce
3. Zobrazí se dialogové okno s názvem Axis Labels. Do pole zadejte Sloupec Jednotková cena (kromě názvu sloupce) jako rozsah popisu osy
4. Klepněte na OK > OK uložit změny.
   
   
7. Bude vytvořen chat s názvem rentabilní graf. Všimnete si bodu zvratu, ke kterému dochází, když se cena rovná 36.
   
   
8. Podobně můžete vytvořit zlomový graf pro analýzu bodu zvratu podle prodaných jednotek:
   

Jsi hotový. Je to tak jednoduché.

proč nevyhrám můj typ klávesnice

Balení

Vzhled svých dat můžete vylepšit pomocí datové sekce a návrhových nástrojů. Excel umožňuje s daty dělat mnoho dalších věcí.

Pokud hledáte další průvodce nebo si chcete přečíst více článků souvisejících s technologiemi, zvažte přihlášení k odběru našeho zpravodaje. Pravidelně vydáváme výukové programy, novinové články a průvodce, které vám pomohou.

Doporučené čtení

1. 13 tipů a triků pro Excel, díky kterým se stanete profesionálem
2. Top 51 šablon Excel pro zvýšení vaší produktivity
3. Cheat sheet Microsoft Office Excel
4. Nejužitečnější klávesové zkratky aplikace Excel
5. Jakou verzi aplikace Excel mám?