Analisis What If di Excel: Kupas Tuntas!


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.

Bagaimana jika analisis Excel?


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.

Bagaimana jika analisis Excel - Goal SeekUntuk 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.

Bagaimana jika analisis Excel - Goal SeekDengan 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.

Bagaimana jika analisis Excel - Goal SeekDengan 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.

Baca Juga:  12 Praktik Terbaik dalam Desain Dasbor

Bagaimana jika analisis Excel - Goal SeekPoin yang perlu diperhatikan:

    1. Sel yang dipilih dalam bidang “Setel sel” harus berupa sel yang berisi rumus.
    1. Sel yang dipilih di bidang “Dengan mengubah sel” harus berupa sel yang berisi konstanta.
    1. 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.

Bagaimana jika analisis Excel - manajer skenario?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.

Bagaimana jika analisis Excel - manajer skenario?Langkah 4 – Untuk skenario “Asli”, jangan sesuaikan nilai apa pun di jendela ‘Nilai Skenario’.

Bagaimana jika analisis Excel - manajer 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).

Bagaimana jika analisis Excel - manajer skenario?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.

Baca Juga:  Cara Memfilter di Excel

Untuk menambahkan skenario lain pada saat ini, pilih ‘Tambah’. Jika tidak, klik OK.

Bagaimana jika analisis Excel - manajer skenario?

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.

Bagaimana jika analisis Excel - manajer skenario?Untuk melihat hasil dari setiap penyesuaian pada sel keluaran, klik dua kali pada nama skenario, atau sorot sebuah nama dan klik Tampilkan.

Bagaimana jika analisis Excel - manajer skenario?

Bagaimana jika analisis Excel - manajer skenario?

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.

Bagaimana jika analisis Excel - manajer skenario?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.

Bagaimana jika analisis Excel - ringkasan skenarioPerhatikan 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.

Baca Juga:  Fungsi Excel AND di Microsoft Excel: Kupas Tuntas

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.

Bagaimana jika analisis Excel - tabel data?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.

Bagaimana jika analisis Excel - tabel data?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.

Bagaimana jika analisis Excel - tabel data?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.

Bagaimana jika analisis Excel - tabel data?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.

Bagaimana jika analisis Excel - tabel data?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).

Bagaimana jika analisis Excel - tabel data?

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.

Tinggalkan komentar