Cara Melakukan Analisis Varians di Excel: Langkah demi Langkah

Untuk melakukan analisis varians di Excel, letakkan angka anggaran dan aktual secara berdampingan untuk setiap item baris. Kurangi anggaran dari aktual untuk mendapatkan varians dalam nilai mata uang. Bagi varians tersebut dengan anggaran untuk mendapatkan persentasenya. Kemudian tandai setiap baris sebagai menguntungkan atau tidak menguntungkan dan jelaskan selisih terbesarnya.
Panduan ini menjelaskan rumus-rumus, tata letak lembar kerja, dan contoh pengerjaan lengkap. Panduan ini juga mencakup cara membuat grafik hasil dan cara membagi selisih pendapatan menjadi harga dan volume. Langkah-langkah Excel berasal dari halaman dukungan Microsoft yang dibaca pada 10 November 2026.
Apa yang ditunjukkan oleh analisis varians
Varians adalah selisih antara apa yang Anda rencanakan dan apa yang terjadi. Analisis varians mencantumkan selisih tersebut baris demi baris dan menjelaskan selisih yang penting.
Sebagian besar tim membandingkan angka aktual dengan salah satu dari tiga garis dasar:
- Anggaran (Budget). Rencana yang ditetapkan di awal tahun.
- Prakiraan (Forecast). Rencana yang diperbarui yang ditetapkan selama tahun berjalan.
- Periode sebelumnya. Bulan lalu, kuartal lalu, atau periode yang sama tahun lalu.
Keterampilan Copilot milik Microsoft sendiri untuk tugas ini mendeskripsikan pekerjaan tersebut dengan baik. Keterampilan analisis variansnya "Membandingkan hasil aktual dengan anggaran, prakiraan, atau periode sebelumnya dan menjelaskan perbedaan berdasarkan harga, volume, bauran, tarif, atau waktu."
Output-nya biasanya berupa tabel ditambah beberapa catatan tertulis. Tabel tersebut menunjukkan setiap selisih. Catatan tersebut menjelaskan tiga atau empat hal yang perlu ditindaklanjuti oleh para pemimpin.
Rumus inti
Tiga rumus melakukan sebagian besar pekerjaan. Asumsikan anggaran ada di kolom B dan aktual ada di kolom C.
| Ukuran | Rumus | Apa yang diberitahukan kepada Anda |
|---|---|---|
| Varians mata uang | =C2-B2 | Seberapa jauh aktual dari anggaran, dalam mata uang |
| Varians persentase | =IFERROR((C2-B2)/ABS(B2),"") | Seberapa besar selisihnya relatif terhadap rencana |
| Status | =IF(D2=0,"Sesuai rencana",IF(D2>0,"Di atas rencana","Di bawah rencana")) | Ke arah mana baris tersebut bergerak |
Fungsi ABS menjaga tanda tetap benar ketika anggaran bernilai negatif. IFERROR membiarkan sel kosong ketika anggaran nol, alih-alih menunjukkan kesalahan.
Arah tidak sama dengan baik atau buruk. Pendapatan di atas rencana adalah kabar baik. Biaya di atas rencana adalah kabar buruk. Bagian selanjutnya menunjukkan cara menanganinya.
Cara mengatur lembar kerja
Tata letak yang bersih membuat rumus mudah disalin. Gunakan satu baris per item baris dan kolom-kolom ini:
- Item baris. Pendapatan, harga pokok penjualan, penggajian, pemasaran, dan sebagainya.
- Tipe. Masukkan "Pendapatan" atau "Biaya" agar Excel tahu arah mana yang baik.
- Anggaran. Jumlah yang direncanakan untuk periode tersebut.
- Aktual. Jumlah dari ekspor akuntansi Anda.
- Varians. Aktual dikurangi anggaran.
- Varians %. Varians dibagi anggaran.
- Menguntungkan atau tidak menguntungkan. Label berdasarkan tipe dan arah.
- Penjelasan. Catatan singkat untuk baris yang material.
Pastikan anggaran dan aktual untuk periode yang sama dan pemetaan akun yang sama. Analisis varians hanya sebagus kecocokan tersebut. Jika penggajian dibagi menjadi dua akun dalam anggaran, bagi dengan cara yang sama dalam aktual.
Format tabel sebagai tabel Excel dengan Ctrl+T. Baris baru kemudian akan mengambil rumus secara otomatis.
Cara menghitung varians di Excel
Dengan tata letak yang sudah ada, langkah-langkahnya singkat. Asumsikan kolom di atas dimulai di baris 2, dengan Tipe di B, Anggaran di C, dan Aktual di D.
- Di E2, masukkan =D2-C2 untuk varians mata uang.
- In F2, masukkan =IFERROR((D2-C2)/ABS(C2),"") dan format sebagai persentase.
- Di kolom Status, masukkan pengujian menguntungkan yang ditunjukkan di bawah ini.
- Salin rumus ke bawah ke setiap baris.
- Tambahkan baris total, dan hitung variansnya dari total, bukan dengan menjumlahkan persentase.
Berikut adalah pengujian menguntungkan untuk baris 2 dari kolom Status:
=IF(E2=0,"Sesuai rencana",IF(B2="Pendapatan",IF(E2>0,"Menguntungkan","Tidak Menguntungkan"),IF(E2<0,"Menguntungkan","Tidak Menguntungkan")))
Rumus ini membaca kolom Tipe terlebih dahulu. Untuk pendapatan, varians positif menguntungkan. Untuk biaya, varians negatif menguntungkan karena Anda membelanjakan lebih sedikit dari yang direncanakan.
Persentase perlu diperhatikan di baris total. Menjumlahkan persentase baris memberikan jawaban yang salah. Bagilah total varians dengan total anggaran sebagai gantinya.
Cara menandai varians yang penting
Tidak setiap selisih membutuhkan catatan. Sebagian besar tim menetapkan aturan materialitas, seperti "di atas 5% dan di atas $2,000." Baris yang lulus kedua pengujian tersebut mendapatkan penjelasan.
Rumus sederhana menandainya. Tambahkan kolom Material dan masukkan =AND(ABS(F2)>0.05,ABS(E2)>2000) di baris 2. Sel akan menunjukkan TRUE untuk baris yang material.
Warna membuat tabel lebih cepat dipindai. Pemformatan bersyarat Excel dapat menambahkan set ikon. Microsoft mengatakan set ikon dapat "memberi anotasi dan mengklasifikasikan data ke dalam tiga hingga lima kategori yang dipisahkan oleh nilai ambang batas."
Untuk menambahkannya, pilih kolom Varians %. Kemudian buka Beranda > Pemformatan Bersyarat > Set Ikon. Microsoft mencatat bahwa dalam set 3 Panah, "panah hijau ke atas mewakili nilai yang lebih tinggi." Untuk baris biaya, balikkan urutan ikon sehingga kenaikan muncul sebagai peringatan.
Cara menjelaskan pendorongnya
Angka menunjukkan di mana selisihnya berada. Pendorong menjelaskan alasannya. Deskripsi keterampilan Microsoft mencantumkan lima pendorong umum: harga, volume, bauran, tarif, dan waktu.
Harga. Anda menjual atau membeli dengan harga yang berbeda dari yang direncanakan. Varians harga = (harga aktual − harga anggaran) × unit aktual.
Volume. Anda menjual atau menggunakan kuantitas yang berbeda. Varians volume = (unit aktual − unit anggaran) × harga anggaran.
Bauran (Mix). Pangsa produk, wilayah, atau pelanggan berubah. Pergeseran ke arah item dengan margin lebih rendah menurunkan laba meskipun volume tetap terjaga.
Tarif. Tarif biaya berubah, seperti upah per jam atau biaya pemasok.
Waktu (Timing). Uang berpindah antar periode. Faktur Maret yang dibayar pada bulan April muncul sebagai selisih di kedua bulan tersebut.
Harga dan volume bersama-sama menjelaskan seluruh selisih pendapatan. Pemeriksaan itu layak dilakukan setiap saat, karena mengonfirmasi perhitungan matematikanya.
Contoh analisis varians
Berikut adalah contoh pengerjaan untuk perusahaan fiktif, Cedar and Pine Coffee. Pemimpin keuangannya membandingkan aktual kuartal ketiga dengan anggaran.
| Item baris | Tipe | Anggaran | Aktual | Varians | Varians % | Status |
|---|---|---|---|---|---|---|
| Pendapatan | Pendapatan | $250,000 | $236,500 | −$13,500 | −5.4% | Tidak Menguntungkan |
| Harga pokok penjualan | Biaya | $100,000 | $97,200 | −$2,800 | −2.8% | Menguntungkan |
| Penggajian | Biaya | $60,000 | $64,800 | $4,800 | 8.0% | Tidak Menguntungkan |
| Pemasaran | Biaya | $20,000 | $26,000 | $6,000 | 30.0% | Tidak Menguntungkan |
| Sewa | Biaya | $15,000 | $15,000 | $0 | 0.0% | Sesuai rencana |
| Laba operasional | $55,000 | $33,500 | −$21,500 | −39.1% | Tidak Menguntungkan |
Totalnya sesuai. Pendapatan berkurang $13,500, harga pokok penjualan menghemat $2,800, biaya penggajian bertambah $4,800, dan biaya pemasaran bertambah $6,000. Secara keseluruhan itu adalah −$21,500, yang cocok dengan selisih laba.
Tiga baris lulus aturan materialitas di atas 5% dan di atas $2,000: pendapatan, penggajian, dan pemasaran. Harga pokok penjualan hanya bergerak 2.8%, jadi tidak mendapatkan catatan.
Selisih pendapatan perlu dicermati lebih lanjut. Anggaran mengasumsikan 50,000 unit seharga $5.00. Kuartal tersebut menjual 43,000 unit seharga $5.50.
- Varians volume. (43,000 − 50,000) × $5.00 = −$35,000.
- Varians harga. ($5.50 − $5.00) × 43,000 = +$21,500.
- Total. −$35,000 + $21,500 = −$13,500, yang cocok dengan baris pendapatan.
Jadi kenaikan harga tetap bertahan, tetapi mengorbankan volume. Catatan untuk para pemimpin mengatakan hal itu dengan tepat. Pemasaran menghabiskan 30% lebih banyak dari yang direncanakan, namun unit turun. Hal itu mengarah pada pertanyaan tentang kampanye mana yang berhasil.
Penggajian butuh pemeriksaannya sendiri. Selisih $4,800 bisa jadi pendorong tarif, seperti kenaikan upah, atau pendorong volume, seperti jam kerja lembur. Pemimpin keuangan bertanya kepada manajer operasi dan mengetahui bahwa dua toko menambahkan sif akhir pekan. Itu menjadikannya selisih volume yang terkait dengan jam buka, bukan kenaikan gaji.
Ringkasan akhir untuk para pemimpin muat dalam satu baris. Laba berada $21,500 di bawah anggaran, karena kenaikan harga mengorbankan lebih banyak volume daripada yang didapat, serta pemasaran dan sif akhir pekan melebihi rencana.
Cara menulis catatan varians
Tabel hanyalah setengah dari analisis varians. Para pemimpin membaca catatan, jadi setiap catatan harus menjawab tiga pertanyaan: apa yang bergerak, mengapa itu bergerak, dan apa yang terjadi selanjutnya.
Catatan yang baik mengikuti pola sederhana:
- Apa. "Pemasaran menghabiskan $6,000 di atas anggaran, 30.0% di atas rencana."
- Mengapa. "Dua kampanye berbayar berjalan lebih lama dari yang direncanakan."
- Selanjutnya. "Kami menghentikan satu kampanye pada 3 Oktober dan memperkirakan Q4 akan sesuai rencana."
Buat setiap catatan menjadi dua atau tiga kalimat. Gunakan urutan baris yang sama dengan tabel, sehingga pembaca dapat mencocokkan catatan dengan angka.
Pisahkan selisih satu kali dari tren. Tagihan perbaikan satu kali berbeda dari kenaikan upah yang akan berulang setiap bulan. Tandai selisih yang berulang dengan jelas, karena hal itu mengubah prakiraan.
Tulis catatan bersama pemilik setiap baris. Tim keuangan melihat angkanya, tetapi pemilik anggaran tahu alasannya. Pemeriksaan singkat dengan setiap pemilik mengubah tebakan menjadi penjelasan.
Terakhir, tambahkan ringkasan satu baris di bagian atas. Untuk contoh di atas, bunyinya: "Laba operasional Q3 berada $21,500 di bawah anggaran, terutama karena volume yang lebih rendah dan pengeluaran pemasaran yang lebih tinggi."
Waktu sama pentingnya dengan kata-kata. Sebagian besar tim menjalankan analisis varians setiap bulan, setelah pembukuan ditutup. Ritme bulanan menangkap masalah selagi masih ada waktu untuk bertindak.
Tinjauan triwulanan dilakukan lebih mendalam. Tinjauan tersebut menambahkan pembagian harga dan volume, prakiraan ulang, dan tinjauan apakah anggaran itu sendiri masih masuk akal. Tinjauan akhir tahun melihat setahun penuh terhadap rencana awal.
Gunakan templat yang sama setiap saat. Ketika kolom dan aturan tetap, analisis varians membutuhkan waktu lebih sedikit setiap bulan, dan tren lebih mudah dilihat di berbagai periode.
Cara membuat grafik varians
Grafik membantu para pemimpin melihat jalur dari anggaran ke aktual. Yang paling cocok adalah grafik air terjun (waterfall chart).
Microsoft mengatakan grafik air terjun "menunjukkan total berjalan saat nilai ditambahkan atau dikurangi." Ditambahkan pula bahwa "grafik air terjun juga disebut grafik jembatan (bridge charts)."
Untuk membuatnya, buat daftar langkah-langkah dari laba anggaran ke laba aktual. Mulai dengan $55,000, lalu tambahkan efek setiap baris pada laba, dan akhiri dengan $33,500. Pilih data dan pilih Sisipkan > Sisipkan Air Terjun > Air Terjun.
Atur batang pertama dan terakhir sebagai total. Halaman Microsoft mencatat bahwa kolom awal dan akhir "sering kali dimulai pada sumbu horizontal," sementara batang tengah mengambang. Kenaikan dan penurunan diberi kode warna, sehingga pendorong terbesar menonjol.
Untuk pelacakan bulan demi bulan, grafik kolom sederhana dari varians % juga bisa digunakan. Panduan kami tentang cara membangun laporan anggaran vs aktual mencakup laporan bulanan lengkap di sekitar tabel ini.
Lakukan analisis varians dengan AI
Rumus hanya memakan waktu beberapa menit setelah tabel ada. Bagian yang lambat sering kali adalah memetakan akun, menemukan pendorong, dan menulis catatan. Alat AI dapat menyusun langkah-langkah tersebut dari ekspor Anda.
Berikut adalah metodenya dalam tiga langkah, menggunakan Powerdrill Bloom.
Langkah 1: Unggah anggaran dan aktual Anda
Ekspor anggaran dan aktual dari alat akuntansi atau perencanaan Anda. AI Budget Analysis Tool menerima "ekspor Excel, CSV atau PDF dari akuntansi dan FP&A."
Sertakan data unit dan harga jika Anda ingin pembagian harga dan volume. Pastikan nama akun sama di kedua file.
Langkah 2: Minta varians dan pendorong dalam bahasa alami
Jelaskan output yang Anda butuhkan. Misalnya: "Bandingkan aktual Q3 dengan anggaran per item baris. Tandai baris di atas 5% dan $2,000, dan bagi selisih pendapatan menjadi harga dan volume."
Halaman alat mencantumkan "Varians per item baris dengan ambang batas materialitas yang diterapkan" sebagai salah satu tampilan. Halaman tersebut juga mencantumkan buku kerja prakiraan ulang dengan "anggaran asli, aktual YTD, dan akhir tahun yang direvisi."
Langkah 3: Periksa angka-angka dan ekspor laporannya
Bandingkan total dengan lembar kerja Anda sendiri sebelum Anda membagikan apa pun. Kemudian ekspor hasilnya. Halaman Analisis Anggaran mencantumkan "Dasbor varians dengan baris material yang disorot dan pendorong yang dilampirkan."
Untuk tampilan berkelanjutan, AI Finance Dashboard Builder mencakup Laba Rugi, kas, modal kerja, dan prakiraan. Coba Powerdrill Bloom dengan anggaran dan aktual kuartal lalu.
Kesalahan umum yang harus dihindari
Beberapa kesalahan muncul di hampir setiap analisis varians pertama.
- Mencampuradukkan tanda. Putuskan sekali apakah varians adalah aktual dikurangi anggaran, dan gunakan itu di mana-mana.
- Menyebut setiap selisih positif itu baik. Biaya yang lebih tinggi tidak menguntungkan meskipun angkanya positif.
- Menjumlahkan persentase. Hitung ulang total persentase dari total dolar.
- Membandingkan periode yang berbeda. Aktual tahun berjalan (year-to-date) terhadap anggaran bulanan memberikan selisih yang salah.
- Mengabaikan waktu. Faktur yang terlambat dapat menciptakan dua selisih yang berlawanan dalam dua bulan.
- Menjelaskan segalanya. Catatan pada selisih kecil menyembunyikan selisih yang penting.
- Mengetik angka secara manual. Tempel atau tautkan aktual dari ekspor, sehingga kesalahan ketik tidak menciptakan selisih palsu.
- Melewatkan pemeriksaan total. Varians baris harus berjumlah sama dengan total varians. Jika tidak, ada baris yang hilang atau dipetakan dua kali.
Pertanyaan yang sering diajukan
Apa rumus untuk varians di Excel?
Untuk varians anggaran, gunakan aktual dikurangi anggaran, seperti =C2-B2. Untuk persentase, gunakan =IFERROR((C2-B2)/ABS(B2),"") dan format sel sebagai persentase.
Bagaimana saya tahu jika varians itu menguntungkan?
Itu tergantung pada tipe baris. Untuk pendapatan, aktual di atas anggaran menguntungkan. Untuk biaya, aktual di bawah anggaran menguntungkan. Kolom Tipe dan rumus IF dapat melabeli setiap baris.
Apa itu varians material?
Ini adalah selisih yang cukup besar untuk dijelaskan. Tim menetapkan aturan mereka sendiri, seperti di atas 5% dan di atas $2,000. Baris yang lulus kedua pengujian tersebut mendapatkan catatan tertulis.
Grafik apa yang terbaik untuk analisis varians?
Grafik air terjun, juga disebut grafik jembatan, menunjukkan bagaimana setiap baris menggerakkan total dari anggaran ke aktual. Excel memilikinya di bawah Sisipkan > Sisipkan Air Terjun.
Bisakah Copilot di Excel melakukan analisis varians?
Microsoft mencantumkan keterampilan analisis varians untuk Copilot di Excel. Ini membandingkan aktual dengan anggaran, prakiraan, atau periode sebelumnya dan menyusun penjelasan untuk ditinjau. Ini membutuhkan lisensi Copilot.
Sumber: Dukungan Microsoft, Gunakan keterampilan bawaan dengan Copilot di Excel · Dukungan Microsoft, Membuat grafik air terjun · Dukungan Microsoft, Gunakan pemformatan bersyarat untuk menyoroti informasi di Excel.