10 Rumus Excel untuk Membuat 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 lengkap dengan contoh penerapnnya berikut ini.

 

01: Aplikasi Excel di Akuntansi

A: Manfaat Excel Adalah?

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:

  1. Laporan Laba Rugi (Income Statements)
  2. Laporan Perubahan Modal (Statement of Changes in Equity)
  3. Neraca (Balance Sheet)
  4. Laporan Arus Kas (Cash Flows Statements)
  5. Catatan Atas Laporan Keuangan

Bila senggang dan berkenan, monggo dibaca-baca. Kalau belum sempat, bisa hubungi penulis untuk mengirimkan dalam bentuk file PDF.

Apa saja rumus Excel yang sering digunakan untuk mendukung pekerjaan dan tugas admin gudang?

 

B: Contoh Manfaat Excel  dalam Finance & Accounting

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 template Excel (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:

 

02:  IF Tunggal dan IF Bercabang

cara membuat laporan keuangan dengan excel

A: Pengertian dan Fungsi IF

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. Rumus IF Tunggal

Pengertian 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?

 

Contoh Penggunaan IF Tunggal

Oke sekarang balik lagi ke contoh:

Perhatikan contoh neraca saldo berikut:

aplikasi laporan keuangan excel
Contoh neraca saldo 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. Rumus IF Bercabang / IF Majemuk

Pengertian Rumus IF Bercabang

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
  • Bila logical_test2 terpenuhi, maka gunakan nilai true2
  • Jika logical_test2 tidak terpenuhi, maka gunakan nilai FALSE.

 

Contoh Penggunaan IF Bercabang

Perhatikan contoh berikut:

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

apliksi keuangan excel
Contoh Aplikasi 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: Tabel Aset Tetap Excel

 

03: Rumus SUM

membuat laporan keuangan dengan excel

A: Pengertian dan Fungsi 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

 

B: Contoh Penggunaan SUM

Perhatikan contoh penggunaan SUM:

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

laporan keuangan excel gratis
Neraca saldo Excel

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.

Baca juga artikel terkait tentang 7 langkah menyusun financial statements.

 

04: SUMIF

A: Pengertian Rumus 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

 

B: Contoh Penggunaan Rumus SUMIF

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
Persediaan Barang 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.

 

5: Rumus COUNTA

cara membuat laporan keuangan dengan excel 2007

A: Pengertian Rumus 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

 

B: Contoh Pemakaian Rumus  COUNTA

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.

 

6: SLN dan DB

cara membuat laporan keuangan di excel 2007

A: Pengertian  dan Fungsi Rumus 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

B: Pengertian dan Fungsi Rumus DB

Pengertian rumus 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

 

C: Contoh Penggunakan Rumus SLN dan DB

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.
  • Bila 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 SOP Keuangandan Accounting Tools Excel

 

7: VLOOKUP

membuat laporan keuangan dengan excel 2010

A: Pengertian Rumus 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.

 

B: Contoh Pemakaian Rumus VLOOKUP

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.

Bagaimana penerapan rumus Excel untuk menghitung nilai penyusutan aset tetap? Baca Metode Jumlah Angka Tahun.

 

8: OFFSET

membuat laporan keuangan dengan excel 2007

A: Pengertian Rumus 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

 

B: Penggunaan Rumus OFFSET

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.

 

09: Rumus INDEX

laporan keuangan perusahaan manufaktur format excel

A: Pengertian Rumus INDEX Adalah?

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.

 

B: Contoh Pemakaian Rumus INDEX

Perhatikan contoh penggunaan rumus excel INDEX:

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

laporan keuangan menggunakan excel
Neraca 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 G13 dengan data pada kolom nama akun lalu mengambil data kode akun dari tabel data akun.

 

10: Rumus ISBLANK

laporan keuangan masjid excel

A: Pengertian Rumus ISBLANK Adalah?

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

 

B: Contoh Penggunaan Rumus ISBLANK

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
Penggunaan Rumus 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.

 

11: LEFT dan RIGHT

laporan keuangan rumah tangga excel

A: Pengertian Rumus Excel LEFT dan 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.

 

B: Penggunaan Rumus Excel Right dan Left

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.

 

12: Kesimpulan tentang Rumus Excel untuk Membuat Laporan Keuangan

Laporan keuangan atau financial statements bisa dibuat dengan berbagai cara dan alat, contohnya dengan Excel atau spreadsheet, baik Laporan Laba Rugi, Laporan Perubahan Ekuitas, Laporan Posisi Keuangan, dan Laporan Cash Flow.

Apapun alat yang digunakan, hal paling penting yang harus dikuasai adalah memahami prinsip dan proses penyusunannya, ataupun siklus akuntansinya. Jika ini sudah Anda ketahui dan pahami dengan benar, maka, apapun alat yang akan digunakan tak masalah.

Namun, jika Anda masih merasa kesulitan untuk melakukan perhitungan nilai penyusutan aktiva tetap dan contoh contoh laporan keuangan perusahaan dagang excel, bisa baca di SOP dan Accounting Tools sederhana powerful.

Demikian sajian renyah, praktis dan aplikatif yang dapat saya sampaikan dari blog manajemen keuangan tentang 10 rumus Excel yang bisa digunakan untuk membantu dalam membuat laporan keuangan usaha Anda. Semoga bermanfaat dan terima kasih.*****

Catatan kaki:
Jika Anda mengutip artikel ini mohon untuk disebutkan link sumbernya ya agar tak ada pihak manapun yang dirugikan. Thanks

Manajemen Keuangan Profil

Profesional lulusan ekonomi yang menekuni ERP (SAP), Accounting Software, Business Analyst dan berbagi pengalaman pekerjaan Finance & Accounting.