Investment Banking Excel Professional Skill

This is an institutional-quality research and investment skill.

When to Use This Skill

• Building DCF, LBO, or M&A models
• Three-statement model integration
• M&A accretion/dilution analysis
• Trading comps and precedent transactions
• Creating sensitivity analysis (1-way/2-way data tables)
• Monte Carlo simulations for valuation ranges
• Tornado charts for sensitivity visualization
• Waterfall charts (EV-to-Equity bridges)
• Football field valuation summaries
• Professional financial tables with IB formatting standards
• Complex formula linkages across multiple sheets
• Handling intentional circular references (LBO debt/interest)
• Auditing models for errors, broken references, and circular dependencies
• Model integrity checks (balance sheet, cash flow reconciliation)

Prerequisites

• Use in conjunction with the base /mnt/skills/public/xlsx/SKILL.md for core Excel operations
• Always run recalc.py after creating formulas

PART 1: COMPLETE IB FUNCTIONS REFERENCE

Formula Frequency in Professional IB Models

1. FINANCIAL FUNCTIONS (Core Valuation)

Time Value of Money - CRITICAL

Debt & Loan Functions

Fixed Income / Bond Functions

Depreciation

2. LOOKUP FUNCTIONS

INDEX/MATCH - The IB Standard

Always use INDEX/MATCH over VLOOKUP - bidirectional, immune to column insertions.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Modern Lookups (Excel 365+ Only)

WARNING: Use only internally - breaks on older Excel versions

# Compatibility check before using XLOOKUP in deliverables
# If model will be shared externally → Use INDEX/MATCH
# If internal use only on Excel 365 → XLOOKUP is fine

3. MATH & AGGREGATION FUNCTIONS

The Power Functions

SUMPRODUCT Patterns (Essential)

# Weighted average (e.g., WACC)
=SUMPRODUCT(weights, values)/SUM(weights)

# Conditional sum without SUMIFS
=SUMPRODUCT((criteria_range="X")*(sum_range))

# Multiple criteria
=SUMPRODUCT((range1="A")*(range2>100)*(values))

4. STATISTICAL FUNCTIONS

Descriptive Statistics

Regression & Correlation (CAPM, Beta)

Monte Carlo Functions

5. LOGICAL & CONDITIONAL FUNCTIONS

6. DATE & TIME FUNCTIONS

7. TEXT FUNCTIONS

8. MODERN EXCEL FUNCTIONS (Excel 365)

Dynamic Arrays

Use internally only - compatibility issues with older Excel

Advanced Functions

LET Example (Improved Readability)

# Without LET - hard to read
=IF(B5/B6>0.5, B5/B6*1.1, B5/B6*0.9)

# With LET - clear and efficient
=LET(
    ratio, B5/B6,
    IF(ratio>0.5, ratio*1.1, ratio*0.9)
)

PART 2: IB MODELING USE CASES

DCF Valuation Model

Unlevered Free Cash Flow Formula

UFCF = EBIT × (1 - Tax Rate) + D&A - ΔWorking Capital - CapEx

Excel Implementation

# UFCF calculation
ws['B15'] = '=B10*(1-$B$5)+B11-B12-B13'  # EBIT*(1-Tax)+D&A-ΔNWC-CapEx

# Terminal Value - Perpetuity Growth (Gordon Growth)
ws['G20'] = '=G15*(1+$B$7)/($B$6-$B$7)'  # FCF*(1+g)/(WACC-g)

# Terminal Value - Exit Multiple
ws['G21'] = '=G10*$B$8'  # EBITDA * Exit Multiple

# Present Value with XNPV (PREFERRED)
ws['B25'] = '=XNPV($B$6, B15:G15, B2:G2)'  # Discount FCFs at WACC

# Enterprise Value
ws['B30'] = '=B25+G22/(1+$B$6)^5'  # PV of FCFs + PV of Terminal Value

WACC Calculation

=([Equity/EV]*[Cost_of_Equity])+([Debt/EV]*[Cost_of_Debt]*(1-[Tax_Rate]))

# Cost of Equity (CAPM)
=Risk_Free_Rate + Beta*(Market_Return - Risk_Free_Rate)

# Beta calculation using SLOPE
=SLOPE(stock_returns, market_returns)

