coretan tentang autocad dan excel

Referensi	:	Perhitungan PPH 21 dengan Excel
Platform	:	Microsoft Excel 2003 dan 2007
Lokasi File	:

 

Kadang kita jumpai pesan error Circular Reference di excel yang biasanya terjadi jika saat membuat rumus atau formula yang di suatu cell, melibatkan cell hasil hitungan itu sendiri….[bingung sendiri aku menerangkan #$??><%%].

Pakai contoh soal aja, jika kita ingin menampilkan hasil hitungan di cell A3 dan menuliskan formula di =A1+A2+A3 maka terjadi circular reference karena dalam rumus tersebut ada referensi A3 yang didapat dari hasil hitungan itu sendiri….[masih bingung?? kalo mau bingung lagi, coba keterangan dalam bahasa inggris di Dealing with Circular References]

Tulisan ini akan membahas bagaimana kita memanfaatkan circular reference tersebut menjadi sebuah fungsi iterasi untuk mendapatkan nilai gross up pph 21.

Jika PKP:Penghasilan Kena Pajak dan PTKP:Penghasilan Tidak Kena Pajak, maka

PPH21=(PKPgross-PTKP)*RumusPPH21

PKPgross=PKP+PPH21

PKPgross=PKP+(PKPgross-PTKP)*RumusPPH21

di rumus terakhir inilah terjadi circular reference, perhatikan variable PKPgross ada di sebelah kanan dan kiri tanda sama dengan (=).

Contoh kasus gross up PPH21

Konsultan mendapatkan pekerjaan Man Power Supply selama satu tahun.

Untuk keperluan pekerjaan tersebut telah dipilih 3 orang dengan standard gaji per bulan yang berbeda dan mempunyai tanggungan keluarga (variable PTKP) yang berbeda pula.

Selain gaji per bulan, setiap tahun mereka juga mendapatkan THR sebesar satu bulan gaji dan bonus sebesar 50% dari gaji.

Perusahaan memutuskan untuk memberikan subsidi PPH21 kepada 3 orang tersebut, sehingga pendapatan mereka selama setahun tidak terpotong PPH21.

Hitungan gaji di excel disusun seperti berikut:

F2=SUM(C2*12,D2:E2), H2=G2-B2, J2=G2-I2

I2=IF(H2>500000000,30%*(H2-500000000)+95000000,IF(H2>250000000,25%*(H2-250000000)+32500000,IF(H2>50000000,15%*(H2-50000000)+2500000,H2*5%)))

K2=J2-F2

Jika K2 bernilai 0 (nol), maka hasil gross up sudah betul, yaitu PKPgross – PPH21 akan sama dengan pendapatan bersih selama setahun.

sebelum melakukan hitungan PKPgross (Kolom G), lakukan setting iterasi di excel sesuai dengan versi excel yang Anda Pakai.

Excel 2003

dari menu [Tool], pilih [Options]. Pada Tab [Caclulation], check “Iteration”

Excel 2007

click icon Office button kemudian pilih [Excel Options]. Pada tab [Formulas], check “Enable Iterative Calculation.

 

Mulai Iterasi

Pada Kolom G di cell G2, ketikkan

=F2+I2

PKPgross=PKPnett+PPH21

Copy formula ke bawah sehingga tabel menjadi :

Perhatikan Kolom J dan F, hasilnya harus sama.

Coba ganti gaji Xi A Lan menjadi Rp 9000000, maka excel secara otomatis menghitung kembali PKPgrossnya.