lbo-model skill

Environment

This skill assumes headless openpyxl — you are producing an .xlsx file on disk. Follow the excel-author skill's conventions for cell coloring, formulas, named ranges, and sensitivity tables. Recalculate before delivery: python /path/to/excel-author/scripts/recalc.py ./out/model.xlsx.

TEMPLATE REQUIREMENT

This skill uses templates for LBO models. Always check for an attached template file first.

Before starting any LBO model:

1. If a template file is attached/provided: Use that template's structure exactly - copy it and populate with the user's data
2. If no template is attached: 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."
3. If using the standard template: Copy examples/LBO_Model.xlsx as your starting point and populate it with the user's assumptions

IMPORTANT: When a file like LBO_Model.xlsx is attached, you MUST use it as your template - do not build from scratch. Even if the template seems complex or has more features than needed, copy it and adapt it to the user's requirements. Never decide to "build from scratch" when a template is provided.

CRITICAL INSTRUCTIONS — READ FIRST

Use Python/openpyxl. Write formula strings (ws["D20"] = "=B5*B6"), then run the excel-author skill's recalc.py helper before delivery.

Core Principles

• Every calculation must be an Excel formula - NEVER compute values in Python and hardcode results into cells. When using openpyxl, write cell.value = "=B5*B6" (formula string), NOT cell.value = 1250 (computed result). The model must be dynamic and update when inputs change.
• Use the template structure - Follow the organization in examples/LBO_Model.xlsx or the user's provided template. Do not invent your own layout.
• Use proper cell references - All formulas should reference the appropriate cells. Never type numbers that should come from other cells.
• Maintain sign convention consistency - Follow whatever sign convention the template uses (some use negative for outflows, some use positive). Be consistent throughout.
• Work section by section, verify with user at each step - Complete one section fully, show the user what was built, run the section's verification checks, and get confirmation BEFORE moving to the next section. Do NOT build the entire model end-to-end and then present it — later sections depend on earlier ones, so catching a mistake in Sources & Uses after the returns are already built means rework everywhere.

Formula Color Conventions

• Blue (0000FF): Hardcoded inputs - typed numbers that don't reference other cells
• Black (000000): Formulas with calculations - any formula using operators or functions (=B4*B5, =SUM(), =-MAX(0,B4))
• Purple (800080): Links to cells on the same tab - direct references with no calculation (=B9, =B45)
• Green (008000): Links to cells on different tabs - cross-sheet references (=Assumptions!B5, ='Operating Model'!C10)

Fill Color Palette — Professional Blues & Greys (Default unless user/template specifies otherwise)

• Keep it minimal — only use blues and greys for cell fills. Do NOT introduce greens, yellows, reds, or multiple accents. A professional LBO model uses restraint.
• Default fill palette:
• Section headers (Sources & Uses, Operating Model, etc.): Dark blue #1F4E79 with white bold text
• Column headers (Year 1, Year 2, etc.): Light blue #D9E1F2 with black bold text
• Input cells: Light grey #F2F2F2 (or just white) — the blue font is the signal, fill is secondary
• Formula/calculated cells: White, no fill
• Key outputs (IRR, MOIC, Exit Equity): Medium blue #BDD7EE with black bold text
• That's the whole palette. 3 blues + 1 grey + white. If the template uses its own colors, follow the template instead.
• Note: The blue/black/purple/green font colors above are for distinguishing inputs vs formulas vs links. Those are separate from the fill palette here — both work together.

Number Formatting Standards

• Currency: $#,##0;($#,##0);"-" or $#,##0.0 depending on template
• Percentages: 0.0% (one decimal)
• Multiples: 0.0"x" (one decimal)
• MOIC/Detailed Ratios: 0.00"x" (two decimals for precision)
• All numeric cells: Right-aligned

Clarify Requirements First

Before filling any formulas:

• Examine the template structure - Identify all sections, understand the timeline (which columns are which periods), note any existing formulas
• Ask the user if anything is unclear - If the template structure, calculation methods, or requirements are ambiguous, ask before proceeding
• Confirm key assumptions - Any key inputs, calculation preferences, or specific requirements
• ONLY AFTER understanding the template, proceed to fill in formulas

TEMPLATE ANALYSIS PHASE - DO THIS FIRST

Before filling any formulas, examine the template thoroughly:

