Cara Menggunakan Goal Seek di Excel: Panduan dengan Contoh Nyata

Cara kerja Goal Seek

Goal Seek digunakan ketika Anda mengetahui hasil yang diinginkan dari sebuah rumus tetapi masih menentukan nilai input yang diperlukan untuk mencapainya. Anda bekerja secara mundur dengan menetapkan hasil yang diinginkan dan membiarkan Excel menyesuaikan variabel input untuk menemukan solusinya.

Aplikasi umum Goal Seek

Meskipun Goal Seek digunakan di berbagai bidang atau domain, penggunaan paling umum adalah dalam pemodelan keuangan dan skenario akademik. Beberapa aplikasinya antara lain:

• Perkiraan Anggaran: Goal Seek membantu menentukan berapa banyak tabungan atau investasi yang diperlukan untuk mencapai tujuan keuangan. Misalnya, jika Anda ingin menabung sejumlah tertentu pada akhir tahun, Goal Seek dapat menghitung tabungan bulanan yang diperlukan untuk mencapai tujuan tersebut.
• Pembayaran Pinjaman: Alat ini dapat menghitung jumlah pinjaman yang mampu Anda tanggung berdasarkan pembayaran bulanan tetap. Jika Anda memiliki jumlah tertentu yang bisa dibayar setiap bulan, Goal Seek dapat menyesuaikan pokok pinjaman untuk melihat seberapa besar Anda bisa meminjam.
• Analisis Titik Impas: Bisnis dapat menggunakan Goal Seek untuk menemukan volume penjualan yang diperlukan untuk menutup biaya dan mencapai titik impas. Dengan menetapkan laba menjadi nol, Goal Seek dapat menyesuaikan jumlah penjualan atau harga untuk menentukan titik impas.
• Perhitungan Nilai: Siswa dan pendidik dapat menggunakan Goal Seek untuk menentukan nilai yang diperlukan pada ujian akhir guna mencapai nilai mata kuliah yang diinginkan. Misalnya, jika seorang siswa ingin mengetahui skor yang dibutuhkan pada ujian akhir untuk memperoleh nilai keseluruhan 85%, Goal Seek dapat menghitungnya berdasarkan nilai saat ini dan bobot ujian akhir.
• Target IPK: Goal Seek juga dapat menentukan nilai yang diperlukan pada mata kuliah mendatang untuk mencapai atau mempertahankan IPK tertentu. Misalnya, jika seorang siswa ingin menaikkan IPK ke tingkat tertentu, Goal Seek dapat menentukan nilai yang diperlukan pada mata kuliah berikutnya untuk memenuhi target tersebut.

Sekarang mari jelajahi contoh nyata di Microsoft Excel.

Cara Menggunakan Goal Seek di Excel

Menerapkan Goal Seek pada model data Anda terutama melibatkan tiga langkah berulang.

Langkah 1: Menyiapkan data Anda

Mulailah dengan menyiapkan data dan rumus Anda agar dapat ditafsirkan oleh Excel. Pastikan rumus yang ingin Anda selesaikan tertaut dengan benar ke sel input yang akan disesuaikan.

Data contoh untuk perhitungan Goal Seek. Gambar oleh Penulis.

Menurut rumus pada gambar di atas, karena Hasil C bergantung pada Input A dan Input B, kita bisa mengubah Input A atau B.

Langkah 2: Menjalankan Goal Seek

Pertama, pilih sel dengan rumus yang hasilnya ingin Anda sesuaikan (misalnya, C2). Buka tab Data, klik What-If Analysis, lalu pilih Goal Seek.

Memilih Goal Seek di Excel. Gambar oleh Penulis.

Di dialog Goal Seek, tentukan sel yang berisi rumus (Set cell), hasil yang diinginkan (To value), dan sel input yang akan diubah (By changing cell).

Kotak dialog Goal Seek. Gambar oleh Penulis.

Klik OK agar Excel menemukan nilai input yang sesuai.

Langkah terakhir Goal Seek. Gambar oleh Penulis.

Sebagai alternatif, Anda dapat klik Cancel untuk membatalkan operasi Goal Seek jika ingin mempertahankan nilai asli. Ini umum terjadi saat kita tidak puas dengan perubahannya dan ingin menjalankan operasi Goal Seek yang berbeda berikutnya.

Langkah 3: Menganalisis hasil 

Setelah menjalankan Goal Seek, Excel secara otomatis menyesuaikan sel input yang dipilih untuk mencapai output target. Nilai terbaru di sel input sekarang dapat dianalisis untuk memahami hasil model Anda.

Hasil Goal Seek. Gambar oleh Penulis.

Pada gambar di atas, dengan mengubah Input B menjadi 200 dari 100, kita mencapai target Hasil C sebesar 1000. Tiga langkah ini membentuk proses standar untuk menggunakan Goal Seek di berbagai model data. 

Mari kita jelajahi bagaimana langkah-langkah ini berlaku untuk pemecahan masalah di dunia nyata. Contoh-contoh ini menunjukkan bagaimana Goal Seek dapat secara efektif menyelesaikan masalah keuangan umum, seperti menentukan pembayaran pinjaman atau mencapai target penjualan.

