Analisis Bagaimana-Jika dengan Manajer Skenario

Manajer Skenario berguna jika Anda memiliki lebih dari dua variabel dalam analisis sensitivitas. Scenario Manager membuat skenario untuk setiap rangkaian nilai input untuk variabel yang sedang dipertimbangkan. Skenario membantu Anda menjelajahi serangkaian kemungkinan hasil, mendukung yang berikut -

• Memvariasikan sebanyak 32 set input.
• Menggabungkan skenario dari beberapa lembar kerja atau buku kerja yang berbeda.

Jika Anda ingin menganalisis lebih dari 32 set input, dan nilainya hanya mewakili satu atau dua variabel, Anda dapat menggunakan Tabel Data. Meskipun dibatasi hanya untuk satu atau dua variabel, Tabel Data dapat menyertakan sebanyak mungkin nilai input yang Anda inginkan. Lihat Analisis Bagaimana-Jika dengan Tabel Data dalam tutorial ini.

Skenario

Skenario adalah sekumpulan nilai yang disimpan Excel dan dapat diganti secara otomatis di lembar kerja Anda. Anda bisa membuat dan menyimpan grup nilai yang berbeda sebagai skenario di lembar kerja lalu beralih di antara skenario ini untuk melihat hasil yang berbeda.

Misalnya, Anda dapat memiliki beberapa skenario anggaran berbeda yang membandingkan berbagai kemungkinan tingkat pendapatan dan pengeluaran. Anda juga dapat memiliki berbagai skenario pinjaman dari berbagai sumber yang membandingkan berbagai kemungkinan tingkat suku bunga dan jangka waktu pinjaman.

Jika informasi yang ingin Anda gunakan dalam skenario berasal dari sumber berbeda, Anda bisa mengumpulkan informasi di buku kerja terpisah, lalu menggabungkan skenario dari buku kerja yang berbeda menjadi satu.

Setelah Anda memiliki semua skenario yang Anda butuhkan, Anda bisa membuat laporan ringkasan skenario -

• Itu menggabungkan informasi dari semua skenario.
• Itu memungkinkan Anda membandingkan skenario secara berdampingan.

Manajer Skenario

Scenario Manager adalah salah satu alat Analisis Bagaimana-jika di Excel.

Untuk membuat laporan analisis dengan Scenario Manager, Anda harus mengikuti langkah-langkah berikut -

Step 1 - Tentukan kumpulan nilai awal dan identifikasi sel input yang ingin Anda variasikan, yang disebut sel yang berubah.

Step 2 - Buat setiap skenario, beri nama skenario dan masukkan nilai untuk setiap sel input yang berubah untuk skenario itu.

Step 3- Pilih sel keluaran, disebut sel hasil yang ingin Anda lacak. Sel-sel ini berisi rumus di set nilai awal. Rumusnya menggunakan sel input yang berubah.

Manajer Skenario membuat laporan yang berisi masukan dan nilai keluaran untuk setiap skenario.

Nilai Awal untuk Skenario

Sebelum Anda membuat beberapa skenario berbeda, Anda perlu menentukan sekumpulan nilai awal yang akan menjadi dasar skenario.

Langkah-langkah untuk menyiapkan nilai awal Skenario adalah -

• Tentukan sel yang berisi nilai input.
• Beri nama sel input dengan tepat.
• Identifikasi sel input dengan nilai konstan.
• Tentukan nilai untuk input konstan.
• Identifikasi sel input dengan nilai yang berubah.
• Tentukan nilai awal untuk input yang berubah.
• Tentukan sel yang berisi hasil. Sel hasil berisi rumus.
• Beri nama sel hasil dengan tepat.
• Tempatkan rumus di sel hasil.

Perhatikan contoh pinjaman sebelumnya. Sekarang, lanjutkan sebagai berikut -

• Tentukan sel untuk Jumlah Pinjaman.

  • Nilai input ini konstan untuk semua skenario.

  • Beri nama sel Loan_Amount.

  • Tentukan nilainya sebagai 5.000.000.

• Tentukan sel Suku Bunga, Jumlah pembayaran dan Jenis (Pembayaran di awal atau akhir bulan).

  • Nilai masukan ini akan berubah di seluruh skenario.

  • Beri nama sel Interest_Rate, NPER dan Type.

  • Tentukan nilai awal untuk analisis dalam sel ini masing-masing sebagai 12%, 360 dan 0.

• Tentukan sel untuk EMI.

  • Ini adalah nilai hasil.

  • Beri nama sel EMI.

  • Tempatkan rumus di sel ini sebagai -

    =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

Lembar kerja Anda terlihat seperti yang ditunjukkan di bawah ini -

Seperti yang Anda lihat bahwa sel input dan sel hasil berada di kolom C dengan nama seperti yang diberikan di kolom D.

Membuat Skenario