LBO Model - Circular Reference Handling

The Circularity Problem

Interest expense → Net income → Cash flow → Debt repayment → Average debt → Interest expense

Solution 1: Enable Iterative Calculations

File → Options → Formulas → Enable iterative calculation
- Maximum iterations: 100
- Maximum change: 0.001

Solution 2: Circuit Breaker Toggle (CRITICAL)

# Create a toggle cell (e.g., $B$1 named "CircuitBreaker")
# 0 = OFF (breaks circularity for debugging)
# 1 = ON (enables circularity)

# Interest expense formula with circuit breaker
ws['B20'] = '=IF($B$1=1, (B18+C18)/2*$B$5, B18*$B$5)'
# When ON: Average debt * interest rate
# When OFF: Beginning debt * interest rate (no circularity)

LBO Returns Formulas

# MOIC (Multiple on Invested Capital)
ws['B30'] = '=B25/B10'  # Exit Equity / Initial Equity

# IRR using XIRR (with dates)
ws['B31'] = '=XIRR(B35:G35, B36:G36)'  # Cash flows with dates

# Quick IRR approximation from MOIC
# 2.0x over 5 years ≈ 15% IRR
# 2.5x over 5 years ≈ 20% IRR
# 3.0x over 5 years ≈ 25% IRR

M&A Accretion/Dilution Analysis

Pro Forma EPS Calculation

# Combined Net Income
ws['B10'] = '=Acquirer_NI + Target_NI + Synergies - Lost_Interest_Income - New_Interest_Expense'

# Pro Forma Shares Outstanding
ws['B11'] = '=Acquirer_Shares + New_Shares_Issued'  # For stock deals

# Pro Forma EPS
ws['B12'] = '=B10/B11'

# Accretion/Dilution %
ws['B15'] = '=B12/Standalone_Acquirer_EPS - 1'
# Positive = Accretive, Negative = Dilutive

Deal Structure Scenarios with CHOOSE

# Consideration mix scenarios
# 1 = All Cash, 2 = All Stock, 3 = 50/50 Mix
ws['B5'] = '=CHOOSE($B$1, Cash_Consideration, Stock_Consideration, Mixed_Consideration)'

Purchase Price Allocation

# Goodwill
ws['B20'] = '=Purchase_Price - Fair_Value_Net_Assets'

Trading Comps / Comparable Company Analysis

Enterprise Value Calculation

EV = Market Cap + Total Debt + Preferred Stock + Minority Interest - Cash

Valuation Multiples

# EV/EBITDA
ws['B15'] = '=B10/B5'  # Enterprise Value / EBITDA

# EV/Revenue
ws['B16'] = '=B10/B4'  # Enterprise Value / Revenue

# P/E Ratio
ws['B17'] = '=B8/B6'  # Share Price / EPS

# Use MEDIAN for peer summary (reduces outlier impact)
ws['B25'] = '=MEDIAN(B15:B22)'  # Median EV/EBITDA of peer group

# Percentile ranges for valuation
ws['B26'] = '=PERCENTILE.INC(B15:B22, 0.25)'  # 25th percentile
ws['B27'] = '=PERCENTILE.INC(B15:B22, 0.75)'  # 75th percentile

# Implied valuation
ws['B30'] = '=Target_EBITDA * B25'  # Target EBITDA × Peer Median Multiple

Precedent Transactions

Control Premium Calculation

# Control Premium
ws['B10'] = '=Offer_Price/Unaffected_Price - 1'

# Unaffected price periods
ws['B11'] = '=Offer_Price/Price_1Day_Prior - 1'   # 1-day premium
ws['B12'] = '=Offer_Price/Price_1Week_Prior - 1'  # 1-week premium
ws['B13'] = '=Offer_Price/Price_1Month_Prior - 1' # 1-month premium

Three-Statement Model Integration

Working Capital Days Formulas

# Accounts Receivable
ws['B20'] = '=Revenue/365*DSO_Days'  # Revenue ÷ 365 × DSO

# Inventory
ws['B21'] = '=COGS/365*DIO_Days'  # COGS ÷ 365 × DIO

# Accounts Payable
ws['B22'] = '=COGS/365*DPO_Days'  # COGS ÷ 365 × DPO

