This skill should be used when completing LBO (Leveraged Buyout) model templates in Excel for private equity transactions, deal materials, or investment committee presentations. The skill fills in formulas, validates calculations, and ensures professional formatting standards that adapt to any template structure.
name
lbo-model
description
This skill should be used when completing LBO (Leveraged Buyout) model templates in Excel for private equity transactions, deal materials, or investment committee presentations. The skill fills in formulas, validates calculations, and ensures professional formatting standards that adapt to any template structure.
TEMPLATE REQUIREMENT
This skill uses templates for LBO models. Always check for an attached template file first.
Before starting any LBO model:
If a template file is attached/provided
: Use that template's structure exactly - copy it and populate with the user's data
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."
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 FOR CLAUDE - READ FIRST
Environment: Office JS vs Python
If running inside Excel (Office Add-in / Office JS environment):
Use Office JS (
Excel.run(async (context) => {...})
) directly — do NOT use Python/openpyxl
Write formulas via
range.formulas = [["=B5B6"
), then run
recalc.py
before delivery
The rest of this skill is written with openpyxl examples, but the same principles apply to Office JS — just translate the API calls.
Core Principles
Every calculation must be an Excel formula
相关技能
B6"]]
— Office JS formulas recalculate natively in the live workbook
The same formulas-over-hardcodes rule applies: set
range.formulas
, never
range.values
for anything that should be a calculation
Use
range.format.font.color
/
range.format.fill.color
for the blue/black/purple/green convention
No separate recalc step needed — Excel handles calculation natively
Merged cell pitfall:
Do NOT call
.merge()
then set
.values
on the merged range (throws
InvalidArgument
— range still reports original dimensions). Instead: write value to top-left cell alone (
ws.getRange("A7").values = [["SOURCES & USES"]]
), then merge + format the full range (
ws.getRange("A7:F7").merge(); ws.getRange("A7:F7").format.fill.color = "#1F4E79";
)
If generating a standalone .xlsx file (no live Excel session):
Use Python/openpyxl as described below
Write formula strings (
ws["D20"] = "=B5
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:
Map the structure
Identify where each section lives and how they relate to each other. Note which sections feed into others.
Understand the timeline
Which columns represent which periods? Is there a "Closing" or "Pro Forma" column? Where does the projection period start?
Identify input vs formula cells
Templates often use color coding, borders, or shading to indicate which cells need inputs vs formulas. Respect these conventions.
Read existing labels carefully
The row labels tell you exactly what calculation is expected. Don't assume - read what the template is asking for.
Check for existing formulas
Some templates come partially filled. Don't overwrite working formulas unless specifically asked.
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 /mnt/skills/public/xlsx/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
Cash Flow (if applicable)
Starts with correct income figure
Non-cash items added/subtracted appropriately
Working capital changes have correct signs
Ending Cash = Beginning Cash + Net Cash Flow
Cash balances are consistent across statements
Supporting Schedules
Roll-forward schedules balance (Beginning + Changes = Ending)
Schedules link correctly to main statements
Calculated items use appropriate drivers
All periods are calculated consistently
Debt/Financing Schedules (if applicable)
Beginning balances tie to sources or prior period
Interest calculated on appropriate balance (typically beginning)
Paydowns respect cash availability and priority
Ending balances cannot be negative
Totals sum tranches correctly
Returns/Output Analysis
Exit/terminal values calculated correctly
All relevant adjustments included
Cash flow signs are correct (negative for investment, positive for proceeds)
IRR/MOIC formulas reference complete ranges
Results are reasonable for the scenario
Sensitivity Tables (if applicable)
Grid dimensions are ODD (5×5 or 7×7) — there is a true center cell
Row and column axis values are symmetric around the base case (
[base-2Δ, base-Δ, base, base+Δ, base+2Δ]
)
Center cell output equals the model's actual IRR/MOIC — confirms the table is wired correctly
Center cell is highlighted (medium-blue fill
#BDD7EE
, bold font)
Row and column headers contain appropriate input values
Each data cell contains a formula (not hardcoded)
Each data cell shows a DIFFERENT value
Values move in expected directions (higher exit multiple → higher IRR, etc.)
Formatting
Hardcoded inputs are blue (0000FF)
Calculated formulas are black (000000)
Same-tab links are purple (800080)
Cross-tab links are green (008000)
All numbers are right-aligned
Appropriate number formats applied throughout
No cells show error values (#REF!, #DIV/0!, #VALUE!, #NAME?)
Logical Sanity Checks
Numbers are reasonable order of magnitude
Trends make sense (growth, decline, stabilization as expected)
No obviously wrong values (negative where should be positive, impossible percentages, etc.)
Key outputs are within reasonable ranges for the type of analysis
COMMON ERRORS TO AVOID
Error
What Goes Wrong
How to Fix
Hardcoding calculated values
Model doesn't update when inputs change
Always use formulas that reference source cells
Wrong cell references after copying
Formulas point to wrong cells
Verify all links, use appropriate $ anchoring
Circular reference errors
Model can't calculate
Use beginning balances for interest-type calcs, break the circle
Sections don't balance
Totals that should match don't
Ensure one item is the plug (calculated as difference)
Negative balances where impossible
Paying/using more than available
Use MAX(0, ...) or MIN functions appropriately
IRR/return errors
Wrong signs or incomplete ranges
Check cash flow signs and ensure formula covers all periods
Sensitivity table shows same value
Formula not varying with inputs
Check cell references - need mixed references ($A5, B$4)
Roll-forwards don't tie
Beginning ≠ prior ending
Verify links between periods
Inconsistent sign conventions
Additions become subtractions or vice versa
Follow template's convention consistently throughout
WORKING WITH THE USER — SECTION-BY-SECTION CHECKPOINTS
If the template structure is unclear
, ask before proceeding
If the user's requirements conflict with the template
, confirm their preference
After completing each major section
, STOP and verify with the user before continuing:
After Sources & Uses
→ show the balanced table, confirm the plug is correct, get sign-off before building the operating model
After Operating Model / Projections
→ show the projected P&L, confirm growth rates and margins look right, get sign-off before the debt schedule
After Debt Schedule
→ show beginning/ending balances and interest, confirm the waterfall logic, get sign-off before returns
After Returns (IRR/MOIC)
→ show the cash flow series and outputs, confirm signs and ranges, get sign-off before sensitivity tables
After Sensitivity Tables
→ show that each cell varies, confirm the base case lands where expected
If errors are found during verification
, fix them before moving to the next section
Show your work
explain key formulas or assumptions when helpful
Never present a completed model without having checked in at each section
— it's faster to catch a wrong cell reference at the source than to trace it backwards from a broken IRR
This skill produces investment banking-quality LBO models by filling templates with correct formulas, proper formatting, and validated calculations. The skill adapts to any template structure while ensuring financial accuracy and professional presentation standards.