Wiekowanie należności w Excelu potrzebuje dokładnie trzech rzeczy: kolumny z liczbą dni po terminie, przypisania każdej faktury do przedziału i sumy kwot w każdym przedziale. Formuły są proste. Trudność leży gdzie indziej, w jednej decyzji podejmowanej na samym początku: wiek faktury liczy się od terminu płatności, a nie od daty wystawienia. Arkusz zbudowany na dacie wystawienia liczy poprawnie, tylko mierzy nie to, co trzeba, i pokazuje zaległości, których nie ma.
Jeśli chcesz pominąć budowanie arkusza, ten sam raport policzy kalkulator wiekowania należności: wpisujesz kwoty i terminy, a dostajesz przedziały, udziały procentowe, średni ważony wiek przeterminowania oraz oznaczenie faktur po 90 dniu. Poniżej wersja dla tych, którzy wolą trzymać to u siebie w pliku.
Od czego liczyć wiek faktury
Od terminu płatności. Wiekowanie ma mierzyć opóźnienie kontrahenta, czyli to, o ile dni przekroczył umówiony termin, a nie to, jak stary jest dokument w segregatorze.
Różnica nie jest teoretyczna. Faktura na 40 000 zł wystawiona 10 czerwca z terminem płatności 60 dni jest wymagalna dopiero 9 sierpnia. Raport robiony 13 sierpnia powinien pokazać ją jako cztery dni po terminie, czyli w najlżejszym przedziale. Arkusz liczący od daty wystawienia pokaże 64 dni i wrzuci ją do przedziału 61 do 90 dni, w którym zwykle uruchamia się wezwanie do zapłaty. Efekt: ktoś dzwoni do dobrego klienta z pretensją o zaległość, której nie ma.
Im dłuższe terminy płatności daje firma, tym większe zniekształcenie. Przy kredycie kupieckim 60 dni cała struktura wiekowa przesuwa się o dwa przedziały w złą stronę, a zarząd widzi kryzys tam, gdzie działa normalna polityka handlowa. Dostawcy sieci handlowych, podwykonawcy budowlani i producenci sprzedający z odroczeniem obrywają najmocniej.
Jakie kolumny musi mieć arkusz
Minimalny zestaw to pięć kolumn. Wszystko poza nimi jest wygodą, nie koniecznością.
| Kolumna | Zawartość | Uwagi |
|---|---|---|
| A | Kontrahent | Do grupowania w tabeli przestawnej |
| B | Numer faktury | Identyfikacja pozycji |
| C | Termin płatności | Data, nie tekst. To jest kolumna, od której wszystko zależy |
| D | Kwota pozostała do zapłaty | Nie kwota faktury: po częściowej wpłacie wiekujesz resztę |
| E | Dni po terminie | Formuła, opisana niżej |
Kolumna D jest częstym miejscem błędu. Jeżeli klient zapłacił 30 000 zł ze 100 000 zł, w raporcie ma wisieć 70 000 zł, a nie pełna kwota faktury. Inaczej wiekowanie zawyża zaległości o wszystkie płatności częściowe, a te w handlu B2B są normą.
Formuła licząca dni po terminie
W kolumnie E wpisz różnicę między dniem raportu a terminem płatności:
=DZIŚ()-C2
Wynik dodatni to liczba dni zwłoki, ujemny oznacza fakturę jeszcze w terminie. Formuła jest zmienna, czyli przelicza się przy każdym otwarciu pliku, i to jest jej zaleta oraz wada naraz. Zaleta, bo raport zawsze pokazuje stan na dziś. Wada, bo nie da się później odtworzyć, jak wyglądał raport na koniec czerwca.
Jeżeli potrzebujesz raportu na konkretny dzień, na przykład na dzień bilansowy, wpisz tę datę do osobnej komórki, powiedzmy G1, i licz od niej z odwołaniem bezwzględnym:
=$G$1-C2
To jedna z niewielu sytuacji, w których warto odejść od DZIŚ. Wycena należności na 31 grudnia musi być policzona na 31 grudnia, a nie na dzień, w którym ktoś przypadkiem otworzył plik w marcu.
Przypisanie przedziału
Są dwa sensowne sposoby. Pierwszy to zagnieżdżone JEŻELI, czytelne przy standardowych przedziałach:
=JEŻELI(E2<=0;"Nieprzeterminowane";JEŻELI(E2<=30;"1 do 30";JEŻELI(E2<=60;"31 do 60";JEŻELI(E2<=90;"61 do 90";JEŻELI(E2<=180;"91 do 180";JEŻELI(E2<=365;"181 do 365";"powyżej 365"))))))
Drugi, wygodniejszy przy zmiennych progach, to WYSZUKAJ.PIONOWO w trybie przybliżonym. Zrób gdzieś z boku małą tabelkę progów: w jednej kolumnie dolne granice (-9999, 1, 31, 61, 91, 181, 366), w drugiej nazwy przedziałów, posortowane rosnąco. Potem:
=WYSZUKAJ.PIONOWO(E2;$H$2:$I$8;2;PRAWDA)
Czwarty argument PRAWDA jest tu kluczowy: każe funkcji znaleźć największą wartość nie większą niż szukana, czyli dokładnie to, czego potrzebuje przypisanie do koszyka. Ta wersja ma przewagę praktyczną: żeby zmienić progi, poprawiasz tabelkę, a nie siedem zagnieżdżonych warunków w każdym wierszu.
Sumy przedziałów i udziały procentowe
Do podsumowania wystarczy SUMA.WARUNKÓW po nazwie przedziału:
=SUMA.WARUNKÓW($D$2:$D$500;$F$2:$F$500;"91 do 180")
Udział procentowy to ta suma podzielona przez sumę wszystkich należności. I to właśnie procenty są tym, na co warto patrzeć. Kwoty bezwzględne rosną razem ze sprzedażą, więc same w sobie nic nie mówią: portfel należności większy o 30 procent po dobrym kwartale jest zdrowy. Udziały są porównywalne w czasie. Wzrost przedziału powyżej 90 dni z 6 do 15 procent portfela to sygnał pogorszenia ściągalności nawet wtedy, gdy sprzedaż i suma należności też urosły.
Jeżeli wolisz nie pisać formuł sumujących, wstaw tabelę przestawną: przedziały w wierszach, kwota w wartościach, kontrahent w filtrze. Dostajesz to samo w trzydzieści sekund i możesz jednym kliknięciem przejść z widoku całej firmy na widok jednego klienta.
Wskaźnik, którego nie ma w gotowych szablonach
Sama tabela przedziałów nie odpowiada na pytanie, jak długo średnio czeka złotówka uwięziona u kontrahentów. Zwykła średnia z kolumny E tego nie policzy, bo traktuje fakturę na 500 zł tak samo jak fakturę na 200 000 zł.
Potrzebna jest średnia ważona kwotą. Mnożysz opóźnienie każdej przeterminowanej faktury przez jej wartość, sumujesz i dzielisz przez sumę należności przeterminowanych:
=SUMA.ILOCZYNÓW((E2:E500>0)*E2:E500*D2:D500)/SUMA.WARUNKÓW(D2:D500;E2:E500;">0")
Ten jeden wskaźnik mówi o ściągalności więcej niż cała tabela, bo jest odporny na mieszanie drobnych faktur z dużymi. Warto zapisywać go co miesiąc obok siebie: trend jest tu ważniejszy niż pojedyncza wartość.
Trzy błędy, przez które raport wprowadza w błąd
Liczenie od daty wystawienia. Opisane wyżej, najczęstsze i najbardziej kosztowne, bo psuje relacje z klientami, którzy nic złego nie zrobili.
Zostawianie faktur zapłaconych. Arkusz nie wie, że przelew przyszedł. Jeżeli nikt nie usuwa rozliczonych pozycji, raport z każdym miesiącem coraz bardziej rozjeżdża się z rzeczywistością, a zaufanie do niego spada do zera. To zwykle powód, dla którego dobrze zbudowany plik po pół roku ląduje w archiwum.
Terminy zapisane jako tekst. Data zaimportowana z systemu bywa tekstem wyglądającym jak data. Formuła odejmowania zwróci wtedy błąd albo, gorzej, wynik pozornie sensowny. Szybki test: zaznacz kolumnę C i sprawdź wyrównanie. Daty Excel wyrównuje do prawej, tekst do lewej. Naprawia to funkcja DATA.WARTOŚĆ albo narzędzie Tekst jako kolumny.
Czego arkusz nie pokaże, a co ma skutek finansowy
Dobrze zbudowana tabela pokaże strukturę wiekową i na tym się kończy. Nie oznaczy dwóch dat, które mają bezpośredni skutek pieniężny, a które trzeba dopisać samodzielnie.
Pierwsza to dziewięćdziesiąty dzień po terminie zapłaty. Od tego dnia możesz skorygować podatek należny VAT (art. 89a ust. 1a ustawy o VAT) i zmniejszyć podstawę opodatkowania w zeznaniu rocznym (art. 18f ustawy o CIT albo art. 26i ustawy o PIT). Co ciekawe, dłużnik ma po tych samych 90 dniach obowiązek skorygować odliczony przez siebie VAT (art. 89b ust. 1 ustawy o VAT), i to niezależnie od tego, czy Ty w ogóle ze swojej ulgi skorzystałeś. Warto o tym wspomnieć w rozmowie, bo dla dłużnika oznacza to realny wypływ gotówki do urzędu. Szczegóły opisaliśmy na stronie o uldze na złe długi.
Druga to przedawnienie. Roszczenia związane z prowadzeniem działalności gospodarczej przedawniają się po trzech latach (art. 118 Kodeksu cywilnego), przy czym koniec terminu przesuwa się na 31 grudnia roku, w którym te trzy lata upływają. Bieg przerywa pozew, wniosek o nadanie klauzuli wykonalności, wniosek egzekucyjny albo uznanie długu przez dłużnika (art. 123 § 1 KC). Samo wezwanie do zapłaty go nie przerywa, co jest chyba najczęściej powtarzanym nieporozumieniem w tym temacie.
Ta druga uwaga ma praktyczne przełożenie na to, co robisz z najstarszym przedziałem. Jeżeli udało się dogadać spłatę w ratach, warto mieć to na piśmie, i to nie tylko dla porządku: podpisany harmonogram albo ugoda są uznaniem długu, więc przerywają bieg przedawnienia i trzy lata zaczynają biec od nowa. Gdy kontrahent siedzi na drugim końcu Polski, papier wędruje tam i z powrotem tydzień albo dwa, dlatego prościej podpisać taki dokument elektronicznie i mieć sprawę zamkniętą tego samego dnia.
Kiedy arkusz przestaje wystarczać
Excel przegrywa nie dlatego, że źle liczy, bo liczy dobrze i przy kilkudziesięciu fakturach jest całkowicie wystarczający. Przegrywa dlatego, że raport wiekowania sam z siebie niczego nie odzyskuje.
Sprawdzian jest prosty: otwórz raport sprzed trzech miesięcy i porównaj z dzisiejszym. Jeżeli te same faktury stoją w tych samych przedziałach, tylko przesunięte o jeden koszyk w prawo, to problemem nie jest narzędzie do liczenia. Problemem jest brak kroku następnego, czyli tego, że po wygenerowaniu tabeli ktoś musi usiąść i wykonać trzydzieści telefonów, a w małym zespole ta kolumna zadań zawsze przegrywa z bieżącą sprzedażą.
W tym momencie sensowniejsze od rozbudowywania arkusza jest przeniesienie samego działania na stały monitoring należności, gdzie przypomnienie wychodzi w dniu po terminie bez czyjejkolwiek decyzji. Jeśli dopiero zaczynasz i chcesz najpierw zrozumieć samą metodę, wyjaśniamy ją od podstaw w tekście o tym, czym jest wiekowanie należności i jak je liczyć.
Najczęstsze pytania
Jakie przedziały przyjąć w wiekowaniu należności w Excelu?
Standardowo trzydziestodniowe do 90 dnia, a potem szersze: 91 do 180, 181 do 365 i powyżej roku. Granice nie są przypadkowe: 90 dni to próg ulgi na złe długi w VAT i w podatku dochodowym, a rok to moment, w którym szanse na dobrowolną zapłatę stają się marginalne. Przy krótkich terminach płatności warto dołożyć przedział 1 do 14 dni.
Czy wiekowanie należności robi się od kwoty brutto czy netto?
Od kwoty brutto pozostałej do zapłaty, bo taka kwota realnie nie wpłynęła na rachunek i taka jest przedmiotem windykacji. Kwota netto ma znaczenie przy analizie przychodowej, a nie przy ocenie płynności. Po częściowej wpłacie wiekuje się resztę, a nie pierwotną wartość faktury.
Jak zrobić wiekowanie należności i zobowiązań w jednym pliku?
Mechanika jest identyczna, zmienia się tylko źródło danych: zamiast faktur sprzedaży bierzesz faktury zakupu i ich terminy płatności. Najwygodniej trzymać je na osobnych arkuszach i zestawić podsumowania obok siebie. Dopiero to porównanie pokazuje, czy firma nie finansuje własnych opóźnień opóźnieniami wobec dostawców.
Czy Comarch ERP Optima i Rewizor mają gotowe wiekowanie należności?
Tak, oba mają wbudowane zestawienie struktury wiekowej i nie trzeba budować go od zera. Różnica dotyczy tego, co dzieje się po wygenerowaniu raportu: program księgowy pokazuje listę i na tym kończy swoją rolę, więc przypomnienia, telefony i wezwania nadal ktoś musi wykonać ręcznie.
Jak często aktualizować raport wiekowania?
Raz w miesiącu na potrzeby sprawozdawcze i raz w tygodniu na potrzeby operacyjne. Miesięczny służy do wyceny należności i odpisów aktualizujących, więc naturalnie wpina się w zamknięcie miesiąca. Tygodniowy służy do czegoś innego: do wyłapania faktur, które właśnie przekroczyły termin, kiedy jedno przypomnienie jeszcze wystarcza.