An agentic system for LBO models and simulations

The banker sends a CIM

Your banker emails over a CIM and a forecast. I want to know how much growth can disappoint before debt stops coming down, and what the equity return looks like without a higher exit multiple.

I’d give the agents the first pass through the documents and the financial definitions, then keep the cash-flow and debt calculations in code. I want to change growth, margin or financing and follow the effect through the annual schedule. The workbook should keep each case’s assumptions beside its results.

Keep the CIM (confidential information memorandum), supporting files and their versions in a document manifest. Record the available text extracts and tables, and retain the filename, page or tab, period, currency and scale with each figure. You’ll want that reference when a number looks wrong three steps later.

Suppose a revenue schedule puts actual results and a forecast in adjacent columns. Preserve that distinction when extracting the table. A trailing-twelve-month figure also needs its own period label. For adjusted EBITDA, keep the adjustment bridge and definition alongside the amount; don’t leave the explanation behind in the PDF.

Give the agents a defined job

To get that first pass done, split it into five jobs. In the diagram, Jared handles diligence, Monica financial analysis, Richard thesis development, Dinesh synthesis and Gilfoyle evidence review. The harness is the code that supplies their instructions, tools and output schemas and runs the tasks in order.

Jared looks for contradictions, missing schedules and questions to send back to the banker. Monica extracts the operating history and forecast, defines the metrics and calculates supported ratios. Both read the evidence package. They run sequentially, but Monica goes back to the financial materials rather than using Jared’s report as her financial source.

For a software business, ask Monica to reconcile revenue, annual recurring revenue, bookings and billings. A services invoice in the sales table shouldn’t quietly join the recurring-revenue series. Each metric needs a definition, a period and a source, with missing and conflicting values recorded separately.

Richard takes the diligence and financial outputs and describes what would need to change in the business. Dinesh assembles the findings into one evaluation. Gilfoyle checks the draft against the materials and removes or qualifies unsupported claims. The reviewed output keeps the same structured shape so the model can read it.

1evidence = load_documents(manifest)2 3diligence = run_task("diligence", evidence)4financials = run_task("financial_analysis", evidence)5 6thesis = run_task("thesis", {7    "evidence": evidence,8    "diligence": diligence,9    "financials": financials,10})11 12draft = run_task("synthesis", {13    "diligence": diligence,14    "financials": financials,15    "thesis": thesis,16})17 18reviewed = run_task("evidence_review", {19    "evidence": evidence,20    "draft": draft,21})

Give the agents tools that fit those jobs. A document reader retrieves narrative evidence. Table tools list registered tables, describe columns and run read-only queries. The runtime controls the available files and tables; text inside a CIM or cell comment cannot grant another permission.

Also decide what happens when a task fails. Record the failure and the missing fields, then carry those gaps forward. A typed response can reject a string where a number belongs. It won’t tell you whether somebody put bookings in the ARR column.

Decide what goes into the model

Monica’s output now has to become something the model can use: entry period, revenue, EBITDA, historical periods and a projection basis, all with source notes. Put financing and operating assumptions in a separate record. I want to be able to point to a value and say where it came from.

If the materials give you revenue and an EBITDA margin, calculate EBITDA and retain the expression and both inputs. If the margin is missing too, you need an assumption or another answer from the banker. An analyst-entered margin must stay distinguishable from a reported one.

Missing evidence and model assumptions are different fields. A default can make an example runnable, but it cannot establish a business fact. Record which rows were reported, calculated or assumed. One source-status label for the whole package loses that distinction when the origins are mixed.

1@dataclass(frozen=True)2class SourceRef:3    file: str4    location: str5    period: str6 7@dataclass(frozen=True)8class Observation:9    amount: Decimal | None10    units: str11    period_type: Literal["actual", "forecast"]12    origin: Literal["reported", "calculated", "assumed"]13    sources: tuple[SourceRef, ...]

The assumptions record covers valuation, debt, interest, fees, cash requirements, taxes, capex, working capital and hold period. Validate units and ranges before calculation. A percentage entered as 25 instead of 0.25 should fail a decimal-rate contract. An EBITDA-based purchase multiple needs EBITDA for the stated entry period.

For the worked numbers, use Pied Piper: entry revenue of $100m, EBITDA of $25m and an entry multiple of 10×. Debt and sponsor equity fund the acquisition. The entry table separates enterprise value, fees and cash funded into the business, so you can reproduce the equity cheque.

