Bagaimana Membangun Skenario Drop-Down di Model Keuangan Anda - dummies

Metode skenario bangunan yang paling umum digunakan adalah dengan menggunakan kombinasi formula dan kotak drop-down. Dalam model keuangan, Anda membuat tabel kemungkinan skenario dan masukan mereka dan menghubungkan nama skenario ke kotak drop-down sel input. Masukan model dihubungkan ke tabel skenario. Jika model telah dibangun dengan benar dengan semua input mengalir ke output, maka hasil model akan berubah saat pengguna memilih opsi yang berbeda dari kotak drop-down.

Kotak drop-down validasi data digunakan untuk sejumlah tujuan berbeda dalam pemodelan keuangan, termasuk analisis skenario.

Menggunakan validasi data untuk memodelkan skenario profitabilitas

Download File 0801. xlsx. Buka dan pilih tab berlabel 8-1-start.

Cara ini dimodelkan, inputnya berbaris di kolom B. Anda dapat melakukan analisis sensitivitas hanya dengan mengubah salah satu input - misalnya mengubah pelanggan per operator panggilan di sel B3 dari 40 menjadi 45, dan Anda akan melihat semua nomor dependen berubah. Ini akan menjadi analisis sensitivitas, karena Anda hanya mengubah satu variabel. Sebagai gantinya, Anda akan mengubah beberapa variabel sekaligus dalam latihan analisis skenario penuh ini, jadi Anda harus melakukan lebih dari sekadar men-tweak beberapa nomor secara manual.

Untuk melakukan analisis skenario menggunakan kotak drop-down validasi data, ikuti langkah-langkah ini:

1. Ambil model yang telah didownload dan potong dan tempelkan deskripsi dari kolom C ke kolom F. Anda dapat melakukan ini dengan menyoroti sel C6: C8, menekan Ctrl + X, memilih sel F6, dan menekan Enter.

   Masukan pada sel B3 ke B8 adalah rentang aktif yang mendorong model dan akan tetap demikian. Namun, mereka perlu menjadi formula yang berubah tergantung pada kotak drop-down yang akan Anda buat.

2. Salin rentang pada kolom B ke kolom C, D, dan E.

   Anda dapat melakukan ini dengan menyoroti B3: B8, menekan Ctrl + C, memilih sel C3: E3, dan menekan Enter. Jumlah ini akan sama untuk setiap skenario sampai Anda mengubahnya.

3. Di baris 2 masukkan judul Kasus Terbaik , Kasus Dasar , dan Kasus Terburuk.

   

   Menyiapkan model untuk analisis skenario.

   Perhatikan bahwa rumus masih link ke input di kolom B, seperti yang dapat Anda lihat dengan memilih sel C12 dan menekan tombol pintas F2.

4. Edit input di bawah setiap skenario.

   Anda dapat memasukkan apapun yang Anda pikir mungkin terjadi, namun untuk mencocokkan nomor dengan angka di contoh ini, masukkan nilainya. Abaikan kolom B untuk saat ini.

   

   Masukan untuk analisis skenario.

   Sekarang Anda perlu menambahkan kotak drop-down di bagian atas, yang akan mengemudikan skenario Anda. Tidak masalah di mana tepatnya Anda meletakkan kotak drop-down, tapi seharusnya berada di lokasi yang mudah ditemukan, biasanya di bagian atas halaman.

5. Di sel E1, masukkan judul Skenario .
6. Pilih sel F1, dan ubah pemformatannya menjadi masukan sehingga pengguna dapat melihat bahwa sel ini dapat diedit.

   Cara termudah untuk melakukannya adalah dengan mengikuti langkah-langkah ini:

   1. Klik salah satu sel yang sudah diformat sebagai masukan, seperti sel E3.
   2. Tekan ikon Format Painter di bagian Clipboard di sisi kiri tab Home. Kursor Anda akan berubah menjadi kuas cat.
   3. Pilih sel F1 untuk menempelkan formatnya.

      Format Painter biasanya digunakan sekali. Setelah Anda memilih sel, kuas akan hilang dari kursor. Jika Anda ingin Format Painter menjadi "lengket" dan berlaku untuk beberapa sel, klik dua kali ikon saat Anda memilihnya dari tab Home.

7. Sekarang, di sel F1, pilih Validasi Data dari bagian Data Tools pada tab Data.

   Kotak dialog Validasi Data muncul.

