Membuat Laporan Aging Piutang Dari Beberapa Customer Dalam Microsoft Excel - webpediax

Membuat Laporan Aging Piutang Dari Beberapa Customer - Dalam artikel sebelumnya kita telah membahas tentang cara membuat laporan umur piutang.

Dalam contoh artikel tersebut dibuatkan laporan aging piutang berdasarkan masing - masing faktur.

Kekurangan dari cara tersebut adalah bagaimana jika kita ingin mengetahui nilai umur piutang tersebut berdasarkan customer ?

Jika data yang kita olah sangat banyak memang artikel tersebut kurang memadai untuk diikuti karena laporan akan terlihat kurang efektif.

Dalam artikel ini saya akan membahas tentang membuat laporan aging piutang dari beberapa customer.

Artikel ini akan memberikan contoh untuk kondisi dimana jumlah faktur dan juga customer sangat banyak sehingga sulit jika menggunakan cara dalam artikel sebelumnya.

Untuk memudahkan dalam pemahaman penggunaan rumus saya akan sertakan link penjelasan tentang masing - masing rumus yang digunakan.

Cara Membuat Laporan Aging Piutang Dalam Excel

Jika dibandingkan dengan cara pada artikel yang pertama memang cara ini sedikit lebih sulit karena selain kriteria umur piutang tersebut terdapat juga kriteria customer untuk memisahkan pejumlahannya.

Sebelum melihat contoh pembuatannya sangat saya sarankan untuk mengetahui terlebih dahulu tentang rumus - rumus yang digunakan.

Rumus tersebut akan mempercepat perhitungan tetapi jika salah menggunakan rumus maka laporan yang kita buat tidak akan akurat bahkan bisa masuk dalam kategori salah perhitungan.

1. Rumus Untuk Membuat Laporan Aging Piutang

Ada tiga rumus yang saya gunakan untuk membuat laporan ini yaitu COUNTIF, SUMIF dan SUMIFS.

Secara umum fungsi dari ketiga rumus tersebut dapat saya jelaskan sebagai berikut :

1. Rumus COUNTIF

Rumus COUNTIF dapat digunakan untuk menjumlahkan range atau cell dengan syarat atau ketentuan yang telah ditetapkan.

Untuk lebih jelasnya tentang argumen san sytanx dari rumus ini silahkan pelajari dalam kelompok artikel Fungsi Statistik.

Dalam kelompok artikel tersebut diberikan juga contoh penggunaan rumus COUNTIF dalam Excelnya.

2. Rumus SUMIF

Rumus SUMIF digunakan untuk menjumlahkan angka dalam range dengan satu syarat atau kondisi yang ditetapkan.

Rumus SUMIF ini berbeda dengan rumus yang ketiga yaitu SUMIFS, hanya saja sama - sama menjumlahkan angka.

3. Rumus SUMIFS

Rumus SUMIFS hampir sama dengan SUMIF hanya berbeda pada syarat penjumlahannya.

Untuk SUMIFS syarat atau kondisi penjumlahannya bisa lebih dari satu kondisi sedangkan SUMIF hanya satu kondisi saja.

Setelah memahami fungsi dari ketiga rumus tersebut diatas maka selanjutnya kita akan membuat lapoaran aging piutang dalam Excel.

2. Contoh Membuat Laporan Aging Piutang Dalam Excel

Untuk contoh pembuatan laporan aging piutang silahkan perhatikan gambar dan uraian dibawah ini.

Pada sheet pertama dalam Excel saya memiliki data piutang yang terdiri dari satu tabel dengan beberapa kolom.

Sheet tersebut saya buat sebagai berikut :

Dalam Sheet tersebut terdapat informasi tentang customer, faktur dan juga umur dari masing - masing faktur.

Untuk kolom F (Umur Piutang) masukan rumus untuk mengitung umur faktur sebagai berikut :


=TODAY()-B7


Rumus tersebut untuk cell F7 dan untuk cell selanjutnya silahkan copy pastekan rumus tersebut.

Tugas kita selanjutnya adalah membuat laporan aging piutang berdasarkan data yang ada pada sheet pertama.

Penting !! : Nama Sheet 1 saya ganti menjadi Faktur dan sheet 2 menjadi Aging.

Dalam sheet kedua saya buatkan laporan aging piutang dengan contoh seperti pada gambar berikut ini :

Dalam gambar tersebut telah dipisahkan nilai piutang berdasarkan umurnya dan juga sesuai customernya.

Rumus apa saja yang digunakan untuk membuat laporan tersebut ? mari kita bahas satu persatu.

- Rumus Kolom D (Faktur)

Kolom ini akan menginformasikan jumlah faktur dari masing - masing customer yang terdapat pada kolom B.

Rumus yang digunakan untuk mengisi kolom Faktur tersebut adalah sebagai berikut : 


