Pemodelan KeuanganLanjutan6 menit baca

Bangun Model DCF AeroVironment di Google Sheets

Bangun DCF AeroVironment 6-tab di Google Sheets dengan skenario bear/base/bull. Kasus dasar menghasilkan $7,20/saham vs. harga pasar ~$175.

Panduan ini memandu Anda membangun model DCF 5 tahun untuk AeroVironment (AVAV) di Google Sheets, lengkap dengan grafik waterfall skenario, yang menghasilkan nilai intrinsik kasus dasar sekitar $7,20/saham dibandingkan harga pasar yang berkisar di $175. Selisihnya bukan kesalahan pemodelan. Itulah intinya. AVAV diperdagangkan berdasarkan optionalitas pertahanan dan narasi geopolitik, bukan arus kas bebas saat ini. Model ini memungkinkan Anda mengukur apa yang diproyeksikan pasar dibandingkan apa yang sebenarnya didukung oleh laporan keuangan historis, lalu menampilkannya secara visual dalam satu grafik yang tidak bisa diabaikan oleh PM Anda. Model ini mencakup 6 tab yang saling terhubung: Assumptions, P&L, FCFF, DCF, Sensitivity, dan Chart.

Yang Anda Perlukan

Akun Google Sheets dengan akses edit ke workbook kosong
AeroVironment 10-K FY2025 (tahun fiskal berakhir 30 April 2025) dan 8-K terbaru
Jumlah saham diluted AVAV: 76,2 juta (dari proxy atau halaman sampul 10-K)
Pemahaman tentang mekanisme unlevered free cash flow dan konstruksi terminal value
30-45 menit waktu membangun yang terfokus

Panduan Langkah demi Langkah

Bangun Tab Assumptions

Tab Assumptions adalah panel kontrol untuk seluruh model. Setiap variabel penggerak - pertumbuhan pendapatan, margin EBIT, WACC, pertumbuhan terminal - ada di sini. Semua tab lainnya mereferensikan tab ini. Jika ada angka yang muncul lebih dari sekali di model Anda tanpa ditelusuri ke tab ini, Anda sudah menciptakan masalah rekonsiliasi.

Beri nama tab ini Assumptions. Kolom A menampung label variabel penggerak, kolom B menampung nilai, kolom C menampung catatan.

B3**: Pendapatan dasar FY2025: 850 (dalam $M). AVAV melaporkan pendapatan $847,8 juta di FY2025 per 10-K yang diajukan Juni 2025.
B4:B8**: Pertumbuhan pendapatan per tahun. Gunakan 8%, 10%, 12%, 10%, 8% untuk kasus dasar. Ini memperhitungkan ramp Switchblade dan Teal Drones dengan risiko eksekusi yang sudah tertanam.
B10**: Margin EBIT (kasus dasar): 6,0%. Margin operasi adjusted FY2025 AVAV mendekati 5,8% per suplemen laporan laba rugi.
B15**: WACC: 11,5%. Diturunkan dari beta ~1,3, tingkat bebas risiko 4,5%, ERP 6%, leverage minimal.
B16**: Tingkat pertumbuhan terminal: 3,0%
B17**: Tarif pajak: 21%
B18**: Kas bersih (FY2025): 280 (dalam $M, dari neraca)
B19**: Saham diluted: 76,2 (dalam juta)
B20**: Harga pasar saat ini: 175 (perbarui sesuai kebutuhan untuk formula % selisih di Langkah 4)

Pro Tip

Terapkan named ranges untuk B15, B16, B17, B19 (WACC, TermGrowth, TaxRate, Shares). Named ranges membuat formula tab DCF lebih mudah dibaca dan mendeteksi kesalahan koneksi sebelum berkembang di 5 tahun proyeksi.

Proyeksikan Pendapatan dan EBIT di Tab P&L

Tambahkan tab bernama P&L. Kolom B hingga G menampung FY2025A hingga FY2030E. Baris 2 adalah header tahun. Aktual FY2025 ada di kolom B, proyeksi di C:G.

Setiap tahun ke depan adalah formula pertumbuhan yang mengacu pada Assumptions. Tidak ada angka hardcoded setelah kolom B.

