Analiza wrażliwości przychodów punktu dorabiania kluczy na zmiany cen i kosztu jednostkowego

Analiza wrażliwości przychodów punktu dorabiania kluczy na zmiany cen i kosztu jednostkowego

Właściciel osiedlowego punku dorabiania kluczy chce otworzyć nowy punkt w galerii handlowej. Z osiedlowego punku zna koszty, przychody jak i ilość dorabianych kluczy. Otwierając nowy punkt w galerii handlowej przedsiębiorca liczy na zwiększenie sprzedaży o 20%. Zanim właściciel zdecyduje się na ten krok, chciałby wiedzieć jak jego zysk, przychód i koszty zmienne będą zależeć od ceny, którą ustali oraz od jednostkowego kosztu zmiennego. Jednostkowy koszt zmienny to przede wszystkim zakup materiałów z których będzie wytwarzał nowe klucze. Zysk takiego punktu zależy od ceny, przychodów i kosztów. Tak więc przedsiębiorca, szacując ryzyko przedsięwzięcia, chciałby wiedzieć jak zmieni się zysk gdy dla przykładu będzie musiał obniżyć cenę o 2 zł a koszt zmienny wzroście o 1 zł. Tabela poniżej zawiera podstawowe dane miesięczne jakie należy użyć do analizy.

01 dane

W komórce D1 jest podana średnia cena sprzedaży, która wynosi 25 zł. Komórka D2 to szacowany popyt na dorabianie kluczy. Popyt zależy od ceny i wynosi -10*cena + 500 = 250. Przykład na obliczenie krzywej popytu przedstawiono pod tym linkiem. Koszt jednostkowy został oszacowany na poziomie 5 zł a miesięczne koszty stałe 2,5 tys. zł. Przychód firmy będzie zależeć od popytu i średniej ceny dorabianego klucza i wynosi 6.250 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 = 2.500 zł.

Jak cena wpływa na zyski i przychody

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 ceny sprzedaży dorabianego klucza od 17 zł do 29 zł z przyrostem 1 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 dane w tabelce

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 miejsce gdzie jest tabela danych

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

04 okno 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 1

W komórkach D11:F11 jest obliczony zysk, przychód i koszty zmienne przy cenie sprzedaży 17 zł. W komórkach D12:F12 obliczony jest zysk, przychód i koszt zmienny przy cenie sprzedaży 18 zł. Cena dająca największy zysk z całej listy to 27 zł i 28 zł. Przy cenie 27 zł miesięczny zysk wyniósłby 2.560 zł, miesięczny przychód 6.210 zł a miesięczny koszt zmienny to 1.150 zł.

Zmiana cen i kosztów

Przedsiębiorca także chce znać wysokość miesięcznego zysku w zależności od ceny zmieniającej się od 15 zł do 29 zł, z przyrostem 1 zł i jednostkowego kosztu zmiennego zmieniającego się od 4 zł do 7 zł, z przyrostem 0,50 zł. Ponieważ tym razem zmieniają się dwie dane wejściowe, należy użyć tabeli danych z dwiema zmiennymi.

06 dane 2 do tabeli

Tabela przedstawia sposób umieszczenia danych. W komórkach I11:I25 umieszczono zmieniające się ceny dorabianych kluczy. 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 wyniki z tab 2

Teraz widać na przykład, że dla ceny 19 zł i jednostkowego kosztu na poziomie 6 zł miesięczny zysk widoczny w komórce N15 wynosi 1.530 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 4 zł i wyniesie on 2,790 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ę 13 – maksymalny zysk dla kosztu zmiennego 4 zł jest umieszczony w 10 wierszu analizowanej tabeli.

11 wynik 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 4 zł.

13 wynik dla 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.

14 dolar w wyszukaj pionowo

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

15 wynik całość

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 4 zł do 5 zł cena która przyniesie maksymalny zysk to 27 zł a dla kosztu jednostkowego w granicach od 5,5 zł do 7 zł to 28 zł.

Dodaj komentarz

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