Menghitung Gaji Karyawan Dalam Excel Dengan Uang Makan Berdasarkan Absensi
Menghitung Gaji Karyawan Excel - Penghitungan gaji karyawan mempunyai beberapa kondisi yang tentunya berbeda - beda.
Dalam artikel sebelumnya kita telah membahas tentang cara menghitung gaji karyawan dengan lembur berdasarkan absensi.
Kali ini saya juga akan membahas tentang cara membuat format untuk menghitung gaji karyawan dengan menggunakan rumus Excel.
Unsur yang akan ditambahkan dalam artikel kali ini adalah adanya uang makan yang dibayarkan berdasarkan absensi.
Penghitungan uang makan ini akan otomatis dipotong pada saat karyawan yang bersangkutan tidak masuk bekerja dengan alasan izin atau tanpa keterangan.
Dalam artikel ini selain akan membahas tentang cara menghitung gaji saya juga akan memberikan contoh file excelnya untuk bahan pembelajaran.
Menghitung Gaji Karyawan Dengan Uang Makan Berdasarkan Absensi
Menurut saya ada dua hal penting yang harus diketahui sebelum menghitung gaji dengan rumus Excel.
Hal pertama adalah ketetapan atau peraturan perusahaan yang berkaitan dengan pembayaran gaji karyawan.
Hal yang kedua adalah pemahaman kita terhadap rumus yang akan kita gunakan dalam menghitung gaji tersebut.
Kedua unsur ini akan digabungkan menjadi satu sehingga jika ada pemahaman yang salah maka kemungkinan besar akan mengakibatkan kesalahan juga dalam proses penghitungan gaji tersebut.
Rumus Excel Untuk Menghitung Gaji Karyawan dan Uang Makan
Dalam contoh penghitungan gaji kali ini ada ada beberapa rumus yang akan digunakan, yaitu :
1. Rumus IFERROR Excel
Rumus IFERROR saya gunakan untuk menghindari munculnya error pada cell yang masih kosong atau ID karyawan belum diisi.
Rumus ini sangat penting untuk dipahami karena akan digabungkan dengan rumus lainnya yang berfungsi sebagai error handling.
2. Rumus VLOOKUP Excel
Rumus VLOOKUP akan digunakan untuk mengambil data dari tabel karyawan pada saat mengisi sheet Gaji Karyawan.
Rumus VLOOKUP ini akan saya gabungkan dengan rumus IFERROR supaya tidak muncul error pada saat ID Karyawan masih kosong atau belum diinput.
3. Rumus NETWORKDAYS Excel
Rumus NETWORKDAYS saya gunakan untuk menghitung seluruh hari kerja dalam bulan yang bersangkutan.
Mohon dicatat bahwa hari kerja yang dihitung dengan rumus ini adalah senin - jum'at sedangkan sabtu dan minggu dihitung hari libur.
Jika dalam bulan tersebut terdapat tanggal merah atau hari libur nasional silahkan masukan angkanya kedalam argumen rumus ini.
Untuk menghindari kesalahan penghitungan hari silahkan pelajari rumus NETWORKDAYS dalam artikel yang telah dibahas sebelumnya.
4. Rumus COUNTIFS Excel
Rumus COUNTIFS saya gunakan untuk menghitung jumlah hari tidak masuk bekerja masing - masing karyawan karena ini akan menentukan jumlah besarnya uang makan yang dibayarkan.
Ada dua syarat untuk penjumlahan dalam contoh penghitungan gaji ini sehingga saya menggunakan rumus COUNTIFS bukan COUNTIF.
5. Rumus IF Excel
Rumus IF majemuk atau IF ganda saya gunakan untuk menghitung PPh pasal 21 yang terdiri dari beberapa pengujian kogika.
Seperti yang saya sampaikan diatas bahwa usahakan untuk memahami rumus - rumus tersebut supaya tidak kesulitan dalam memahami contoh penghitungan gaji ini.
Contoh Menghitung Gaji Karyawan dan Uang Makan Dengan Rumus Excel
Untuk contoh penghitungan gaji ini ada tiga sheet yang akan dibuat yaitu sheet Karyawan, Sheet Absensi dan Sheet Gaji.
Ketiga sheet ini dipisahkan untuk memudahkan penambahan nama dan juga input yang dilakukan.
1. Membuat Sheet Daftar Karyawan
Sheet pertama yang akan kita buat adalah Daftar Karyawan dan tentu sheet ini digunakan untuk input data - data karyawan.
Sheet pertama ini akan saya ganti namanya menjadi Karyawan dan mohon untuk dicatat karena yang akan muncul dalam rumus adalah Karyawan bukan Sheet1.
Untuk nama - nama kolom dan juga contoh tampilan sheet karyawan ini silahkan perhatikan gambar berikut ini :
Dalam gambar tersebut terlihat bahwa sheet ini hanya berisi informasi dari maisng - masing karyawan.
Semua kolom dalam tabel ini wajib untuk diisi karena akan menentukan pada sheet penghitungan gaji.
Untuk kolom uang makan merupakan nilai uang makan yang akan dibayarkan per harinya dan ini nanti akan dikalikan dengan jumlah hari kerja dalam bulan yang dihitung gajinya.
2. Membuat Sheet Absen
Sheet kedua yang akan kita buat adalah Absen dan sheet ini akan diisi dengan data absensi karyawan.
Untuk tampilan dari sheet Absensi ini adalah sebagai berikut :
Kode yang dimasukan kedalam tabel tersebut adalah S untuk Sakit, I untuk Izin dan A untuk tidak masuk kerja tanpa keterangan.
Dalam tabel ini terdapat cell yang diberikan rumus yaitu kolom Nama, kolom Nama ini akan mengambil nama Karyawan dari sheet Karyawan berdasarkan ID.
Rumus yang digunakan adalah sebagai berikut :
=IFERROR(VLOOKUP(B4;Karyawan!$B$4:$G$9;2;0);"")
Rumus tersebut dibuat dalam cell C4 dan untuk cell selanjutnya silahkan copy pastekan.
Untuk mengisi tabel ini caranya adalah pertama masukan ID dan kolom Nama akan otomatis terisi, selanjutnya isi tanggal absen dan masukan jenis absennya.
Khusus untuk kondisi sakit tidak dihitung kedalam potongan uang makan dengan syarat harus memberikan surat keterangan dari dokter.
Jika tidak memberikan surat keterangan dari dokter maka statusnya akan menjadi Izin dan dilakukan pemotongan terhadap uang makan.
3. Membuat Sheet Gaji Karyawan
Sheet yang terakhir adalah sheet gaji karyawan, sheet ini merupakan sheet utama yang akan menghitung keseluruhan gaji masing - masing karyawan.
Untuk tampilannya akan terlihat seperti berikut ini :
Silahkan klik gambar tersebut untuk memperjelas tampilan dan juga contoh angka - angkanya.
Rumus yang digunakan dalam kolom - kolom tabel tersebut diatas adalah sebagai berikut :
- Kolom Nama
Kolom Nama ini akan diisi dengan Nama masing - masing karyawan sesuai dengan ID yang dimasukan kedalam kolom B.
Rumus kolom Nama (cell C7) adalah sebagai berikut :
=IFERROR(VLOOKUP(B7;Karyawan!$B$4:$G$9;2;0);"")
Jika kita masukan ID Karyawan maka otomatis kolom ini akan terisi sesuai dengan nama yang dimasukan kedalam tabel Karyawan.
- Kolom Jabatan
Kolom Jabatan hampir sama dengan kolom Nama yaitu mengambil data dari tabel karyawan.
Rumus kolom jabatan adalah sebagai berikut :
=IFERROR(VLOOKUP(B7;Karyawan!$B$4:$G$9;3;0);"")
Rumus tersebut dimasukan kedalam cell D7 dan untuk cell selanjutnya silahkan copy pastekan rumus tersebut.
- Kolom Status
Kolom status akan diisi dengan status pernikahan masing - masing karyawan dan akan digunakan untuk menghitung pajak.
Rumus kolom status adalah sebabagi berikut :
=IFERROR(VLOOKUP(B7;Karyawan!$B$4:$G$9;6;0);"")
Rumus inipun akan mengambil data sesuai dengan yang kita masukan kedalam tabel karyawan.
- Kolom Hari
Kolom Hari ini akan menghitung seluruh hari kerja dalam bulan yang bersangkuta, data yang diambil dari periode awal dan akhir.
Rumus kolom hari adalah sebabagi berikut :
=IF(B7<>"";NETWORKDAYS($C$2;$C$3);"")
Cell C2 dan C3 harus diisi dengan tanggal awal dan tanggal akhir karena rumus ini akan menghitung jumlah hari kerja berdasarka kedua cell tersebut.
- Kolom Absen
Kolom Absen akan menghitung jumlah I dan A yang telah kita masukan kedalam tabel Absen sesuai dengan masing - masing karyawan berdasarkan ID.
Rumus kolom Absen adalah sebabagi berikut :
=COUNTIFS(Absen!B4:B9;Gaji!B7;Absen!E4:E9;"A")+COUNTIFS(Absen!B4:B9;Gaji!B7;Absen!E4:E9;"I")
Mohon diperhatikan bahwa yang dihitung adalah status Alpa dan Izin jika ada tambahan C untuk cuti maka otomatis tidak akan diamasukan kedalam hitungan ini.
- Kolom Efektif
Kolom Efektif ini akan mengurangi jumlah total hari kerja dengan jumlah total absen dari kolom G.
Rumus kolom Efektif adalah sebabagi berikut :
=IFERROR(F7-G7;0)
Saya tetap menambahkan rumus IFERROR kedalam kolom ini supaya tidak muncul error pada saat ada kolom yang kosong.
- Kolom Gaji Pokok
Kolom Gaji Pokok akan mengambil data dari tabel data karyawan kolom F atau kolom Gaji Pokok.
Rumus kolom gaji pokok adalah :
=IFERROR(VLOOKUP(B7;Karyawan!$B$4:$G$9;5;0);"")
Jika kolom ID sudah diisi maka otomatis kolom Gaji Pokok ini akan terisi sesuai dengan gaji masing - masing karyawan.
- Kolom UM / Uang Makan
Kolom ini akan menghitung jumlah uang makan yang diterima oleh masing - masing karyawan.
Rumus kolom UM adalah sebagai berikut :
=IFERROR(VLOOKUP(B7;Karyawan!$B$4:$G$9;4;0);0)*H7
Rumus tersebut akan otomatis mengalikan jumlah hari masuk kerja dengan uang makan per hari dalam tabel data Karyawan.
- Kolom Total Pendapatan
Kolom Total Pendapatan ini akan menjumlahkan kolom Gaji Pokok dengan kolom UM sebagai pendapatan masing - masing karaywan.
- Kolom PTKP
Kolom PTKP akan mengambil nilai PTKP atau Penghasilan Tidak Kena Pajak dari tabel PTKP dibawah tabel Gaji.
Rumus kolom PTKP adalah sebabagi berikut :
=IFERROR(VLOOKUP(E7;$H$17:$I$21;2;0);0)
Rumus ini akan mengambil nilai PTKP masing - masing karyawan berdasarkan statusnya.
- Kolom PKP
Kolom PKP akan menghitung gaji setahun dengan cara mengalikan kolom K dengan 12 kemudian dikurangi dengan PTKP.
Rumus kolom PKP adalah :
=IF((K7*12)>L7;(K7*12)-L7;0)
Dengan rumus ini secara otomatis nilai PKP akan muncul dan jika nilai PKP tersebut negatif maka otomatis dalam kolom akan ditampilkan angka 0 (nol).
- Kolom PPh 21
Kolom PPh 21 ini akan menghitung nilai Pajak Penghasilan untuk satu bulan sesuai dengan nilai PKP masing - masing karyawan.
Rumus kolom PPh 21 adalah :
=(IF(AND(M7>0;M7<=50000000);M7*5%;IF(AND(M7>50000000;M7<=250000000);(50000000*5%)+((M7-50000000)*15%);IF(AND(M7>250000000;M7<=500000000);(50000000*5%)+(200000000*15%)+(M7-50000000-200000000)*25%;IF(M7>500000000;(50000000*5%)+(200000000*15%)+(250000000*25%)+(M7-50000000-200000000-250000000)*30%;0)))))/12
Rumus tersebut akan otomatis memisahkan dan menjumlahkan PPh 21 sesuai dengan tingkat PKP dan tarifnya.
Baca Juga : Menghitung PPh 21, Cara Menghitung PPh 21 Dalam Microsoft Excel
Untuk lebih jelasnya tentang penghitungan PPh21 ini silahkan pelajari dalam artikel yang telah dibahas sebelumnya.
- Kolom Nett
Kolom ini merupakan gaji bersih yang akan dibayarkan kepada masing - masing karyawan setelah dipotong Pajak.
Jadi, untuk pengisian tabel gaji karyawan ini cukup dengan mengisi tabel Karyawan kemudian mengisi tabel absensi dan terakhir mengisi kolom ID dalam tabel Gaji dan otomatis seluruh unsur gaji akan muncul.
Jika membutuhkan contoh filenya silahkan dapat diambil melalui link berikut ini :
-------------------------------------
Nama File : Contoh gaji dengan excel
Ukuran file : 24 kb
Link : Contoh file
-------------------------------------
Itulah pembahasan kita kali ini tentang cara menghitung gaji karyawan dengan tambahan uang makan dalam Excel, semoga artikel ini bermanfaat untuk semua pembaca.
Posting Komentar untuk "Menghitung Gaji Karyawan Dalam Excel Dengan Uang Makan Berdasarkan Absensi "