Membuat Laporan Keuangan Dengan Excel

Menyusun Laporan Keuangan Sederhana Dengan Microsoft Excel

Sudahkah Menggunakan 10 Rumus Excel ini untuk Membantu Menyusun Laporan Keuangan Usaha Anda?

Laporan keuangan Excel dibuat dengan memanfaatkan fungsi dan formula yang disediakan Microsoft Excel.

Sebuah software yang sudah sangat familiar dan tidak asing lagi bagi kita dan dunia.

Banyak fitur gratis yang bisa digunakan sebagai alat bantu untuk menganalisa transaksi usaha, mencatat, mengolah dan menyajikannya dalam sebuah laporan keuangan yang menarik, dinamis, interaktif serta akurat.

Bagaimana cara membuat laporan keuangan dengan Excel?

Yuk ikuti pembahasan lengkapnya berikut ini.

01. Membuat Laporan Keuangan Excel

Kita sudah tahu, begitu banyak fungsi dan manfaat Excel untuk pengolahan data, tentu bila kita mau dan tahu kegunaan serta cara untuk menggunakan fitur-fitur yang tersedia di MS Excel.

Nah, sekarang kita akan menggunakan rumus-rumus Excel untuk menyusun laporan keuangan.

Tentunya karena rumus-rumus Excel tersebut bisa digunakan untuk membantu menyusun Laporan Keuangan, iya kan 🙂

Bukti lengkapnya ada di artikel Siklus Akuntansi dari A sampai Z kami menggunakan rumus-rumus Excel untuk menyusun Laporan Keuangan.

Mulai dari transaksi, jurnal umum & jurnal khusus, buku besar, neraca saldo, neraca lajur sampai Laporan Keuangan Lengkap:

Laporan Laba Rugi,
Laporan Perubahan Modal,
Neraca dan
Laporan Arus Kas.

Bila senggang dan berkenan, monggo dibaca-baca….

Kalau belum sempat, bisa hubungi penulis untuk mengirimkan dalam bentuk file PDF.

Contoh lain adalah seperti pada gambar paling awal artikel ini.

Grafik-grafik itu adalah sebuah dashboard atau dynamic chart dengan Excel.

Cukup Powerful dan menarik!

Dashboard Excel tersebut saya buat dengan memanfaatkan fitur-fitur yang ada di Microsoft Excel 2016

Cara untuk membuatnya pun relatif mudah, dan bisa di-custom sesuai keinginan kita. Asik kan?

Berbagai dashboard yang lengkap, dinamis dan interaktif bisa kita buat.

Misalnya sales report dashboard untuk menganlisa laporan penjualan tahun 2012 sampai 2017 dengan beberapa kategori, seperti lokasi warehouse dan produk.

Bagaimana cara membuat dashboar Excel tersebut?

Kita bisa menggunakan fitur Excel berikut:

Fitur pivot table
Power pivot yang dikombinasikan dengan
Fitur slicer.

Tiga fitur itu sudah cukup bagus untuk mengelola dan menampilkan data-data dengan lebih menarik dan akurat.

Demikian juga bila kita ingin membuat custom yang lain, misalnya ingin menampilkan data-data lain yang kita butuhkan. Kita tinggal mengatur tombol-tombol sesuai keinginan kita.

Sangat membantu kan?

Dan bila anda ingin melihat form atau template laporan keuangan perusahaan dagang dengan menggunakan Excel, bisa download gratis di sini (tempat download file-file dan ebook bermanfaat gratis).

Kembali ke pokok pembahasan…

Sekarang, kita akan menunjukkan 10 rumus Excel yang populer, sederhana, mudah dan sering digunakan dalam proses membuat laporan keuangan dengan excel.

Dan berikut ini 10 rumus Excel yang biasa digunakan sebagai alat bantu dalam menyusun Laporan Keuangan perusahaan dagang:

Rumus Excel #1: IF Tunggal dan IF Bercabang

