excel.my.id
Contoh kasus 4 menit baca

Rencana Anggaran Perjalanan Excel: Cara dan Rumusnya

Cara membuat rencana anggaran perjalanan di Excel atau Google Sheets: kolom, rumus total, dana cadangan, persentase per kategori, dan tanda melebihi anggaran.

Hadie Danker Terbit Diperbarui
Daftar isi

Rencana anggaran perjalanan di Excel cukup berisi enam kolom: kategori, rincian, kuantitas, harga satuan, total, dan persentase. Total tiap baris dihitung kuantitas × harga, lalu semuanya dijumlahkan dengan SUM dan dibandingkan dengan anggaran. Hasilnya langsung menunjukkan sisa uang dan pos mana yang paling besar.

Contoh kasus

Dua orang naik kereta dari Jakarta ke Yogyakarta selama empat hari tiga malam. Anggaran yang tersedia Rp5.500.000. Semua harga di bawah adalah asumsi untuk contoh. Ganti dengan harga yang Anda temukan sendiri.

Susunan sheet

Sel B1 berisi anggaran tersedia: 5.500.000. Tabel mulai di baris 3.

Kolom 1A (Kategori)B (Rincian)C (Qty)D (Harga satuan)E (Total)F (% dari total)
3TransportasiTiket kereta pergi-pulang, 2 orang4350.0001.400.00028,5%
4PenginapanHotel per malam3450.0001.350.00027,5%
5MakanMakan 2 orang per hari4180.000720.00014,6%
6Transportasi lokalSewa motor atau ojek per hari3100.000300.0006,1%
7Tiket wisataTiket masuk, 2 orang2150.000300.0006,1%
8Oleh-olehPaket oleh-oleh1400.000400.0008,1%
9Dana cadangan10% dari subtotal447.0009,1%
10Total rencana4.917.000100%

Qty untuk tiket kereta adalah 4 karena 2 orang × 2 arah. Qty penginapan 3 karena tiga malam.

Rumus yang dipakai

SelRumusHasilFungsinya
E3=IF(D3="","",IF(C3="",1,C3)*D3)1.400.000Total baris. Jika Qty kosong dianggap 1
E9=SUM(E3:E8)*10%447.000Dana cadangan 10% dari subtotal (4.470.000)
E10=SUM(E3:E9)4.917.000Total rencana
F3=E3/$E$1028,5%Porsi baris dari total. Tanda $ mengunci E10 saat rumus disalin ke bawah
E12=B1-E10583.000Sisa anggaran
E13=IF(E10>B1,"Melebihi anggaran","Dalam anggaran")Dalam anggaranPeringatan
E14=E10/B189,4%Persentase anggaran terpakai
E15=E10/22.458.500Biaya per orang (2 orang)
E16=E10/41.229.250Biaya per hari (4 hari)

Format kolom D, E, dan sel hasil sebagai angka dengan pemisah ribuan atau Rupiah lewat Ctrl+1. Ketik angkanya saja tanpa “Rp”, supaya rumus tidak berubah menjadi #VALUE! (penyebab dan solusi error). Format kolom F sebagai persentase (Ctrl+Shift+5).

Kolom Qty boleh dikosongkan untuk biaya sekali bayar. Rumus IF(C3="",1,C3) di E3 menganggapnya 1 sehingga totalnya tidak menjadi 0. Fungsi yang dipakai: IF dan SUM. Operator > dan ="" dijelaskan di operator perbandingan.

Membuat kategori lebih rapi

Satu kategori sering punya banyak baris, misalnya tiga jenis makan. Totalnya dihitung dengan SUMIF:

=SUMIF($A$3:$A$8,"Makan",$E$3:$E$8) menghasilkan 720.000.

Agar kategori tidak salah ketik (“Makan”, “makan “, “Makan siang”), pasang dropdown list di kolom A. Daftar kategorinya ditaruh di sheet lain.

Rencana vs kenyataan

