Financial ModelingAdvanced6 min read

Build an AeroVironment DCF Model in Google Sheets

Build a 6-tab AeroVironment DCF in Google Sheets with bear/base/bull scenarios. Base case lands at $7.20/share vs. ~$175 market price.

This guide walks you through building a 5-year DCF model for AeroVironment (AVAV) in Google Sheets - complete with a scenario waterfall chart - that produces a base-case intrinsic value near $7.20/share against a market price hovering around $175. The gap isn't a modeling error. It's the point. AVAV trades on defense optionality and geopolitical narrative, not current free cash flow. This model lets you quantify what the market is pricing in versus what the trailing financials actually support, then show it visually in one chart your PM can't dismiss. The model runs across 6 linked tabs: Assumptions, P&L, FCFF, DCF, Sensitivity, and Chart.

What You'll Need

Google Sheets account with edit access to a blank workbook
AeroVironment FY2025 10-K (fiscal year ended April 30, 2025) and most recent 8-K
AVAV diluted share count: 76.2M (from the proxy or 10-K cover page)
Familiarity with unlevered free cash flow mechanics and terminal value construction
30-45 minutes of focused build time

Step-by-Step Guide

Build the Assumptions Tab

The Assumptions tab is the control panel for the entire model. Every driver - revenue growth, EBIT margin, WACC, terminal growth - lives here. Everything else references it. If a number appears more than once in your model without tracing back to this tab, you've already introduced a reconciliation problem.

Name this tab Assumptions. Column A holds driver labels, column B holds values, column C holds notes.

B3**: FY2025 base revenue: 850 (in $M). AVAV reported $847.8M in FY2025 revenue per the 10-K filed June 2025.
B4:B8**: Revenue growth by year. Use 8%, 10%, 12%, 10%, 8% for base. This prices in Switchblade and Teal Drones ramp with execution risk embedded.
B10**: EBIT margin (base): 6.0%. AVAV's FY2025 adjusted operating margin landed near 5.8% per earnings supplements.
B15**: WACC: 11.5%. Derived from ~1.3 beta, 4.5% risk-free rate, 6% ERP, minimal leverage.
B16**: Terminal growth rate: 3.0%
B17**: Tax rate: 21%
B18**: Net cash (FY2025): 280 (in $M, from balance sheet)
B19**: Diluted shares: 76.2 (in millions)
B20**: Current market price: 175 (update as needed for the % gap formula in Step 4)

Pro Tip

Apply named ranges to B15, B16, B17, B19 (WACC, TermGrowth, TaxRate, Shares). Named ranges make DCF tab formulas readable and catch wiring errors before they compound across 5 projection years.

Forecast Revenue and EBIT on the P&L Tab

Add a tab named P&L. Columns B through G hold FY2025A through FY2030E. Row 2 is the year header. FY2025 actuals go in column B, forecasts in C:G.

Every forward year is a growth formula pulling from Assumptions. No hardcoded numbers past column B.

C3 (FY2026 Revenue)**: ='P&L'!B3*(1+Assumptions!$B$4) - drag right through FY2030, incrementing the growth rate row reference for each year ($B$5, $B$6, etc.)
C5 (FY2026 EBIT)**: ='P&L'!C3*Assumptions!$B$10
C6 (D&A)**: ='P&L'!C3*0.056 - 5.6% of revenue, matching AVAV's FY2025 D&A of $47.5M on $847.8M revenue
C7 (EBITDA)**: ='P&L'!C5+'P&L'!C6
C8 (Capex)**: =-'P&L'!C3*0.065 - entered negative; 6.5% of revenue reflects drone manufacturing investment above D&A
C9 (ΔNWC)**: =-'P&L'!C3*0.018 - NWC build at 1.8% of revenue, also entered negative

Pro Tip

Color column B (actuals) grey and lock it. Accidental edits to the FY2025 anchor corrupt every downstream calculation and are surprisingly hard to spot in a long model.

Calculate Unlevered Free Cash Flow

Add a tab named FCFF. This tab pulls entirely from P&L - no manual entries beyond headers. Every cell is a cross-tab reference.

FCFF = EBIT × (1 - Tax Rate) + D&A - Capex - ΔNWC