1{2  "company": "Pied Piper",3  "currency": "USD",4  "units": "millions",5  "years": 5,6  "entry_revenue": 100,7  "ebitda_margin": 0.25,8  "revenue_growth": 0.08,9  "entry_multiple": 10,10  "exit_multiple": 10,11  "entry_debt": 125,12  "entry_fees": 5,13  "minimum_cash": 5,14  "interest_rate": 0.08,15  "tax_rate": 0.25,16  "depreciation_pct": 0.02,17  "capex_pct": 0.03,18  "nwc_pct": 0.1,19  "forecast_ebitda_margin": 0.2520}

Keep the plan, then underwrite it

Keep management’s forecast as a reference, then write down what you’re willing to underwrite. I’d keep those in different records so the assumptions have an owner. Build base, upside and downside cases, each stating how growth, margins, financing or exit assumptions differ.

Each case needs a complete assumption set. The upside might combine higher growth with better margins and a different exit multiple. The downside tests slower growth, weaker margins and a lower exit multiple. I’d show those changes beside the return, including the case with no help from a higher exit multiple.

Take Richard’s operating proposals back to the inputs. If the thesis depends on pricing, specify whether it changes revenue growth, gross margin or both, and when. A cost program needs a margin path. A paragraph saying pricing will improve is not yet a forecast assumption.

Keep the reference forecast unchanged when editing a case. Store overrides separately and retain them with the outputs. In the Pied Piper comparison, forecast growth, forecast EBITDA margin and exit multiple change. Entry EBITDA, purchase enterprise value of $250m and sponsor equity of $135m stay fixed.

1cases = {2    "base": {},3    "upside": {4        "revenue_growth": 0.10,5        "forecast_ebitda_margin": 0.27,6        "exit_multiple": 10.5,7    },8    "downside": {9        "revenue_growth": 0.05,10        "forecast_ebitda_margin": 0.22,11        "exit_multiple": 9.0,12    },13}14 15for name, overrides in cases.items():16    case_inputs = {**base_inputs, **overrides}17    results[name] = calculate(case_inputs)

Run those three input sets through the same calculation. The case selector below lets you compare the assumptions and resulting returns. The labels describe scenarios; they don’t attach a probability to what happens.

Change a lever and follow the cash

Say you want to test the growth assumption. Follow it through revenue, EBITDA, tax, capex and working capital before looking at the return. Do the same for a financing change: debt affects interest, repayment and the balance left at exit.

In the base example, Year 1 revenue is $108m and EBITDA is $27m. Opening debt is $125m, so opening-balance interest is $10m. Depreciation affects tax; it is not deducted again from cash. After tax, capex and the working-capital increase, $9.25m remains before debt repayment.

The sweep uses cash above the $5m minimum balance and cannot repay more than the debt outstanding. Year 1 ends with $115.75m of debt. If the calculation cannot maintain the cash floor, it raises a cash-shortfall error. Once debt is repaid, further cash accumulates.

Now change the debt amount. The initial equity cheque changes, but so do interest, tax, cash available for repayment and later debt balances. Reusing the original debt schedule would leave out those effects. I’d inspect the annual schedule before spending much time on the new IRR.

At exit, value the business using final-year EBITDA and the exit multiple. This example includes all closing cash in equity proceeds, no exit fees and full sponsor ownership. The equity bridge is $\$367.33\mathrm{m}-\$57.48\mathrm{m}+\$5\mathrm{m}=\$314.85\mathrm{m}$. Divide the proceeds by initial sponsor equity to get $\frac{\$314.85\mathrm{m}}{\$135\mathrm{m}}\approx 2.33\times$.

The annualized return is 18.5% over five years with no interim distributions. Distributions on other dates need the corresponding cash-flow calculation; Google distinguishes IRR for periodic cash flows from XIRR for irregular dates.

Run the cases, then the sensitivities

Now take the base case and lower growth while keeping the exit multiple fixed. Inspect how cash available for repayment and the debt balance change. Then vary both inputs across a grid, with annualized equity return in each cell. Name the axes and output so there’s no guessing what the colors mean.

In this public example, every grid point reruns the annual operating and debt schedules. That matters when growth changes working-capital investment or debt changes interest. Keeping the original ending-debt figure would hide part of the effect you’re trying to test.

Start with the center cell and compare it with the base case. Then move along a row or column. An entry-multiple versus exit-multiple table examines purchase price and sale valuation; a growth versus exit-multiple table combines an operating assumption with valuation.

