Analiza break even point w Excelu
Kiedy tworzymy nowy produkt/usługę dobrze jest oszacować, kiedy zwrócą się nam pieniądze włożone w jego stworzenie, czyli dobrze jest wyznaczyć próg rentowności albo, po angielsku, break even point.
Żeby oszacować taki próg w pierwszej kolejności musimy przyjąć pewne założenia. Przede wszystkim musimy ustalić w jakiej cenie będziemy nasz produkt/usługę sprzedawać. Później jak najdokładniej musimy oszacować wszystkie koszty związane z pojedynczym produktem czy usługą (koszty zmienne), czyli m.in. podatki, koszty wytworzenia, pensje pracowników itd. Przy obliczaniu progu rentowności istotne są też początkowe koszty stworzenia produktu (koszty stałe). Mogą to być np.: projekt nowego produktu, koszt pierwszej kampanii reklamowej, koszt stworzenia strony internetowej czy linii produkcyjnej. Gdy już mamy te 3 przykładowe wartości (Rysunek 1), możemy przejść do szacowania progu rentowności.
Przy podobnych obliczeniach przydaje się wizualizacja, więc chcemy przygotować zbiór wyników, na podstawie których stworzymy wykres przedstawiający próg rentowności.
W pierwszej kolejności musimy przygotować kolumnę z liczbą sprzedanych sztuk produktu. Następnie na podstawie ceny produktu i kosztów zmiennych wytworzenia pojedynczego produktu możemy obliczyć przychód i koszty zmienne stworzenia konkretnej ilości produktów (Rysunek 2).
Na tej podstawie możemy obliczyć, ile marży nam zostanie na pokrycie kosztów stałych (Rysunek 3).
Łącząc dalej koszty zmienne i stałe uzyskujemy koszty całkowite (=C6+E6), a na ich podstawie możemy obliczyć dochód (=B6-F6). Dzięki tym wszystkim obliczeniom możemy zobaczyć, że dla naszego przykładu próg rentowności wypada mniej więcej przy trzystu kilkudziesięciu sprzedanych produktach (Rysunek 4).
Przyszła pora, żeby nasze obliczenia przedstawić na wykresie. Żeby to zrobić musimy zaznaczyć 4 kolumny – liczba sprzedanych sztuk, przychód, koszty stałe oraz koszty całkowite. Gdy już zaznaczymy te kolumny (przytrzymując Ctrl, żeby zaznaczyć niesąsiadujące zakresy) wstawiamy wykres punktowy z prostymi liniami (Rysunek 5).
Wykres potwierdza nasze wcześniejsze obserwacje progu rentowności wyciągnięte z obliczeń dochodu, ale chcemy ten punkt wyliczyć dokładniej. Żeby to zrobić wystarczy podzielić koszty stałe przez zysk ze sprzedaży pojedynczego produktu, czyli od ceny produktu trzeba odjąć koszty zmienne (Rysunek 6).
W wyniku takiego obliczenia prawie na pewno nie uzyskamy liczby całkowitej sprzedanych produktów, dlatego wynik musimy zaokrąglić w górę do pełnych liczb całkowitych =ZAOKR.GÓRA(C3/(A3-B3);0) = 334. Możemy nawet dokonać zaokrąglenia do pełnych dziesiątek wstawiając -1 w drugi argument funkcji ZAOKR.GÓRA -> =ZAOKR.GÓRA(C3/(A3-B3);-1) = 340, bo często nie uwzględniamy wszystkich kosztów.
Obliczona przez nas wartość jest x na wykresie. Potrzebujemy jeszcze wyliczyć y. To po prostu przemnożenie liczby sprzedanych produktów przez cenę produktu (Rysunek 7).
Teraz możemy ten punkt dodać do wykresu. Żeby to zrobić najpierw zaznaczamy wykres, a następnie na karcie Projekt wykresu klikamy w polecenie Zaznacz dane. Kolejnym krokiem jest kliknięcie w przycisk Dodaj w oknie Wybierania źródła danych, które się otworzy. Spowoduje to otworzenie okna Edytowania serii, gdzie musimy podać nazwę serii (możemy ją wpisać jako tekst, albo jako odwołanie do komórki), wartości x i y (u nas są to pojedyncze komórki – Rysunek 8).
Po zatwierdzeniu dodania nowej serii wykres się nie zmieni, ponieważ dodaliśmy zaledwie pojedynczy punkt do wykresu punktowego z prostymi liniami. Dlatego potrzebujemy wyróżnić ten punkt na wykresie. Zaznaczenie nowej serii przez kliknięcie w nią jest bardzo trudne, bo to tylko pojedynczy punkt. Na szczęście można do niej przejść, zaznaczając inną serię, a następnie przytrzymując Ctrl lub Alt i naciskając strzałkę w górę.
Inną drogą zaznaczenia punktu rentowności na wykresie (nowej serii danych), jest wybranie jej z listy rozwijanej znajdującej się po prawej stronie karty Formatowanie, gdy zaznaczony jest wykres (Rysunek 9).
Gdy zaznaczyliśmy już punkt naciskamy Ctrl + 1, a następnie odszukujemy w oknie Formatowania serii danych, które się otworzy, zakładki z opcjami znaczników i zmieniamy go odpowiednio (Rysunek 10).
Teraz próg rentowności powinien być widoczny na wykresie, ale brakuje mu podpisu, dlatego, gdy jest już zaznaczony, klikamy w zielony plus i zaznaczamy Etykiety danych. Domyślnie powinna się nad punktem pojawić wartość y dla tego punktu (czyli przychód), ale nam zależy na dłuższym opisie liczby sprzedanych produktów. Musimy ten opis stworzyć w komórce i możemy użyć do tego przykładowej formuły: =”BEP sztuk = „&B20 (Rysunek 11).
Żeby wartość z komórki przypisać do konkretnej etykiety danych, musimy ją zaznaczyć (jej obramowanie musi być linią ciągłą, a nie przerywaną), a następnie kliknąć klawisz =, następnie komórkę przechowującą opis (Rysunek 11), a na koniec zatwierdzić formułę Enterem.
Warto jeszcze dopracować odrobinę wykres, np. usunąć z legendy serię związaną z progiem rentowności (zaznaczyć tę serię w legendzie i nacisnąć klawisz Delete), dopracować osie x i y, poprawić tytuł oraz dodać opis osi x (Rysunek 12).
Gdy już przygotowaliśmy wszystkie obliczenia i stworzyliśmy wykres, możemy zmienić początkowe założenia (Rysunek 1) oraz ewentualnie ciąg liczby sztuk (Rysunek 2) i uzyskać progi rentowności dla innych produktów/usług.