8. Pada tab Settings, ubah Allow drop-down to List, gunakan mouse untuk memilih range = $ C $ 2: $ E $ 2, dan klik OK.

   

   Membuat skenario drop-down validasi data.
9. Klik kotak drop-down, yang sekarang muncul di sebelah sel F1, dan pilih salah satu skenario (misalnya, Base Case).

Menerapkan formula ke skenario

Sel di kolom B masih mengemudikan model, dan ini perlu diganti dengan formula. Sebelum menambahkan formula, Anda harus mengubah format sel dalam kisaran untuk menunjukkan bahwa itu mengandung formula, bukan kode dengan kode keras. Ikuti langkah-langkah ini:

1. Pilih sel B3: B8, dan pilih Fill Color dari grup Font pada tab Home.
2. Ubah Warna Isi menjadi latar belakang putih.

   Sangat penting untuk membedakan antara formula dan sel masukan dalam model. Anda perlu menjelaskan kepada pengguna mana pun yang membuka model bahwa sel dalam rentang ini mengandung formula dan tidak boleh diganti.

Sekarang Anda perlu mengganti nilai kode keras di kolom B dengan formula yang akan berubah saat kotak drop-down berubah. Anda bisa melakukan ini dengan menggunakan sejumlah fungsi yang berbeda; sebuah HLOOKUP, sebuah pernyataan IF bersarang, IFS, dan SUMIF semuanya akan melakukan triknya. Tambahkan rumus dengan mengikuti langkah-langkah berikut:

1. Pilih sel B3, dan tambahkan formula yang akan mengubah nilainya tergantung pada apa yang ada di sel F1.

   Inilah rumusnya di bawah pilihan yang berbeda:

   • = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, 2, 0)

     Perhatikan bahwa dengan solusi ini, Anda perlu mengubah nomor indeks baris dari 2 sampai 3 dan seterusnya saat Anda menyalin rumus ke bawah. Sebagai gantinya, Anda bisa menggunakan fungsi ROW di bidang ketiga seperti ini: = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, ROW (A3) -1, 0)

   • = IF ($ F $ 1 = $ C $ 2, C3, IF ($ F $ 1 = $ D $ 2, D3, E3))
   • = IFS ($ F $ 1 = $ C $ 2, C3, $ F $ 1 = $ D $ 2, D3, $ F $ 1 = $ E $ 2, E3)
   • = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)

     Seperti biasa, ada beberapa pilihan yang berbeda untuk dipilih dan solusi terbaik adalah yang adalah yang paling sederhana dan mudah dipahami.Salah satu fungsi ini akan menghasilkan hasil yang persis sama, namun harus mengubah nomor indeks baris di HLOOKUP tidak kuat, dan menambahkan ROW mungkin membingungkan bagi pengguna. Pernyataan IF yang disarangkan rumit untuk dibangun dan diikuti, dan walaupun fungsi IFS yang baru dirancang untuk membuat fungsi JIKA bersarang lebih sederhana, ini masih agak berat. SUMIF cukup mudah untuk dibangun dan diikuti, dan mudah diperluas jika Anda perlu menambahkan skenario ekstra di masa mendatang.

     Perhatikan bahwa IFS adalah fungsi baru yang hanya tersedia dengan Office 365 dan Excel 2016 atau yang lebih baru yang diinstal. Jika Anda menggunakan fungsi ini dan seseorang membuka model ini di versi Excel sebelumnya, dia dapat melihat rumusnya, namun dia tidak dapat mengeditnya.

2. Salin rumus di sel B3 ke kolom.

   

   Analisis skenario selesai.

   Dengan menggunakan copy dan paste biasa, Anda akan kehilangan semua format Anda. Penting untuk mempertahankan pemformatan model sehingga Anda dapat melihat sekilas masukan mana yang ada dalam nilai dolar, persentase, atau nomor pelanggan. Gunakan Paste Rumus untuk mempertahankan format. Anda dapat mengaksesnya dengan menyalin sel ke clipboard, menyoroti rentang tujuan, mengklik kanan, dan memilih ikon Tempel Formula untuk menempelkan formula saja, dan membiarkan format tetap utuh.

   Sekarang untuk bagian yang menyenangkan! Saatnya untuk menguji fungsionalitas skenario dalam model.

3. Klik sel F1, ganti kotak drop-down, dan perhatikan output model berubah saat Anda beralih di antara berbagai skenario.