Map Vendor Concentration, Leverage, and Renewal Risk from a Spreadsheet
Paste a vendor list into Claude and surface the procurement risks that derail integrations
Question this answers: Which vendors create concentration risk, hold pricing power, or have contracts expiring at a moment that could complicate a transaction or integration?
Tools used: Claude, Excel or CSV vendor export
Time to run: ~20 min
The problem
Vendor risk analysis during diligence is typically buried in a data room folder containing dozens of contract PDFs and a raw AP export. An analyst must manually cross-reference spend data against contract terms, renewal dates, and supplier market position to identify which vendors hold meaningful leverage. For a mid-market company with 80 to 200 active vendors, this process consumes two to four hours and still tends to miss structural patterns because the work is done line by line rather than across the full population at once.
The pain lands on deal teams and operating partners who need answers before management calls, not after them. Concentration risk matters because a target that sources 40 percent of a critical input from a single supplier has a negotiating position that looks very different once an acquirer is disclosed. Renewal risk matters because contracts expiring within 12 months of close create immediate cost uncertainty that should flow into the model.
The output of this analysis directly informs LOI assumptions, working capital adjustments, and the 100-day vendor renegotiation roadmap. Getting it wrong, or skipping it, means surprises post-close that are structurally harder to fix than they would have been at the term sheet stage.
The workflow
Step 1: Prepare the vendor data
Export the target’s accounts payable or vendor master file as a CSV. The minimum useful columns are: vendor name, annual spend, category or spend type, contract end date, and whether the vendor is sole-sourced. If contract end dates are not in the AP system, pull them from the contract register in the data room and add them manually for the top 30 vendors by spend. Save the file as vendor_list.csv.
If spend data is in raw transaction form rather than summarized, run a quick pivot in Excel to aggregate annual spend by vendor before proceeding.
Step 2: Structure the prompt
Open Claude and paste the vendor data directly into the conversation window, or copy the top 50 rows if the list is large. Use the prompt in the Try It section below verbatim, substituting your pasted data where indicated.
The prompt instructs Claude to perform four discrete analyses: spend concentration by vendor and by category, identification of sole-source or limited-source suppliers, renewal clustering by quarter, and a qualitative leverage assessment for the highest-spend and highest-risk vendors.
Step 3: Review the concentration output
Claude will return a structured breakdown. The first section ranks vendors by spend share and flags any vendor representing more than 10 percent of total addressable spend. The second section groups vendors by category and calculates category-level concentration. Review both layers because a company can look diversified at the vendor level but be dangerously concentrated by category.
Step 4: Interrogate the leverage flags
For each vendor Claude flags as high-leverage, ask a follow-up question: “For [Vendor Name], what are the typical switching costs in this category, and what contractual terms would indicate the company has limited negotiating power?” This second pass surfaces the qualitative risk that raw spend data cannot show.
Step 5: Build the renewal calendar
Claude will group contracts by renewal quarter. Ask it to output this as a table sorted by spend within each quarter. Any quarter containing renewals representing more than 15 percent of total vendor spend, or any single critical sole-source renewal, should be flagged in the diligence memo and modeled with a price increase scenario.
Step 6: Export and integrate
Copy Claude’s tables into your diligence workbook. The vendor risk section of the memo should include the concentration heatmap by category, the sole-source registry, the renewal calendar, and the three to five highest-priority vendor risks with a recommended action for each.
The output
Vendor Concentration Summary
| Vendor | Category | Annual Spend | Spend Share | Sole Source | Contract End | Risk Flag |
|---|---|---|---|---|---|---|
| Vendor A | Freight | $2,340,000 | 18.2% | Yes | Q2 2025 | Critical |
| Vendor B | SaaS / Data | $1,820,000 | 14.1% | Yes | Q4 2024 | Critical |
| Vendor C | Packaging | $1,200,000 | 9.3% | No | Q1 2026 | Moderate |
| Vendor D | Marketing Print | $980,000 | 7.6% | No | Q3 2025 | Low |
| Vendor E | IT Infrastructure | $870,000 | 6.8% | No | Q1 2025 | Moderate |
Category Concentration
| Category | Total Spend | Vendor Count | Top Vendor Share | Risk Level |
|---|---|---|---|---|
| Freight | $3,100,000 | 3 | 75.5% | High |
| SaaS / Data | $2,400,000 | 4 | 75.8% | High |
| Packaging | $1,900,000 | 6 | 63.2% | Moderate |
| IT Infrastructure | $1,600,000 | 5 | 54.4% | Moderate |
Renewal Clustering by Quarter
| Quarter | Contracts Renewing | Total Spend at Risk | Sole-Source Count |
|---|---|---|---|
| Q4 2024 | 4 | $2,310,000 | 1 |
| Q1 2025 | 6 | $1,740,000 | 0 |
| Q2 2025 | 3 | $2,580,000 | 1 |
| Q3 2025 | 5 | $1,120,000 | 0 |
Leverage Assessment: Top Risks
- Vendor A (Freight): Sole-source freight provider representing 18.2% of spend. Contract expires Q2 2025, which falls within the likely integration window. Switching costs are high given embedded routing systems. Recommend locking a 24-month extension before disclosure or modeling a 12-15% rate increase.
- Vendor B (SaaS / Data): Proprietary data feed with no identified alternative. Renewal in Q4 2024 creates pre-close pricing risk. Vendor has raised rates an average of 9% annually. Treat as a working capital exposure.
- Vendor E (IT Infrastructure): Not sole-source but migration cost is 6-9 months of engineering time. Renewal in Q1 2025 requires attention if IT stack rationalization is part of the thesis.
How to use this
Run this analysis immediately after receiving the initial data room drop, before management presentations. The output shapes the questions you ask management about procurement strategy and whether they have any formal vendor risk program. If the concentration picture is worse than expected, it also informs how you present the business to lenders who will want to understand supply chain resilience.
The renewal calendar feeds directly into the financial model. Any sole-source renewal within 18 months of close should carry a price increase assumption in the base case, not the downside. Failure to model this is one of the most common causes of EBITDA miss in the first year post-close.
Watch for two failure modes. First, AP data often understates true vendor dependency because some critical vendors are paid through expense reports or intercompany allocations rather than the AP system. Always cross-check the top vendor list against the operations team’s own view of critical suppliers. Second, Claude’s leverage assessment is directionally correct but not a substitute for market knowledge. Use it to prioritize which vendors warrant a specialist call, not to replace one.
You are a procurement risk analyst supporting a private equity diligence process.
Below is a vendor list exported from the target company's accounts payable system. Each row contains: Vendor Name, Category, Annual Spend (USD), Sole Source (Yes/No), Contract End Date.
[PASTE VENDOR DATA HERE]
Perform the following analyses and return each as a clearly labeled section:
1. SPEND CONCENTRATION: Rank vendors by annual spend. Calculate each vendor's share of total spend. Flag any vendor above 10% of total spend.
2. CATEGORY CONCENTRATION: Aggregate spend by category. For each category, calculate total spend, number of vendors, and the top vendor's share of category spend. Flag any category where the top vendor represents more than 60% of category spend.
3. SOLE-SOURCE REGISTRY: List all vendors marked as sole-source. For each, note annual spend, contract end date, and a one-sentence description of the typical switching cost profile for that category based on your general knowledge.
4. RENEWAL CALENDAR: Group contracts by renewal quarter. For each quarter, show total spend renewing and the count of sole-source vendors renewing in that quarter. Flag any quarter where renewing spend exceeds 15% of total spend or includes a sole-source vendor.
5. LEVERAGE ASSESSMENT: For the three vendors that represent the greatest combination of high spend, sole-source status, and near-term renewal, write a short paragraph on each explaining the negotiating risk and a recommended action for the diligence team.
Format your output using markdown tables where applicable. Be specific and avoid generic procurement advice.