Analisis Sensitiviti dalam Excel (Contoh Lembaran Data)

Proses dalam bidang kewangan sentiasa saling berkaitan - satu faktor bergantung kepada yang lain dan berubah dengannya. Jejaki perubahan ini dan fahami perkara yang diharapkan pada masa hadapan, mungkin menggunakan fungsi Excel dan kaedah hamparan.

Mendapatkan berbilang hasil dengan jadual data

Keupayaan helaian data ialah elemen analisis bagaimana jika—sering dilakukan melalui Microsoft Excel. Ini adalah nama kedua untuk analisis sensitiviti.

Gambaran Keseluruhan

Jadual data ialah sejenis julat sel yang boleh digunakan untuk menyelesaikan masalah dengan menukar nilai dalam beberapa sel. Ia dicipta apabila perlu untuk menjejaki perubahan dalam komponen formula dan menerima kemas kini kepada keputusan, mengikut perubahan ini. Mari ketahui cara menggunakan jadual data dalam penyelidikan, dan jenisnya.

Asas mengenai jadual data

Terdapat dua jenis jadual data, ia berbeza dalam bilangan komponen. Anda perlu menyusun jadual dengan tumpuan pada bilangan nilai uXNUMXbuXNUMXbyang anda perlu semak dengannya.

Ahli statistik menggunakan jadual pembolehubah tunggal apabila terdapat hanya satu pembolehubah dalam satu atau lebih ungkapan yang boleh mengubah keputusannya. Sebagai contoh, ia sering digunakan bersama dengan fungsi PMT. Formula ini direka untuk mengira jumlah pembayaran biasa dan mengambil kira kadar faedah yang dinyatakan dalam perjanjian. Dalam pengiraan sedemikian, pembolehubah ditulis dalam satu lajur, dan hasil pengiraan dalam lajur yang lain. Contoh plat data dengan 1 pembolehubah:

Seterusnya, pertimbangkan plat dengan 2 pembolehubah. Ia digunakan dalam kes di mana dua faktor mempengaruhi perubahan dalam mana-mana penunjuk. Kedua-dua pembolehubah mungkin berakhir dalam jadual lain yang dikaitkan dengan pinjaman, yang boleh digunakan untuk menentukan tempoh pembayaran balik yang optimum dan amaun bayaran bulanan. Dalam pengiraan ini, anda juga perlu menggunakan fungsi PMT. Contoh jadual dengan 2 pembolehubah:

Membuat jadual data dengan satu pembolehubah

Pertimbangkan kaedah analisis menggunakan contoh kedai buku kecil dengan hanya 100 buku dalam stok. Sebahagian daripada mereka boleh dijual lebih mahal ($50), selebihnya akan dikenakan kos pembeli kurang ($20). Jumlah pendapatan daripada penjualan semua barangan dikira – pemilik memutuskan bahawa dia akan menjual 60% buku pada harga yang tinggi. Anda perlu mengetahui bagaimana hasil akan meningkat jika anda menaikkan harga volum barangan yang lebih besar – 70%, dan seterusnya.

Beri perhatian! Jumlah hasil mesti dikira menggunakan formula, jika tidak, jadual data tidak akan dapat disusun.

1. Pilih sel bebas dari tepi helaian dan tulis formula di dalamnya: =Sel jumlah hasil. Sebagai contoh, jika pendapatan ditulis dalam sel C14 (penamaan rawak ditunjukkan), anda perlu menulis ini: =S14.
2. Kami menulis peratusan volum barang dalam lajur di sebelah kiri sel ini – bukan di bawahnya, ini sangat penting.
3. Kami memilih julat sel di mana lajur peratusan dan pautan kepada jumlah pendapatan terletak.

4. Kami dapati pada tab "Data" item "Bagaimana jika analisis" dan klik padanya - dalam menu yang terbuka, pilih pilihan "Jadual data".

5. Tetingkap kecil akan dibuka di mana anda perlu menentukan sel dengan peratusan buku pada mulanya dijual pada harga yang tinggi dalam lajur "Ganti nilai dengan baris dalam ...". Langkah ini dilakukan untuk mengira semula jumlah hasil dengan mengambil kira peratusan yang semakin meningkat.

