Amazon [AMZN]
Task 2 · AMZN_Underwriting_Model_2026-07-27.xlsx · 6 tabs, live formulas
| Tab | Purpose |
|---|---|
| Assumptions | Every driver, blue = input / black = formula. Segment growth, EBITDA margin, capex, asset-class shares, useful lives, placed-in-service lags, tax, SBC, WACC, terminal growth. Base-rate check block at the foot. |
| Depreciation Engine | The analytical core. Vintage straight-line schedule: each forecast year's capex is split into servers/network, buildings/shell and other equipment, each depreciated over its own life after its own CIP lag. Includes a full parallel calculation at a 4-year server life. |
| Income Statement | Segment revenue → consolidated → EBITDA → less modelled D&A → EBIT → pre-tax → EPS. Plus an earnings-quality overlay: EPS excluding non-operating income, and EPS at a 4-year server life. |
| Cash Flow & BS | OCF, capex, economic FCF (SBC treated as a real cost), cash roll-forward including debt issuance, net debt, lease liabilities, off-balance-sheet memo. |
| Scenarios | Bull / Base / Bear with terminal revenue linked live to the Income Statement, exit EV/NOPAT, discounting to today, probability weighting, E[R] and the cash-hurdle comparison. Base-rate check per scenario. |
| DCF & Valuation | Unlevered FCF, a five-year fade period in which capex reverts to maintenance, Gordon terminal value, sensitivity grid, comps, football field. |
All workbooks were closed first (an open workbook serves a stale in-memory copy), the file was opened in Excel via AppleScript, and computed cells were read back. openpyxl writes formulas as strings without evaluating them, so a formula that looks right can be silently wrong; only a readback proves otherwise.
Readback results (2026-07-27):
Revenue : $716,924 $818,001 $915,415 $1,014,763 $1,114,876 $1,213,848
EBIT : $79,994 $94,530 $103,297 $108,924 $112,043 $117,120
D&A : $41,860 $56,800 $75,209 $96,058 $119,851 $140,215
D&A@4yr : $53,358 $72,213 $95,388 $121,670 $151,955 $177,635
EPS : $7.24 $8.26 $7.76 $8.23 $8.50 $8.93
EPSexNonOp:$5.95 $7.07 $7.76 $8.23 $8.50 $8.93
EPS@4yr : $6.39 $7.11 $6.25 $6.29 $6.07 $6.08
EconFCF : (19,044) (50,686) (59,576) (47,729) (27,940) (7,019)
DCF/sh = $14.20 TV% = 189% Scenario PW = $116.81 E[R] = -49.5%
FY2025A ties to the 10-K: revenue $716,924m ✓ (exact), segment D&A $41,860m ✓ (exact), operating income $79,994m vs $79,975m reported (0.02% residual from the margin input precision — disclosed, not hidden).
Three model errors were found and fixed during verification, each of which would have failed silently: 1. The D&A fade formula referenced capex as a signed (negative) figure, driving terminal D&A negative and the DCF to −$168/share. 2. The cash roll-forward omitted debt issuance, so cash went deeply negative and inflated scenario net debt. 3. The original five-year DCF terminated while capex was still 1.8x D&A — not a steady state. A five-year fade period was added so the terminal value is reached from a normalised position.
The engine is fitted to reported depreciation, then extrapolated. Fitting first is what makes the extrapolation credible.
| Parameter | Fitted value | Method |
|---|---|---|
| Servers / network share of capex | 52% | From FY2025 gross PP&E movement by class (R58) |
| Buildings / shell share | 30% | same |
| Other equipment share | 18% | residual |
| Server useful life | 5.5 years | 10-K discloses "Five to six years" |
| Buildings life | 30 years | 10-K: "lesser of forty years or remaining life"; 30 used as an effective average |
| Other equipment life | 9 years | 10-K: "Three to ten years" |
| Placed-in-service lag, servers | 0.5 years | Fitted |
| Placed-in-service lag, buildings | 1.0 years | Fitted — construction in progress was $71.7bn at Dec-25, depreciating nothing |
| Calibration RMSE, FY2022–FY2025 | $2.9bn on a $25–42bn series | Grid search over lags and scale |
The CIP lag is not a modelling convenience — it is the mechanism. $71.7bn of Amazon's $534.1bn gross PP&E was construction in progress at Dec-2025 and was depreciating nothing at all. That is 13% of the asset base sitting outside the income statement, waiting.
Reproduced on the Assumptions and Scenarios tabs.
AWS revenue growth. AWS grew 28% in Q1-2026, its fastest in 15 quarters, off a $128.7bn base. The reference class says cloud-infrastructure growth at >$100bn scale decelerates: AWS itself went from 37% (2021) to 13% (2023) before re-accelerating. The model assumes 27% → 14% over five years — deceleration, i.e. WITH the base rate, and on the conservative side of the current run-rate. No override is being claimed.
North America retail operating margin. FY2025 was 6.95%, up from 3.86% in 2022. ~$0.9bn of the FY2025 improvement is the heavy-equipment useful-life extension (10→13 years), not operating leverage — 16% of the year-on-year segment gain. The model expands retail margin only ~150bp over five years, deliberately modest, because part of the observed trend is an accounting estimate that does not repeat.
Capex. FY2025 PP&E additions of $142.4bn were +66% year on year. No company in the reference class has sustained >50% capex growth for four consecutive years. The model assumes deceleration to +1.6% by FY2030 ($200bn → $247bn). This is an override of the recent trend in the conservative direction and is named as such.
Consolidated operating margin. The model's FY2030 operating margin of 9.6% is below FY2025's 11.2%. That is not a bearish assumption imposed on the business — it is the arithmetic consequence of the depreciation engine running against a rising EBITDA margin. Stated explicitly so it is not mistaken for a demand call.
Per references/regime-change-test.md. Amazon discloses no contracted revenue tranche, so duration matching
can only be applied to the cost side — and it is:
| Tranche | Contracted duration | Model treatment |
|---|---|---|
| Leases not yet commenced ($106.3bn) | Disclosed ladder: $5.1bn 2026, $9.6bn 2027, $7.2bn 2028, $7.3bn 2029, $7.3bn 2030, $69.8bn thereafter | Modelled at the disclosed ladder within the horizon |
| Unconditional purchase obligations ($103.8bn, incl. energy) | $19.1bn 2026, $15.8bn 2027, then declining | Modelled at the ladder |
| Long-term debt ($122.6bn face, to 2076) | Full ladder disclosed | Interest modelled through the forecast |
| AWS revenue | None disclosed | Fully merchant; mean-reverting growth throughout. No contracted tranche exists to model at contracted terms. |
Contracted share of revenue by forecast year: 0% in every year. That is the honest expression of the uncertainty — and it is why the regime-change test fails for Amazon.