CARA MENG-AUDIT DATA MENGGUNAKAN EXCEL

Dalam studi kasus ini anda berperan sebagai auditor intern yang sedang melakukan audit atas Sistem Penggajian. Dalam audit ini, Pemimpin Tim anda telah menyusun program audit yang harus anda laksanakan dalam rangka menguji pengendalian pada sistem penggajian. Anda diminta untuk melaksanakan program audit tersebut dan diharapkan dapat selesai dalam waktu satu jam.

Masing-masing pegawai memiliki tarif gaji kotor per bulan yang berbeda, tergantung kepada kontrak kerja masing-masing pegawai. Nilai gaji setahun dan pembayaran bulanannya dicatat pada Daftar Pegawai.

Sistem penggajian di kantor anda sudah menggunakan sistem komputer. Setiap bulannya Bagian Gaji akan membuat suatu Daftar Gaji. Daftar Gaji tersebut memuat informasi mengenai nomor pegawai dan kode unit kerja serta jumlah gaji kotor, pajak dan gaji bersih (setelah dikurangi pajak) untuk masing-masing pegawai. Daftar gaji tersebut kemudian diproses komputer untuk mencetak lembar cek secara otomatis untuk setiap pegawai.

WorkDept Daftar kode dan nama unit kerja

Table	Keterangan
Payroll	Daftar Gaji beserta nomor cek yang telah dicetak
Emp_Mast	Daftar Pegawai beserta data kepegawaiannya
WorkDept	Daftar kode dan nama unit kerja

CASE 1
Pengujian apakah seluruh pegawai sudah tercantum dalam daftar gaji.

Tujuan

Disini kita akan mencari tahu apakah terdapat pegawai yang ada di daftar pegawai (Emp_Mast) namun di tabel daftar gaji (Payroll) tidak ada, jadi pegawai yang tidak menerima gaji.

Step By Step

