Analiza wrażliwości przychodów okręgowej stacji kontroli pojazdów na zmiany ceny i kosztu jednostkowego

Analiza wrażliwości przychodów okręgowej stacji kontroli pojazdów na zmiany ceny i kosztu jednostkowego

Przedsiębiorca przed odpowiedzeniem sobie na pytanie jak podnieść sprzedaż powinien przeprowadzić analizę, która pokaże wpływ zmiany cen i kosztów na zyskowność firmy. W poniższym przykładzie przedstawiona jest analiza zyskowności firmy ze względu na zmiany ceny i kosztu jednostkowego.

Właściciel okręgowej stacji kontroli pojazdów zauważył, że jego zyski są na tym samym poziomie już od dwóch lat. W związku z tym zastanawia się nad zmianą ceny kontroli pojazdów. Przedsiębiorca zastanawia się jak będą kształtowały się jego przychody, koszty i zysk 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 wykonywanej usługi, która wynosi 90 zł. Komórka D2 określa ilość wykonywanych usług miesięcznie. Ilość ta zależy od ceny i wynosi -3 * cena + 600 = 330. Koszt jednostkowy został oszacowany na poziomie 15 zł a miesięczne koszty stałe 6 tys. zł. Przychód stacji kontroli pojazdów będzie zależeć od popytu i średniej ceny usługi i wynosi 29.700 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 = 18.750 zł.

Właściciel okręgowej stacji kontroli pojazdów chciałby wiedzieć, jak zmiana ceny usługi wpływa na miesięczny zysk, przychód i koszt zmienny. Przedsiębiorca chce zbadać te zmiany dla średniej ceny wykonywanej usługi od 70 zł do 190 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ą.

Tabela danych

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ę usługi 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 usługi 70 zł. W komórkach D12:F12 obliczony jest zysk, przychód i koszt zmienny przy cenie wykonywanej usługi 80 zł. Cena dająca największy zysk z całej listy to 110 zł. Przy cenie 110 zł miesięczny zysk wyniósłby 19.650 zł, miesięczny przychód 29.700 zł a miesięczny koszt zmienny to 4.050 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 70 zł do 210 zł, z przyrostem 10 zł i jednostkowego kosztu zmiennego zmieniającego się od 12,50 zł do 27,50 zł, z przyrostem 2,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 wykonywanej usługi. 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 okręgowej stacji kontroli pojazdów. 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 90 zł i jednostkowego kosztu na poziomie 22,50 zł miesięczny zysk widoczny w komórce N13 wynosi 16.275 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.

Określenie maksymalnego zysku

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 12,50 zł i wyniesie on 20.325 zł.

08

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ę 5 – maksymalny zysk dla kosztu zmiennego 12,50 zł jest umieszczony w 5 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 wykonywanej usługi (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 110 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.

Dodaj komentarz

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