C3 (NOPAT)**: ='P&L'!C5*(1-Assumptions!$B$17)
C4 (D&A add-back)**: ='P&L'!C6
C5 (Capex)**: =-'P&L'!C8 - P&L has capex as negative; flip sign here so the FCFF sum works correctly
C6 (ΔNWC)**: =-'P&L'!C9
C7 (FCFF)**: =SUM('FCFF'!C3:C6)

Pro Tip

If your FCFF is negative in early years, check NWC. A company growing revenue 10%+ annually will consume working capital. $15-18M of annual NWC burn on $850-950M revenue is realistic for AVAV's manufacturing profile.

Build the DCF Engine and Terminal Value

Add a tab named DCF. This is where $7.20 gets calculated. Structure: discount each year's FCFF at WACC, compute a Gordon Growth terminal value off Year 5 FCFF, sum everything, add net cash, divide by shares.

C3:G3 (Discount factors)**: =1/(1+Assumptions!$B$15)^(COLUMN(C3)-2) - produces 1/1.115^1 through 1/1.115^5. Lock the column offset so drag works correctly.
C4:G4 (PV of FCFF)**: ='FCFF'!C7*DCF!C3 dragged right
B6 (Sum of PV FCFFs)**: =SUM(DCF!C4:G4) - base case lands near $79-81M
B8 (Terminal FCFF)**: ='FCFF'!G7*(1+Assumptions!$B$16) - Year 5 FCFF grown one period
B9 (Terminal Value)**: =DCF!B8/(Assumptions!$B$15-Assumptions!$B$16) - Gordon Growth; base case ~$325M
B10 (PV of Terminal Value)**: =DCF!B9*DCF!G3 - discounted at the Year 5 factor; base case ~$189M
B12 (Enterprise Value)**: =DCF!B6+DCF!B10 - base case ~$268M
B13 (Equity Value)**: =DCF!B12+Assumptions!$B$18 - add $280M net cash
B14 (Implied Share Price)**: =DCF!B13/Assumptions!$B$19 - divides by 76.2M shares

Pro Tip

Sanity-check your terminal value as a percentage of total enterprise value. If PV of TV exceeds 75% of EV, your near-term FCF is so thin that the model is essentially a terminal value exercise. For AVAV in base case, PV of TV is ~70% of EV. That's uncomfortable but honest given the FCF profile.

Build Bear / Base / Bull Scenario Analysis

Add a tab named Sensitivity. Structure this as a 3-column scenario matrix with named driver rows, not a two-variable data table. Named scenarios communicate more cleanly in a board deck.

Driver	Bear	Base	Bull
Revenue CAGR (5yr)	6%	9%	18%
EBIT Margin	4.5%	6.0%	10.5%
WACC	13.0%	11.5%	10.0%
Terminal Growth	2.0%	3.0%	3.5%
Implied Price	$4.10	$7.20	$22.40

The bull case requires AVAV to reach Adobe-level margins on a hardware manufacturer and a WACC compression that presumes significant business model transformation. Even at $22.40 the stock is trading at roughly 8x intrinsic value.

To make the scenarios live rather than static, add a Data Validation dropdown in Sensitivity!$B$1 (options: Bear, Base, Bull). Then in your DCF tab, replace the direct Assumptions references with conditional lookups:

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

One dropdown change cascades through the full model in under a second.

Add implied EV/EBITDA rows at the bottom of the scenario table, referencing ='P&L'!C7 for FY2026 EBITDA. Bear: ~3.1x. Base: ~5.5x. Bull: ~17x. The current market prices AVAV above 40x forward EBITDA - that context belongs in every scenario comparison.
Reference all scenario outputs into your Chart tab using =SUMIFS(Sensitivity!C:C,Sensitivity!A:A,"Implied Price") so the chart updates when you change assumptions.

Pro Tip

Before sending the model to a bank syndicate or board, freeze the Sensitivity tab with Data > Protect sheets and ranges. A reviewer who edits WACC to 7% and presents the resulting $38 base case as defensible is a problem you can prevent structurally.

Build the Scenario Chart

Add a tab named Chart. This is the visual your audience will actually look at. The most effective display is a horizontal bar range showing Bear/Base/Bull implied prices against the current ~$175 market price.

Set up a small reference block in Chart!A1:C5. Reference all values from Sensitivity - no hardcoded numbers:

Label	Value	Market
Bear	=$'Sensitivity'!B5	175
Base	=$'Sensitivity'!C5	175
Bull	=$'Sensitivity'!D5	175

Insert a column chart with Bear/Base/Bull as the primary series (red, amber, green fill) and a line series for the $175 market price running across all three columns.
The visual should make the overvaluation self-evident: three columns reaching $4-22, a horizontal line at $175 floating far above.
Label the market price line directly in the chart: "AVAV ~$175 (June 2026)"
Wire the chart title dynamically so it updates when WACC changes:
Add a text box annotation near the gap: "Implied premium to bull case: ~680%" - calculated as =(Assumptions!$B$20-Sensitivity!$D$5)/Sensitivity!$D$5

Pro Tip

If this chart ends up in a PowerPoint deck, the scale distortion (bars at $4-22 vs. a line at $175) makes it nearly unreadable. Add a second inset chart zoomed to $0-$30 showing just the DCF scenarios with an arrow or callout pointing up toward the $175 line off-chart. Two panels, one story.

Wrapping Up

The model is complete. Six tabs, all linked: Assumptions drives P&L, P&L feeds FCFF, FCFF flows into DCF, DCF outputs into Sensitivity, and Chart visualizes the gap.

What the model says about AVAV is blunt: on a pure FCF basis, the stock is priced for a future that the trailing financials don't support. Bear case $4.10. Base case $7.20. Bull case $22.40. Current market: $175. Something - Switchblade production scaling to $2B+ annually, international military sales transforming the margin structure, AI-guided munitions becoming a platform business - has to go dramatically right for a long time.

Whether you're stress-testing a long position, building a short case, or running a sensitivity for a fund's quarterly review, the model gives you a defensible number with clear scenario logic. Update column B of the P&L each quarter as AVAV reports, and the whole model refreshes in seconds.

For models that need live AVAV financials, contract win announcements, or defense budget filings pulled automatically, Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.

Frequently Asked Questions

Why does the base case DCF produce $7.20 when AVAV trades near $175?

The gap reflects a market-priced optionality premium. AVAV's current FCF is thin - the base case models $18-21M of annual FCFF on $850M+ revenue, a margin of roughly 2.1-2.5%. Discounting that at 11.5% WACC with 3.0% terminal growth produces an enterprise value near $268M, or roughly $7.20/share after adding $280M net cash and dividing by 76.2M diluted shares. The ~$175 market price implies investors are underwriting dramatic margin and revenue expansion over the next 10+ years - not what the trailing financials suggest.

What WACC is appropriate for AeroVironment?

A range of 10-13% is defensible. Using a 4.5% 10-year Treasury, a 5-year monthly beta near 1.3, a 6% ERP, and minimal leverage, the CAPM-derived cost of equity lands around 12.3%. The 11.5% base case applies a modest downward adjustment for defense-sector revenue stability and long-cycle contract visibility. At 13.0% WACC with 2.0% terminal growth, implied value drops to ~$4.10. At 10.0% WACC with 3.5% terminal growth, it rises to ~$22.40. Run the full sensitivity matrix before anchoring on any single number.

How do I make the scenario dropdown automatically update the chart?

Add a Data Validation dropdown in `Sensitivity!$B$1` with options Bear, Base, Bull. Replace the direct Assumptions references in your DCF tab with `IF()` statements that read that cell. Since the Chart tab references `=DCF!B14` for the implied price, one dropdown change cascades through the full model. The chart updates without touching any formula manually.

Should I use FCFF or FCFE for this analysis?

FCFF is the right choice here. It values the operating business independently of capital structure, then adds net cash at the end - cleaner for a company like AVAV that has been active in equity issuances and carries variable cash balances. If you modeled FCFE instead, you'd need to track every financing flow quarter by quarter, and with AVAV's history of dilutive raises, that adds noise without improving the output.

What would need to be true for the bull case to justify the current market price?

To get to $175/share in a DCF, you'd need roughly 20-25% revenue CAGR sustained over 10 years (implying $5-6B revenue by 2035), EBIT margins expanding to 18-22% (Palantir-level software margins on a hardware manufacturer), and a WACC at or below 9%. That's not modeling AVAV - that's projecting a fundamentally different business. The $22.40 bull case in this model represents the high end of what the current platform could plausibly generate. The distance from $22.40 to $175 is the market's bet on strategic transformation, not current fundamentals.