Tutorial Analisis Sensitivitas di Excel: Ikhtisar, Jenis, dan Praktik Terbaik

Saat merancang sebuah model di spreadsheet, kita mungkin ingin mengetahui bagaimana suatu hasil dapat berubah ketika mengubah nilai salah satu variabel yang terlibat. Proses ini dapat dilakukan di Excel dengan menjalankan analisis sensitivitas

Mengetahui cara melakukan analisis sensitivitas sangat penting jika Anda seorang praktisi data yang bekerja dalam pemodelan keuangan atau proses pengambilan keputusan. 

Dalam artikel ini, kita akan membahas analisis sensitivitas secara mendalam. Kita akan mengulas dasar-dasar teknik bermanfaat ini dalam keluarga analisis “what-if” dan cara melakukan serta menafsirkan hasil analisis sensitivitas dasar dan lanjutan di Excel. Terakhir, kita akan membahas peran AI dan machine learning dalam menghadirkan analisis sensitivitas yang lebih kuat, serta praktik terbaik dan jebakan umum. 

Jika Anda ingin langsung mempraktikkannya, lihat kursus Financial Analysis in Excel kami, yang membahas analisis what-if secara mendetail. 

Apa itu Analisis Sensitivitas di Excel?

Pendekatan paling umum dalam analisis sensitivitas adalah menelaah bagaimana perubahan pada satu variabel pada satu waktu memengaruhi hasil akhir sambil menjaga semua faktor lain tetap konstan. Ini diulangi untuk semua variabel yang dipertimbangkan. Pendekatan ini membantu mengidentifikasi pendorong paling berpengaruh dalam sebuah model, sehingga bisnis dapat berfokus pada faktor-faktor kunci yang memengaruhi kinerja mereka. 

Analisis sensitivitas membantu bisnis memprioritaskan risiko dan peluang utama dengan mengidentifikasi faktor-faktor yang paling berdampak pada kinerja. Mengingat sifatnya, analisis sensitivitas umum digunakan di bidang keuangan, bisnis, dan manajemen proyek, misalnya untuk menghitung bagaimana suku bunga dapat berubah dari waktu ke waktu atau untuk memahami berbagai tren pertumbuhan. 

Lebih lanjut, sensitivitas sering dikombinasikan dengan analisis skenario, yang umumnya menganalisis kombinasi variabel secara bersamaan. Kedua analisis what-if ini mempelajari bagaimana variabel dependen akan bereaksi terhadap input tertentu. Namun, analisis skenario spesifik pada suatu "skenario" tertentu, sementara analisis sensitivitas lebih terbuka karena memberikan rentang input dan nilai. 

Tujuan analisis sensitivitas adalah memahami bagaimana variabel dependen bereaksi terhadap rentang nilai input, yang dikenal sebagai variabel independen.

Dengan memasukkan teknik ini ke dalam pengambilan keputusan, organisasi dapat secara proaktif menyesuaikan strategi dan meningkatkan ketahanan terhadap ketidakpastian. 

Untuk penjelasan lebih rinci tentang teori di balik analisis sensitivitas, kami sangat merekomendasikan kursus Advanced Probability: Uncertainty in Data.

Sering kali, analisis sensitivitas disajikan dalam tabel sensitivitas dengan format bersyarat yang menyoroti nilai dari tertinggi hingga terendah. 

Di bawah ini adalah contoh sensitivitas harga terhadap penawaran dan permintaan. Penawaran dan permintaan adalah variabel independen yang diberi label pada sumbu, dan harga adalah variabel dependen yang menampung nilai dalam tabel.

Kita akan bekerja dengan tabel sensitivitas Excel serupa di bagian selanjutnya.

Tabel sensitivitas. Sumber: DataCamp

Memulai Analisis Sensitivitas di Excel

Mari lihat cara melakukan analisis sensitivitas di Excel, mengikuti setiap langkah prosesnya. 

Alat dan fitur di Excel