=COUNTIF(Faktur!$D$7:$D$23;Aging!B7)


Rumus tersebut untuk cell D7 dan untuk cell kebawahnya silahkan copy pastekan rumus tersebut.

Bagaimana cara kerja rumus tersebut untuk menghitung faktur dari masing - masing Customer ?

Seperti yang saya sebutkan diatas bahwa rumus COUNTIF akan menghitung junlah cell pada rentang tertentu.

Rumus tersebut akan menghitung jumlah cell yang ada pada sheet Faktur (Sheet 1) dengan syarat atau kondisi adalah kolom B (ID Customer).

- Rumus Kolom E (Jumlah)

Kolom tersebut akan menjumlahkan seluruh faktur dari masing - masing Customer yang terdapat dalam tabel Faktur.

Rumus yang digunakan untuk kolom tersebut adalah sebagai berikut :


=SUMIF(Faktur!$D$7:$D$23;Aging!B7;Faktur!$G$7:$G$23)


Untuk cell selanjutnya silahkan copy pastekan rumus tersebut, sudah saya buatkan cell absolut sehingga tidak akan error pada saat copy paste.

Rumus tersebut akan menjumlahkan nilai faktur pada tabel faktur (Sheet 1) kolom G (Jumlah) dengan syarat atau kondisi adalah ID Customer.

- Rumus Kolom F (0-30 Hari)

Kolom ini akan menghitung faktur yang mempunyai umur antara 0 sampai dengan 30 hari.

Rumus untuk kolom F cell F7 adalah sebagai berikut :


=SUMIFS(Faktur!$G$7:$G$23;Faktur!$D$7:$D$23;Aging!B7;Faktur!$F$7:$F$23;"<=30")


Untuk cell selanjutnya sampai dengan cell F15 copy pastekan rumus tersebut diatas.

Rumus ini akan menghitung nilai faktur dari kolom G (Jumlah) pada tabel dengan syarat atau kondisi sebagai berikut :

Syarat pertama adalah ID Customer, ini terlihat dalam potongan rumus sebagai berikut :


Faktur!$D$7:$D$23;Aging!B7


Syarat kedua adalah Umur Piutang pada tabel Faktur kolom F, potongan rumusnya terlihat seperti dibasah ini :


Faktur!$F$7:$F$23;"<=30"


Tabel Faktur F7:F23 adalah rentang pada kolom sheet 1 tabel faktur sedangkan "<=30" adalah batasan dari umur piutang yaitu dibawah atau sama dengan 30 hari.

- Rumus Kolom G (31-60 Hari)

Kolom ini akan menghitung Piutang yang memiliki umur antara 31 sampai dengan 60 hari.

Rumus yang digunakan adalah sebagai berikut :


=(SUMIFS(Faktur!$G$7:$G$23;Faktur!$D$7:$D$23;Aging!B7;Faktur!$F$7:$F$23;"<=60"))-F7


Untuk syarat atau kondisi sama dengan kolom F yaitu ID Customer dan juga umur piutang tapi untuk kolom ini dikurangi oleh kolom F.

Kenapa harus dikurangi dengan kolom F ?

Batasan syarat pada rumus tersebut saya masukan adalah "<=60", ini akan menghitung piutang yang berumur antara 0 sampai dengan 60.

Sedangkan yang kita butuhkan adalah 31-60 hari, jadi saya kurangkan dengan rumus sebelumnya yaitu kolom F yang telah menghitung umur Piutang sampai dengan 30 hari.

- Rumus Kolom H (61-90 Hari)

Kolom ini akan menghitung piutang yang telah memiliki umur 61-90 hari, untuk rumusnya saya buat sebagai berikut :


=(SUMIFS(Faktur!$G$7:$G$23;Faktur!$D$7:$D$23;Aging!B7;Faktur!$F$7:$F$23;"<=90"))-F7-G7


Untuk syarat tetap sama dengan dua kolom sebelumnya yaitu ID Customer dan juga umur piutang.

Tambahan untuk rumus ini adalah dikurangkan dengan kolom F dan G, untuk penejlesannya kenapa dikurangkan dengan dua kolom tersebut sama dengan pada penjelasan kolom G

- Rumus Kolom I (>90 Hari)

Kolom ini akan menghitung piutang yang mempunyai umur lebih dari 90 hari, rumusnya adalah sebagai berikut :

=(SUMIFS(Faktur!$G$7:$G$23;Faktur!$D$7:$D$23;Aging!B7;Faktur!$F$7:$F$23;">90"))

Untuk syarat dan kondisinya tetap sama yaitu ID Customer dan juga umur dari piutang tersebut.

Itulah penjelasan tentang cara membuat laporan aging piutang dengan rumus Excel, semoga artikel ini bermanfaat untuk semua pembaca.

Untuk contoh filenya akan saya sertakan segera karena belum saya upload, silahkan ditunggu.