Neura Market LBO Model Builder Reference

Overview

The LBO Model Builder constructs investment banking-quality leveraged buyout models as .xlsx files. It uses headless openpyxl, a provided or standard template, and Excel formulas to produce dynamic, formula-driven financial models. The tool is designed for users who need a professional LBO model with Sources & Uses, Operating Model, Debt Schedule, Returns Analysis, and Sensitivity Tables.

When to Use

Use this tool when:

A user requests an LBO model for a target company.
The user provides or references an LBO template file.
The user needs a dynamic, formula-driven financial model with all standard sections.
The user expects professional formatting, consistent sign conventions, and built-in validation checks.

Prerequisites

Before using the tool, ensure the following are in place:

A Python environment with openpyxl installed.
Access to the excel-author skill's conventions and the recalc.py script located at /path/to/excel-author/.
A template file (either user-provided or the standard template at examples/LBO_Model.xlsx).
User-provided assumptions or the ability to source data from MCP or web resources (SEC EDGAR, company IR pages).
User confirmation at each section checkpoint.

Capabilities

The tool provides the following capabilities:

Formula-driven cells: Uses openpyxl to write formula strings into cells, never hardcoded computed values.
Template population: Copies and populates a provided template file. If none is provided, it asks the user and uses the standard template from examples/LBO_Model.xlsx.
Font color conventions:
- Blue for inputs.
- Black for formulas.
- Purple for same-tab links.
- Green for cross-tab links.
Fill color palette:
- Dark blue headers (#1F4E79).
- Light blue column headers (#D9E1F2).
- Light grey input cells (#F2F2F2).
- White formula cells.
- Medium blue key outputs (#BDD7EE).
Number formatting:
- Currency: $#,##0;($#,##0);"-"
- Currency with one decimal: $#,##0.0
- Percentages: 0.0%
- Multiples: 0.0"x"
- MOIC: 0.00"x"
- Right-aligned text.
Balancing: Balances Sources & Uses with a plug item.
Tax calculation: Calculates tax from the relevant income line and tax rate only.
Interest calculation: Uses beginning balance for interest to avoid circular references.
Debt paydown: Implements debt paydown/cash sweep with a priority waterfall and MAX/MIN to prevent negative balances.
Returns computation: Computes IRR and MOIC with correct sign conventions (investment negative, proceeds positive).
Sensitivity tables: Builds sensitivity tables with odd dimensions (5x5 or 7x7), symmetric axes around the base case, center cell highlighted and matching actual output. Uses mixed references ($A5, B$4) in sensitivity tables.
Recalculation: Runs the recalc.py script before delivery.
Section-by-section workflow: Works section-by-section with user verification checkpoints after each major section.
Data sourcing: Falls back to web search, SEC EDGAR, or company IR pages for financial data if no MCP is configured; never fabricates data.

Parameters

The tool accepts the following parameters:

template_file: Path or reference to an LBO template .xlsx file provided by the user. Not required; if not provided, the tool asks the user and uses the standard template.
user_assumptions: Key inputs such as purchase price, debt structure, growth rates, margins, exit multiple, etc. Required; must be provided by the user or sourced from MCP or web.
sensitivity_dimensions: Dimensions of the sensitivity table (e.g., 5x5, 7x7). Must be odd. Not required; defaults to 5x5.

Constraints and Caveats

Every calculation must be an Excel formula string, never a hardcoded computed value. For example, use cell.value = "=B5*B6" instead of cell.value = 1250.
Use the template structure exactly; do not invent your own layout.
Maintain sign convention consistency throughout the model.
Interest calculations must use beginning balance to avoid circular references.
Debt balances cannot go negative; use MAX/MIN appropriately.
Sensitivity tables must have odd dimensions (5x5 or 7x7) with a true center cell that equals the base case output.
The center cell of the sensitivity table must be highlighted with medium-blue fill (#BDD7EE) and bold font.
Never fabricate financial data; flag unsourced cells as [UNSOURCED] and surface to the user.
Recalculate before delivery using the recalc.py script.
Work section-by-section with user verification at each checkpoint; do not build the entire model end-to-end before presenting.

Procedure

Template Acquisition

Check if a template file is attached or provided. If yes, use it exactly. If no, ask the user: "Do you have a specific LBO template you'd like me to use? If not, I can use the standard template which includes Sources & Uses, Operating Model, Debt Schedule, and Returns Analysis." If using the standard template, copy examples/LBO_Model.xlsx.

Template Examination

Examine the template thoroughly. Map the structure, understand the timeline (closing column, projection periods), identify input cells versus formula cells, read all labels, check for existing formulas, and note any template-specific conventions.

Requirements Clarification

Clarify requirements with the user if anything is ambiguous, such as structure, calculation methods, or key assumptions.

Formula Population

Fill formulas section by section. For each cell, check the template (existing formula, comment, label, pattern), check user instructions, and apply standard LBO practice. Never hardcode calculated values. Use formula strings like ws["D20"] = "=B5*B6" or cell.value = "=B5*B6". Common formula patterns include:

=B4*B5 for multiplication.
=SUM() for summation.
=-MAX(0,B4) to prevent negative values.
=B9 or =B45 for same-sheet references.
=Assumptions!B5 for cross-sheet references.
='Operating Model'!C10 for cross-sheet references with sheet names containing spaces.

Sources & Uses Verification

After completing the Sources & Uses section, show the balanced table to the user. Confirm the plug item is correct. Get user sign-off before proceeding.

Operating Model Verification

After completing the Operating Model or Projections section, show the projected P&L. Confirm growth rates and margins. Get user sign-off before proceeding to the debt schedule.

Debt Schedule Verification

After completing the Debt Schedule, show beginning and ending balances and interest. Confirm the waterfall logic. Get user sign-off before proceeding to returns.

Returns Verification

After completing the Returns section (IRR and MOIC), show the cash flow series and outputs. Confirm signs and ranges. Get user sign-off before proceeding to sensitivity tables.

Sensitivity Tables Verification

After completing the Sensitivity Tables, show that each cell varies. Confirm the base case lands where expected. The sensitivity table should use symmetric axes around the base case, for example [8.0x, 9.0x, 10.0x, 11.0x, 12.0x] for exit multiples or [base-2Δ, base-Δ, base, base+Δ, base+2Δ] for other inputs. Use mixed references like $A5 and B$4 to ensure proper variation.

Formula Validation

Run formula validation using the recalc.py script. Execute the following command:

python /path/to/excel-author/scripts/recalc.py model.xlsx

The command must return success with zero errors. Note the exact command for delivery is python /path/to/excel-author/scripts/recalc.py ./out/model.xlsx.

Verification Checklist

Run a verification checklist covering:

Section balancing.
Income and operating projections.
Balance sheet (if applicable).
Cash flow.
Supporting schedules.
Debt and financing schedules.
Returns and output analysis.
Sensitivity tables.
Formatting.
Logical sanity checks.

Delivery

Deliver the .xlsx file to the user.

Failure Modes

Be aware of these common failure modes and how to avoid them:

Hardcoding calculated values: The model does not update when inputs change. Always use formula strings.
Wrong cell references after copying: Formulas point to wrong cells. Verify all references.
Circular reference errors: The model cannot calculate. Fix by using beginning balance for interest.
Sections don't balance: Totals that should match don't. Ensure one item is the plug.
Negative balances where impossible: Paying or using more than available. Use MAX/MIN.
IRR or return errors: Wrong signs or incomplete ranges. Check cash flow signs and formula range.
Sensitivity table shows same value: Formula not varying with inputs. Check mixed references.
Roll-forwards don't tie: Beginning balance does not equal prior period ending balance. Verify links between periods.
Inconsistent sign conventions: Additions become subtractions or vice versa. Maintain consistency.
Data table function not working with openpyxl: Use explicit formulas instead of Excel's DATA TABLE function.

Data Sourcing

If the user does not provide financial data and no MCP is configured, use the following fallback methods in order:

native-mcp for MCP-based data.
web_search and web_extract for web-based data.
browser_navigate to https://www.sec.gov/cgi-bin/browse-edgar for SEC EDGAR data.
Company IR pages for additional data.

Never fabricate data. If data cannot be sourced, flag the cell with [UNSOURCED] and inform the user.

Formatting Details

Font Colors

Apply font colors as follows:

Inputs: Blue.
Formulas: Black.
Same-tab links: Purple.
Cross-tab links: Green.

Fill Colors

Apply fill colors as follows:

Dark blue headers: #1F4E79.
Light blue column headers: #D9E1F2.
Light grey input cells: #F2F2F2.
White formula cells: No fill or white.
Medium blue key outputs: #BDD7EE.

Number Formats

Apply number formats as follows:

Currency: $#,##0;($#,##0);"-" for standard currency.
Currency with one decimal: $#,##0.0 for precise values.
Percentages: 0.0% for growth rates, margins, and tax rates.
Multiples: 0.0"x" for valuation multiples.
MOIC: 0.00"x" for return multiples.

Sensitivity Table Center Cell

The center cell of the sensitivity table must be highlighted with medium-blue fill (#BDD7EE) and bold font. This cell must equal the base case output exactly.

Example Workflow

A typical workflow proceeds as follows:

The user provides a template file LBO_Model.xlsx.
The tool copies it and populates the Sources & Uses section with the user's debt and equity assumptions.
The tool builds operating projections from growth rates and margins.
The tool creates a debt schedule with a waterfall paydown structure.
The tool computes IRR and MOIC from the exit multiple.
The tool generates a 5x5 sensitivity table around the base case.
Each section is verified with the user before proceeding to the next.
The tool runs recalc.py and delivers the final .xlsx file.

Sign Conventions

Maintain consistent sign conventions throughout the model. For example:

Investments (initial equity) are negative.
Proceeds (exit proceeds, dividends) are positive.
Debt draws are positive, debt repayments are negative.
Revenues are positive, expenses are negative (or shown as positive with subtraction formulas).

Interest Calculation

To avoid circular references, always calculate interest using the beginning balance of the period. For example, if the beginning balance is in cell B10 and the interest rate is in cell C5, use =B10*C5 for the interest expense.

Debt Paydown Waterfall

Implement debt paydown with a priority waterfall. Use MAX and MIN to ensure balances never go negative. For example, to calculate the paydown amount for a tranche, use =-MAX(0, MIN(available_cash, beginning_balance)).

Sensitivity Table Construction

When building sensitivity tables:

Use odd dimensions (5x5 or 7x7).
Create symmetric axes around the base case. For example, if the base exit multiple is 10.0x, the axis could be [8.0x, 9.0x, 10.0x, 11.0x, 12.0x].
For other inputs, use [base-2Δ, base-Δ, base, base+Δ, base+2Δ].
Use mixed references: $A5 for the row input and B$4 for the column input.
Ensure the center cell (intersection of the base row and base column) equals the actual output.

Recalculation

Before delivering the model, run the recalc.py script to ensure all formulas calculate correctly. The exact command is:

python /path/to/excel-author/scripts/recalc.py ./out/model.xlsx

This script must return success with zero errors. If errors occur, fix them before delivery.

Unsourced Data

If financial data cannot be sourced from the user, MCP, or web resources, flag the cell with [UNSOURCED] and inform the user. Do not fabricate any data.

Build LBO Models with IRR/MOIC in Excel Using Hermes Agent