Cara Melakukan Analisis Sensitivitas di Excel: Langkah demi Langkah

Untuk melakukan analisis sensitivitas di Excel, tempatkan setiap input model Anda di selnya masing-masing. Kemudian ubah input tersebut dan catat bagaimana hasilnya berubah. Gunakan tabel data satu variabel untuk satu input, tabel data dua variabel untuk dua input, dan Scenario Manager untuk kasus lengkap. Akhiri dengan tornado chart yang mengurutkan input berdasarkan dampaknya.

Panduan ini menjelaskan apa itu analisis sensitivitas, cara menyiapkan model, dan cara menggunakan setiap alat Excel. Ini mencakup contoh praktis dengan formula nyata, metode yang lebih cepat dengan AI, dan kesalahan yang harus dihindari.

Apa itu analisis sensitivitas

Analisis sensitivitas menunjukkan seberapa besar hasil berubah ketika satu asumsi berubah. Ini menjawab pertanyaan seperti "Apa yang terjadi pada laba jika harga turun 10%?"

Sebagian besar model bisnis didasarkan pada tebakan. Volume penjualan, harga, biaya, dan tingkat pertumbuhan semuanya adalah estimasi. Analisis sensitivitas menguji tebakan mana yang paling berpengaruh.

Excel mengelompokkan alat-alat ini di bawah What-If Analysis. Microsoft mendefinisikannya sebagai "proses mengubah nilai dalam sel" untuk melihat bagaimana hasil formula berubah.

Microsoft mencantumkan tiga alat bawaan: "Scenarios, Goal Seek, dan Data Tables." Masing-masing memiliki tugas yang berbeda:

• Data tables menunjukkan satu hasil di berbagai nilai dari satu atau dua input.
• Scenario Manager menyimpan set input lengkap, seperti kasus terburuk dan kasus terbaik.
• Goal Seek bekerja secara terbalik. Alat ini menemukan input yang diperlukan untuk mencapai hasil target.

Analisis sensitivitas biasanya menggunakan dua yang pertama. Goal Seek membantu menjawab pertanyaan terkait, seperti harga titik impas.

Siapkan model sebelum Anda mulai

Alat Excel hanya berfungsi jika model dibangun dengan bersih. Periksa poin-poin ini terlebih dahulu.

Tempatkan setiap input di selnya sendiri. Unit, harga, biaya, dan tarif masing-masing membutuhkan sel. Jangan mengetik angka di dalam formula.

Hubungkan hasil ke input. Sel hasil, seperti laba, harus berupa formula yang merujuk ke sel input. Jika tidak, tidak ada yang akan berubah saat Excel memasukkan nilai baru.

Satukan input. Kelompokkan dalam satu blok di bagian atas lembar kerja. Beri label pada masing-masing, dan beri warna pada sel input agar menonjol.

Pilih satu hasil untuk diuji. Analisis sensitivitas lebih mudah dibaca jika melacak satu output, seperti laba, net present value, atau kas di akhir tahun.

Catat base case Anda. Tuliskan nilai awal. Setiap hasil dalam analisis dibandingkan dengan basis ini.

Berikut adalah model laba sederhana untuk diikuti. Ini adalah toko sepeda fiktif, Northgate Cycles:

Sel	Label	Nilai
B1	Unit terjual	2,400
B2	Harga per unit	$850
B3	Biaya variabel per unit	$510
B4	Biaya tetap	$520,000
B5	Laba	=B1*(B2-B3)-B4

Pada nilai dasar ini, laba adalah $296,000.

Cara melakukan analisis sensitivitas satu variabel dengan tabel data

Tabel data satu variabel menguji rentang nilai untuk satu input. Microsoft mengatakan ini menunjukkan "bagaimana nilai yang berbeda dari satu variabel dalam satu atau lebih formula akan mengubah hasil."

Berikut cara menguji harga dalam contoh model:

1. Di D2 hingga D6, ketik harga yang akan diuji: $800, $825, $850, $875, dan $900.
2. Di E1, satu baris di atas dan satu kolom di sebelah kanan harga pertama, ketik =B5.
3. Pilih D1:E6.
4. Pada tab Data, pilih What-If Analysis > Data Table.
5. Di Column input cell, masukkan B2, sel harga. Biarkan Row input cell kosong.
6. Klik OK.

