Analisis What-If di Excel: Goal Seek, Scenario Manager, dan Data Tables

Anda memberikan tiga hal:

• Set cell: sel yang berisi rumus Anda (output)
• To value: hasil target yang Anda inginkan
• By changing cell: sel input yang boleh disesuaikan Excel

Mari kita lihat praktiknya. 

Anda menjalankan bisnis kecil yang menjual buku catatan. Pendapatan Anda saat ini $25,000, tetapi targetnya $50,000. Harganya tetap $25, jadi Anda perlu mengetahui berapa banyak unit yang harus dijual. 

Berikut caranya:

1. Buka Data > What-If Analysis > Goal Seek

2. Pada Set cell, klik sel B4 (sel rumus pendapatan Anda)

3. Pada To value, ketik 50000

4. Pada By changing cell, pilih B2 (Units Sold)

5. Klik OK

Goal Seek menyesuaikan B2 dari 1.000 menjadi 2.000 unit — jumlah tepat yang dibutuhkan untuk mencapai pendapatan $50.000 pada harga $25 per unit.

Menggunakan Goal Seek untuk mencapai hasil tertentu. Gambar oleh Penulis.

Cara menggunakan Scenario Manager di Excel

Scenario Manager adalah alat Analisis What-If yang memungkinkan Anda menyimpan dan beralih di antara beberapa set nilai input dalam spreadsheet yang sama. Setiap versi yang disimpan disebut skenario. 

Anda dapat membuat sebanyak yang Anda perlukan dan berpindah di antaranya seketika untuk membandingkan hasil. 

Ini berguna saat Anda ingin merencanakan hasil skenario terbaik, terburuk, dan yang diharapkan tanpa menyimpan tiga file terpisah. 

Anda memberikan tiga hal:

• Scenario name: label untuk setiap set asumsi
• Changing cells: sel input yang berbeda antar skenario
• Values: angka spesifik untuk setiap skenario

Membuat skenario

Misalkan Anda merencanakan penjualan triwulanan untuk bisnis buku catatan Anda.

Anggaplah laba Anda saat ini $15,000. Anda ingin memodelkan tiga skenario: terbaik, terburuk, dan yang diharapkan. 

Berikut cara membuat skenario:

1. Buka Data > What-If Analysis > Scenario Manager

2. Di kotak dialog Scenario Manager, klik Add

3. Pada Scenario name, ketik Best Case.

4. Pada Changing cells, pilih B2:B4.

5. Kotak dialog Scenario Value akan muncul. Pada kolom yang tersedia, masukkan nilai berikut:

• B2 = 2000, 

• B3 = $30

• B4= $10

6. Klik OK. 

Ulangi langkah ini untuk dua skenario lainnya menggunakan nilai di bawah ini:

Buat skenario dengan tiga kasus. Gambar oleh Penulis.

Untuk beralih antar skenario, pilih salah satu dari daftar dan klik Show. Excel langsung menukar nilainya, dan laba Anda diperbarui.

Klik Show untuk melihat nilai setiap skenario. Gambar oleh Penulis.

Mengedit skenario

Pilih skenario apa pun dari daftar dan klik Edit. Anda dapat mengganti nama, mengubah sel yang dicakup, atau memperbarui nilainya. Tidak ada yang berubah di spreadsheet Anda sampai Anda mengklik Show

Membuat laporan skenario

Scenario Manager juga dapat membuat laporan yang membandingkan semua skenario tersimpan secara berdampingan. 

Untuk membuat laporan skenario:

1. Di dalam Scenario Manager, klik Summary

2. Pada Scenario Summary, pilih Scenario Summary

3. Pada Result cells, pilih B7 (sel laba Anda)

4. Klik OK

Excel membuat tabel ringkasan pada lembar baru yang menampilkan ketiga skenario secara berdampingan

Buat laporan ringkasan skenario. Gambar oleh Penulis.

Cara menggunakan Data Tables di Excel

Data Tables mengotomatiskan perhitungan berulang di berbagai rentang nilai input. Alih-alih mengubah input secara manual, mencatat output, dan mengulanginya, Anda mendefinisikan rentangnya sekali, lalu Excel mengisi semua hasil sekaligus.

Anda memberikan tiga hal:

• Input range: nilai yang ingin Anda uji
• Row/column input cell: sel dalam rumus Anda yang digantikan oleh nilai tersebut
• Formula cell: output yang menjadi dasar tabel Anda

Data Tables satu variabel

One-Variable Data Table menguji bagaimana perubahan satu input memengaruhi hasil rumus.

Misalnya, Anda memiliki rumus pembayaran pinjaman dan ingin melihat bagaimana pembayaran bulanan berubah pada tingkat bunga dari 5% hingga 10%.

Untuk membuat One-Variable Data Table:

1. Siapkan dataset Anda. Satu hal yang perlu diperhatikan: dalam dataset saya, pada sel E1, tepat di atas tingkat pertama Anda, bukan di samping header kolom, saya memasukkan =B5. Jika E1 dibiarkan kosong, atau referensi rumus dimasukkan ke sel lain, data table akan berjalan tetapi mengembalikan semua nol alih-alih nilai pembayaran.

Buat One-Variable Data Table. Gambar oleh Penulis.

2. Selanjutnya, pilih seluruh rentang D1:E9 (termasuk E1)

3. Buka Data > What-If Analysis > Data Table

4. Biarkan Row input cell kosong

5. Pada Column input cell, pilih B4 (tingkat bunga)

6. Klik OK.

Excel mengisi tabel dengan hasil yang dihitung untuk setiap nilai input. Perhatikan bahwa pembayaran tidak meningkat secara merata. Lonjakan dari 3% ke 4% adalah $49, tetapi dari 9% ke 10% sebesar $61. Perubahan tingkat bunga semakin terasa dampaknya saat nilainya lebih tinggi.

Hasil yang dihitung untuk setiap nilai input. Gambar oleh Penulis.

Data Tables dua variabel

Two-variable data table menguji bagaimana perubahan dua input secara bersamaan memengaruhi hasil rumus.

Misalnya, model penetapan harga mungkin bergantung pada harga produk dan unit terjual. Two-variable data table dapat menampilkan laba di berbagai titik harga dan volume penjualan dalam satu tampilan.

Pertama, siapkan dataset. 

Lihat dataset yang saya gunakan:

• Pada sel D1, masukkan =B5. Ini adalah referensi rumus, dan harus ditempatkan di sudut kiri atas grid, bukan di samping label

• Sepanjang baris 1, mulai dari E1, daftarkan nilai unit terjual yang ingin Anda uji: 500, 1000, 1500, 2000

• Ke bawah kolom D, mulai dari D2, daftarkan titik harga yang ingin Anda uji: $20, $25, $30, $35

Siapkan dataset untuk Two-Variable Data Table. Gambar oleh Penulis.

Untuk membuat Two-Variable Data Table:

1. Pilih seluruh rentang D1:H5.

2. Buka Data > What-If Analysis > Data Table

3. Pada Row input cell, pilih B3 (unit terjual)

4. Pada Column input cell, pilih B2 (harga per unit)

5. Klik OK.

Excel mengisi laba untuk setiap kombinasi harga dan unit terjual

Hasil yang dihitung untuk nilai input. Gambar oleh Penulis.

Two-Variable Data Table sangat membantu ketika satu asumsi saja tidak memberikan gambaran lengkap. Harga mungkin terlihat baik sendiri, tetapi laba juga bergantung pada berapa banyak unit yang benar-benar dibeli pelanggan. Variabel kedua inilah yang membuat model menjadi lebih berguna.

Goal Seek vs. Scenario Manager vs. Data Tables

Goal Seek, Scenario Manager, dan Data Tables semuanya termasuk dalam perangkat Analisis What-If Excel, tetapi mereka menyelesaikan masalah yang berbeda. 

Berikut perbandingan singkat di antara mereka 

Analisis What-If vs. Solver

Analisis What-If dan Solver sama-sama membantu kita mengeksplorasi berbagai hasil di Excel, tetapi mereka menyelesaikan jenis masalah yang berbeda.

• Analisis What-If menguji asumsi. Anda mengubah satu atau lebih input dan melihat bagaimana perubahan tersebut memengaruhi hasil. Tujuannya adalah memahami kemungkinan hasil, bukan mencari yang terbaik.
• Solver bekerja secara berbeda. Anda memberinya tujuan, seperti memaksimalkan laba atau meminimalkan biaya, dan ia mencari kombinasi input yang paling memenuhi tujuan tersebut. Ini bukan menguji satu asumsi. Ini menyelesaikan untuk yang optimal. 

Misalnya, pemohon pinjaman dapat menggunakan Goal Seek untuk menentukan jumlah pinjaman yang menghasilkan pembayaran bulanan $500. Namun perusahaan manufaktur dapat menggunakan Solver untuk menentukan kombinasi produksi yang menghasilkan laba tertinggi sambil tetap berada dalam batas tenaga kerja dan material. 

Catatan: Solver bukan bagian dari alat standar Analisis What-If. Ini adalah add-in terpisah. Pada sebagian besar versi Excel, Anda perlu menyalakannya secara manual di Excel Options sebelum muncul di tab Data.

Praktik Terbaik Analisis What-If

Berikut beberapa praktik terbaik saat bekerja dengan Analisis What-If: 

1. Pisahkan asumsi dari rumus

Simpan nilai input di sel khusus dan referensikan sel tersebut dalam rumus Anda. Hindari menanamkan angka langsung ke dalam perhitungan. 

Misalnya, rumus ini sulit diperbarui:

=(50*1000)-10000

Namun versi ini jauh lebih mudah dipelihara:

=(B2*B3)-B4

Saat asumsi berubah, Anda hanya memperbarui sel input alih-alih mengedit rumus di seluruh workbook.

2. Beri label skenario dengan jelas

Nama skenario harus langsung memberi tahu pembaca apa yang diwakilinya. "Scenario 1," "Scenario 2," dan "Scenario 3" tidak memberi tahu apa pun enam bulan dari sekarang. Namai sesuai apa yang sebenarnya diwakili, seperti "Best Case: Pertumbuhan 15%." 

3. Validasi input

Analisis What-If hanya seandal input yang diuji. Sebelum menjalankan analisis, pastikan bahwa:

• Persentase dimasukkan dengan benar
• Tanggal menggunakan format yang konsisten
• Satuan ukuran konsisten
• Nilai input berada dalam kisaran yang realistis

Perkiraan pendapatan berdasarkan peningkatan penjualan 500% mungkin benar secara teknis tetapi tetap tidak berguna untuk pengambilan keputusan.

4. Dokumentasikan asumsi

Model yang masuk akal bagi Anda hari ini belum tentu masuk akal bagi Anda atau orang lain dalam tiga bulan. 

Catatan singkat di samping setiap asumsi, bahkan sekadar "berdasarkan realisasi Q3," menghemat banyak tebakan di kemudian hari. Mendokumentasikannya membantu orang lain memahami dari mana angka-angka tersebut berasal dan memudahkan pembaruan di masa depan.

Masalah Umum dengan Analisis What-If

Sebagian besar masalah Analisis What-If berasal dari penyiapan lembar kerja, bukan alatnya. Jika Goal Seek, Scenario Manager, atau Data Tables menghasilkan hasil yang tidak terduga, periksa beberapa area masalah umum; biasanya itu menyelesaikan isu tersebut.

1. Rumus tidak memperbarui 

Anda mengubah input, dan tidak ada yang terjadi. Biasanya, ini berarti mode perhitungan Excel beralih ke manual di suatu titik. 

Cara memperbaikinya: buka Formulas > Calculation Options dan pastikan disetel ke Automatic. Jika sudah, tekan F9 untuk memaksa perhitungan ulang.

2. Referensi sel yang salah

Alat Analisis What-If bergantung pada referensi sel yang akurat. Jika sel yang salah dipilih, hasilnya akan keliru meskipun Excel menyelesaikan perhitungannya dengan sukses. 

Misalnya, Goal Seek mungkin mengubah sel input yang salah. Data Table mungkin mereferensikan row atau column input cell yang salah. Scenario Manager mungkin menyimpan nilai untuk sel yang salah.

Cara memperbaikinya: Tinjau sel input Anda sebelum menjalankan analisis, periksa kembali apa yang dipilih di kotak dialog setiap alat, dan gunakan label deskriptif sehingga jelas apa yang diwakili setiap sel. 

3. Salah paham tentang keterbatasan Goal Seek

Banyak pengguna mengharapkan Goal Seek menyelesaikan masalah yang melibatkan beberapa variabel yang berubah. Itu tidak bisa. Goal Seek hanya mengubah satu sel input pada satu waktu untuk mencapai nilai target. 

Misalnya, Goal Seek dapat menentukan volume penjualan yang diperlukan untuk menghasilkan laba $15.000. Ia tidak dapat secara bersamaan menyesuaikan volume penjualan, harga, dan biaya untuk mencapai target tersebut.

Cara memperbaikinya: Gunakan Goal Seek ketika hanya satu variabel yang perlu diubah. Gunakan Scenario Manager atau Solver saat melibatkan banyak variabel. 

4. Model skenario yang terlalu kompleks

Scenario Manager secara teknis memungkinkan hingga 32 sel yang diubah per skenario, tetapi bukan berarti Anda harus menggunakannya sebanyak itu. 

Lebih dari empat atau lima variabel, skenario menjadi sulit dibandingkan dan bahkan lebih sulit dijelaskan kepada orang lain. Jika model Anda membutuhkan begitu banyak komponen yang bergerak, mungkin sudah saatnya merestrukturisasi alih-alih memaksakannya dalam satu skenario.

Penutup 

Setiap spreadsheet memiliki asumsi di suatu tempat. Bisa berupa tingkat pertumbuhan, harga, atau suku bunga.

Analisis What-If tidak menghapus asumsi tersebut. Alat ini menunjukkan apa yang terjadi jika asumsi itu ternyata keliru, yang merupakan inti dari pengambilan keputusan yang baik.

Jika Anda baru memulai, jangan langsung dengan model yang memiliki lima variabel dan tiga skenario bertingkat. Bangun sesuatu yang kecil terlebih dahulu, seperti satu input, satu output, satu Goal Seek, dan pahami bagaimana input dan hasil bergerak bersama. Hal yang kompleks bisa menyusul setelah dasarnya benar-benar dipahami.