Setelah menyiapkan nilai awal untuk Skenario, Anda dapat membuat skenario menggunakan Manajer Skenario sebagai berikut -

• Klik tab DATA di Pita.
• Klik Analisis Bagaimana-jika di grup Alat Data.
• Pilih Scenario Manager dari daftar dropdown.

Kotak Dialog Scenario Manager muncul. Anda dapat mengamati bahwa itu berisi pesan -

“No Scenarios defined. Choose Add to.”

Anda perlu membuat skenario untuk setiap kumpulan nilai yang berubah di Scenario Manager. Ada baiknya untuk menetapkan skenario pertama dengan nilai awal, karena ini memungkinkan Anda untuk beralih kembali ke nilai awal kapan pun Anda mau sambil menampilkan skenario yang berbeda.

Buat skenario pertama dengan nilai awal sebagai berikut -

• Klik Add di kotak Dialog Scenario Manager.

Itu Add Scenario kotak dialog muncul.

• Di bawah Scenario Name, ketik Scenario 1.
• Di bawah Mengubah Sel, masukkan referensi untuk sel yaitu C3, C4 dan C5 dengan menekan tombol Ctrl.

Nama kotak dialog berubah menjadi Edit Skenario.

• Edit teks di Comment as – Initial Values kotak.

• Pilih opsi Mencegah perubahan di bawah Proteksi dan kemudian klik OK.

Itu Scenario Valueskotak dialog muncul. Nilai awal yang telah Anda tentukan muncul di setiap kotak sel yang berubah.

Scenario 1 dengan nilai awal dibuat.

Buat tiga skenario lagi dengan nilai yang berbeda-beda di sel yang berubah sebagai berikut -

• Klik Add di kotak dialog Scenario Values.

Kotak dialog Add Scenario muncul. Perhatikan bahwa C3, C4, C5 muncul di kotak Mengubah sel.

• Di kotak Nama Skenario, ketik Skenario 2.

• Edit teks di Comment sebagai - Suku Bunga Berbeda.

• Pilih Cegah perubahan di bawah Proteksi dan klik OK.

Itu Scenario Valueskotak dialog muncul. Nilai awal muncul di sel yang berubah. Ubah nilaiInterest_Rate untuk 0.13 dan klik Add.

Itu Add Scenariokotak dialog muncul. Perhatikan bahwa C3, C4, C5 muncul di kotak di bawah sel yang berubah.

• Di kotak Nama Skenario, ketik Skenario 3.

• Edit teks di Commentkotak sebagai - Tidak berbeda. dari Pembayaran.

• Pilih Cegah perubahan di bawah Proteksi dan klik OK.

Kotak dialog Scenario Values ​​muncul. Nilai awal muncul di sel yang berubah. Ubah nilai NPER menjadi 300 dan klikAdd.

Itu Add Scenariokotak dialog muncul. Perhatikan bahwa C3, C4, C5 muncul di kotak Mengubah sel.

• Di kotak Nama Skenario, ketik Skenario 4.

• Edit teks di Comment box as - Jenis Pembayaran yang Berbeda.

• Pilih Cegah perubahan di bawah Proteksi dan klik OK.

Itu Scenario Valueskotak dialog muncul. Nilai awal muncul di sel yang berubah. Ubah nilai Type menjadi 1. Klik OK karena Anda telah menambahkan semua skenario yang ingin Anda tambahkan.

Itu Scenario Managerkotak dialog muncul. Di kotak di bawah Skenario, Anda akan menemukan nama semua skenario yang telah Anda buat.

• Klik Skenario 1. Seperti yang Anda ketahui, Skenario 1 berisi nilai awal.


Sekarang, klik Summary. Kotak dialog Scenario Summary (Ringkasan Skenario) muncul.

Laporan Ringkasan Skenario

Excel menyediakan dua jenis laporan Ringkasan Skenario -

• Ringkasan skenario.
• Laporan skenario PivotTable.

Dalam kotak dialog Ringkasan Skenario, Anda dapat menemukan dua jenis Laporan ini.

Pilih Ringkasan skenario di bawah Jenis laporan.

Ringkasan Skenario

Dalam Result cells kotak, pilih sel C6 (Di sini, kami telah meletakkan file PMTfungsi). Klik OK.

Laporan Ringkasan Skenario muncul di lembar kerja baru. Lembar kerja tersebut dinamai sebagai Ringkasan Skenario.

Anda dapat mengamati hal berikut dalam laporan Ringkasan Skenario -

• Changing Cells- Memasukkan semua sel yang digunakan sebagai sel pengubah. Saat Anda menamai sel, Interest_Rate, NPER dan Type, ini tampaknya membuat laporan bermakna. Jika tidak, hanya referensi sel yang akan dicantumkan.

• Result Cells - Menampilkan sel hasil yang ditentukan, yaitu EMI.

