KHIZHNYAKOVEwidencja należności w Excelu: jak kontrolować płatności klientów
Ewidencja należności — szablon Excel: rejestr długów klientów i kontrola płatności — Zobacz szablon →
Sprzedaż z odroczonym terminem płatności to w wielu branżach standard: 14, 30, a czasem 60 dni. Dopóki klientów jest kilku, wszystko da się pamiętać. Przy kilkudziesięciu fakturach miesięcznie zaczynają się pytania: kto zapłacił tylko część, kto zalega już dwa miesiące i do kogo trzeba było zadzwonić w zeszłym tygodniu?
Ewidencja należności w Excelu odpowiada na te pytania w kilka sekund. Poniżej pokazuję, jak zbudować rejestr faktur i wpłat, policzyć opóźnienia i strukturę wiekową oraz ustawić przypomnienia o kontakcie z dłużnikiem.
Krok 1: słownik klientów
Na osobnym arkuszu zapisz klientów i standardowy termin płatności każdego z nich (np. 14 lub 30 dni) oraz osobę odpowiedzialną po Twojej stronie. Dzięki temu termin zapłaty uzupełni się na fakturze sam.
Krok 2: rejestr faktur
Każda faktura to jeden wiersz: numer, klient, data wystawienia, kwota. Termin zapłaty:
=C2+WYSZUKAJ.PIONOWO(B2;Klienci!A:B;2;FAŁSZ)
Jeśli z konkretnym klientem uzgodniłeś inny termin na tej fakturze, dodaj kolumnę „własny termin” i użyj jej w pierwszej kolejności: =JEŻELI(E2<>"";C2+E2;C2+WYSZUKAJ.PIONOWO(...)).
Krok 3: wpłaty osobno
Wpłaty zapisuj na osobnym arkuszu: data, numer faktury, kwota. Klienci często płacą w ratach albo jednym przelewem za kilka faktur – osobna tabela pozwala to wszystko zapisać bez nadpisywania danych. Suma wpłat dla faktury:
=SUMA.JEŻELI(Wpłaty!B:B;A2;Wpłaty!C:C)
Pozostało do zapłaty: =D2-G2.
Krok 4: dni opóźnienia
=JEŻELI(H2<=0;0;MAX(0;DZIŚ()-F2))
Jeśli faktura jest spłacona, opóźnienie wynosi 0. Jeśli nie – liczba dni od terminu zapłaty do dziś.
Krok 5: struktura wiekowa
Pogrupuj należności według okresu opóźnienia:
=JEŻELI(H2<=0;"Zapłacona";JEŻELI(I2=0;"W terminie";JEŻELI(I2<=30;"1–30";JEŻELI(I2<=60;"31–60";JEŻELI(I2<=90;"61–90";">90")))))
Potem SUMA.JEŻELI według grup daje obraz całości. Przykład hurtowni:
| Grupa | Kwota | Udział |
|---|---|---|
| W terminie | 84 000 zł | 60% |
| 1–30 dni | 32 000 zł | 23% |
| 31–60 dni | 14 000 zł | 10% |
| 61–90 dni | 6 000 zł | 4% |
| powyżej 90 dni | 4 000 zł | 3% |
| Razem | 140 000 zł | 100% |
Udział przeterminowanych: 40%. Szczególnej uwagi wymagają kwoty powyżej 60 dni – im starsza należność, tym trudniej ją odzyskać.
Krok 6: przypomnienia o kontakcie
Dodaj kolumnę „data następnego kontaktu”. Sygnał:
=JEŻELI(H2<=0;"";JEŻELI(K2="";JEŻELI(I2>0;"brak kontaktu";"");JEŻELI(K2<=DZIŚ();"Zadzwonić";"")))
Rano filtrujesz „Zadzwonić” i masz listę rozmów na dziś. Po rozmowie wpisujesz nową datę – np. termin, który obiecał klient.
Krok 7: należności według klientów
Ostatni element to zestawienie według klientów: suma pozostałych kwot i suma przeterminowanych dla każdego odbiorcy.
=SUMA.WARUNKÓW(Faktury!H:H;Faktury!B:B;A2;Faktury!I:I;">0")
Taka tabela często pokazuje, że większość problemów to dwóch–trzech klientów. Z nimi warto rozmawiać o krótszym terminie, przedpłacie albo limicie kupieckim, zanim wystawisz kolejną fakturę. Dobrym nawykiem jest też przegląd tego zestawienia raz w tygodniu, np. w poniedziałek rano – zajmuje kilka minut, a pozwala reagować, zanim opóźnienie zamieni się w kłopot z płynnością.
Najczęstsze błędy
- Nadpisywanie kwoty faktury po wpłacie. Tracisz historię i nie wiesz, ile faktycznie wpłynęło.
- Wpłaty bez numeru faktury. Przy kilku fakturach jednego klienta nie wiadomo, co zostało zapłacone.
- Brak struktury wiekowej. Sama suma należności nic nie mówi: 50 000 zł w terminie i 50 000 zł po 90 dniach to zupełnie inne sytuacje.
- Reagowanie dopiero po miesiącu. Telefon kilka dni po terminie działa znacznie lepiej niż wezwanie po kwartale.
Gotowy szablon: ewidencja należności
Nasz płatny szablon to gotowy rejestr długów klientów:
- „Klienci”: odbiorcy, termin płatności w dniach i odpowiedzialni handlowcy.
- „Faktury”: do 800 faktur – klient, data wydania, kwota, termin zapłaty (z możliwością własnego terminu), pozostała należność, dni opóźnienia, grupa, sygnalizacja i data następnego kontaktu ze znacznikami „Zadzwonić” i „brak kontaktu”.
- „Wpłaty”: do 1500 wpłat według numeru faktury, wpłaty częściowe sumują się automatycznie.
- „Podsumowanie”: należności razem, udział przeterminowanych, struktura wiekowa 1–30, 31–60, 61–90 i powyżej 90 dni, należności według klientów i wykresy.
Plik działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr. Obliczenia mają charakter orientacyjny.
Pytania i odpowiedzi
Co zrobić z nadpłatą klienta?
Pozostała kwota wyjdzie ujemna. Najprościej przypisać nadwyżkę do kolejnej faktury tego klienta, wpisując korektę na arkuszu wpłat: minus przy jednej fakturze, plus przy drugiej.
Jak traktować faktury korygujące?
Jako osobny wiersz z kwotą ujemną i odwołaniem do numeru faktury pierwotnej w opisie. Albo jako „wpłatę” na fakturę pierwotną – ważne, by stosować jedną zasadę.
Kiedy wysłać wezwanie do zapłaty?
To zależy od relacji z klientem. Wiele firm wysyła przypomnienie kilka dni po terminie, a formalne wezwanie po 14–30 dniach opóźnienia. Struktura wiekowa pomaga ustalić te progi.
Czy można liczyć odsetki za opóźnienie?
Arkusz liczy dni opóźnienia, więc odsetki łatwo dodać w osobnej kolumnie: kwota × stawka roczna × dni / 365. Aktualną stawkę odsetek ustawowych sprawdź samodzielnie.
