TUGAS KHUSUS SPREAD SHEET 1 HDSN LATIH-‐1_NIM
LATIHAN: 1 Petunjuk: a. Kerjakan latihan ini dengan nama file:LATIH_1_NIM b. Setelah selesai kirimkan ke alamat email:
[email protected] paling lambat hari ini jam 00.00 WIB; di luar tanggal dan jam tersebut, maka tidak ada nilai latih-‐1 c. Mahasiswa yang melakukan copy file dari teman, akan ketahuan, karena segala author akan terlacak, jika diketahui copy file, maka nilai langsung dapat E. Diketahui salah satu perusahaan swasta memiliki data-‐data penggajian sebagai berikut: 1. a. Tabel Gaji Pokok (untuk Hlookup) Golongan
1
2
3
4
Gaji Pokok
1.568.769
2.599.768
3.879.999
5.768.988
2. 1.b. Table Gaji Pokok (untuk Vlookup) Golongan Gaji Pokok 1
1.568.769
2
2.599.768
3
3.879.999
4
5.768.988
3. Tabel Insentive dari Gaji Pokok (Lookup Vertical) Golongan Insentive 1
10%
2
15%
3
25%
4
50% 4.
Golongan
1
2
3
4
Insentive
10%
15%
25%
50%
TUGAS KHUSUS SPREAD SHEET 2 HDSN LATIH-‐1_NIM
5. Tunjangan Anak: Anak maksimal sebanyak 5 anak yg diberikan tunjangan, tunjangan anak 10% per anak dari gaji pokoknya. 6. Tunjangan beras 10 kg per kepala, maksimal anak 5 kepala, harga beras per kg Rp.9.000,-‐ Misal: Karyawan status D/J dengan anak 8, maka dihitung tunjangan beras 6 kepala, tetapi status K tunjangan beras 7 kepala. 5. Tunjangan transportasi berdasarkan golongan, yaitu gol 1 dan 3 sebesar Rp.50.000 per hari, sedangkan golongan 2 dan 4 sebesar Rp.90.000 menurut jumlah kehadiran per bulan. 6. Tunjangan perumahan ditentukan berdasarkan pengalaman kerja sebagai berikut: Lama kerja 0 s.d 5 tahun 10% dari gapok, lama kerja di atas 5 th s.d. 10 tahun 20%; Lama kerja di atas 10 s.d. 20 th 30%; dan lama kerja di atas 20 th sebesar 50% dari gapoknya. 7. Uang makan ditentukan Rp.45.000 per hari, menurut jumlah kehadiran karyawan 8. Bonus diberikan sebagai berikut: Kehadiran per bulan 0-‐15 hari bonus tidak dapat; di atas 15 hari s.d. 20 hari bonus 25%; di atas 20 hari bonus 50% dari Insentive. 9. THR yang terdiri dari Fitri, Natal dan Ngaben dan lain-‐lain berlaku seperti tarif prosentase Insentive tetapi dihitung dari jumlah bonus yang diterima. (Lihat tabel Insentive) 10. Tunjangan Pendidikan: Golongan/Ruang
A
B
C
D
E
1
1.000.000
1.200.000
1.300.000
1.400.000
1.500.000
2
2.000.000
2.300.000
2.500.000
2.700.000
2.900.000
3
3.000.000
3.200.000
3.300.000
3.400.000
3.500.000
4
4.000.000
4.300.000
4.500.000
4.700.000
4.900.000
11.
TUGAS KHUSUS SPREAD SHEET 3 HDSN LATIH-‐1_NIM
11. Tunjangan Lauk Pauk 1
2
3
4
A
400.000
500.000
600.000
700.000
B
450.000
550.000
650.000
750.000
C
460.000
560.000
660.000
760.000
D
490.000
590.000
690.000
790.000
E
800,000
820,000
850,000
900,000
Ruang/Golongan
12. 12. Iuran Koperasi ditentukan berdasarkan golongan, golongan 1 dan 2 Rp.15.000; golongan 3 dan 4 Rp.25.000 per bulan. Daftar Gaji Bulan Maret 2015 PT BARBARA MEGA
NO Nama
Status
Gol Anak Hadir
TH Mulai Kerja
1
Dida
K
4A
4
12
2001
2
Andi
D
3B
5
23
1978
3
Ruli
J
3C
2
24
2000
4
Amin
B
2E
0
21
1990
5
Rini
K
2D
4
20
1997
6
Ana
K
1E
3
17
1998
7
Sita
K
4E
2
19
2006
8
Akbar
D
4A
8
12
2001
9
Basu
D
3B
6
23
1978
10
Iman
K
3C
2
24
2000
11
Andri
K
2E
2
21
1990
12
Griya
K
2D
3
20
1997
13
Dida
J
1E
6
17
1998
14
Nia
B
4E
0
19
2006
15
Sitah
B
2B
0
22
2000
Gaji Pokok Insentive Tun. (if)* (IF) Anak
TUGAS KHUSUS SPREAD SHEET 4 HDSN LATIH-‐1_NIM
T Beras Transp
Perum
Uang Makan
Bonus
THR
T.PEN
T. Lauk
Kop
Bersih
Total Setiap Kolom Total Gaji bersih Golongan 4 Total gaji bersih Ruang A Total gaji bersih untuk yg khdiran di atas 20 hari Total gaji bersih untuk karyawan Janda Total gaji bersih untuk karyawan Duda Jumlah karyawan yang belum kawin * Gaji Pokok/Insentive dicari dengan formula: a. IF b. Vlookup c. Hlookup Diminta: 1.
Gaji Pokok dengan formula IF ; =if(left(gol=“1”);….dst; Displit jadi 2 kolom (Hlookup dan Vlookup)
2.
Gaji Pokok dengan HLOOKUP ; =hlookup(sel uji;table array,brs ke)
3.
Gaji POKOK dengan VLOOKUP; =vlookup(seluji,tabel array, klm ke)
4.
Insentive dengan IF =if(left(gol;1)=“1”;10/100;if(left(sel gol;1)=“3”;25/100;50/100)))*sel gapok
5.
Insentive dengan HLOOKUP =hlookup(left(gol;1);$a$1:$e$2;2)*sel gapok ybs
6.
Insentive dengan VLOOKUP =vlookup(value(left(gol;1));$b$14:$c$17;2)* sel gapok
7.
Tunjangan anak = if(sel jumlah anak=>5,50/100;sel jumlah anak*10/100)*sel gaji pokok
8.
Tunjangan Beras =if(or(or(sel status=“d”;sel status=“j”; sel status=“b”));1+sel jumlah anak;2+sel jumlah anak)*10
9.
Tunjangan transportasi =if(or(left(sel gol;1)=“1”;left(sel gol;1)=“3”);50000;90000)*sel jumlah hari hadir per bulan
gol;1)=“2”;15/100;if(left(sel
10. Tunjangan Perumahan =if(thn ini -‐ sel tahun msk kerja>20,50/100;if(thn ini -‐ sel thn msk kerja>10;30/100;if(thn ini -‐ sel thn masuk kerja>=5;20/100;10/100)))*sel gaji pokoknya a. =if(2015-‐b18>20;50/100;if(2015-‐b18>10;30/100;if(2015-‐b18>=5;20/100;10/100)))*g18
TUGAS KHUSUS SPREAD SHEET 5 HDSN LATIH-‐1_NIM b. =IF(AND(……
11. Uang Makan =45000*sel kehadiran 12. Bonus =IF(AND(sel kehadiran kehadiran<=20);25/100;50/100))*gp
>=0;kehadiran<=15);0;if(and(sel
kehadiran>15;sel
13. THR =hlookuP(…. atau Vlookup(….. atau IF 14. Tunjangan Pendidikan: a. Dengan rumus Vlookup b. Dengan Rumus Hlookup c. Dengan Index/Match: ‘1 ‘2 ‘3 ‘4 Asumsi tabel pada posisi sebagai berikut:
=INDEX($A$19:$F$23;MATCH(VALUE(LEFT(SEL GOL;1);$A$19:F19;0))
GOL;1));$A$19:$F$23;0),MATCH(RIGHT(SEL
VLOOKUP
=if(right(sel gol;1)=“A”;vlookup(left(gol;1);$a$20:$b$23;2);if(right(sel gol;1)=“B”;vlookup(left(gol;1);$a$20:$c$23;3);if(right(sel
TUGAS KHUSUS SPREAD SHEET 6 HDSN LATIH-‐1_NIM
gol;1)=“C”;vlookup(left(gol;1);$a$20:$d$23;4);if(right(sel gol;1)=“D”;vlookup(left(gol;1);$a$20:$e$23;5); vlookup(left(gol;1);$a$20:$F$23;6)))) Hlookup =IF(LEFT(gol;1)=“1”;HLOOKUP(RIGHT(GOL;1);$b$19:$F$20;2); =IF(LEFT(gol;1)=“2”;HLOOKUP(RIGHT(GOL;1);$b$19:$F$21;3); =IF(LEFT(gol;1)=“3”;HLOOKUP(RIGHT(GOL;1);$b$19:$F$22;4); HLOOKUP(RIGHT(GOL;1);$b$19:$F$23;5))))
SELAMAT BEKERJA