C3 (Pendapatan FY2026)**: ='P&L'!B3*(1+Assumptions!$B$4) - seret ke kanan hingga FY2030, tambahkan referensi baris tingkat pertumbuhan untuk setiap tahun ($B$5, $B$6, dst.)
C5 (EBIT FY2026)**: ='P&L'!C3*Assumptions!$B$10
C6 (D&A)**: ='P&L'!C3*0.056 - 5,6% dari pendapatan, sesuai dengan D&A FY2025 AVAV sebesar $47,5 juta pada pendapatan $847,8 juta
C7 (EBITDA)**: ='P&L'!C5+'P&L'!C6
C8 (Capex)**: =-'P&L'!C3*0.065 - dimasukkan negatif; 6,5% dari pendapatan mencerminkan investasi manufaktur drone di atas D&A
C9 (ΔNWC)**: =-'P&L'!C3*0.018 - pembangunan NWC sebesar 1,8% dari pendapatan, juga dimasukkan negatif

Pro Tip

Warnai kolom B (aktual) dengan abu-abu dan kunci. Pengeditan tidak sengaja pada jangkar FY2025 akan merusak setiap kalkulasi hilir dan mengejutkan sulitnya dideteksi dalam model yang panjang.

Hitung Unlevered Free Cash Flow

Tambahkan tab bernama FCFF. Tab ini sepenuhnya mengacu pada P&L - tidak ada entri manual selain header. Setiap sel adalah referensi lintas tab.

FCFF = EBIT × (1 - Tarif Pajak) + D&A - Capex - ΔNWC

C3 (NOPAT)**: ='P&L'!C5*(1-Assumptions!$B$17)
C4 (Tambahan D&A)**: ='P&L'!C6
C5 (Capex)**: =-'P&L'!C8 - P&L memiliki capex sebagai negatif; balik tanda di sini agar penjumlahan FCFF bekerja dengan benar
C6 (ΔNWC)**: =-'P&L'!C9
C7 (FCFF)**: =SUM('FCFF'!C3:C6)

Pro Tip

Jika FCFF Anda negatif di tahun-tahun awal, periksa NWC. Perusahaan yang menumbuhkan pendapatan 10%+ per tahun akan mengonsumsi modal kerja. Pembakaran NWC tahunan $15-18 juta pada pendapatan $850-950 juta adalah realistis untuk profil manufaktur AVAV.

Bangun Mesin DCF dan Terminal Value

Tambahkan tab bernama DCF. Di sinilah angka $7,20 dihitung. Struktur: diskon FCFF setiap tahun dengan WACC, hitung terminal value Gordon Growth dari FCFF Tahun 5, jumlahkan semuanya, tambahkan kas bersih, bagi dengan jumlah saham.

C3:G3 (Faktor diskon)**: =1/(1+Assumptions!$B$15)^(COLUMN(C3)-2) - menghasilkan 1/1,115^1 hingga 1/1,115^5. Kunci offset kolom agar seret bekerja dengan benar.
C4:G4 (PV dari FCFF)**: ='FCFF'!C7*DCF!C3 seret ke kanan
B6 (Jumlah PV FCFF)**: =SUM(DCF!C4:G4) - kasus dasar mendekati $79-81 juta
B8 (Terminal FCFF)**: ='FCFF'!G7*(1+Assumptions!$B$16) - FCFF Tahun 5 ditumbuhkan satu periode
B9 (Terminal Value)**: =DCF!B8/(Assumptions!$B$15-Assumptions!$B$16) - Gordon Growth; kasus dasar ~$325 juta
B10 (PV dari Terminal Value)**: =DCF!B9*DCF!G3 - didiskon pada faktor Tahun 5; kasus dasar ~$189 juta
B12 (Enterprise Value)**: =DCF!B6+DCF!B10 - kasus dasar ~$268 juta
B13 (Equity Value)**: =DCF!B12+Assumptions!$B$18 - tambahkan kas bersih $280 juta
B14 (Harga Saham Implied)**: =DCF!B13/Assumptions!$B$19 - bagi dengan 76,2 juta saham

Pro Tip

Lakukan sanity-check terminal value sebagai persentase dari total enterprise value. Jika PV dari TV melebihi 75% dari EV, FCF jangka pendek Anda begitu tipis sehingga model ini pada dasarnya adalah latihan terminal value. Untuk AVAV dalam kasus dasar, PV dari TV adalah ~70% dari EV. Itu tidak nyaman tapi jujur mengingat profil FCF-nya.

Bangun Analisis Skenario Bear / Base / Bull

Tambahkan tab bernama Sensitivity. Strukturkan ini sebagai matriks skenario 3 kolom dengan baris variabel penggerak bernama, bukan tabel data dua variabel. Skenario bernama lebih jelas dikomunikasikan dalam presentasi board.

