Próg rentowności sklepu odzieżowego – analiza wrażliwości na zmiany cen i kosztu jednostkowego

Próg rentowności sklepu odzieżowego – analiza wrażliwości na zmiany cen i kosztu jednostkowego

Przedsiębiorca chciałby wiedzieć ile wynosi próg rentowności w jego nowo powstałym sklepie odzieżowym. Innymi słowy ile musi sprzedać produktów miesięcznie aby opłacić wszystkie koszty w sklepie. Dodatkowo chciałby wiedzieć jak będzie kształtował się próg rentowności w zależności od zmiany cen bądź kosztu zmiennego firmy. Tabela poniżej przedstawia wykaz kategorii produktów wraz ze średnimi cenami i kosztami.

01

Kolumna B średni koszt jednostkowy produktów z danej kategorii, kolumna C to średnia cena sprzedaży. w kolumnie D wpisano ilości sprzedaży. kolumna E to przychód sklepu odzieżowego, czyli cena sprzedaży pomnożona przez ilość. Dla przykładu w komórce E2 wstawiono =C2*D2. W kolumnie G przedstawiono koszt zmienny sklepu uzależniony od ilości sprzedanych produktów i ich kosztu jednostkowego. Dla przykładu w komórce G2 wstawiono =B2*D2. W komórkach D17, E17 i G17 obliczono sumę dla ilości, przychodu i kosztu zmiennego dla całego sklepu. W komórce F17 wyliczono średnią cenę sprzedaży dzieląc przychód sklepu przez ilość sprzedanych produktów (=E17/D17). W komórce H17 obliczono średni koszt jednostkowy sklepu dzieląc koszt zmienny przez ilość sprzedanych produktów (=G17/D17).

Próg rentowności

Próg rentowności (ang. BEP – break even point) obrazuje sytuacje, w której przychody ze sprzedaży równoważą koszty stałe i zmienne firmy. Innymi słowy, próg rentowności odpowiada na pytanie ile należy sprzedać produktów w sztukach, aby „wyjść na zero”. Wzór na próg rentowności to:

próg rentowności
próg rentowności

Podstawiając do wzoru wyliczone koszty jednostkowe, cenę jak i koszt stały (w tym przypadku 15 tys zł) wyliczono, iż próg rentowności osiągnie się przy sprzedaży 470 szt odzieży za średnią cenę 107,68 zł. Wyliczenia te obrazuje tabela poniżej.

03

W komórce H22 wstawiono =D21/(D23-D22).

Zmiana ceny a opłacalność

Oprócz progu rentowności przedsiębiorca chciałby także wiedzieć jak będzie kształtował się ten wskaźnik w zależności od zmiany kosztu jednostkowego i średniej ceny sprzedaży. Właściciel chce zbadać te zmiany dla średniej ceny sprzedaży odzieży od 80 zł do 130 zł z przyrostem 10 zł, jak i dla kosztu jednostkowego od 65 zł do 100 zł z przyrostem 10 zł. Aby obliczyć zmiany progu rentowności wg tych wskazówek należy posłużyć się tabelą z dwoma zmiennymi. W pierwszej kolejności należy umieścić dane w Excelu według poniższego schematu.

04

W komórce K5 umieszczono równanie wg którego Excel ma dokonać obliczeń. W tym przypadku będzie to równanie progu rentowności z komórki H22. Wystarczy w komórce K5 umieścić =H22. W komórkach L5:S5 wpisano zmiany kosztu jednostkowego dla którego Excel ma obliczyć próg rentowności. W komórkach K6:K16 wpisano zmiany średniej ceny sprzedaży. Następnie zaznaczamy cały zakres tabeli K5:S16. Po zaznaczeniu wybieramy z menu Dane w Excelu polecenie „analiza warunkowa”, gdzie klikamy na „tabela danych”.

05

Po kliknięciu wyskoczy okno dialogowe jak na rysunku poniżej.

06

Jako wierszową komórkę wejściową podajemy komórkę, do której chcemy wstawić nowe wartości kosztu zmiennego z komórek L5:S5. W naszym przypadku będzie to komórka D22.

Jako kolumnową komórkę wejściową podajemy komórkę, do której chcemy wstawić zmiany cen z zakresu K6:K16. W tym przypadku będzie to komórka D23. Po kliknięciu przycisku OK Excel tworzy tabelę danych z dwoma zmiennymi tak jak przedstawiono poniżej

07

W tabeli Excel policzył, ile sztuk odzieży przy danej średniej cenie sprzedaży i koszcie jednostkowym należy sprzedać aby osiągnąć próg rentowności. Dla przykładu, przy cenie 110 zł i koszcie jednostkowym 85 zł należy sprzedać 600 szt. odzieży (komórka P12). W komórkach O6, P7, Q8, R9 i S10 Excel wyświetlił informację iż nie można dzielić przez 0. Wynika to z tego iż dla tych komórek koszt jednostkowy i średnia cena sprzedaży są takie same. W komórkach znajdujących się blisko górnego prawego rogu tabeli są wartości ujemne odpowiadające wyższemu kosztowi jednostkowemu niż średniej cenie sprzedaży co oznacza iż w tych warunkach firma nie osiągnie progu rentowności.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *