Semua Invoice atau faktur seharusnya memiliki nomer urut (Prenumbered) karena itu merupakan salah satu internal kontrol didalam perusahaan, namun dalam pemeriksaan kita menemui daftar penjualan yang secara kasat mata tidak urut atau loncat. Data ini kalo jumlahnya kecil mungkin tidak terlalu masalah bisa kita cek secara manual namun jika udah melibatkan data yang puluhan ribu maka kita akan kesulitan
Berikut adalah cara untuk mengenali apakah ada Nomor yang loncat serta cara menghitung ada berapa jumlahnya :
Langkah2nya secara garis besar :
- Tentukan dikarakter berapa nomer urut/prenumbered-nya (biasanya aka dibelakang), misalnya Invoice no : "16/XXX/YYY/00015" berarti ada setelah karakter "/" yang terakhir
- Tentukan berapa nilai maksimal atau nilai terakhir dari nomer urut untuk tiap jenis invoice , misalnya jenis "16/XXX/YYY/" nomer terakhir adalah 00100
- Tentukan berapa jumlah dari tiap jenis invoice , misalnya jenis "16/XXX/YYY/" setelah kita hitung hanya ada 30
- Jadi terdapat 70 invoice yang hilang atau loncat
Gampang kan?, dan ini tidak memerlukan rumus yang aneh2, hanya gunakan fungsi text seperti LEN, LEFT,MID,SUBSTITUTE, INT
Data dan hasil bisa didonlot disini
[wpfilebase tag=file id=221 tpl=download-button /]I. Tentukan dikarakter berapa nomer urutnya
- Lihat gambar berikut ini, ada di Folder List Invoices
- Posisi nomer urut ada setelah karakter "/" terakhir, kita bisa gunakan fungsi right untuk ambil lima angka terakhir namun rupanya WP tidak konsisten karena ada nomer yang 5 digit, 3 digit, dll
- Sudah aku bikinkan step by step rumusnya
- Jadi kita cari dulu posisi karakter "/" ada di mana?
- kita bisa gunakan FIND namun fungsi ini hanya menemukan kata/karakter yang pertama kali ketemu
- Disini logikanya kita akan ubah karakter "/" terakhir dengan karakter unik lain (misalnya "|") lalu kita find
- Untuk mengganti karakter tertentu dengan karakter lain kita gunakan SUBSTITUTE, fungsi ini unik karena bisa mengganti semua karakter yang ketemu atau karakter yang ke berapa yang ketemu, misalnya hanya mengganti karakter "/" yang ke -2, ke-3, dst
- untuk mengetahui nomer berapa karakter "/" yang terakhir maka kita hitung ada berapa karakter "/" di nomer faktur tsb
- Secara kasat mata kita tau karakter ke-3 namun sekali lagi WP tidak konsisten karena ada nomer "16/MAG/FAK/PB/03829" yang memilik 4 karakter "/"
- Jadi kita gunakan rumus LEN dan SUBSTITUTE
- Jadi logikanya kita buang semua karakter "/" lalu panjangnya kita bandingin dengan panjang nomer awal sebelum dibuang karakter "/"
- Kita buang dulu karakter "/"
- =SUBSTITUTE(A2,"/","")
- Hasilnya "16MAGFAKPB03829"
- Kita bandingkan Panjang Nomer awal dengan Panjang nomer yang gak ada karakter "/"-nya
- =LEN(B1)-LEN(SUBSTITUTE(A2,"/",""))
- =LEN("16/MAG/FAK/PB/03829")-LEN("16MAGFAKPB03829")
- =19-15
- =4
- Jadi ada 4 karakter "/"
- Lalu kita ganti karakter terakhir dengan "|", gunakan SUBSTITUTE namun hanya dikarakter terakhir
- =SUBSTITUTE(B1,"/","|",B3)
- =SUBSTITUTE("16/MAG/FAK/PB/03829","/","|",4)
- hasilnya "16/MAG/FAK/PB|03829"
- Lalu kita search karakter "|"
- =FIND(B1,"|")
- Hasilnya adalah 14
- Kita ambil type Invoice (Nomer invoice diluar Nomer Urut)
- =LEFT(B1,14)
- Hasilnya "16/MAG/FAK/PB/"
- Ambil Nomer urut, ambil karakter setelah karakter "/" terakhir+1
- =MID(B1,B5+1)
- Hasilnya "03829"
- Kita convert ke number
- =VALUE(B7)
- hasilnya 3829
- Rumus tadi lalu terapkan ke sheet yang berisi daftar Semua Invoice lalu kita kasih nama "List Invoices"
II. Tentukan untuk tiap type invoice berapa jumlah invoice yang telah dikeluarkan/nomor invoice terakhir
- Caranya adalah cari nomer urut terakhir dari semua nomer invoice
- Jadi kita sort semua invoice tapi descendent jadi dari nomer urut terbesar ke terkecil
- Kenapa mesti disort begitu karena akan kita gunakan fungsi VLOOKUP untuk tiap type invoice
- VLOOKUP akan mencari apakah type invoice ada di list semua invoice NAMUN hanya akan memberikan posisi invoice yang pertama kali ketemu
- Karena dibalik urutannya maka pasti yang ketemu adalah nomor yang terakhir
- Pertama kali kita sort dulu Sheet List Invoice secara menurun pada kolom NO Faktur/ kolom A
- Lalu kita buat sheet baru yang berisi semua Type invoice (kolom F)
- Kita copas kolom F dan Paste ke sheet baru yang kita beri nama No Faktur Loncat
- Lalu kita buang semua Duplikat (Data - Remove Duplicates)
- Dikolom B kita buat rumus
- =VLOOKUP(A2,'List Invoice'!F:H,3,FALSE)
- ini hasilnya
- Kita buat rumus untuk mencari atas berapa jumlah invoice yang telah dikeluarkan sesuai daftar
- Kita gunakan fungsi COUNTIF dengan kriteria Type Invoice
- Bikin rumus dikolom C
- =COUNTIF('List Invoice'!F:F,A2)
- ketemulah jumlah aktual invoice yg ada di List Daftar Invoice
- Kita bandingkan antara nomer terakhir dengan Jumlah Invoce yg ada
- Itulah jumlah Invoices yang loncat
- Ini hasilnya
- Coba kita cek misalnya item ke 4
- 16/MAG/WTC/
- Kita lihat di List Invoices apakah bener invoice cuman 3 biji dan nomer teralhir adalah 27 yang berarti ada 24 Invoice yang gak dilaporkan
Terbukti benar- Semoga Trik audit ini berguna untuk mencari koreksi