Financial Modelling for M&A

The problem: when financial modelling fails, negotiations collapse

I see this happen repeatedly: a company is in advanced M&A negotiations, has already invested €40,000–€60,000 in due diligence, lawyers, and consultants. At the final negotiation table, the buyer presents a financial model projecting EBITDA significantly below the seller’s expectations. Or worse: the seller has no credible model to defend their price.

The deal collapses. Not due to lack of strategic interest. Not due to due diligence issues. But because neither party can quantify, with technical rigour, the real value of the company and the expected return of the transaction.

The cost? Months of lost work, hundreds of wasted management hours, and a strategic opportunity that disappears. In a recent case where we acted as fairness opinion, the difference between the buyer’s and seller’s models was €2.8 million—not due to bad faith, but because of different assumptions on revenue growth, cost structure, and working capital requirements.

M&A financial modelling in Excel is not an academic exercise. It is the common language that allows buyers and sellers to negotiate based on facts, not intuition. Without an integrated model linking financial statements, synergy analysis, financing structure, and valuation sensitivities, you are negotiating blind.

This guide presents the technical template we use in over 50 transactions—an Excel file integrating the three financial statements, quantifying operational and financial synergies, modelling different financing structures, and calculating the real ROI of the acquisition. This is not theory. It is the file you open in the negotiation meeting.

The framework: technical architecture of M&A financial modelling in Excel

Step 1: Base structure—the three integrated statements (working capital as pivot)

What to do: Build a fully integrated three-statement structure: income statement, balance sheet, and cash flow. Integration occurs through working capital and financing decisions.

Why: Non-integrated models produce fatal inconsistencies. I have seen buyers present projections where revenue grows significantly each year, but working capital remains flat—mathematically impossible. Integration forces coherence between growth, investment, and financing.

How to implement:

Sheet 1 — Income Statement (P&L):

• Lines 1–10: Revenues by business line (3-year history + 5-year projection)
• Lines 11–15: Growth drivers (volume vs. price, market share, CAC)
• Lines 16–30: Detailed COGS by category (raw materials, direct labour, industrial costs)
• Lines 31–35: Gross margin by business line
• Lines 36–50: Structured OPEX (sales, marketing, G&A, R&D)
• Lines 51–55: Adjusted EBITDA (excluding one-offs, non-recurring costs)
• Lines 56–60: D&A (depreciation calculated from the CAPEX sheet)
• Lines 61–65: EBIT
• Lines 66–70: Financial costs (linked to debt structure in the balance sheet)
• Lines 71–75: Taxes (effective rate, not nominal—crucial in Portugal)
• Lines 76–80: Net income

Sheet 2 — Balance Sheet:

• Fixed assets: accumulated CAPEX minus depreciation (direct link to P&L)
• Detailed working capital: DSO (days), DPO (days), inventory (days of COGS)
• Cash: calculated from the cash flow statement, not entered manually
• Debt: complete structure (short vs. long term, interest rate, amortisations)
• Equity: opening balance + net income - dividends

Sheet 3 — Cash Flow:

• Operating cash flow: EBITDA - taxes paid - change in working capital
• Investing cash flow: CAPEX (maintenance vs. expansion)
• Financing cash flow: debt amortisations, new loans, dividends
• Change in cash: sum of the three blocks (must match change in balance sheet)

Critical integration formulas:

• Ending cash (balance sheet) = Opening cash + Change in cash (cash flow)
• Ending debt = Opening debt + New loans - Amortisations
• Financial costs (P&L) = Average debt × Interest rate
• Depreciation (P&L) = Accumulated CAPEX / Useful life
• Change in WC (cash flow) = (DSO + Inventory - DPO) × Revenue growth

Real example: In the acquisition of a food distributor, the buyer projected significant annual growth. In the initial model, working capital did not reflect this growth. When we integrated it correctly, the additional WC requirement was €1.2 million in the first two years—completely changing the necessary financing structure.

Common mistake: Entering cash manually in the balance sheet instead of calculating it from the cash flow. This breaks integration and allows errors to go unnoticed until the final negotiation.

