Memo·Conceptual outline · implementation in progress
Investment·Case Study 03
Case Study 03 · DCF · LBO · ETL · Snowflake · Power BI
Automated DCF & LBO Pipeline
Private equity funds and activist hedge funds run and to surface undervalued companies, but doing it manually in Excel is slow and inconsistent. This pipeline fetches financial statements for 500 companies, algorithmically calculates a baseline valuation for each, and pushes the output to a data warehouse connected to a Power BI dashboard that visually flags the most mathematically undervalued targets.
The Travel Industry Connection
Travel is one of the most active sectors for private equity. Blackstone, Carlyle, and KKR have all made significant hospitality acquisitions, from Extended Stay America to Las Vegas Sands' assets. The sector's appeal: asset-light hotel management companies (Marriott, Hilton) generate stable, franchise-fee-driven cash flows that are highly amenable to without requiring ownership of physical assets.
Post-COVID dislocations created valuation gaps: many OTAs, cruise lines, and regional hotel operators traded at depressed multiples relative to historical norms, even as their underlying business models remained structurally intact. A systematic screen would have identified these dislocations at scale, which no analyst team could replicate manually across 500 companies.
Screening Universe · 500 Companies
| Sector | Count | Examples |
|---|---|---|
| Hotels & Resorts | 42 | MAR, HLT, IHG, NH Hotels, Accor |
| Airlines | 38 | DAL, LUV, IAG, EasyJet, Ryanair |
| Cruise Lines | 12 | CCL, RCL, NCLH |
| Online Travel (OTAs) | 28 | BKNG, EXPE, Airbnb, Trip.com |
| Tour Operators | 18 | TUI, Thomas Cook, Kuoni |
| Other (Broader Screen) | 362 | Cross-sector control group |
The Method
Algorithmic + at Scale
Each company runs through a standardised model: five years of projected free cash flows are discounted at a company-specific , then a is added using the Gordon Growth Model. The output is intrinsic enterprise value, compared to the current market EV to compute an upside or downside percentage.
The module layers a standard 65/35 debt-equity structure on top: it models annual debt amortisation using the projected operating cash flow, and back-solves the implied IRR at assumed entry and exit multiples.
V = Σ FCFₜ/(1+WACC)ᵗ + TV/(1+WACC)ⁿ
TV = FCFₙ×(1+g)/(WACC−g); default g = 2.0%
IRR: solve NPV(CF₀, CF₁…CF₅ + exit) = 0
CF₀ = equity cheque; exit = (EV at exit − debt remaining)
Data Pipeline
Financial Statement Ingestion
Python · requests · pandas
Pull income statement, balance sheet, and cash flow statement for 500 companies via Financial Modelling Prep or Intrinio API. Store raw JSON in S3/GCS.
Cleaning & Normalisation
pandas · Great Expectations
Standardise GAAP vs. IFRS line items. Validate data quality constraints (non-negative revenue, EBITDA margin bounds). Fill or flag missing quarters.
DCF Valuation Engine
Python · numpy
Project 5-year free cash flow using median analyst growth consensus. Compute WACC from CAPM + sector debt spread. Calculate terminal value and sum to enterprise value.
LBO Model
Python · scipy
Layer a standard 65/35 debt-equity acquisition structure. Amortise debt over 5 years using operating cash flow. Back-solve IRR at assumed exit EV/EBITDA multiples.
Data Warehouse Load
Snowflake · dbt
Load valuation outputs (intrinsic EV, market EV, upside %, LBO IRR) into Snowflake. dbt transformations materialise comparison views by sector and geography.
Power BI Dashboard
Power BI · DAX
Visual layer: scatter plot of market EV vs. DCF EV flags undervalued companies. LBO IRR heat map by sector. Exportable watchlist for investment committee.
Valuation Inputs & Assumptions
| Input | Source / Method |
|---|---|
| Revenue CAGR (5Y) | Analyst consensus per ticker |
| EBITDA Margin | Trailing 3-year average |
| Capex / Revenue | Sector median (maintenance + growth) |
| WACC | CAPM cost of equity + sector debt spread |
| Terminal Growth | 2.0% (real GDP proxy) |
| Entry Multiple | Last 12-month EV/EBITDA |
| Exit Multiple | Sector median EV/EBITDA (5-year horizon) |
| LBO Leverage | 65% debt / 35% equity |
Planned Output
500
Companies screened
138 in travel & hospitality
~45
Flagged as undervalued
DCF upside > 30% at conservative WACC
7.5–12%
WACC range across universe
higher for leveraged cruise/airline names
~18%
Median LBO IRR (travel)
asset-light models vs. owned-asset operators
Conclusion
At scale, consistency beats sophistication.
A manually built DCF is only as good as the analyst's assumptions on that day. A systematic pipeline applies the same logic, the same construction, and the same methodology to every company in the universe, making the screen reproducible, auditable, and fast to update when market conditions shift.
For travel and hospitality, where valuations collapsed and recovered violently between 2020 and 2023, the ability to run this screen weekly, rather than monthly, is the operational advantage. The edge is not in having a better model. It is in seeing the same model's output before others do.
Excel is a research tool. A data warehouse is an investment process.