Panduan Praktis Menghitung Umur Piutang dengan Rumus Excel Sederhana

Salah satu aspek penting dalam manajemen keuangan perusahaan adalah mengelola piutang dengan efisien. Salah satu cara untuk mengukur efisiensi tersebut adalah dengan menghitung umur piutang dengan rumus Excel. Umur piutang memberikan gambaran tentang seberapa cepat pelanggan membayar tagihan mereka, dan ini dapat menjadi indikator kesehatan keuangan perusahaan. Dalam artikel ini, kita akan membahas cara menghitung umur piutang menggunakan Microsoft Excel.

Persiapkan Data Menghitung Umur Piutang dengan Rumus Excel

Langkah pertama untuk menghitung umur piutang dengan rumus Exceladalah mengumpulkan data yang diperlukan. Pastikan Anda memiliki daftar tagihan yang belum dibayar beserta tanggal jatuh tempo masing-masing. Dan untuk contoh, berikut adalah tabel piutang yang akan kita isi dengan fungsi Excel:

Agar perhitungan umur piutang selalu up to date, untuk kolom tanggal perhitungan piutang di sel D2 kita ketik rumus: =TODAY() yang artinya adalah tanggal hari ini.

Dan untuk kolom-kolom lainnya adalah data piutang dari pelanggan mulai dari Nama, Saldo Piutang, Tanggal Transaksi dan Jatuh Temponya. Silahkan Anda isi sesuai dengan data yang ada.

Hitung Apakah Sudah Jatuh Tempo atau Belum

Untuk kolom berikutnya untuk menghitung umur piutang dengan rumus Excel adalah untuk menghitung apakah piutang tersebut sudah jatuh tempo atau belum. Dan jika belum, maka nominal piutang akan tertampil di kolom ini.

Untuk menghitung selisih waktu antara tanggal transaksi dengan batas jatuh tempo, gunakan rumus IF Tunggal di sel F7 untuk kemudian dicopy dan paste ke sel dibawahnya hingga sel F14. Dan rumus IF Tunggal di sel F7 adalah:

=IF($D$2-D7<=E7;C7;0)

Copy-paste rumus tersebut ke sel F8 hingga F14. Dan hasilnya adalah sebagai berikut:

Disini bisa kita lihat bahwa satu-satunya piutang yang belum jatuh tempo adalah piutang dari Sdr. Endah dengan tanggal transaksi 15-11-2023 dengan syarat pembayaran 15 hari, yang artinya ketika dihitung, umur pitang Endah baru 11 hari (belum jatuh tempo).

Penjelasan Rumus

Mari kita jabarkan bagian-bagian dari rumus menghitung umur piutang dengan rumus Excel di atas:

1. IF: Ini adalah fungsi kondisional di Excel. Fungsi ini mengevaluasi suatu kondisi dan memberikan hasil yang berbeda tergantung pada apakah kondisi tersebut benar atau salah.
2. $D$2-D7: Ini adalah bagian dari kondisi. $D$2 mengacu pada sel D2 yang bersifat absolut, artinya, ketika rumus ini disalin ke sel lain, referensi ke D2 akan tetap D2. D7 adalah referensi relatif, sehingga akan menyesuaikan diri dengan baris tempat rumus itu berada. Jadi, rumus ini menghitung selisih antara nilai di D2 dan nilai di kolom D pada baris tempat rumus berada.
3. <=E7: Ini adalah bagian lain dari kondisi. Ini membandingkan hasil dari $D$2-D7 dengan nilai di kolom E pada baris tempat rumus berada. Jadi, kondisinya adalah apakah selisih tersebut kurang dari atau sama dengan nilai di kolom E pada baris tersebut.
4. C7: Jika kondisi tersebut benar (selisih <= E7), maka rumus ini mengembalikan nilai dari sel C7.
5. 0: Jika kondisi tersebut salah (selisih > E7), maka rumus ini mengembalikan nilai 0.