The base example produces 2.33× and 18.5%. When another cell looks attractive, open its schedule and inspect the implied revenue, margin, debt balance and cash requirement. Read those rows alongside the return.

Save the input set and calculation result for every run. When you return to the workbook, a chart or return should identify its case. You also need to distinguish a changed assumption from new evidence in an updated forecast.

Put the model in Google Sheets

Once you’ve worked through those cases, put the model into an editable workbook. The compiler assigns inputs and calculations to sheets, cells and formulas. Keep the operating model, debt schedule, cash flow and returns linked to the selected case. A case selector needs to change those references as well as the displayed name.

The downloadable example uses three sheets: Inputs, CashFlow and Returns. Its manifest records 92 formula cells. In CashFlow!C13, =MIN(C5,MAX(C12-Inputs!$B$9,0)) caps repayment at opening debt and cash above the minimum balance. Keep the address and expression together.

{  "CashFlow!C6": "=C5*Inputs!$B$10",  "CashFlow!C7": "=MAX(C3-C4-C6,0)*Inputs!$B$11",  "CashFlow!C10": "=C3-C6-C7-C8-C9",  "CashFlow!C13": "=MIN(C5,MAX(C12-Inputs!$B$9,0))",  "Returns!B13": "=B10-B11+B12",  "Returns!B14": "=B13/B7",  "Returns!B15": "=B14^(1/5)-1"}

Before writing, compare formula coordinates with the manifest and reject unexpected or missing formulas. Write literal contents with RAW, then write only the compiler’s formula cells with USER_ENTERED. Google’s input-option reference explains the difference: RAW leaves strings unparsed; USER_ENTERED interprets them as spreadsheet input.

1for batch in workbook['value_batches']:2    service.spreadsheets().values().batchUpdate(3        spreadsheetId=spreadsheet_id, body=batch,4    ).execute()

The two batch methods do different work. spreadsheets.values.batchUpdate writes cell ranges. spreadsheets.batchUpdate applies structural requests such as formatting, validation and charts. Keep the formatting requests separate from the financial calculation.

A live export needs an authenticated account, a destination and a sharing policy. The example’s helper accepts an authenticated service and leaves sign-in and permissions to the caller. Check Sheets scopes and Drive access rules for that account and destination.

Read it back, then rerun it

Don’t stop at the new spreadsheet URL. Read the formulas and calculated values back. Google’s render-option reference defines FORMULA and UNFORMATTED_VALUE; its cell reference distinguishes entered formulas, effective values and calculation errors.

Check required sheets and ranges, formula errors, financing identities and selected outputs. If the workbook has a case selector, switch it, read the dependent ranges and compare them with that case’s expected results. Then restore the original selection. You want the numbers to move with the case.

• Entry debt + sponsor equity = enterprise value + fees + funded cash.
• Opening debt − repayment = closing debt.
• Opening cash + cash flow before repayment − repayment = closing cash.

The worked example evaluates all 92 formulas locally and compares 85 numeric cells with the Python calculation. Its 102 checks include those comparisons and cash, debt and financing identities. The preview shows those local results; Google Sheets has not calculated them.

{  "checks": 102,  "formula_cells": 92,  "compared_cells": 85,  "tolerance": "0.000001",  "google_sheets_executed": false}

One test deliberately changes the closing-debt formula to add repayment and changes the manifest to match. The formula text agrees with the manifest, but Year 1 debt becomes $134.25m instead of $115.75m. The numeric comparison fails. Compare formula text and calculated values separately.

Then your banker sends version two of the forecast. Keep the old file, record the new source version and review which inputs changed before rerunning the cases. A model can balance while using the wrong period or an assumed margin. The source references let you go back to the document and make that decision.

To run the calculation yourself, use python3 run.py from the downloaded example’s root, then python3 -m unittest discover -s . -v for the tests. Change inputs.json to change the assumptions. The download contains the financial calculation, workbook compiler and local verifier; the document-analysis sequence above is pseudocode.