Excel mengisi E2:E6 dengan laba untuk setiap harga. Penempatan formula sangat penting. Microsoft menyarankan untuk mengetiknya "satu baris di atas dan satu sel di sebelah kanan kolom nilai."

Hasil untuk Northgate Cycles:

Harga	Laba
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

Setiap perubahan harga sebesar $25 menggeser laba sebesar $60,000. Itulah sensitivitas laba terhadap harga.

Cara melakukan analisis sensitivitas dua variabel

Tabel data dua variabel menguji dua input sekaligus. Microsoft mengatakan ini menunjukkan "bagaimana nilai yang berbeda dari dua variabel dalam satu formula akan mengubah hasil formula tersebut."

Berikut cara menguji harga dan unit secara bersamaan:

1. Di sel kosong, seperti D10, ketik =B5.
2. Di bawahnya, di D11 hingga D13, ketik volume unit: 2,000, 2,400, dan 2,800.
3. Di sebelah kanannya, di E10 hingga G10, ketik harga: $800, $850, dan $900.
4. Pilih D10:G13.
5. Pilih What-If Analysis > Data Table.
6. Di Row input cell, masukkan B2. Di Column input cell, masukkan B1.
7. Klik OK.

Hasilnya adalah kisi-kisi laba:

Unit \ Harga	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

Kisi-kisi tersebut menunjukkan bahwa toko tetap menguntungkan dalam setiap kombinasi yang diuji. Titik terlemah, 2,000 unit pada harga $800, masih menghasilkan $60,000.

Ada satu batasan keras. Microsoft menyatakan "Tabel data tidak dapat menampung lebih dari dua variabel." Untuk input lebih banyak, gunakan Scenario Manager.

Tabel data juga menghitung ulang setiap kali lembar kerja dihitung ulang. Dalam buku kerja yang besar, hal itu dapat memperlambat Excel. Microsoft menyarankan untuk mengatur kalkulasi agar tabel data hanya dihitung ulang saat Anda menekan F9.

Cara membandingkan kasus lengkap dengan Scenario Manager

Scenario Manager menyimpan set input lengkap dan memasukkannya secara bergantian. Microsoft mendeskripsikan skenario sebagai "kumpulan nilai yang disimpan Excel dan dapat diganti secara otomatis pada lembar kerja Anda."

Berikut cara menyiapkan tiga kasus:

1. Pada tab Data, pilih What-If Analysis > Scenario Manager.
2. Klik Add, beri nama skenario Base, dan masukkan B1:B4 sebagai sel yang berubah. Pertahankan nilai saat ini.
3. Tambahkan kasus Terburuk (Worst case): 2,000 unit, harga $825, biaya variabel $530, dan biaya tetap $540,000.
4. Tambahkan kasus Terbaik (Best case): 2,800 unit, harga $875, biaya variabel $495, dan biaya tetap $510,000.
5. Pilih skenario dan klik Show untuk memuat nilainya.
6. Klik Summary dan pilih B5 sebagai sel hasil untuk membuat laporan.

Untuk Northgate Cycles, ringkasan menunjukkan tiga laba. Kasus terburuk menghasilkan $50,000, base case $296,000, dan kasus terbaik $554,000.

Microsoft mencatat beberapa batasan. Setiap skenario dapat menampung hingga 32 nilai yang berubah. Laporan ringkasan tidak otomatis: "Laporan skenario tidak dihitung ulang secara otomatis." Jika Anda mengubah skenario, buat ringkasan baru.

Cara menemukan titik impas dengan Goal Seek

Analisis sensitivitas menunjukkan seberapa jauh hasil bergeser. Goal Seek menjawab pertanyaan sebaliknya: nilai input apa yang memberikan hasil target?

Microsoft mengatakan Goal Seek "mengambil hasil dan menentukan nilai input yang mungkin menghasilkan hasil tersebut." Contohnya menemukan suku bunga yang diperlukan untuk pembayaran pinjaman.

Berikut cara menemukan harga titik impas untuk Northgate Cycles:

1. Pada tab Data, pilih What-If Analysis > Goal Seek.
2. Di Set cell, masukkan B5, sel laba.
3. Di To value, masukkan 0.
4. Di By changing cell, masukkan B2, sel harga.
5. Klik OK.