Tambahkan dua kolom: Realisasi dan Selisih.

KategoriRencanaRealisasiSelisih (Rencana − Realisasi)
Penginapan1.350.0001.500.000−150.000
Makan720.000650.00070.000

Rumus selisih: =E4-G4, dengan G berisi realisasi. Selisih negatif berarti melebihi rencana. Beri warna merah pada angka negatif lewat Conditional Formatting, caranya mirip dengan menandai data duplikat, hanya dengan rumus =H4<0.

Dua baris di atas hanya contoh pengisian realisasi. Angkanya bukan data nyata.

Perjalanan ke luar negeri

Tambahkan sel kurs, misalnya H1 berisi 16.000 untuk 1 dolar AS. Harga dalam dolar diubah ke Rupiah dengan =D3*$H$1. Hotel 80 dolar per malam menjadi 1.280.000 per malam pada kurs contoh itu. Kurs di contoh hanya perumpamaan. Isi dengan kurs yang berlaku saat Anda merencanakan. Tambahkan pos visa, asuransi perjalanan, dan biaya kartu atau penukaran uang.

Grafik

Pilih kolom A dan E (tanpa baris total), lalu Insert > Pie atau Column. Untuk anggaran, diagram batang lebih mudah dibaca daripada lingkaran jika kategorinya banyak. Cara membuat grafik dasar ada di belajar Excel untuk pemula.

Kesalahan yang sering terjadi

  • Qty tiket lupa dikali pergi-pulang. Hitung per orang per arah.
  • Dana cadangan dihitung dari total yang sudah memuat cadangan. Hitung dari subtotal, seperti E9 di atas.
  • Makan dihitung per orang per hari, lalu dikali dua kali. Tentukan satuan sejak awal dan tulis di kolom Rincian.
  • Harga belum termasuk pajak atau biaya layanan. Catat di Rincian apakah harga sudah termasuk atau belum.
  • Sel total menunjuk rentang kurang panjang. Setelah menyisipkan baris di tengah tabel, rumus SUM ikut menyesuaikan. Jika Anda menambahkan baris di bawah, perluas rentangnya.

Pola ini sama untuk anggaran lain: acara, renovasi, atau belanja bulanan. Cukup ganti kategori dan satuannya. Jika ingin menguasai rumus-rumusnya lebih jauh, mulai dari 50 rumus dasar, dan pelajari dasar tabel di pengertian spreadsheet.

Baca juga

Pertanyaan yang sering muncul

Apa saja yang perlu dimasukkan dalam anggaran perjalanan?

Transportasi (tiket pergi dan pulang), penginapan, makan, transportasi lokal, tiket wisata, oleh-oleh, dan dana cadangan. Untuk perjalanan luar negeri tambahkan visa, asuransi, dan selisih kurs.

Berapa dana cadangan yang wajar untuk perjalanan?

Banyak orang memakai kisaran 10 persen dari total rencana, dan contoh di artikel ini memakai 10 persen. Angka itu bukan aturan baku, jadi sesuaikan dengan seberapa pasti harga dan jadwal perjalanan Anda.

Bagaimana menghitung total anggaran perjalanan di Excel?

Hitung total tiap baris dengan kuantitas dikali harga satuan, jumlahkan dengan SUM, lalu tambahkan dana cadangan, misalnya =SUM(E2:E7)*110%. Bandingkan hasilnya dengan anggaran yang tersedia.

Bagaimana menghitung biaya per orang atau per hari?

Bagi total dengan jumlah orang atau jumlah hari, misalnya =E10/2 untuk dua orang dan =E10/4 untuk empat hari. Hasil per orang berguna untuk patungan.

Bagaimana memberi tanda otomatis jika anggaran terlampaui?

Pakai IF yang membandingkan total rencana dengan anggaran tersedia dan menampilkan teks peringatan, lalu tambahkan Conditional Formatting merah pada sel itu.

Cari di excel.my.id

Coba cari