# Net Working Capital
ws['B23'] = '=B20+B21-B22'  # AR + Inventory - AP

# Change in NWC (for cash flow)
ws['B24'] = '=B23-A23'  # Current NWC - Prior NWC

PP&E Roll-Forward

Beginning PP&E + CapEx - Depreciation = Ending PP&E

Cash Flow Statement Links

# Operating Activities
ws['B30'] = '=Net_Income'              # Start with NI
ws['B31'] = '=Depreciation'            # Add back D&A (non-cash)
ws['B32'] = '=-Change_in_NWC'          # Subtract increase in NWC

# Investing Activities
ws['B35'] = '=-CapEx'                  # Capital expenditures (negative)

# Financing Activities
ws['B40'] = '=Debt_Issuance'
ws['B41'] = '=-Debt_Repayment'
ws['B42'] = '=-Dividends'

# Ending Cash = Beginning Cash + Sum of all sections
ws['B50'] = '=A50+SUM(B30:B32)+B35+SUM(B40:B42)'

PART 3: MODEL INTEGRITY CHECKS

Dedicated Checks Tab Structure

def create_model_checks_tab(ws):
    """
    Creates a dedicated checks tab with integrity validations.
    All checks should show "✓" when passing.
    """
    
    checks = [
        ("Balance Sheet Balances", "=ABS('Balance Sheet'!Assets-'Balance Sheet'!Liabilities-'Balance Sheet'!Equity)<0.01"),
        ("Cash Ties to CFS", "=ABS('Balance Sheet'!Cash-'Cash Flow'!Ending_Cash)<0.01"),
        ("Retained Earnings Ties", "=ABS(RE_Current-(RE_Prior+Net_Income-Dividends))<0.01"),
        ("Debt Schedule Ties to BS", "=ABS('Debt Schedule'!Total_Debt-'Balance Sheet'!Total_Debt)<0.01"),
        ("Sources = Uses (LBO)", "=ABS(Total_Sources-Total_Uses)<0.01"),
        ("No Circular Ref Errors", "=ISERROR(Interest_Expense)=FALSE"),
    ]
    
    ws['A1'] = "MODEL INTEGRITY CHECKS"
    ws['A1'].font = Font(bold=True, size=14)
    
    headers = ["Check Description", "Formula Result", "Status"]
    for i, h in enumerate(headers):
        ws.cell(row=3, column=i+1, value=h).font = Font(bold=True)
    
    for i, (description, formula) in enumerate(checks):
        row = 4 + i
        ws.cell(row=row, column=1, value=description)
        ws.cell(row=row, column=2, value=formula)
        ws.cell(row=row, column=3, value=f'=IF(B{row},"✓","✗")')
    
    # Master check
    last_row = 4 + len(checks)
    ws.cell(row=last_row+2, column=1, value="MASTER CHECK")
    ws.cell(row=last_row+2, column=1).font = Font(bold=True)
    ws.cell(row=last_row+2, column=3, value=f'=IF(COUNTIF(C4:C{last_row},"✗")=0,"ALL PASS","REVIEW REQUIRED")')

Balance Sheet Check Formula

=IF(ABS(Total_Assets - Total_Liabilities - Total_Equity) < 0.01, "✓", "✗")

Common Imbalance Sources

1. Retained earnings not linked to net income
2. Cash not matching cash flow statement ending balance
3. Depreciation mismatch between statements
4. Debt schedule not tied to balance sheet

PART 4: ERROR DETECTION & REPAIR

Excel Error Types Reference

Automated Error Scanner

from openpyxl import load_workbook
from collections import defaultdict
import json

def scan_workbook_for_errors(filepath):
    """
    Comprehensive workbook scanner for all error types.
    """
    ERROR_PATTERNS = {
        '#REF!': 'Broken cell reference',
        '#DIV/0!': 'Division by zero',
        '#VALUE!': 'Wrong data type',
        '#NAME?': 'Unrecognized function/name',
        '#N/A': 'Lookup value not found',
        '#NUM!': 'Invalid numeric result',
        '#NULL!': 'Invalid range intersection',
        '#CALC!': 'Calculation engine error',
    }
    
    wb = load_workbook(filepath, data_only=True)
    errors = defaultdict(list)
    
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        for row in ws.iter_rows():
            for cell in row:
                if cell.value in ERROR_PATTERNS:
                    errors[cell.value].append(f"{sheet_name}!{cell.coordinate}")
    
    wb.close()
    
    return {
        'total_errors': sum(len(v) for v in errors.values()),
        'error_details': dict(errors)
    }