Excel mengubah harga hingga laba mencapai nol. Pada 2,400 unit dan biaya dasar, harga titik impas adalah sekitar $726.67. Itu sekitar 15% di bawah harga $850 saat ini.

Goal Seek memiliki satu batasan. Microsoft mengatakan alat ini "hanya bekerja dengan satu nilai input variabel." Untuk lebih dari satu input, Microsoft menyarankan add-in Solver.

Cara membuat tornado chart

Tornado chart mengurutkan input berdasarkan seberapa besar mereka menggerakkan hasil. Ini adalah cara paling jelas untuk menyajikan analisis sensitivitas.

Berikut cara membuatnya:

1. Pilih rentang pengujian, seperti setiap input 10% lebih rendah dan 10% lebih tinggi.
2. Ubah satu input pada satu waktu, dan catat hasil rendah dan tingginya. Biarkan input lainnya pada basis.
3. Masukkan hasil ke dalam tabel dengan satu baris per input.
4. Urutkan baris sehingga input dengan rentang terluas berada di atas.
5. Masukkan diagram batang dari hasil rendah dan tinggi.

Untuk Northgate Cycles, dengan setiap input digeser 10% naik dan turun:

Input	Laba pada −10%	Laba pada +10%	Rentang
Harga	$92,000	$500,000	$408,000
Biaya variabel	$418,400	$173,600	$244,800
Unit terjual	$214,400	$377,600	$163,200
Biaya tetap	$348,000	$244,000	$104,000

Harga paling berpengaruh, dengan selisih yang lebar. Penurunan harga 10% memangkas laba dari $296,000 menjadi $92,000. Biaya tetap paling tidak berpengaruh. Itu memberi tahu pemilik di mana harus menghabiskan waktu untuk memeriksa asumsi.

Diagram batang di Excel menggambar batang dari kiri ke kanan. Untuk mendapatkan bentuk tornado, plot perubahan setiap input dari laba dasar alih-alih nilai mentahnya. Kemudian batang kasus rendah menunjuk ke kiri dan batang kasus tinggi menunjuk ke kanan.

Cara melakukan analisis sensitivitas dengan AI

Membangun tabel data, skenario, dan tornado chart untuk model yang besar memakan waktu. Alat AI dapat menjalankan pengujian yang sama dari buku kerja Anda.

Berikut metodenya dalam tiga langkah, menggunakan Powerdrill Bloom.

Langkah 1: Unggah model Anda

Mulailah dengan spreadsheet yang berisi model Anda. AI Sensitivity Analysis Generator meminta Anda untuk mengunggah "DCF, business case, operating plan atau model spreadsheet apa pun."

Pastikan input dan sel hasil diberi label dengan jelas. Alat ini perlu mengetahui angka mana yang merupakan asumsi.

Langkah 2: Pilih tampilan sensitivitas dalam bahasa alami

Jelaskan pengujian yang Anda inginkan. Misalnya: "Tunjukkan tornado chart untuk laba dengan setiap input digeser 10% naik dan turun. Tambahkan tabel dua arah untuk harga dan unit terjual."

Halaman alat mencantumkan empat tampilan: "tornado, two-way, Monte Carlo atau break-even." Ini mendeskripsikan output tornado sebagai bagan yang "mengurutkan input yang paling menggerakkan jawaban." Alat ini juga menawarkan "Tabel sensitivitas atas dua asumsi yang berpengaruh."

Langkah 3: Periksa hasil dan ekspor analisis

Bandingkan satu atau dua hasil dengan tabel data Anda sendiri sebelum Anda membagikannya. Base case harus sama persis dengan model Anda.

Kemudian ekspor. Halaman alat mencantumkan "buku kerja Excel, PDF bermerek, atau tautan kanvas." Untuk presentasi, alat Excel to PPT mengubah file yang sama menjadi slide. Halaman AI Financial Modeling mencakup pembuatan model itu sendiri.

Cara membaca dan menyajikan hasil

Analisis sensitivitas hanya berguna jika orang menindaklanjutinya. Kebiasaan ini membantu.

Mulai dengan peringkat. Buka dengan tornado chart. Ini menunjukkan dalam satu gambar asumsi mana yang mendorong hasil.

Terjemahkan rentang menjadi keputusan. "Laba turun menjadi $92,000 jika harga turun 10%" adalah sebuah fakta. "Kita harus menguji harga sebelum menandatangani kontrak sewa" adalah sebuah keputusan.

