lbo-model
The lbo-model skill automates the creation and validation of Leveraged Buyout financial models in Excel for private equity transactions. It populates formulas across sources and uses sections, enforces professional formatting with color-coded cell conventions, validates IRR and MOIC calculations through headless LibreOffice recalculation, and adapts to user-provided templates or utilizes a bundled standard structure. Use this skill when building deal models, investment committee presentations, or transaction materials requiring standardized financial modeling conventions.
git clone --depth 1 https://github.com/fivetaku/claude-office-skills /tmp/lbo-model && cp -r /tmp/lbo-model/claude-in-excel/lbo-model ~/.claude/skills/lbo-modelSKILL.md
# LBO Model Builder ## Preflight: Dependency Check Before starting, verify required libraries and tools are installed and install any that are missing. ```bash python3 -c "import openpyxl" 2>/dev/null || python3 -m pip install openpyxl command -v soffice >/dev/null 2>&1 || command -v libreoffice >/dev/null 2>&1 || ls /Applications/LibreOffice.app/Contents/MacOS/soffice >/dev/null 2>&1 || echo "WARNING: LibreOffice not found. Install: brew install --cask libreoffice (macOS) or apt install libreoffice (Linux). Required for scripts/recalc.py." ``` **Important**: Do not skip this step — `scripts/recalc.py` is required to verify IRR/MOIC, cash sweep mechanics, and sensitivity base-case. ## Scripts - `scripts/recalc.py` — Force formula recalculation via headless LibreOffice. Run after building: `python scripts/recalc.py <model.xlsx>` ## Reference Template A starter LBO template is bundled at `lbo-model.xlsx` in this skill directory. If the user does not attach their own template, reuse the bundled file structure (do not copy cell values, only the layout/section conventions). ## TEMPLATE REQUIREMENT - If template attached → use that template's structure exactly - If no template → ask user, or use standard examples/LBO_Model.xlsx - NEVER build from scratch when template is provided ## CRITICAL INSTRUCTIONS - READ FIRST ### Environment: Office JS vs Python - Office JS: range.formulas = [["=B5*B6"]], no recalc needed - Python/openpyxl: ws["D20"] = "=B5*B6", run recalc.py before delivery - Merged cell pitfall: value to top-left first, then merge + format ### Core Principles - Every calculation must be an Excel formula — NEVER compute in Python and hardcode - Use the template structure — follow existing organization - Work section by section, verify with user at each step ### Formula Color Conventions (4-color system) - Blue (0000FF): Hardcoded inputs - Black (000000): Formulas with calculations - Purple (800080): Links to cells on SAME tab (direct references, no calculation) - Green (008000): Links to cells on DIFFERENT tabs ### Fill Color Palette — Professional Blues & Greys - Section headers: Dark blue #1F4E79 (white bold text) - Column headers: Light blue #D9E1F2 (black bold text) - Input cells: Light grey #F2F2F2 - Formula cells: White - Key outputs (IRR, MOIC): Medium blue #BDD7EE (black bold) ### Number Formatting Standards - Currency: $#,##0;($#,##0);"-" - Percentages: 0.0% - Multiples: 0.0"x" - MOIC/Detailed Ratios: 0.00"x" ## TEMPLATE ANALYSIS PHASE 1. Map the structure — identify sections and relationships 2. Understand the timeline — columns = periods, pro forma column 3. Identify input vs formula cells — respect template conventions 4. Read existing labels — they specify expected calculations 5. Check for existing formulas — don't overwrite working formulas 6. Note template-specific conventions — sign conventions, subtotal structures ## FILLING FORMULAS - GENERAL APPROACH ### Step 1: Check the Template (existing formula? comment? label? pattern?) ### Step 2: Check the User's Instructions ### Step 3: Apply Standard Practice ## COMMON PROBLEM AREAS - Balancing Sections: Sources = Uses, one item is "plug" - Tax Calculations: reference income line + tax rate only - Interest and Circular References: use Beginning Balance to break circularity - Debt Paydown / Cash Sweeps: priority waterfall, MAX/MIN to prevent negative - Returns Calculations (IRR/MOIC): correct signs, consecutive periods - Sensitivity Tables: ODD dimensions, center cell = base case, #BDD7EE highlight ## VERIFICATION CHECKLIST - [ ] Section balancing (Sources = Uses) - [ ] Income projections (subtotals, margins, links) - [ ] Balance Sheet (Assets = L+E, beginning = prior ending) - [ ] Cash Flow (correct signs, ending cash ties) - [ ] Supporting Schedules (roll-forwards balance) - [ ] Debt Schedule (beginning balance, interest, paydown priority) - [ ] Returns (IRR/MOIC correct signs and ranges) - [ ] Sensitivity Tables (ODD grid, center = base, all cells have formulas) - [ ] Formatting (blue/black/purple/green, number formats, no errors) - [ ] Logical Sanity Checks (magnitude, trends, reasonableness) ## COMMON ERRORS TO AVOID | Hardcoding | Wrong cell refs | Circular refs | Sections unbalanced | | Negative balances | IRR/return errors | Sensitivity same value | Roll-forwards don't tie | | Inconsistent signs | ## WORKING WITH THE USER — SECTION-BY-SECTION CHECKPOINTS 1. After Sources & Uses → confirm plug, get sign-off 2. After Operating Model → confirm growth rates, margins 3. After Debt Schedule → confirm waterfall logic 4. After Returns (IRR/MOIC) → confirm cash flow signs 5. After Sensitivity Tables → confirm base case lands correctly
Complete, populate and fill out 3-statement financial model templates (Income Statement, Balance Sheet, Cash Flow Statement). Use when asked to fill out model templates, complete existing model frameworks, populate financial models with data, complete a partially filled IS/BS/CF framework, or link integrated financial statements within an existing template structure. Triggers include requests to fill in, complete, or populate a 3-statement model template
Audit a spreadsheet for formula accuracy, errors, and common mistakes. Scopes to a selected range, a single sheet, or the entire model, including financial-model integrity checks like BS balance, cash tie-out, and logic sanity. Triggers on "audit this sheet", "check my formulas", "find formula errors", "QA this spreadsheet", "sanity check this", "debug model", "model check", "model won't balance", "something's off in my model", and "model review".
Clean up messy spreadsheet data — trim whitespace, fix inconsistent casing, convert numbers-stored-as-text, standardize dates, remove duplicates, and flag mixed-type columns. Use when data is messy, inconsistent, or needs prep before analysis. Triggers on "clean this data", "clean up this sheet", "normalize this data", "fix formatting", "dedupe", "standardize this column", and "this data is messy".
Build institutional-grade comparable company analyses with operating metrics, valuation multiples, and statistical benchmarking in Excel/spreadsheet format.
Real DCF (Discounted Cash Flow) model creation for equity valuation. Retrieves financial data from SEC filings and analyst reports, builds comprehensive cash flow projections with proper WACC calculations, performs sensitivity analysis, and outputs professional Excel models with executive summaries. Use when users need to value a company using DCF methodology, request intrinsic value analysis, or ask for detailed financial modeling with growth projections and terminal value calculations.
Framework for building competitive landscape decks — market positioning, competitor deep-dives, comparative analysis, strategic synthesis. Use when the user asks for a competitive landscape, competitor analysis, peer comparison, market positioning assessment, strategic review, or investment memo deck. Also triggers on "who are the competitors to X", "benchmark X against peers", "build a market map", or any request to systematically evaluate competitive dynamics across an industry.
Updates a presentation with new numbers — quarterly refreshes, earnings updates, comp rolls, rebased market data. Use whenever the user asks to "update the deck with Q4 numbers", "refresh the comps", "roll this forward", "swap in the new earnings", "change all the $485M to $512M", or any request to swap figures across an existing deck without rebuilding it.
Investment banking presentation quality checker. Reviews a pitch deck or client-ready presentation for (1) number consistency across slides, (2) data-narrative alignment, (3) language polish against IB standards, (4) visual and formatting QC. Use whenever the user asks to review, check, QC, proof, or do a final pass on a deck, pitch, or client materials — including requests like "check my numbers", "reconcile figures across slides", "is this client-ready", or "what am I missing before I send this out".