Beralih di antara berbagai kumpulan nilai dengan menggunakan skenario

Ada tiga jenis alat analisis What-If yang disertakan dengan Excel: Skenario, Tabel Data dan Pencarian Tujuan. Skenario dan Tabel Data mengambil sekumpulan nilai input dan melanjutkan proyek untuk menentukan kemungkinan hasil. Pencarian Tujuan berbeda dari Skenario dan Tabel Data karena dibutuhkan hasil dan proyek mundur untuk menentukan kemungkinan nilai input yang menghasilkan hasil tersebut.

Setiap skenario dapat mengakomodasi hingga 32 nilai variabel. Jika Anda ingin menganalisis lebih dari 32 nilai, dan nilai hanya mewakili satu atau dua variabel, Anda bisa menggunakan Tabel Data. Meskipun terbatas hanya satu atau dua variabel (satu untuk sel input baris dan satu untuk sel input kolom), Tabel Data bisa menyertakan nilai variabel yang berbeda seperti yang Anda inginkan. Skenario bisa memiliki maksimal 32 nilai berbeda, tapi Anda bisa membuat skenario sebanyak yang Anda inginkan.

Selain tiga alat ini, Anda bisa menginstal add-in yang membantu Anda melakukan Analisis What-If, seperti add-in Solver. Add-in Solver mirip dengan Pencarian Tujuan, tetapi dapat menampung lebih banyak variabel. Anda juga dapat membuat perkiraan menggunakan pemegang isi dan berbagai perintah yang ada dalam Excel. Untuk model tingkat lanjut lainnya, Anda dapat menggunakan add-in Analysis ToolPak.

Misalkan Anda ingin membuat anggaran tetapi tidak pasti pendapatan Anda. Dengan menggunakan skenario, Anda bisa menentukan nilai yang mungkin berbeda untuk pendapatan lalu beralih antar skenario untuk melakukan analisis bagaimana-jika.

Misalnya, anggaplah skenario anggaran kasus terburuk Anda adalah Pendapatan Kotor $50.000 dan Biaya Barang Terjual sebesar $13.200, meninggalkan $36.800 dalam Laba Kotor. Untuk menentukan kumpulan nilai ini sebagai skenario, masukkan nilai dalam lembar kerja terlebih dahulu, seperti yang diperlihatkan dalam ilustrasi berikut:

Sel yang diubah memiliki nilai yang Anda ketikkan, sementara sel Hasil berisi rumus yang didasarkan pada Sel yang diubah (dalam sel ilustrasi ini B4 memiliki rumus =B2-B3).

Anda lalu menggunakan dialog Manajer Skenario untuk menyimpan nilai ini sebagai skenario. Goto tab Data > What-If Analysis > Scenario Manager > Tambahkan.

Dalam dialog Nama skenario , beri nama skenario Kasus Terburuk, dan tentukan bahwa sel B2 dan B3 adalah nilai yang berubah di antara skenario. Jika Anda memilih Mengubah sel di lembar kerja Anda sebelum menambahkan Skenario, Manajer Skenario akan secara otomatis menyisipkan sel untuk Anda, jika tidak, Anda bisa mengetiknya dengan tangan, atau menggunakan dialog pilihan sel di sebelah kanan kotak dialog Mengubah sel.

Proteksi – Anda juga bisa memproteksi skenario Anda, jadi di bagian Proteksi periksa opsi yang Anda inginkan, atau hapus centang jika Anda tidak menginginkan perlindungan apa pun.

• Pilih Cegah Perubahan untuk mencegah pengeditan skenario saat lembar kerja diproteksi.

• Pilih Tersembunyi untuk mencegah menampilkan skenario saat lembar kerja diproteksi.

Sekarang anggaplah skenario anggaran Kasus Terbaik Anda adalah Pendapatan Kotor $150.000 dan Biaya Barang Terjual sebesar $26.000, meninggalkan $124.000 dalam Laba Kotor. Untuk menentukan kumpulan nilai ini sebagai skenario, buat skenario lain, beri nama Best Case, dan masukkan nilai yang berbeda untuk sel B2 (150.000) dan sel B3 (26.000). Karena Laba Kotor (sel B4) adalah rumus - perbedaan antara Pendapatan (B2) dan Biaya (B3) - Anda tidak mengubah sel B4 untuk skenario Kasus Terbaik.

Setelah Anda menyimpan skenario, skenario akan tersedia dalam daftar skenario yang bisa Anda gunakan dalam analisis bagaimana-jika Anda. Mengingat nilai dalam ilustrasi sebelumnya, jika Anda memilih untuk menampilkan skenario Kasus Terbaik, nilai dalam lembar kerja akan berubah menyerupai ilustrasi berikut ini:

Mungkin ada saat-saat ketika Anda memiliki semua informasi dalam satu lembar kerja atau buku kerja yang diperlukan untuk membuat semua skenario yang ingin Anda pertimbangkan. Namun, Anda mungkin ingin mengumpulkan informasi skenario dari sumber lain. Misalnya, Anda mencoba membuat anggaran perusahaan. Anda mungkin mengumpulkan skenario dari departemen yang berbeda, seperti Penjualan, Gaji, Produksi, Pemasaran, dan Hukum, karena setiap sumber ini memiliki informasi yang berbeda untuk digunakan dalam membuat anggaran.

Anda bisa mengumpulkan skenario ini ke dalam satu lembar kerja dengan menggunakan perintah Gabungkan . Setiap sumber dapat memasukkan sebanyak atau sesekali mengubah nilai sel seperti yang Anda inginkan. Misalnya, Anda mungkin ingin setiap departemen memasok proyeksi pengeluaran, tetapi hanya membutuhkan proyeksi pendapatan dari beberapa.

Saat Anda memilih untuk menggabungkan, Manajer Skenario akan memuat panduan Skenario Gabungan, yang akan mencantumkan semua lembar kerja dalam buku kerja aktif, serta mencantumkan buku kerja lain yang mungkin telah Anda buka pada saat itu. Panduan akan memberi tahu Anda berapa banyak skenario yang Anda miliki di setiap lembar kerja sumber yang Anda pilih.

Saat Anda mengumpulkan skenario yang berbeda dari berbagai sumber, Anda harus menggunakan struktur sel yang sama di setiap buku kerja. Misalnya, Pendapatan mungkin selalu masuk ke sel B2 dan Pengeluaran mungkin selalu masuk ke sel B3. Jika Anda menggunakan struktur yang berbeda untuk skenario dari berbagai sumber, akan sulit untuk menggabungkan hasil.

Untuk membandingkan beberapa skenario, Anda bisa membuat laporan yang meringkasnya di halaman yang sama. Laporan bisa mencantumkan skenario secara berdampingan atau menyajikannya dalam laporan PivotTable.

Laporan ringkasan skenario berdasarkan dua contoh skenario sebelumnya akan terlihat seperti berikut ini:

Anda akan melihat bahwa Excel telah menambahkan tingkat Pengelompokan secara otomatis untuk Anda, yang akan memperluas dan menciutkan tampilan saat Anda mengklik pemilih yang berbeda.

Catatan muncul di akhir laporan ringkasan yang menjelaskan bahwa kolom Nilai Saat Ini mewakili nilai perubahan sel pada saat Laporan Ringkasan Skenario dibuat, dan sel yang berubah untuk setiap skenario disorot dalam warna abu-abu.