Bagaimana melakukan analisis sensitivitas di Excel | Langkah-langkah membuat tabel WACC x Laju Pertumbuhan Permanen menggunakan tabel data (analisis Bagaimana-Jika) / Apa yang harus dilakukan jika tidak dihitung

Jika Anda ingin membuat tabel analisis sensitivitas di Excel,Tabel data (analisis Bagaimana-Jika)paling pasti. Ada lima langkah untuk membuat tabel analisis sensitivitas untuk metode DCF. ① Gabungkan asumsi yang akan diubah (WACC/tingkat pertumbuhan permanen) ke dalam satu sel dan buat semua perhitungan dalam model mengacu pada sel tersebut. ② Tempatkan rumus yang mengacu pada sel hasil (nilai stok) di kiri atas tabel, dan langsung masukkan nilai kandidat pada sumbu horizontal dan vertikal. ③ Pilih rentang tabel dan buka tab “Data” → “Analisis Bagaimana-Jika” → “Tabel Data”. ④ Tentukan sel yang berisi asumsi sumbu horizontal di "Sel Tugas Baris" dan sel yang berisi asumsi sumbu vertikal di "Sel Tugas untuk Kolom". ⑤ Periksa apakah nilai di tengah tabel sesuai dengan hasil model.

Sebagian besar alasan mengapa tidak berhasil adalah karena asumsi ditulis langsung ke dalam rumus, nilai sumbu merujuk ke sel dalam model, sel substitusi ada di lembar lain, atau metode penghitungan disetel ke "Otomatis kecuali untuk tabel data".

Pada artikel ini, kami akan menjelaskan prosedur pembuatan tabel analisis sensitivitas di Excel menggunakan contoh model DCF dan diagram operasional, serta cara mengatasi kesalahan umum dan cara menggunakannya dalam penghitungan harga saham dan pengujian penurunan nilai. Harap dicatat bahwa gambar tersebut bukanlah layar Excel sebenarnya, namun diagram skematik untuk mereproduksi operasi.

Jika Anda ingin melakukan analisis sensitivitas di Excel: Apa itu tabel data?

Tabel data adalah salah satu fungsi "Analisis Bagaimana-Jika" di tab "Data" Excel, dan merupakan fungsi yang secara berurutan menggantikan nilai dari satu atau dua sel masukan dalam model dan merangkum hasil yang dihitung setiap kali dalam sebuah tabel. Dengan kata lain, Excel secara otomatis melakukan pekerjaan menulis ulang asumsi secara manual dan menyalin hasilnya. Ada ``tabel data 1 variabel'' yang mengubah satu masukan, dan ``tabel data 2 variabel'' yang mengubah 2 masukan sekaligus. Dalam analisis sensitivitas menggunakan metode DCF, tabel data dua variabel yang menggabungkan WACC (sumbu vertikal) dan laju pertumbuhan permanen (sumbu horizontal) adalah standarnya.

Contoh model DCF untuk analisis sensitivitas di Excel

Contoh yang digunakan untuk penjelasan adalah sebagai berikut (satuannya sewenang-wenang).

• Arus kas bebas (FCF) untuk periode perkiraan 5 tahun: 100, 110, 120, 130, 140
• Tingkat pertumbuhan permanen (セルC4): 0%
• WACC (sel C5): 0%
• Hutang bersih (sel C6): 200
• Nilai lanjutan = FCF tahun ke-5 × (1 + tingkat pertumbuhan abadi) (WACC - tingkat pertumbuhan abadi) = 140 × 1.01 − 0.09 ＝ 1,571.1
• Nilai bisnis = Total nilai sekarang FCF setiap tahun7 + Nilai sekarang dari nilai berkelanjutan 975.5 ＝ 1,423.2
• Nilai saham (sel C17) = nilai bisnis 1,423.2 - Hutang bersih 200 ＝ 1,223.2
  

  Gambar 15-1 Contoh model DCF dan kerangka tabel analisis sensitivitas (diagram skema operasi). Pembilang nilai berkelanjutan adalah FCF x tahun ke-5 (1 + C4), dan penyebutnya adalah (C5 - C4), keduanya mengacu pada sel premis.

