Analisis Sensitivitas di Excel (Contoh Datasheet)

Proses di bidang keuangan selalu saling berhubungan – satu faktor bergantung pada faktor lain dan berubah dengannya. Lacak perubahan ini dan pahami apa yang diharapkan di masa mendatang, mungkin menggunakan fungsi Excel dan metode spreadsheet.

Mendapatkan beberapa hasil dengan tabel data

Kemampuan lembar data adalah elemen analisis bagaimana-jika—sering dilakukan melalui Microsoft Excel. Ini adalah nama kedua untuk analisis sensitivitas.

Ringkasan

Tabel data adalah jenis rentang sel yang dapat digunakan untuk menyelesaikan masalah dengan mengubah nilai di beberapa sel. Itu dibuat ketika diperlukan untuk melacak perubahan dalam komponen rumus dan menerima pembaruan untuk hasilnya, sesuai dengan perubahan ini. Mari kita cari tahu bagaimana menggunakan tabel data dalam penelitian, dan apa jenisnya.

Dasar-dasar tentang tabel data

Ada dua jenis tabel data, mereka berbeda dalam jumlah komponen. Anda perlu menyusun tabel dengan fokus pada jumlah nilai uXNUMXbuXNUMXbyang perlu Anda periksa.

Ahli statistik menggunakan tabel variabel tunggal ketika hanya ada satu variabel dalam satu atau lebih ekspresi yang dapat mengubah hasilnya. Misalnya, sering digunakan bersama dengan fungsi PMT. Rumus ini dirancang untuk menghitung jumlah pembayaran reguler dan memperhitungkan tingkat bunga yang ditentukan dalam perjanjian. Dalam perhitungan seperti itu, variabel ditulis dalam satu kolom, dan hasil perhitungan di kolom lain. Contoh pelat data dengan 1 variabel:

Selanjutnya, pertimbangkan pelat dengan 2 variabel. Mereka digunakan dalam kasus di mana dua faktor mempengaruhi perubahan indikator apa pun. Kedua variabel tersebut mungkin berakhir di tabel lain yang terkait dengan pinjaman, yang dapat digunakan untuk menentukan periode pembayaran yang optimal dan jumlah pembayaran bulanan. Dalam perhitungan ini, Anda juga perlu menggunakan fungsi PMT. Contoh tabel dengan 2 variabel:

Membuat tabel data dengan satu variabel

Pertimbangkan metode analisis dengan menggunakan contoh toko buku kecil dengan stok hanya 100 buku. Beberapa dari mereka dapat dijual lebih mahal ($50), sisanya akan lebih murah ($20). Total pendapatan dari penjualan semua barang dihitung – pemilik memutuskan bahwa dia akan menjual 60% buku dengan harga tinggi. Anda perlu mencari tahu bagaimana pendapatan akan meningkat jika Anda menaikkan harga barang dengan volume yang lebih besar – 70%, dan seterusnya.

Perhatikan! Total pendapatan harus dihitung menggunakan rumus, jika tidak, tabel data tidak dapat disusun.

1. Pilih sel bebas dari tepi lembar dan tulis rumus di dalamnya: = Sel dari total pendapatan. Misalnya, jika pendapatan ditulis di sel C14 (penunjukan acak ditunjukkan), Anda harus menulis ini: = S14.
2. Kami menulis persentase volume barang di kolom di sebelah kiri sel ini - tidak di bawahnya, ini sangat penting.
3. Kami memilih rentang sel tempat kolom persentase dan tautan ke total pendapatan berada.

4. Kami menemukan pada tab "Data" item "Bagaimana jika analisis" dan klik di atasnya - di menu yang terbuka, pilih opsi "Tabel data".

5. Sebuah jendela kecil akan terbuka di mana Anda perlu menentukan sel dengan persentase buku yang awalnya dijual dengan harga tinggi di kolom "Ganti nilai dengan baris di ...". Langkah ini dilakukan untuk menghitung ulang total pendapatan dengan memperhitungkan persentase kenaikannya.

Setelah mengklik tombol "OK" di jendela tempat data dimasukkan untuk menyusun tabel, hasil perhitungan akan muncul di baris.

Menambahkan Rumus ke Tabel Data Variabel Tunggal

Dari tabel yang membantu menghitung tindakan dengan hanya satu variabel, Anda dapat membuat alat analisis yang canggih dengan menambahkan rumus tambahan. Itu harus dimasukkan di sebelah rumus yang sudah ada – misalnya, jika tabel berorientasi baris, kita memasukkan ekspresi di sel di sebelah kanan yang sudah ada. Ketika orientasi kolom diatur, kami menulis rumus baru di bawah yang lama. Selanjutnya, ikuti algoritma:

1. Pilih rentang sel lagi, tetapi sekarang harus menyertakan rumus baru.
2. Buka menu analisis “bagaimana jika” dan pilih “Lembar Data”.
3. Kami menambahkan formula baru ke bidang yang sesuai dalam baris atau kolom, tergantung pada orientasi pelat.

Buat tabel data dengan dua variabel