Selepas mengklik butang "OK" dalam tetingkap di mana data dimasukkan untuk menyusun jadual, hasil pengiraan akan muncul dalam baris.

Menambah Formula pada Jadual Data Pembolehubah Tunggal

Daripada jadual yang membantu mengira tindakan dengan hanya satu pembolehubah, anda boleh membuat alat analisis yang canggih dengan menambahkan formula tambahan. Ia mesti dimasukkan di sebelah formula yang sedia ada – contohnya, jika jadual berorientasikan baris, kami memasukkan ungkapan dalam sel di sebelah kanan yang sedia ada. Apabila orientasi lajur ditetapkan, kami menulis formula baharu di bawah formula lama. Seterusnya, ikuti algoritma:

1. Pilih julat sel sekali lagi, tetapi kini ia harus memasukkan formula baharu.
2. Buka menu analisis "bagaimana jika" dan pilih "Lembaran Data".
3. Kami menambah formula baharu pada medan yang sepadan dalam baris atau lajur, bergantung pada orientasi plat.

Buat jadual data dengan dua pembolehubah

Permulaan jadual sedemikian sedikit berbeza - anda perlu meletakkan pautan kepada jumlah hasil di atas nilai peratusan. Seterusnya, kami melakukan langkah-langkah ini:

1. Tulis pilihan harga dalam satu baris dengan pautan kepada pendapatan – satu sel untuk setiap harga.
2. Pilih julat sel.

3. Buka tetingkap jadual data, seperti semasa menyusun jadual dengan satu pembolehubah - melalui tab "Data" pada bar alat.
4. Gantikan dalam lajur "Ganti nilai dengan lajur dalam ..." sel dengan harga awal yang tinggi.
5. Tambahkan sel dengan peratusan awal jualan buku mahal pada lajur "Ganti nilai dengan baris dalam ..." dan klik "OK".

Akibatnya, seluruh jadual dipenuhi dengan jumlah pendapatan yang mungkin dengan syarat yang berbeza untuk penjualan barang.

Mempercepatkan pengiraan untuk lembaran kerja yang mengandungi jadual data

Jika anda memerlukan pengiraan pantas dalam jadual data yang tidak mencetuskan pengiraan semula keseluruhan buku kerja, terdapat beberapa perkara yang boleh anda lakukan untuk mempercepatkan proses.

1. Buka tetingkap pilihan, pilih item "Formula" dalam menu di sebelah kanan.
2. Pilih item "Automatik, kecuali untuk jadual data" dalam bahagian "Pengiraan dalam buku kerja".

3. Mari kita mengira semula keputusan dalam jadual secara manual. Untuk melakukan ini, pilih formula dan tekan kekunci F.

Alat Lain untuk Melakukan Analisis Sensitiviti

Terdapat alat lain dalam program ini untuk membantu anda melakukan analisis sensitiviti. Mereka mengautomasikan beberapa tindakan yang sebaliknya perlu dilakukan secara manual.

1. Fungsi "Pemilihan parameter" sesuai jika hasil yang diingini diketahui, dan anda perlu mengetahui nilai input pembolehubah untuk mendapatkan keputusan sedemikian.
2. "Cari penyelesaian" ialah alat tambah untuk menyelesaikan masalah. Ia adalah perlu untuk menetapkan had dan menunjuk kepada mereka, selepas itu sistem akan mencari jawapannya. Penyelesaian ditentukan dengan menukar nilai.
3. Analisis sensitiviti boleh dilakukan menggunakan Pengurus Senario. Alat ini ditemui dalam menu analisis bagaimana jika di bawah tab Data. Ia menggantikan nilai dalam beberapa sel – bilangannya boleh mencapai 32. Penghantar membandingkan nilai ini supaya pengguna tidak perlu menukarnya secara manual. Contoh menggunakan pengurus skrip:

Analisis sensitiviti projek pelaburan dalam Excel

