Excel

Określenie punktu przerwania w programie Microsoft Excel

Jednym z podstawowych ekonomicznych i finansowych obliczeń działalności każdego przedsiębiorstwa jest ustalenie jego progu rentowności. Wskaźnik ten wskazuje, w jakim stopniu działalność organizacji będzie opłacalna i nie poniesie strat. Excel zapewnia użytkownikom narzędzia, które znacznie ułatwiają definicję tego wskaźnika i wyświetlają wyniki w formie graficznej. Dowiedzmy się, jak z nich korzystać, gdy znajdujemy punkt zwrotny na konkretnym przykładzie.

Punkt progu rentowności

Podstawą progu rentowności jest ustalenie wartości wielkości produkcji, w której kwota zysku (straty) będzie równa zeru. Oznacza to, że wraz ze wzrostem produkcji, przedsiębiorstwo zacznie wykazywać opłacalność działalności, a przy tym będzie ją zmniejszać.

Podczas obliczania progu rentowności należy zrozumieć, że wszystkie koszty przedsiębiorstwa można warunkowo podzielić na stałe i zmienne. Pierwsza grupa nie zależy od wielkości produkcji i jest niezmienna. Może to obejmować wysokość wynagrodzeń dla personelu administracyjnego, koszt wynajmu lokalu, amortyzację środków trwałych itp. Ale koszty zmiennych zależą bezpośrednio od wielkości produkcji. Należy to przede wszystkim przypisać kosztowi zakupu surowców i nośników energii, więc ten rodzaj kosztów jest zwykle wskazywany na jednostkę produkcji.

Jest to stosunek stałych i zmiennych kosztów związanych z koncepcją progu rentowności. Przed osiągnięciem pewnej wielkości produkcji koszty stałe stanowią znaczną kwotę w całkowitych kosztach produkcji, ale wraz ze wzrostem ich objętości ich udział spada, a tym samym spada koszt jednostkowy produkowanych towarów. W progu opłacalności koszty produkcji i dochody ze sprzedaży towarów lub usług są równe. Wraz z dalszym wzrostem wielkości produkcji przedsiębiorstwo zaczyna osiągać zyski. Dlatego tak ważne jest określenie wielkości produkcji, w których osiągnięto próg rentowności.

Obliczanie punktu przerwania

Wskaźnik ten obliczamy za pomocą narzędzi programu Excel, a także rysujemy wykres, na którym odnotowujemy punkt progu rentowności. Do obliczeń użyjemy tabeli, w której podane są takie początkowe dane dotyczące działalności firmy:

  • Stałe koszty;
  • Zmienne koszty na jednostkę produkcji;
  • Cena sprzedaży jednostki produkcyjnej.

Obliczmy zatem dane na podstawie wartości wskazanych w tabeli na obrazku poniżej.

Tabela wydajności przedsiębiorstwa w programie Microsoft Excel

  1. Budujemy nową tabelę na podstawie tabeli źródłowej. Pierwsza kolumna nowej tabeli to ilość towarów (lub partii) wyprodukowanych przez przedsiębiorstwo. Oznacza to, że numer linii będzie oznaczać liczbę wyprodukowanych towarów. Druga kolumna zawiera wartość kosztów stałych. Będzie równa 25000 we wszystkich linijkach. W trzeciej kolumnie - całkowita kwota kosztów zmiennych. Ta wartość dla każdego wiersza będzie równa iloczynowi ilości towarów, czyli zawartości odpowiedniej komórki pierwszej kolumny, o 2000 rubli .

    W czwartej kolumnie znajduje się całkowity koszt. Jest to suma komórek odpowiedniego rzędu drugiej i trzeciej kolumny. Piąta kolumna zawiera całkowity przychód. Oblicza się ją, mnożąc cenę jednostkową ( 4500 rubli ) przez całkowitą liczbę towarów, która jest wskazana w odpowiednim wierszu pierwszej kolumny. Szósta kolumna pokazuje zysk netto. Jest on obliczany poprzez odjęcie od całkowitego przychodu ( kolumna 5 ) kwoty kosztów ( kolumna 4 ).

    Oznacza to, że w tych wierszach, które mają wartość ujemną w odpowiednich komórkach ostatniej kolumny, obserwuje się stratę przedsiębiorstwa, w tych, w których wskaźnik będzie wynosił 0 - osiągnięty zostanie punkt progu rentowności, a w tych, w których będzie on dodatni - zysk w działaniach organizacji jest zaznaczony.

    Dla jasności wypełnij 16 linii. W pierwszej kolumnie będzie liczba towarów (lub partii) od 1 do 16 . Kolejne kolumny są wypełniane zgodnie z algorytmem wspomnianym powyżej.

    2. Tablica obliczeń punktu granicznego w programie Microsoft Excel

  2. Jak widać, punkt przejścia na 10 produktów. Wtedy łączne przychody (45 000 rubli) są równe kosztom całkowitym, a zysk netto wynosi 0 . Już od premiery jedenastego towaru przedsiębiorstwo wykazuje opłacalną działalność. Tak więc, w naszym przypadku, punktem zwrotnym w wskaźniku ilościowym jest 10 jednostek, a w monetarnej - 45 000 rubli .

Breakeven punkt w przedsiębiorstwie w Microsoft Excel

Tworzenie wykresu

