lbo-model

CRITICAL INSTRUCTIONS FOR CLAUDE - READ FIRST

Core Principles

• Every calculation must be an Excel formula - NEVER compute values in Python and hardcode results into cells. 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 - Complete one section fully before moving to the next, as later sections often depend on earlier ones.

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)

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

• Excel's DATA TABLE function may not work with openpyxl
• May need 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)
• [ ] Sch