Apabila Anda ingin melakukan perhitungan yang cukup rumit menggunakan Excel, seperti menghitung nilai yang tidak diketahui. Andadapat saja mencoba memecahkan masalah tersebut dengan cara Anda sendiri, memasukkan nomor yang berbeda-beda sampai Anda menemukan jawaban yang tepat. Namun, metode ini dapat memakan waktu dan usaha.
Daripada menerka menghitung jawaban sendiri, Anda bisa menggunakan fungsi Excel yang disebut analisis Bagaimana-Jika/what-if analysis. Fitur ini memungkinkan Anda bereksperimen dengan data Anda. Pada pelajaran ini, kami akan menunjukkan kepada Anda bagaimana menggunakan what-if analysis untuk menjawab berbagai jenis pertanyaan. Saksikan video berikut ini mengenai analisis Bagaimana-Jika.
Excel memiliki banyak fitur untuk melakukan perhitungan matematika yang rumit, termasuk what-if analysis. Fitur ini dapat membantu Anda bereksperimen dan menjawab pertanyaan dengan data Anda. Pada pelajaran ini, Anda akan belajar bagaimana menggunakan what-if analysis yang disebut Goal Seek .
Opsional: Anda dapat mengunduh contoh lampiran di bawah ini untuk latihan ekstra.
Menggunakan Goal Seek
Ketika Anda membuat formula atau fungsi di Excel, Anda tempatkan berbagai macam komponen untuk menghitung suatu hasil.Goal seek bekerja dengan cara yang sebaliknya: Ini memungkinkan Anda memulai dengan hasil yang diinginkan, dan menghitung nilai masukan yang kemudian akan memberikan nilai yang dibutuhkan. Kami akan menggunakan beberapa contoh untuk menunjukkan bagaimana menggunakan Goal Seek.
Menggunakan Goal Seek (Contoh 1):
Katakanlah Anda terdaftar di kelas. saat ini nilai Anda 65, dan setidaknya Anda perlu 70 untuk lulus kelas. Untungnya, Anda memiliki satu tugas akhir yang dapat meningkatkan nilai rata-rata. Anda dapat menggunakan Goal Seek untuk mencari tahu berapa nilai yang diperlukan pada tugas akhir agar dapat lulus kelas.
Pada gambar di bawah, Anda dapat melihat bahwa nilai pada empat tugas pertama adalah 58, 70, 7, dan 60. Meskipun kita tidak tahu apaberapakah nilai yang kelima nanti, kita bisa menulis formula atau fungsi untuk menghitung nilai akhir. Dalam hal ini, setiap tugas memiliki bobot yang sama, sehingga yang harus kita lakukan adalah merata-ratakan kelima nilai dengan mengetik = AVERAGE (B2: B6). Setelah kita menggunakan Goal Seek, sel B6 akan menunjukkan kepada kita nilai minimal yang harus kita dapatkan pada tugas akhir.
1. Pilih sel yang berisi nilai yang ingin Anda ubah. Bila Anda menggunakan Goal Seek, Anda perlu memilih sel yang sudah berisi rumus atau fungsi. Dalam contoh kita, kita akan pilih sel B7 karena mengandung formula = AVERAGE (B2: B6).
2. Dari tab data, klik perintah What-If Analysis, kemudian pilih Goal Seek dari menu drop-down.
3. Sebuah kotak dialog akan muncul dengan tiga bidang:
o Set Cell : ini adalah sel yang akan berisi hasil yang diinginkan. Dalam contoh ini, sel B7 sudah dipilih.
o To value: ini adalah hasil yang diinginkan. Dalam contoh kita, setidaknya kita akan memasukkan 70 agar lulus kelas.
o By changing cell: ini adalah sel dimana Goal Seek akan menempatkan jawabannya. Dalam contoh ini, kita akan pilih selB6 karena kita ingin menentukan nilai yang kita butuhkan pada tugas akhir.
4. Setelah selesai, klik OK.
5. Kotak dialog akan memberitahu Anda jika Goal Seek mampu menemukan solusi. Klik OK.
6. Hasilnya akan muncul di sel tertentu. Dalam contoh ini, Goal Seek menghitung bahwa setidaknya diperlukan 90 pada tugas akhir agar lulus.
Menggunakan Goal Seek (Contoh 2):
Katakanlah Anda membutuhkan pinjaman untuk membeli mobil baru. Anda sudah mengetahui bahwa memerlukan jumlah pinjaman/loan amount sebesar $ 20.000, jangka/term waktu lamanya yang dibutuhkan untuk melunasi pinjaman 60 bulan – dan pembayaran/paymentcicilan tidak lebih dari $ 400 per bulan. Namun, Anda tidak belum tahu berapa bunganya/interest rate.
Pada gambar di bawah, Anda dapat melihat bahwa interest rate dibiarkan kosong dan payment adalah $ 333,33. Hal ini karena pembayaran yang dihitung dengan fungsi khusus yang disebut fungsi PMT (Payment), dan $ 333,33 adalah pembayaran bulanan yang akan dibayar jika ada ada bunga ($ 20.000 dibagi dengan 60 pembayaran bulanan).
Jika kita mengetik nilai yang berbeda ke dalam sel kosong interest rate, kita bisa menemukan nilai yang menghasilkan paymentmenjadi $ 400, dan itu akan menjadi tingkat bunga tertinggi yang sanggup ditangani. Namun, Goal Seek dapat melakukan ini secara otomatis dengan memulai dari hasil dan bekerja mundur.
Menyisipkan fungsi PMT:
- Pilih sel di mana Anda ingin fungsi ini diletakan.
- Dari tab Formula, pilih perintah Financial.
3. Sebuah menu drop-down akan muncul menampilkan semua fungsi yang terkait keuangan. Gulir ke bawah dan pilih fungsiPMT.
4. Sebuah kotak dialog akan muncul.
5. Masukkan nilai yang diinginkan dan/atau referensi sel. Dalam contoh ini, kita hanya menggunakan Rate, Nper (Number of Payments-jumlah pembayaran), dan Pv (Loan Amount- Jumlah Pinjaman).
6. Klik OK. Hasilnya akan muncul di sel yang dipilih. Perhatikan bahwa ini bukan hasil akhir karena kita masih tidak tahuberapa tingkat bunganya.
Menggunakan Goal Seek untuk menemukan tingkat bunga:
Sekarang kita telah menambahkan fungsi PMT, kita dapat menggunakan Goal Seek untuk mencari tingkat bunga yang kita butuhkan.
1. Dari tab data, klik perintah What-if analysis.
2. Pilih Goal Seek.
3. Sebuah kotak dialog akan muncul yang berisi tiga bidang:
- Set Cell: ini adalah sel yang akan berisi hasil yang diinginkan (dalam hal ini, pembayaran bulanan). Dalam contoh ini, kita akan set keB5 (tidak peduli apakah itu adalah referensi mutlak atau relatif).
- To Value: ini adalah hasil yang diinginkan. Kita akan mengaturnya untuk -400. Karena kita melakukan pembayaran yang akandikurangi dari jumlah pinjaman, kita harus memasukkan pembayaran sebagai angka negatif.
- By Changing Cell: ini adalah sel dimana Goal Seek akan menempatkan jawabannya (dalam hal ini, tingkat bunga). Kita akanmenempatkannya pada B4.
4. Setelah selesai, klik OK. Kotak dialog akan memberitahu Anda apakah Goal Seek mampu menemukan solusi. Pada contoh ini, solusinya adalah 7.42%, dan telah ditempatkan di sel B4. Ini memberitahu bahwa tingkat bunga 7,42% memberitahukan kepada kita bahwa pembayaran sebesar $ 400 per bulan pada pinjaman $ 20.000 yang dibayar lunas selama lima tahun, atau 60 bulan.
Jenis lain dari what-if analysis
Anda mungkin ingin menggunakan jenis lain what-if analysis: skenario dan tabel data. Daripada memulai dari hasil yang diinginkan dan menghitung mundur seperti menggunakan Goal Seek, Anda dapat menggunakan pilihan ini untuk menguji beberapa nilai dan melihat bagaimana hasilnya.
· Skenario memungkinkan Anda mengganti nilai untuk beberapa sel (sampai dengan 32) pada waktu yang sama. Anda dapat membuat skenario sebanyak yang Anda inginkan dan kemudian membandingkannya tanpa mengubah nilai-nilai secara manual. Pada contoh di bawah ini, masing-masing skenario berisi jangka waktu/term dan bunga/rate. Ketika masing-masing skenario dipilih, akan menggantikan nilai-nilai dalam spreadsheet dengan nilainya sendiri, dan hasilnya akan dihitung ulang.
· Tabel data memungkinkan Anda untuk mengambil satu atau dua variabel dalam formula dan menggantinya dengan banyak nilai yang berbeda yang Anda inginkan, kemudian melihat hasilnya dalam sebuah tabel. Tidak seperti skenario atau Goal Seek, cara ini sangat baik karena menunjukkan beberapa hasil pada saat yang sama. Pada contoh di bawah, kita dapat melihat 24 hasil yang didapat dari peminjaman mobil.
Latihan!
1. Buka workbook Excel 2010 yang ada. Jika Anda ingin, Anda dapat menggunakan contoh lampiran di bawah.
2. Gunakan Goal Seek untuk menentukan nilai yang tidak diketahui. Jika Anda menggunakan contoh, bukalah lembar kerjaHistory Class dan gunakan Goal Seek untuk menentukan kelas apa yang Anda butuhkan di Ujian ke- 3 agar mendapatkan rata-rata nilai akhir 90.
3. Gunakan fungsi PMT ke dalam worksheet. Jika Anda menggunakan contoh, bukalah worksheet Car Loan dan masukkan fungsi ke dalam sel B5.
4. Gunakan Goal Seek untuk mencari tingkat bunga yang akan Anda butuhkan untuk memiliki pembayaran bulanan dari $ 400. Berapa tingkat bunga yang akan Anda butuhkan jika Anda hanya bisa melakukan pembayaran bulanan sebesar $ 380?
Lampiran :
Tidak ada komentar:
Posting Komentar