Persiapan sebelum analisis sensitivitas di Excel: 90% adalah cara membuat model

1. Asumsi yang ingin Anda variasikan ada dalam "satu sel": Jika WACC atau tingkat pertumbuhan abadi ditulis langsung sebagai angka dalam beberapa rumus, tabel data tidak akan berfungsi. Misalnya, daripada menulis nilai numerik secara langsung seperti "=I9*(1+1%)/(10%-1%)", rumus untuk menghitung nilai berkelanjutan ditulis dengan mengacu pada sel premis seperti "=I9*(1+$C$4)/($C$5-$C$4)".
2. Pastikan semua penghitungan mengacu pada sel asumsi tersebut: Faktor diskon (=1/(1+$C$5)^tahun), Nilai Berkelanjutan, dan Nilai Sekarang dari Nilai Berkelanjutan semuanya mengacu pada C5. Sebab jika ada input langsung pada satu tempat saja, maka hasil tabel datanya akan mati.
3. Sel penetapan harus berada pada lembar yang sama dengan tabel data: "sel penetapan baris" dan "sel penetapan kolom" pada tabel data harus merupakan sel pada lembar yang sama dengan tabel data. Jika Anda menentukan sel di lembar lain, kesalahan akan terjadi yang mengatakan "Referensi sel masukan salah." Solusinya akan dijelaskan nanti.

Langkah-langkah membuat tabel analisis sensitivitas di excel (tabel data 2 variabel)

Secara khusus, kami memvariasikan WACC dalam kisaran 10% ± 1% dan tingkat pertumbuhan abadi dalam kisaran 1% ± 1%, dan membuat tabulasi perubahan nilai saham.

Langkah 1: Buat bingkai tabel

• Sel pojok kiri atas (B20): Merujuk ke sel yang Anda inginkan sebagai hasil rumus ("=C17").
• Sisi kanan sel sudut (sumbu horizontal, C20 hingga G20): Langsung masukkan nilai kandidat tingkat pertumbuhan permanen 0%, 0,5%, 1,0%, 1,5%, 2,0%.
• Sel sudut bawah (sumbu vertikal, B21 hingga B25): Langsung masukkan nilai kandidat WACC 0%, 9,5%, 10,0%, 10,5%, 11,0%.

Silakan langsung masukkan nilai numerik untuk calon nilai sumbu horizontal dan vertikal. Ini karena jika Anda merujuk ke C4 atau C5 dalam model dan membuat sesuatu seperti "=C5+1%", tabel data akan berpindah ke nilai sumbu setiap kali sel yang ditetapkan diganti, dan hasilnya tidak akan benar.

Langkah 2: Pilih rentang dan luncurkan tabel data

Dari sel sudut B20, pilih rentang persegi panjang B20:G25 yang mencakup sel kosong yang akan berisi nilai kandidat dan hasil untuk sumbu horizontal dan vertikal, dan klik tab "Data" → "Analisis Bagaimana-Jika" → "Tabel Data". Alternatifnya, pada keyboard, tekan Alt, lalu A, W, T.

Langkah 3: Tentukan sel tugas

Dalam dialog "Sel Substitusi Baris", tentukan tujuan penggantian nilai kandidat yang disusun secara horizontal (baris) dalam tabel, yaitu sel asumsi C4 dari tingkat pertumbuhan permanen. Di "Sel penetapan kolom", tentukan tujuan untuk menetapkan nilai kandidat yang disusun secara vertikal (kolom), yaitu sel C5 prasyarat WACC, dan tekan OK.

Namun, jika Anda membalikkan baris dan kolom di sini, nilai kandidat untuk tingkat pertumbuhan permanen (0 hingga 2%) akan ditetapkan ke sel WACC, dan nilai kandidat untuk WACC (9 hingga 11%) akan ditetapkan ke sel tingkat pertumbuhan permanen. Tabel tidak diubah urutannya, namun asumsi modelnya sendiri tertukar, sehingga menghasilkan hasil yang salah. Oleh karena itu, pastikan untuk memeriksa korespondensi antara judul tabel dan kotak dialog.