Step 2: Synergy analysis—technical quantification (not wishful thinking)

What to do: Create a dedicated sheet quantifying operational and financial synergies with realistic implementation timing and integration costs.

Why: Synergies are the main value driver in M&A. But many transactions fail to capture projected synergies because they were quantified with excessive optimism and no implementation plan. A credible M&A financial model in Excel enforces discipline: each synergy has a source, timing, implementation cost, and responsible party.

How to implement:

Sheet 4 — Operational Synergies:

Block A — Revenue Synergies:

• Cross-selling (client A buys product B): quantify by segment
• Geographic expansion: incremental revenue by region
• Up-selling: increase in average ticket (with realistic conversion rate)
• Timing: year 1 (significant synergy), year 2 (significant), year 3 (significant), year 4+ (significant)
• Implementation cost: sales team, marketing, systems

Block B — Cost Synergies:

• Purchasing: aggregated volume (quantify by key supplier)
• G&A structure: elimination of duplications (finance, HR, IT, legal)
• Operations: facility consolidation, logistics optimisation
• Technology: elimination of redundant systems (especially ERP)
• Timing: faster than revenue—year 1 (significant), year 2 (significant), year 3 (significant)
• One-off costs: severance, relocations, systems integration

Block C — Financial Synergies:

• Cost of debt: post-acquisition interest rate (larger company = better rating)
• Tax optimisation: consolidated legal structure
• Working capital: better payment terms with suppliers

Quantification template (per synergy line):

• Column A: Synergy description
• Column B: Type (revenue / cost / financial)
• Column C: Source (where the value is—supplier X, client Y, facility Z)
• Column D: Annual recurring value (run-rate)
• Columns E–I: Capture by year (year 1 to year 5, in %)
• Column J: Implementation cost (one-off)
• Column K: Execution responsible
• Column L: Execution risk (low/medium/high)
• Column M: Probability of capture (significant)

Link to P&L: Synergies feed directly into the integrated P&L. Revenue synergies increase sales. Cost synergies reduce COGS or OPEX. Financial synergies reduce financial costs. Each synergy has a specific target cell in the P&L.

Real example: In a horizontal merger of two service companies, we identified 12 operational synergies. The largest: consolidation of two Lisbon offices (annual saving of €180k). But the cost of moving, contract terminations, and space adaptation was €320k. In the model, the synergy only became cash flow positive in year 3. Without this detail, the buyer would have assumed immediate savings—and run out of liquidity in the first 18 months.

Common mistake: Adding up synergies without considering implementation timing or one-off costs. This artificially inflates the valuation and creates unrealistic return expectations.

Step 3: Financing structure—debt vs. equity (and the impact on ROI)

What to do: Model 3–4 alternative financing structures for the acquisition and calculate the impact on ROI for the buyer and the effective price for the seller.

Why: Financing structure is not neutral. A buyer who finances most of the deal with debt has a very different ROI from one who uses equity—even if they pay the same price. And for the seller, receiving cash, earn-out, or paper (buyer’s shares) has completely different tax and risk implications.

How to implement:

Sheet 5 — Financing Structures:

Scenario 1 — All Cash (pure equity):

• Buyer: invests the full enterprise value in equity
• No additional financial costs
• ROI = (Target EBITDA + Synergies - CAPEX) / Total investment
• Payback period: years to recover investment

Scenario 2 — Leveraged Buyout (mostly debt / some equity):

• Senior debt: significant portion of EV, 4.x% rate, 7-year term, linear amortisation
• Subordinated debt: significant portion of EV, 7.x% rate, 5-year term, bullet payment
• Equity: remainder of EV
• Covenants: Debt/EBITDA < 3.5x, DSCR > 1.3x
• ROI on equity invested (not total EV)—leverage amplifies return
• Stress test: can debt be serviced if EBITDA drops significantly?

Scenario 3 — Earn-out (partial upfront + deferred payment):

