A reverse discounted-cash-flow model. Instead of forecasting growth to find a value, it takes today's market price, backs out the constant FCFF growth rate the market is already discounting for the next 10 years, and tests that growth against Coca-Cola's own history.
- Python engine (
src/reverse_dcf/) — SEC data → FCFF → WACC → solver → sensitivities. - Excel model (
outputs/reverse_dcf_model.xlsx) — generated by the engine; every number is a live formula (the implied growth is solved inside Excel), and aModel Checkssheet reconciles it to the Python results line by line. - Running the model rewrites
outputs/reverse_dcf_model.xlsx. The copy inexcel_formatted/is a snapshot of the same model, formatted and saved in Excel for easy viewing. It is not updated automatically, so after changing inputs, open the new file fromoutputs/in Excel, save it intoexcel_formatted/, and replace the old copy. - All SEC data comes from the same SEC API as before (
data.sec.gov/api/xbrl/companyfacts).
pip install -r requirements.txt
export SEC_USER_AGENT="Your Name your.email@example.com" # only needed to refresh SEC data
python run_model.py # Oct-23-2026 snapshot from data/market_inputs.csv
python run_model.py --refresh # re-download SEC company facts first
python run_model.py --mid-year # mid-year discounting
python run_model.py --simple-bridge # EV = mkt cap + debt - cash only (no equity-method adj.)
python run_model.py --valuation-date 2026-10-28 --price <close> # roll forward to the Q3 10-Q
python -m pytest # tests (Excel parity tests need LibreOffice)Units: USD millions and millions of shares everywhere; rates are decimals.
-
Market EV (target) = price × diluted shares + debt & operating leases + noncontrolling interests − cash, short-term investments & marketable securities − equity-method investments. (EBIT excludes equity income, so the investments are non-operating and must come out of EV.)
-
WACC = E/(D+E)·[rf + β·ERP] + D/(D+E)·kd·(1−t), market-value weights, marginal tax rate.
-
Base FCFF₀ (TTM) = EBIT(1 − effective tax rate) + D&A − CapEx − ΔNWC, with TTM = last fiscal year + current YTD − prior-year YTD.
-
Solve for g such that
EV_market = Σₜ₌₁ᴺ FCFF₀(1+g)ᵗ/(1+WACC)ᵗ + FCFF₀(1+g)ᴺ(1+gT)/(WACC−gT)/(1+WACC)ᴺBecause the explicit PV is a geometric series in q = (1+g)/(1+WACC), EV(g) has the closed form
(1+WACC)^(m/2)·FCFF₀·[q(1−qᴺ)/(1−q) + qᴺ(1+gT)/(WACC−gT)](m = 1 for mid-year). EV is strictly increasing in g, so the root is unique. Python uses Brent's method; Excel uses a 60-step bisection on the same formula. They agree to ~1e‑16. -
Plausibility (Damodaran) — compare the implied g with (i) revenue and EBIT CAGRs, (ii) fundamental growth = reinvestment rate × ROIC, and (iii) the year‑10 FCFF margin the implied path would require.
| SEC basis (strict as-of) | Q2 2026 10‑Q, TTM to 3‑Jul‑2026 |
| Market EV | $410,093 m |
| WACC | 7.31 % |
| Base FCFF (TTM) | $12,161 m |
| Terminal growth | 2.50 % |
| Market-implied FCFF growth (10 y) | 8.25 % (7.80 % mid-year) |
| Terminal value share of EV | 68.9 % |
| Benchmarks | revenue CAGR 3/5/6y 3.7 / 7.7 / 4.3 %; EBIT CAGR 8.1 / 8.9 / 5.3 %; fundamental growth 1.7 % (ROIC 26.7 % × normalised reinvestment 6.4 %) |
Reading: the price needs FCFF to compound at roughly the best recent EBIT growth (a post-COVID, post-charge rebound) for ten years, far above what reinvestment supports (~1.7 %). Reaching it requires sustained margin expansion: at 5 % revenue growth the year‑10 FCFF margin must be ~33 % vs. a NOPAT margin of ~25 % today. The implied rate is a statement about the price, not a forecast.
CC code mis-indented, units mixed ($bn/$m) | Mid-year switch (Excel + Python), requirements, real tests, one unit system |
Summary (KPIs, plausibility table, 3 charts) · Inputs · Historical· DCF Engine · Sensitivity ·
Methodology. Model Checks · SEC Data · Solver · Solver Grid ·
Standards: Arial 10, gridlines off, blue = hard-coded input/historical data, black = formula or link,
USD millions #,##0;(#,##0), rates 0.00%, dates d-mmm-yy, period headers computed with EDATE
from the SEC period end, frozen panes, one-page-wide landscape print setup.
coca_cola_reverse_dcf/
│
├── run_model.py CLI entry point: runs the model and writes the Excel file
├── requirements.txt Python dependencies
├── conftest.py lets pytest import the project
├── README.md this file
│
├── data/
│ ├── market_inputs.csv price, rf, ERP, beta, kd, tax, terminal g (non-SEC inputs, with sources)
│ └── raw/ SEC company facts download (gitignored)
│
├── src/reverse_dcf/ the Python engine
│ ├── config.py inputs, settings, WACC
│ ├── sec.py point-in-time XBRL extraction from the SEC API
│ ├── fcff.py FCFF calculation (vectorised)
│ ├── dcf.py explicit schedule + closed-form EV
│ ├── reverse_dcf.py Brent and bisection solvers for implied growth
│ ├── sensitivities.py numpy sensitivity grids
│ ├── fundamentals.py plausibility metrics (CAGR, ROIC, fundamental growth)
│ ├── model.py orchestration: ties all of the above together
│ ├── excel_model.py workbook builder + LibreOffice recalculation
│ └── outputs.py JSON / CSV exports
│
├── excel_formatted/ formatted model, finished and saved in Excel
│ └── Final_Reverse_DCF_Model.xlsx
│
├── outputs/ generated each time run_model.py runs
│ └── reverse_dcf_model.xlsx Excel model generated by the code (other outputs are gitignored)
│
├── scripts/
│ ├── download_sec_data.py downloads the SEC data
│ └── legacy_html_extraction/ old 10-K / 10-Q HTML scrapers (no longer used by the model)
│
└── tests/ math, point-in-time (no look-ahead), Excel-vs-Python parity
Constant growth then perpetuity (no fade); terminal reinvestment is not re-based to g/ROIC; regression beta 0.58 is low; equity-method investments and minority interests at book value; operating leases from the latest 10‑K; TTM ΔNWC includes tax items and is volatile (see sensitivity above); price, risk-free rate, ERP, beta and cost of debt are manual inputs — verify them before relying on the output. Not investment advice.