Jak obliczyć wysokość rabatu przy zachowaniu marży sklepu

Jak obliczyć wysokość rabatu przy zachowaniu marży sklepu.

Właściciel sklepu oferującego foteliki samochodowe dla dzieci chciałby wiedzieć jaki może dać rabat swoim klientom tak, aby zachować wysokość marży na wybranym poziomie. Przedsiębiorca chciałby wiedzieć jaki może dać rabat aby marża wynosiła minimalnie 15%. Aby to obliczyć należy przygotować tabelę w której będą podane koszty zakupu każdego z produktów i ceny sprzedaży detalicznej. Poniższa tabela przedstawia fragment takiej tabeli dla 15 wybranych produktów.

01 rabat marża

Aby odpowiedzieć na pytanie właściciela sklepu należy skorzystać z dwóch narzędzi dostępnych w Excelu. Pierwsze narzędzie to funkcja „szukaj wyniku” gdy chcemy obliczyć wielkość rabatu dla pojedynczego produktu. Jeśli chcemy „hurtowo” policzyć poszczególne wielkości rabatu to należy skorzystać z narzędzia solver. W tym artykule przedstawimy oba te sposoby.

Wyliczenie marży przy obecnych cenach

Pierwszym krokiem jest obliczenie marży dla każdego z produktów jakie sklep osiąga przy obecnych cenach. W poniższej tabeli pokazano wielkości obliczonych marż.

02 rabat marża

W kolumnie E obliczono marżę odejmując od ceny sprzedaży koszt zakupu danego produktu. Dla przykładu w komórce E3 wpisano =D3-C3. W kolumnie F obliczono marżę w ujęciu procentowym. Oblicza się ją poprzez podzielenie marży wartościowej przez cenę sprzedaży produktu. Dla przykładu w komórce F3 wpisano =E3/D3.

Drugi krok to obliczenie marży sklepu przy hipotetycznym rabacie 10%. Należy to obliczyć aby w późniejszych etapach „zamieniać” wartość rabatu tak aby marża wynosiła zakładane na wstępie 15%. Obliczenia te podaje tabela poniżej.

03 rabat marża

Kolumna H przedstawia hipotetyczny rabat o wysokości 10%. W kolumnie I obliczono cenę sprzedaży produktu po rabacie. W komórce I3 wpisano =D3-D3*H3, czyli od ceny sprzedaży odjęto cenę sprzedaży * hipotetyczny rabat. W kolumnie J policzono wysokość marży. W komórce J3 wpisano =I3-C3, czyli od ceny po rabacie odjęto koszt zakupu produktu. W kolumnie K obliczono procentową wysokość marży, dzieląc marżę po rabacie przez cenę produktu po rabacie. Dla przykładu w komórce K3 obliczono =J3/I3. Jak widać w kolumnie K marża dla każdego z produktów jest inna i czasami jest niższa niż zakładane na wstępnie 15%. W tym momencie należy przejść do obliczeń wysokości rabatu z kolumny H tak aby marża w kolumnie K wynosiła 15%. Poniżej przedstawione są dwa sposoby dzięki, którym można to obliczyć.

Znalezienie wysokości rabatu za pomocą „szukaj wyniku”

Pierwszy sposób pozwala na obliczenie wysokości rabatu pojedynczego produktu. W tym celu należy skorzystać z narzędzia „szukaj wyniku” dostępnego na karcie DANE,  w menu ANALIZA WARUNKOWA

04 szukaj wyniku

Obliczenie to dokonamy dla pierwszego produktu. W tym celu należy zaznaczyć komórkę K3 i znaleźć „szukaj wyniku” w menu DANE. Po kliknięciu powinno wyskoczyć okno dialogowe, które należy wypełnić tak jak na rysunku poniżej.

05 szukaj wyniku okno dialogowe

