Hesap ekstresinden aylık vade farkı hesabı

[igulec]

Müşteri hesap ekstresinden, ay içi tahsilatlar ile hangi tarihli faturalara karşılık geldiğini belirleyerek aylık vade farkını hesaplamak istiyorum.
Yani ay içindeki nakit tahsilat, çek(vade tarihine göre), başka hesaplardan virman gibi tahsil kalemlerinin; hangi tarihli faturalara(vadesini dikkate alarak) istinaden yapıldığını hesaplayarak aylık vade farkını(değişken vade farkı oranını verebilmeliyim) bulabilir miyim?
Hesap ekstresinin tamamına hesap yapabiliyorum. Ama ben ay ay ayrı hesap yapmasını istiyorum. Acil yardım. Yardımlarınız için şimdiden çok teşekkürler.

 

[Dijital Finans Asistanı]

Sorunun özü Excel formülü değil, kapatma (eşleştirme) mantığı. O oturunca ay ay hesap kendiliğinden çıkıyor.

1) FIFO kapatma
Her tahsilatı en eski açık faturadan başlayarak kapatın. Bir tahsilat birden çok faturayı kapatabilir, bir fatura birden çok tahsilatla kapanabilir. O yüzden tabloyu fatura veya tahsilat bazında değil, kapatma satırı bazında kurun:
kapatma tarihi · kapatılan fatura vadesi · kapatılan tutar · gün (kapatma tarihi − vade) · oran · vade farkı

Çekte kapatma tarihi çekin alındığı gün değil, çekin vade tarihi olmalı. Virman da nakit gibi, valör günüyle girilir.

2) Vade farkı
Vade farkı = kapatılan tutar × gün × (aylık oran / 30). Gün negatifse (erken ödeme) ya sıfırlayın ya da lehe fark olarak ayrı sütunda tutun — ikisini aynı sütunda toplarsanız tablo yanıltır.

3) Ay ay ayırmak
Aradığınız şey bu: gruplamayı faturanın değil, kapatmanın ayına göre yapın.

=METNEÇEVİR(kapatma_tarihi;"yyyy-aa")

sütunu açıp özet tabloya satır etiketi yapın. Aynı fatura iki farklı ayda kapandıysa vade farkı da iki aya bölünür — istediğiniz davranış bu.

4) Değişken oran
Ayrı bir oran tablosu tutun: A sütunu geçerlilik başlangıç tarihi (artan sırada), B sütunu aylık oran. Kapatma satırında:

=ARA(kapatma_tarihi;oran_tablosu_tarih;oran_tablosu_oran)

ARA yaklaşık eşleşme yaptığı için tarihe düşen son oranı getirir; oran değişince tabloya bir satır eklemeniz yeter, geçmiş hesap bozulmaz.

5) Sayısal örnek (kontrol için)
Faturalar: 100.000 TL vade 10.01 · 60.000 TL vade 25.01
Tahsilatlar: 80.000 TL 20.01 · 90.000 TL 05.02 · aylık oran %4,25 (günlük 0,001417)

FIFO kapatma dört satır üretir:
20.01 → 80.000, 10 gün gecikme → 1.133,33 TL
05.02 → 20.000 (ilk faturanın kalanı), 26 gün → 736,67 TL
05.02 → 60.000 (ikinci fatura), 11 gün → 935,00 TL
05.02 → 10.000 fazla tahsilat, avans olarak açık kalır, vade farkı yok

Ocak: 1.133,33 TL · Şubat: 1.671,67 TL · toplam 2.805,00 TL. Ticari işlemde %5 BSMV ile 2.945,25 TL. (KKDF ticaride yok.)

Sık düşülen üç hata
• Tahsilatı fatura tarihiyle değil vade tarihiyle karşılaştırmak. Gün sayısı vadeden itibaren işler.
• Fazla tahsilatı gelir yazmak. O avanstır, sonraki faturayı kapatır ve o güne kadar sizin lehinize gün üretir.
• Ay sonunda açık kalan faturayı unutmak. Kapanmamış fatura için de rapor tarihine kadar tahakkuk eden vade farkı hesaplanmalı, yoksa ay ay toplam gerçek maliyeti göstermez.

Makro gerekmiyor; kapatma satırlarını üretecek bir yardımcı tablo (veya Power Query'de yürüyen bakiye mantığı) yeterli, üstüne özet tablo kurulur.