KHIZHNYAKOVДебиторлық берешек есебі: клиенттердің қарызын Excel-де бақылау
Дебиторлық берешек есебі: клиенттер қарыздарының журналы — Excel үлгісі — Үлгіні ашу →
Тауарды немесе қызметті кейінге қалдырып төлеуге беру — көтерме сауда, мердігерлер, агенттіктер үшін қалыпты тәжірибе. Бірақ клиенттер саны көбейген сайын, кім қанша қарыз, қай шот ішінара төленді, кім 60 күннен бері кешіктіріп жүр — бұл туралы ақпарат хат алмасуда, бухгалтерлік бағдарламада және менеджердің жадында шашырап кетеді.
Дебиторлық берешек есебі бұл ақпаратты бір кестеге жинайды: әр шот, әр төлем, мерзімі өткен күндер және келесі қадам. Төменде Excel-де мұндай есепті қалай құру керектігін, негізгі формулаларды және жиі қателерді көрсетеміз.
Есептің құрылымы
Үш кесте жеткілікті:
- Клиенттер — атауы, келісілген кейінге қалдыру (күнмен), жауапты менеджер.
- Шоттар — әр шот бір жол: нөмірі, клиент, жөнелту күні, сома, төлем мерзімі.
- Төлемдер — әр төлем бір жол: күні, шот нөмірі, сома.
Төлемдерді шоттармен бір жолда емес, бөлек кестеде жүргізу маңызды: бір шот бірнеше бөлікпен төленуі мүмкін.
Негізгі формулалар
Төлем мерзімі — жөнелту күні плюс клиенттің кейінге қалдыруы:
=C2+VLOOKUP(B2;Клиенттер!$A:$B;2;FALSE)
Шот бойынша басқа мерзім келісілсе, оны бөлек бағанға жазып, =IF(F2<>"";F2;...) арқылы басымдық беріңіз.
Төленгені — барлық ішінара төлемдердің сомасы:
=SUMIF(Төлемдер!$B:$B;A2;Төлемдер!$C:$C)
Қарыз қалдығы: =D2-H2
Мерзімі өткен күндер: =IF(AND(I2>0;TODAY()>G2);TODAY()-G2;0)
Мерзімдік топ:
=IF(I2<=0;"Төленді";IF(J2=0;"Мерзімі өтпеген";IF(J2<=30;"1–30";IF(J2<=60;"31–60";IF(J2<=90;"61–90";"90+")))))
Мысал
Бүгін — 15 қараша.
| Шот | Клиент | Сома, ₸ | Мерзімі | Төленді, ₸ | Қалдық, ₸ | Мерзімі өтті, күн | Топ |
|---|---|---|---|---|---|---|---|
| 101 | «Береке» ЖШС | 1 200 000 | 20.11 | 0 | 1 200 000 | 0 | Мерзімі өтпеген |
| 095 | «Алтын-Дән» ЖК | 850 000 | 01.11 | 400 000 | 450 000 | 14 | 1–30 |
| 088 | «Береке» ЖШС | 600 000 | 05.10 | 0 | 600 000 | 41 | 31–60 |
| 072 | «Самал Трейд» | 300 000 | 10.08 | 100 000 | 200 000 | 97 | 90+ |
Жалпы қарыз — 2 450 000 ₸, оның ішінде мерзімі өткені — 1 250 000 ₸ (51%). «Береке» бір мезгілде жаңа тауар алып, ескі шотты кешіктіріп отыр — бұл менеджермен сөйлесуге себеп.
Мерзімдік құрылым және не үшін ол маңызды
Қарызды мерзімі өткен уақыты бойынша бөлу (0, 1–30, 31–60, 61–90, 90+ күн) тәуекелді көрсетеді: қарыз неғұрлым ескі болса, оны қайтару мүмкіндігі соғұрлым төмен. Топтар бойынша жиын: =SUMIF($K:$K;"31–60";$I:$I).
Әдеттегі тәртіп: 1–30 күн — еске салу хаты, 31–60 — қоңырау және жаңа жөнелтуді тоқтату, 61–90 — ресми талап, 90+ — заңгерлік шаралар немесе күмәнді қарыз ретінде бағалау.
Еске салғыш
Әр шотқа «келесі байланыс күні» бағанын қосыңыз. Формула:
=IF(AND(I2>0;L2<>"";L2<=TODAY());"Қоңырау шалу";IF(AND(J2>0;L2="");"байланыс жоқ";""))
Таңертең сүзгі арқылы «Қоңырау шалу» жолдарын шығарсаңыз — бүгінгі жұмыс тізімі дайын.
Жиі кездесетін қателер
- Ішінара төлемдерді шот жолына жазу. Үшінші төлемде бәрі шатасады — төлемдерді бөлек кестеде жүргізіңіз.
- Шот нөмірін көрсетпей төлем енгізу. Мұндай төлем ешқандай шотқа байланбайды, ал қалдық қате шығады.
- Тек жалпы қарызға қарау. 2 млн ₸ қарыз мерзімі өтпеген болса — қалыпты, ал 90+ күндік болса — проблема.
- Еске салуды кейінге қалдыру. Алғашқы апталарда клиентке қоңырау шалу әлдеқайда жеңіл және тиімді.
Дайын Excel үлгісі
Ақылы «Дебиторлық берешек есебі» үлгісі осы жүйені дайын күйде береді:
- «Клиенттер» — сатып алушылар, күнмен төлемді кейінге қалдыру және жауапты менеджерлер.
- «Шоттар» — 800 шотқа дейін: клиент, жөнелту күні, сома, төлем мерзімі, қарыз қалдығы, мерзімі өткен күндер, топ, бағдаршам және келесі байланыс күні. Шот үшін басқа мерзім келісілсе, «Өз төлем мерзімі» бағаны бар.
- «Төлемдер» — шот нөмірі бойынша 1500 төлемге дейін, ішінара төлемдер өздігінен қосылады.
- «Қорытынды» — жалпы қарыз, мерзімі өткеннің үлесі, 1–30, 31–60, 61–90 және 90 күннен астам мерзімдік құрылым, клиенттер бойынша қарыз және диаграммалар.
«Қоңырау шалу» және «байланыс жоқ» белгілері өздігінен шығады. Excel 2007 және жаңарақ, LibreOffice, макроссыз, кәдімгі формулалармен. Файл қазақ тілінде.
Жиі қойылатын сұрақтар
Клиентке қандай кейінге қалдыру беруге болады?
Бұл сіздің айналым қаражатыңызға және клиенттің тарихына байланысты. Жаңа клиентке қысқа мерзім немесе алдын ала төлем, ал уақтылы төлейтін тұрақты клиентке ұзағырақ кейінге қалдыру — қалыпты тәсіл.
Қарыз лимитін қалай бақылаймын?
Клиенттер кестесіне «лимит» бағанын қосып, клиенттің жалпы қалдығымен (SUMIF шоттар бойынша) салыстырыңыз. Лимиттен асса, жаңа жөнелтуді алдын ала төлеммен ғана жасаңыз.
Бухгалтерлік бағдарлама болса, Excel есебі керек пе?
Көбіне иә: бухгалтерлік жүйе берешекті көрсетеді, бірақ менеджерге ыңғайлы еске салғыштар, жауаптылар және күнделікті жұмыс тізімі сирек болады. Excel-ге шоттар мен төлемдерді жүйеден түсіріп алуға болады.
Күмәнді қарызды қашан есептен шығару керек?
Бұл бухгалтерлік және салықтық шешім, оны бухгалтермен ақылдасып, қолданыстағы ережелерге сай қабылдаңыз. Басқару есебінде 90+ күндік қарызды бөлек бақылау жеткілікті.