Fungsi IF adalah fungsi logika yang paling banyak digunakan, terutama berkaitan dengan pengolahan banyak data dan keputusan bercabang.

Ada dua jenis fungsi IF, yaitu : IF Tunggal dan IF bercabang.

01. IF Tunggal

IF Tunggal adalah fungsi dasar yang kegunaannya sederhana, yaitu untuk memeriksa nilai sebuah logika kemudian mengambil tindakan jika benar dan tindakan lain jika salah.

Penulisan fungsi IF Tunggal adalah sebagai berikut :

=IF(logical_test;[value_if_true];[value_if_false])

logical_test : kriteria acuan yang dijadikan pembanding
value_if_true : nilai untuk kondisi yang terpenuhi
value_if_false : nilai untuk kondisi yang tidak terpenuhi

Perhatikan contoh penggunaan rumus IF berikut ini:

Pada contoh ini, rumus IF tunggal digunakan untuk memeriksa nillai debit dan kredit di Neraca Saldo.

Masih ingat pengertian Debit dan Kredit kan?

Oke sedikit me-refresh,

Debit adalah jumlah yang dimasukkan ke sisi kiri suatu akun.

Kredit adalah jumlah yang dimasukkan ke sisi kanan suatu akun.

Sudah ingat kan?

Oke sekarang balik lagi ke contoh:

Perhatikan contoh neraca saldo berikut:

Laporan neraca saldo adalah sebuah laporan yang menyajikan daftar akun-akun beserta saldonya.

Total saldo di sisi debit harus sama dengan total saldo di sisi kredit.

Dan untuk memeriksan dan meyakinkan bahwa saldo tersebut sudah benar-benar sama, maka cara sederhana dan mudahnya adalah menggunakan rumus Excel IF tunggal.

Pada gambar di atas, saya sengaja meletakkan hasil pemeriksaan itu di pojok kanan atas dan saya beri warna MERAH.

Tujuannya agar mudah dan langsung bisa dilihat.

Dan begini cara penulisan rumus IF-nya:

=IF(D51=E51;”Seimbang”;”Tidak seimbang”)

Keterangan rumus:

Jika nilai sel D51 sama dengan nilai sel E51 maka hasilnya seimbang,
Jika hasilbya tidak sama, maka tidak seimbang.

02. IF Bercabang / IF Majemuk

Pengertian rumus Excel IF Bercabang adalah rumus untuk memeriksa nilai sebuah logika yang jumlahnya lebih dari satu.

Fungsi IF tunggal tidak bisa digunakan bila logika yang diperiksa lebih dari satu.

Oleh karena itu maka digunakan fungsi IF bercabang.

Cara penulisan rumusnya hampir sama dengan IF Tunggal.

Tapi karena kriteria acuan yang dijadikan pembanding lebih dari satu maka untuk nilai FALSE digunakan kembali fungsi IF berikutnya.

Sehingga penulisan rumusnya menjadi :