Excel adalah alat praktis untuk membuat model matematika, termasuk analisis what-if. Analisis what-if pada dasarnya adalah membuat rumus yang memungkinkan pertanyaan dieksplorasi, seperti analisis sensitivitas.

Versi terbaru Excel hadir dengan Tab Data, yang menyediakan beragam alat dan kemungkinan untuk bekerja dengan data. Ini termasuk tombol What-if Analysis di pita menu. Terdapat tiga alat. Tombol Data Table secara khusus dirancang untuk membuat tabel sensitivitas.

Untuk melakukan analisis skenario, cukup klik tombol Scenario Manager.

Kita juga akan menggunakan Solver, sebuah add-in yang dapat Anda aktifkan dari Tab Developer. 

Solver digunakan dalam analisis what-if untuk mencari nilai optimal (maksimum atau minimum) bagi sebuah rumus di satu sel —disebut sel objektif— yang tunduk pada kendala, atau batasan, pada nilai sel rumus lain di lembar kerja.

Sebelum mengaktifkan Solver, Anda harus mengakses Tab Developer, yang tidak ditampilkan secara default. Untuk menampilkan tab ini:

• Masuk ke File > Options
• Pilih Customize Ribbon dari panel kiri
• Di kolom kanan di bawah "Main Tabs," centang kotak Developer

Melakukan Analisis Sensitivitas Satu Variabel

Sekarang alat kita sudah siap, mari mulai dengan membuat analisis sensitivitas satu variabel yang sederhana di Excel.

Menyiapkan model Anda

Langkah awal untuk melakukan analisis sensitivitas di Excel adalah mengidentifikasi input dan output yang akan menjadi dasar model Anda. 

Untuk tutorial ini, kita akan menggunakan analisis sensitivitas untuk mempelajari bagaimana laba bersih sebuah perusahaan penjual gitar akan bervariasi jika kita mengubah nilai variabel input tertentu, seperti jumlah gitar yang terjual, harga gitar, atau biaya produksi.

Di bawah ini, Anda dapat menemukan tabel dengan variabel input dan output. 

Saat menggunakan data table untuk analisis sensitivitas, penting untuk menautkan sel output ke variabel input agar Excel dapat memahami hubungan antarvariabel model. Proses ini dilakukan melalui rumus. Dalam contoh kita:

• Pendapatan dihitung dengan mengalikan harga per unit dengan unit terjual (=B2*B4)
• Biaya penjualan dihitung dengan mengalikan biaya produksi per unit dengan unit terjual (=B3*B4)
• Laba dihitung dengan mengurangkan pendapatan dari biaya penjualan (=B7-B8).

Membuat data table satu arah

Anda mungkin ingin mengetahui bagaimana laba berubah jika kita menaikkan atau menurunkan harga per unit, sambil menjaga biaya produksi dan jumlah unit terjual tetap. Saat kita menganalisis bagaimana sebuah variabel output berubah ketika kita memodifikasi nilai variabel input, kita memerlukan data table satu arah.

GIF berikut menunjukkan cara membuat data table satu arah di Excel. Seperti yang Anda lihat, tabel secara otomatis menghitung ulang laba untuk setiap unit terjual. Secara alami, laba saat kita menjual 250 unit sama dengan parameter awal (yaitu 50.000 €).

Anda juga mungkin ingin mempelajari bagaimana berbagai variabel output berubah ketika kita memodifikasi penjualan unit. Ini juga cukup mudah, seperti ditunjukkan di bawah. Dalam GIF berikut, kita menghitung bagaimana penjualan unit memengaruhi laba dan biaya penjualan.

Melakukan Analisis Sensitivitas Dua Variabel

Alih-alih menganalisis bagaimana suatu output dipengaruhi oleh satu input, Anda juga dapat membuat tabel dua variabel untuk menganalisis dampak dua variabel input. Misalnya Anda ingin mengetahui bagaimana laba berubah jika kita memodifikasi penjualan gitar dan harga per gitar. 