Analisis bagaimana jika amat berguna dalam situasi di mana ramalan diperlukan, seperti pelaburan. Penganalisis menggunakan kaedah ini untuk mengetahui bagaimana nilai saham syarikat akan berubah akibat perubahan dalam beberapa faktor.

Kaedah Analisis Sensitiviti Pelaburan

Apabila menganalisis "bagaimana jika" gunakan penghitungan - manual atau automatik. Julat nilai diketahui, dan ia digantikan ke dalam formula satu demi satu. Hasilnya ialah satu set nilai. Pilih nombor yang sesuai daripada mereka. Mari kita pertimbangkan empat petunjuk yang mana analisis sensitiviti dijalankan dalam bidang kewangan:

1. Nilai Kini Bersih – Dikira dengan menolak jumlah pelaburan daripada jumlah pendapatan.
2. Kadar pulangan / keuntungan dalaman – menunjukkan berapa banyak keuntungan yang perlu diterima daripada pelaburan dalam setahun.
3. Nisbah bayaran balik ialah nisbah semua keuntungan kepada pelaburan awal.
4. Indeks keuntungan terdiskaun – menunjukkan keberkesanan pelaburan.

Formula

Kepekaan benam boleh dikira menggunakan formula ini: Perubahan dalam parameter output dalam % / Perubahan dalam parameter input dalam %.

Parameter output dan input boleh menjadi nilai yang diterangkan sebelum ini.

1. Anda perlu mengetahui hasilnya di bawah keadaan standard.
2. Kami menggantikan salah satu pembolehubah dan memantau perubahan dalam hasilnya.
3. Kami mengira peratusan perubahan kedua-dua parameter berbanding keadaan yang ditetapkan.
4. Kami memasukkan peratusan yang diperoleh ke dalam formula dan menentukan sensitiviti.

Contoh analisis sensitiviti projek pelaburan dalam Excel

Untuk pemahaman yang lebih baik tentang metodologi analisis, satu contoh diperlukan. Mari analisa projek dengan data yang diketahui berikut:

1. Isikan jadual untuk menganalisis projek di atasnya.

2. Kami mengira aliran tunai menggunakan fungsi OFFSET. Pada peringkat awal, aliran adalah sama dengan pelaburan. Seterusnya, kami menggunakan formula: =IF(OFFSET(Nombor,1;)=2;SUM(Aliran Masuk 1:Aliran Keluar 1);SUM(Aliran Masuk 1:Aliran Keluar 1)+$B$ 5)
   Penamaan sel dalam formula mungkin berbeza, bergantung pada susun atur jadual. Pada penghujungnya, nilai daripada data awal ditambah - nilai simpanan.

3. Kami menentukan tempoh projek itu akan dibayar. Untuk tempoh awal, kami menggunakan formula ini: =SUMMESLY(G7: G17;»<0″). Julat sel ialah lajur aliran tunai. Untuk tempoh selanjutnya, kami menggunakan formula ini: =Tempoh permulaan+JIKA(e.strim pertama>0; e.strim pertama;0). Projek ini berada pada titik pulang modal dalam tempoh 4 tahun.

4. Kami mencipta lajur untuk bilangan tempoh tersebut apabila projek itu membuahkan hasil.

5. Kami mengira pulangan pelaburan. Adalah perlu untuk membuat ungkapan di mana keuntungan dalam tempoh masa tertentu dibahagikan dengan pelaburan awal.

6. Kami menentukan faktor diskaun menggunakan formula ini: =1/(1+Cakera%) ^Nombor.

7. Kami mengira nilai semasa menggunakan pendaraban - aliran tunai didarab dengan faktor diskaun.

8. Mari kita mengira PI (indeks keuntungan). Nilai semasa dari semasa ke semasa dibahagikan dengan pelaburan pada permulaan projek.

9. Mari kita tentukan kadar pulangan dalaman menggunakan fungsi IRR: =IRR(Julat aliran tunai).

Analisis Sensitiviti Pelaburan Menggunakan Lembaran Data