• Initial payment: significant portion of EV in cash
• Earn-out: significant portion paid over a realistic period, conditional on EBITDA targets
• Earn-out formula: if year 3 EBITDA > €Xm, pay significant amount; if < €Ym, pay lower amount
• Buyer advantage: shares performance risk
• Seller disadvantage: uncertainty, mandatory stay, risk of manipulation

Scenario 4 — Stock deal (buyer’s shares):

• Seller receives buyer’s shares instead of cash
• Valuation: price per share based on multiple or independent valuation
• Lock-up period: seller cannot sell shares for X years
• Tax advantage: capital gains deferral
• Risk: seller exposed to buyer’s performance

Comparative ROI calculation (critical for buyers):

For each scenario, calculate:

• IRR (Internal Rate of Return) on invested capital
• MOIC (Multiple on Invested Capital): final value / capital invested
• Payback period in years
• Average ROE (Return on Equity) over the period
• Sensitivity to EBITDA variations (-x%, -y%, base, +y%, +x%)

Real example: In the acquisition of an industrial company, the buyer had three options: (A) pay €5M in cash, (B) pay €4M + €1.5M earn-out, or (C) finance with significant debt. We modelled all three. Scenario B had the best IRR (x% vs. y% in A), but only if synergies were captured. Scenario C had an IRR of z%, but breached bank covenants if EBITDA dropped significantly. The final decision: scenario A with a €1M standby credit line for liquidity security.

Common mistake: Calculating ROI on enterprise value instead of on actual invested capital. In an LBO, the relevant ROI is on the equity invested, not the total price—the difference can be x% vs. y% annual return.

Step 4: Integrated valuation—DCF, multiples, and sensitivity analysis

What to do: Integrate three valuation methods into the model (DCF, multiples, comparable transactions) and create sensitivity tables for critical assumptions.

Why: A single valuation method is insufficient for negotiation. DCF captures intrinsic value but is sensitive to assumptions. Market multiples reflect what others pay but ignore specifics. Triangulating all three methods creates a defensible valuation range.

How to implement:

Sheet 6 — DCF (Discounted Cash Flow):

Block A — Free Cash Flow to Firm (FCFF):

• EBIT × (1 - effective tax rate)
• + Depreciation and amortisation
• - CAPEX (maintenance + expansion)
• - Change in working capital
• = Annual FCFF (years 1–5 explicit)

Block B — Terminal Value (residual value):

• Method 1: Perpetuity (Year 6 FCFF / (WACC - perpetual growth))
• Method 2: Exit multiple (Year 5 EBITDA × sector multiple)
• Use the more conservative of the two

Block C — WACC (Weighted Average Cost of Capital):

• Cost of equity: Rf + Beta × (Rm - Rf) + specific premium
• Rf (risk-free): 10-year Portuguese government bonds (~3.x% in 2024)
• Beta: sector-adjusted (source: Damodaran or Bloomberg)
• Market risk premium: x% (Europe historical)
• Specific premium: +y% for SMEs (illiquidity, concentration)
• Cost of debt: effective post-tax rate × (1 - CIT rate)
• Capital structure: % debt vs. % equity (target, not current)
• WACC = (E/V × Ke) + (D/V × Kd × (1-T))

Block D — Valuation:

• Enterprise Value = Σ (FCFF / (1+WACC)^n) + (Terminal Value / (1+WACC)^5)
• Equity Value = EV - Net debt + Non-operating assets
• Price per share (if applicable) = Equity Value / Number of shares

Sheet 7 — Valuation by Multiples:

Gather 5–8 comparable companies (sector, size, similar geography):

• EV/EBITDA (most common in M&A)
• EV/Revenue (for growth or low-margin companies)
• P/E (Price/Earnings—for listed companies)
• EV/EBIT (alternative to EBITDA)

Application to target:

• Peer group average multiple
• Adjustment: -x% to -y% if target is unlisted SME (illiquidity discount)
• Adjustment: +y% to +z% if target has clear competitive advantages
• EV = Target EBITDA × Adjusted multiple

