Analiza progu rentowności to narzędzie do oceny potencjału zysku modelu biznesowego oraz do oceny różnych strategii cenowych. W programie Excel można łatwo zestawiać koszty stałe, koszty zmienne i opcje cenowe, aby określić próg rentowności produktu. Jest to liczba jednostek, które musisz sprzedać po ustalonej przez Ciebie cenie, aby wyjść na zero.
Kroki
Część 1 z 5: Tworzenie tabeli kosztów zmiennych
Krok 1. Otwórz Excel i utwórz nowy pusty skoroszyt
W tym skoroszycie utworzysz wiele arkuszy do obsługi śledzenia wszystkich kosztów.
Krok 2. Kliknij przycisk „+” obok „Arkusz1” u dołu ekranu
Spowoduje to utworzenie nowego pustego arkusza roboczego.
Krok 3. Zmień nazwę nowego arkusza na „Koszty Zmienne
" Ten arkusz zawiera tabelę, w której śledzone są wszystkie koszty zmienne Twojego produktu, takie jak wysyłka, prowizja i inne koszty.
Krok 4. Utwórz etykiety nagłówka dla nowego arkusza
Aby utworzyć podstawową tabelę kosztów zmiennych, wpisz „Opis” do A1 i „Kwota” do B1.
Krok 5. Wpisz nazwy kosztów zmiennych Twojej firmy w kolumnie A
Poniżej nagłówka „Opis” wprowadź rodzaje kosztów zmiennych, którymi będziesz się zajmował dla swojego produktu.
Krok 6. Na razie pozostaw kolumnę B („Kwota”) pustą
Rzeczywiste koszty zostaną podane w dalszej części procesu.
Krok 7. Utwórz tabelę z wprowadzonych danych
Przekształcenie danych w tabelę ułatwi późniejsze podłączanie do formuł:
- Wybierz wszystkie dane, w tym wiersz nagłówka i puste kwoty, klikając i przeciągając myszą na wszystkie komórki.
- Kliknij przycisk „Formatuj jako tabelę”. Znajdziesz to na karcie Strona główna. Jeśli używasz programu Excel dla komputerów Mac, kliknij kartę Tabele, kliknij przycisk „Nowy”, a następnie wybierz „Wstaw tabelę z nagłówkami”.
- Zaznacz pole „Moja tabela ma nagłówki”. Pozwoli to zachować etykiety w pierwszym wierszu jako etykiety nagłówka.
- Kliknij pole „Nazwa tabeli” w prawym górnym rogu i nadaj mu nazwę „Koszty zmienne”.
Część 2 z 5: Tworzenie tabeli kosztów stałych
Krok 1. Kliknij przycisk „+” obok „Kosztów zmiennych” u dołu ekranu
Spowoduje to utworzenie kolejnego pustego arkusza roboczego.
Krok 2. Zmień nazwę nowego arkusza na „FixedCosts
" Ten arkusz zawiera wszystkie stałe koszty produktu, takie jak czynsz, ubezpieczenie i inne koszty, które się nie zmieniają.
Krok 3. Utwórz etykiety nagłówka
Podobnie jak w przypadku arkusza Koszty zmienne, utwórz etykietę „Opis” w komórce A1 i etykietę „Kwota” w komórce B1.
Krok 4. Wpisz nazwy kosztów stałych Twojej firmy w kolumnie A
Wypełnij pierwszą kolumnę opisami kosztów stałych, np. „Wynajem”.
Krok 5. Na razie pozostaw kolumnę B („Kwota”) pustą
Koszty te zostaną uzupełnione po utworzeniu pozostałej części arkusza kalkulacyjnego.
Krok 6. Utwórz tabelę z wprowadzonych danych
Wybierz wszystko, co utworzyłeś w tym arkuszu, w tym nagłówki:
- Kliknij przycisk „Formatuj jako tabelę” na karcie Strona główna.
- Zaznacz „Moja tabela ma nagłówki”, aby zmienić wiersz 1 w nagłówki tabeli.
- Kliknij pole „Nazwa tabeli” i nazwij tabelę „Koszty stałe”.
Część 3 z 5: Tworzenie arkusza progu rentowności
Krok 1. Zmień nazwę Sheet1 na „BEP” i wybierz go
Ten arkusz będzie zawierał Twój główny wykres BEP (Break Even Point). Nie musisz zmieniać jego nazwy na „BEP”, ale nawigacja po skoroszycie będzie łatwiejsza.
Krok 2. Utwórz układ arkusza rentowności
Na potrzeby tego przykładu utwórz arkusz, korzystając z następującego układu:
- O1: Sprzedaż - jest to etykieta sekcji Sprzedaż w arkuszu kalkulacyjnym.
- B2: Cena za sztukę - będzie to cena, jaką naliczasz za każdy sprzedany przedmiot.
- B3: Sprzedane jednostki - będzie to liczba sprzedanych jednostek po określonej cenie w określonym przedziale czasowym.
- A4: Koszty - jest to etykieta sekcji Koszty w arkuszu kalkulacyjnym.
- B5: Koszty zmienne - są to koszty produktu, nad którymi masz kontrolę (wysyłka, stawki prowizji itp.)
- B6: Koszty stałe - są to koszty produktu, nad którymi nie masz kontroli (wynajem lokalu, ubezpieczenie itp.)
- O7: Przychody - jest to kwota, jaką generuje sprzedaż Twoich produktów, zanim zostaną uwzględnione koszty.
- B8: Marża jednostkowa - Jest to kwota, którą zarabiasz na jednostkę po uwzględnieniu kosztów.
- B9: Marża brutto - Jest to całkowita kwota pieniędzy, którą zarabiasz na wszystkich sprzedanych jednostkach po kosztach.
- O10: BEP - jest to etykieta sekcji progu rentowności w arkuszu kalkulacyjnym.
- B11: Jednostki - Jest to liczba jednostek, które musisz sprzedać, aby dopasować się do nakładu kosztów.
Krok 3. Zmień formaty liczbowe dla komórek wyjściowych i wejściowych
Aby dane były wyświetlane poprawnie, musisz zmienić formaty liczbowe niektórych komórek:
- Podświetl C2, C5, C6, C8 i C9. Kliknij menu rozwijane w sekcji „Liczba” na karcie Strona główna i wybierz „Waluta”.
- Podświetl C3 i C11. Kliknij menu rozwijane i wybierz „Więcej formatów liczb”. Wybierz „Liczba”, a następnie ustaw „Miejsca dziesiętne” na „0”.
Krok 4. Utwórz zakresy do wykorzystania w formułach
Wybierz i utwórz następujące zakresy, aby Twoje formuły działały. Spowoduje to utworzenie zmiennych, które można podłączyć do formuł, umożliwiając łatwe odwoływanie się do tych wartości i aktualizowanie ich.
- Wybierz B2:C3, a następnie kliknij zakładkę „Formuły”. Kliknij „Utwórz z wyboru”, a następnie kliknij „OK”.
- Wybierz B5:C6, a następnie kliknij zakładkę „Formuły”. Kliknij „Utwórz z zaznaczenia”, a następnie kliknij „OK”.
- Wybierz B8:C9, a następnie kliknij zakładkę „Formuły”. Kliknij „Utwórz z wyboru”, a następnie kliknij „OK”.
- Wybierz B11:C11, a następnie kliknij zakładkę „Formuły”. Kliknij „Utwórz z zaznaczenia”, a następnie kliknij „OK”.
Część 4 z 5: Wprowadzanie formuł
Krok 1. Wprowadź formułę kosztów zmiennych
Spowoduje to obliczenie łącznych kosztów zmiennych dla liczby sprzedawanych przedmiotów. Kliknij C5 i wprowadź następującą formułę:
=SUMA(Koszty Zmienne)*Jednostki_Sprzedane
Krok 2. Wprowadź formułę kosztów stałych
Spowoduje to obliczenie całkowitych kosztów stałych Twojego produktu. Kliknij C6 i wprowadź następującą formułę:
=SUMA(koszty stałe)
Krok 3. Wprowadź formułę marży jednostkowej
Spowoduje to obliczenie marży, którą uzyskasz po uwzględnieniu kosztów zmiennych. Kliknij C8 i wprowadź następującą formułę:
=Cena_za_jednostkę-SUM (koszty zmienne)
Krok 4. Wprowadź formułę marży brutto
To określa całkowitą kwotę, jaką zarabiasz za wszystkie jednostki, które sprzedajesz po kosztach zmiennych. Kliknij C9 i wprowadź następującą formułę:
=Marża_jednostkowa*Jednostki_Sprzedane
Krok 5. Wprowadź formułę BEP
To pobiera Twoje koszty stałe i porównuje je z Twoimi marżami, informując Cię, ile jednostek musisz sprzedać, aby wyjść na zero. Kliknij C11 i wprowadź następującą formułę:
=JEŻELIBŁĄD(Stałe_koszty/jednostkowa_marża, 0)
Część 5 z 5: Określanie progu rentowności
Krok 1. Wprowadź koszty zmienne swojej firmy
Wróć do tabeli VariableCosts i wypełnij wszystkie koszty związane z Twoim produktem. Im dokładniejszy jesteś tutaj, tym dokładniejsze będą Twoje obliczenia BEP.
Każdy koszt w tabeli VariableCosts powinien być na sprzedaną jednostkę
Krok 2. Wprowadź koszty stałe swojej firmy
Wprowadź te koszty do tabeli Kosztów stałych. Są to koszty prowadzenia firmy i powinny być ustawione na ten sam przedział (na przykład koszty miesięczne).
Krok 3. Wprowadź cenę za sztukę
W arkuszu BEP wprowadź początkową szacunkową cenę za jednostkę. Będziesz mógł to dostosować podczas wykonywania obliczeń
Krok 4. Wprowadź liczbę jednostek, które chcesz sprzedać
Jest to liczba jednostek, które zamierzasz sprzedać w tym samym czasie, co koszty stałe. Na przykład, jeśli koszty stałe obejmują miesięczny czynsz i ubezpieczenie, sprzedane jednostki będą liczbą jednostek sprzedanych w tym samym przedziale czasowym.
Krok 5. Przeczytaj dane wyjściowe „Jednostki”
Komórka wyjściowa Jednostki (C11) wyświetli liczbę jednostek, które musisz sprzedać w swoim przedziale czasowym, aby wyjść na zero. Ta liczba będzie się zmieniać w zależności od ceny jednostkowej oraz tabel kosztów zmiennych i kosztów stałych.
Krok 6. Dokonaj korekty ceny i kosztów
Zmiana ceny za jednostkę zmieni liczbę jednostek potrzebnych do osiągnięcia progu rentowności. Spróbuj zmienić cenę i zobacz, co stanie się z Twoją wartością BEP.
