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 1 | A (Kategori) | B (Rincian) | C (Qty) | D (Harga satuan) | E (Total) | F (% dari total) |
|---|---|---|---|---|---|---|
| 3 | Transportasi | Tiket kereta pergi-pulang, 2 orang | 4 | 350.000 | 1.400.000 | 28,5% |
| 4 | Penginapan | Hotel per malam | 3 | 450.000 | 1.350.000 | 27,5% |
| 5 | Makan | Makan 2 orang per hari | 4 | 180.000 | 720.000 | 14,6% |
| 6 | Transportasi lokal | Sewa motor atau ojek per hari | 3 | 100.000 | 300.000 | 6,1% |
| 7 | Tiket wisata | Tiket masuk, 2 orang | 2 | 150.000 | 300.000 | 6,1% |
| 8 | Oleh-oleh | Paket oleh-oleh | 1 | 400.000 | 400.000 | 8,1% |
| 9 | Dana cadangan | 10% dari subtotal | 447.000 | 9,1% | ||
| 10 | Total rencana | 4.917.000 | 100% |
Qty untuk tiket kereta adalah 4 karena 2 orang × 2 arah. Qty penginapan 3 karena tiga malam.
Rumus yang dipakai
| Sel | Rumus | Hasil | Fungsinya |
|---|---|---|---|
| E3 | =IF(D3="","",IF(C3="",1,C3)*D3) | 1.400.000 | Total baris. Jika Qty kosong dianggap 1 |
| E9 | =SUM(E3:E8)*10% | 447.000 | Dana cadangan 10% dari subtotal (4.470.000) |
| E10 | =SUM(E3:E9) | 4.917.000 | Total rencana |
| F3 | =E3/$E$10 | 28,5% | Porsi baris dari total. Tanda $ mengunci E10 saat rumus disalin ke bawah |
| E12 | =B1-E10 | 583.000 | Sisa anggaran |
| E13 | =IF(E10>B1,"Melebihi anggaran","Dalam anggaran") | Dalam anggaran | Peringatan |
| E14 | =E10/B1 | 89,4% | Persentase anggaran terpakai |
| E15 | =E10/2 | 2.458.500 | Biaya per orang (2 orang) |
| E16 | =E10/4 | 1.229.250 | Biaya 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.
| Kategori | Rencana | Realisasi | Selisih (Rencana − Realisasi) |
|---|---|---|---|
| Penginapan | 1.350.000 | 1.500.000 | −150.000 |
| Makan | 720.000 | 650.000 | 70.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
- Anggaran bulanan di Excel: rencana, realisasi, dan sisa untuk pengeluaran harian.
- Target tabungan di Excel: menghitung setoran per bulan untuk tujuan tertentu, seperti biaya perjalanan.