Tunjukkan titik impas. Pasangkan analisis dengan nilai di mana hasil mencapai nol. Untuk Northgate Cycles, titik impas adalah sekitar 1,530 unit pada harga dan biaya dasar. Panduan kami tentang analisis titik impas di Excel membahas perhitungan tersebut.

Gunakan rentang yang realistis. Pergeseran 10% cocok untuk input yang stabil. Volume produk baru mungkin memerlukan rentang yang jauh lebih luas.

Nyatakan apa yang dibuat konstan. Setiap pengujian satu-per-satu menjaga input lainnya pada basis. Katakan demikian pada slide atau dalam catatan kaki. Pembaca kemudian tahu bahwa perubahan harga 10% mengasumsikan volume, biaya, dan pengeluaran tetap yang sama. Satu baris ini mencegah sebagian besar pertanyaan lanjutan tentang metode tersebut.

Tinjau kembali setelah hasil keluar. Begitu angka aktual tiba, bandingkan dengan rentang Anda. Laporan anggaran vs. aktual adalah tindak lanjut yang alami.

Kesalahan umum analisis sensitivitas

Kesalahan ini umum terjadi dan mudah dihindari.

Angka yang diketik langsung (hard-coded) dalam formula. Jika formula berisi 850 alih-alih B2, tabel data tidak dapat mengubahnya. Simpan setiap input dalam sel.

Menguji terlalu banyak input sekaligus. Tabel data menangani satu atau dua. Beralihlah ke Scenario Manager atau tornado chart untuk lebih banyak lagi.

Menggunakan rentang yang sama untuk setiap input. Harga dan volume jarang membawa ketidakpastian yang sama. Pilih rentang yang mencerminkan apa yang sebenarnya Anda ketahui.

Mengabaikan hubungan antar input. Harga yang lebih rendah dapat meningkatkan volume. Pengujian satu-per-satu melewatkan hal itu, jadi tambahkan skenario yang menggerakkan keduanya.

Ringkasan skenario yang usang. Laporan skenario tidak diperbarui dengan sendirinya. Bangun kembali setelah ada perubahan apa pun.

Melupakan base case. Setiap tabel harus menunjukkan hasil dasar, sehingga pembaca dapat melihat besarnya setiap perubahan.

Saat Anda siap untuk melakukan stress-test pada model berikutnya, coba Powerdrill Bloom dengan buku kerja yang Anda gunakan untuk perencanaan.

Pertanyaan yang sering diajukan

Apa itu analisis sensitivitas di Excel?

Ini adalah cara untuk menguji bagaimana hasil berubah ketika inputnya berubah. Di Excel, Anda menggunakan alat What-If Analysis, terutama tabel data dan Scenario Manager, untuk memasukkan nilai input baru dan mencatat hasilnya.

Bagaimana cara melakukan analisis sensitivitas satu variabel di Excel?

Daftarkan nilai input dalam satu kolom. Tempatkan formula yang merujuk ke hasil satu baris di atas dan satu kolom di sebelah kanan. Pilih rentang tersebut, pilih What-If Analysis > Data Table, dan masukkan sel input sebagai sel input kolom.

Apa perbedaan antara tabel data dan Scenario Manager?

Tabel data menguji satu atau dua variabel di berbagai nilai dan menunjukkan semua hasil dalam satu kisi. Scenario Manager menyimpan set input lengkap, masing-masing hingga 32 nilai yang berubah, dan memasukkannya satu per satu.

Bagaimana cara membuat tornado chart di Excel?

Geser setiap input naik dan turun dengan jumlah yang sama, satu per satu, dan catat hasilnya. Urutkan input berdasarkan ukuran rentang, lalu petakan perubahan setiap input dari hasil dasar sebagai batang horizontal.

Bisakah AI melakukan analisis sensitivitas?

Ya. Alat AI dapat membaca model spreadsheet Anda, menjalankan pengujian tornado dan dua arah, serta mengekspor hasilnya sebagai buku kerja atau laporan. Anda tetap perlu memeriksa base case dan memilih rentang yang realistis.

Sumber: Microsoft Support, Introduction to What-If Analysis · Microsoft Support, Calculate multiple results by using a data table · Microsoft Support, Switch between various sets of values by using scenarios.