=IF(logical_test1;[value_if_true1];(IF(logical_test2; [value_if_true2];[value_if_false]))

Lihat logical_test1, jika terpenuhi maka gunakan nilai true1
Jika logical_test1 tidak terpenuhi, maka lihat logical_test2
Jika logical_test2 terpenuhi, maka gunakan nilai true2
Jika logical_test2 tidak terpenuhi, maka gunakan nilai FALSE.

Perhatikan contoh berikut:

Berikut ini contoh penggunaan fungsi IF Majemuk untuk menghitung nilai penyusutan aktiva tetap :

Penulisan rumus Excel-nya seperti ini :

=IF(ISBLANK($E$10);” “;IF(B25<$B$25+$F$14;B25+1;” “))

Keterangan rumus:

Jika sel E10 bernilai kosong maka hasilnya kosong
Jika nilai sel B25 lebih kecil dari hasil penjumlahan sel B25 ditambah sel F14 maka tambahkan nilai sel B25 dengan 1
Selain itu hasilnya kosong.

Lebih detail mengenai rumus excel untuk menghitung penyusutan silahkan baca di: 2 Rumus Excel ini untuk Menghitung Nilai Penyusutan Aktiva Tetap?

Rumus Excel #2. SUM

Rumus ini paling banyak diketahui dan digunakan oleh sebagian pengguna Excel.

Pengertian Rumus SUM adalah rumus excel yang digunakan untuk menjumlahkan data dari beberapa sel.

Penulisannya adalah sebagai berikut :

=SUM(number1;[number2]:..)

number1, adalah sel awal
number2, dan seterusnya

Perhatikan contoh penggunaan SUM:

Berikut ini contoh penggunaan rumus SUM untuk menghitung nilai total debit neraca saldo di bawah ini :

Pada contoh di atas, kita akan menjumlah total debit.

Caranya, letakkan kursor di sel total (tanda merah), selanjutnya tuliskan rumus SUM.

Penulisan rumus lengkapnya seperti ini :

=SUM(D13:D50)

Keterangan rumus :

Total nilai kolom D13 sampai D50.

Rumus Excel #3. SUMIF

Pengertian rumus Excel SUMIF adalah rumus excel yang digunakan untuk menjumlahkan data pada range tertentu sesuai dengan kriteria yang diinginkan.

Penulisan rumusnya adalah sebagai berikut:

=SUMIF(range;criteria;[sum_range])

range : range kriteri
criteria : kriteria yang menjadi acuan
sum_range : range jumlah dari kriteria

Perhatikan contoh penggunaan rumus SUMIF:

Berikut ini contoh penggunaan rumus Excel SUMIF untuk menghitung nilai saldo akhir persediaan persediaan barang dagang :

Pada contoh di atas, rumus Excel SUM IF digunakan untuk menghitung nilai persediaan akhir.

Caranya dengan meletakkan kursus di sel saldo akhir (perhatikan panah merah), kemudian menuliskan rumus lengkapnya sebagai berikut :

=IF(C11=””;””;H11+SUMIF(D18:D70;C11;total_harga))

Keterangan rumus :

Jika sel C11 bernilai kosong maka hasilnya kosong.
Jika tidak, jumlahkan saldo awal produk dengan total transaksi produk pada kolom total harga.

Rumus Excel #4. COUNTA

Pengertian rumus Excel COUNTA adalah rumus excel yang digunakan untuk menghitung jumlah kolom dan baris yang tidak blank.

Penulisan rumus ini adalah :

=COUNTA(value1;[value2]; …)

value1 : nilai pada kolom/baris pertama
value2 : nilai pada kolom/baris kedua dan setereusnya

Perhatikan contoh penggunaannya berikut ini:

Pada contoh berikut ini, rumus excel COUNTA digunakan untuk untuk membuat buku hutang piutang perusahaan dagang.

Berikut ini penerapannya :

Penulisan rumus lengkapnya adalah :

=IF(ROW()>COUNTA(ListCustomer);OFFSET(ListSupplier;ROW()-1-COUNTA(ListCustomer);0;1;1);OFFSET(ListCustomer;ROW()-1;0;1;1))

Keterangan rumus :

Rumus excel di atas akan mengambil data kode customer pada range ListCustomer dan kode supplier pada range ListSupplier.

Rumus Excel #5. SLN dan DB

Fungsi SLN

Pengertian rumus excel SLN adalah rumus excel yang digunakan untuk menghitung nilai penyusutan aktiva tetap dengan menggunakan metode garis lurus (straight line method).

Penulisan rumusnya adalah seperti berikut :

=SLN(cost;salvage;life)

cost : harga perolehan barang
salvage : taksiran nilai sisa (residu)
life : taksiran umur ekonomis

Fungsi DB

PengertianrRumus excel DB adalah rumus excel yang digunakan untuk menghitung nilai penyusutan aktiva tetap dengan menggunakan metode saldo menurun (declining balance method).

Penulisan rumusnya seperti ini :

=DB(cost;salvage;life;periode;[month])

cost : harga perolehan barang
salvage : taksiran nilai sisa (residu)
life : taksiran umur ekonomis
periode : periode penyusutan
month : bulan ke berapa dalam periode penyusutan

Perhatikan contoh penggunaan dua rumus excel tersebut berikut ini:

Pada contoh ini, dua rumus excel tersebut digunakan untuk menghitung nilai penyusutan aktiva tetap perusahaan dagang yang dibundle sebagai aplikasi Excel:

Berikut penulisan rumus lengkapnya :

=IF(OR($D$20=0;B25=” “);” “;IF($D$20=”Garis lurus”;SLN($C$16;$F$16;$F$14)/12*C25;IF($D$20=”Saldo menurun”;DB($C$16;$F$16;$F$14;COUNT($B$25:B25);C25))))

Keterangan rumus :

Jika sel D20 bernilai nol atau sel B25 bernilai kosong, maka hasilnya kosong.
Jika sel D20 bernilai garis lurus maka hitung penyusutan aset dengan metode garis lurus, hasilnya dibagi 12 bulan kemudian dikali dengan jumlah bulan di sel C25.
Jika sel D20 bernilai saldo menurun, maka hitung penyusutan aset dengan metode saldo menurun.

Aplikasi Excel untuk menghitung nilai penyusutan aktiva tetap dapat diperoleh di Accounting Tools+SOP

Rumus Excel #6. VLOOKUP

Pengertian rumus Excel VLOOKUP adalah fungsi Excel yang digunakan untuk mengambil data dari tabel lain.

Seringkali kita membutuhkan data dari tabel lain sebagai referensi atau acuan untuk membantu menyelesaikan pekerjaan.

Penulisan rumus VLOOKUP adalah :

=VLOOKUP(lookup_value;table_array;col_index_number;[range_lookup])

lookup_value : nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai ini harus ada baik di tabel yang akan disi dan tabel referensi.
table_array : range dari tabel referensi
col_index_number : nomor urut kolom tabel referensi yang berisikan data yang akan diambil. Dimulai dari kolom paling kiri pada tabel referensi dengan nomor index 1 (satu) dan seterusnya.
range_lookup : range jenis tipe data yang dicari. Jenis tipe data ini ada 2 (dua) yaitu True dan False.

Perhatikan contoh penggunaan berikut ini:

Pada contoh ini, rumus VLOOKUP digunakan untuk mengambil nilai saldo awal persediaan barang dagang dari tabel lain.

Perhatikan gambar di atas:

Pada tanda kotak merah, menunjukkan penggunaan rumus VLOOKUP untuk menghitung saldo awal persediaan barang dagang.

Saldo ini diambil dari tabel lain, pada contoh penggunaan di atas adalah dari tabel produk.

Ini penulisan rumus lengkapnya:

=IF(C11=””;””;VLOOKUP(C11;produk;6;0))

Keterangan rumus :

Jika sel C11 bernilai kosong maka hasilnya kosong.
Jika tidak maka isi dengan data saldo awal produk.

Rumus Excel #7. OFFSET

Pengertian rumus Excel OFFSET adalah formula Excel yang digunakan untuk mengambil data pada suatu range berdasarkan pada sel awal, dengan menentukan jarak kolom dan baris pada posisi yang akan diambil.

Penulisan rumusnya seperti ini :

=OFFSET(reference;rows;cols;[height];[width])

reference : sel yang dijadikan sebagai acuan awal
rows : pergeseran baris dari acuan reference
cols : pergeseran kolom dari acuan reference
height : rentang baris data yang akan diambil
width : rentang kolom data yang akan diambil

Perhatikan contoh penggunaan rumus Excel OFFSET:

Berikut ini contoh penggunaan rumus OFFSET untuk menyusun tabel hutang piutang perusahaan dagang.

Berikut penulisan rumus lengkapnya :

=IF(ROW()>COUNTA(ListCustomer);OFFSET(ListSupplier;ROW()-1-COUNTA(ListCustomer);0;1;1);OFFSET(ListCustomer;ROW()-1;0;1;1))

Keterangan rumus :

Seperti pada penggunaan fungsi COUNTA, yaitu Rumus excel di atas akan mengambil data kode customer pada range ListCustomer dan kode supplier pada range ListSupplier.

Rumus Excel #8. INDEX

Pengertian rumus Excel INDEX adalah formula Excel yang digunakan untuk menentukan nilai berdasarkan letak baris dan kolom dari rentang data yang ada.

Penulisan rumus ini adalah :

01. Penulisan Format Array :

=INDEX(array, row_num, [column_num])

Array : range pada tabel nilai yang akan dicari.
row_num : nomor baris
column_num : nomor kolom

02. Penulisan Format Reference :

=INDEX(reference, row_num, [column_num], [area_num])

Reference : nilai dari suatu range.

Perhatikan contoh penggunaan rumus excel INDEX:

Berikut ini contoh penggunaan rumus excel INDEX untuk menyusun Laporan Neraca Keuangan perusahaan dagang.

Ini bentuk penulisan rumus lengkapnya:

=INDEX(kode_akun;MATCH(G13;nama_akun;0))

Keterangan rumus :

Rumus di atas akan membandingkan nama akan pada sel D13 dengan data pada kolom nama akun lalu mengambil data kode akun dari tabel data akun.

Rumus Excel #9. ISBLANK

Pengertian rumus Excel ISBLANK adalah formula Excel yang digunakan untuk mengetahui atau memeriksa data kosong dalam suatu sel.

Penulisan rumusnya adalah sebagai berikut :

=ISBLANK(value)

Value : nilai atau sel yang diperiksa

Perhatikan contoh penggunaan rumus Excel ISBLANK berikut ini:

Pada contoh ini, rumus Excel ISBLANK digunakan untuk memeriksa nilai kode akun dalam tabel jurnal umum perusahaan dagang.

Penulisan rumus lengkapnya adalah :

=IF(ISBLANK(H9);” “;VLOOKUP(H9;akun;2;0))

Keterangan rumus :

Jika sel H9 kosong maka hasilnya adalah kosong, jika maka ambil data nama akun dari tabel data akun sesuai dengan kode akun yang dipilih di sel H9.

Rumus Excel #10. LEFT & RIGHT

Pengertian rumus Excel LEFT & RIGHT adalah fungsi Excel yang digunakan untuk mengambil beberapa karakter dari suatu teks.

Pengertian rumus excel LEFT adalah fungsi excel yang digunakan untuk mengambil beberapa karakter dari arah KIRI.

Sedangkan rumus excel RIGHT adalah fungsi Excel yang digunakan untuk mengambil sejumlah karakter dari arah KANAN.

Perhatikan contoh penggunaan rumus Excel RIGHT dan LEFT berikut ini:

Pada contoh ini rumus Excel LEFT dan RIGHT digunakan untuk menyusun tabel harga produk dan harga pokok penjualan.

Penulisan lengkap rumusnya adalah :

=IF(ISBLANK(C10);” “;COUNTA(C$10:C10))&” -“&LEFT(D10;3)& RIGHT(D10;3)

Keterangan rumus :

Jika sel C10 bernilai kosong maka hasilnya kosong.
Jika tidak maka buat kode produk dengan menggabungkan jumlah sel yang berisi nilai pada kolom C ditambah karakter “-“ kemudian 3 karakter sebelah kiri sel D10 dan 3 karakter sebelah kanan sel D10.

02. Kesimpulan

Demikian sajian renyah, praktis dan aplikatif dari blog manajemen keuangan tentang 10 rumus Excel yang bisa digunakan untuk membantu dalam membuat laporan keuangan usaha Anda.

Untuk template perhitungan nilai penyusutan aktiva tetap dan contoh contoh laporan keuangan perusahaan dagang excel, bisa dibaca di SOP dan Accounting Tools sederhana powerful.

Demikian yang dapat saya sampaikan. Semoga bermanfaat.

Terima kasih

***