Gambar 15-2 Korespondensi antara dialog tabel data dan sel penugasan (diagram skema operasi). Nilai yang disusun secara horizontal ditetapkan ke "sel penetapan baris", dan nilai yang disusun secara vertikal ditetapkan ke "sel penetapan kolom".

Langkah 4: Periksa hasilnya

Tabel tersebut menampilkan nilai saham untuk setiap kombinasi sekaligus. Jika WACC model dan tingkat pertumbuhan abadi 10%/1%, nilai di tengah tabel (WACC10,0% x tingkat pertumbuhan abadi 1,0%) harus sesuai dengan nilai saham model sebesar 1,223. Selain itu, untuk memastikan, coba ubah C4 dan C5 dalam model secara manual dan periksa apakah nilainya sama dengan sel terkait di tabel.

Gambar 15-3 Tabel analisis sensitivitas yang lengkap (contoh hasil perhitungan, satuannya sewenang-wenang). Angka-angka tersebut dihitung dari model contoh.

Langkah 5: Sesuaikan tampilannya

Sel B20 pojok kiri atas menampilkan nilai nilai saham apa adanya sehingga tidak terlihat bagus apa adanya. Oleh karena itu, dengan mengatur format tampilan sel menjadi ";;;" (tiga titik koma), Anda dapat menyembunyikan rumus sambil menyimpannya. Menambahkan batas atau teks tebal ke sel kasus dasar (kombinasi yang sama dengan asumsi model) memudahkan pembaca untuk memahami kriteria. Selain itu, menggunakan pemformatan bersyarat untuk membuat bayangan sel di atas harga tertentu (misalnya, harga perolehan) dapat berguna dalam bahan papan.

Jika sel masukan ada di lembar lain

Jika asumsi dirangkum pada lembar masukan dan model ditempatkan pada lembar terpisah, sel pada lembar masukan tidak dapat ditentukan sebagai sel substitusi dalam tabel data. Jadi, cobalah salah satu cara berikut:

4. Tempatkan tabel data pada lembar masukan: Pertama, buat tabel data pada lembar yang sama dengan sel penugasan (C4 dan C5 pada lembar masukan). Di sel sudut, tempatkan rumus ("=DCF!C17") yang mengacu pada sel hasil lembar model.
5. Buat sel relai pada lembar model dan buat penghitungan model mengacu pada sel relai tersebut: Misalnya, buat sel relai dengan "=input!C4" di C4 lembar model dan "=input!C5" di C5. Kemudian sambungkan kembali semua rumus dalam model Anda, seperti faktor diskon dan nilai berkelanjutan, untuk mereferensikan sel relai ini, bukan lembar masukan. Terakhir, tentukan sel relai C4 dan C5 di sel substitusi tabel data. Selain itu, jika Anda baru saja membuat sel relai dan rumus dalam model terus merujuk langsung ke lembar masukan, hasil model tidak akan berubah meskipun tabel data menggantikan sel relai, dan semua nilai dalam tabel akan sama.

Kesalahan umum dan solusi ketika analisis sensitivitas tidak berfungsi di Excel

Catatan tentang pengaturan "Otomatis kecuali tabel data"

Jika disetel ke "Otomatis kecuali tabel data", jika Anda lupa memperbarui tabel setelah memodifikasi model, ada risiko hasil lama akan ditempelkan ke dalam dokumen. Oleh karena itu, sebelum melakukan dokumentasi, biasakan menghitung ulang menggunakan F9 dan memeriksa apakah sel tengah sudah sesuai dengan hasil model.

Cara menggunakannya dalam perhitungan harga saham/uji penurunan nilai

Dari sudut pandang penilai, biasanya ditampilkan tabel yang WACC dan tingkat pertumbuhan abadi yang mereka tetapkan bervariasi. Di sisi lain, jika Anda berada dalam posisi membuat rencana bisnis, tabel berdasarkan asumsi rencana bisnis, seperti tingkat pertumbuhan penjualan dan margin laba operasi, atau margin laba operasi dan tingkat investasi modal, juga berguna. Bagaimanapun, ingatlah hal-hal berikut:

• Kisaran fluktuasi harus berada dalam "kisaran yang memungkinkan": umumnya ±1 hingga 2% untuk WACC, dan 0 hingga 2% untuk tingkat pertumbuhan permanen. Sebaliknya jika lebarnya terlalu ekstrim maka tabel menjadi kurang bermakna.
• Jangan tampilkan kombinasi yang tingkat pertumbuhan abadinya mendekati WACC: Hal ini karena penyebut rumus penghitungan nilai berkelanjutan (WACC - tingkat pertumbuhan) menjadi kecil dan nilainya menjadi sangat besar.
• Pengujian penurunan nilai menunjukkan "tempat yang menyebabkan penurunan nilai": Dengan mengarsir kombinasi yang jumlah terpulihkannya lebih kecil dari nilai buku, Anda dapat melihat ruang kosong secara sekilas. Berdasarkan IFRS, catatan sensitivitas diperlukan ketika perubahan asumsi yang mungkin terjadi mengakibatkan kerugian penurunan nilai, sehingga tabel ini berfungsi sebagai dasar catatan tersebut.

1Memanfaatkan tabel data variabel

• 1Memanfaatkan tabel data variabel: Anda juga dapat memvariasikan satu asumsi, seperti hanya tingkat pertumbuhan penjualan atau hanya tingkat laba operasional, dan menampilkan beberapa hasil seperti nilai saham, kelipatan EV/EBITDA, IRR, dll. secara berdampingan. Secara khusus, sejajarkan rumus yang merujuk ke beberapa sel hasil di sisi kanan sel sudut, dan tentukan hanya "sel penetapan kolom".

Bahkan jika Anda membuat tabel analisis sensitivitas di Excel, premisnya tetap bahwa ``WACC yang dimasukkan dan tingkat pertumbuhan abadi adalah wajar.''

ringkasan

Untuk meringkas cara membuat tabel analisis sensitivitas di Excel, tabel data merupakan fitur standar Excel yang memungkinkan Anda membuat tabel analisis sensitivitas untuk metode DCF sekaligus. Ada empat kunci untuk menggunakannya dengan sukses. Kelompokkan asumsi yang ingin Anda variasikan ke dalam satu sel, dan buat semua penghitungan dalam model mengacu pada sel tersebut. Masukkan nilai kandidat sumbu secara langsung. Tempatkan sel substitusi dan tabel data pada lembar yang sama (jika berada pada lembar terpisah, sambungkan kembali model ke sel relai). Tentukan sumbu horizontal untuk sel penetapan baris dan sumbu vertikal untuk sel penetapan kolom. Jika Excel menjadi lambat, harap atur metode penghitungan ke "Otomatis kecuali tabel data" dan pastikan untuk menghitung ulang dan memeriksa nilai pusat sebelum mendokumentasikan.

Hal-hal yang dapat Anda diskusikan dengan Keystone Advisory

Di Keystone Advisory, kami membuat model penghitungan untuk penghitungan harga saham, PPA, dan uji penurunan nilai, termasuk analisis sensitivitas, dan memberikan model penghitungan (Excel). Silakan berkonsultasi dengan kami tentang praktik melakukan analisis sensitivitas di Excel, seperti meninjau model penilaian yang telah Anda buat sendiri (hubungan referensi sel asumsi, perhitungan nilai berkelanjutan, dasar tingkat diskonto), membuat bahan analisis sensitivitas untuk diserahkan ke perusahaan audit, dan menghitung WACC untuk dimasukkan.Formulir kontakSilakan hubungi kami dari.

Referensi

Dukungan Microsoft "Hitung beberapa hasil menggunakan tabel data"

Penulis

Kensuke Tanabe (CPA)

Penasihat Keystone

Terlibat dalam pekerjaan penilaian/PPA, audit undang-undang, dan dukungan IPO di Tohmatsu Audit Corporation dan PwC Advisory. Dia mengambil posisinya saat ini setelah bekerja sebagai direktur di dana PE dan emiten di perusahaan yang beroperasi.

*Prosedur pengoperasian dalam artikel ini menggunakan versi Microsoft 365 Excel (Windows). Nama dan posisi menu mungkin berbeda tergantung versinya. Gambar tersebut merupakan diagram skema operasi dan bukan layar Excel sebenarnya. Nilai numerik dalam contoh telah disederhanakan demi pemahaman.