Driver	Bear	Base	Bull
Revenue CAGR (5 thn)	6%	9%	18%
Margin EBIT	4,5%	6,0%	10,5%
WACC	13,0%	11,5%	10,0%
Terminal Growth	2,0%	3,0%	3,5%
Harga Implied	$4,10	$7,20	$22,40

Kasus bull mengharuskan AVAV mencapai margin setara Adobe pada produsen perangkat keras dan kompresi WACC yang mengasumsikan transformasi model bisnis yang signifikan. Bahkan pada $22,40 pun saham ini diperdagangkan sekitar 8x nilai intrinsiknya.

Untuk membuat skenario menjadi dinamis alih-alih statis, tambahkan dropdown Data Validation di Sensitivity!$B$1 (opsi: Bear, Base, Bull). Kemudian di tab DCF Anda, ganti referensi Assumptions langsung dengan conditional lookups:

=IF(Sensitivity!$B$1="Bull",Sensitivity!$D$3,
   IF(Sensitivity!$B$1="Bear",Sensitivity!$B$3,
   Sensitivity!$C$3))

Satu perubahan dropdown mengalir ke seluruh model dalam waktu kurang dari satu detik.

Tambahkan baris implied EV/EBITDA di bagian bawah tabel skenario, yang mereferensikan ='P&L'!C7 untuk EBITDA FY2026. Bear: ~3,1x. Base: ~5,5x. Bull: ~17x. Pasar saat ini memproyeksikan AVAV di atas 40x forward EBITDA - konteks ini harus ada di setiap perbandingan skenario.
Referensikan semua output skenario ke tab Chart Anda menggunakan =SUMIFS(Sensitivity!C:C,Sensitivity!A:A,"Implied Price") agar grafik diperbarui saat Anda mengubah asumsi.

Pro Tip

Sebelum mengirim model ke sindikasi bank atau board, bekukan tab Sensitivity dengan Data > Protect sheets and ranges. Seorang reviewer yang mengubah WACC menjadi 7% dan menyajikan kasus dasar $38 yang dihasilkan sebagai defensible adalah masalah yang bisa Anda cegah secara struktural.

Bangun Grafik Skenario

Tambahkan tab bernama Chart. Ini adalah visual yang sebenarnya akan dilihat audiens Anda. Tampilan paling efektif adalah grafik batang horizontal yang menunjukkan harga implied Bear/Base/Bull terhadap harga pasar saat ini ~$175.

Siapkan blok referensi kecil di Chart!A1:C5. Referensikan semua nilai dari Sensitivity - tidak ada angka hardcoded:

Label	Nilai	Pasar
Bear	=$'Sensitivity'!B5	175
Base	=$'Sensitivity'!C5	175
Bull	=$'Sensitivity'!D5	175

Sisipkan grafik kolom dengan Bear/Base/Bull sebagai seri primer (isian merah, kuning, hijau) dan seri garis untuk harga pasar $175 yang melintasi ketiga kolom.
Visual harus membuat overvaluasi terlihat jelas: tiga kolom mencapai $4-22, garis horizontal di $175 mengambang jauh di atas.
Beri label garis harga pasar langsung di grafik: "AVAV ~$175 (Juni 2026)"
Hubungkan judul grafik secara dinamis agar diperbarui saat WACC berubah:
Tambahkan anotasi text box di dekat selisihnya: "Implied premium terhadap kasus bull: ~680%" - dihitung sebagai =(Assumptions!$B$20-Sensitivity!$D$5)/Sensitivity!$D$5

Pro Tip

Jika grafik ini masuk ke deck PowerPoint, distorsi skala (batang di $4-22 vs. garis di $175) membuatnya hampir tidak terbaca. Tambahkan grafik inset kedua yang diperbesar ke $0-$30 yang hanya menampilkan skenario DCF dengan panah atau callout yang mengarah ke atas menuju garis $175 di luar grafik. Dua panel, satu cerita.

Kesimpulan

Model sudah selesai. Enam tab, semuanya terhubung: Assumptions menggerakkan P&L, P&L menyuplai FCFF, FCFF mengalir ke DCF, DCF menghasilkan output ke Sensitivity, dan Chart memvisualisasikan selisihnya.

Apa yang dikatakan model tentang AVAV sangat tegas: berdasarkan FCF murni, saham ini dihargai untuk masa depan yang tidak didukung oleh laporan keuangan historis. Kasus bear: $4,10. Kasus dasar: $7,20. Kasus bull: $22,40. Pasar saat ini: $175. Sesuatu - penskalaan produksi Switchblade hingga $2 miliar+ per tahun, penjualan militer internasional yang mengubah struktur margin, munisi berpaduan AI yang menjadi bisnis platform - harus berjalan dengan sangat baik dalam waktu yang lama.

