Analisis sensitivitas Excel (sampel tabel data)

Proses keuangan selalu saling terkait - satu faktor tergantung pada yang lain dan berubah dengannya. Melacak perubahan ini dan memahami apa yang diharapkan di masa depan dimungkinkan menggunakan fungsi Excel dan metode tabular.

Memperoleh banyak hasil menggunakan tabel data

Kemampuan tabel data adalah elemen dari analisis "bagaimana jika" yang sering dilakukan melalui Microsoft Excel. Ini adalah nama kedua dari analisis sensitivitas.

Umum

Tabel data adalah jenis rentang sel, yang dengannya Anda dapat menyelesaikan masalah yang timbul dengan mengubah nilai dalam beberapa sel. Itu dibuat ketika perlu untuk memonitor perubahan pada komponen formula dan menerima pembaruan hasil, sesuai dengan perubahan ini. Cari tahu cara menerapkan tablet data dalam studi, dan spesies mana mereka.

Informasi dasar tentang tabel data

Ada dua jenis tabel data, mereka berbeda dalam jumlah komponen. Buat tabel dibutuhkan dengan orientasi dengan jumlah nilai yang perlu diperiksa dengannya.

Spesialis Statistik menerapkan tabel dengan satu variabel ketika hanya ada satu variabel dalam satu atau beberapa ekspresi, yang dapat memengaruhi perubahan hasilnya. Misalnya, sering digunakan dalam bundel dengan fungsi PL. Formula ini dirancang untuk menghitung jumlah pembayaran reguler dan memperhitungkan tingkat bunga yang ditetapkan dalam kontrak. Dengan perhitungan seperti itu, variabel dicatat dalam satu kolom, dan hasil perhitungan ke yang lain. Contoh plat data dengan 1 variabel:

satu

Selanjutnya, pertimbangkan tanda-tanda dengan 2 variabel. Mereka mendaftar dalam kasus di mana dua faktor mempengaruhi perubahan dalam indikator apa pun. Dua variabel mungkin berada di tabel lain yang terkait dengan pinjaman - dengan bantuannya Anda dapat mengidentifikasi periode pembayaran yang optimal dan jumlah pembayaran bulanan. Perhitungan ini juga perlu menggunakan fungsi PPT. Plat contoh dengan 2 variabel:

2 Membuat tabel data dengan satu variabel

Pertimbangkan metode analisis atas contoh toko buku kecil, di mana hanya 100 buku yang tersedia. Beberapa dari mereka dapat dijual lebih mahal ($ 50), sisanya akan dikenakan biaya pembeli lebih murah ($ 20). Total pendapatan dari penjualan semua barang dirancang - pemilik memutuskan bahwa dengan harga tinggi 60% dari buku. Perlu untuk mengetahui bagaimana pendapatan akan tumbuh jika Anda meningkatkan harga volume barang yang lebih besar - 70% dan seterusnya.

1. Pilih jarak sel gratis dari tepi lembar dan tulis rumus di dalamnya: = Sel dari total pendapatan. Misalnya, jika pendapatan dicatat dalam sel C14 (penunjukan acak diindikasikan), perlu untuk menulis seperti ini: = C14.
2. Kami menuliskan jumlah barang di kolom di sebelah kiri sel ini - tidak di bawahnya, itu sangat penting.
3. Kami mengalokasikan rentang sel di mana kolom bunga berada dan tautan ke total pendapatan.

3.

1. Kami menemukan pada tab "Data" dari "Analisis" Bagaimana jika "" dan klik di menu yang terbuka, Anda harus memilih opsi "Tabel Data".

empat.

1. Sebuah jendela kecil akan terbuka, di mana Anda perlu menentukan sel dengan persentase dari buku yang awalnya dijual dengan harga tinggi di kolom "untuk mengganti nilai pada baris di ...". Langkah ini dilakukan untuk melakukan perhitungan ulang pendapatan umum, dengan mempertimbangkan peningkatan persentase.

lima

Setelah menekan tombol "OK" di jendela di mana data dimasukkan untuk mengkompilasi tabel, hasil perhitungan akan muncul di baris.

Menambahkan rumus ke tabel data dengan satu variabel

Dari tabel yang membantu menghitung tindakan hanya dengan satu variabel, Anda dapat membuat alat analisis yang rumit dengan menambahkan rumus tambahan. Ini harus dimasukkan di dekat formula yang sudah ada - misalnya, jika tabel difokuskan pada baris, masukkan ekspresi ke dalam sel di sebelah kanan yang sudah ada. Ketika orientasi diinstal pada kolom, tuliskan formula baru di bawah yang lama. Selanjutnya harus berakting sesuai dengan algoritma:

1. Kami kembali menyoroti berbagai sel, tetapi sekarang harus mencakup formula baru.
2. Buka menu analisis "Bagaimana jika" dan pilih "Tabel Data".
3. Tambahkan rumus baru ke bidang yang sesuai pada garis atau kolom, tergantung pada orientasi piring.

Membuat tabel data dengan dua variabel

Awal persiapan tabel semacam itu sedikit berbeda - Anda perlu menempatkan tautan ke pendapatan keseluruhan di atas nilai persen. Selanjutnya, kami melakukan langkah-langkah ini:

1. Rekam opsi untuk harga satu baris dengan mengacu pada pendapatan - setiap harga adalah satu sel.
2. Pilih rentang sel.

6.

1. Buka jendela tabel data, seperti saat menggambar satu variabel - melalui tab data pada bilah alat.
2. Pengganti dalam jumlah "untuk mengganti nilai pada kolom di ..." Sel dengan harga tinggi awal.
3. Tambahkan ke kolom "untuk mengganti nilai pada string di ..." Sel dengan minat awal penjualan buku-buku mahal dan klik "OK".

Akibatnya, seluruh piring diisi dengan jumlah penghasilan yang mungkin dengan berbagai hal penjualan barang.

7 percepatan perhitungan untuk lembar yang berisi tabel data

Jika perhitungan cepat diperlukan di pelat data yang tidak menjalankan seluruh perhitungan ulang buku, Anda dapat melakukan beberapa tindakan untuk mempercepat proses.

1. Buka jendela parameter, pilih klausul "rumus" di menu di sebelah kanan.
2. Pilih item "Secara otomatis, kecuali untuk tabel data" dalam bagian "Perhitungan dalam buku".

delapan

1. Melakukan perhitungan ulang hasil di pelat secara manual. Untuk ini, Anda perlu menyorot formula dan tekan tombol F

Alat lain untuk melakukan analisis sensitivitas

Program ini memiliki alat lain untuk membantu melakukan analisis sensitivitas. Mereka mengotomatiskan beberapa tindakan yang sebaliknya harus dilakukan secara manual.

1. Fungsi "pemilihan parameter" cocok jika hasil yang diinginkan diketahui, dan diperlukan untuk mengetahui nilai input variabel untuk mendapatkan hasil seperti itu.
2. "Pencarian Solusi" adalah add-in untuk memecahkan masalah. Perlu untuk menetapkan batasan dan mengindikasikannya, setelah itu sistem akan menemukan jawabannya. Solusinya ditentukan dengan mengubah nilai.
3. Analisis sensitivitas dapat dilakukan dengan menggunakan manajer skrip. Alat ini ada di menu analisis "bagaimana jika" pada tab Data. Ini menggantikan nilai-nilai menjadi beberapa sel - jumlahnya dapat mencapai 32. Dispatcher membandingkan nilai-nilai ini, dan pengguna tidak harus mengubahnya secara manual. Contoh penerapan Scripting Manager:

sembilan

Analisis sensitivitas proyek investasi di Excel

Metode untuk menganalisis sensitivitas di bidang investasi

Saat menganalisis "Bagaimana jika" menggunakan bust - manual atau otomatis. Berbagai nilai yang diketahui, dan mereka pada gilirannya diganti dalam formula. Akibatnya, satu set nilai diperoleh. Ini, pilih gambar yang cocok. Pertimbangkan empat indikator yang analisis sensitivitas di bidang Keuangan:

1. Nilai sekarang murni - dihitung dengan mengurangi ukuran investasi dari jumlah pendapatan.
2. Tingkat internal profitabilitas / laba - menunjukkan keuntungan mana yang diperlukan dari investasi untuk tahun ini.
3. Rasio pengembalian adalah rasio semua keuntungan untuk investasi awal.
4. Indeks laba diskon - menunjukkan efektivitas investasi.

Rumus

Sensitivitas lampiran dapat dihitung menggunakan rumus ini: Ubah parameter output dalam% / perubahan dalam parameter input dalam%.

Parameter output dan input mungkin nilai yang dijelaskan sebelumnya.

1. Perlu untuk mengetahui hasilnya dalam kondisi standar.
2. Kami mengganti salah satu variabel dan mengikuti hasil hasilnya.
3. Hitung perubahan persentase pada kedua parameter relatif terhadap kondisi yang ditetapkan.
4. Kami memasukkan persentase yang diperoleh ke dalam rumus dan menentukan sensitivitas.

Contoh analisis sensitivitas proyek investasi di Excel

Untuk pemahaman yang lebih baik tentang teknik analisis, diperlukan contoh. Mari kita menganalisis proyek dengan data terkenal itu:

10.

1. Isi tabel untuk menganalisis proyek di atasnya.

sebelas