Contoh Goal Seek di Excel

Mari kita lihat beberapa contoh Goal Seek di Excel yang mungkin Anda temui dalam pekerjaan Anda. 

Contoh 1: Perhitungan pembayaran pinjaman

Bayangkan Anda berencana membeli rumah dan ingin menentukan jumlah pinjaman maksimum yang mampu Anda tanggung dengan pembayaran bulanan $1.000. Berdasarkan riset Anda di berbagai bank, Anda sudah memiliki suku bunga dan jangka waktu pinjaman, tetapi perlu menghitung pokok pinjaman yang akan diajukan. Anda dapat menggunakan Goal Seek untuk menyelesaikannya.

Langkah 1: Menyiapkan data Anda 

Buka lembar kerja Excel baru dan buat susunannya seperti pada gambar di bawah. 

Dataset pembayaran pinjaman. Gambar oleh Penulis.

Masukkan suku bunga hasil riset Anda (misalnya, 5%) di sel A2, jangka waktu pinjaman (misalnya, 30 tahun) di sel B2, dan perkiraan awal jumlah pinjaman (misalnya, $200.000) di sel C2. Di sel D2, masukkan rumus untuk menghitung pembayaran bulanan menggunakan fungsi PMT:

=PMT(B1/12, B2*12, -B3)

Rumus ini membagi suku bunga tahunan dengan 12 (untuk bunga bulanan), mengalikan jangka waktu pinjaman dengan 12 (untuk total jumlah pembayaran), dan menggunakan jumlah pinjaman negatif untuk menghitung pembayaran bulanan.

Langkah 2: Menjalankan Goal Seek 

Tujuan kita adalah mencari jumlah pinjaman yang menghasilkan pembayaran bulanan $1.000. Dengan kata lain, kita akan memodifikasi sel C2 sehingga D2 ditetapkan menjadi $1.000. Seperti yang telah dipelajari, kita akan memanggil Goal Seek dalam bagian What-If Analysis di bawah tab Data untuk mendapatkan kotak dialog berikut.

Opsi Goal Seek untuk contoh pembayaran pinjaman. Gambar oleh Penulis.

Di kotak dialog Goal Seek, kita masukkan hal berikut:

• Set Cell: D2, sel pembayaran bulanan.

• To Value: Masukkan 1000 (pembayaran bulanan yang diinginkan).

• By Changing Cell: Masukkan C2 (jumlah pinjaman).

Setelah kita klik OK, Excel akan menyesuaikan jumlah pinjaman di sel C2 untuk menentukan berapa banyak yang dapat Anda pinjam dengan menjaga pembayaran bulanan sebesar $1.000.

Langkah 3: Menafsirkan hasil 

Operasi Goal Seek berjalan dan berhenti pada gambar di bawah ini:

Hasil Goal Seek untuk contoh pembayaran pinjaman. Gambar oleh Penulis.

Setelah kita klik OK, Excel akan menampilkan jumlah pinjaman yang disesuaikan di sel C2. Inilah pokok pinjaman maksimum yang dapat Anda tanggung dengan suku bunga, jangka waktu, dan pembayaran bulanan yang diberikan.

Jumlah pinjaman yang disesuaikan adalah $186.282. Kita dapat menanggung jumlah pinjaman maksimum ini dengan pembayaran bulanan $1.000. Silakan bereksperimen dengan angka yang berbeda dan jalankan operasi Goal Seek beberapa kali.

Contoh 2: Mencapai target pendapatan

Misalkan Anda seorang manajer penjualan yang menargetkan pendapatan sebesar $500.000 untuk suatu produk tertentu. Anda sudah mengetahui harga per unit produk tetapi masih menentukan berapa unit yang harus dijual untuk mencapai target pendapatan ini. Goal Seek dapat membantu Anda menentukan jumlah unit yang tepat.

Langkah 1: Menyiapkan data Anda  

Buka lembar kerja baru dan masukkan data seperti pada gambar di bawah. 

Dataset target penjualan. Gambar oleh Penulis.

Di sel A2, kita masukkan harga per unit aktual dan perkiraan jumlah unit di sel B2. Kita masukkan rumus berikut untuk menghitung total pendapatan di sel C2:

=A2 * B2

Rumus ini mengalikan harga per unit dengan jumlah unit terjual untuk menghitung total pendapatan.

Langkah 2: Menjalankan Goal Seek  

Mari panggil Goal Seek dalam bagian What-If Analysis di bawah tab Data untuk mendapatkan kotak dialog berikut.

Opsi Goal Seek untuk contoh target penjualan. Gambar oleh Penulis.

Di kotak dialog Goal Seek, kita masukkan hal berikut:

• Set Cell: Ketik C2 (sel total pendapatan Anda).

• To Value: Masukkan 500000 (target pendapatan yang diinginkan).

• By Changing Cell: Masukkan B2 (jumlah unit terjual).