def find_circular_references(filepath):
    """
    Detects circular references by building dependency graph.
    """
    from openpyxl import load_workbook
    import re
    
    wb = load_workbook(filepath, data_only=False)
    dependencies = defaultdict(set)
    
    cell_ref_pattern = r"(?:'?([^'!]+)'?!)?\$?([A-Z]+)\$?(\d+)"
    
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        for row in ws.iter_rows():
            for cell in row:
                if cell.value and str(cell.value).startswith('='):
                    formula = str(cell.value)
                    current_cell = f"{sheet_name}!{cell.coordinate}"
                    
                    for match in re.finditer(cell_ref_pattern, formula):
                        ref_sheet = match.group(1) or sheet_name
                        ref_col, ref_row = match.group(2), match.group(3)
                        ref_cell = f"{ref_sheet}!{ref_col}{ref_row}"
                        
                        if ref_cell != current_cell:
                            dependencies[current_cell].add(ref_cell)
    
    wb.close()
    
    # Detect cycles using DFS
    def find_cycle(start, current, visited, path):
        if current in path:
            return path[path.index(current):] + [current]
        if current in visited:
            return None
        visited.add(current)
        path.append(current)
        for neighbor in dependencies.get(current, []):
            cycle = find_cycle(start, neighbor, visited, path)
            if cycle:
                return cycle
        path.pop()
        return None
    
    circular_refs = []
    all_visited = set()
    
    for cell in dependencies:
        if cell not in all_visited:
            cycle = find_cycle(cell, cell, set(), [])
            if cycle:
                circular_refs.append(cycle)
            all_visited.update(set())
    
    return {
        'has_circular_refs': len(circular_refs) > 0,
        'circular_chains': circular_refs
    }


def add_error_protection(filepath, output_path=None):
    """
    Wraps division formulas with IFERROR and lookups with IFNA.
    """
    wb = load_workbook(filepath)
    output_path = output_path or filepath
    fixes = []
    
    lookup_functions = ['VLOOKUP', 'HLOOKUP', 'XLOOKUP', 'MATCH', 'INDEX']
    
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        for row in ws.iter_rows():
            for cell in row:
                if cell.value and str(cell.value).startswith('='):
                    formula = str(cell.value)
                    formula_upper = formula.upper()
                    
                    if 'IFERROR' in formula_upper or 'IFNA' in formula_upper:
                        continue
                    
                    has_lookup = any(f in formula_upper for f in lookup_functions)
                    has_division = '/' in formula
                    
                    if has_lookup:
                        cell.value = f'=IFNA({formula[1:]},"—")'
                        fixes.append(f"{sheet_name}!{cell.coordinate}")
                    elif has_division:
                        cell.value = f'=IFERROR({formula[1:]},0)'
                        fixes.append(f"{sheet_name}!{cell.coordinate}")
    
    wb.save(output_path)
    wb.close()
    
    return {'fixes_made': len(fixes), 'cells_fixed': fixes}

Safe Formula Patterns

# Safe division
=IFERROR(A1/B1, 0)
=IF(B1=0, 0, A1/B1)

# Safe lookup
=IFNA(VLOOKUP(A1, range, 2, 0), "Not Found")
=IFERROR(INDEX(range, MATCH(A1, lookup, 0)), "N/A")

# Display "NM" for not meaningful
=IFERROR(Revenue/EBITDA, "NM")

PART 5: SENSITIVITY ANALYSIS

Two-Way Data Table Structure

