Analiza wrażliwości przychodów sklepu obuwniczego na zmiany cen i kosztu jednostkowego
Właściciel sklepu obuwniczego nie jest zadowolony z zysków jakie osiągnął w ostatnim roku. W związku z tym myśli nad zmianą cen oferowanych butów. Do tego zmienia się koszt jednostkowy zakupu butów u producenta. Przedsiębiorca zastanawia się jak będą kształtowały się jego przychody, koszty i zyski w zależności od zmiany ceny czy też kosztu jednostkowego. Tabela poniżej zawiera podstawowe dane miesięczne jakie należy użyć do analizy.
W komórce D1 jest podana średnia cena sprzedaży, która wynosi 220 zł. Komórka D2 to szacowany popyt na dorabianie kluczy. Popyt zależy od ceny i wynosi -2,25 * cena + 795 = 300 (aby oszacować krzywą popytu kliknij tutaj). Koszt jednostkowy został oszacowany na poziomie 150 zł a miesięczne koszty stałe 5 tys. zł. Przychód firmy będzie zależeć od popytu i średniej ceny sprzedanej pary butów i wynosi 66.000 zł – komórka D5. Koszt zmienny zależy od kosztu jednostkowego pomnożonego przez popyt. Zysk to przychód pomniejszony o koszt stały i zmienny i równa się D5 – D4 – D6 = 16.000 zł.
Jak zmiana ceny wpływa na sprzedaż?
Przedsiębiorca chciałby wiedzieć, jak zmiana ceny sprzedaży wpływa na miesięczny zysk, przychód i koszt zmienny. Właściciel chce zbadać te zmiany dla średniej ceny sprzedaży pary burów od 180 zł do 300 zł z przyrostem 10 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 180 zł. W komórkach D12:F12 obliczony jest zysk, przychód i koszt zmienny przy cenie sprzedaży 190 zł. Cena dająca największy zysk z całej listy to 250 zł. Przy cenie 250 zł miesięczny zysk wyniósłby 18.250 zł, miesięczny przychód 58,125 zł a miesięczny koszt zmienny to 34.875 zł.
Miesięczne zyski a ceny
Przedsiębiorca także chce znać wysokość miesięcznego zysku w zależności od ceny zmieniającej się od 170 zł do 310 zł, z przyrostem 10 zł i jednostkowego kosztu zmiennego zmieniającego się od 140 zł do 170 zł, z przyrostem 5 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 sprzedawanych par butów. 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 190 zł i jednostkowego kosztu na poziomie 160 zł miesięczny zysk widoczny w komórce N13 wynosi 6.025 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 140 zł i wyniesie on 20.575 zł.
Maksymalny zysk
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ę 9 – maksymalny zysk dla kosztu zmiennego 140 zł jest umieszczony w 9 wierszu analizowanej tabeli.
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 pary butó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 250 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 140 zł do 155 zł cena która przyniesie maksymalny zysk to 250 zł a dla kosztu jednostkowego w granicach od 160 zł do 170 zł to 260 zł.