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

membuat laporan keuangan dengan 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

cara membuat laporan keuangan dengan excel

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:

membuat aplikasi laporan keuangan dengan excel

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 :

membuat laporan keuangan perusahaan jasa dengan excel

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

cara membuat laporan keuangan perusahaan jasa dengan excel

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 :

laporan keuangan excel gratis

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

aplikasi laporan keuangan sederhana excel

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 :

laporan keuangan bumdes excel

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

cara membuat laporan keuangan dengan excel 2007

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 :

cara membuat laporan keuangan dengan excel 2010

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

cara membuat laporan keuangan di excel 2007

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:

cara membuat laporan keuangan di excel 2010

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

membuat laporan keuangan dengan excel 2010

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.

membuat laporan keuangan sederhana dengan excel 2007

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

membuat laporan keuangan dengan excel 2007

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.

laporan keuangan pribadi excel

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

laporan keuangan perusahaan manufaktur format excel

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.

laporan keuangan menggunakan excel

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

laporan keuangan masjid excel

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.

template laporan keuangan bulanan excel

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

laporan keuangan rumah tangga excel

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.

contoh laporan keuangan perusahaan dagang lengkap excel

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 dan contoh contoh laporan keuangan perusahaan dagang excel, bisa di download pada link yang sudah disediakan.

Demikian yang dapat saya sampaikan. Semoga bermanfaat.

Terima kasih

***

manajemen keuangan dan SOP

sop akuntansi keuangan powerful