Analýza rovnovážného stavu je nástrojem pro hodnocení ziskových příležitostí obchodního modelu a různých cenových strategií. V aplikaci Excel můžete najít fixní náklady, variabilní náklady a možnosti stanovení cen a určit tak bod zlomu produktu. Bod zvratu je počet jednotek, které je třeba prodat za stanovenou cenu, aby se dosáhlo rovnováhy (pokrytí výrobních nákladů).
Krok
Část 1 z 5: Vytvoření tabulky variabilních nákladů
Krok 1. Otevřete Excel a vytvořte sešit
Ke sledování všech vašich nákladů budete potřebovat nějaké pracovní listy v sešitu.
Krok 2. Klikněte na tlačítko „+“vedle „List1“ve spodní části obrazovky
Tím se otevře nový pracovní dokument.
Krok 3. Pojmenujte nový list „Variabilní náklady“
Tento list bude obsahovat všechny tabulky, které monitorují všechny variabilní náklady, jako jsou náklady na dopravu, provize atd.
Krok 4. Vytvořte popisek názvu pro nový list
Chcete -li vytvořit standardní tabulku variabilních nákladů, zadejte do buňky A1 „Popis“a do buňky B1 „Částka“.
Krok 5. Do sloupce A zadejte název obchodní variabilní ceny
Pod nadpis „Popis“zadejte typ variabilních nákladů produktu, s nimiž se má manipulovat.
Krok 6. Sloupec B („Částka“) prozatím ponechte prázdný
Tento sloupec bude později vyplněn náklady, které skutečně během procesu nastaly.
Krok 7. Ze zadaných dat vytvořte tabulku
Proměňte data v tabulky, abyste je mohli snadno zadat do vzorců:
- Kliknutím a přetažením kurzoru myši přes všechny buňky vyberte všechna data, včetně řádku názvu a prázdných buněk.
- Klikněte na tlačítko „Formátovat jako tabulku“. Najdete ho pod štítkem Domů. Pokud používáte Excel pro Mac, klikněte na štítek Tabulky, klikněte na tlačítko „Nový“a vyberte „Vložit tabulku se záhlavími“.
- Zaškrtněte políčko „Moje tabulka obsahuje záhlaví“. Štítek prvního řádku bude tedy nastaven jako popisek názvu.
- Klikněte na pole „Název tabulky“v pravém horním rohu a změňte název na „Variabilní náklady“
Část 2 z 5: Vytvoření tabulky fixních nákladů
Krok 1. Klikněte na tlačítko „+“vedle „Variabilní náklady“ve spodní části obrazovky
Tím se otevře nový pracovní dokument.
Krok 2. Přejmenujte nový list na „Fixní náklady“
Tento list bude obsahovat všechny fixní náklady produktu, jako je nájem, pojištění a další náklady, které se nemění.
Krok 3. Vytvořte titulní štítek
Stejně jako u listu s variabilními náklady vytvořte v buňce A1 popisek „Popis“a v buňce B1 popisek „Částka“.
Krok 4. Do sloupce A zadejte názvy fixních nákladů vaší firmy
Vyplňte první sloupec s popisem fixních nákladů, například „Pronájem“.
Krok 5. Sloupec B („Částka“) prozatím ponechte prázdný
Tyto náklady vyplníte po dokončení veškerých papírů.
Krok 6. Vytvořte tabulku ze zadaných dat
Na tomto listu vyberte vše, co jste vytvořili, včetně názvu:
- Na domovském štítku klikněte na tlačítko „Formátovat jako tabulku“.
- Zaškrtnutím políčka „Moje tabulka obsahuje záhlaví“změníte řádek 1 na název tabulky.
- Klikněte na pole „Název tabulky“a zadejte název tabulky „Fixní náklady“
Část 3 z 5: Vytvoření pracovního listu s rovnováhou
Krok 1. Přejmenujte list1 na „BEP“a vyberte jej
Tento list bude obsahovat váš hlavní graf BEP (Break Even Point). Název nemusíte měnit na „BEP“, ale pokud to uděláte, usnadní vám to práci.
Krok 2. Nastavte rozvržení pro vyrovnaný list
V zájmu tohoto příkladu vytvořte list pomocí následujícího rozložení:
- A1: Prodej - Toto je štítek pro sekci Prodej na listu.
- B2: Cena za jednotku - toto je cena účtovaná za každou prodanou položku.
- B3: Prodané jednotky - Toto je počet prodaných jednotek za stanovenou cenu v daném časovém období.
- A4: Náklady - Toto je štítek pro sekci Náklady na listu.
- B5: Variabilní náklady - Jedná se o náklady na produkt, které můžete ovládat (poštovné, provizní sazba atd.)
- B6: Fixní náklady - Jedná se o náklady na produkt, nad nimiž nemáte žádnou kontrolu (pronájem zařízení, pojištění atd.)
- A7: Výnosy - Toto je množství peněz generovaných prodejem produktu před odečtením nákladů.
- B8: Unit Margin - Toto je množství peněz vydělaných na jednotku po odečtení nákladů.
- B9: Hrubá zisková marže - Jedná se o celkovou částku peněz generovaných z prodejních jednotek produktu po odečtení nákladů.
- A10: BEP-Toto je štítek pro část bodu zlomu v listu.
- B11: Jednotky - Toto je počet jednotek, které je třeba prodat, aby se pokryly výrobní náklady.
Krok 3. Změňte číselný formát pro výstupní a vstupní buňky
Aby se data zobrazila správně, je třeba změnit číselný formát pro konkrétní buňky:
- Zvýrazněte C2, C5, C6, C8 a C9. Klikněte na rozbalovací nabídku v části „Číslo“štítku Domů a vyberte „Měna“.
- Zvýrazněte C3 a C11. Klikněte na rozbalovací nabídku a vyberte „Další formáty čísel“. Vyberte „Číslo“a nastavte „Desetinná místa“na „0“.
Krok 4. Vytvořte rozsah, který chcete použít ve vzorcích
Vyberte a vytvořte následující rozsahy, aby vzorec fungoval. Tímto způsobem lze vytvářet proměnné, které se zadávají do vzorců, takže můžete snadno sledovat a aktualizovat jejich hodnoty.
- Vyberte B2: C3 a poté klikněte na štítek „Vzorce“. Klikněte na „Vytvořit z výběru“a pokračujte „OK“.
- Vyberte B5: C6 a poté klikněte na štítek „Vzorce“. Klikněte na „Vytvořit z výběru“a pokračujte „OK“.
- Vyberte B8: C9 a poté klikněte na štítek „Vzorce“. Klikněte na „Vytvořit z výběru“a pokračujte „OK“.
- Vyberte B11: C11 a poté klikněte na štítek „Vzorce“. Klikněte na „Vytvořit z výběru“a pokračujte „OK“.
Část 4 z 5: Zadávání vzorců
Krok 1. Zadejte vzorec variabilních nákladů
Tento vzorec vypočítá celkové variabilní náklady na prodané zboží. Klikněte na C5 a zadejte následující vzorec:
= SUM (variabilní náklady)*Prodané jednotky
Krok 2. Zapojte do vzorce fixní náklady
Excel vypočítá celkové fixní náklady na váš produkt. Klikněte na C6 a zadejte následující vzorec:
= SUM (fixní náklady)
Krok 3. Zadejte vzorec jednotkové marže
Tento vzorec vypočítá výslednou marži po zahrnutí variabilních nákladů. Klikněte na C8 a zadejte následující vzorec:
= Price_Per_Unit-SUM (variabilní náklady)
Krok 4. Zadejte vzorec marže hrubého zisku
Tento vzorec určuje celkovou částku zisku pro všechny jednotky po odečtení variabilních nákladů. Klikněte na C9 a zadejte následující vzorec:
= Margin_Unit*Unit_Sold
Krok 5. Zadejte vzorec BEP
Tento vzorec bere fixní náklady a porovnává je s marží, abyste věděli, kolik jednotek potřebujete prodat, abyste se vyrovnali. Klikněte na C11 a zadejte následující vzorec:
= IFERROR (Fixed_Cost/Unit_Margin, 0)
Část 5 z 5: Určení bodu zvratu
Krok 1. Zadejte variabilní náklady vaší firmy
Vraťte se do tabulky Variabilní náklady a vyplňte všechny náklady spojené s produktem. Čím přesnější jsou evidované náklady, tím přesnější budou výpočty.
Každá cena v tabulce variabilních nákladů musí být za prodanou jednotku
Krok 2. Zadejte fixní náklady vaší firmy
Tyto náklady zadejte do tabulky fixních nákladů. Jedná se o náklady na podnikání, všechny stanovené ve stejných intervalech (např. Měsíční poplatky).
Krok 3. Zadejte cenu za jednotku
Do pracovního listu BEP zadejte odhadovanou cenu za jednotku. Postupem výpočtu jej budete moci upravit.
Krok 4. Zadejte počet jednotek, které chcete prodat
Toto je požadovaný počet prodejů ve stejném časovém rámci jako fixní náklady. Pokud například vaše fixní náklady zahrnují měsíční nájem a pojištění, prodané jednotky představují počet prodaných jednotek za stejné časové období.
Krok 5. Přečtěte si výstup „Unit“
Ve výstupní buňce jednotek (C11) se zobrazí počet jednotek, které bude nutné prodat, aby se dosáhlo rovnoměrnosti. Tento údaj se bude měnit v závislosti na ceně za jednotku a také na tabulkách variabilních nákladů a fixních nákladů.
Krok 6. Proveďte úpravy cen a nákladů
Změnou ceny za jednotku se také změní počet jednotek nutných k vyrovnání. Zkuste změnit prodejní cenu jednotky a uvidíte dopad na hodnotu BEP.
