Analiza wrażliwości przychodów sklepu obuwniczego na zmiany cen i kosztu jednostkowego

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.

01

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.

02

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

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

04

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

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.

06

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.

07

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ł.

08

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”.

09

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

10

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.

11

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

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ł.

13

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.

14

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

15

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ł.

Dodaj komentarz

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