Analiza wrażliwości zysku punktu gastronomicznego na zmiany cen i kosztu jednostkowego

Analiza wrażliwości zysku punktu gastronomicznego na zmiany cen i kosztu jednostkowego

Przedsiębiorca zastanawia się nad otwarciem punktu sprzedaży jogurtów mrożonych w galerii handlowej. Pomysł na prowadzenie takiego punktu jak i produkty można zakupić w formie franczyzy. Zanim przedsiębiorca zdecyduje się na wykup franczyzy, chciałby wiedzieć jak jego zysk, przychód i koszty zmienne będą zależeć od ceny, którą ustali, oraz jednostkowego kosztu zmiennego. Innymi słowy jak zmieni się zysk, przychód i koszty gdy dla przykładu cena spadnie o 0,50 zł a koszt zmienny wzrośnie o 0,10 zł.

Dane do analizy

Tabela poniżej zawiera podstawowe dane jakie należy użyć do analizy.

01 ceny i koszty

W komórce D1 jest podana średnia cena sprzedaży, która wynosi 4 zł. Komórka D2 to szacowany popyt na jogurty mrożone. Dane takie można otrzymać od franczyzodawcy. Popyt zależy od ceny i wynosi 65 tys – 9 tys * cena = 29.000. Koszt jednostkowy został oszacowany na poziomie 0,45 zł a koszty stałe 45 tys zł. Przychód firmy będzie zależeć od popytu i średniej ceny sprzedaży jogurtu i wynosi 116 tys zł. Koszt zmienny zależy od kosztu jednostkowego pomnożonego przez popyt. Zysk to przychód pomniejszony o koszt stały i zmienny.

Wpływ ceny na zysk

Przedsiębiorca chciałby wiedzieć, jak zmiana ceny sprzedaży wpływa na roczny zysk, przychód i koszt zmienny. Właściciel chce zbadać te zmiany dla ceny sprzedaży jogurtu od 1 zł do 4 zł z przyrostem 0,25 zł. Ponieważ zmieniamy tylko jedną daną wejściową tj. cenę sprzedaży, do rozwiązania tego problemu wystarczy tabela z jedną zmienną.

Aby utworzyć tabelę danych z jedną zmienną, należy zacząć od umieszczenia w jednej kolumnie wszystkich danych wejściowych. Zaczniemy od wpisania wszystkich interesujących przedsiębiorcę cen w komórkach C11:C23, tak jak w tabeli poniżej.

02 tabela do analizy wrażliwości

W komórce D10 wpisujemy formułę obliczającą zysk. Ponieważ wcześniej już to zrobiono to wystarczy wpisać =D7. Tak samo należy zrobić dla przychodu – w komórce E10 wpisać =D5. Także postępujemy dla kosztu zmiennego z komórki F10, gdzie wpisujemy =D6. Następnie zaznaczamy cały zakres tabeli C10:F23. Po zaznaczeniu wybieramy z menu Dane w excelu polecenie „analiza warunkowa”, gdzie klikamy na „tabela danych”.

03 excel tabela danych

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

04 tabela danych

Jako kolumnową komórkę wejściową podajemy komórkę, do której chcemy wstawić wartości z listy danych wejściowych – czyli z zakresu C11:C23. Ponieważ wymienione na liście dane wejściowe to ceny, jako kolumnową komórkę wejściową wybieramy D1, czyli podstawową średnią cenę sprzedaży wziętą do analizy. Po kliknięciu przycisku OK Excel tworzy tabelę danych z jedną zmienną tak jak na rysunku poniżej.

05 wynik tabeli danych

W komórkach D11:F11 jest obliczony zysk, przychód i koszty zmienne przy cenie sprzedaży 1 zł. W komórkach D12:F12 obliczony jest zysk, przychód i koszt zmienny przy cenie sprzedaży 1,25 zł. Cena dająca największy zysk z całej listy to 3,75 zł. Przy cenie 3,75 zł roczny zysk wyniósłby 58.125 zł, roczny przychód 177.188 zł a roczny koszt zmienny to 14.063 zł.

Zmiana cen i kosztu jednostkowego

Przedsiębiorca także chce znać wysokość rocznego zysku w zależności od ceny zmieniającej się od 1 zł do 5 zł, z przyrostem 0,25 zł i jednostkowego kosztu zmiennego zmieniającego się od 0,30 zł do 0,60 zł, z przyrostem 0,05 zł. Ponieważ tym razem zmieniają się dwie dane wejściowe, należy użyć tabeli danych z dwiema zmiennymi.

06 tabela dla cen i kosztów

