Jak przygotować raport wiekowania należności w Excelu (30, 60, 90 dni)
Raport wiekowania dzieli niezapłacone faktury na przedziały w zależności od tego, jak bardzo są przeterminowane – zazwyczaj 0–30, 31–60, 61–90 oraz powyżej 90 dni. O poprawności Twojego raportu decydują dwie kluczowe decyzje. Pierwsza to wybór, czy wiekujesz od terminu płatności, czy od daty wystawienia faktury. Druga dotyczy tego, czy częściowo opłacona faktura wykazuje pełną kwotę, czy pozostałe saldo.
Popełnij błąd w tych dwóch kwestiach, a suma w każdym przedziale będzie nieprawidłowa, co jest gorsze niż brak jakiegokolwiek raportu.
Ten poradnik wyjaśnia, dlaczego proces tworzenia raportu się sypie, omawia trzy stosowane podejścia oraz wskazuje, w którym momencie każde z nich przestaje działać. To opis przepływu danych, a nie porada księgowa, więc potwierdź sposób ujęcia transakcji z osobą odpowiedzialną za Twoją księgę główną.
Dlaczego raport wiekowania rozbija arkusz kalkulacyjny
Pierwszym problemem jest kwestia daty. Wiekowanie od daty wystawienia faktury mówi o tym, jak stare są dokumenty. Wiekowanie od terminu płatności informuje, jak bardzo spóźnia się klient – i w przypadku windykacji to właśnie tej liczby potrzebujesz.
Oba podejścia mają swoje uzasadnienie i dają różne raporty. Najczęstszym błędem jest arkusz kalkulacyjny, w którym nikt nie zapisał, która metoda została zastosowana.
Drugim problemem są płatności częściowe. Faktura na kwotę $10,000, z której otrzymano $7,000, to należność w wysokości $3,000 i musi ona pojawić się jako $3,000 w dokładnie jednym przedziale. Raporty wiekowania tworzone na podstawie listy faktur, a nie listy otwartych pozycji, po cichu zawyżają wszystkie wartości.
Trzecim problemem jest to, że raport stanowi migawkę stanu na dany moment. Przedziały są obliczane względem dnia dzisiejszego, więc wczorajszy plik jest już nieaktualny, a każda aktualizacja przelicza każdy wiersz na nowo.
Do tego dochodzą kłopotliwe wiersze. Noty kredytowe, przedpłaty, faktury sporne i salda wielowalutowe – każda z tych rzeczy wymaga osobnej reguły. A każda reguła musi potem przetrwać ingerencję kolejnej osoby, która otworzy plik.
Żaden z tych problemów z osobna nie jest trudny. Stają się one trudne, ponieważ pojawiają się wszystkie naraz, raz w miesiącu, pod presją czasu.
Ile Cię to kosztuje
Lista windykacyjna, na której nie można polegać. Celem podziału na przedziały jest wiedza, do kogo zadzwonić w pierwszej kolejności. Raport, który zawyża salda, sprawia, że ktoś ściga pieniądze, które już wpłynęły.
Praca od nowa w każdym miesiącu. Ponieważ przedziały są powiązane z dniem dzisiejszym, raport wiekowania nigdy nie jest gotowy na stałe. Każdy cykl to powtarzanie tych samych połączeń danych, tych samych formuł i tych samych ręcznych weryfikacji.
Sumy, które nie zgadzają się z księgą główną. Gdy sumy przedziałów nie dają w efekcie salda należności, raport traci wiarygodność. Znalezienie przyczyny zazwyczaj zajmuje więcej czasu niż samo stworzenie raportu.
Raport wiekowania cieszy się zaufaniem, ponieważ jego suma zgadza się z księgą główną. Jeśli ten warunek nie jest spełniony, cała reszta nie ma znaczenia.
Rozwiązania doraźne, których ludzie próbują
Opcja 1: Ustal definicje przed dotknięciem jakiejkolwiek formuły
Zapisz cztery rzeczy na samej górze arkusza. Od której daty wiekujesz i jakie są granice przedziałów. Czy kwoty są podane w ujęciu brutto, czy netto po uwzględnieniu płatności, oraz jaka jest data sporządzenia raportu.
Zajmuje to dziesięć minut, a zapobiega najczęstszym sporom. Journal of Accountancy opisuje ten sam proces budowy raportu, kładąc taki sam nacisk na prawidłowe przygotowanie konfiguracji na samym początku.
To decyduje również o wyborze źródła danych. Potrzebujesz zestawienia otwartych pozycji z pozostałymi saldami, a nie listy wszystkich faktur, jakie kiedykolwiek wystawiono.
Ograniczeniem jest to, że same definicje niczego nie obliczają. Chronią Cię jedynie przed obliczeniem błędnych danych.
Opcja 2: Stwórz kolumnę przedziałów, a następnie zsumuj dane w tabeli przestawnej
Oblicz dni po terminie jako datę sporządzenia raportu minus termin płatności, a następnie przypisz tę liczbę do etykiety przedziału. Funkcja TODAY daje Ci aktualną datę sporządzenia raportu, a DATEDIF zwraca liczbę dni między dwiema datami.
Jeśli chodzi o samą etykietę, funkcja IFS będzie po sześciu miesiącach o wiele bardziej czytelna niż zagnieżdżone instrukcje IF. Następnie zsumuj kwoty według klientów i przedziałów za pomocą SUMIFS, co pozwala na łatwy audyt obliczeń wiersz po wierszu.
Gdy raport trafia do obiegu, użyj wpisanej na sztywno daty sporządzenia raportu zamiast funkcji TODAY. Plik, który w kolejnym tygodniu po cichu sam zmieni wiekowanie, będzie sprzeczny z wersją, która już znajduje się w czyjejś skrzynce odbiorczej.
Ograniczeniem w tym przypadku jest wolumen danych i przypadki szczególne. Formuły działają, ale noty kredytowe, płatności częściowe i spory nadal trzeba obsługiwać ręcznie.
Opcja 3: Stwórz zakładkę z regułami obok danych liczbowych
Umieść kłopotliwe decyzje w jednym miejscu. Jak rozliczane są noty kredytowe, czy sporne faktury są wykluczane, czy oznaczane flagą. Jak przeliczane są salda w walutach obcych i po jakim kursie.
Dzięki temu raport ma szansę przetrwać, gdy uruchomi go ktoś inny. Jest to jednak również ta zakładka, która bywa pomijana, gdy gonią terminy na koniec miesiąca.
Ograniczeniem jest to, że zakładka z regułami jedynie dokumentuje przyjęte kryteria, ale ich nie stosuje. Ktoś wciąż musi wdrażać każdą regułę w każdym cyklu. Nasz poradnik o uzgadnianiu transakcji w arkuszu kalkulacyjnym opisuje proces dopasowywania danych, który stanowi podstawę tego procesu.
Wspólne ograniczenie. Wszystkie trzy podejścia zakładają, że zaczynasz od czystego zestawienia otwartych pozycji. Gdy źródłem jest surowy eksport faktur oraz osobny plik z płatnościami, prawdziwym wyzwaniem jest ich połączenie, zanim w ogóle rozpocznie się podział na przedziały.
Jak zbudować raport wiekowania za pomocą Powerdrill Bloom
Krok 1: Prześlij dane o fakturach i płatnościach
Prześlij zestawienie otwartych pozycji lub pliki faktur i płatności razem. Powerdrill Bloom profiluje kolumny zaraz po ich przesłaniu, dzięki czemu brakujące terminy płatności, puste kwoty i zduplikowane numery faktur zostaną wykryte przed obliczeniem jakichkolwiek przedziałów.
Krok 2: Opisz reguły podziału na przedziały w języku naturalnym
Zamiast budować reguły, po prostu je opisz. Wskaż, że wiekujesz od terminu płatności na określony dzień. Podaj granice przedziałów i określ, że kwoty powinny być pomniejszone o otrzymane płatności.
Następnie w tym samym kroku poproś o przeprowadzenie weryfikacji. Zapytaj, które faktury mają płatności przekraczające kwotę faktury, a które mają terminy płatności wcześniejsze niż daty ich wystawienia. Na koniec zapytaj, czy sumy przedziałów zgadzają się z saldem należności.
Krok 3: Wyeksportuj wykres, raport lub prezentację
Pobierz tabelę wiekowania dla poszczególnych klientów, wykres rozkładu przedziałów lub listę windykacyjną posortowaną według najstarszego salda.
Dlaczego to rozwiązanie jest lepsze niż tworzenie raportu od nowa co miesiąc
| Ręczna metoda | Powerdrill Bloom | |
|---|---|---|
| Łączenie faktur z płatnościami | Formuły wyszukiwania dla każdego pliku | Prześlij oba i zapytaj |
| Zmiana daty sporządzenia raportu | Ponowne obliczenie i weryfikacja | Podaj nową datę |
| Rozliczanie płatności częściowych | Ręczne tworzenie kolumny salda | Poproś o salda pomniejszone o płatności |
| Uzgadnianie sum z księgą główną | Ręczna weryfikacja w każdym cyklu | Zapytaj, czy sumy się zgadzają |
To właśnie na te pośrednie etapy ucieka większość czasu w miesiącu. Sam podział na przedziały to zwykła arytmetyka; prawdziwym zadaniem jest uzyskanie czystej listy otwartych pozycji.
Najczęstsze błędy
Wiekowanie od daty wystawienia faktury, gdy chodziło o termin płatności. W przypadku windykacji termin płatności jest prawie zawsze właściwym wyborem. Niezależnie od tego, co wybierzesz, zaznacz to w raporcie.
Wykazywanie kwot faktur zamiast pozostałych sald. Częściowo opłacona faktura powinna trafić do przedziału z kwotą pozostałą do zapłaty. Pełne kwoty zawyżają każdą sumę końcową.
Pozwalanie, aby funkcja TODAY ponownie wiekowała plik będący w obiegu. Zamroź datę sporządzenia raportu przed jego wysłaniem, w przeciwnym razie dwie osoby mogą odczytać różne liczby z tego samego pliku.
Ignorowanie not kredytowych. Nierozliczona nota kredytowa przypisana do klienta zmniejsza jego zadłużenie. Pominięcie jej sprawia, że saldo wygląda na gorsze niż w rzeczywistości.
Podział na przedziały według klientów zamiast według faktur. Przedziały ustala się dla poszczególnych faktur, a następnie sumuje dla każdego klienta. Wyciąganie średniej z wieku należności klienta ukrywa najstarszą pozycję, a to właśnie jej potrzebujesz.
Brak weryfikacji z księgą główną. Sumy przedziałów must sum to the receivables control balance. Pomiń tę weryfikację, a raport stanie się jedynie bezużyteczną ozdobą.
Tworzenie raportu od zera w każdym cyklu. Reguły nie zmieniają się co miesiąc, zmieniają się tylko dane. Zachowaj reguły i po prostu podmień wyeksportowane dane – to ta sama zasada, co w przypadku raportu wykonania budżetu.
Podsumowanie
Wybierz datę wiekowania, używaj pozostałych sald, zamroź datę sporządzenia raportu i uzgodnij sumy z księgą główną. Te cztery kroki decydują o tym, czy otrzymasz raport, na podstawie którego ludzie podejmą działania, czy tabelę, z którą będą dyskutować.
Kosztowne jest to, że cały proces odnosi się do dnia dzisiejszego, więc praca nigdy się nie kończy. Łączenie danych i weryfikacje powracają w każdym cyklu.
Jeśli na to właśnie ucieka Twój czas na koniec miesiąca, wypróbuj Powerdrill Bloom do obsługi wyeksportowanych faktur i płatności. Zobacz również nasz poradnik o tym, jak zamienić sprawozdania finansowe w formacie PDF na wykresy oraz stronę poświęconą analizie przepływów pieniężnych AI.
Najczęściej zadawane pytania
Jakie są standardowe przedziały w raporcie wiekowania należności?
Większość raportów wykorzystuje przedziały: 0–30, 31–60, 61–90 oraz powyżej 90 dni, często z dodatkową kolumną dla należności bieżących lub jeszcze niewymagalnych. Granice te są raczej kwestią umowną niż sztywną regułą, dlatego warto wskazać, które z nich zostały zastosowane.
Czy należy wiekować faktury od daty wystawienia, czy od terminu płatności?
Użyj terminu płatności, jeśli chcesz wiedzieć, jak bardzo spóźnia się klient – co jest zazwyczaj głównym celem windykacji. Użyj daty wystawienia faktury, jeśli chcesz wiedzieć, jak stare są same dokumenty.
Jak postępować z płatnościami częściowymi?
Wykaż pozostałe saldo, a nie pierwotną kwotę faktury, i umieść to saldo w jednym przedziale. Praca na zestawieniu otwartych pozycji zamiast na liście faktur pozwala rozwiązać ten problem automatycznie.
Jakich funkcji programu Excel potrzebuję?
Funkcji TODAY lub stałej daty jako daty sporządzenia raportu oraz DATEDIF do obliczenia dni po terminie. Funkcja IFS przypisuje etykietę przedziału, a SUMIFS sumuje kwoty według klientów i przedziałów. Żadna z nich nie jest skomplikowana – to definicje stanowią najtrudniejszą część.
Jak często należy aktualizować raport?
Co najmniej raz w miesiącu, a w przypadku aktywnych działań windykacyjnych – co tydzień, ponieważ każdy przedział jest obliczany względem daty sporządzenia raportu. Zamroź tę datę w każdej wersji, którą przekazujesz dalej.