Jadi, secara keseluruhan, rumus menghitung umur piutang dengan rumus Excel ini digunakan untuk menghasilkan nilai dari sel C7 jika selisih antara nilai di D2 dan D7 kurang dari atau sama dengan nilai di kolom E pada baris tersebut. Jika kondisi tersebut tidak terpenuhi, hasilnya akan 0.

Menghitung Piutang yang Lewat jatuh Tempo

Setelah mendapatkan selisih waktu, kelompokkan umur piutang ke dalam kategori tertentu. Misalnya seperti terlihat pada contoh, Anda dapat membuat kategori “1-30 hari”, “31-60 hari”, “61-90 hari”, dan seterusnya. Gunakan fungsi IF untuk menentukan kategori berdasarkan selisih waktu. Dan fungsi IF yang akan kita gunakan di sini adalah IF AND karena akan menganalisis berdasarkan kategori. Dan berikut adalah fungsi IF AND untuk sel G7:

=IF(AND($D$2-$D7-$E7>=VALUE(LEFT(G$6;2));$D$2-$D7-$E7<=VALUE(RIGHT(G$6;2)));$C7;0)

Jika sudah, copy dan paste rumus tersebut ke samping dan ke bawah (range G7:I14). Dan berikut adalah hasilnya:

Dari sini bisa kita lihat umur piutang setelah jatuh tempo. Ada 2 orang yang umur piutangnya 1-30 hari setelah jatuh tempo, 2 orang yang umur piutangnya 31-60 hari setelah jatuh tempo, dan seterusnya.

Penjelasan Rumus

Rumus Excel untuk menghitung umur piutang dengan rumus Excel ini menggunakan fungsi IF dan AND untuk melakukan pengecekan kondisi yang lebih kompleks. Mari kita bahas bagian-bagian dari rumus tersebut:

1. IF: Seperti sebelumnya, ini adalah fungsi kondisional di Excel. Fungsi ini memberikan hasil yang berbeda tergantung pada apakah kondisi yang diberikan benar atau salah.
2. AND: Ini adalah fungsi logika AND yang memeriksa apakah semua kondisi yang diberikan benar. Jika semua kondisi benar, maka fungsi AND mengembalikan nilai TRUE; jika salah satu atau lebih kondisi salah, maka mengembalikan nilai FALSE.
3. $D$2-$D7-$E7: Ini adalah selisih antara nilai di sel D2, D7, dan E7. Rumus ini mengurangkan nilai di D7 dan E7 dari nilai di D2. Ini mungkin digunakan untuk menghitung sisa waktu atau selisih yang terkait dengan tanggal atau waktu.
4. VALUE(LEFT(G$6;2)): Ini mengambil dua karakter pertama (kiri) dari sel G$6 dan mengonversinya menjadi nilai numerik menggunakan fungsi VALUE. G$6 mungkin berisi suatu nilai teks, dan rumus ini mengambil dua karakter pertama dari nilai tersebut.
5. VALUE(RIGHT(G$6;2)): Ini mengambil dua karakter terakhir (kanan) dari sel G$6 dan mengonversinya menjadi nilai numerik menggunakan fungsi VALUE.
6. $C7: Jika kondisi yang diberikan oleh fungsi AND benar, maka rumus ini mengembalikan nilai dari sel C7.
7. 0: Jika kondisi yang diberikan oleh fungsi AND salah, maka rumus ini mengembalikan nilai 0.

Secara keseluruhan, rumus ini mengembalikan nilai dari sel C7 jika kondisi berikut terpenuhi: selisih antara D2, D7, dan E7 berada dalam rentang yang ditentukan oleh dua karakter pertama dan dua karakter terakhir dari G$6. Jika kondisi ini tidak terpenuhi, hasilnya akan 0. Rumus ini mungkin digunakan untuk memberikan nilai tertentu berdasarkan suatu aturan yang melibatkan perhitungan waktu dan batasan tertentu.

Hitung Jumlah Piutang yang Dianggap Macet