def create_two_way_sensitivity(ws, start_row, start_col, row_input, col_input, 
                                output_cell, row_values, col_values, title):
    """
    WACC vs Terminal Growth sensitivity table.
    """
    from openpyxl.styles import Font, PatternFill, Border, Side
    from openpyxl.utils import get_column_letter
    
    thin = Border(left=Side(style='thin'), right=Side(style='thin'),
                  top=Side(style='thin'), bottom=Side(style='thin'))
    header_fill = PatternFill(start_color="1F4E79", fill_type="solid")
    
    # Title
    ws.cell(row=start_row, column=start_col, value=title).font = Font(bold=True, size=12)
    ws.cell(row=start_row+1, column=start_col, value="WACC ↓ / Terminal Growth →").font = Font(italic=True, size=9)
    
    # Top-left corner: link to output
    corner = ws.cell(row=start_row+2, column=start_col, value=f"={output_cell}")
    corner.fill = header_fill
    corner.font = Font(bold=True, color="FFFFFF")
    
    # Column headers
    for j, val in enumerate(col_values):
        cell = ws.cell(row=start_row+2, column=start_col+1+j, value=val)
        cell.fill = header_fill
        cell.font = Font(bold=True, color="FFFFFF")
        cell.number_format = '0.0%'
    
    # Row headers and result cells
    for i, row_val in enumerate(row_values):
        ws.cell(row=start_row+3+i, column=start_col, value=row_val).font = Font(color="0000FF")
        ws.cell(row=start_row+3+i, column=start_col).number_format = '0.0%'
        
        for j in range(len(col_values)):
            ws.cell(row=start_row+3+i, column=start_col+1+j).number_format = '$#,##0.00'

Performance Optimization for Data Tables

# Data tables are memory-intensive
# Change calculation mode:
File → Options → Formulas → "Automatic Except for Data Tables"

# Press F9 to manually recalculate when needed

PART 6: FORMATTING STANDARDS

Color Coding (Universal IB Standard)

from openpyxl.styles import Font, PatternFill

# Text Colors
BLUE_INPUT = Font(color="0000FF")      # Hardcoded inputs/assumptions
BLACK_FORMULA = Font(color="000000")   # All formulas and calculations
GREEN_LINK = Font(color="008000")      # Links to other sheets in workbook
RED_EXTERNAL = Font(color="FF0000")    # Links to external files

# Background Colors
YELLOW_INPUT = PatternFill(start_color="FFFF00", fill_type="solid")   # Input cells
HEADER_BLUE = PatternFill(start_color="1F4E79", fill_type="solid")    # Headers
GRAY_ALT = PatternFill(start_color="D6DCE4", fill_type="solid")       # Alternating rows

Number Formats

NUMBER_FORMATS = {
    'currency': '$#,##0;($#,##0);"-"',        # Parentheses for negatives
    'currency_mm': '$#,##0,,"mm";($#,##0,,"mm");"-"',
    'percent': '0.0%;(0.0%);"-"',             # One decimal
    'multiple': '0.0x;(0.0x);"-"',            # Valuation multiples
    'number': '#,##0;(#,##0);"-"',            # General numbers
    'year': '0',                               # No commas for years
}

Font & Layout Rules

• One font only: Arial or Calibri, 10-11pt
• Bold: Titles and total rows only
• Alignment: Right-align numbers, left-align text
• Indent: Subordinate line items
• Never hide rows: Use grouping instead (Alt+Shift+→)

PART 7: KEYBOARD SHORTCUTS

Essential IB Shortcuts

Navigation & Selection

Formatting & Editing

Data Tables & Analysis

PART 8: WORKFLOW CHECKLIST

Before Building

• Define all assumptions in dedicated Assumptions tab
• Plan sheet structure: Cover → Assumptions → Schedules → Statements → Valuation → Output
• Establish naming conventions
• Set up circuit breaker for circular references if needed

During Build

• Use formulas, NEVER hardcode calculations
• Apply color coding consistently (blue inputs, black formulas)
• Link everything to assumptions
• Wrap divisions with IFERROR
• Wrap lookups with IFNA
• Use MEDIAN not AVERAGE for comps

After Build

• Run recalc.py to calculate all formulas
• Run error scan - fix any #REF!, #DIV/0!, etc.
• Verify no unintended circular references
• Check all model integrity tests pass
• Validate balance sheet balances
• Verify cash ties to cash flow statement
• Test sensitivity tables work correctly
• Review formulas with Ctrl+` (formula view)

Before Delivery

• Remove any modern functions (XLOOKUP, FILTER) if sharing externally
• Test on target Excel version
• Protect input cells if needed
• Add documentation/instructions tab