Określenie relacji pomiędzy ceną a ilością sprzedaży w sklepie rowerowym
Każdy przedsiębiorca powinien umieć ustalić związek pomiędzy ważnymi zmiennymi, takimi jak cena a sprzedaż, marża a sprzedaż, cena a zysk itp. Linia trendu, dostępna w programie Excel, pomaga często ustalić rodzaj zależności miedzy dwoma zmiennymi. Zmienna, którą chcemy obliczyć nazywana jest zmienną zależną. Zmienna, dzięki której wykonamy obliczenia, która wg właściciela firmy wpływa na inne, nazywamy zmienną niezależną. Jeśli właściciel firmy chciałby zbadać ilość sprzedaży i wie że na to wpływa cena produktu to ilość sprzedaży będzie zmienną zależną a cena niezależną. Inne przykłady relacji, które można zbadać:
| Zmienna niezależna | Zmienna zależna |
| Liczba produktów wytworzonych | Miesięczny koszt działania fabryki |
| Budżet wydany na reklamę | Miesięczna sprzedaż |
| Liczba pracowników | Roczne wydatki na delegacje |
| Dochód firmy | Liczba sprzedawców |
| Marżowość firmy | Zysk firmy |
Opis przypadku
Właściciel sklepu rowerowego zastanawia się jaki jest poziom sprzedaży produktów w zależności od ceny. Wiadomo, iż jeśli produkt jest droższy to ilość sprzedanych produktów jest niższa. Przedsiębiorca zastanawia się jak może ustalić relację między miesięczną ilością sprzedanych produktów a ceną. Poniższa tabela przedstawia fragment danych sprzedażowych sklepu rowerowego.
W kolumnie B podane zostały nazwy produktów, w kolumnie C ceny a w kolumnie D miesięczne ilości sprzedaży.
Wykres cen i ilości sprzedaży
Pierwszym krokiem do ustalenia relacji dwóch zmiennych, w tym przypadku ceny i ilości sprzedaży, jest utworzenie wykresu z punktami danych. W tym celu należy zaznaczyć dane na podstawie których chcemy utworzyć wykres. W tym przypadku będą to kolumny C i D. Na karcie „wstawianie”, w menu „wykresy” należy kliknąć „punktowy”. Po kliknięciu powinien pojawić się wykres obrazujący zależność cen i ilości sprzedaży tak jak na rysunku poniżej.
W tym momencie należy zwrócić uwagę aby ceny (zmienna niezależna) była na osi poziomej a ilość sprzedaży (zmienna zależna) na osi poziomej. Jeśli jest inaczej to po kliknięciu w wykres w menu Excela powinny pojawić się trzy dodatkowe zakładki: „projektowanie”, „układ” i „formatowanie”. Na karcie „projektowanie” należy kliknąć w „przełącz kolumnę/wiersz”. Ponieważ w tym przypadku ceny i ilości sprzedaży są prawidłowo ułożone na wykresie nie należy tego robić.
Wybór linii najlepiej określający wzajemny związek ceny i ilości sprzedaży
Kolejnym krokiem jest określenie linii trendu najlepiej dopasowanej do danych z wykresu. W tym celu, klikając wcześniej na wykres aby pojawiły się dodatkowe zakładki, należy na karcie „układ” kliknąć w „linia trendu / więcej opcji linii trendu”.
Po kliknięciu powinno wyskoczyć okno dialogowe, w którym do wyboru mamy kilka typów linii: liniowy, wykładniczy, logarytmiczny, wielomianowy, potęgowy i średnia ruchoma.
Naszym celem jest wybranie linii najlepiej dopasowanej do danych. Aby wybrać taką linię należy zaznaczyć na samym dole pole „wyświetl wartości R-kwadrat na wykresie”. R-kwadrat jest jedną z podstawowych miar dopasowania modelu i mieści się w przedziale od 0 do 1. Im R-kwadrat jest bliżej 1 tym linia trendu jest lepiej dopasowana do danych. Poziomy interpretacji R-kwadrat przedstawiają się następująco:
0,0 – 0,5 – dopasowanie niezadowalające
0,5 – 0,6 – dopasowanie słabe
0,6 – 0,8 – dopasowanie zadowalające
0,8 – 0,9 – dopasowanie dobre
0,9 – 1,0 – dopasowanie bardzo dobre
Dla przykładu w oknie dialogowym dla linii trendu zaznaczono „liniowy”.
Jak widać na wykresie powstała linia trendu a także R2. W tym przypadku R2 wynosi 0,2082. W związku z tym trend liniowy bardzo słabo jest dopasowany do zależności pomiędzy ceną a ilością sprzedaży i należy szukać dalej. W tym celu po kolei można zaznaczać pozostałe linie trendu aż wybierze się tą o najwyższym R-kwadrat.
Wpływ ceny na ilość sprzedaży
Po kolejnym dopasowaniu linii trendu zaobserwowano iż w tym konkretnym przypadku jest to linia potęgowa gdzie R2 wynosi 0,9525.
Wynik ten oznacza iż wpływ ceny na ilość sprzedaży ma kształt linii potęgowej a przede wszystkim ilość sprzedaży jest w 95,25% uzależniona od ceny. Dodatkowa informacja to równanie określające ten wpływ, które zostaje wyświetlone poprzez zaznaczenie „wyświetl równanie na wykresie” w oknie dialogowym formatowania linii trendu. W tym przykładzie równanie to ma postać
y=1420,8*x-0,987. Oznacza to, iż ilość sprzedaży wynosi 1420,8 pomnożone przez cenę do potęgi -0,987.
Planowanie sprzedaży nowych produktów
Jeśli przedsiębiorca chce oszacować sprzedać nowego produktu to korzystając z równania w łatwy sposób może to zrobić. W tym celu w komórce F19 wpisano cenę nowego produktu (34 zł) a w komórce G19 obliczono ilość sprzedaży poprzez wstawienie funkcji =1420,8*F19^-0,987.
Po obliczeniu przedsiębiorca wie, iż wprowadzając nowy produkt o cenie 19 zł może spodziewać się sprzedaży na poziomie 77 sztuk miesięcznie.