Sourcing M&A financial data into Excel: a workflow guide

Junior bankers and buy-side associates lose hours every week pulling M&A data into Excel by hand. The bottleneck is not Excel skill. It is knowing where deal terms, enterprise values, and pro forma metrics actually live across filings, and which sources will hold up when the IC asks where a number came from.

This guide covers the data sources that survive an IC defensibility test, the filings to mine for each component of a transaction, and the Excel patterns that keep the model auditable.

Where the data actually lives

M&A modeling needs five inputs: purchase price and consideration mix, target standalone financials, acquirer standalone financials, synergy and cost assumptions, and the deal timeline including financing structure. Each lives in a different filing.

Purchase price and consideration mix. The 8-K filed within four business days of announcement carries the headline number. The merger agreement attached as an exhibit, usually Exhibit 2.1, has the actual terms: cash per share, exchange ratio, collar mechanics, ticking fees, termination fees. The S-4 (for stock-or-mixed deals) or DEFM14A (the definitive merger proxy) holds the fairness opinion and the banker's analysis with comparable transactions and DCF backup. This is where the EV calculation gets reconciled against assumed debt and net cash.

Target standalone financials. Pull the most recent 10-K and 10-Q. If the deal closes mid-year, the proxy will include unaudited interim figures that bridge to the most recent quarter. Use those, not stale annuals.

Acquirer standalone financials. Same drill. The pro forma section of the S-4 will combine both, but rebuild it yourself. Bankers' pro formas embed assumptions you may not agree with.

Synergy and cost assumptions. Investor presentations on the acquirer's IR site, filed as 425s with the SEC, are the source. Treat the synergy number as management's claim, not as fact. Most deals miss synergy targets; the academic literature on this is consistent.

Timeline and financing. The merger agreement has the outside date and closing conditions. Financing commitments (bridge loans, term loans, bond issuance) appear in subsequent 8-Ks and the prospectus supplements when debt prices.

The premium calculation trap

The most common error in junior-level M&A work is computing the takeover premium against the close on announcement day. By then, leaks and rumors have usually pulled the stock up. The right reference is the unaffected price, the last close before the market began pricing in the deal.

The fairness opinion in the proxy will identify the unaffected date explicitly. Use that. If the deal was clean (no leak), the unaffected price is the close one trading day before announcement. If WSJ or Bloomberg ran a rumor a week prior, the unaffected price is the close before that rumor.

Getting this wrong understates premiums on leaked deals by 10 to 30%. It is the kind of error that gets caught in the first read of a memo and damages credibility for the rest of the process.

Source hierarchy

For IC defensibility, sources rank roughly as follows:

1. SEC filings (primary). 8-K, S-4, DEFM14A, 10-K, 10-Q, 425. Free via EDGAR. The merger agreement and the fairness opinion are non-negotiable reads.
2. Company press releases and investor presentations. Useful for synergy framing and management commentary.
3. Paid platforms. FactSet, S&P Capital IQ, Bloomberg, Pitchbook, Refinitiv Eikon. These pre-parse deal terms and give you comp screens. Quality varies on private and cross-border deals.
4. Sell-side research. Goldman, JPM, Morgan Stanley notes on the deal day are useful for sector-specific framing. Treat their numbers as opinion.
5. Trade press with primary attribution. Reuters, Bloomberg News, FT, WSJ. Cite when they have a quoted source; do not cite when they are just rewriting the press release.

What does not survive an IC challenge: Wikipedia, Statista, Gartner numbers without methodology, AI chatbot summaries.

Excel patterns that hold up

A few rules separate models that survive a senior review from models that get sent back:

Inputs in one tab, formulas in another. Hardcoded inputs (share counts, debt balances, synergy assumptions) live in a clearly colored input tab. Calculation tabs reference inputs only. This makes scenario toggles trivial and makes the audit trail readable.

Color conventions. Industry standard: blue for hardcoded inputs, black for formulas, green for cross-sheet links. Some shops use additional colors for external links. Pick one and apply it everywhere.

No hardcoded numbers in formula cells. If you find yourself typing =B5*1.05 because you want a 5% growth rate, that 5% belongs in an input cell. Hardcoded constants buried in formulas are the single biggest source of model errors.

Scenario manager or simple flag cells. For deal models, you usually need at least three cases: base, low synergies, high financing cost. Build them as toggles, not as separate workbooks. Separate workbooks drift.

Source every number. Either a comment on the cell or a separate source column noting the filing and page. When the MD asks where the $2.3B revenue figure came from at 10 PM, you do not want to be hunting.

Where automation helps and where it does not

Vendors like Daloopa, Calcbench, and AlphaSense have built parsers that extract financial line items from filings into Excel. For comp tables and historical financials, this is genuine time savings. The grunt work of typing five years of segment revenue from a 10-K is solved.

What is not solved: deal-specific judgment. The fairness opinion's WACC assumption, the synergy ramp schedule, the assumed debt paydown, the choice of comparable transactions. These are analytical decisions, and the parsing tools do not make them. They give you a clean base layer faster.

For analysts new to deal work: read three full DEFM14As before you build your first model. The fairness opinion section, where the bankers walk through their methodology, is the best free training material on M&A modeling that exists. Pick a deal in your sector, read the proxy cover to cover, then rebuild the banker's analysis in Excel. That exercise teaches more than any course.