1. Kita akan membuat hubungan antara dua tabel yaitu Payroll dan Emp_Mast Vlookup.
2.  Arahkan kursor pada sel A1, tekan END+ >.
3.  Buat worksheet baru, beri nama soal01.
4.  Isi dengan Tabel Emp_Mast, bisa via copy paste dari sheet Emp_Mast atau import dari Ms. Query.
5.  Ketik di row heading paling kanan (yang kosong) untuk membuat field baru, isi dengan “EMPNO_PAYROLL”.
6.  Isi rumusnya
   =VLOOKUP(Table_Query_from_TABK36[[#This Row];[EMPNO]],PAYROLL!B:G;1;FALSE).
7.  Rumusnya bisa berbeda tergantung komputer anda tapi caranya adalah, ketik =vlookup(  
8. Lalu arahkan kursor ke field EMPNO pada baris rumus (this row), lalu ketik ; (titik koma), lalu arahkan kursor ke sheet  PAYROLL, pilih kolom B:G (klik pada headernya) , ketik ;1;FALSE
9. Pastikan kalo field kunci yaitu EMPNO pada tabel/sheet PAYROLL berada di paling kanan.
10. Akan ada yang ada isinya berupa EMPNO dari tabel payroll serta nilai error #N/A yang artinya nilai tidak tersedia/kosong/tidak ada yang cocok. 
11.  Kita filter berdasarkan nilai yang error, klik pada tanda panah pada kanan bawah sel, filter, pilih Select All, OK. maka akan didapat hasil seperti ini:
12. Terdapat satu pegawai yang tidak ada di daftar gaji.

CASE 2

Pengujian apakah seluruh pegawai sudah tercantum dalam daftar gaji.

Tujuan
Mencari tahu apakah terdapat pegawai yang ada di daftar gaji (Payroll) namun di tabel daftar pegawai (Emp_Mast) tidak ada, jadi pegawai illegal yang menerima gaji.

Step By Step

1. Kita akan membuat hubungan antara dua tabel yaitu Payroll dan Emp_Mast Vlookup.
2. Kita buat worksheet baru, beri nama soal02.
3. Isi dengan Tabel Payroll, bisa via copy paste dari sheet Payroll atau import dari Ms. Query.
4. Ketik di row heading paling kanan (yg kosong) untuk membuat field baru, isi dengan “EMPNO_EMP_MAST”.
5. Isi rumusnya
   =VLOOKUP(Table_Query_from_TABK5[[#This Row];[EMPNO]];EMP_MAST!I:S;1;FALSE)
6.  Rumusnya bisa berbeda tergantung komputer anda tapi caranya adalah, ketik
   =vlookup( 
7. Lalu arahkan kursor ke field EMPNO pada baris rumus (this row), lalu ketik ; (titik koma) , lalu arahkan kursor ke sheet EMP_MAST, pilih kolom I:S (klik pada headernya) , ketik ; 1;FALSE)
8.  Pastikan kalo field kunci yaitu EMPNO pada tabel/sheet EMP_MAST berada di paling kanan.
9.  Akan ada yang sel yang isinya berupa EMPNO dari tabel payroll serta nilai error #N/A yang artinya nilai tidak tersedia/kosong/tidak ada yang cocok.
10.  Kita filter berdasarkan nilai yang error
    
11. Hasilnya akan keluar seperti ini
    
    Kesimpulan: terdapat dua pegawai yang menerima gaji namun tidak terdaftar/tidak ada di tabel EMP_MAST

CASE 3 
Pengujian apakah gaji kotor sudah sesuai dengan kontrak masing-masing pegawai

Tujuan
Kita bandingkan field GROSS_PAY pada tabel payroll dengan PAY_PER_PE pada tabel Emp_Mast (Nilai Kontrak)

Step by Step

1.  Kita akan membuat hubungan antara dua tabel yaitu Payroll dan Emp_Mast Vlookup.
2.  Kita buat worksheet baru, beri nama soal03.
3.   Isi dengan Tabel Payroll, bisa via copy paste dari sheet Payroll atau import dari Ms. Query.
4.   Ketik di row heading paling kanan (yang kosong) untuk membuat field baru, isi dengan “GROSSPAY_EMP_MAST”.
5.   Isi rumusnya
   =VLOOKUP(Table_Query_from_TABK7[[#This Row];[EMPNO]];EMP_MAST!I:N;6;FALSE)
6. Rumusnya bisa berbeda tergantung komputer anda tapi caranya adalah, ketik =vlookup( lalu arahkan kursor ke field EMPNO pada baris rumus (this row), lalu ketik ; (titik koma) , lalu arahkan kursor ke sheet EMP_MAST, pilih kolom I:N (klik pada headernya) , ketik ;1;false)
7.  Pastikan kalau field kunci yaitu EMPNO pada tabel/sheet EMP_MAST berada di paling kanan.
8. Akan ada sel yang isinya berupa PAY_PER_PE (field ke 6 dari tabel referensi/Emp_Mast, kolom I-N) serta nilai error #N/A yang artinya nilai tidak tersedia/kosong/tidak ada yang cocok.
9. Buat field baru “SELISIH”, yang rumusnya adalah =Table_Query_from_TABK7[[#This Row];[GROSS_PAY]]-Table_Query_from_TABK7[[#This Row];[GROSSPAY_EMP_MAST]]
10. Yaitu selisih antara GROSS_PAY dengan GROSSPAY_EMP_MAST
11. Kita filter berdasarkan nilainya tidak sama dengan nol
    
    
    Hasilnya akan keluar seperti ini
    
    Ada yang hasilnya #N/A karena memang tidak terdaftar di Emp_Mast (Soal 02), jadi terdapat 2 yang gaji kotor (Gross_Pay) tidak sama dengan kontrak/ daftar pegawai

CASE 4
 Pengujian perhitungan jumlah gaji bersih untuk setiap pegawai

 Tujuan
 Menguji apakah perhitungan Net Pay/Gaji Bersih sudah benar atau belum

Step by Step

1.   Kita buat worksheet baru, beri nama soal04.
2.   Isi dengan Tabel Payroll, bisa via copy paste dari sheet Payroll atau import dari Ms. Query.
3.  Ketik di row heading paling kanan (yang kosong) untuk membuat field baru, isi dengan “NET_PAY_RIGHT”.
4. Isi rumusnya  =IF(Table_Query_from_TABK9[[#This Row];[GROSS_PAY]]-Table_Query_from_TABK9[[#This Row];[TAX_AMOUNT]]=Table_Query_from_TABK9[[#This Row];[NET_PAY]];”BENAR”;”SALAH”)
5.  Arahkan kursor pada table GROSS-PAY, -(dikurang) table TAX AMOUNT, = (sama dengan) table NET_PAY ; (titik koma) ”BENAR”;”SALAH”)
6. Selanjutnya filter berdasarkan nilainya sama dengan “SALAH”
   
   
   Hasilnya akan keluar seperti ini
   
   
   Kesimpulan: semua perhitungan gaji bersih udah benar

6

CASE 5
 Pengujian perhitungan jumlah gaji bersih untuk setiap pegawai

TujuanMencari jumlah record yang berisi EMPNO yang sama dari tabel Payroll, yang menandakan kalau terdapat pegawai yang menerima cheque/gaji lebih dari sekali

Step By Step

1.  Buat worksheet baru, lalu beri nama soal05.
2.  Isi dengan Tabel Payroll, bisa via copy paste dari sheet Payroll atau import dari Ms. Query.
3. Ketik di row heading paling kanan (yang kosong) untuk membuat field baru, isi dengan “COUNT_EMPNO”.
4. Isi rumusnya =COUNTIF(B:B;Table_Query_from_TABK8[[#This Row];[EMPNO]])
5.  Rumusnya bisa berbeda tergantung komputer anda tapi caranya adalah, ketik  =COUNTIF(  lalu pilih kolom EMPNO/ kolom B), lalu ketik ; (titik koma) , lalu arahkan kursor ke field EMPNO pada baris rumus (this row) , ketik )
6. Kita mencari apakah ada nilainya atau tidak .
7. Kita filter berdasarkan nilainya yang lebih dari 1
   
   
   
   Hasilnya akan muncul seperti ini
   
   Kesimpulan: terdapat pegawai yang bernomor 000320 yang menerima pembayaran 2 x 

CASE 6
Hitung total gaji bersih yang dibayarkan untuk tiap dept , menggunakan rumus DSUM

Tujuan
Melakukan pengujian pada field CHEQUE_NO dari tabel PAYROLL, menggunakan fungsi Countif

Step By Step

1.  Buat worksheet baru, lalu beri nama soal06.
2.  Aktifkan sheet PAYROLL.
3.  Buat Field baru disebelah kanan, kasih nama COUNT_CHEQUE.
4.  Ketik Rumus
   =COUNTIF(A:A;Table_Query_from_TABK10[[#This Row];[CHEQUE_NO]])
5.  Count akan mencari Field CHEQUE_NO di tiap baris terjadi berapa kali.
6. Lakukan Filter untuk hasil yang lebih dari 1.
   Hasilnya akan keluar seperti ini
   
   Kesimpulan: tidak ada yang ganda nomor cheknya
   

CASE 7
Menghitung gaji kotor yang dibayarkan untuk tiap dept menggunakan rumus SUMIF

Tujuan
 Mencari jumlah Gaji kotor (GROSS_PAY) dari tabel Payroll dengan menggunakan fungsi SUMIF

Step By Step

1. Buat worksheet baru, beri nama soal07.
2.  Isi dengan Tabel Payroll, bisa via copy paste dari sheet Payroll atau import dari Ms. Query.
3. Buat tabel EMPNO, bisa dari copy paste tabel WORKDEPT atau buat dengan Advanced Filter, kalau dengan Advanced filter maka, blok kolom WORKDEPT di tabel Payroll, lalu klik advandced di ribbon Data–> Group Sort & Filter
   
   Akan keluar dialog box sbb, kita pilih “Copy to another location”, isi lokasi tujuan serta Pilih “Unique Record Only”, Klik OK.
   
4. Kemudian isi di cell K2 dengan =SUMIF(G:G,J2,C:C)
5. Copy dan paste ke cell K3:K10 dan liat hasilnya
   

CASE 8 
Menghitung total gaji bersih yang dibayarkan untuk tiap dept pergunakan rumus DSUM

Tujuan
Mencari jumlah Gaji Bersih (NET_PAY) dari tabel Payroll dengan menggunakan fungsi DSUM

Step By Step

1.   Buat worksheet baru, beri nama soal08.
2.   Isi dengan Tabel Payroll, bisa via copy paste dari sheet Payroll atau import dari Ms. Query.
3.  Buat tabel EMPNO bisa copy paste tabel WORKDEPT atau buat dengan Advanced Filter, kalau dengan Advanced filter maka, blok kolom WORKDEPT di tabel Payroll, lalu klik advandced di ribbon Data->group Sort & Filter.
   
   Akan keluar dialog box sbb, kita pilih “Copy to another location”, isi lokasi tujuan serta Pilih “Unique Record Only”, Klik OK.
   
   
   Hasilnya akan keluar seperti ini
   
4. Ubah susunan sehingga ada header row di tiap kriteria, bisa mendatar atau menurun , tergantung selera.
5. Isi di cell K2 dengan =DSUM(Table_Query_from_TABK12[#All],”NET_PAY”,J1:J2) lalu
   Copy dan paste ke cell bawahnya.
6.  Jika ingin mengisi yang mendatar, maka ketik rumus di cell N4
   =DSUM(Table_Query_from_TABK12[#All],”NET_PAY”,N1:N2) lalu Copy dan paste ke cell sebelahnya.
7. Hasilnya akan keluar seperti ini
   
8. Selesai