Po utworzeniu tabeli, w której obliczany jest punkt progu rentowności, można utworzyć wykres, w którym ten wzór będzie wyświetlany wizualnie. Aby to zrobić, będziemy musieli zbudować diagram z dwiema liniami, które odzwierciedlają koszty i przychody przedsiębiorstwa. Na przecięciu tych dwóch linii i będzie punkt progu rentowności. Na osi X tego wykresu zostanie zlokalizowana ilość towarów, a wzdłuż osi Y kwoty pieniężne.

  1. Przejdź do zakładki "Wstaw" . Kliknij ikonę "Spot" , która jest umieszczona na wstążce w oknie narzędziowym "Diagramy" . Prezentowany jest wybór kilku rodzajów wykresów. Aby rozwiązać nasz problem, odpowiedni jest typ "Spot with smooth curve and markers" , więc kliknij ten element na liście. Chociaż, jeśli chcesz, możesz użyć innych rodzajów diagramów.

    2. Wybierz typ wykresu w programie Microsoft Excel

  2. Przed nami otwiera pusty obszar diagramu. Konieczne jest wypełnienie go danymi. Aby to zrobić, kliknij prawym przyciskiem myszy obszar. W aktywowanym menu wybierz pozycję "Wybierz dane ..." .

    3. Przejdź do wyboru danych w programie Microsoft Excel

  3. Otworzy się okno wyboru źródła danych. W jego lewej części znajduje się blok "Elementy legendy (wiersze)" . Kliknij przycisk "Dodaj" , który znajduje się w określonym bloku.

    4. Okno wyboru źródła wierszy w programie Microsoft Excel

  4. Przed nami otwiera się okno o nazwie "Zmiana serii" . Musimy w nim określić współrzędne rozmieszczenia danych, na podstawie których zostanie zbudowany jeden z wykresów. Na początek skonstruujemy wykres, w którym wyświetlane są całkowite koszty. Dlatego w polu "Nazwa wiersza" wprowadź z klawiatury wpis "Koszty całkowite" .

    W polu "Wartości X" wskazujemy współrzędne danych znajdujących się w kolumnie "Ilość towarów" . Aby to zrobić, ustawiamy kursor w tym polu, a następnie po przycięciu lewego przycisku myszy wybierz odpowiednią kolumnę tabeli na arkuszu. Jak widać, po określonych akcjach jego współrzędne będą wyświetlane w oknie zmiany serii.

    W kolejnym polu "Wartości Y" należy wyświetlić adres kolumny "Całkowity koszt" , w której wyświetlane są potrzebne dane. Postępujemy zgodnie z powyższym algorytmem: umieszczamy kursor w polu i wybieramy komórki kolumny, której potrzebujemy, z wciśniętym lewym przyciskiem myszy. Dane będą wyświetlane w polu.

    Po przeprowadzeniu tych manipulacji kliknij przycisk "OK" znajdujący się w dolnej części okna.

    5. Okno do zmiany serii Całkowite koszty w Microsoft Excel

  5. Następnie automatycznie powraca do okna wyboru źródła danych. Musi również kliknąć przycisk "OK" .

    6. Zamykanie okna Wybór źródła danych w programie Microsoft Excel

  6. Jak widać, po tej dacie na arkuszu zostanie wyświetlony harmonogram całkowitych kosztów firmy.

    7. Harmonogram kosztów całkowitych w programie Microsoft Excel

  7. Teraz musimy zbudować linię całkowitych dochodów firmy. W tym celu klikamy prawym przyciskiem myszy obszar wykresu, na którym znajduje się już całkowita linia kosztów organizacji. W menu kontekstowym wybierz pozycję "Wybierz dane ..." .

    8. Przejdź do wyboru danych w programie Microsoft Excel

  8. Ponownie pojawi się okno wyboru źródła danych, w którym ponownie trzeba kliknąć przycisk "Dodaj" .

    9. Okno wyboru źródła wierszy w programie Microsoft Excel

  9. Pojawi się małe okno, aby zmienić wiersz. W polu "Nazwa serii" tym razem wpisujemy "Całkowity dochód" .

    W polu "Wartości X" wprowadź współrzędne kolumny "Ilość towarów" . Robimy to w taki sam sposób, jaki uwzględniliśmy przy konstruowaniu linii całkowitych kosztów.

    W polu "Wartości Y" podajemy również współrzędne kolumny "Całkowite dochody" .

    Po wykonaniu tych kroków kliknij przycisk "OK" .

    10. Okno do zmiany serii Łączne przychody w Microsoft Excel

  10. Okno wyboru źródła danych zostanie zamknięte po kliknięciu przycisku "OK" .

    11. Zamykanie okna wyboru źródła danych w programie Microsoft Excel

  11. Następnie linia całkowitego dochodu zostanie wyświetlona na płaszczyźnie arkusza. Punkt przecięcia linii łącznych dochodów i kosztów całkowitych jest punktem progu rentowności.

Punkt Breakeven na wykresie w programie Microsoft Excel

W ten sposób osiągnęliśmy cele związane z tworzeniem tego harmonogramu.

Lekcja: Jak narysować wykres w Excelu

Jak widać, znalezienie progu rentowności opiera się na określeniu wielkości produkcji, przy której całkowite koszty będą równe dochodom całkowitym. Graficznie znajduje to odzwierciedlenie w budowie linii kosztów i przychodów oraz w znalezieniu punktu ich przecięcia, który będzie punktem progu rentowności. Przeprowadzanie takich obliczeń jest podstawą do organizacji i planowania działań każdego przedsiębiorstwa.