Analiza progu rentowności to narzędzie do oceny możliwości zysku modelu biznesowego i różnych strategii cenowych. W programie Excel można znaleźć koszty stałe, koszty zmienne i opcje cenowe, aby określić próg rentowności produktu. Próg rentowności to liczba jednostek, które należy sprzedać po ustalonej cenie, aby osiągnąć próg rentowności (pokryć koszty produkcji).
Krok
Część 1 z 5: Tworzenie tabeli kosztów zmiennych
Krok 1. Otwórz Excel i utwórz skoroszyt
Będziesz potrzebował kilku arkuszy roboczych w skoroszycie, aby śledzić wszystkie swoje koszty.
Krok 2. Kliknij przycisk „+” obok „Arkusz1” u dołu ekranu
Otworzy to nowy dokument roboczy.
Krok 3. Nazwij nowy arkusz roboczy „Koszt zmienny”
Ten arkusz będzie zawierał wszystkie tabele monitorujące wszystkie koszty zmienne, takie jak koszty wysyłki, prowizje i tak dalej.
Krok 4. Utwórz etykietę tytułową dla nowego arkusza roboczego
Aby utworzyć standardową tabelę kosztów zmiennych, wpisz „Opis” w komórce A1 i „Kwota” w komórce B1.
Krok 5. Wprowadź nazwę kosztu zmiennego firmy w kolumnie A
Pod nagłówkiem „Opis” wpisz rodzaj kosztów zmiennych produktu do obsługi.
Krok 6. Na razie pozostaw kolumnę B („Kwota”) pustą
Ta kolumna zostanie później wypełniona kosztami, które faktycznie wystąpiły podczas procesu.
Krok 7. Utwórz tabelę z wprowadzonych danych
Przekształć dane w tabele, aby łatwo wprowadzać je do formuł:
- Wybierz wszystkie dane, w tym wiersz tytułu i puste komórki, klikając i przeciągając kursor myszy przez wszystkie komórki.
- Kliknij przycisk „Formatuj jako tabelę”. Znajdziesz go pod etykietą Home. Jeśli używasz programu Excel dla komputerów Mac, kliknij etykietę Tabele, kliknij przycisk „ Nowy” i wybierz opcję „ Wstaw tabelę z nagłówkami”.
- Zaznacz pole " Moja tabela ma nagłówki ". W ten sposób etykieta pierwszego wiersza zostanie ustawiona jako etykieta tytułu.
- Kliknij pole „Nazwa tabeli” w prawym górnym rogu i zmień nazwę na „Koszt zmienny”
Część 2 z 5: Tworzenie tabeli kosztów stałych
Krok 1. Kliknij przycisk „+” obok „Koszt zmienny” u dołu ekranu
Otworzy to nowy dokument roboczy.
Krok 2. Zmień nazwę nowego arkusza na „Koszty stałe”
Ten arkusz będzie zawierał wszystkie koszty stałe produktu, takie jak czynsz, ubezpieczenie i inne koszty, które się nie zmieniają.
Krok 3. Utwórz etykietę tytułową
Podobnie jak w przypadku arkusza kosztów zmiennych, 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
W pierwszej kolumnie wypełnij opis kosztów stałych, np. „Wynajem”.
Krok 5. Na razie pozostaw kolumnę B („Kwota”) pustą
Wypełnisz te koszty po wypełnieniu wszystkich formalności.
Krok 6. Utwórz tabelę z wprowadzonych danych
Wybierz wszystko, co utworzyłeś w tym arkuszu, w tym tytuł:
- Kliknij przycisk „Formatuj jako tabelę” na etykiecie Strona główna.
- Zaznacz „Moja tabela ma nagłówki”, aby zmienić wiersz 1 na tytuł tabeli.
- Kliknij pole „Nazwa tabeli” i wprowadź nazwę tabeli „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ć nazwy na „BEP”, ale to ułatwi Ci pracę, jeśli to zrobisz.
Krok 2. Ustaw układ arkusza progu rentowności
Na potrzeby tego przykładu utwórz arkusz, korzystając z następującego układu:
- A1: Sprzedaż - jest to etykieta sekcji Sprzedaż w arkuszu.
- B2: Cena za jednostkę - jest to cena pobierana za każdy sprzedany przedmiot.
- B3: Jednostki sprzedane – jest to liczba sprzedanych jednostek po ustalonej cenie w danym przedziale czasowym.
- A4: Koszty - jest to etykieta sekcji Koszty w arkuszu.
- B5: Koszty zmienne – są to koszty produktu, które możesz kontrolować (wysyłka, stawka prowizji itp.)
- B6: Koszty stałe – są to koszty produktu, nad którymi nie masz kontroli (wynajem obiektu, ubezpieczenie itp.)
- O7: Przychody – Jest to kwota pieniędzy wygenerowana ze sprzedaży produktu przed odliczeniem kosztów.
- B8: Marża jednostkowa – Jest to kwota pieniędzy zarobiona na jednostkę po odliczeniu kosztów.
- B9: Marża zysku brutto – Jest to całkowita kwota pieniędzy wygenerowana ze sprzedaży jednostek produktu po odliczeniu kosztów.
- A10: BEP – To jest etykieta dla sekcji arkusza progu rentowności.
- B11: Jednostki – Jest to liczba jednostek, które należy sprzedać, aby pokryć koszty produkcji.
Krok 3. Zmień format liczbowy dla komórek wyjściowych i wejściowych
Musisz zmienić format liczbowy dla określonych komórek, aby dane wyświetlały się poprawnie:
- Podświetl C2, C5, C6, C8 i C9. Kliknij menu rozwijane w sekcji „Liczba” na etykiecie głównej i wybierz „Waluta”.
- Podświetl C3 i C11. Kliknij menu rozwijane i wybierz „Więcej formatów liczb”. Wybierz „Numer” i ustaw „Miejsca dziesiętne” na „0”.
Krok 4. Utwórz zakres do użycia w formułach
Wybierz i utwórz następujące zakresy, aby formuła działała. W ten sposób można tworzyć zmienne, które mają być wprowadzane do formuł, dzięki czemu można łatwo śledzić i aktualizować ich wartości.
- Wybierz B2:C3, a następnie kliknij etykietę „Formuły”. Kliknij „Utwórz z wyboru” i kontynuuj „OK”.
- Wybierz B5:C6, a następnie kliknij etykietę „Formuły”. Kliknij „Utwórz z wyboru” i kontynuuj „OK”.
- Wybierz B8:C9, a następnie kliknij etykietę „Formuły”. Kliknij „Utwórz z wyboru” i kontynuuj „OK”.
- Wybierz B11:C11, a następnie kliknij etykietę „Formuły”. Kliknij „Utwórz z wyboru” i kontynuuj przyciskiem „OK”.
Część 4 z 5: Wprowadzanie wzorów
Krok 1. Wprowadź formułę kosztów zmiennych
Ta formuła obliczy całkowity koszt zmienny dla sprzedanych towarów. Kliknij C5 i wprowadź następującą formułę:
=SUMA(Koszt zmienny)*Jednostki_Sprzedane
Krok 2. Wstaw koszty stałe do formuły
Excel obliczy całkowity koszt stały Twojego produktu. Kliknij C6 i wprowadź następującą formułę:
=SUMA(Koszt stały)
Krok 3. Wprowadź formułę marży jednostkowej
Ta formuła obliczy wynikową marżę po uwzględnieniu kosztów zmiennych. Kliknij C8 i wprowadź następującą formułę:
=Cena_za_jednostkę-SUM(Zmienny koszt)
Krok 4. Wprowadź formułę marży zysku brutto
Ta formuła określa łączną kwotę zysku dla wszystkich jednostek po odjęciu kosztów zmiennych. Kliknij C9 i wprowadź następującą formułę:
=Jednostka_Margin*Jednostka_Sprzedana
Krok 5. Wprowadź formułę BEP
Ta formuła uwzględnia koszty stałe i porównuje je z marżą, dzięki czemu wiesz, ile jednostek musisz sprzedać, aby wyjść na zero. Kliknij C11 i wprowadź następującą formułę:
=JEŻELI. BŁĄD(Stały_koszt/Marża_jednostkowa; 0)
Część 5 z 5: Określanie progu rentowności
Krok 1. Wprowadź koszty zmienne swojej firmy
Wróć do tabeli Koszty Zmienne i wypełnij wszystkie koszty związane z produktem. Im dokładniejsze są zarejestrowane koszty, tym dokładniejsze będą obliczenia.
Każdy koszt w tabeli Koszt zmienny musi 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, wszystkie ustawione w równych odstępach czasu (np. opłaty miesięczne).
Krok 3. Wprowadź cenę za sztukę
W arkuszu BEP wprowadź szacunkową cenę jednostkową. Będziesz mógł go dostosować w miarę postępu obliczeń.
Krok 4. Wprowadź liczbę jednostek, które chcesz sprzedać
Jest to pożądana liczba sprzedaży w tym samym przedziale czasowym, co koszty stałe. Na przykład, jeśli koszty stałe obejmują miesięczny czynsz i ubezpieczenie, sprzedane jednostki to liczba jednostek sprzedanych w tym samym okresie.
Krok 5. Przeczytaj dane wyjściowe „Jednostka”
Komórka wyjściowa Jednostki (C11) wyświetli liczbę jednostek, które będą musiały zostać sprzedane, aby wyjść na zero. Liczba ta zmieni się w zależności od ceny jednostkowej oraz tabel Kosztów zmiennych i Kosztów stałych.
Krok 6. Dostosuj ceny i koszty
Zmiana ceny za jednostkę spowoduje również zmianę liczby jednostek wymaganych do osiągnięcia progu rentowności. Spróbuj zmienić cenę sprzedaży urządzenia i zobacz wpływ na wartość BEP.