1. Map the structure - Identify where each section lives and how they relate to each other. Note which sections feed into others.

1. Understand the timeline - Which columns represent which periods? Is there a "Closing" or "Pro Forma" column? Where does the projection period start?

1. Identify input vs formula cells - Templates often use color coding, borders, or shading to indicate which cells need inputs vs formulas. Respect these conventions.

1. Read existing labels carefully - The row labels tell you exactly what calculation is expected. Don't assume - read what the template is asking for.

1. Check for existing formulas - Some templates come partially filled. Don't overwrite working formulas unless specifically asked.

1. Note template-specific conventions - Sign conventions, subtotal structures, how sections are organized, whether there are separate tabs for different components, etc.

FILLING FORMULAS - GENERAL APPROACH

For each cell that needs a formula, follow this hierarchy:

Step 1: Check the Template

• Does the cell already have a formula? If yes, verify it's correct and move on.
• Is there a comment or note indicating the expected calculation?
• Does the row/column label make the calculation obvious?
• Do neighboring cells show a pattern you should follow?

Step 2: Check the User's Instructions

• Did the user specify a particular calculation method?
• Are there stated assumptions that affect this formula?
• Any special requirements mentioned?

Step 3: Apply Standard Practice

• If neither template nor user specifies, use standard LBO modeling conventions
• Document any assumptions you make
• If genuinely uncertain, ask the user

COMMON PROBLEM AREAS

The following calculation patterns frequently cause issues across LBO models. Pay special attention when you encounter these:

Balancing Sections

• When two sections must equal (e.g., Sources = Uses), one item is typically the "plug" (balancing figure)
• Identify which item is the plug and calculate it as the difference

Tax Calculations

• Tax formulas should only reference the relevant income line and tax rate
• Should NOT reference unrelated sections (e.g., debt schedules)
• Consider whether losses create tax shields or are simply ignored

Interest and Circular References

• Interest calculations can create circularity if they reference balances affected by cash flows
• Use Beginning Balance (not average or ending) to break circular references
• Pattern: Interest → Cash Flow → Paydown → Ending Balance (if interest uses ending balance, this circles back)

Debt Paydown / Cash Sweeps

• When multiple debt tranches exist, there's usually a priority order
• Cash sweep should respect the priority waterfall
• Balances cannot go negative - use MAX or MIN functions appropriately

Returns Calculations (IRR/MOIC)

• Cash flows must have correct signs: Investment = negative, Proceeds = positive
• If using XIRR, need corresponding dates
• If using IRR, cash flows should be in consecutive periods
• MOIC = Total Proceeds / Total Investment

Sensitivity Tables

• Use ODD dimensions (5×5 or 7×7) — never 4×4 or 6×6. Odd dimensions guarantee a true center cell.
• Center cell = base case. Build the row and column axis values symmetrically around the model's actual assumptions (e.g., if base entry multiple = 10.0x, axis = [8.0x, 9.0x, 10.0x, 11.0x, 12.0x]). The center cell's IRR/MOIC MUST then equal the model's actual IRR/MOIC output — this is the proof the table is wired correctly.
• Highlight the center cell — medium-blue fill (#BDD7EE) + bold font so the base case is visually anchored.
• Excel's DATA TABLE function may not work with openpyxl — instead write explicit formulas that reference row/column headers
• Each cell should show a DIFFERENT value — if all same, formulas aren't varying correctly
• Use mixed references (e.g., $A5 for row input, B$4 for column input)

VERIFICATION CHECKLIST - RUN AFTER COMPLETION

Run Formula Validation

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

Must return success with zero errors.

Section Balancing

• [ ] Any sections that must balance (Sources/Uses, Assets/Liabilities) balance exactly
• [ ] Plug items are calculated correctly as the balancing figure
• [ ] Amounts that should match across sections are consistent

Income/Operating Projections

• [ ] Revenue/top-line builds correctly from drivers or growth rates
• [ ] All cost and expense items calculated appropriately
• [ ] Subtotals and totals sum correctly
• [ ] Margins and ratios are reasonable
• [ ] Links to assumptions are correct

Balance Sheet (if applicable)

• [ ] Assets = Liabilities + Equity (must balance)
• [ ] All items link to appropriate schedules or roll-forwards
• [ ] Beginning balances = prior period ending balances
• [ ] Check row included and shows zero