• Current Values - Ini adalah kolom pertama dan mencantumkan nilai skenario yang dipilih dalam kotak Dialog Manajer Skenario sebelum membuat laporan ringkasan.

• Untuk semua skenario yang telah Anda buat, sel yang berubah akan disorot dengan warna abu-abu.

• Di baris EMI, nilai hasil untuk setiap skenario akan ditampilkan.

Anda dapat membuat laporan lebih bermakna dengan menampilkan komentar yang Anda tambahkan saat membuat skenario.

• Klik tombol + di sebelah kiri baris yang berisi nama skenario. Komentar untuk skenario muncul di baris di bawah nama skenario.

Skenario dari Berbagai Sumber

Misalkan Anda mendapatkan skenario dari tiga sumber berbeda dan Anda perlu menyiapkan laporan ringkasan skenario dalam buku kerja Master. Anda bisa melakukan ini dengan menggabungkan skenario dari buku kerja yang berbeda ke dalam buku kerja Master. Ikuti langkah-langkah yang diberikan di bawah ini -

• Asumsikan bahwa skenario ada di buku kerja, skenario Bank1, skenario bank2 dan skenario bank3. Buka tiga workbook.

• Buka buku kerja Master, di mana Anda memiliki nilai awal.

• Klik DATA> Analisis Bagaimana-jika> Manajer Skenario di buku kerja Master.

Itu Scenario Manager Kotak dialog muncul.

Seperti yang dapat Anda amati, tidak ada skenario karena Anda belum menambahkannya. KlikMerge.

Kotak dialog Gabungkan Skenario muncul.

Seperti yang Anda lihat, di bawah Gabungkan skenario dari, Anda memiliki dua kotak -

• Book
• Sheet

Anda bisa memilih lembar kerja tertentu dari buku kerja tertentu yang berisi skenario, yang ingin Anda tambahkan ke hasil Anda. Klik panah tarik-turun dariBook untuk melihat buku kerja.

Note - Buku kerja yang sesuai harus terbuka untuk muncul di daftar ini.

Pilih buku - Bank1_Scenarios.

Lembar Bank1 ditampilkan. Di bagian bawah kotak dialog, jumlah skenario yang ditemukan di lembar sumber ditampilkan. Klik OK.

Kotak dialog Scenario Manager muncul. Dua skenario yang digabungkan ke dalam buku kerja Master akan dicantumkan di bawah Skenario.

Klik Mergetombol. ItuMerge Scenarioskotak dialog muncul. Sekarang, pilihBank2_Scenarios dari daftar drop-down di kotak Book.

Lembar bank2 ditampilkan. Di bagian bawah kotak dialog, jumlah skenario yang ditemukan di lembar sumber ditampilkan. Klik OK.

Itu Scenario ManagerKotak dialog muncul. Empat skenario yang digabungkan ke dalam buku kerja Master dicantumkan di bawah Skenario.

Klik Mergetombol. ItuMerge Scenarioskotak dialog muncul. Sekarang, pilihBank3_Scenarios dari daftar drop-down di kotak Book.

Lembar bank3 ditampilkan. Di bagian bawah kotak dialog, jumlah skenario yang ditemukan pada lembar sumber akan ditampilkan. Klik OK.

Kotak Dialog Scenario Manager muncul. Lima skenario yang digabungkan ke dalam buku kerja Master akan dicantumkan di bawah Skenario.

Sekarang, Anda memiliki semua skenario yang diperlukan untuk menghasilkan laporan ringkasan Skenario.

Klik tombol Ringkasan. ItuScenario Summary kotak dialog muncul.

• Pilih Ringkasan skenario.
• Di kotak Sel hasil, ketik C6 dan klik OK.

Laporan ringkasan skenario muncul di lembar kerja baru di buku kerja Master.

Menampilkan Skenario

Misalkan Anda mempresentasikan skenario Anda dan Anda ingin beralih secara dinamis dari satu skenario ke skenario lainnya dan menampilkan kumpulan nilai input dan nilai hasil dari skenario yang sesuai.

• Klik DATA> Analisis Bagaimana-jika> Manajer Skenario dari grup Alat Data. Kotak Dialog Scenario Manager muncul. Daftar skenario muncul.

• Pilih skenario yang ingin Anda tampilkan. KlikShow.

Nilai pada lembar kerja diperbarui dengan skenario yang dipilih. Nilai hasil dihitung ulang.

Laporan PivotTable Skenario

Anda juga bisa melihat laporan Skenario dalam bentuk PivotTable.

• Klik tombol Ringkasan di Scenario ManagerKotak dialog. Kotak dialog Scenario Summary (Ringkasan Skenario) muncul.

• Pilih Scenario PivotTable report di bawah Jenis laporan.

• Ketik C6 di Result cells kotak.

Laporan skenario PivotTable muncul di lembar kerja baru.