Meskipun daftar panjang fungsi Excel adalah salah satu fitur paling menarik dari aplikasi spreadsheet Microsoft, ada beberapa permata yang kurang dimanfaatkan yang meningkatkan fungsi ini. Salah satu alat yang sering diabaikan adalah Analisis Bagaimana-Jika.

Alat Analisis Bagaimana-Jika^{(What-If Analysis)} Excel dipecah menjadi tiga komponen utama. Bagian yang dibahas di sini adalah fitur Goal Seek yang kuat yang memungkinkan Anda bekerja mundur dari suatu fungsi dan menentukan input yang diperlukan untuk mendapatkan output yang diinginkan dari formula dalam sel. Baca terus untuk mempelajari cara menggunakan alat Pencarian Sasaran Analisis Bagaimana-Jika di Excel.^{(What-If Analysis Goal Seek)}

Contoh Alat Pencarian Sasaran Excel

Misalkan Anda ingin mengambil pinjaman hipotek untuk membeli rumah dan Anda khawatir tentang bagaimana tingkat bunga pinjaman akan mempengaruhi pembayaran tahunan. Jumlah hipotek adalah $ 100.000 dan Anda akan membayar kembali pinjaman selama 30 tahun.

Menggunakan fungsi PMT Excel , Anda dapat dengan mudah mengetahui berapa pembayaran tahunan jika tingkat bunga 0%. Spreadsheet kemungkinan akan terlihat seperti ini:

Sel di A2 mewakili tingkat bunga tahunan, sel di B2 adalah panjang pinjaman dalam tahun, dan sel di C2 adalah jumlah pinjaman hipotek. Rumus di D2 adalah:

=PMT(A2,B2,C2)

dan mewakili pembayaran tahunan hipotek $ 100.000 selama 30 tahun dengan bunga 0%. Perhatikan^(Notice) bahwa angka di D2 adalah negatif karena Excel mengasumsikan bahwa pembayaran adalah arus kas negatif dari posisi keuangan Anda.

Sayangnya, tidak ada pemberi pinjaman hipotek yang akan meminjamkan Anda $ 100.000 dengan bunga 0%. Misalkan^(Suppose) Anda menghitung dan mengetahui bahwa Anda mampu membayar kembali $6.000 per tahun dalam pembayaran hipotek. Anda sekarang bertanya-tanya berapa tingkat bunga tertinggi yang dapat Anda ambil untuk pinjaman guna memastikan Anda tidak membayar lebih dari $6.000 per tahun.

Banyak orang dalam situasi ini hanya akan mulai mengetik angka di sel A2 sampai angka di D2 mencapai sekitar $6.000. Namun, Anda dapat membuat Excel melakukan pekerjaan untuk Anda dengan menggunakan alat Pencarian Sasaran Analisis^{(Analysis Goal Seek)} Bagaimana-Jika . Pada dasarnya, Anda akan membuat Excel bekerja mundur dari hasil di D4 hingga tiba pada tingkat bunga yang memenuhi pembayaran maksimum Anda sebesar $6.000.

Mulailah dengan mengklik tab Data pada ^(Data)Pita^(Ribbon) dan temukan tombol Analisis Bagaimana-Jika^{(What-If Analysis)} di bagian Alat Data^{(Data Tools)} . Klik tombol What-If Analysis dan pilih Goal Seek dari menu.

Excel membuka jendela kecil dan meminta Anda untuk memasukkan hanya tiga variabel. Variabel Set Cell harus berupa sel yang berisi rumus. Dalam contoh kita di sini, itu adalah D2 . Variabel To Value adalah jumlah yang Anda inginkan agar sel di D2 berada di akhir analisis.

Bagi kami, itu adalah -6.000 . Ingatlah bahwa Excel melihat pembayaran sebagai arus kas negatif. Variabel Dengan Mengubah Sel^{(By Changing Cell)} adalah tingkat bunga yang Anda ingin Excel temukan untuk Anda sehingga hipotek $100.000 hanya akan dikenakan biaya $6.000 per tahun. Jadi, gunakan sel A2 .

Klik tombol OK dan Anda mungkin memperhatikan bahwa Excel mem-flash sekelompok angka di sel masing-masing hingga iterasi akhirnya bertemu pada angka akhir. Dalam kasus kami, sel di A2 sekarang harus membaca sekitar 4,31%.

Analisis ini memberi tahu kita bahwa agar tidak menghabiskan lebih dari $6.000 per tahun untuk hipotek senilai $100.000 selama 30 tahun, Anda perlu mengamankan pinjaman tidak lebih dari 4,31%. Jika Anda ingin terus melakukan analisis bagaimana-jika, Anda dapat mencoba berbagai kombinasi angka dan variabel untuk mengeksplorasi opsi yang Anda miliki saat mencoba mengamankan tingkat bunga yang baik pada hipotek.

Alat Pencarian Sasaran Analisis Bagaimana-Jika^{(What-If Analysis Goal Seek)} Excel adalah pelengkap yang kuat untuk berbagai fungsi dan rumus yang ditemukan dalam spreadsheet biasa. Dengan bekerja mundur dari hasil rumus dalam sel, Anda dapat menjelajahi berbagai variabel dalam perhitungan Anda dengan lebih jelas.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s long list of funсtions is one of the most enticing features of Microsoft’s spreadsheet aрplication, there a few underutilized gems that enhance thesе funсtionѕ. One often-overlooked tool is the What-If Analysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.

Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• Cara Menggunakan Analisis Bagaimana-Jika Excel

• Cara Menggunakan Fitur Sel Bicara Excel

• 2 Cara Menggunakan Fungsi Transpose Excel

• Bagaimana Memahami Analisis Bagaimana-Jika Di Microsoft Excel

• Cara Menghapus Baris Kosong di Excel

• Google Spreadsheet vs Microsoft Excel – Apa Perbedaannya?

• Cara Menggabungkan Data Dalam Beberapa File Excel

• Cara Mengurutkan Berdasarkan Tanggal di Excel

• Pertahankan Referensi Sel saat Menyalin Rumus di Excel

• Cara Memperbaiki Kesalahan #N/A dalam Rumus Excel seperti VLOOKUP

• Mengapa Anda Harus Menggunakan Rentang Bernama di Excel

• Cara Menggunakan Isi Flash Di Excel

• Cara Membuat Beberapa Daftar Drop Down Tertaut di Excel

• Cara Melacak Tanggungan di Excel

• Cara Menulis Rumus/Pernyataan IF di Excel

• Gunakan Nama Rentang Dinamis di Excel untuk Dropdown Fleksibel

• Cara Membuat Diagram Lingkaran di Excel

• Kelompokkan Baris dan Kolom dalam Lembar Kerja Excel

• Cara Membuat Makro atau Script VBA Di Excel

• Microsoft Outlook Tidak Bisa Dibuka? 10 Cara untuk Memperbaiki