Pertama, Anda perlu membuat tabel dua dimensi dengan rentang harga per unit di baris dan penjualan unit di kolom. Lalu, di sudut kiri atas tabel, pilih variabel yang ingin Anda pelajari. Terakhir, pilih referensi sel dari variabel independen dalam tabel variabel input (yaitu harga per unit dan unit terjual).

Seperti yang Anda lihat, setelah Anda membuat tabel, Anda dapat dengan mudah mengubah variabel output di sudut kiri atas ke variabel lain, dan Excel akan menghitung ulang semua nilai

Menafsirkan Hasil Analisis Sensitivitas Excel

Sekarang Anda memiliki data table, langkah terakhir adalah menafsirkan hasil untuk menghasilkan wawasan tentang bagaimana perubahan input memengaruhi output.

Menganalisis output data table

Berdasarkan analisis sensitivitas ini, kita dapat membuat keputusan yang lebih tepat tentang laba. Berikut beberapa wawasan:

• Jika kita menaikkan harga gitar menjadi 350 €, kita bisa menyamai laba saat ini sebesar 50.000 € hanya dengan menjual 200 gitar.
• Jika kita menurunkan harga gitar menjadi 200 €, kita perlu menjual 500 unit untuk mencapai laba saat ini.
• Jika kita menurunkan jumlah gitar yang terjual menjadi 150, kita tidak akan menyamai laba saat ini kecuali kita mengubah harga gitar menjadi di atas 400 €.

Penafsiran analisis akan bergantung pada kebutuhan khusus perusahaan Anda. Mengajukan pertanyaan seperti berapa banyak gitar yang dapat Anda produksi, berapa tingkat laba minimum yang Anda butuhkan agar perusahaan tetap sehat, dan apa yang bisa Anda lakukan untuk menurunkan biaya produksi gitar akan krusial untuk mengekstrak wawasan bermakna dari analisis sensitivitas.

Menggunakan Solver untuk analisis sensitivitas

Saat Anda melakukan analisis sensitivitas, Anda mengembalikan serangkaian output potensial berdasarkan perubahan variabel input dari suatu model. Namun, Anda mungkin ingin mengetahui lebih banyak detail tentang cara kerja internal model Anda.

Untungnya, Excel dilengkapi alat yang kuat bernama Solver, yang dapat membantu Anda dalam tugas ini. Seperti disebutkan, Solver digunakan dalam analisis what-if untuk mencari nilai optimal (maksimum atau minimum) bagi sebuah rumus yang tunduk pada kendala, atau batasan, pada nilai sel rumus lain di lembar kerja. 

Ini mungkin terdengar seperti tujuan Goal Seek, jenis analisis What-If lain yang berfokus pada penyesuaian satu variabel untuk memenuhi hasil yang diinginkan. Namun, Solver jauh lebih dari sekadar Goal Seek. 

Pertama, Solver memungkinkan Anda menghitung hasil optimal berdasarkan lebih dari satu variabel dan memungkinkan Anda memasukkan kendala dalam model. 

Selain itu, Solver juga memiliki fitur sensitivitas opsional yang memberi Anda wawasan tentang bagaimana solusi optimal berubah ketika Anda mengubah koefisien model. 

Untuk menggambarkan kekuatan Solver, mari kembali ke perusahaan gitar kita. Bayangkan perusahaan memproduksi dua model gitar (model A dan B), masing-masing membutuhkan kombinasi mahoni dan cedar tertentu. Setiap model memiliki harga yang berbeda. 

Perusahaan ingin mengetahui berapa banyak unit dari setiap model yang harus diproduksi untuk memaksimalkan laba, mengingat ketersediaan mahoni dan cedar saat ini. 

Ada tiga hal yang kita ingin Solver hitung: Laba, unit Model A, dan unit Model B. Gambar di atas menunjukkan input, rumus, dan kendala yang akan menjadi dasar model. 