1. Hitung arus kas menggunakan fungsi perpindahan. Pada tahap awal, alirannya sama dengan investasi. Selanjutnya kita menggunakan rumus: = jika (perpindahan (nomor; 1;) = 2; jumlah (inflow 1: outflow 1); jumlah (inflow 1: outflow 1) + $ B $ 5) Penunjukan sel dalam rumus mungkin Berbeda, itu tergantung pada tabel penempatan. Pada akhirnya, nilai dari data awal ditambahkan - nilai likuidasi.

12.

1. Kami mendefinisikan tenggat waktu yang akan dibayar oleh proyek. Untuk periode awal, kami menggunakan rumus ini: = SILENT (G7: G17; "0; D.Potok pertama; 0). Proyek ini berada pada titik impas selama 4 tahun.

13.

1. Buat kolom untuk jumlah periode tersebut ketika proyek terbayar.

empat belas

1. Menghitung profitabilitas investasi. Perlu untuk membuat ekspresi, di mana keuntungan dalam periode waktu tertentu dibagi menjadi investasi awal.

limabelas

1. Tentukan koefisien diskon untuk formula ini: = 1 / (1 + disc.%) ^ Nomor.

enambelas

1. Hitung nilai sekarang dengan multiplikasi - arus kas dikalikan dengan tingkat diskonto.

17.

1. Hitung PI (Indeks Profitabilitas). Nilai yang diberikan dalam segmen waktu dibagi menjadi lampiran pada awal pengembangan proyek.

delapan belas

1. Kami mendefinisikan tingkat laba internal menggunakan fungsi EMD: = FMR (Kisaran Arus Kas).

Analisis sensitivitas investasi menggunakan tabel data

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

Analisis faktor dan dispersi di Excel dengan otomatisasi perhitungan

Analisis dispersi di Excel

Tujuan dari analisis semacam itu adalah untuk membagi variabilitas besarnya tiga komponen:

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

Lakukan analisis dispersi melalui "analisis data" Excel Add-on. Jika tidak diaktifkan, itu dapat dihubungkan dalam parameter.

Tabel awal harus mematuhi dua aturan: Setiap nilai akun untuk satu kolom, dan data di dalamnya disusun dalam menaik atau turun. Perlu untuk menguji dampak tingkat pendidikan tentang perilaku dalam konflik.

sembilan belas

1. Kami menemukan alat "Data" tab "Data" dan buka jendelanya. Daftar perlu memilih analisis dispersi faktor tunggal.

dua puluh

1. Isi baris kotak dialog. Interval inlet adalah semua sel tanpa memperhitungkan tutup dan angka. Kami mengelompokkan kolom. Beri tahu hasil pada lembar baru.

21.

Karena nilai dalam sel kuning lebih besar dari unit, kita dapat mengasumsikan asumsi yang salah - tidak ada hubungan antara pendidikan dan perilaku dalam konflik.

Analisis faktor di Excel: Contoh

Kami menganalisis hubungan antara data di bidang penjualan - perlu untuk mengidentifikasi barang-barang populer dan tidak populer. Informasi Awal:

22.

1. Perlu untuk mengetahui barang mana permintaan untuk bulan kedua telah tumbuh selama bulan kedua. Kami membuat meja baru untuk menentukan pertumbuhan dan pengurangan permintaan. Pertumbuhan dihitung sesuai dengan formula ini: = jika (((menuntut 2-permintaan 1)> 0; permintaan 2 - permintaan 1; 0). Rumus penurunan: = jika (pertumbuhan = 0; permintaan adalah 1- permintaan 2; 0).

23.

1. Hitung pertumbuhan permintaan barang dalam persen: = jika (pertumbuhan / total 2 = 0; reduksi / total 2; tinggi / total 2).

24.

1. Kami akan membuat bagan untuk kejelasan - mengalokasikan rentang sel dan membuat histogram melalui tab "Sisipkan". Dalam pengaturan Anda perlu menghapus fill, itu dapat dilakukan melalui alat "Format Data Format".

25 Analisis dispersi dua faktor di Excel

Analisis dispersi dilakukan dengan beberapa variabel. Pertimbangkan ini pada contoh: Anda perlu mencari tahu seberapa cepat reaksi terhadap suara volume yang berbeda pada pria dan wanita dimanifestasikan.

26.

1. Buka "analisis data", Anda perlu menemukan analisis dispersi dua faktor tanpa pengulangan.
2. Interval input - sel di mana data terkandung (tanpa topi). Kami membawa hasil ke lembar baru dan klik "OK".

27.

Indikator F lebih besar dari F-Critical - Ini berarti bahwa lantai memengaruhi laju reaksi terhadap suara.

28.

Kesimpulan

Artikel ini dijelaskan secara rinci analisis sensitivitas dalam prosesor tabel Excel, sehingga setiap pengguna dapat mengetahui metode penggunaannya.

Pesan analisis sensitivitas di Excel (tabel data sampel) muncul terlebih dahulu untuk teknologi informasi.