Jak obliczyć analizę progu rentowności w programie Excel?
Analiza progu rentowności to badanie tego, jaka wielkość sprzedaży lub sprzedanych jednostek jest wymagana do osiągnięcia progu rentowności po uwzględnieniu wszystkich stałych i zmiennych kosztów prowadzenia działalności firmy. Analiza progu rentowności ma kluczowe znaczenie w planowaniu biznesowym i finansach przedsiębiorstw, ponieważ założenia dotyczące kosztów i potencjalnej sprzedaży określają, czy firma (lub projekt) jest na dobrej drodze do osiągnięcia rentowności.
Jako prosty przykład weźmy stojak na lemoniadę. Bez względu na to, ile sprzedajesz lemoniady, nadal musisz płacić tę samą kwotę czynszu każdego miesiąca. Czynsz jest więc kosztem stałym. Porównaj to z wydatkami na cytryny: kupisz tylko tyle owoców, ile potrzebujesz, aby zaspokoić popyt na lemoniadę przewidziany dla Twojej firmy. W lecie może to być więcej cytryn; zimą mniej cytryn. Dzięki temu zakupy cytryny są zmienne. Koszty zmienne rosną proporcjonalnie do Twojej firmy.
Wzór na analizę progu rentowności
Mając na uwadze powyższy przykład, próg rentowności występuje, gdy:
Całkowite koszty stałe + całkowite koszty zmienne = przychód
- Całkowite koszty stałe są zwykle znane; obejmują one czynsz, wynagrodzenie, media, koszty odsetek, amortyzację i amortyzację.
- Trudniej jest poznać całkowite koszty zmienne, ale są one możliwe do oszacowania i obejmują takie rzeczy, jak bezpośredni materiał, płatna praca, prowizje i opłaty.
- Przychód to cena jednostkowa * Liczba sprzedanych jednostek
Dzięki tym informacjom możemy rozwiązać algebraicznie dowolny fragment układanki. Zanim to zilustruję, warto wspomnieć o kilku kwestiach.
Po pierwsze, w formule nie ma zgodności co do tego, jaki jest najlepszy korektor dźwięku. Standardowa definicja to przychód, ale problem z wykorzystaniem przychodu polega na tym, że powiedzenie „musimy sprzedać kwotę X na pokrycie kosztów” wyklucza podatki, które są bardzo realnym wydatkiem. W planowaniu biznesowym zawsze powinieneś brać pod uwagę, jak wygląda dochód operacyjny po opodatkowaniu, a ta miara nazywa się Zysk operacyjny netto po opodatkowaniu (NOPAT). Korzystając z NOPAT, uwzględniasz koszty wszystkich faktycznych operacji, w tym wpływ podatków. Jednak szeroko rozumiana definicja wykorzystuje przychody, więc tego właśnie użyjemy.
Drugim krytycznym punktem jest to, że rdzeniem tej formuły jest marża składkowa, która odgrywa kluczową rolę w dźwigni operacyjnej.
Trzecią rzeczą wartą odnotowania jest to, że każdą część równania - całkowite koszty stałe, całkowite koszty zmienne i całkowity przychód - można wyrazić jako „Suma” lub jako miarę na jednostkę w zależności od tego, jakiego konkretnego wymaganego progu rentowności. Zostało to dokładniej zbadane w naszym przykładzie Excel.
Próg rentowności sprzedaży
Gdy spojrzymy na całkowitą sprzedaż, miarą są dolary ($), a nie jednostki:
Próg rentowności = całkowite koszty stałe / (marża wkładowa)
Margines wkładu = 1 - (Zmienne koszty / przychody)
Pamiętaj, że może to być zarówno jednostka, jak i suma, ponieważ jest wyrażone w procentach.
Jednostki progu rentowności
Patrząc na całkowitą sprzedaż, miarą są jednostki, a nie dolary. W zależności od posiadanych danych może być konieczne przetłumaczenie całkowitych wartości dolara na wartości jednostkowe:
Jednostki progowe = całkowite koszty stałe / (cena za jednostkę - zmienny koszt za jednostkę)
Cena progowa
Tutaj rozwiązujemy cenę, biorąc pod uwagę znany koszt stały i zmienny, a także szacunkową liczbę sprzedanych jednostek. Zauważ, że w pierwszych dwóch formułach znamy cenę sprzedaży i w zasadzie wyprowadzamy ilość sprzedaną do osiągnięcia progu rentowności. Ale w tym przypadku musimy oszacować zarówno liczbę sprzedanych jednostek (lub całkowitą sprzedaną ilość) i powiązać to z funkcją ceny sprzedaży, którą rozwiązujemy.
Procent kosztów zmiennych na jednostkę = całkowite koszty zmienne / (zmienna ogółem + całkowite koszty stałe)
Całkowite koszty stałe na jednostkę = całkowite koszty stałe / całkowita liczba jednostek
Cena progowa = 1 / ((1 - Procent całkowitych kosztów zmiennych na jednostkę) * (Łączne stałe koszty na jednostkę))
Zasadniczo wszystkie te formuły można uznać za formę analizy okresu zwrotu, z tym wyjątkiem, że „czas w latach” to efektywnie czas potrzebny do wygenerowania wymaganej liczby sprzedaży w powyższych obliczeniach.
Analiza progu rentowności w programie Excel
Teraz, gdy wiemy, na czym polega analiza progu rentowności, możemy rozpocząć modelowanie w programie Excel. Istnieje wiele sposobów osiągnięcia tego celu. Dwa najbardziej przydatne to utworzenie kalkulatora progu rentowności lub użycie narzędzia Goal Seek, które jest wbudowanym narzędziem Excel.
Pokazujemy kalkulator, ponieważ jest on lepiej zgodny z najlepszymi praktykami modelowania finansowego, stwierdzając, że formuły powinny zostać rozbite i poddane kontroli.
Tworząc analizę scenariusza, możemy nakazać Excelowi obliczanie na podstawie jednostki. ( Uwaga: jeśli tabela wydaje się niewielka, kliknij obraz prawym przyciskiem myszy i otwórz nową kartę, aby uzyskać wyższą rozdzielczość).
Lub na podstawie ceny:
Wreszcie możemy łatwo zbudować matrycę wrażliwości, aby zbadać interakcje tych czynników. Biorąc pod uwagę różne struktury kosztów, widzimy przedział cen progowych od 28 do 133 USD.