Baik Anda sedang menguji posisi long, membangun kasus short, atau menjalankan sensitivitas untuk ulasan triwulanan sebuah dana investasi, model ini memberi Anda angka yang defensible dengan logika skenario yang jelas. Perbarui kolom B di P&L setiap kuartal saat AVAV melaporkan, dan seluruh model diperbarui dalam hitungan detik.

Untuk model yang membutuhkan data keuangan AVAV secara langsung, pengumuman kontrak yang dimenangkan, atau pengajuan anggaran pertahanan yang diambil secara otomatis, Coba ModelMonkey gratis 14 hari - tersedia untuk Google Sheets dan Excel.

Pertanyaan Umum

Mengapa kasus dasar DCF menghasilkan $7,20 sementara AVAV diperdagangkan mendekati $175?

Selisih ini mencerminkan premi optionalitas yang diproyeksikan pasar. FCF AVAV saat ini sangat tipis - kasus dasar memodelkan $18-21 juta FCFF tahunan pada pendapatan $850 juta+, margin sekitar 2,1-2,5%. Mendiskon itu pada WACC 11,5% dengan terminal growth 3,0% menghasilkan enterprise value sekitar $268 juta, atau sekitar $7,20/saham setelah menambahkan kas bersih $280 juta dan membaginya dengan 76,2 juta saham diluted. Harga pasar ~$175 mengimplikasikan bahwa investor sedang memproyeksikan ekspansi margin dan pendapatan yang dramatis selama 10+ tahun ke depan, bukan yang didukung oleh laporan keuangan historis.

WACC berapa yang tepat untuk AeroVironment?

Rentang 10-13% dapat dipertahankan. Menggunakan US Treasury 10 tahun 4,5%, beta bulanan 5 tahun mendekati 1,3, ERP 6%, dan leverage minimal, cost of equity yang diturunkan dari CAPM mendekati 12,3%. Kasus dasar 11,5% menerapkan penyesuaian ke bawah yang moderat untuk stabilitas pendapatan sektor pertahanan dan visibilitas kontrak jangka panjang. Pada WACC 13,0% dengan terminal growth 2,0%, nilai implied turun menjadi ~$4,10. Pada WACC 10,0% dengan terminal growth 3,5%, nilai naik menjadi ~$22,40. Jalankan matriks sensitivitas penuh sebelum menetapkan angka tunggal mana pun.

Bagaimana cara membuat dropdown skenario memperbarui grafik secara otomatis?

Tambahkan dropdown Data Validation di `Sensitivity!$B$1` dengan opsi Bear, Base, Bull. Ganti referensi Assumptions langsung di tab DCF Anda dengan pernyataan `IF()` yang membaca sel tersebut. Karena tab Chart mereferensikan `=DCF!B14` untuk harga implied, satu perubahan dropdown mengalir ke seluruh model. Grafik diperbarui tanpa menyentuh formula mana pun secara manual.

Haruskah saya menggunakan FCFF atau FCFE untuk analisis ini?

FCFF adalah pilihan yang tepat di sini. Ini menilai bisnis operasional secara independen dari struktur modal, kemudian menambahkan kas bersih di akhir - lebih bersih untuk perusahaan seperti AVAV yang aktif dalam penerbitan ekuitas dan memiliki saldo kas yang bervariasi. Jika Anda memodelkan FCFE, Anda perlu melacak setiap arus pembiayaan per kuartal, dan dengan riwayat penerbitan dilutif AVAV, itu menambah kebisingan tanpa meningkatkan output.

Apa yang harus terjadi agar kasus bull membenarkan harga pasar saat ini?

Untuk mencapai $175/saham dalam DCF, Anda membutuhkan sekitar 20-25% revenue CAGR yang dipertahankan selama 10 tahun (mengimplikasikan pendapatan $5-6 miliar pada 2035), margin EBIT yang berkembang hingga 18-22% (margin perangkat lunak setara Palantir pada produsen perangkat keras), dan WACC di bawah 9%. Itu bukan memodelkan AVAV - itu memproyeksikan bisnis yang secara fundamental berbeda. Kasus bull $22,40 dalam model ini mewakili batas atas dari apa yang mungkin dihasilkan oleh platform saat ini. Jarak dari $22,40 ke $175 adalah taruhan pasar pada transformasi strategis, bukan fundamental saat ini.