Jak zrobić arkusz Breakeven w programie Excel

Zawartość

Kroki
Porady
Ostrzeżenia

Analiza rentowności to narzędzie finansowe, które poprzez ocenę różnych strategii cenowych określa potencjał zysku modelu biznesowego. W połączeniu z Excelem bardzo łatwo jest skompilować koszty stałe i zmienne, oprócz szacowania cen, aby osiągnąć punkt równowagi danego produktu. Głównym celem tej analizy jest wyliczenie ceny, jaką należy przyjąć, aby dopasować wydatki do przychodów firmy.

Kroki

Część 1 z 5: Tworzenie tabeli wydatków zmiennych

Otwórz program Excel i utwórz pusty skoroszyt. Później zostanie utworzonych więcej arkuszy danych finansowych, aby umożliwić analizę.
Naciśnij przycisk „+” obok karty „Arkusz 1” u dołu ekranu. W rezultacie nowy arkusz kalkulacyjny pojawi się w tym samym skoroszycie.
Zmień nazwę arkusza kalkulacyjnego na „Zmienne wydatki”. Będzie zawierał wszystkie dane związane z kosztami zmiennymi, takie jak m.in .: dostawa, prowizja.
Utwórz nagłówki. Prosta tabela wydatków zmiennych musi zawierać: „Opis” w komórce A1 i „Kwota” w B1.
Wpisz nazwy wszystkich wydatków zmiennych w kolumnie A. Muszą być wymienione pod nagłówkiem „Opis”. Postaraj się zapamiętać wszystkie rodzaje zmiennych wydatków związanych z ocenianym produktem.
Na razie pozostaw kolumnę B („Wartość”) pustą. Kwoty wydatków zostaną uzupełnione później.
Sformatuj jako tabelę programu Excel. Przekształcenie danych w tabelę ułatwi późniejsze wprowadzanie formuł:
- Wybierz wszystkie dane, w tym nagłówki i komórki pustych wartości. Aby to zrobić, kliknij środek komórki A1 i przeciągnij do ostatniej wypełnionej nazwy wydatku, a następnie przeciągnij w prawo, obejmując kolumnę wartości do wybrania.
- Kliknij przycisk „Formatuj jako tabelę” na karcie „Strona główna”. Jeśli korzystasz z programu Excel dla systemu Mac OS, wybierz kartę „Tabele”, naciśnij przycisk „Nowy” i wybierz opcję „Wstaw tabelę z nagłówkami”.
- Kliknij pole wyboru „Moja tabela ma nagłówki”. W związku z tym będą postrzegane jako etykiety danych.
- Kliknij pole „Nazwa tabeli” w lewym górnym rogu i wpisz: „Wydatki zmienne”.

Część 2 z 5: Tworzenie arkusza kalkulacyjnego stałych wydatków

Kliknij przycisk „+” obok zakładki „Wydatki zmienne” u dołu ekranu. W ten sposób skoroszyt zyska nowy arkusz kalkulacyjny.
Zmień nazwę nowego arkusza kalkulacyjnego na „Stałe wydatki”. Podobnie jak w poprzednim arkuszu kalkulacyjnym posłuży to do wyszczególnienia wszystkich danych o wydatkach, w tym przypadku stałych, związanych z produktem, takich jak: czynsz, ubezpieczenie i inne rodzaje kosztów, które się nie zmieniają.
Utwórz nagłówki. Będą takie same, jak w arkuszu zmiennych wydatków, a zatem: „Opis” w komórce A1; i „Valor” w B1.
Wymień wszystkie nazwy stałych wydatków służbowych w kolumnie A. Spróbuj zapamiętać wszystkie stałe wydatki (np. Czynsz) i wpisz je pod nagłówkiem „Opis”.
Na razie pozostaw kolumnę wartości (B) pustą. Wartości zostaną wypełnione po utworzeniu głównego arkusza kalkulacyjnego.
Przekształć dane w tabelę programu Excel. Wybierz dwie kolumny, w tym tytuły, i:
- Kliknij przycisk „Formatuj jako tabelę” w zakładce „Strona główna”;
- Zaznacz pole wyboru „Moja tabela ma nagłówki”, aby przekształcić pierwszy wiersz w etykiety danych;
- Wybierz pole „Nazwa tabeli” i nazwij je „Stałe wydatki”.

Część 3 z 5: Tworzenie arkusza Breakeven

