KHIZHNYAKOVJak rozliczać kurierów w Excelu: stawka za zamówienie, kilometry, kary i premia
Ewidencja dostaw i wynagrodzenia kurierów w Excelu – szablon — Zobacz szablon →
Gdy kurierom płaci się akordowo, rozliczenie szybko robi się kłopotliwe. Jedna stawka za zamówienie dla centrum, inna dla przedmieść, do tego kilometry, kara za spóźnienie i premia, jeśli kurier zrealizował plan. Liczone na kalkulatorze lub w notesie, takie rozliczenie kończy się rozmową „ja mam inaczej”.
Poniżej znajdziesz prosty sposób, który działa w zwykłym Excelu: jak zbudować dziennik dostaw, jakie formuły policzą wynagrodzenie za jedną dostawę i jak z tego złożyć kwotę do wypłaty dla każdego kuriera. Na końcu pokażę, jak to samo mamy gotowe w szablonie.
Jak zbudować rozliczenie kurierów krok po kroku
1. Słowniki: strefy, stawki i zasady
Zacznij od osobnej zakładki z ustawieniami. Wpisz tam wszystko, co się zmienia rzadko, żeby nie szukać liczb w formułach:
- strefy z dwiema liczbami: stawką za zamówienie i dopłatą za kilometr,
- karę za spóźnienie i tolerancję w minutach (np. 10 minut),
- premię za wykonanie planu i minimalny odsetek dostaw na czas,
- listę kurierów z miesięcznym planem zamówień.
2. Dziennik dostaw: jeden wiersz, jedna dostawa
Kolumny: data, numer zamówienia, strefa, kurier, kilometry, godzina obiecana, godzina faktyczna, wartość zamówienia. Resztę mają policzyć formuły. Nie wpisuj ręcznie spóźnienia ani kwoty.
3. Formuły dla jednej dostawy
Spóźnienie w minutach (godziny w Excelu to ułamki doby, dlatego mnożymy przez 1440):
=MAX(0;(G2-F2)*1440)
Czy dostawa jest spóźniona, przy tolerancji 10 minut zapisanej w komórce Słowniki!$B$2:
=JEŻELI(H2>Słowniki!$B$2;1;0)
Stawka strefy i dopłata za kilometr pobierane ze słownika:
=WYSZUKAJ.PIONOWO(C2;Słowniki!$A$5:$C$12;2;FAŁSZ)
Wynagrodzenie za dostawę nie powinno spaść poniżej zera, więc całość obejmij funkcją MAX:
=MAX(0;stawka+km*dopłata-kara*spóźniona)
4. Rozliczenie za okres
Dla każdego kuriera zliczasz zamówienia, kilometry i spóźnienia w wybranym okresie. Tu przydaje się LICZ.WARUNKI i SUMA.WARUNKÓW:
=LICZ.WARUNKI(Dostawy!D:D;A2;Dostawy!A:A;">="&$B$1;Dostawy!A:A;"<="&$C$1)
=SUMA.WARUNKÓW(Dostawy!J:J;Dostawy!D:D;A2;Dostawy!A:A;">="&$B$1;Dostawy!A:A;"<="&$C$1)
Odsetek dostaw na czas to 1 minus spóźnienia podzielone przez zamówienia. Premię przyznaj funkcją JEŻELI, gdy zamówienia ≥ plan oraz odsetek ≥ wymagany próg.
Przykład z liczbami
Załóżmy dwie strefy: Centrum (6,00 zł za zamówienie, 0,40 zł za km) i Obrzeża (9,00 zł, 0,70 zł za km). Kara za spóźnienie to 3 zł.
| Dostawa | Strefa | Km | Spóźnienie | Wynagrodzenie |
|---|---|---|---|---|
| 1 | Centrum | 4 | nie | 6,00 + 4 × 0,40 = 7,60 zł |
| 2 | Obrzeża | 11 | tak | 9,00 + 11 × 0,70 − 3,00 = 13,70 zł |
| 3 | Centrum | 2 | nie | 6,00 + 2 × 0,40 = 6,80 zł |
Razem 28,10 zł. Gdyby kurier miał w miesiącu 200 zamówień, plan 180 i 92% dostaw na czas przy wymaganych 80%, dolicza się jeszcze premię za plan, np. 300 zł. Zasady premii ustalasz sam i zapisujesz w ustawieniach.
Najczęstsze błędy
- Stawki wpisane na sztywno w formułach. Po zmianie cennika trzeba poprawiać setki komórek. Trzymaj je w słowniku.
- Ręczne liczenie spóźnień. Dwie osoby policzą to różnie. Niech robi to formuła z jedną tolerancją.
- Brak kontroli dolnej granicy. Duża kara przy krótkiej dostawie może dać ujemne wynagrodzenie.
- Mieszanie okresów. Rozliczenie za miesiąc sprawdzaj po datach, nie po numerach wierszy.
- Brak planu w tym samym pliku. Premii za plan nie da się policzyć, jeśli plan leży w innym dokumencie.
Gotowy szablon rozliczenia kurierów
Jeśli nie chcesz budować tego od zera, w szablonie jest to samo, już połączone. Płatny plik ma cztery arkusze:
- „Słowniki”: kurierzy z planem zamówień, strefy ze stawką za zamówienie i dopłatą za km, kara za spóźnienie, tolerancja w minutach, premia za plan i minimalny odsetek dostaw na czas.
- „Dostawy”: dziennik do 1000 dostaw z datą, zamówieniem, strefą, kurierem, kilometrami, godziną obiecaną i faktyczną, wartością zamówienia, spóźnieniem i wynagrodzeniem kuriera.
- „Rozliczenie za okres”: dla każdego kuriera zamówienia, kilometry, spóźnienia, odsetek dostaw na czas, kary, wykonanie planu, premia i kwota do wypłaty. Inny miesiąc to zmiana dat okresu, bo dziennik nie jest czyszczony.
- „Podsumowanie”: dostawy według stref i dni, średni koszt dostawy, udział dostawy w wartości zamówień i dwa wykresy.
Korzyść jest prosta: dostawę wpisujesz raz, a kurier i Ty widzicie, z czego wzięła się kwota. W zestawie jest pusty plik oraz przykład z fikcyjnymi danymi. Działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr. Stawki i progi są w ustawieniach, więc przed startem sprawdź je z lokalnymi zasadami. Obliczenia są orientacyjne.
FAQ
Czy kara za spóźnienie powinna być stała, czy zależeć od minut?
To Twoja decyzja. Najprościej stosować stałą kwotę po przekroczeniu tolerancji, bo kurier od razu widzi, za co ją naliczono. Kara rosnąca z minutami wymaga dodatkowej kolumny i trudniej ją wytłumaczyć. Zasady kar wobec osób współpracujących sprawdź w umowie i lokalnych przepisach.
Jak liczyć kilometry, jeśli kurier jedzie z kilkoma zamówieniami naraz?
Ustal jedną regułę i trzymaj się jej: albo kilometry dzieli się równo między zamówienia na trasie, albo wpisuje się je przy pierwszej dostawie. Ważne, żeby ta sama zasada obowiązywała wszystkich kurierów.
Czy da się to rozliczać w zwykłych godzinach, a nie w stawkach za zamówienie?
Tak, ale to inny model. Wtedy zamiast stawki za dostawę liczysz godziny pracy pomnożone przez stawkę godzinową. Dziennik dostaw nadal przydaje się do kontroli jakości, np. odsetka dostaw na czas.
Jak uniknąć sporów o wypłatę?
Pokazuj kurierowi wiersze, z których powstała suma: datę, strefę, kilometry i spóźnienie. Gdy każda złotówka ma swój wiersz, dyskusja kończy się szybko.