Awal dari tabel semacam itu sedikit berbeda – Anda harus meletakkan tautan ke total pendapatan di atas nilai persentase. Selanjutnya, kami melakukan langkah-langkah ini:

1. Tulis opsi harga dalam satu baris dengan tautan ke pendapatan – satu sel untuk setiap harga.
2. Pilih rentang sel.

3. Buka jendela tabel data, seperti saat menyusun tabel dengan satu variabel – melalui tab “Data” pada bilah alat.
4. Ganti di kolom "Ganti nilai dengan kolom di ..." sel dengan harga tinggi awal.
5. Tambahkan sel dengan persentase awal penjualan buku mahal ke kolom "Ganti nilai dengan baris di ..." dan klik "OK".

Akibatnya, seluruh tabel diisi dengan jumlah pendapatan yang mungkin dengan kondisi yang berbeda untuk penjualan barang.

Mempercepat perhitungan untuk lembar kerja yang berisi tabel data

Jika Anda memerlukan penghitungan cepat dalam tabel data yang tidak memicu penghitungan ulang seluruh buku kerja, ada beberapa hal yang dapat Anda lakukan untuk mempercepat prosesnya.

1. Buka jendela opsi, pilih item "Rumus" di menu di sebelah kanan.
2. Pilih item "Otomatis, kecuali untuk tabel data" di bagian "Komputasi di buku kerja".

3. Mari kita hitung ulang hasil di tabel secara manual. Untuk melakukan ini, pilih rumus dan tekan tombol F.

Alat Lain untuk Melakukan Analisis Sensitivitas

Ada alat lain dalam program untuk membantu Anda melakukan analisis sensitivitas. Mereka mengotomatiskan beberapa tindakan yang seharusnya dilakukan secara manual.

1. Fungsi "Pemilihan parameter" cocok jika hasil yang diinginkan diketahui, dan Anda perlu mengetahui nilai input variabel untuk mendapatkan hasil seperti itu.
2. "Mencari solusi" adalah tambahan untuk memecahkan masalah. Penting untuk menetapkan batas dan menunjuk ke sana, setelah itu sistem akan menemukan jawabannya. Solusinya ditentukan dengan mengubah nilai.
3. Analisis sensitivitas dapat dilakukan dengan menggunakan Scenario Manager. Alat ini ditemukan di menu analisis bagaimana-jika di bawah tab Data. Ini menggantikan nilai dalam beberapa sel – jumlahnya bisa mencapai 32. Operator membandingkan nilai-nilai ini sehingga pengguna tidak perlu mengubahnya secara manual. Contoh penggunaan pengelola skrip:

Analisis sensitivitas proyek investasi di Excel

Analisis bagaimana-jika sangat berguna dalam situasi di mana peramalan diperlukan, seperti investasi. Analis menggunakan metode ini untuk mengetahui bagaimana nilai saham suatu perusahaan akan berubah sebagai akibat dari perubahan beberapa faktor.

Metode Analisis Sensitivitas Investasi

Saat menganalisis "bagaimana jika" gunakan enumerasi - manual atau otomatis. Rentang nilai diketahui, dan mereka disubstitusikan ke dalam rumus satu per satu. Hasilnya adalah sekumpulan nilai. Pilih nomor yang sesuai dari mereka. Mari kita pertimbangkan empat indikator yang analisis sensitivitasnya dilakukan di bidang keuangan:

1. Net Present Value – Dihitung dengan mengurangkan jumlah investasi dari jumlah pendapatan.
2. Tingkat pengembalian / keuntungan internal – menunjukkan berapa banyak keuntungan yang diperlukan untuk diterima dari investasi dalam setahun.
3. Rasio pengembalian adalah rasio dari semua keuntungan untuk investasi awal.
4. Indeks keuntungan yang didiskon – menunjukkan keefektifan investasi.

Rumus

Sensitivitas penyematan dapat dihitung menggunakan rumus ini: Perubahan parameter output di % / Perubahan parameter input di %.

Parameter output dan input dapat berupa nilai yang dijelaskan sebelumnya.

1. Anda perlu mengetahui hasilnya dalam kondisi standar.
2. Kami mengganti salah satu variabel dan memantau perubahan hasilnya.
3. Kami menghitung persentase perubahan kedua parameter relatif terhadap kondisi yang ditetapkan.
4. Kami memasukkan persentase yang diperoleh ke dalam rumus dan menentukan sensitivitasnya.

Contoh analisis sensitivitas proyek investasi di Excel

Untuk pemahaman yang lebih baik tentang metodologi analisis, sebuah contoh diperlukan. Mari kita menganalisis proyek dengan data yang diketahui berikut:

1. Isi tabel untuk menganalisis proyek di atasnya.

2. Kami menghitung arus kas menggunakan fungsi OFFSET. Pada tahap awal, alirannya sama dengan investasi. Selanjutnya, kami menerapkan rumus: =IF(OFFSET(Nomor,1;)=2;SUM(Aliran Masuk 1:Aliran 1); SUM(Aliran Masuk 1:Aliran 1)+$B$ 5)
   Penunjukan sel dalam rumus mungkin berbeda, tergantung pada tata letak tabel. Pada akhirnya, nilai dari data awal ditambahkan – nilai sisa.

