Jak przygotować analizę progu rentowności w Excelu: Kompletny przewodnik
Analiza progu rentowności pozwala określić poziom sprzedaży, przy którym całkowite przychody zrównują się z całkowitymi kosztami, co oznacza, że firma nie odnotowuje ani zysku, ani straty. Aby przygotować taką analizę w Excel, wprowadź koszty stałe, cenę jednostkową oraz jednostkowy koszt zmienny. Podziel koszty stałe przez różnicę między ceną a kosztem zmiennym, a następnie przedstaw na wykresie przychody w zestawieniu z kosztami całkowitymi.
W tym poradniku omówiono wzory, niezbędne dane wejściowe, trzy kroki w Excel oraz praktyczny przykład. Pokazano w nim również, jak używać narzędzia Goal Seek i tabel danych, aby sprawdzić, co się zmieni w przypadku wahań cen lub kosztów.
Czym jest analiza progu rentowności
Amerykańska agencja US Small Business Administration definiuje próg rentowności jako „punkt, w którym całkowity koszt i całkowity przychód są równe”. Poniżej tego punktu każda sprzedaż wciąż nie pozwala firmie na pokrycie kosztów. Powyżej niego każda sprzedaż generuje zysk.
SBA wymienia analizę progu rentowności jako jeden z powodów kalkulacji kosztów rozpoczęcia działalności, obok szacowania zysków i zabezpieczania kredytów. Kredytodawcy i inwestorzy często o nią proszą, ponieważ pokazuje ona, ile firma musi sprzedać, zanim przestanie przynosić straty.
Analiza progu rentowności odpowiada na trzy praktyczne pytania. Ile jednostek musimy sprzedać? Jaki przychód to reprezentuje? Oraz jak wrażliwa jest ta odpowiedź na zmiany cen i kosztów?
Służy ona również jako szybki test nowego pomysłu. Zanim zdecydujesz się na nowy produkt lub lokalizację, oszacuj trzy dane wejściowe. Następnie zadaj sobie pytanie, czy wymagany wolumen jest realistyczny dla rynku, który obsługujesz.
Wzory na próg rentowności
SBA podaje dwie wersje wzoru na próg rentowności.
W jednostkach ilościowych:
Próg rentowności (w jednostkach) = koszty stałe / (cena sprzedaży za jednostkę - jednostkowy koszt zmienny)
W wartości sprzedaży (walucie):
Próg rentowności (wartościowy) = koszty stałe / marża pokrycia
SBA wyjaśnia drugie pojęcie jako „różnicę między ceną produktu a kosztem jego wytworzenia”. W przypadku wzoru wartościowego marża ta jest obliczana jako wskaźnik: cena minus koszt zmienny, podzielone przez cenę.
To rozróżnienie ma znaczenie w arkuszu kalkulacyjnym. Jednostkowa marża pokrycia to kwota kwotowa, na przykład $3 przy produkcie za $5. Wskaźnik marży pokrycia to wartość procentowa, na przykład 60%. Użyj kwoty kwotowej we wzorze ilościowym, a wskaźnika procentowego we wzorze wartościowym.
SBA wyznacza również granicę: „Ta analiza progu rentowności opiera się na założeniu jednego produktu lub usługi”. W dalszej części omówiono, co zrobić w przypadku posiadania więcej niż jednego produktu.
Co jest potrzebne przed rozpoczęciem
Każdą analizę progu rentowności napędzają trzy dane wejściowe. Prawidłowy podział kosztów ma większe znaczenie niż sama praca w Excel.
| Dana wejściowa | Co oznacza | Przykłady |
|---|---|---|
| Koszty stałe | Koszty, które pozostają takie same bez względu na wielkość sprzedaży | Czynsz, wynagrodzenia, ubezpieczenie, subskrypcje oprogramowania |
| Jednostkowy koszt zmienny | Koszty, które rosną wraz z każdą sprzedaną jednostką | Materiały, opakowania, opłaty płatnicze, wysyłka |
| Cena jednostkowa | Kwota, którą klient płaci za jedną jednostkę | Cena katalogowa lub średnia cena sprzedaży po rabatach |
Używaj tego samego okresu dla wszystkich danych wejściowych. Jeśli czynsz jest rozliczany miesięcznie, wynikiem będzie miesięczny próg rentowności. Połączenie rocznego wynagrodzenia z miesięcznym czynszem da wynik, który nic nie znaczy.
Jednostkowy koszt zmienny to dana wejściowa, którą ludzie najczęściej zgadują. Zamiast tego oszacuj go na podstawie danych historycznych. Weź całkowite koszty zmienne z ostatniego kwartału i podziel je przez liczbę jednostek sprzedanych w tym samym kwartale. Jeśli wynik znacznie różni się w poszczególnych kwartałach, użyj średniej z kilku okresów.
Niektóre koszty mają charakter mieszany. Abonament telefoniczny z opłatą podstawową i opłatami za zużycie ma część stałą i zmienną. Rozdziel je, zamiast zgadywać, do którego koszyka należą.
Jak przygotować analizę progu rentowności w Excel
Trzy poniższe kroki pozwolą zbudować działający model progu rentowności i wykres. Wykorzystują one standardowe formuły, które działają również w Google Sheets.
Krok 1: Skonfiguruj dane wejściowe
Otwórz pusty arkusz i nazwij komórki od A1 do A3 jako Koszty stałe, Cena jednostkowa oraz Jednostkowy koszt zmienny. Wprowadź wartości w komórkach od B1 do B3.
Przechowuj dane wejściowe w ich własnych komórkach, nigdy nie wpisuj ich bezpośrednio do formuł. Dzięki temu każde kolejne obliczenie zaktualizuje się automatycznie przy zmianie danych wejściowych, a Ty będziesz mógł testować różne scenariusze, edytując tylko jedną komórkę.
Sformatuj komórki wejściowe za pomocą jasnego koloru wypełnienia. Sygnalizuje to każdemu, kto otworzy plik, które liczby może modyfikować.
Krok 2: Oblicz próg rentowności
Dodaj obliczenia w wierszach od 5 do 8.
- W komórce A5 wpisz Jednostkowa marża pokrycia, a w B5 wprowadź
=B2-B3. - W komórce A6 wpisz Próg rentowności w jednostkach, a w B6 wprowadź
=ROUNDUP(B1/B5,0). - W komórce A7 wpisz Wskaźnik marży pokrycia, a w B7 wprowadź
=B5/B2. - W komórce A8 wpisz Próg rentowności wartościowo, a w B8 wprowadź
=B1/B7.
Funkcja ROUNDUP ma kluczowe znaczenie w komórce B6. Nie można sprzedać ułamka jednostki, a zaokrąglenie w dół spowodowałoby, że zabrakłoby Ci odrobinę do osiągnięcia progu rentowności.
Upewnij się, że wartość w komórce B8 jest w przybliżeniu równa wartości z komórki B6 pomnożonej przez cenę. Jeśli tak nie jest, jedna z danych wejściowych znajduje się w niewłaściwej komórce.
Krok 3: Zbuduj wykres progu rentowności
Poniżej obliczeń stwórz małą tabelę. W kolumnie D wypisz wolumeny sprzedaży od 0 w górę w równych odstępach, na przykład 0, 500 i 1,000. Kontynuuj powyżej swojego progu rentowności. W kolumnie E oblicz przychody za pomocą formuły =D11*$B$2. W kolumnie F oblicz koszt całkowity za pomocą formuły =$B$1+D11*$B$3.
Zaznacz kolumny od D do F i wstaw wykres. Najlepiej sprawdzi się wykres punktowy z prostymi liniami, ponieważ traktuje kolumnę jednostek jako rzeczywistą oś liczbową.
Linia przychodów zaczyna się od zera i gwałtownie rośnie. Linia kosztów całkowitych zaczyna się na poziomie kosztów stałych i rośnie wolniej. Miejsce ich przecięcia to próg rentowności. Dodaj tam etykietę danych, aby czytelnicy nie musieli go szacować wzrokowo. Zaznacz obszar po prawej stronie punktu przecięcia jasnym kolorem, aby pokazać strefę zysku.
Praktyczny przykład
Wózek z kawą generuje koszty stałe w wysokości $6,000 miesięcznie. Sprzedaje kawę po $5.00 za kubek, a koszt każdego kubka to $2.00 (ziarna, mleko, kubki i opłaty za płatności kartą).
| Obliczenie | Wynik |
|---|---|
| Jednostkowa marża pokrycia | $5.00 - $2.00 = $3.00 |
| Próg rentowności w jednostkach | $6,000 / $3.00 = 2,000 kubków |
| Wskaźnik marży pokrycia | $3.00 / $5.00 = 60% |
| Próg rentowności wartościowo | $6,000 / 0.60 = $10,000 |
Wózek musi sprzedać 2,000 kubków miesięcznie, co daje $10,000 przychodu, aby pokryć swoje koszty. Każdy kolejny sprzedany kubek generuje $3.00 zysku.
Zysk docelowy oblicza się według tej samej logiki. Aby zarobić $3,000 miesięcznie, dodaj tę kwotę do kosztów stałych: $9,000 podzielone przez $3.00 daje 3,000 kubków.
Margines bezpieczeństwa pokazuje, jak duży zapas posiadasz. Jeśli wózek planuje sprzedać 2,600 kubków, sprzedaż może spaść o 600 kubków, zanim firma zacznie przynosić straty. Stanowi to około 23% oczekiwanej sprzedaży.
Użyj narzędzia Goal Seek, aby znaleźć cenę progu rentowności
Czasami pytanie brzmi odwrotnie. Znasz swój wolumen sprzedaży i chcesz dowiedzieć się, jaka cena pozwoli osiągnąć próg rentowności.
Strona pomocy technicznej firmy Microsoft w prosty sposób opisuje ten przypadek użycia. Znasz wynik, jaki chcesz uzyskać z formuły, ale nie znasz wartości wejściowej, która go generuje. Narzędzie Goal Seek (Szukaj wyniku) znajduje tę wartość, dostosowując jedną komórkę.
Dodaj komórkę zysku, na przykład B9, z formułą =B2*C1-B1-B3*C1, gdzie C1 zawiera oczekiwany wolumen. Następnie postępuj zgodnie z instrukcjami firmy Microsoft. Na karcie Dane, w grupie Prognoza, wybierz Analiza warunkowa, a następnie Szukaj wyniku (Goal Seek). Ustaw komórkę B9 na wartość 0, zmieniając komórkę B2.
Excel dostosuje cenę, aż zysk wyniesie dokładnie zero. Przy sprzedaży 1,500 kubków miesięcznie, wózek z kawą potrzebowałby ceny na poziomie $6.00, aby osiągnąć próg rentowności.
Microsoft zwraca uwagę na jedno ograniczenie: „Narzędzie Goal Seek działa tylko z jedną zmienną wartością wejściową”. Aby rozwiązać problem dla kilku danych wejściowych jednocześnie, odsyła do dodatku Solver.
Wrażliwość: co jeśli zmieni się cena lub koszty?
Pojedyncza wartość progu rentowności ukrywa to, jak bardzo może być on niestabilny. Tabela wrażliwości pokazuje wynik dla całego zakresu danych wejściowych.
W przypadku wózka z kawą niewielka zmiana ceny lub kosztu wyraźnie przesuwa próg rentowności:
| Scenariusz | Marża pokrycia | Próg rentowności w jednostkach |
|---|---|---|
| Scenariusz bazowy: cena $5.00, koszt $2.00 | $3.00 | 2,000 |
| Cena rośnie do $5.50 | $3.50 | 1,715 |
| Koszt zmienny rośnie do $2.50 | $2.50 | 2,400 |
| Koszty stałe rosną do $7,500 | $3.00 | 2,500 |
Funkcja Tabela danych w Excel, dostępna w sekcji Analiza warunkowa, pozwala automatycznie zbudować taką siatkę. Umieść zakres cen w jednej kolumnie, a zakres kosztów zmiennych w jednym wierszu. Następnie wskaż tabeli komórkę z progiem rentowności w jednostkach.
Porównaj zmiany o tej samej wielkości, aby zobaczyć, która dana wejściowa ma największe znaczenie. W tym przykładzie zmiana o 50 centów dodaje 400 kubków do progu rentowności, gdy dotyczy kosztu zmiennego, ale odejmuje tylko 285, gdy dotyczy ceny. Właśnie dlatego koszt zmienny jest często pierwszym kosztem, który warto negocjować.
Analiza progu rentowności dla więcej niż jednego produktu
Wzór SBA zakłada jeden produkt. Większość firm sprzedaje ich kilka, a każdy z nich ma własną marżę.
Zazwyczaj stosuje się średnią ważoną marżę pokrycia. Oszacuj udział każdego produktu w sprzedaży, pomnóż marżę każdego produktu przez jego udział i zsumuj wyniki. Podziel koszty stałe przez tę ważoną wartość, aby uzyskać próg rentowności w jednostkach dla całego asortymentu.
Oto krótki przykład. Produkt A ma marżę $3 i stanowi 60% sprzedawanych jednostek. Produkt B ma marżę $5 i stanowi 40%. Ważona marża wynosi $3 razy 0.6 plus $5 razy 0.4, czyli $3.80. Przy kosztach stałych wynoszących $7,600 próg rentowności wynosi 2,000 jednostek: 1,200 produktu A i 800 produktu B.
SBA zauważa, że warto również przeprowadzić te obliczenia indywidualnie dla każdego produktu, jeśli sprzedaż różni się z miesiąca na miesiąc. Dodaje też ostrzeżenie, o którym warto pamiętać: próg rentowności jest wartością szacunkową na potrzeby planowania i oceny wiarygodności dla kredytodawców i nie ma na celu zastąpienia szczegółowej księgowości.
Jeśli struktura sprzedaży ulegnie zmianie, próg rentowności zmieni się wraz z nią. Przesunięcie w stronę produktów o niskiej marży podnosi próg rentowności, nawet jeśli całkowita sprzedaż pozostaje na stałym poziomie.
Jak prezentować analizę progu rentowności
Większość odbiorców potrzebuje trzech liczb i jednego wykresu. Zacznij od progu rentowności w jednostkach, wartości sprzedaży na progu rentowności oraz marginesu bezpieczeństwa. Umieść wykres bezpośrednio pod spodem, z wyraźnie oznaczonym punktem przecięcia.
Następnie przedstaw tabelę wrażliwości. Odpowiada ona na pytanie, które każdy kredytodawca i menedżer zadaje w następnej kolejności: co się stanie, jeśli koszty wzrosną lub sprzedaż okaże się niższa od oczekiwanej?
Zadbaj o to, aby dane wejściowe były widoczne. Krótka lista kosztów stałych, cena oraz jednostkowy koszt zmienny pozwalają czytelnikowi zweryfikować Twoją logikę w minutę. SBA zauważa, że próg rentowności to kluczowe obliczenie w biznesplanie, więc należy spodziewać się, że zostanie ono dokładnie przeanalizowane.
Najczęstsze błędy
- Mieszanie okresów. Miesięczny czynsz połączony z rocznym wynagrodzeniem daje bezużyteczny wynik. Przelicz wszystko na jeden okres.
- Używanie ceny katalogowej, gdy klienci płacą mniej. Jeśli rabaty są powszechne, użyj średniej ceny faktycznie uzyskiwanej ze sprzedaży.
- Zapominanie o drobnych kosztach zmiennych. Opłaty za płatności kartą, opakowania i wysyłka sumują się na każdą jednostkę i zmieniają ostateczny wynik.
- Ignorowanie mocy produkcyjnych. Koszty stałe często gwałtownie rosną przy określonych wolumenach, na przykład gdy potrzebna jest druga zmiana lub większa przestrzeń. Przeliczaj próg rentowności na każdym etapie.
- Zaokrąglanie w dół. Wynik ułamkowy oznacza, że potrzebujesz kolejnej pełnej jednostki, a nie poprzedniej.
Szybsze działanie dzięki AI
Metoda arkusza kalkulacyjnego sprawdza się świetnie, gdy podział kosztów jest już jasny. Najbardziej czasochłonne jest zazwyczaj dojście do tego etapu, ponieważ koszty znajdują się w eksporcie z księgi głównej lub na stosie faktur.
Przestrzeń robocza AI może zająć się tym sortowaniem. Prześlij wyeksportowane koszty do Powerdrill Bloom i poproś o sklasyfikowanie każdego wiersza jako koszt stały lub zmienny, a następnie o obliczenie progu rentowności w jednostkach i wartościowo. W tym samym zapytaniu poproś o tabelę wrażliwości i wykres.
Przejrzyj klasyfikację, zanim zaufasz wynikom. Koszty mieszane, takie jak opłaty za media, często wymagają indywidualnej oceny, którą możesz podjąć tylko Ty.
W szerszym kontekście planowania strony generator analizy wrażliwości oraz modelowanie finansowe AI omawiają powiązane modele. Nasz przewodnik po raporcie budżet vs. wykonanie pokazuje, jak śledzić, czy rzeczywiste wyniki plasują się powyżej, czy poniżej planu. Jeśli chodzi o harmonogram, a nie tylko sumy, raport z przepływów pieniężnych pokazuje, kiedy gotówka faktycznie wpływa.
Jeśli masz gotowy eksport kosztów, możesz wypróbować Powerdrill Bloom i porównać uzyskany próg rentowności z własnym arkuszem kalkulacyjnym.
Najczęściej zadawane pytania
Co to jest analiza progu rentowności?
Analiza progu rentowności pozwala obliczyć poziom sprzedaży, przy którym całkowity przychód zrównuje się z całkowitym kosztem. W tym punkcie firma nie odnotowuje ani zysku, ani straty. Jest powszecznie stosowana w biznesplanach i wnioskach kredytowych.
Jak obliczyć próg rentowności w jednostkach?
Podziel koszty stałe przez jednostkową marżę pokrycia, która stanowi cenę sprzedaży pomniejszoną o jednostkowy koszt zmienny. Przy kosztach stałych wynoszących $6,000 i marży jednostkowej $3, próg rentowności wynosi 2,000 jednostek.
Jak zrobić analizę progu rentowności w Excel?
Wprowadź koszty stałe, cenę i koszt zmienny w osobnych komórkach. Oblicz marżę jako cenę minus koszt zmienny, a następnie podziel przez nią koszty stałe. Przedstaw przychody i koszty całkowite na wykresie w odniesieniu do jednostek i odczytaj punkt przecięcia.
Jaka jest różnica między marżą pokrycia a wskaźnikiem marży pokrycia?
Jednostkowa marża pokrycia to kwota kwotowa: cena minus koszt zmienny. Wskaźnik to ta kwota podzielona przez cenę, wyrażona w procentach. Użyj kwoty kwotowej do obliczenia progu rentowności w jednostkach, a wskaźnika procentowego do obliczenia progu rentowności wartościowo.
Dlaczego analiza progu rentowności jest ważna?
Pokazuje, ile firma musi sprzedać, zanim przestanie przynosić straty. Ujawnia również, która dana wejściowa – np. cena, koszt zmienny czy czynsz – najbardziej przesuwa ten próg. SBA wymienia ją wśród powodów kalkulacji kosztów rozpoczęcia działalności.
Źródła: US Small Business Administration, Calculate your startup costs · Microsoft Support, Use Goal Seek. Praktyczny przykład opiera się na przykładowych danych.