„Ustaw komórkę” należy zaznaczyć K3, czyli komórkę w której marża ma wynosić zakładane 15%. W polu „wartość” należy wpisać 0,15. W polu „zmieniając komórkę” należy kliknąć H3, czyli komórkę w której jest określony rabat. Po kliknięciu w OK Excel powinien podać następujący wynik.

06 rabat marża

Jak widać w tabeli, w komórce H3 została podana wysokość rabatu (8,25%) jaki przedsiębiorca może zaoferować klientowi tak aby marża sklepu na tym produkcie osiągnęła 15% (komórka K3).

Wysokość rabatu dla wielu produktów

Narzędzie „szukaj wyniku” w łatwy sposób pomaga w znalezieniu wyniku obliczeń jeśli wpływ na ten wynik ma tylko jeden czynnik (np. rabat). Wadą tego narzędzia jest to, iż można jednorazowo obliczyć tylko jedną wysokość marży. Jeśli przedsiębiorca chciałby „hurtowo” policzyć dopuszczalne wysokości rabatu dla każdego z produktów to należy skorzystać z narzędzia solver. Narzędzie to jest umieszczone w menu DANE, na karcie ANALIZA.

07 solver analiza danych

Wcześniej należy wykonać jedno obliczenie pomocnicze. Mianowicie w komórce K18 należy obliczyć sumę dla poszczególnych marż, poprzez wstawienie =SUMA(K3:K17). Wynik ten nie ma żadnej interpretacji biznesowej, natomiast pomaga przy korzystaniu obliczania poszczególnych marż. W tym momencie można już kliknąć w solver. Po kliknięciu wyświetli się okno dialogowe które należy wypełnić w następujący sposób.

08 solver

Pierwszym polem które należy wypełnić to „ustaw cel” zaznaczając komórkę K18. Poniżej mamy do wyboru pola „max”, „min” i „wartość”. W tym przykładzie można zaznaczyć „max” bądź „min” – na rysunku jest zaznaczone „max”. W polu „przez zmienianie komórek zmiennych” należy zaznaczyć komórki w których mają pojawić się wysokości rabatu dla każdego z produktów tak aby marża wynosiła 15%. W tym przykładzie będą to komórki H3:H17. Aby uzupełnić pole „podlegających ograniczeniom” należy kliknąć w przycisk „dodaj” umieszczony po prawej strony tego pola. Po kliknięciu wyskoczyć okno dialogowe, które należy wypełnić następująco.

09 solver warunek ograniczający

Okno to jest po to aby wpisać warunki ograniczające, czyli w tym przypadku marża która ma wynosić 15%. W polu „odwołanie do komórki” należy wpisać komórki w których ma wyświetlić się wysokość marży – komórki K3:K17. W polu środkowym należy znaleźć znak „=”. W polu „ograniczenie” należy wpisać 0,15. Po wypełnieniu w ten sposób tego pola dialogowego, należy kliknąć w OK a excel powróci do poprzedniego okna dialogowego z wpisanym już ograniczeniem w polu „podlegających ograniczeniom”. Pozostałe pola w solver pozostawiamy tak jak były, nic w nich nie zmieniając – nie mają znaczenia dla rozwiązania wysokości rabatów. W tym momencie można kliknąć w „rozwiąż”. Powinno wyskoczyć okno dialogowe „wyniki dodatku solver, które nie jest nam potrzebne w tym momencie, także należy kliknąć w OK. Wyniki otrzymane przez excel przedstawia poniższa tabela.

10 rabat marża

W kolumnie H zostały wyświetlone wysokości rabatów jakie właściciel sklepu może dać klientom tak aby marża sklepu wynosiła 15% (widoczna w kolumnie K). Dla przykładu maksymalny rabat jaki można udzielić dla foteliku dziecięcy Hamax  Sleepy wynosi 8,24%; dla produktu fotelik rowerowy KISS widocznego w wierszu 10 będzie to 15,29% (komórka H10).

Dodaj komentarz

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