1import json2from decimal import Decimal, localcontext3from pathlib import Path4 5D = Decimal6ZERO = D(0)7INPUT_KEYS = {8    'company', 'currency', 'units', 'years', 'entry_revenue', 'ebitda_margin',9    'revenue_growth', 'entry_multiple', 'exit_multiple', 'entry_debt',10    'entry_fees', 'minimum_cash', 'interest_rate', 'tax_rate',11    'depreciation_pct', 'capex_pct', 'nwc_pct', 'forecast_ebitda_margin',12}13 14 15def load_inputs(path: Path) -> dict:16    data = json.loads(path.read_text(), parse_float=D, parse_int=D)17    return validate_inputs(data)18 19 20def validate_inputs(data: dict) -> dict:21    if set(data) != INPUT_KEYS:22        raise ValueError(f'input_keys: missing={INPUT_KEYS - set(data)} extra={set(data) - INPUT_KEYS}')23    result = dict(data)24    for key in INPUT_KEYS - {'company', 'currency', 'units'}:25        value = D(str(data[key]))26        if not value.is_finite():27            raise ValueError(f'non_finite: {key}')28        result[key] = value29    if result['currency'] != 'USD' or result['units'] != 'millions':30        raise ValueError('units')31    if not isinstance(result['company'], str) or not result['company'].strip():32        raise ValueError('company')33    if result['years'] != 5:34        raise ValueError('years')35    result['years'] = int(result['years'])36    for key in ('entry_revenue', 'entry_multiple', 'exit_multiple'):37        if result[key] <= 0:38            raise ValueError(f'non_positive: {key}')39    for key in ('entry_debt', 'entry_fees', 'minimum_cash'):40        if result[key] < 0:41            raise ValueError(f'negative: {key}')42    for key in ('ebitda_margin', 'forecast_ebitda_margin', 'interest_rate', 'tax_rate', 'depreciation_pct', 'capex_pct', 'nwc_pct'):43        if not ZERO <= result[key] <= 1:44            raise ValueError(f'rate: {key}')45    if result['revenue_growth'] <= -1:46        raise ValueError('revenue_growth')47    return result48 49 50def calculate(data: dict) -> dict:51    a = validate_inputs(data)52    with localcontext() as context:53        context.prec = 3654        entry_ebitda = a['entry_revenue'] * a['ebitda_margin']55        enterprise_value = entry_ebitda * a['entry_multiple']56        uses = enterprise_value + a['entry_fees'] + a['minimum_cash']57        equity = uses - a['entry_debt']58        if equity <= 0:59            raise ValueError('sponsor_equity')60        previous_revenue = a['entry_revenue']61        debt = a['entry_debt']62        cash = a['minimum_cash']63        rows = []64        for year in range(1, a['years'] + 1):65            revenue = previous_revenue * (1 + a['revenue_growth'])66            ebitda = revenue * a['forecast_ebitda_margin']67            depreciation = revenue * a['depreciation_pct']68            interest = debt * a['interest_rate']69            taxes = max(ebitda - depreciation - interest, ZERO) * a['tax_rate']70            capex = revenue * a['capex_pct']71            delta_nwc = (revenue - previous_revenue) * a['nwc_pct']72            free_cash_flow = ebitda - interest - taxes - capex - delta_nwc73            cash_before_sweep = cash + free_cash_flow74            shortfall = max(a['minimum_cash'] - cash_before_sweep, ZERO)75            if shortfall > 0:76                raise ValueError(f'cash_shortfall: year={year} amount={shortfall}')77            repayment = min(debt, max(cash_before_sweep - a['minimum_cash'], ZERO))78            closing_debt = debt - repayment79            closing_cash = cash_before_sweep - repayment80            rows.append({81                'year': year, 'revenue': revenue, 'ebitda': ebitda,82                'depreciation': depreciation, 'opening_debt': debt,83                'interest': interest, 'taxes': taxes, 'capex': capex,84                'delta_nwc': delta_nwc, 'free_cash_flow': free_cash_flow,85                'opening_cash': cash, 'cash_before_sweep': cash_before_sweep,86                'repayment': repayment, 'closing_debt': closing_debt,87                'closing_cash': closing_cash, 'shortfall': shortfall,88            })89            previous_revenue, debt, cash = revenue, closing_debt, closing_cash90        exit_ev = rows[-1]['ebitda'] * a['exit_multiple']91        exit_equity = exit_ev - debt + cash92        moic = exit_equity / equity93        irr = moic ** (D(1) / D(a['years'])) - 194        return {95            'entry': {'ebitda': entry_ebitda, 'enterprise_value': enterprise_value,96                      'fees': a['entry_fees'], 'cash': a['minimum_cash'],97                      'uses': uses, 'debt': a['entry_debt'], 'equity': equity},98            'years': rows,99            'exit': {'enterprise_value': exit_ev, 'debt': debt, 'cash': cash,100                     'equity': exit_equity, 'moic': moic, 'irr': irr},101        }102 103 104def json_number(value):105    if isinstance(value, D):106        return float(value)107    raise TypeError(type(value).__name__)