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