Jak obliczyć prowizję od sprzedaży w arkuszu kalkulacyjnym (stawki progowe i podziały)
Prawidłowe obliczenie prowizji od sprzedaży w arkuszu kalkulacyjnym sprowadza się do czterech decyzji. Czy progi są progresywne czy płaskie i jak wyszukiwana jest stawka? Następnie, jak dzielona jest wspólna transakcja i gdzie trafiają zwroty prowizji (clawbacks)? Jeśli pomylisz się przy pierwszym kroku, każda kolejna liczba będzie błędna.
Sama arytmetyka nie jest trudna. Trudność polega na tym, że zasady znajdują się w dokumencie planu napisanym przez kogoś innego. Arkusz kalkulacyjny musi następnie zapisać je w formie, którą współpracownik będzie mógł zweryfikować.
Ten poradnik wyjaśnia, dlaczego ten proces destabilizuje arkusze kalkulacyjne, omawia trzy stosowane podejścia oraz wskazuje momenty, w których model przestaje radzić sobie ze zmianami w planach. Jest to opis przepływu danych, a nie porada płacowa czy prawna, dlatego wynik należy potwierdzić z osobą odpowiedzialną za plan prowizyjny.
Dlaczego prowizje od sprzedaży destabilizują arkusz kalkulacyjny
Pierwszym problemem jest to, że słowo „progowany” oznacza dwie różne rzeczy, a dokumenty planów rzadko precyzują, o którą chodzi.
W planie z progami płaskimi (flat tier) osiągnięcie danego przedziału powoduje zastosowanie przypisanej do niego stawki do całej kwoty. W planie z progami progresywnymi (progressive tier) każda część kwoty generuje prowizję według stawki dla przedziału, w którym się znajduje – podobnie jak w przypadku progów podatku dochodowego. Przy sprzedaży o wartości $120,000 rozbitej na przedziały ze stawkami 5%, 7% i 9%, te dwie interpretacje różnią się o tysiące dolarów.
Drugim problemem jest to, że transakcja szybko przestaje być pojedynczym wierszem. Wspólna transakcja zamienia się w dwa wiersze, akcelerator zmienia stawkę w środku okresu, zwrot cofa część płatności, a limit (cap) ogranicza łączną kwotę.
Trzecim problemem jest audytowalność. Prowizja musi być zrozumiała dla osoby, która ją otrzymuje. Pojedyncza komórka zawierająca sześć zagnieżdżonych instrukcji IF nie jest czytelna, a w takim formacie dostarczana jest większość tych modeli.
Zaokrąglenia kumulują się po cichu. Zaokrąglanie na każdym etapie pośrednim, zamiast jednorazowo przy końcowej płatności, powoduje rozbieżności, które rosną wraz z liczbą wierszy i nigdy nie zgadzają się z listą płac.
Ile Cię to kosztuje
Spory, których nie da się szybko rozwiązać. Gdy handlowiec kwestionuje kwotę, musisz pokazać mu całą ścieżkę od transakcji do płatności. Zagnieżdżonej formuły nie da się łatwo wytłumaczyć, więc rozmowa kończy się budowaniem modelu od nowa.
Kwota prowizji, której nie da się wyjaśnić, to kwota, która zostanie zakwestionowana w kolejnym kwartale.
Przebudowa modelu każdego roku. Stawki, przedziały i akceleratory zmieniają się co roku, a czasem nawet indywidualnie dla każdego handlowca. Model, który ma zaszyte stawki wewnątrz formuł, wymaga napisania na nowo, a nie zwykłej rekonfiguracji.
Uciążliwe uzgadnianie danych. Dział płac rozlicza wszystko co do grosza. Model z zaokrągleniami w trakcie obliczeń wykaże drobne różnice w setkach wierszy, a znalezienie przyczyny zajmie więcej czasu niż pierwotne stworzenie arkusza.
Rozwiązania doraźne, których próbują firmy
Opcja 1: Przeniesienie stawek poza formuły
Umieść przedziały i stawki w małej tabeli, a następnie wyszukuj stawkę, zamiast wpisywać ją na sztywno. Funkcja VLOOKUP z wyszukiwaniem zakresu ustawionym na TRUE znajdzie przedział, w którym mieści się wartość, pod warunkiem, że tabela jest posortowana rosnąco.
Funkcja XLOOKUP robi to samo z jawnym trybem dopasowania „dokładne dopasowanie lub następny mniejszy element”, co jest znacznie łatwiejsze do odczytania pół roku później. Tam, gdzie logika sprowadza się do krótkiego łańcucha warunków, funkcja IFS bije na głowę zagnieżdżone instrukcje IF pod względem czytelności.
To pojedyncza zmiana o największej wartości, ponieważ przyszłoroczny plan będzie wymagał jedynie edycji tabeli, a nie przepisywania formuł. Rozwiązuje to całkowicie problem progów płaskich, ale w ogóle nie pomaga przy progach progresywnych.
Opcja 2: Prawidłowe obliczanie progów progresywnych
W planie progresywnym prowizja jest sumą kwot wpadających w poszczególne przedziały pomnożonych przez stawki dla tych przedziałów. Tabela pomocnicza z jednym wierszem na przedział, pokazująca część transakcji, która do niego trafia, czyni ten proces przejrzystym i łatwym do zweryfikowania.
Jeśli chcesz mieć to w jednej komórce, funkcja SUMPRODUCT zastosowana do progów przedziałów i różnic między kolejnymi stawkami da ten sam wynik. Niezależnie od wybranej formy, zachowaj gdzieś tabelę pomocniczą, ponieważ to właśnie ją pokażesz handlowcowi, który nie będzie zgadzał się z wyliczeniami.
Zastosuj funkcję ROUND tylko raz, przy ostatecznej kwocie płatności, i nigdy w trakcie obliczeń. Ograniczeniem tego podejścia jest konserwacja: każda zmiana przedziału wpływa zarówno na strukturę pomocniczą, jak i na tabelę stawek.
Opcja 3: Traktowanie podziałów, limitów i zwrotów prowizji jako wierszy księgi głównej
Oprzyj się pokusie modyfikowania pierwotnego wiersza transakcji. Zamiast tego rejestruj każde zdarzenie jako osobny wiersz z określonym typem: pierwotne uznanie, alokacja podziału, korekta akceleratora, redukcja limitu, zwrot prowizji (clawback).
Podziały stają się wtedy dwoma wierszami alokacji, których wartości procentowe muszą sumować się do 100%, a weryfikacja tej sumy pozwala wyłapać najczęstszy błąd. Zwrot staje się ujemnym wierszem z datą z okresu, w którym nastąpił, co pozwala zachować nienaruszone zestawienia z poprzednich okresów.
Daje to model, który można kontrolować linijka po linijce, co jest kluczowe. Generuje to jednak również cztery razy więcej wierszy i wymaga dyscypliny od każdego, kto korzysta z pliku. Nasz poradnik o tym, jak zamienić eksport z CRM w raport lejka sprzedaży, opisuje przygotowanie danych o transakcjach, na których opiera się ten proces.
Wspólny sufit. Wszystkie trzy podejścia zakładają, że plan jest stabilny w danym okresie. In praktyce śródroczne zmiany, jednorazowe gwarancje i wyjątki dla poszczególnych handlowców przychodzą e-mailem, a każda z nich to ręczna poprawka, której nikt nie dokumentuje.
Jak obliczyć prowizję od sprzedaży za pomocą Powerdrill Bloom
Krok 1: Prześlij dane transakcji i tabelę stawek
Prześlij jednocześnie eksport zamkniętych transakcji oraz tabelę stawek planu. Powerdrill Bloom profiluje oba pliki, dzięki czemu brakujący właściciele, puste kwoty i wartości procentowe podziału, które nie sumują się do 100%, zostaną wykryte przed obliczeniem jakiejkolwiek płatności.
Krok 2: Opisz zasady planu w języku naturalnym
Zamiast budować plan, po prostu go opisz. Wskaż, że progi są progresywne, podaj przedziały i stawki oraz określ próg akceleratora i ewentualny limit (cap).
Następnie w tym samym kroku poproś o przeprowadzenie weryfikacji. Zapytaj, które transakcje mają podziały niesumujące się do 100% i którzy handlowcy przekroczyli próg akceleratora w środku okresu. Na koniec zapytaj, które zwroty przypadają na inny okres niż ich pierwotna transakcja.
Krok 3: Wyeksportuj wykres, raport lub prezentację
Wygeneruj indywidualne zestawienie dla handlowca pokazujące ścieżkę od transakcji do płatności, wykres realizacji celów lub podsumowanie dla działu finansowego.
Dlaczego to rozwiązanie jest lepsze niż przebudowywanie modelu co kwartał
| Metoda ręczna | Powerdrill Bloom | |
|---|---|---|
| Stawki na nowy rok planu | Edycja tabel, a następnie ponowna weryfikacja formuł | Wskazanie nowych przedziałów i stawek |
| Progi progresywne a płaskie | Przebudowa struktury pomocniczej | Wskazanie, z którego rozwiązania korzysta plan |
| Procenty podziału, które się nie sumują | Ręczna kolumna kontrolna | Zapytanie, które transakcje nie przeszły weryfikacji |
| Wyjaśnianie kwoty handlowcowi | Odtwarzanie ścieżki formuły | Zapytanie o rozbicie od transakcji do płatności |
Ostatni wiersz to ten, który pozwala zaoszczędzić najwięcej czasu. Większość pracy przy prowizjach to nie same obliczenia, ale ich wyjaśnianie – a wyjaśnianie jest dokładnie tym, co uniemożliwia zagnieżdżona formuła.
Typowe błędy
Zastosowanie jednej stawki do całej kwoty w planie progresywnym. To najkosztowniejszy błąd w tej kategorii, który zawsze najmocniej zawyża lub zaniża wypłaty dla najlepszych handlowców.
Wpisywanie stawek na sztywno w formułach. Działa to przez rok, a przy kolejnej zmianie planu zmusza do pisania wszystkiego od nowa. Trzymaj stawki w tabeli, którą możesz przekazać działowi finansowemu.
Zaokrąglanie na każdym kroku. Zaokrąglaj tylko raz, przy ostatecznej płatności. Zaokrąglenia pośrednie powodują rozbieżności, które uniemożliwią uzgodnienie danych z listą płac.
Edytowanie pierwotnego wiersza w przypadku zwrotu. To niszczy wcześniejsze zestawienia, które zostały już zatwierdzone. Dodaj ujemny wiersz z datą z okresu, w którym nastąpił zwrot.
Zapominanie, że procenty podziału muszą sumować się do 100%. Dwie alokacje po 60% dają w sumie wypłatę 120% prowizji, a w arkuszu wyglądają zupełnie normalnie.
Trzymanie zasad planu wyłącznie w wiadomościach e-mail. Model prowizyjny, którego zasady istnieją tylko w wątku e-mail, nie może zostać poddany audytowi ani przekazany innej osobie. Zapisz je bezpośrednio w skoroszycie.
Mieszanie definicji okresów. Data zamknięcia transakcji, data faktury i data otrzymania płatności dają trzy różne wyniki. Wybierz jedną, zapisz ją i stosuj do każdego wiersza – to ta sama dyscyplina, której wymaga raport budżetu i jego wykonania.
Podsumowanie
Zdecyduj, czy plan jest progresywny czy płaski, przenieś stawki do tabeli, obliczaj przedziały w jawny sposób i rejestruj podziały, limity oraz zwroty prowizji jako osobne wiersze. Taka struktura przetrwa audyt i zmianę planu. Model prowizji od sprzedaży ocenia się po tym, czy ktoś inny jest w stanie go zrozumieć.
To, co generuje koszty, to konieczność przebudowy za każdym razem, gdy zmienia się plan, oraz późniejsze tłumaczenie wyliczeń. Jeśli na to schodzi Ci cały kwartał, wypróbuj Powerdrill Bloom na swoim eksporcie transakcji i tabeli stawek. Zobacz również nasz poradnik o tym, jak obliczyć koszt pozyskania klienta (CAC) z arkusza kalkulacyjnego, a także strony poświęcone asystentowi AI dla programu Excel oraz analizie finansowej AI.
Najczęściej zadawane pytania
Jaka jest różnica między płaskimi a progresywnymi progami prowizji od sprzedaży?
Próg płaski stosuje jedną stawkę do całej kwoty po osiągnięciu danego przedziału. Próg progresywny stosuje stawkę każdego przedziału tylko do tej części kwoty, która się w nim mieści – podobnie jak w przypadku progów podatku dochodowego.
Jak wyszukać stawkę prowizji bez zagnieżdżonych instrukcji IF?
Umieść przedziały i stawki w posortowanej tabeli, a następnie użyj funkcji VLOOKUP z dopasowaniem przybliżonym lub funkcji XLOOKUP ustawionej na dokładne dopasowanie lub następny mniejszy element. Oba rozwiązania pozwalają na zmianę stawek bez modyfikowania formuły.
Jak należy obsługiwać wspólne transakcje?
Zarejestruj jeden wiersz alokacji na każdego handlowca z jawną wartością procentową i dodaj weryfikację, czy wartości te sumują się do 100%. Modyfikowanie zamiast tego pierwotnego wiersza transakcji sprawia, że podział jest niemożliwy do skontrolowania.
Gdzie trafiają zwroty prowizji (clawbacks) i zwroty kosztów?
W okresie, w którym nastąpił zwrot, jako ujemny wiersz odwołujący się do pierwotnej transakcji. Wsteczna edycja pierwotnego wiersza zmienia zestawienia, które zostały już zatwierdzone i wypłacone.
Kiedy należy zaokrąglać kwoty?
Tylko raz, przy ostatecznej kwocie płatności. Zaokrąglanie kroków pośrednich wprowadza rozbieżności w wielu wierszach, co jest najczęstszą przyczyną braku zgodności modelu prowizyjnego z listą płac.