Untuk analisis projek dalam bidang pelaburan, kaedah lain lebih sesuai daripada jadual data. Ramai pengguna mengalami kekeliruan apabila menyusun formula. Untuk mengetahui pergantungan satu faktor pada perubahan yang lain, anda perlu memilih sel yang betul untuk memasukkan pengiraan dan untuk membaca data.

Analisis faktor dan penyebaran dalam Excel dengan automasi pengiraan

Satu lagi tipologi analisis sensitiviti ialah analisis faktor dan analisis varians. Jenis pertama mentakrifkan hubungan antara nombor, yang kedua mendedahkan pergantungan satu pembolehubah pada yang lain.

ANOVA dalam Excel

Tujuan analisis sedemikian adalah untuk membahagikan kebolehubahan nilai kepada tiga komponen:

1. Kebolehubahan akibat pengaruh nilai lain.
2. Perubahan disebabkan oleh hubungan nilai yang mempengaruhinya.
3. Perubahan rawak.

Mari lakukan analisis varians melalui "Analisis Data" tambahan Excel. Jika ia tidak didayakan, ia boleh didayakan dalam tetapan.

Jadual awal mesti mengikut dua peraturan: terdapat satu lajur untuk setiap nilai, dan data di dalamnya disusun dalam tertib menaik atau menurun. Adalah perlu untuk menyemak pengaruh tahap pendidikan terhadap tingkah laku dalam konflik.

1. Cari alat Analisis Data dalam tab Data dan buka tetingkapnya. Dalam senarai, anda perlu memilih analisis varians sehala.

2. Isikan baris kotak dialog. Selang input ialah semua sel, tidak termasuk pengepala dan nombor. Himpunkan mengikut lajur. Kami memaparkan keputusan pada helaian baharu.

Oleh kerana nilai dalam sel kuning lebih besar daripada satu, andaian boleh dianggap tidak betul - tidak ada hubungan antara pendidikan dan tingkah laku dalam konflik.

Analisis faktor dalam Excel: contoh

Mari kita analisa hubungan data dalam bidang jualan – adalah perlu untuk mengenal pasti produk yang popular dan tidak popular. Maklumat awal:

1. Kita perlu mengetahui permintaan barangan mana yang paling banyak meningkat pada bulan kedua. Kami sedang menyusun jadual baharu untuk menentukan pertumbuhan dan penurunan dalam permintaan. Pertumbuhan dikira menggunakan formula ini: =IF((Permintaan 2-Permintaan 1)>0; Permintaan 2- Permintaan 1;0). Kurangkan formula: =IF(Pertumbuhan=0; Permintaan 1- Permintaan 2;0).

2. Kira pertumbuhan dalam permintaan barang sebagai peratusan: =IF(Pertumbuhan/Hasil 2 =0; Penurunan/Hasil 2; Pertumbuhan/Hasil 2).

3. Mari buat carta untuk kejelasan – pilih julat sel dan buat histogram melalui tab “Sisipkan”. Dalam tetapan, anda perlu mengalih keluar isian, ini boleh dilakukan melalui alat Format Data Series.

Analisis varians dua hala dalam Excel

Analisis varians dijalankan dengan beberapa pembolehubah. Pertimbangkan ini dengan contoh: anda perlu mengetahui seberapa cepat tindak balas kepada bunyi dengan kelantangan yang berbeza menunjukkan dirinya pada lelaki dan wanita.

1. Kami membuka "Analisis Data", dalam senarai yang anda perlukan untuk mencari analisis varians dua hala tanpa pengulangan.
2. Selang input – sel yang mengandungi data (tanpa pengepala). Kami memaparkan keputusan pada helaian baru dan klik "OK".

Nilai F adalah lebih besar daripada F-kritikal, yang bermaksud bahawa lantai mempengaruhi kelajuan tindak balas kepada bunyi.

Kesimpulan

Dalam artikel ini, analisis sensitiviti dalam hamparan Excel telah dibincangkan secara terperinci, supaya setiap pengguna akan dapat memahami kaedah aplikasinya.