Excel akan menyesuaikan jumlah unit terjual di sel B2 untuk menentukan jumlah unit yang perlu Anda jual guna mencapai pendapatan $500.000.

Langkah 3: Menafsirkan Hasil  

Operasi Goal Seek berjalan dan berhenti pada langkah di bawah ini:

Hasil Goal Seek untuk contoh target penjualan. Gambar oleh Penulis.

Setelah Anda klik OK, Excel akan menampilkan jumlah unit yang disesuaikan di sel B2. Hasilnya, ditampilkan 2.000 unit — Anda perlu menjual 2.000 unit dengan harga $250 per unit untuk memenuhi target pendapatan $500.000. Jika harga per unit atau target pendapatan berubah nanti, Anda dapat menjalankan ulang operasi Goal Seek dengan nilai yang diperbarui.

Kesalahan Umum Saat Menggunakan Goal Seek dan Cara Menanganinya

Jika Anda meninjau dua contoh di atas, kita mengikuti langkah yang sama dengan cara tertentu untuk memperoleh hasil. Namun, ada beberapa kesalahan umum yang dapat mengacaukan perhitungan saat menggunakan Goal Seek di Excel. Mari kita lihat.

1. Excel Goal Seek mungkin tidak menemukan solusi

Salah satu masalah yang paling sering terjadi dengan Goal Seek adalah ketika alat ini tidak dapat menemukan solusi yang memenuhi target Anda. Ini dapat terjadi dalam dua skenario:

• Solusi tidak ada.
• Solusi tidak dapat konvergen dalam langkah yang ditentukan.

Mari pahami ini dari contoh target penjualan yang kita lihat sebelumnya. Misalkan kita keliru memasukkan harga per unit sebesar $0 alih-alih $250. Datanya terlihat sebagai berikut:

Dataset target penjualan dengan kesalahan. Gambar oleh Penulis.

Kita akan mengikuti langkah Goal Seek seperti biasa untuk menjalankan operasinya:

Goal Seek pada dataset target penjualan dengan kesalahan. Gambar oleh Penulis.

Setelah kita klik OK, apa yang terjadi selanjutnya?

Kesalahan Goal Seek mungkin tidak menemukan solusi. Gambar oleh Penulis.

Kita melihat hasilnya berupa “Goal Seek may not have found a solution.” Kesalahan ini terjadi karena persamaan tidak dapat diselesaikan. Sebelum menjalankan operasi Goal Seek, penting untuk memeriksa rumus dan nilai input untuk memastikan persamaan dapat diselesaikan.

Jika Anda yakin solusinya ada, maka bisa jadi operasi Goal Seek tidak dapat konvergen dalam 100 langkah yang dialokasikan. Untuk membantu Goal Seek berkonvergensi ke sebuah solusi:

1. Meningkatkan Max Iterations: Nilai iterasi yang lebih tinggi memungkinkan operasi Goal Seek berjalan lebih lama. Anda dapat mengubahnya dari File -> Options -> Formulas -> Max Iterations.
2. Mulai dengan Perkiraan yang Lebih Baik: Jika Anda dapat menebak rentang jumlah unit yang benar, masukkan nilai awal dalam rentang tersebut.

Memeriksa apakah persamaan dapat diselesaikan dan memungkinkannya berkonvergensi dalam langkah yang ditentukan akan mencegah kesalahan solusi tidak ditemukan.

2. Kesalahan the cell must contain a value

Goal Seek mengharapkan sel yang kita ubah berisi sebuah nilai dan akan menampilkan kesalahan “Cell must contain a value” jika tidak. Ini umum terjadi ketika nilai dalam sel diturunkan dari sel lain melalui persamaan.

Mari pahami kesalahan ini dengan contoh target penjualan yang sama. Misalkan harga per unit dihitung dengan membagi total biaya dengan total unit. Susunan data kita akan terlihat seperti di bawah:

Dataset target penjualan yang diperbarui. Gambar oleh Penulis.

Saat kita mencoba menjalankan Goal Seek, terjadi kesalahan ketika kita memilih sel A2 untuk opsi By changing cell.

Kesalahan the cell must contain a value. Gambar oleh Penulis.

Ini terjadi karena Goal Seek tidak dapat dijalankan ketika sel yang akan dimodifikasi tidak berisi sebuah nilai. Memastikan sel yang akan dimodifikasi berisi nilai akan mencegah kesalahan ini.

Kesimpulan

Artikel ini memperkenalkan Anda pada salah satu alat What-if Analysis yang umum digunakan — Goal Seek. Melalui beberapa skenario contoh, kita mempelajari cara menggunakannya di Excel dengan langkah-langkah sederhana. Kita juga memahami kesalahan umum yang dihadapi selama implementasi dan cara menghindarinya.

Saya mendorong Anda untuk bereksperimen dengan Goal Seek dalam model data Anda untuk membiasakan diri dengan alat ini dan penerapannya. Jika Anda tertarik mempelajari lebih lanjut tentang pemodelan keuangan di Excel menggunakan alat serupa Goal Seek, Anda dapat melihat kursus pemula kami Financial Modelling in Excel.