Dalam GIF berikut, kami menunjukkan cara menggunakan Solver untuk menemukan nilai unit yang optimal guna memaksimalkan keuntungan, menggunakan kendala ketersediaan kayu dan memastikan unit berupa bilangan bulat. Ada beberapa strategi yang dapat digunakan Solver untuk mengoptimalkan model, baik untuk masalah linear maupun non-linear. Dalam kasus kita, kita akan menggunakan Simple LP. 

Setelah kita menambahkan semua nilai parameter, Solver memperkirakan bahwa 48 unit model A dan 9 unit model B adalah kombinasi terbaik untuk memaksimalkan laba (17.591 €). Kita dapat mengklik Sensitivity sebelum menutup kotak Solver, dan Excel akan membuat lembar baru dengan laporan Sensitivity.

Laporan sensitivitas memberikan wawasan berharga untuk memperkirakan bagaimana laba akan bervariasi jika kita mengubah variabel input. Shadow Price menunjukkan seberapa banyak laba akan meningkat jika Anda memiliki satu unit tambahan dari sumber daya yang terkendala. Dalam contoh ini, setiap tambahan satu meter persegi mahoni meningkatkan laba sebesar €98, sedangkan cedar meningkatkan laba sebesar €103, menonjolkan potensi pengembalian investasi cedar yang lebih besar

Elemen penting lain dalam laporan sensitivitas adalah rentang di mana Anda dapat mempertahankan harga model agar hasil model tetap tidak berubah. 

Laba dihitung dengan mengalikan unit setiap model dengan harganya, yang tercantum di kolom Final Value dan Objective Coefficient. Rentangnya tercantum di kolom Allowable Increase dan Allowable Decrease. 

Sebagai contoh, Solver akan menemukan jumlah unit produksi yang sama jika Anda menaikkan nilai Model A sebesar 1.140 € atau menurunkannya sebesar 180 €.

Memvisualisasikan data dengan conditional formatting

Jika Anda membuat tabel sensitivitas satu arah dan dua arah, memformat hasil dapat menjadi pembeda untuk dengan cepat menemukan informasi relevan dan meningkatkan pengambilan keputusan. Conditional formatting adalah kunci untuk membuat ambang batas penting dan heatmap yang menarik. 

Mari kembali ke tabel dua arah kita. Alih-alih menampilkan tabel dalam sel putih, kita dapat menggunakan conditional formatting untuk membuat heatmap multiwarna dari skala dua warna, menetapkan ambang khusus, seperti ditunjukkan pada GIF.

Mengintegrasikan AI dan Machine Learning

Mari bahas secara singkat bagaimana AI dan machine learning dapat membantu meningkatkan analisis sensitivitas Anda.

Analisis sensitivitas yang ditingkatkan AI

Sejauh ini, kita telah melihat cara melakukan analisis sensitivitas secara manual. Namun, seiring meningkatnya kompleksitas model Anda, melakukan dan menafsirkan hasil analisis sensitivitas dapat memakan waktu. 

Di sinilah AI berperan. Dengan AI dan machine learning, Anda dapat membiarkan mesin melakukan perhitungan setiap kali kondisi tertentu terpenuhi dan memasukkan hasilnya ke dalam dasbor yang menarik. 

Setelah Anda mengetahui perhitungan yang ingin dilakukan, langkah berikutnya adalah mendefinisikan pipeline AI dengan jelas untuk merapikan dan mengotomatiskan prosesnya. 

Ingin tahu caranya? Lihat Tutorial Machine Learning, Pipelines, Deployment and MLOps kami.

Simulasi Monte Carlo

Simulasi Monte Carlo adalah teknik matematika yang digunakan untuk memodelkan probabilitas berbagai hasil dalam suatu proses yang tidak mudah diprediksi karena adanya variabel acak.

Masalah terlebih dahulu dimodelkan dengan distribusi probabilitas untuk setiap variabel yang memiliki ketidakpastian bawaan. Sejumlah besar sampel acak kemudian diambil dari distribusi probabilitas ini, dan sampel tersebut digunakan untuk menghitung hasilnya. 