For more on this method, see our guide on company valuation by market multiples.

Sheet 8 — Comparable Transactions:

Research recent M&A transactions in the sector (sources: Mergermarket, Capital IQ, press):

• Price paid (EV)
• Implied multiple (EV/EBITDA, EV/Revenue)
• Target characteristics (size, geography, growth)
• Control premium paid (vs. pre-announcement price, if applicable)

Sheet 9 — Sensitivity Tables:

Create two-dimensional tables showing how valuation varies:

Table 1 — DCF: WACC vs. Perpetual growth rate:

• Horizontal axis: perpetual growth (x%, 1.x%, y%, 2.x%, z%)
• Vertical axis: WACC (x%, y%, z%, a%, b%)
• Cells: Resulting Enterprise Value
• Goal: show valuation range (e.g., €4.2M to €6.8M)

Table 2 — Multiples: EBITDA vs. Multiple:

• Horizontal axis: EV/EBITDA multiple (5x, 6x, 7x, 8x, 9x)
• Vertical axis: Normalised EBITDA (€800k, €900k, €1M, €1.1M, €1.2M)
• Cells: Resulting Enterprise Value

Table 3 — Break-even analysis:

• Maximum price that still generates minimum acceptable IRR (e.g., x%)
• Varying: revenue growth, synergy capture, CAPEX

Real example: In a B2B software company transaction, the DCF pointed to €3.8M (WACC x%, g=y%). Market multiples (6–8x EBITDA) suggested €4.2M–€5.6M. Comparable transactions (two in the last year) paid 7.2x and 8.1x. The sensitivity table showed that with a WACC of z% (more optimistic), the DCF rose to €4.5M. The final negotiation range was €4.3M–€4.8M—triangulating the three methods with adjustments for the target’s specifics.

Common mistake: Using a "textbook" WACC without adjusting for the specific risk premium of Portuguese SMEs. A WACC of x% (typical for listed companies) is unrealistic for an SME—the correct range is y%–z%, depending on sector and maturity.

Step 5: Buyer return analysis—IRR, payback, and value creation

What to do: Build a dashboard showing, for each financing structure, the buyer’s effective return: IRR, payback period, NPV, and value creation vs. cost of capital.

Why: The price you pay matters less than the return you achieve. A buyer can pay a premium of x% and still achieve an excellent return if synergies are real and the financing structure is efficient. This dashboard is what you present to the board or investors to approve the transaction.

How to implement:

Sheet 10 — Return Dashboard:

Section A — Total Investment:

• Enterprise Value paid
• + Transaction costs (due diligence, lawyers, consultants): x% of EV
• + Integration costs (IT, relocations, severance): modelled in the synergies sheet
• + Additional required investment (CAPEX, working capital): first 24 months
• = Total investment

Section B — Cash Flows to Buyer (years 1–10):

• Year 0: -Total investment
• Years 1–5: Standalone FCFF + Captured synergies - Debt service (if LBO)
• Years 6–10: Normalised FCFF (assuming synergies fully captured)
• Year 10: + Residual value (exit multiple × year 10 EBITDA)

Section C — Return Metrics:

• IRR (Internal Rate of Return): Excel XIRR function on cash flows
• Payback period: years until Σ cash flows = 0
• MOIC (Multiple on Invested Capital): (Σ cash flows + residual value) / investment
• NPV (Net Present Value): Σ (cash flows / (1+WACC)^n)
• Average annual ROI: (Final value / Investment)^(1/10) - 1

Section D — Value Creation:

• Annual EVA (Economic Value Added): (ROIC - WACC) × Invested capital
• ROIC (Return on Invested Capital): NOPAT / (Equity + Net debt)
• Spread: ROIC - WACC (positive = value creation)
• Cumulative value created: Σ EVA years 1–10

Section E — Return Sensitivity Analysis:

Table: Resulting IRR varying two critical assumptions:

• Axis 1: Synergy capture (x%, y%, z%, a%)
• Axis 2: Exit multiple in year 10