Tabela przedstawia sposób umieszczenia danych. W komórkach I11:I25 umieszczono zmieniające się ceny sprzedaży jogurtu. W komórkach J10:P10 wstawiono zmieniające się koszty jednostkowe. Tabela danych z dwiema zmiennymi (cena i koszt jednostkowy) może mieć tylko jedną daną wyjściową, w tym przypadku zysk. Dla przychodu i kosztu zmiennego należałoby przeprowadzić odrębne analizy. Reasumując komórka I10 to zysk przedsiębiorstwa. W tym przypadku jest wstawiona funkcja =D7. Zaznaczamy zakres tabeli I10:P25 i następnie z menu dane wybieramy „analiza warunkowa” i klikamy na „tabela danych”. Jako kolumnową komórkę wejściową podajemy cenę (komórka D1) a jako wierszową komórkę wejściową podajemy koszt jednostkowy (komórka D3). W ten sposób mamy pewność, że wartości z pierwszej kolumny zakresu są traktowane jako ceny, a wartości z pierwszego wiersza zakresu tabeli jako koszty jednostkowe. Po kliknięciu w OK na ekranie powinna pojawić się tabela danych z dwiema zmiennymi, taka jak na rysunku poniżej.

07 wynik tabeli danych dla cen i kosztów

Teraz widać na przykład, że dla ceny 3,5 zł i jednostkowego kosztu na poziomie 0,40 zł roczny zysk widoczny w komórce L19 wynosi 58.850 zł. Zauważmy, że wraz ze wzrostem kosztu jednostkowego, rośnie cena zapewniająca maksymalny zysk, jako że część naszego rosnącego kosztu przerzucamy na klientów. Aby w czytelny sposób pokazać maksymalny zysk dla każdego kosztu jednostkowego należy w komórce J27 wstawić funkcję MAX dostępną w „narzędzia główne” i zaznaczyć zakres J11:J25. W tej komórce ukaże się maksymalny zysk dla kosztu jednostkowego 0,30 zł i wyniesie on 62.813 zł.

08 max

Aby w łatwy sposób znaleźć cenę dla której ten zysk jest maksymalny należy użyć funkcji PODAJ.POZYCJE i WYSZUKAJ.PIONOWO. Funkcje te znajdziemy na karcie „narzędzia główne” po kliknięciu w strzałkę obok „autosumowanie”.

09 wiecej funkcji

Funkcje podaj.pozycje umieścimy w komórce J28. Po  znalezieniu tej funkcji  we wszystkich funkcjach wyskoczy nam okno dialogowe.

10 podaj pozycje

Jako szukana wartość należy zaznaczyć komórkę z maksymalnym zyskiem, czyli J27. Przeszukiwana_tab to tablica, która ma być przeszukana w celu znalezienia maksymalnego zysku. W tym przypadku to J11:J25. Typ porównania to 0 ponieważ dane nie są uszeregowane w kolejności malejącej lub rosnącej. Po wypełnieniu okna dialogowego należy kliknąć ok i w komórce 28 pojawi się 10 – maksymalny zysk dla kosztu zmiennego 0,30 zł jest umieszczony w 10 wierszu.

11 wynik dla podaj pozycje

Aby Excela w przejrzysty sposób pokazywał cenę dla której zysk jest maksymalny, w komórce J29 umieścimy funkcję wyszukaj.pionowo. Okno dla tej funkcji wypełniamy jak na rysunku.

12 wyszukaj pionowo

Szukana wartość to komórka J28, czyli pozycja maksymalnego zysku. Tabela_tablica to zakres danych porządkowych i cen jogurtów (komórki H11:I25). Nr indeksu kolumny to 2 czyli Excel ma podać wynik z kolumny drugiej tj cenę dla zysku maksymalnego. Przeszukiwany zakres zostawiamy pusty. Po kliknięciu w ok w komórce J29 pojawi się cena dla której zysk jest maksymalny przy koszcie jednostkowym równym 0,30 zł.

12 wynik wyszukaj pionowo

Aby obliczyć ceny dla każdego kosztu jednostkowego należy przeciągnąć funkcję MAX i PODAJ.POZYCJĘ do komórek K27:P28. W funkcji WYSZUKAJ.PIONOWO przed przeciągnięciem należy wstawić znaki $ w pasku zadań w komórkę H11 i I25, tak jak na rysunku poniżej.

13 dolar w wyszukaj pionowo

Dopiero po tym zabiegu można przeciągnąć tą funkcję aż do komórki P29.

14 wynik końcowy

Na wykresie poniżej w komórkach J29:P29 widać cenę dającą maksymalny zysk dla każdej zmiany kosztu jednostkowego. I tak dla kosztu jednostkowego od 0,30 zł do 0,50 zł cena która przyniesie maksymalny zysk to 3,75 zł a dla kosztu jednostkowego w granicach od 0,55 zł do 0,60 zł to 4 zł.

Dodaj komentarz

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