Jika Anda pernah bereksperimen dengan variabel yang berbeda untuk melihat bagaimana perubahan Anda akan mempengaruhi hasil dari suatu situasi, Anda telah melakukan analisis What if di excel.
Apakah Anda dapat menjual lebih banyak barang jika Anda memiliki obralan minggu ini? Atau apakah Anda akan menghasilkan lebih banyak uang dengan menaikkan harga? Dalam skenario di atas, Anda ingin mengetahui sejauh mana setiap perubahan memengaruhi hasil keseluruhan. Untuk alasan ini, analisis what if juga dikenal sebagai analisis sensitivitas.
Sebagian besar analisis what if benar-benar perhitungan matematis, dan itu adalah spesialisasi Excel. Untuk membantu Anda melakukan analisis what if, Excel menggunakan perintah dari grup perintah Prakiraan pada tab Data untuk menyiapkan prakiraan sederhana atau model bisnis tingkat lanjut.
Goal seek
Alat analisis sensitivitas paling sederhana di Excel adalah Goal Seek. Dengan asumsi bahwa Anda mengetahui satu hasil yang ingin Anda capai, fitur Goal Seek di Excel memungkinkan Anda mencapai tujuan tersebut dengan menyesuaikan satu variabel secara matematis dalam persamaan.
Untuk mengilustrasikan cara kerjanya, bayangkan bank menawarkan tingkat bunga 9% per tahun untuk pinjaman pribadi dengan 24 bulan untuk melunasinya, dan Anda ingin meminjam $40.000.
Dengan menggunakan informasi di atas, bank menghitung bahwa jumlah pinjaman ditambah bunga selama periode pinjaman akan menjadi $47.200, seperti yang ditunjukkan pada sel B5. Jumlah yang harus dibayar setiap bulan juga dihitung dan ditampilkan di sel B6.
Dengan menggunakan perintah Goal Seek, kita dapat menunjukkan hasil yang diinginkan dan Excel akan menentukan penyesuaian yang perlu kita buat untuk satu variabel.
Dalam contoh di atas, sel B5 bergantung pada variabel dalam sel B1, B2, dan B3. Sel B6 bergantung pada sel B3 dan B5. Oleh karena itu, jika kami menentukan bahwa jumlah pembayaran bulanan yang dikutip lebih tinggi dari yang diinginkan, kami dapat menggunakan Goal Seek untuk menetapkan jumlah bulanan menjadi $1750. Excel dapat bekerja mundur untuk mengubah sel B1, B2, atau B3 untuk mencapai tujuan tersebut.
Secara praktis, kami mungkin tidak memiliki banyak kendali atas tingkat bunga, jadi kemungkinan besar kami memiliki pilihan untuk menyesuaikan jumlah yang kami pinjam, atau jangka waktu pembayaran.
Kecenderungan pertama kita mungkin adalah mencari tahu berapa banyak yang dapat kita pinjam jika kita membayar $1750 per bulan dan semua variabel lainnya tetap sama. Excel akan mengubah prinsip (B1) berdasarkan angka yang kita masukkan sebagai nilai baru untuk sel B6.
Dengan asumsi bahwa jumlah bunga (9%) dan jangka waktu pinjaman (24 bulan) tetap sama, jumlah pokok baru dihitung dan ditampilkan di sel B1 jika ada solusi yang valid.
Poin yang perlu diperhatikan:
-
- Sel yang dipilih dalam bidang “Setel sel” harus berupa sel yang berisi rumus.
-
- Sel yang dipilih di bidang “Dengan mengubah sel” harus berupa sel yang berisi konstanta.
-
- Setelah “OK” dipilih dari jendela Status Pencarian Sasaran, nilai pada lembar kerja disesuaikan dan hanya dapat diambil dengan memilih perintah ‘Batalkan’ (Ctrl+Z pintasan Windows/Cmd+Z pintasan Mac).
Scenario Manager
Alat analisis what if lainnya adalah Scenario Manager. Opsi ini agak lebih maju daripada Goal Seek karena memungkinkan penyesuaian beberapa variabel secara bersamaan.
Beberapa perbedaan mencolok lainnya antara Goal Seek dan Scenario Manager tercantum di bawah ini:
-
- Scenario manager memungkinkan pembuatan skenario dalam jumlah tak terbatas dengan mengubah hingga 32 variabel sekaligus.
-
- Setiap skenario dapat disimpan untuk tujuan perbandingan.
-
- Skenario dapat diberi nama dan diedit, dan deskripsi singkat disediakan.
-
- Hanya nilai konstan yang harus diubah dalam scenario manager — sel dengan rumus tidak boleh disesuaikan secara manual.
Jika kita melanjutkan contoh pinjaman bank, kita dapat menentukan sensitivitas model untuk berubah dengan menyesuaikan salah satu atau semua nilai di sel B1, B2, atau B3.
Praktek terbaik
Sebagai praktik terbaik, data lembar kerja asli harus disimpan sebagai skenario sehingga Anda dapat mengembalikannya setelah semua eksperimen selesai.
Langkah 1 – Klik ‘What If Analysis’ dari tab Data dan pilih Scenario Manager.
Langkah 2 – Klik ‘Tambah’ dari jendela pop-up scenario manager.
Langkah 3 – Beri nama skenario ini “Asli” dan masukkan referensi sel dari semua sel dengan nilai konstan yang dapat Anda pertimbangkan untuk diubah dalam skenario lain (maksimum 32 sel). Klik Oke.
Langkah 4 – Untuk skenario “Asli”, jangan sesuaikan nilai apa pun di jendela ‘Nilai Skenario’.
Langkah 5 – Klik ‘Tambah’ untuk membuat skenario eksperimental pertama Anda.
Membuat skenario eksperimental
Saat membuat skenario eksperimental, berikan skenario nama deskriptif dari jendela pop-up ‘Tambah Skenario’. Sel yang berubah akan sama dengan yang dirujuk dalam skenario ‘Asli’ Anda.
Bahkan jika Anda tidak akan menyesuaikan semua nilai-nilai dalam sel-sel itu, sangat disarankan agar mereka tetap direferensikan di bidang ‘mengubah sel’. Anda dapat menempatkan detail tambahan tentang skenario eksperimental di bidang ‘Komentar’ (lihat di bawah).
Seperti yang diilustrasikan di atas, skenario eksperimental kami diberi nama “36 bulan” dan mengacu pada sel B1 ke B3 sebagai sel yang berubah. Komentar tambahan menunjukkan bahwa skenario ini adalah untuk menentukan pengaruh pinjaman $40.000 selama periode 36 bulan.
Di jendela ‘Nilai Skenario’, setiap sel yang berubah ditampilkan sebagai bidang di mana kita dapat memanipulasi nilai konstan untuk memengaruhi hasil sel yang bergantung — dalam kasus kita, sel B5 dan B6. Seperti yang dijelaskan dalam nama skenario dan komentar kami, kami hanya menyesuaikan sel B3 dengan mengubah nilainya menjadi 36.
Untuk menambahkan skenario lain pada saat ini, pilih ‘Tambah’. Jika tidak, klik OK.
Sesuaikan beberapa variabel
Untuk bereksperimen dengan menyesuaikan beberapa variabel dalam satu skenario, langkah-langkahnya sama seperti di atas, dengan pengecualian bahwa perubahan yang diinginkan akan dibuat di jendela Nilai Skenario.
Misalnya, untuk membuat Excel melakukan analisis what if meminjam $50.000 selama periode 36 bulan dalam situasi di atas pada tingkat bunga yang sama, kami hanya akan menyesuaikan bidang yang merujuk variabel tersebut setelah membuat skenario baru. Manajer Skenario Excel dapat menangani skenario dalam jumlah tak terbatas yang dibuat dengan cara yang sama.
Daftar skenario yang dibuat dapat dilihat dengan mengklik OK dari jendela Nilai Skenario, atau dengan memilih scenario manager dari menu tarik-turun Bagaimana Jika Analisis.
Untuk melihat hasil dari setiap penyesuaian pada sel keluaran, klik dua kali pada nama skenario, atau sorot sebuah nama dan klik Tampilkan.
Ringkasan skenario
Skenario yang telah dibuat juga dapat dibandingkan secara berdampingan dengan pembuatan lembar kerja ringkasan Skenario, yang dihasilkan dengan memilih ‘Ringkasan’ dari jendela scenario manager.
Ada dua tipe laporan yang tersedia — ringkasan skenario dan laporan PivotTable skenario. Sel hasil adalah sel yang akan ditampilkan dalam ringkasan. Idealnya, ini harus mencakup semua sel yang disesuaikan serta sel hasil. Ini juga merupakan ide yang baik untuk memilih sel yang berisi nama header sehingga ini ditampilkan dengan jelas dalam ringkasan.
Memilih opsi ‘Ringkasan scenario’ akan membuat lembar baru di dalam buku kerja yang menampilkan setiap skenario dalam format kolom. Mengubah Sel disorot dalam warna abu-abu, dan Sel Hasil ditampilkan di bawah Mengubah Sel.
Perhatikan bahwa jika rentang bernama dibuat untuk Mengubah atau Sel Hasil, nama rentang akan ditampilkan alih-alih referensi sel.
Memilih jenis laporan PivotTable Skenario akan membuat laporan tabel pivot di lembar kerja baru. Pelajari lebih lanjut tentang tabel pivot dari Pustaka Sumber Daya kami.
Menggunakan tabel data untuk analisis what if
Alat analisis what if ketiga dari kelompok perintah Forecast adalah Tabel Data. Tabel data memungkinkan penyesuaian hanya satu atau dua variabel dalam kumpulan data, tetapi setiap variabel dapat memiliki jumlah kemungkinan nilai yang tidak terbatas. Tabel data dirancang untuk perbandingan berdampingan dengan cara yang membuatnya lebih mudah dibaca daripada skenario, setelah disiapkan dengan benar.
Tabel data kurang dimanfaatkan, tetapi tidak seseram kelihatannya.
Tabel data satu variabel
Jika satu-satunya variabel yang dipertimbangkan dalam contoh pinjaman kami adalah jumlah yang dipinjam, kami dapat membuat tabel data satu variabel.
Langkah 1 – buat daftar semua kemungkinan jumlah pinjaman pokok. Daftar ini mungkin berdasarkan kolom atau baris. Dalam contoh kami, kami akan memasukkan daftar kolom dalam rentang D9 hingga D12.
Langkah 2 – Di kolom yang berdekatan, masukkan rumus yang digunakan untuk sampai pada hasil awal. Dalam hal ini, kita cukup mengetikkan =B6 di sel E8. Ini menautkan tabel data baru kami ke variabel asli.
Langkah 3 – Pilih seluruh rentang tabel data, termasuk daftar nilai variabel, rumus, dan sel kosong.
Langkah 4 – Dari menu tarik-turun Analisis what if, pilih Tabel Data.
Langkah 5 – Dalam sel masukan kolom bidang (karena kami memasukkan variabel kami dalam format kolom), masukkan referensi sel yang digunakan untuk menghitung hasil dalam kumpulan data asli. Dalam contoh di atas, ini akan menjadi sel B1 karena ini adalah variabel yang telah kita sesuaikan. Tidak ada nilai yang dimasukkan dalam bidang ‘Row input cell’ dalam contoh ini karena ini adalah tabel data satu variabel.
Langkah 6 – Pilih Oke. Hasilnya adalah daftar hasil yang dibuat dengan menyesuaikan satu variabel di sel B1, dengan asumsi bahwa semua variabel lainnya tetap konstan.
Untuk membuat tabel data berorientasi baris, variabel akan dicantumkan secara horizontal, dan sel input baris akan digunakan di jendela Tabel Data alih-alih sel input kolom.
Tabel data dua variabel
Saat membuat tabel data dua variabel, satu kumpulan nilai dicantumkan secara horizontal dan kumpulan lainnya dicantumkan secara vertikal. Dalam contoh kami, kami akan menambahkan periode pinjaman (jangka waktu) sebagai variabel kedua kami, ditampilkan secara horizontal.
Dalam hal ini, rumus yang digunakan untuk sampai pada hasil asli harus direplikasi di atas daftar vertikal variabel. Seperti yang ditunjukkan di bawah ini, kami mengetik =B6 di sel D7. Ini menautkan tabel data baru kami ke variabel asli.
Seperti sebelumnya, sorot seluruh rentang tabel data dan pilih Tabel Data dari menu Bagaimana Jika Analisis. Sel input baris adalah referensi sel (B3) yang sesuai dengan variabel horizontal dari dataset asli, sedangkan sel input kolom (B1) sesuai dengan variabel vertikal.
Saat kami memilih OK, Excel mengembalikan matriks yang dapat digunakan untuk membandingkan hasil perubahan yang berbeda dengan skenario awal kami. Mungkin perlu menyesuaikan sel keluaran ke format angka yang sesuai untuk tipe data Anda (dalam kasus contoh di atas, mata uang).
Ringkasan
Sekarang Anda telah meluangkan waktu untuk mengungkap bagaimana melakukan analisis what if di Excel dengan menggunakan tiga alat utama ini, mengapa tidak bereksperimen dengan menggunakannya dalam pengaturan yang berbeda — seperti manajemen anggaran, persentase margin keuntungan, target penyelesaian proyek.