Bagaimana Membina Senario Drop-Down dalam Model Kewangan Anda - dummies

Senario bangunan kaedah yang paling biasa digunakan ialah menggunakan gabungan formula dan kotak lungsur. Dalam model kewangan, anda membuat jadual kemungkinan senario dan input mereka dan hubungkan nama senario ke kotak drop-down sel input. Input model dikaitkan dengan jadual senario. Jika model telah dibina dengan betul dengan semua input yang mengalir ke output, maka hasil model akan berubah apabila pengguna memilih pilihan yang berbeza dari kotak drop-down.

Kotak drop-down pengesahan data digunakan untuk beberapa tujuan yang berbeza dalam pemodelan kewangan, termasuk analisis senario.

Menggunakan pengesahan data untuk senario model keuntungan

Muat turun Fail 0801. xlsx. Buka dan pilih tab berlabel 8-1-start.

Cara ini telah dimodelkan, inputnya dibariskan dalam lajur B. Anda boleh melakukan analisis kepekaan hanya dengan mengubah salah satu input - contohnya, menukar pelanggan per operator panggilan dalam sel B3 dari 40 hingga 45, dan anda akan melihat semua perubahan bergantung kepada nombor. Ini akan menjadi analisis sensitiviti, kerana anda hanya mengubah satu pemboleh ubah. Sebaliknya, anda akan mengubah beberapa pemboleh ubah sekaligus dalam senario analisis senario penuh ini, jadi anda perlu melakukan lebih daripada menala beberapa nombor secara manual.

Untuk melakukan analisis senario menggunakan kotak lungsur pengesahan data, ikuti langkah berikut:

1. Ambil model yang dimuat turun dan potong dan tampal penerangan dari ruangan C hingga lajur F. Anda boleh melakukan ini dengan menonjolkan sel C6: C8, menekan Ctrl + X, memilih sel F6, dan menekan Enter.

   Input dalam sel B3 hingga B8 adalah julat aktif yang memacu model dan akan tetap demikian. Walau bagaimanapun, mereka perlu menjadi formula yang berubah bergantung pada kotak drop-down yang akan anda buat.

2. Salin julat di lajur B merentas ke lajur C, D, dan E.

   Anda boleh melakukan ini dengan menonjolkan B3: B8, menekan Ctrl + C, memilih sel C3: E3, dan menekan Enter. Jumlah ini akan sama untuk setiap senario sehingga anda mengubahnya.

3. Dalam baris 2 masukkan tajuk Kes Kasut , Kes Pangkalan , dan Kes Terburuk.

   

   Menyediakan model untuk analisis senario.

   Perhatikan bahawa formula masih memaut ke input dalam lajur B, seperti yang anda lihat dengan memilih sel C12 dan menekan kekunci pintasan F2.

4. Edit input di bawah setiap senario.

   Anda boleh meletakkan apa yang anda fikir mungkin, tetapi untuk memadankan nombor-nombor kepada mereka dalam contoh ini, masukkan nilai-nilai. Abaikan ruangan B buat masa sekarang.

   

   Input untuk analisis senario.

   Sekarang anda perlu menambah kotak drop-down di bahagian atas, yang akan memandu senario anda. Ia tidak semestinya di mana anda meletakkan kotak drop-down, tetapi ia harus berada di lokasi yang mudah dicari, biasanya di bahagian atas halaman.

5. Dalam sel E1, masukkan tajuk Skenario .
6. Pilih sel F1, dan ubah format ke input supaya pengguna dapat melihat bahawa sel ini dapat diedit.

   Cara paling mudah untuk melakukannya ialah dengan mengikuti langkah berikut:

   1. Klik satu daripada sel yang sudah diformat sebagai input, seperti sel E3.
   2. Tekan ikon Penapis Format di bahagian Clipboard di sebelah kiri tab Laman Utama. Kursor anda akan berubah menjadi kuas.
   3. Pilih sel F1 untuk tampal pemformatan.

      Format Painter biasanya untuk penggunaan tunggal. Selepas anda memilih sel, berus cat akan hilang dari kursor. Jika anda mahu Format Painter menjadi "melekit" dan memohon kepada berbilang sel, klik dua kali ikon apabila anda memilihnya dari tab Rumah.

