📁 Finance
Investment Banking Comps Table from Pasted Company Data
Paste peer prices, shares, debt, cash, and LTM figures to get a checked trading comps table, EV multiples with outliers flagged, and an implied value range.
0Reviews
Prompt
Act as an investment banking analyst who builds trading comparables from data the banker pastes, shows every enterprise value and multiple calculation, flags outliers instead of quietly dropping them, and turns the peer range into an implied value for the target. Inputs: - Target company and its LTM revenue, LTM EBITDA, and net debt: [TargetFinancials] - Peer data, one row per company: share price, diluted shares, total debt, cash, LTM revenue, LTM EBITDA, and the as-of date: [PeerData] - Multiples to show: EV/Revenue, EV/EBITDA, others: [Multiples] - How to pick the range: median, interquartile, middle peers: [RangeMethod] - Units and currency: [Units] - Who will read it: MD review, pitch book page, client call: [Audience] Generate: 1. Data check: missing fields, mismatched dates or units, negative or tiny EBITDA, and any margin far from the group. Flag outliers with the reason. 2. Comps table: equity value, plus debt, minus cash, enterprise value, each multiple, and EBITDA margin, with the formula stated once. 3. Summary statistics for each multiple: low, median, mean, high, with and without flagged outliers. 4. Implied value for the target: apply the range from RangeMethod to TargetFinancials, show implied enterprise value and implied equity value (EV minus net debt). 5. Notes for Audience: what drives the range, which peers are least comparable and why, and what is not checked. 6. Open questions for the deal team before the numbers go in a book. Rules: - Use only the numbers pasted. Do not look up, estimate, or invent prices, multiples, or financials. - Show the calculation behind every number and keep units consistent. - This is analysis support, not investment advice or a fairness opinion; a banker must check every figure against the source filings.
Instructions
Use only data you are allowed to share, and never paste material non-public information or client-confidential numbers into a public AI tool; use your firm's approved tools. Run it in ChatGPT, Claude, or Gemini, then recheck every enterprise value and multiple in Excel against the source filings. Not investment advice.
Generated Output
This image was generated using the prompt above.
Examples
Example Input
TargetFinancials: Harbor Payroll (private), LTM revenue 120, LTM EBITDA 30, net debt 15. PeerData (as of the same close date, $m except price): Peer A: price $42.00, diluted shares 85m, debt 300, cash 120, LTM revenue 610, LTM EBITDA 140 Peer B: price $18.50, diluted shares 160m, debt 0, cash 240, LTM revenue 520, LTM EBITDA 95 Peer C: price $65.00, diluted shares 40m, debt 450, cash 90, LTM revenue 880, LTM EBITDA 210 Peer D: price $27.00, diluted shares 110m, debt 150, cash 60, LTM revenue 470, LTM EBITDA 60 Peer E: price $120.00, diluted shares 30m, debt 200, cash 350, LTM revenue 1150, LTM EBITDA 330 Multiples: EV/Revenue and EV/EBITDA. RangeMethod: middle peers and median, after removing outliers. Units: USD millions. Audience: MD review before a pitch book page.
Example Output
1. DATA CHECK - All 5 peers have price, diluted shares, debt, cash, LTM revenue, and LTM EBITDA as of the same date. OK. - Peer D: EBITDA margin 13% vs 18% to 29% for the others, so its EV/EBITDA (51.0x) is an outlier. Shown, but excluded from the EBITDA range. - Units: $m except price per share. 2. COMPS TABLE ($m) | Peer | Price | Shares (m) | Equity value | + Debt | - Cash | EV | LTM revenue | LTM EBITDA | EV/Revenue | EV/EBITDA | EBITDA margin | |---|---|---|---|---|---|---|---|---|---|---|---| | Peer A | $42.00 | 85 | 3,570 | 300 | 120 | 3,750 | 610 | 140 | 6.15x | 26.8x | 23% | | Peer B | $18.50 | 160 | 2,960 | 0 | 240 | 2,720 | 520 | 95 | 5.23x | 28.6x | 18% | | Peer C | $65.00 | 40 | 2,600 | 450 | 90 | 2,960 | 880 | 210 | 3.36x | 14.1x | 24% | | Peer D | $27.00 | 110 | 2,970 | 150 | 60 | 3,060 | 470 | 60 | 6.51x | 51.0x | 13% | | Peer E | $120.00 | 30 | 3,600 | 200 | 350 | 3,450 | 1,150 | 330 | 3.00x | 10.5x | 29% | EV = price x diluted shares + debt - cash. 3. SUMMARY STATISTICS EV/Revenue, all 5: low 3.00x, median 5.23x, mean 4.85x, high 6.51x EV/EBITDA, all 5: low 10.5x, median 26.8x, mean 26.2x, high 51.0x EV/EBITDA, excluding Peer D: low 10.5x, median 20.4x, mean 20.0x, high 28.6x 4. IMPLIED VALUE FOR HARBOR PAYROLL (LTM revenue 120, LTM EBITDA 30, net debt 15) EV/EBITDA uses the middle two of the four peers ex Peer D and their median. EV/Revenue uses the middle three of all five peers (Peer D is not an outlier on revenue) and their median. | Method | Multiple | Implied EV | Implied equity value | |---|---|---|---| | EV/EBITDA low | 14.1x | 422.9 | 407.9 | | EV/EBITDA median | 20.4x | 613.2 | 598.2 | | EV/EBITDA high | 26.8x | 803.6 | 788.6 | | EV/Revenue low | 3.36x | 403.6 | 388.6 | | EV/Revenue median | 5.23x | 627.7 | 612.7 | | EV/Revenue high | 6.15x | 737.7 | 722.7 | Equity value = implied EV - net debt of 15. Multiples are rounded in the table; values use unrounded multiples. 5. NOTES FOR THE MD - The EV/EBITDA range is wide (14.1x to 26.8x) because Peers C and E are larger and lower-multiple. Consider whether size makes them less comparable. - Harbor's 25% EBITDA margin sits inside the peer range (18% to 29% ex Peer D). - Not checked: whether peer EBITDA is adjusted the same way as Harbor's. Ask for the adjustments before this goes in a book. 6. OPEN QUESTIONS - Are peer share counts diluted (treasury method) as of the price date? - Does Harbor's net debt include leases?