3. Kami menentukan periode di mana proyek akan terbayar. Untuk periode awal, kami menggunakan rumus ini: =SUMMES(G7:G17;»<0″). Rentang sel adalah kolom arus kas. Untuk periode selanjutnya, kami menerapkan rumus ini: =Periode awal+IF(E.stream pertama>0; E.stream pertama;0). Proyek ini berada pada titik impas dalam 4 tahun.

4. Kami membuat kolom untuk jumlah periode tersebut ketika proyek terbayar.

5. Kami menghitung laba atas investasi. Perlu untuk membuat pernyataan di mana keuntungan dalam periode waktu tertentu dibagi dengan investasi awal.

6. Kami menentukan faktor diskon menggunakan rumus ini: =1/(1+Cakram%) ^Nomor.

7. Kami menghitung nilai sekarang menggunakan perkalian – arus kas dikalikan dengan faktor diskonto.

8. Mari kita hitung PI (indeks profitabilitas). Nilai sekarang dari waktu ke waktu dibagi dengan investasi pada awal proyek.

9. Mari kita tentukan tingkat pengembalian internal menggunakan fungsi IRR: =IRR(Rentang arus kas).

Analisis Sensitivitas Investasi Menggunakan Datasheet

Untuk analisis proyek di bidang investasi, metode lain lebih cocok daripada tabel data. Banyak pengguna mengalami kebingungan saat menyusun formula. Untuk mengetahui ketergantungan satu faktor pada perubahan yang lain, Anda perlu memilih sel yang benar untuk memasukkan perhitungan dan untuk membaca data.

Analisis faktor dan dispersi di Excel dengan otomatisasi perhitungan

Tipologi lain dari analisis sensitivitas adalah analisis faktor dan analisis varians. Jenis pertama mendefinisikan hubungan antara angka, yang kedua mengungkapkan ketergantungan satu variabel pada yang lain.

ANOVA di Excel

Tujuan dari analisis tersebut adalah untuk membagi variabilitas nilai menjadi tiga komponen:

1. Variabilitas sebagai akibat dari pengaruh nilai-nilai lain.
2. Perubahan karena hubungan nilai-nilai yang mempengaruhinya.
3. Perubahan acak.

Mari kita lakukan analisis varians melalui add-in Excel “Analisis Data”. Jika tidak diaktifkan, itu dapat diaktifkan di pengaturan.

Tabel awal harus mengikuti dua aturan: ada satu kolom untuk setiap nilai, dan data di dalamnya diatur dalam urutan menaik atau menurun. Perlu untuk memeriksa pengaruh tingkat pendidikan terhadap perilaku dalam konflik.

1. Temukan alat Analisis Data di tab Data dan buka jendelanya. Dalam daftar, Anda perlu memilih analisis varians satu arah.

2. Isi baris kotak dialog. Interval input adalah semua sel, tidak termasuk header dan angka. Kelompokkan menurut kolom. Kami menampilkan hasilnya pada lembar baru.

Karena nilai dalam sel kuning lebih besar dari satu, asumsi tersebut dapat dianggap salah – tidak ada hubungan antara pendidikan dan perilaku dalam konflik.

Analisis faktor di Excel: sebuah contoh

Mari kita menganalisis hubungan data di bidang penjualan – perlu untuk mengidentifikasi produk yang populer dan tidak populer. Informasi awal:

1. Kita perlu mencari tahu permintaan barang mana yang paling meningkat selama bulan kedua. Kami sedang menyusun tabel baru untuk menentukan pertumbuhan dan penurunan permintaan. Pertumbuhan dihitung menggunakan rumus ini: =IF((Permintaan 2-Permintaan 1)>0; Permintaan 2- Permintaan 1;0). Turunkan rumus: =IF(Pertumbuhan=0; Permintaan 1- Permintaan 2;0).

2. Hitung pertumbuhan permintaan barang sebagai persentase: =IF(Pertumbuhan/Hasil 2 =0; Penurunan/Hasil 2; Pertumbuhan/Hasil 2).

3. Mari buat bagan untuk kejelasan – pilih rentang sel dan buat histogram melalui tab “Sisipkan”. Dalam pengaturan, Anda perlu menghapus isian, ini dapat dilakukan melalui alat Format Seri Data.

Analisis varians dua arah di Excel

Analisis varians dilakukan dengan beberapa variabel. Pertimbangkan ini dengan sebuah contoh: Anda perlu mencari tahu seberapa cepat reaksi terhadap suara dengan volume berbeda memanifestasikan dirinya pada pria dan wanita.

1. Kami membuka "Analisis Data", dalam daftar Anda perlu menemukan analisis varians dua arah tanpa pengulangan.
2. Interval input – sel yang berisi data (tanpa header). Kami menampilkan hasilnya pada lembar baru dan klik "OK".

Nilai F lebih besar dari F-kritis, yang berarti bahwa lantai mempengaruhi kecepatan reaksi terhadap suara.

Kesimpulan

Pada artikel ini, analisis sensitivitas dalam spreadsheet Excel dibahas secara rinci, sehingga setiap pengguna dapat memahami metode penerapannya.