7. Sekarang, dalam sel F1, pilih Pengesahan Data dari bahagian Alat Data pada tab Data.

   Peti dialog Pengesahan Data muncul.

8. Pada tab Tetapan, tukar turun-turun Benarkan ke Senarai, gunakan tetikus untuk memilih julat = $ C $ 2: $ E $ 2, dan klik OK.

   

   Mencipta senario drop-down validasi data.
9. Klik kotak lungsur, yang kini muncul bersebelahan dengan sel F1, dan pilih satu daripada senario (sebagai contoh, Kes Pangkalan).

Memohon formula kepada senario

Sel dalam ruang B masih memandu model, dan ini perlu diganti dengan formula. Sebelum anda menambahkan formula, bagaimanapun, anda perlu mengubah pemformatan sel-sel dalam julat untuk menunjukkan bahawa ia mengandungi formula, bukannya nombor kod berkod keras. Ikuti langkah berikut:

1. Pilih sel B3: B8, dan pilih Warna Isi daripada kumpulan Fon pada tab Laman Utama.
2. Tukar Warna Isi ke latar belakang putih.

   Sangat penting untuk membezakan antara formula dan sel input dalam model. Anda perlu menjelaskan kepada mana-mana pengguna yang membuka model bahawa sel-sel dalam julat ini mengandungi formula dan tidak boleh ditindih.

Sekarang anda perlu menggantikan nilai-nilai yang berkod keras dalam lajur B dengan formula yang akan berubah apabila kotak drop-down berubah. Anda boleh melakukan ini menggunakan beberapa fungsi yang berbeza; sebuah HLOOKUP, satu kenyataan tersarang, IFS, dan SUMIF semua akan melakukan tipu helah itu. Tambahkan formula dengan mengikuti langkah berikut:

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

   Berikut adalah formula yang ada di bawah pilihan yang berlainan:

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

     Perhatikan bahawa dengan penyelesaian ini, anda perlu menukar nombor indeks baris dari 2 hingga 3 dan sebagainya semasa anda menyalin formula ke bawah. Sebaliknya, anda boleh menggunakan fungsi ROW dalam 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 = $ E $ 2, E3)
   • = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)

     Seperti biasa, terdapat beberapa pilihan yang berbeza untuk dipilih dan penyelesaian terbaik ialah adalah yang paling mudah dan mudah difahami.Mana-mana fungsi ini akan menghasilkan keputusan yang sama, tetapi perlu mengubah nombor indeks baris dalam HLOOKUP tidak teguh, dan menambah ROW mungkin mengelirukan untuk pengguna. Pernyataan IF bersarang adalah rumit untuk membina dan mengikuti, dan walaupun fungsi IFS baru direka untuk membuat fungsi JIKA bersarang lebih mudah, ia masih agak sukar dikawal. SUMIF agak mudah untuk dibina dan diikuti, dan mudah untuk berkembang jika anda perlu menambahkan senario tambahan pada masa akan datang.

     Perhatikan bahawa IFS adalah fungsi baru yang hanya boleh didapati dengan Office 365 dan Excel 2016 atau kemudian dipasang. Sekiranya anda menggunakan fungsi ini dan seseorang membuka model ini dalam Excel versi terdahulu, dia boleh melihat formula itu, tetapi dia tidak akan dapat mengeditnya.

2. Salin formula dalam sel B3 ke bawah lajur.

   

   Analisis senario yang lengkap.

   Dengan menggunakan salinan biasa dan paste, anda akan kehilangan semua pemformatan anda. Penting untuk mengekalkan pemformatan model supaya anda dapat melihat sekilas pandang yang input dalam nilai dolar, peratusan, atau nombor pelanggan. Gunakan Formula Tampal untuk mengekalkan pemformatan. Anda boleh mengaksesnya dengan menyalin sel ke papan klip, menonjolkan julat destinasi, mengklik kanan, dan memilih ikon Formula Tampalkan untuk memasukkan formula sahaja, dan biarkan pemformatan itu utuh.

   Sekarang untuk bahagian yang menyeronokkan! Sudah tiba masanya untuk menguji fungsi senario dalam model.

3. Klik sel F1, ubah kotak drop-down, dan tonton perubahan output model semasa anda bertukar-tukar antara senario yang berbeza.