Wróć do arkusza roboczego 1 i zmień jego nazwę na PDE (rentowność). Będzie zawierać główną tabelę progów rentowności. Nazywanie go PDE nie jest konieczne, ale ta wskazówka ułatwia poruszanie się po skoroszycie.
Zaprojektuj opłacalny układ arkusza kalkulacyjnego. Jako sugestię utwórz arkusz kalkulacyjny z następującymi nagłówkami:
- A1: Sprzedaż - sekcja sprzedaży;
- B2: Cena jednostkowa - cena końcowa dla konsumenta;
- B3: Sprzedane sztuki - całkowita liczba sprzedaży w danym okresie;
- A4: Wydatki - sekcja wydatków;
- B5: Koszty zmienne - wydatki, które nie są kontrolowane (dostawa, prowizje, podatki itp.);
- B6: Koszty stałe - wydatki kontrolowane (wynajem punktu handlowego, ubezpieczenie itp.);
- A7: Przychody - całkowity zysk ze sprzedaży (bez kosztów dyskontowania);
- B8: Zysk jednostkowy - zysk na jednostkę po zdyskontowaniu kosztów;
- B9: Total Profit - całkowity zysk po zdyskontowaniu kosztów;
- A10: PDE - sekcja progu rentowności;
- B11: Jednostki - liczba jednostek, które należy sprzedać, aby uzyskać równe wydatki biznesowe.
Zmień formaty liczb dla komórek wejściowych i wyjściowych. Będzie to konieczne w przypadku niektórych komórek, aby wartości były wyświetlane poprawnie.
- Wybierz komórki C2, C5, C6, C8 i C9. Na karcie „Strona główna” odszukaj pasek narzędzi „Numer” i po kliknięciu selektora wybierz opcję „Waluta”.
- Wybierz komórki C3 i C11. Kliknij ponownie selektor i wybierz ostatnią opcję „Więcej formatów liczb”. Zmień liczbę miejsc dziesiętnych na 0.
Utwórz nazwy argumentów, które będą używane w formułach. Wybierz poniższe komórki, aby służyły jako argumenty używane w formułach, co pozwoli na łatwe odwoływanie się i zmianę wartości.
- Wybierz B2: C3 (od B2 do C3) i otwórz zakładkę „Formuły”. Kliknij przycisk „Utwórz z wyboru”, a następnie potwierdź.
- Wybierz B5: C6 i otwórz zakładkę „Formuły”. Kliknij przycisk „Utwórz z wyboru”, a następnie potwierdź.
- Wybierz B8: C9 i otwórz zakładkę „Formuły”. Kliknij przycisk „Utwórz z wyboru”, a następnie potwierdź.
- Wybierz B11: C11 i otwórz zakładkę „Formuły”. Kliknij przycisk „Utwórz z wyboru”, a następnie potwierdź.

Część 4 z 5: Dodawanie formuł

Zacznij od wzoru na koszt zmienny. Obliczy całkowite zmienne wydatki na podstawie liczby sprzedanych przedmiotów. Kliknij komórkę C5 i wprowadź formułę:
- = SUMA (wydatki zmienne) * Jednostki_Sprzedane
Wprowadź wzór na wydatki stałe. Obliczy całkowity stały koszt produktów. Kliknij komórkę C6 i wprowadź formułę:
- = SUMA (wydatki stałe)
Wprowadź wzór na zarobki jednostkowe. Dzięki temu będzie można poznać zyski uzyskane po zdyskontowaniu kosztów zmiennych. Kliknij komórkę C8 i wprowadź formułę:
- = SUMA_Cena_Sprzedaż (wydatki zmienne)
Wprowadź wzór do obliczenia całkowitego zysku. Określa zysk od całkowitego zysku ze sprzedanych przedmiotów, pomniejszonego o wydatki. Kliknij komórkę C9 i wprowadź formułę:
- = Unit_profit * Jednostki_Sold
Zakończ formułą, aby obliczyć próg opłacalności. Porówna stałe wydatki ze sprzedanymi jednostkami, aby obliczyć, ile jednostek trzeba sprzedać, aby osiągnąć rentowność. Kliknij C11 i wprowadź wzór:
- = SECRET (Fixed_Expenses / Unit_profit, 0)

Część 5 z 5: Obliczanie progu rentowności

Wprowadź zmienne wartości wydatków dla firmy. Wróć do arkusza kalkulacyjnego wydatków zmiennych i wprowadź w kolumnie B wszystkie wartości związane z opisem dokonanym w kolumnie A. Im dokładniejsze wartości, tym wyższy poziom ufności obliczenia progu rentowności.
- Koszty należy zgłaszać proporcjonalnie do jednej sprzedanej jednostki produktu.
Dodaj stałe kwoty wydatków. Wróć do tabeli wydatków stałych i zgłoś wszystkie koszty związane z utrzymaniem biznesu. Wszystkie muszą znajdować się w tej samej jednostce czasu, na przykład: co tydzień, co miesiąc, co rok itp.
Wpisz cenę jednostkową w arkuszu kalkulacyjnym PDE. Dokonaj wstępnej wyceny i zmień ją później.
Wpisz liczbę jednostek, które chcesz sprzedać. Pamiętaj, że należy określić okres równy wydatkom stałym. Na przykład, jeśli czynsz za punkt handlowy jest opłacany co miesiąc, wprowadź liczbę lokali, które zamierzasz sprzedać w ciągu miesiąca.
Zobacz wynik jednostek. Komórka C11 pokaże liczbę jednostek, które trzeba będzie sprzedać po szacowanej cenie, aby osiągnąć rentowność. Liczba ta będzie się różnić w zależności od wydatków i szacowanej ceny jednostkowej.
Dostosuj cenę i wydatki. Zmień cenę za jednostkę produktu, a liczba jednostek potrzebnych do osiągnięcia progu rentowności również się zmieni. Zrób kilka testów i przeanalizuj wyniki.