Proses ini diulangi berkali-kali untuk membuat distribusi kemungkinan hasil, yang dapat dianalisis secara statistik untuk memberikan prediksi tentang bagaimana suatu sistem akan berperilaku. Monte Carlo membantu menganalisis ketidakpastian secara lebih komprehensif dibandingkan tabel sensitivitas tradisional.

Anda dapat mempelajari segala hal tentang teknik yang kuat ini dalam Panduan Simulasi Monte Carlo di Excel kami.

Mengingat sifat simulasi Monte Carlo, Anda dapat menggunakannya untuk memperkuat analisis sensitivitas Anda guna menemukan variabel mana yang paling berdampak pada output. 

Dalam contoh gitar kita, kita dapat menahan harga pokok dan unit terjual tetap, serta mengubah harga per unit untuk memahami perubahan estimasi. Lalu, ulangi proses yang sama untuk dua variabel lainnya satu per satu. Pada akhirnya, teknik ini membantu memahami variabel mana yang perlu difokuskan untuk meningkatkan akurasi.

Praktik Terbaik dan Jebakan Umum

Meskipun Excel membuat analisis sensitivitas sangat mudah dan dapat diakses, masih ada beberapa jebakan umum yang perlu diingat. Berikut beberapa hal yang perlu dipertimbangkan.

Merancang model yang tangguh

Seperti yang Anda lihat dalam tutorial ini, agar analisis sensitivitas berfungsi, Excel harus mengetahui hubungan antara variabel input dan output. Ini mengharuskan Anda menggunakan rumus secara bijak, dengan menyediakan referensi sel dan rentang yang terdefinisi jelas. 

Jika Anda ingin menambahkan nilai baru ke data table, praktik yang baik untuk menghindari potensi masalah saat penghitungan ulang adalah menggunakan Excel Tables, karena secara bawaan bersifat dinamis, artinya tabel akan memperluas diri saat baris atau kolom baru ditambahkan. 

Ini akan membantu Anda melakukan penghitungan ulang rumus yang tepat agar informasi tetap pada format yang benar. 

Mengurangi kesalahan dan memastikan akurasi

Meskipun analisis sensitivitas Anda didefinisikan dengan jelas, Anda perlu memverifikasi hasil dengan saksama. Pengujian manual dan bagan adalah opsi yang bagus untuk memvisualisasikan hasil dan memeriksa outlier atau anomali pada output.  

Namun, penting untuk diingat bahwa analisis sensitivitas mungkin saja tidak cocok untuk kasus penggunaan Anda. Analisis ini didasarkan pada serangkaian asumsi sederhana, seperti independensi variabel input, keberadaan hubungan linear, dan sifat statis variabel input. 

Akan tetapi, model Anda mungkin lebih kompleks dan mungkin dipengaruhi oleh faktor eksternal yang secara bawaan diabaikan oleh analisis sensitivitas. Oleh karena itu, meskipun bermanfaat, penting untuk memiliki pemahaman menyeluruh tentang model Anda di luar sensitivitas untuk memastikan analisis yang tepat.

Kesimpulan

Selamat telah mencapai akhir tutorial ini. Analisis sensitivitas adalah analisis what-if yang kuat yang dapat menjadi pembeda dalam proses pengambilan keputusan Anda. Ada analisis what-if lain untuk melengkapi model keuangan Anda, seperti Goal Seek atau Scenario Manager. Untungnya, DataCamp siap membantu Anda. Lihat kursus dan materi Excel pilihan kami:

• Data Analysis in Excel
• Financial Modeling in Excel
• Excel Fundamentals
• Data Analysis in Google Sheets
• Advanced Excel Functions
• How to Calculate Confidence Intervals in Excel
• Excel Formulas Cheat Sheet
• How to Calculate the Coefficient of Variation in Excel
• Monte Carlo Simulation in Excel: A Complete Guide