Untuk kategori macet, sebagai contoh kita asumsikan ketika umur piutang lebih dari 90 hari setelah jatuh tempo. Pada kenyataannya, Anda bisa tentukan sendiri batas kredit dianggap macet setelah berapa hari. Ini akan memberikan gambaran yang lebih jelas tentang distribusi umur piutang perusahaan Anda.

Untuk mengkalkulasi mana saja yang dianggap kredit macet (lebih dari 90 hari) maka di sel J7 rumus yang digunakan adalah:

=IF($D$2-$D7-$E7>=RIGHT($J$6;2)+1;C7;0)

Copy dan paste rumus tersebut ke sel-sel di bawahnya. Dan hasilnya adalah sebagai berikut:

Penjelasan Rumus

Rumus Excel ini menggunakan fungsi IF untuk melakukan pengecekan kondisi. Mari kita bahas bagian-bagian dari rumus tersebut:

1. IF: Seperti sebelumnya, ini adalah fungsi kondisional di Excel. Fungsi ini memberikan hasil yang berbeda tergantung pada apakah kondisi yang diberikan benar atau salah.
2. $D$2-$D7-$E7: Ini adalah bagian dari kondisi yang menghasilkan selisih antara nilai di sel D2, D7, dan E7. Rumus ini mungkin digunakan untuk menghitung sisa waktu atau selisih yang terkait dengan tanggal atau waktu.
3. RIGHT($J$6;2)+1: Ini mengambil dua karakter terakhir (kanan) dari sel J$6, dan kemudian menambahkan 1 ke nilai numerik yang dihasilkan. RIGHT($J$6;2) digunakan untuk mengambil dua karakter terakhir dari nilai di sel J$6, dan kemudian +1 dilakukan pada hasilnya. Ini mungkin digunakan sebagai batasan atau parameter tertentu.
4. C7: Jika kondisi yang diberikan oleh IF benar (yaitu, jika selisih dari $D$2-$D7-$E7 lebih besar atau sama dengan nilai dari RIGHT($J$6;2)+1), maka rumus ini mengembalikan nilai dari sel C7.
5. 0: Jika kondisi yang diberikan oleh IF salah (yaitu, jika selisih dari $D$2-$D7-$E7 kurang dari nilai dari RIGHT($J$6;2)+1), maka rumus ini mengembalikan nilai 0.

Secara keseluruhan, rumus ini mengembalikan nilai dari sel C7 jika selisih antara D2, D7, dan E7 lebih besar atau sama dengan nilai dari dua karakter terakhir dari J$6 ditambah 1. Jika kondisi ini tidak terpenuhi, hasilnya akan 0.

Analisis dan Pengambilan Keputusan

Setelah menghitung umur piutang dengan rumus Excel, lakukan analisis terhadap data yang telah Anda kumpulkan. Pertimbangkan apakah ada pola tertentu yang perlu diidentifikasi, dan buat strategi untuk meningkatkan efisiensi pengelolaan piutang.

Untuk menjumlahkan nominal piutang di sel yang berwarna hitam bisa menggunakan rumus SUM. Pada contoh, di sel F15 gunakan rumus: =SUM(F7:F14) dan hasilnya adalah sebagai berikut:

Sementara untuk baris kolom rasio piutang tidak tertagih di sel F19 adalah: =F15*F17 dan hasilnya adalah:

Kesimpulan

Menggunakan Microsoft Excel untuk menghitung umur piutang dengan rumus Excel dapat membantu Anda memahami lebih baik bagaimana piutang perusahaan Anda dikelola. Dengan memantau dan menganalisis umur piutang secara teratur, Anda dapat mengambil langkah-langkah proaktif untuk meningkatkan likuiditas dan mengoptimalkan arus kas perusahaan. Semoga panduan ini bermanfaat bagi Anda dalam mengelola piutang perusahaan dengan lebih efektif.

Terakhir, barangkali ada yang membutuhkan file Excel Panduan Praktis Menghitung Umur Piutang dengan Rumus Excel Sederhana ini, silahkan download melalui tautan di bawah ini: