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.
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.
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”.
Po kliknięciu wyskoczy okno dialogowe jak na rysunku poniżej.
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.
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.
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.
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ł.
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”.
Funkcje podaj.pozycje umieścimy w komórce J28. Po znalezieniu tej funkcji we wszystkich funkcjach wyskoczy nam okno dialogowe.
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.
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.
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ł.
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.
Dopiero po tym zabiegu można przeciągnąć tą funkcję aż do komórki P29.
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ł.