← Files Investment BankingARCHIVED FILE
skills/lbo-model-build/scripts/runtime/lbo_core
64.1 KB · Oct 2, 2026 · 00:27 UTC
"""Core LBO modeling utilities.
Design goals:
- Deterministic (no external data, no network)
- Standard-library only
- Produces a "long" table representing statements + schedules across scenarios
This is a reusable engine; keep UI/output rendering in run_pipeline.py.
"""
from __future__ import annotations
import csv
import json
import math
import zipfile
from dataclasses import dataclass
from pathlib import Path
from typing import Any, Dict, List, Optional, Tuple
from xml.sax.saxutils import escape
ALLOWED_PERIODICITIES = {"annual", "quarterly"}
def deep_merge(base: Any, override: Any) -> Any:
"""Recursively merge `override` into `base`.
- dict: merge keys recursively
- list: override replaces base (lists are treated as atomic)
- other: override wins unless it is None
"""
if override is None:
return base
if isinstance(base, dict) and isinstance(override, dict):
out = dict(base)
for k, v in override.items():
out[k] = deep_merge(out.get(k), v)
return out
if isinstance(override, list):
return override
return override
def safe_get_year_map(d: Dict[str, Any], year: int, default: float) -> float:
"""Get a value for `year` from a {"YYYY": value} map, with fallback to last known."""
if not d:
return default
# Exact
key = str(year)
if key in d and d[key] is not None:
return float(d[key])
# Fallback to the most recent prior year
keys = sorted([int(k) for k in d.keys() if str(k).isdigit()])
prior = [k for k in keys if k <= year]
if prior:
return float(d[str(prior[-1])])
return float(d[str(keys[0])])
def sum_amounts(value: Any) -> float:
"""Return a numeric total from either a scalar or a list/dict of amount items."""
if value is None:
return 0.0
if isinstance(value, (int, float)):
return float(value)
if isinstance(value, dict):
if "amount" in value:
return float(value.get("amount") or 0.0)
return sum(sum_amounts(v) for v in value.values())
if isinstance(value, list):
total = 0.0
for item in value:
if isinstance(item, dict):
total += float(item.get("amount", 0.0) or 0.0)
elif isinstance(item, (int, float)):
total += float(item)
return total
return 0.0
@dataclass
class Period:
index: int
label: str
year: int
frequency: str
time_factor: float # fraction of year
def build_timeline(start_year: int, horizon_years: int, periodicity: str) -> List[Period]:
if periodicity not in ALLOWED_PERIODICITIES:
raise ValueError(f"Unsupported periodicity: {periodicity}")
periods: List[Period] = []
if periodicity == "annual":
for i in range(horizon_years):
y = start_year + i
periods.append(Period(i, f"FY{y}", y, periodicity, 1.0))
else:
# quarterly
total = horizon_years * 4
for i in range(total):
y = start_year + (i // 4)
q = (i % 4) + 1
periods.append(Period(i, f"Q{q}-{y}", y, periodicity, 0.25))
return periods
def load_profile(skill_root: Path, kind: str, name: str) -> Dict[str, Any]:
"""Load assets/<kind>_profiles/<name>.json if it exists."""
candidates = [
skill_root / "assets" / f"{kind}_profiles" / f"{name}.json",
skill_root / "assets" / "deep" / f"{kind}_profiles" / name,
]
for p in candidates:
if p.exists():
return json.loads(p.read_text())
return {}
def normalize_plan(plan: Dict[str, Any], skill_root: Path) -> Tuple[Dict[str, Any], Dict[str, Any]]:
"""Apply defaults and enforce periodicity rule. Returns (normalized_plan, run_log)."""
run_log: Dict[str, Any] = {
"model_status": "screen-grade",
"workbook_mode": "deterministic_export",
"source_basis": [],
"hard_failures": [],
"warnings": [],
"info": [],
"assumptions": {},
"checks": {},
"p0_handoff": {},
}
meta = plan.get("meta", {})
industry = meta.get("industry", "")
stage = meta.get("stage", "")
# Base template defaults (very small; rely on profiles and plan for most)
base_defaults: Dict[str, Any] = {
"meta": {"currency": "USD", "units": "USD_mm"},
"transaction": {
"purchase_accounting": {
"enabled": False,
"book_net_assets": None,
"fair_value_step_up": 0.0,
"identifiable_intangibles": 0.0,
"deferred_tax_rate": 0.25,
"intangible_amortization_years": 10,
"goodwill": None,
},
"management_incentive": {
"enabled": False,
"pool_pct_fully_diluted": 0.0,
"threshold_equity_value": 0.0,
},
"public_to_private": {
"enabled": False,
"offer_price": None,
"fully_diluted_shares": None,
"unaffected_share_price": None,
},
"carve_out": {
"enabled": False,
"standalone_costs": {},
"tsa_costs": {},
"stranded_costs": {},
"separation_capex": {},
"one_time_separation_costs": {},
"working_capital_peg": None,
},
},
"operating": {
"tax_rate": 0.25,
"da": {"model": "pct_revenue", "pct": 0.03},
"capex": {"model": "pct_revenue", "pct": 0.04},
"working_capital": {"model": "days", "ar_days": 45, "ap_days": 30, "include_inventory": False},
"dividends": {"enabled": False, "amounts": {}},
"one_time_cash_costs": {},
},
"balance_sheet": {
"opening_nwc": None,
"opening_ppe": None,
"opening_other_assets": 0.0,
"opening_other_liabilities": 0.0,
"opening_retained_earnings": 0.0,
"lease_liabilities": 0.0,
},
"debt": {"base_rate": {"name": "SOFR", "assumption": 0.04}, "tranches": [], "cash_sweep": {"enabled": True, "min_cash": None, "percentage": 1.0}},
"covenants": {"test_frequency": "quarterly", "definitions": {"cash_in_net_debt": True, "ebitda_source": "model"}, "tests": {}},
"sensitivities": {"target_irr": 0.20},
}
ind_profile = load_profile(skill_root, "industry", industry) if industry else {}
stg_profile = load_profile(skill_root, "stage", stage) if stage else {}
merged = deep_merge(base_defaults, ind_profile)
merged = deep_merge(merged, stg_profile)
merged = deep_merge(merged, plan)
run_log["source_basis"] = list(merged.get("source_basis", []) or [])
# Periodicity rule
tl = merged.get("timeline", {})
horizon_years = int(tl.get("horizon_years", 5))
periodicity = tl.get("periodicity")
periodicity_defaulted = False
if periodicity is None:
periodicity_defaulted = True
periodicity = "annual" if horizon_years > 3 else "quarterly"
run_log["assumptions"]["periodicity"] = {
"value": periodicity,
"reason": "Defaulted because periodicity was not provided; rule: annual if horizon_years>3 else quarterly.",
}
elif periodicity not in ALLOWED_PERIODICITIES:
periodicity_defaulted = True
run_log["warnings"].append({"code": "INVALID_PERIODICITY", "message": f"Unsupported periodicity '{periodicity}'. Defaulting by horizon."})
periodicity = "annual" if horizon_years > 3 else "quarterly"
merged.setdefault("timeline", {})["periodicity"] = periodicity
if periodicity == "quarterly":
run_log["info"].append(
{
"code": "QUARTERLY_ANNUALIZATION_BASIS",
"message": "Quarterly leverage, coverage and exit-multiple calculations use annualized run-rate values until four modeled quarters exist, then trailing-four-quarter values.",
}
)
merged["timeline"]["horizon_years"] = horizon_years
# Ensure min_cash present
tx = merged.setdefault("transaction", {})
if tx.get("min_cash") is None:
tx["min_cash"] = float(tx.get("uses", {}).get("min_cash_funding", 0.0) or 0.0)
if tx.get("min_cash") is None:
tx["min_cash"] = 0.0
if periodicity_defaulted:
run_log["info"].append({"code": "PERIODICITY_DEFAULTED", "message": f"Using periodicity='{periodicity}'."})
return merged, run_log
def compute_entry_values(plan: Dict[str, Any]) -> Dict[str, float]:
tx = plan["transaction"]
entry = tx.get("entry", {})
method = entry.get("method")
if method == "multiple":
entry_ev = float(entry["entry_multiple"]) * float(entry["entry_ebitda"])
equity_purchase = None
elif method == "ev":
entry_ev = float(entry["entry_ev"])
equity_purchase = None
elif method == "public_take_private":
p2p = tx.get("public_to_private", {})
offer_price = float(p2p.get("offer_price") or entry.get("offer_price"))
diluted_shares = float(p2p.get("fully_diluted_shares") or entry.get("fully_diluted_shares"))
equity_purchase = offer_price * diluted_shares
assumed_cash = float(tx.get("assumed_cash", 0.0))
assumed_debt = float(tx.get("assumed_debt", 0.0))
debt_like = sum_amounts(tx.get("debt_like_items", 0.0))
wc_trueup = float(tx.get("working_capital_trueup", 0.0))
entry_ev = equity_purchase + assumed_debt + debt_like - assumed_cash - wc_trueup
else:
raise ValueError("transaction.entry.method must be 'multiple', 'ev', or 'public_take_private'")
assumed_cash = float(tx.get("assumed_cash", 0.0))
assumed_debt = float(tx.get("assumed_debt", 0.0))
debt_like = sum_amounts(tx.get("debt_like_items", 0.0))
wc_trueup = float(tx.get("working_capital_trueup", 0.0))
if equity_purchase is None:
equity_purchase = entry_ev - (assumed_debt - assumed_cash) - debt_like + wc_trueup
uses = tx.get("uses", {})
transaction_fees = sum_amounts(uses.get("transaction_fees", 0.0))
financing_fees = sum_amounts(uses.get("financing_fees", 0.0))
oid = sum_amounts(uses.get("oid", 0.0))
refi = sum_amounts(uses.get("refi_existing_debt", 0.0))
management_cashout = sum_amounts(uses.get("management_option_cashout", 0.0))
change_of_control = sum_amounts(uses.get("change_of_control_payments", 0.0))
escrow = sum_amounts(uses.get("escrow_funding", 0.0))
funded_separation = sum_amounts(uses.get("funded_separation_reserve", 0.0))
other_uses = sum_amounts(uses.get("other_uses", 0.0))
min_cash = float(tx.get("min_cash", 0.0))
total_uses = (
equity_purchase
+ transaction_fees
+ financing_fees
+ oid
+ refi
+ min_cash
+ management_cashout
+ change_of_control
+ escrow
+ funded_separation
+ other_uses
)
rollover = float(tx.get("equity", {}).get("rollover_equity", 0.0) or 0.0)
# New debt sources (face amounts, excluding revolver day-1 draw)
debt_sources = 0.0
for tr in plan.get("debt", {}).get("tranches", []):
ttype = tr.get("type")
if ttype == "revolver":
continue
debt_sources += float(tr.get("face", 0.0) or 0.0)
sponsor_equity = tx.get("equity", {}).get("sponsor_equity")
if sponsor_equity is None:
sponsor_equity = total_uses - debt_sources - rollover
sponsor_equity = float(sponsor_equity)
total_sources = debt_sources + rollover + sponsor_equity
return {
"entry_ev": entry_ev,
"equity_purchase": equity_purchase,
"total_uses": total_uses,
"transaction_fees": transaction_fees,
"financing_fees": financing_fees,
"oid": oid,
"refi_existing_debt": refi,
"management_option_cashout": management_cashout,
"change_of_control_payments": change_of_control,
"escrow_funding": escrow,
"funded_separation_reserve": funded_separation,
"other_uses": other_uses,
"assumed_cash": assumed_cash,
"assumed_debt": assumed_debt,
"debt_like_items": debt_like,
"working_capital_trueup": wc_trueup,
"debt_sources": debt_sources,
"rollover_equity": rollover,
"sponsor_equity": sponsor_equity,
"sources_minus_uses": total_sources - total_uses,
"opening_cash": min_cash,
}
def revenue_by_year(plan: Dict[str, Any], start_year: int, horizon_years: int) -> Dict[int, float]:
rev = plan["operating"]["revenue"]
model = rev.get("model")
out: Dict[int, float] = {}
if model == "growth":
base_revenue = float(rev["base_revenue"])
growth_rates = rev.get("growth_rates", {})
out[start_year] = base_revenue
for i in range(1, horizon_years):
y = start_year + i
g = safe_get_year_map(growth_rates, y, 0.0)
out[y] = out[y - 1] * (1.0 + g)
return out
if model == "volume_price":
units0 = float(rev["base_units"])
price0 = float(rev["base_price"])
gu = rev.get("unit_growth_rates", {})
gp = rev.get("price_growth_rates", {})
units = {start_year: units0}
price = {start_year: price0}
out[start_year] = units0 * price0
for i in range(1, horizon_years):
y = start_year + i
units[y] = units[y - 1] * (1.0 + safe_get_year_map(gu, y, 0.0))
price[y] = price[y - 1] * (1.0 + safe_get_year_map(gp, y, 0.0))
out[y] = units[y] * price[y]
return out
if model == "arr":
begin_arr = float(rev["begin_arr"])
new_arr = rev.get("new_arr", {})
nrr = rev.get("net_retention", {})
churn = rev.get("churn_rate", {})
recognition = rev.get("revenue_recognition", "avg_arr")
arr_beg = {start_year: begin_arr}
arr_end: Dict[int, float] = {}
for i in range(horizon_years):
y = start_year + i
if i > 0:
arr_beg[y] = arr_end[y - 1]
nrr_y = safe_get_year_map(nrr, y, 1.0)
churn_y = safe_get_year_map(churn, y, 0.0)
new_y = safe_get_year_map(new_arr, y, 0.0)
arr_end[y] = arr_beg[y] * nrr_y * (1.0 - churn_y) + new_y
if recognition == "avg_arr":
out[y] = 0.5 * (arr_beg[y] + arr_end[y])
else:
out[y] = arr_end[y]
return out
raise ValueError(f"Unsupported revenue model: {model}")
def expand_year_to_periods(periods: List[Period], by_year: Dict[int, float]) -> List[float]:
out: List[float] = []
for p in periods:
yval = by_year.get(p.year)
if yval is None:
# fallback to nearest year
years = sorted(by_year.keys())
prior = [y for y in years if y <= p.year]
yval = by_year[prior[-1]] if prior else by_year[years[0]]
if p.frequency == "annual":
out.append(float(yval))
else:
out.append(float(yval) / 4.0)
return out
def annualized_or_ltm_series(periods: List[Period], values: List[float]) -> List[float]:
"""Return annual-equivalent values for ratio and exit calculations.
Quarterly cash flows remain quarterly in operating and debt schedules. Ratios
use an annualized run rate before four forecast quarters exist and LTM values
thereafter so debt/EBITDA and exit multiples are not computed on one quarter.
"""
if not periods:
return []
if periods[0].frequency == "annual":
return list(values)
result: List[float] = []
for i, value in enumerate(values):
if i >= 3:
result.append(sum(values[i - 3 : i + 1]))
else:
result.append(float(value) / periods[i].time_factor)
return result
def compute_operating_series(plan: Dict[str, Any], periods: List[Period]) -> Dict[str, List[float]]:
tl = plan["timeline"]
start_year = int(tl["start_year"])
horizon_years = int(tl["horizon_years"])
rev_year = revenue_by_year(plan, start_year, horizon_years)
revenue = expand_year_to_periods(periods, rev_year)
op = plan["operating"]
ebitda_margin_map = op.get("ebitda_margin", {})
tax_rate = float(op.get("tax_rate", 0.25))
da_cfg = op.get("da", {"model": "pct_revenue", "pct": 0.03})
capex_cfg = op.get("capex", {"model": "pct_revenue", "pct": 0.04})
ebitda: List[float] = []
gross_profit: List[float] = []
opex: List[float] = []
da: List[float] = []
ebit: List[float] = []
capex: List[float] = []
maintenance_capex: List[float] = []
growth_capex: List[float] = []
separation_capex: List[float] = []
standalone_costs: List[float] = []
tsa_costs: List[float] = []
stranded_costs: List[float] = []
one_time_cash_costs: List[float] = []
carve = plan.get("transaction", {}).get("carve_out", {}) or {}
carve_enabled = bool(carve.get("enabled", False))
one_time_cfg = op.get("one_time_cash_costs", {}) if isinstance(op.get("one_time_cash_costs", {}), dict) else {}
add_on_cfg = plan.get("add_on_acquisitions", {}) or {}
add_on_revenue = [0.0] * len(periods)
add_on_ebitda = [0.0] * len(periods)
add_on_integration_costs = [0.0] * len(periods)
if bool(add_on_cfg.get("enabled", False)):
for deal in add_on_cfg.get("deals", []) or []:
close_year = int(deal.get("close_year", start_year))
annual_revenue = float(deal.get("revenue", 0.0) or 0.0)
annual_ebitda = float(deal.get("ebitda", 0.0) or 0.0)
annual_synergy = float(deal.get("synergy_ebitda", 0.0) or 0.0)
integration_cost = float(deal.get("integration_cost", 0.0) or 0.0)
for i, p in enumerate(periods):
if p.year >= close_year:
add_on_revenue[i] += annual_revenue * p.time_factor
add_on_ebitda[i] += (annual_ebitda + annual_synergy) * p.time_factor
if p.year == close_year:
add_on_integration_costs[i] += integration_cost * p.time_factor
for i, (p, rev) in enumerate(zip(periods, revenue)):
if add_on_revenue[i]:
rev += add_on_revenue[i]
revenue[i] = rev
m = safe_get_year_map(ebitda_margin_map, p.year, float(list(ebitda_margin_map.values())[0]) if ebitda_margin_map else 0.2)
gross_margin_cfg = op.get("gross_margin", {})
if isinstance(gross_margin_cfg, dict) and gross_margin_cfg:
gm = safe_get_year_map(gross_margin_cfg, p.year, 0.5)
gp = rev * gm
else:
gp = float("nan")
standalone = safe_get_year_map(carve.get("standalone_costs", {}), p.year, 0.0) * p.time_factor if carve_enabled else 0.0
tsa = safe_get_year_map(carve.get("tsa_costs", {}), p.year, 0.0) * p.time_factor if carve_enabled else 0.0
stranded = safe_get_year_map(carve.get("stranded_costs", {}), p.year, 0.0) * p.time_factor if carve_enabled else 0.0
one_time = safe_get_year_map(one_time_cfg, p.year, 0.0) * p.time_factor + add_on_integration_costs[i]
if carve_enabled:
one_time += safe_get_year_map(carve.get("one_time_separation_costs", {}), p.year, 0.0) * p.time_factor
e = rev * m + add_on_ebitda[i] - standalone - tsa - stranded
ebitda.append(e)
gross_profit.append(gp)
opex.append((gp - e) if gp == gp else float("nan"))
standalone_costs.append(standalone)
tsa_costs.append(tsa)
stranded_costs.append(stranded)
one_time_cash_costs.append(one_time)
if da_cfg.get("model") == "pct_revenue":
da_t = rev * float(da_cfg.get("pct", 0.03))
else:
da_t = float(da_cfg.get("amount", 0.0)) * p.time_factor
da.append(da_t)
ebit.append(e - da_t)
sep_capex = safe_get_year_map(carve.get("separation_capex", {}), p.year, 0.0) * p.time_factor if carve_enabled else 0.0
if capex_cfg.get("model") == "split":
maint = rev * float(capex_cfg.get("maintenance_pct_revenue", capex_cfg.get("maintenance_pct", 0.0)))
growth = rev * float(capex_cfg.get("growth_pct_revenue", capex_cfg.get("growth_pct", 0.0)))
capex_t = maint + growth + sep_capex
elif capex_cfg.get("model") == "pct_revenue":
capex_t = rev * float(capex_cfg.get("pct", 0.04))
maint = capex_t
growth = 0.0
else:
capex_t = float(capex_cfg.get("amount", 0.0)) * p.time_factor
maint = capex_t
growth = 0.0
capex_t += sep_capex if capex_cfg.get("model") != "split" else 0.0
capex.append(capex_t)
maintenance_capex.append(maint)
growth_capex.append(growth)
separation_capex.append(sep_capex)
# Working capital
wc_cfg = op.get("working_capital", {})
wc_model = wc_cfg.get("model", "days")
ar_days = float(wc_cfg.get("ar_days", 45))
ap_days = float(wc_cfg.get("ap_days", 30))
inv_days = float(wc_cfg.get("inv_days", 0))
include_inv = bool(wc_cfg.get("include_inventory", False))
ar: List[float] = []
ap: List[float] = []
inv: List[float] = []
nwc: List[float] = []
delta_nwc: List[float] = []
if wc_model == "pct_revenue":
pct = float(wc_cfg.get("pct", 0.05))
for i, (p, rev) in enumerate(zip(periods, revenue)):
nwc_t = rev * pct
nwc.append(nwc_t)
delta_nwc.append(nwc_t - (nwc[i - 1] if i > 0 else nwc_t))
# placeholders
ar = [0.0] * len(periods)
ap = [0.0] * len(periods)
inv = [0.0] * len(periods)
else:
# Simplified days-based: approximate payables and inventory off revenue.
# This is intentionally conservative and avoids needing a full COGS stack.
ap_factor = float(wc_cfg.get("ap_revenue_factor", 0.7))
inv_factor = float(wc_cfg.get("inv_revenue_factor", 0.5))
for i, (p, rev) in enumerate(zip(periods, revenue)):
ar_t = rev / 365.0 * ar_days
ap_t = rev / 365.0 * ap_days * ap_factor
inv_t = (rev / 365.0 * inv_days * inv_factor) if include_inv else 0.0
nwc_t = ar_t + inv_t - ap_t
ar.append(ar_t)
ap.append(ap_t)
inv.append(inv_t)
nwc.append(nwc_t)
delta_nwc.append(nwc_t - (nwc[i - 1] if i > 0 else nwc_t))
return {
"revenue": revenue,
"gross_profit": gross_profit,
"opex": opex,
"ebitda": ebitda,
"da": da,
"ebit": ebit,
"capex": capex,
"maintenance_capex": maintenance_capex,
"growth_capex": growth_capex,
"separation_capex": separation_capex,
"standalone_costs": standalone_costs,
"tsa_costs": tsa_costs,
"stranded_costs": stranded_costs,
"one_time_cash_costs": one_time_cash_costs,
"add_on_revenue": add_on_revenue,
"add_on_ebitda": add_on_ebitda,
"add_on_integration_costs": add_on_integration_costs,
"tax_rate": [tax_rate] * len(periods),
"ar": ar,
"ap": ap,
"inv": inv,
"nwc": nwc,
"delta_nwc": delta_nwc,
}
def tranche_rate(tr: Dict[str, Any], base_rate: float) -> float:
ttype = tr.get("type")
if ttype in {"notes", "mezz"}:
return float(tr.get("fixed_rate", tr.get("cash_rate", 0.0)))
if ttype == "pik":
return float(tr.get("cash_rate", 0.0))
# floating
floor = float(tr.get("floor", 0.0))
spread = float(tr.get("spread", 0.0))
return max(base_rate, floor) + spread
def irr(cashflows: List[float]) -> Optional[float]:
"""Compute IRR (annualized) using Newton method with fallback bisection."""
# Require at least one negative and one positive
if not (any(cf < 0 for cf in cashflows) and any(cf > 0 for cf in cashflows)):
return None
def npv(rate: float) -> float:
return sum(cf / ((1 + rate) ** t) for t, cf in enumerate(cashflows))
def d_npv(rate: float) -> float:
return sum(-t * cf / ((1 + rate) ** (t + 1)) for t, cf in enumerate(cashflows) if t > 0)
r = 0.2
for _ in range(50):
f = npv(r)
df = d_npv(r)
if abs(df) < 1e-9:
break
new_r = r - f / df
if new_r <= -0.95 or new_r > 10:
break
if abs(new_r - r) < 1e-8:
return new_r
r = new_r
# Bisection fallback
lo, hi = -0.9, 5.0
f_lo, f_hi = npv(lo), npv(hi)
if f_lo * f_hi > 0:
return None
for _ in range(100):
mid = 0.5 * (lo + hi)
f_mid = npv(mid)
if abs(f_mid) < 1e-8:
return mid
if f_lo * f_mid <= 0:
hi = mid
f_hi = f_mid
else:
lo = mid
f_lo = f_mid
return 0.5 * (lo + hi)
def run_debt_and_cash(plan: Dict[str, Any], periods: List[Period], op: Dict[str, List[float]], entry: Dict[str, float], run_log: Dict[str, Any]) -> Dict[str, Any]:
debt_cfg = plan.get("debt", {})
base_rate = float(debt_cfg.get("base_rate", {}).get("assumption", 0.04))
tranches = list(debt_cfg.get("tranches", []))
# Initialize balances
balances: Dict[str, float] = {}
commitments: Dict[str, float] = {}
tranche_map: Dict[str, Dict[str, Any]] = {}
for tr in tranches:
tid = tr["id"]
tranche_map[tid] = tr
if tr.get("type") == "revolver":
balances[tid] = 0.0
commitments[tid] = float(tr.get("commitment", 0.0))
else:
balances[tid] = float(tr.get("face", 0.0) or 0.0)
min_cash = float(plan.get("transaction", {}).get("min_cash", 0.0))
sweep_cfg = debt_cfg.get("cash_sweep", {})
if sweep_cfg.get("min_cash") is not None:
min_cash = float(sweep_cfg["min_cash"])
sweep_enabled = bool(sweep_cfg.get("enabled", True))
sweep_pct = float(sweep_cfg.get("percentage", 1.0))
# Debt schedule outputs
sched: Dict[str, Dict[str, List[float]]] = {}
for tid in tranche_map:
sched[tid] = {k: [0.0] * len(periods) for k in ["beg", "draw", "repay", "amort", "interest", "end", "pik"]}
cash = [0.0] * len(periods)
cash[0] = float(entry.get("opening_cash", min_cash))
cash_interest = [0.0] * len(periods)
cash_taxes = [0.0] * len(periods)
fcf_after_debt_service = [0.0] * len(periods)
dividends = [0.0] * len(periods)
ebt = [0.0] * len(periods)
net_income = [0.0] * len(periods)
nol_used = [0.0] * len(periods)
ending_nol = [0.0] * len(periods)
# Dividend schedule (optional)
div_cfg = plan.get("operating", {}).get("dividends", {})
div_enabled = bool(div_cfg.get("enabled", False))
div_amounts = div_cfg.get("amounts", {}) if isinstance(div_cfg.get("amounts", {}), dict) else {}
tax_cfg = plan.get("tax", {}) or {}
nol_balance = float(tax_cfg.get("opening_nol", 0.0) or 0.0)
interest_limit_pct = tax_cfg.get("interest_deductibility_limit_pct_ebitda")
financing_events: Dict[int, List[Dict[str, float | str]]] = {}
for event in debt_cfg.get("dividend_recaps", []) or []:
year = int(event.get("year"))
financing_events.setdefault(year, []).append(
{
"kind": "dividend_recap",
"target_tranche": event.get("target_tranche", ""),
"debt_raise": float(event.get("debt_raise", 0.0) or 0.0),
"cash_use": float(event.get("dividend", 0.0) or 0.0) + float(event.get("fees", 0.0) or 0.0),
}
)
add_on_cfg = plan.get("add_on_acquisitions", {}) or {}
if bool(add_on_cfg.get("enabled", False)):
for deal in add_on_cfg.get("deals", []) or []:
close_year = int(deal.get("close_year", periods[0].year))
purchase_price = float(deal.get("purchase_price", 0.0) or 0.0)
if purchase_price == 0.0 and deal.get("purchase_multiple") is not None:
purchase_price = float(deal.get("purchase_multiple")) * float(deal.get("ebitda", 0.0) or 0.0)
fees = float(deal.get("transaction_fees", 0.0) or 0.0)
debt_pct = float(deal.get("debt_financing_pct", 0.0) or 0.0)
financing_events.setdefault(close_year, []).append(
{
"kind": "add_on_acquisition",
"target_tranche": deal.get("target_tranche", ""),
"debt_raise": purchase_price * debt_pct,
"cash_use": purchase_price + fees,
}
)
# Sweep order
order = sorted(tranches, key=lambda x: int(x.get("sweep_rank", 999)))
# Iterate period-by-period
for t, p in enumerate(periods):
cash_beg = cash[t - 1] if t > 0 else cash[0]
# Scheduled amort
scheduled_amort: Dict[str, float] = {}
for tr in tranches:
tid = tr["id"]
if tr.get("type") == "term_loan":
face = float(tr.get("face", 0.0))
amort_pct = float(tr.get("amort_pct_per_year", 0.0))
amort = face * amort_pct * p.time_factor
scheduled_amort[tid] = min(amort, balances[tid])
else:
scheduled_amort[tid] = 0.0
# Per-period fixed-point iteration to handle interest/taxes/sweep/revolver
end_bal = balances.copy()
end_cash = cash_beg
max_iter = 50
tol = 1e-6
converged = False
for it in range(max_iter):
# Interest on avg balances (use current guess for end balances)
interests: Dict[str, float] = {}
total_interest = 0.0
for tr in tranches:
tid = tr["id"]
beg = balances[tid]
end_guess = end_bal[tid]
avg = 0.5 * (beg + end_guess)
rate = tranche_rate(tr, base_rate)
interest = avg * rate * p.time_factor
interests[tid] = interest
# Commitment fee on undrawn revolver
if tr.get("type") == "revolver":
commit_fee = float(tr.get("commit_fee", 0.0))
undrawn = max(0.0, commitments[tid] - avg)
interest += undrawn * commit_fee * p.time_factor
interests[tid] = interest
total_interest += interest
taxable_ebt = op["ebit"][t] - total_interest
if interest_limit_pct is not None:
deductible_interest = min(total_interest, max(0.0, op["ebitda"][t] * float(interest_limit_pct)))
taxable_ebt = op["ebit"][t] - deductible_interest
taxable_income = max(0.0, taxable_ebt)
taxable_after_nol = max(0.0, taxable_income - nol_balance)
taxes = taxable_after_nol * float(op["tax_rate"][t])
# Operating cash after taxes/interest, before financing actions
op_cash = op["ebitda"][t] - op["capex"][t] - op["delta_nwc"][t] - op.get("one_time_cash_costs", [0.0] * len(periods))[t] - taxes - total_interest
# Apply scheduled amort (financing outflow)
cash_pre = cash_beg + op_cash
for tr in tranches:
tid = tr["id"]
if scheduled_amort[tid] > 0:
cash_pre -= scheduled_amort[tid]
# Optional dividends (after ops, before sweeps by default)
div = 0.0
if div_enabled:
div = safe_get_year_map(div_amounts, p.year, 0.0) * p.time_factor
cash_pre -= div
for event in financing_events.get(p.year, []):
cash_pre -= float(event.get("cash_use", 0.0))
# Sweep / revolver
new_end_bal = balances.copy()
# Start from balances and apply scheduled amort to end balances
for tr in tranches:
tid = tr["id"]
new_end_bal[tid] = max(0.0, balances[tid] - scheduled_amort[tid])
end_cash_candidate = cash_pre
# Revolver draw if shortfall
rev_id = None
for tr in tranches:
if tr.get("type") == "revolver":
rev_id = tr["id"]
break
if rev_id is not None:
shortfall = max(0.0, min_cash - end_cash_candidate)
if shortfall > 0:
avail = max(0.0, commitments[rev_id] - new_end_bal[rev_id])
draw = min(avail, shortfall)
new_end_bal[rev_id] += draw
end_cash_candidate += draw
for event in financing_events.get(p.year, []):
debt_raise = float(event.get("debt_raise", 0.0))
target = str(event.get("target_tranche") or "")
if debt_raise <= 0:
continue
if target and target in new_end_bal:
new_end_bal[target] += debt_raise
end_cash_candidate += debt_raise
else:
target_ids = [tr["id"] for tr in tranches if tr.get("type") != "revolver"]
if target_ids:
new_end_bal[target_ids[0]] += debt_raise
end_cash_candidate += debt_raise
# Sweep excess cash
if sweep_enabled and sweep_pct > 0:
excess = max(0.0, end_cash_candidate - min_cash)
sweep_cash = excess * sweep_pct
# Repay in rank order
remaining = sweep_cash
for tr in order:
tid = tr["id"]
if remaining <= 0:
break
if tr.get("type") == "revolver":
repay = min(new_end_bal[tid], remaining)
new_end_bal[tid] -= repay
remaining -= repay
else:
repay = min(new_end_bal[tid], remaining)
new_end_bal[tid] -= repay
remaining -= repay
end_cash_candidate -= (sweep_cash - remaining)
# PIK interest capitalization
for tr in tranches:
if tr.get("type") == "pik":
tid = tr["id"]
pik_rate = float(tr.get("pik_rate", 0.0))
beg = balances[tid]
end_guess = new_end_bal[tid]
avg = 0.5 * (beg + end_guess)
pik_amt = avg * pik_rate * p.time_factor
new_end_bal[tid] += pik_amt
# Check convergence
diff = max(abs(new_end_bal[k] - end_bal[k]) for k in end_bal)
diff = max(diff, abs(end_cash_candidate - end_cash))
end_bal = new_end_bal
end_cash = end_cash_candidate
if diff < tol:
converged = True
break
if not converged:
run_log["warnings"].append({
"code": "SOLVER_NO_CONVERGE",
"message": f"Debt/interest solver did not converge in period {p.label}; using last iterate.",
"period": p.label,
})
# With final end_bal, compute final interest and taxes for reporting
interests_final: Dict[str, float] = {}
total_interest_final = 0.0
for tr in tranches:
tid = tr["id"]
beg = balances[tid]
avg = 0.5 * (beg + end_bal[tid])
rate = tranche_rate(tr, base_rate)
interest = avg * rate * p.time_factor
if tr.get("type") == "revolver":
commit_fee = float(tr.get("commit_fee", 0.0))
undrawn = max(0.0, commitments[tid] - avg)
interest += undrawn * commit_fee * p.time_factor
interests_final[tid] = interest
total_interest_final += interest
ebt_final = op["ebit"][t] - total_interest_final
taxable_ebt_final = ebt_final
if interest_limit_pct is not None:
deductible_interest = min(total_interest_final, max(0.0, op["ebitda"][t] * float(interest_limit_pct)))
taxable_ebt_final = op["ebit"][t] - deductible_interest
taxable_income_final = max(0.0, taxable_ebt_final)
nol_used_t = min(nol_balance, taxable_income_final)
taxes_final = max(0.0, taxable_income_final - nol_used_t) * float(op["tax_rate"][t])
nol_balance = max(0.0, nol_balance - nol_used_t)
# Update schedules
for tr in tranches:
tid = tr["id"]
pik_amt_final = 0.0
if tr.get("type") == "pik":
pik_rate = float(tr.get("pik_rate", 0.0))
avg_for_pik = 0.5 * (balances[tid] + end_bal[tid])
pik_amt_final = avg_for_pik * pik_rate * p.time_factor
sched[tid]["beg"][t] = balances[tid]
sched[tid]["amort"][t] = scheduled_amort.get(tid, 0.0)
sched[tid]["interest"][t] = interests_final.get(tid, 0.0)
sched[tid]["end"][t] = end_bal[tid]
sched[tid]["pik"][t] = pik_amt_final
# Draw/repay inferred
delta = end_bal[tid] - (balances[tid] - scheduled_amort.get(tid, 0.0) + pik_amt_final)
if delta > 0:
sched[tid]["draw"][t] = delta
elif delta < 0:
sched[tid]["repay"][t] = -delta
balances = end_bal
cash[t] = end_cash
cash_interest[t] = total_interest_final
cash_taxes[t] = taxes_final
ebt[t] = ebt_final
net_income[t] = ebt_final - taxes_final
nol_used[t] = nol_used_t
ending_nol[t] = nol_balance
# Dividends for reporting
if div_enabled:
dividends[t] = safe_get_year_map(div_amounts, p.year, 0.0) * p.time_factor
for event in financing_events.get(p.year, []):
if event.get("kind") == "dividend_recap":
dividends[t] += float(event.get("cash_use", 0.0))
# FCF after debt service: cash change excluding financing? Provide a simple measure: op cash minus interest/taxes/capex/dNWC
fcf_after_debt_service[t] = op["ebitda"][t] - op["capex"][t] - op["delta_nwc"][t] - op.get("one_time_cash_costs", [0.0] * len(periods))[t] - cash_taxes[t] - cash_interest[t]
return {
"cash": cash,
"cash_interest": cash_interest,
"cash_taxes": cash_taxes,
"ebt": ebt,
"net_income": net_income,
"nol_used": nol_used,
"ending_nol": ending_nol,
"fcf_after_debt_service": fcf_after_debt_service,
"dividends": dividends,
"sched": sched,
"final_balances": balances,
}
def compute_covenants(plan: Dict[str, Any], periods: List[Period], op: Dict[str, List[float]], debt_result: Dict[str, Any]) -> Dict[str, List[float]]:
cash = debt_result["cash"]
sched = debt_result["sched"]
total_debt = []
for t, _p in enumerate(periods):
td = 0.0
for tid in sched:
td += sched[tid]["end"][t]
total_debt.append(td)
ebitda = annualized_or_ltm_series(periods, op["ebitda"])
raw_cash_interest = debt_result["cash_interest"]
cash_interest = annualized_or_ltm_series(periods, raw_cash_interest)
cash_taxes = annualized_or_ltm_series(
periods, debt_result.get("cash_taxes", [0.0] * len(periods))
)
capex = annualized_or_ltm_series(periods, op.get("capex", [0.0] * len(periods)))
delta_nwc = annualized_or_ltm_series(
periods, op.get("delta_nwc", [0.0] * len(periods))
)
total_lev = [ (total_debt[t] / ebitda[t]) if ebitda[t] != 0 else float("inf") for t in range(len(periods))]
cash_in_net = bool(plan.get("covenants", {}).get("definitions", {}).get("cash_in_net_debt", True))
net_debt = [total_debt[t] - (cash[t] if cash_in_net else 0.0) for t in range(len(periods))]
net_lev = [(net_debt[t] / ebitda[t]) if ebitda[t] != 0 else float("inf") for t in range(len(periods))]
icr = [(ebitda[t] / cash_interest[t]) if cash_interest[t] != 0 else float("inf") for t in range(len(periods))]
mandatory_amort = []
total_debt_service = []
for t in range(len(periods)):
amort_t = sum(s["amort"][t] for s in sched.values())
mandatory_amort.append(amort_t)
total_debt_service.append(raw_cash_interest[t] + amort_t)
covenant_amort = annualized_or_ltm_series(periods, mandatory_amort)
coverage_debt_service = [
cash_interest[t] + covenant_amort[t] for t in range(len(periods))
]
fixed_charge_coverage = [
((ebitda[t] - cash_taxes[t] - capex[t]) / coverage_debt_service[t]) if coverage_debt_service[t] != 0 else float("inf")
for t in range(len(periods))
]
debt_service_coverage = [
((ebitda[t] - cash_taxes[t] - delta_nwc[t] - capex[t]) / coverage_debt_service[t]) if coverage_debt_service[t] != 0 else float("inf")
for t in range(len(periods))
]
return {
"total_debt": total_debt,
"net_debt": net_debt,
"annualized_ebitda": ebitda,
"total_leverage": total_lev,
"net_leverage": net_lev,
"interest_coverage": icr,
"fixed_charge_coverage": fixed_charge_coverage,
"debt_service_coverage": debt_service_coverage,
"mandatory_amortization": mandatory_amort,
"total_debt_service": total_debt_service,
"coverage_debt_service": coverage_debt_service,
"liquidity": cash,
}
def compute_purchase_accounting(plan: Dict[str, Any], entry: Dict[str, float]) -> Dict[str, float]:
"""Compute a simplified opening purchase-accounting bridge.
This is intentionally deterministic and transparent. It is not a substitute
for a full valuation/PPA workpaper, but it prevents the model from hiding the
goodwill/intangible/deferred-tax implications of the entry price.
"""
pa = plan.get("transaction", {}).get("purchase_accounting", {}) or {}
enabled = bool(pa.get("enabled", False))
book_net_assets = pa.get("book_net_assets")
if book_net_assets is None:
book_net_assets = entry.get("equity_purchase", 0.0)
book_net_assets = float(book_net_assets or 0.0)
fair_value_step_up = float(pa.get("fair_value_step_up", 0.0) or 0.0)
identifiable_intangibles = float(pa.get("identifiable_intangibles", 0.0) or 0.0)
deferred_tax_rate = float(pa.get("deferred_tax_rate", plan.get("operating", {}).get("tax_rate", 0.25)) or 0.0)
deferred_tax_liability = (fair_value_step_up + identifiable_intangibles) * deferred_tax_rate if enabled else 0.0
identifiable_net_assets = book_net_assets + fair_value_step_up + identifiable_intangibles - deferred_tax_liability
goodwill = pa.get("goodwill")
if goodwill is None:
goodwill = max(0.0, float(entry.get("equity_purchase", 0.0)) - identifiable_net_assets)
return {
"enabled": 1.0 if enabled else 0.0,
"book_net_assets": book_net_assets,
"fair_value_step_up": fair_value_step_up,
"identifiable_intangibles": identifiable_intangibles,
"deferred_tax_liability": deferred_tax_liability,
"identifiable_net_assets": identifiable_net_assets,
"goodwill": float(goodwill or 0.0),
"intangible_amortization_years": float(pa.get("intangible_amortization_years", 10) or 10),
}
def compute_balance_sheet(
plan: Dict[str, Any],
periods: List[Period],
entry: Dict[str, float],
op: Dict[str, List[float]],
debt_result: Dict[str, Any],
cov: Dict[str, List[float]],
) -> Dict[str, Any]:
pa = compute_purchase_accounting(plan, entry)
bs_cfg = plan.get("balance_sheet", {}) or {}
opening_nwc = bs_cfg.get("opening_nwc")
if opening_nwc is None:
opening_nwc = op["nwc"][0] if op.get("nwc") else 0.0
opening_ppe = bs_cfg.get("opening_ppe")
if opening_ppe is None:
opening_ppe = max(0.0, float(pa["book_net_assets"]) - float(opening_nwc))
opening_other_assets = float(bs_cfg.get("opening_other_assets", 0.0) or 0.0)
opening_other_liabilities = float(bs_cfg.get("opening_other_liabilities", 0.0) or 0.0)
lease_liabilities = float(bs_cfg.get("lease_liabilities", 0.0) or 0.0)
opening_re = float(bs_cfg.get("opening_retained_earnings", 0.0) or 0.0)
ppe: List[float] = []
intangibles: List[float] = []
goodwill: List[float] = []
deferred_tax_liability: List[float] = []
other_assets: List[float] = []
other_liabilities: List[float] = []
retained_earnings: List[float] = []
sponsor_equity_book: List[float] = []
total_assets: List[float] = []
total_liabilities: List[float] = []
total_equity: List[float] = []
balance_check: List[float] = []
ppe_prev = float(opening_ppe)
intang_prev = float(pa["identifiable_intangibles"])
goodwill_val = float(pa["goodwill"])
dtl_val = float(pa["deferred_tax_liability"])
re_prev = opening_re
amort_years = max(1.0, float(pa["intangible_amortization_years"]))
for t, p in enumerate(periods):
ppe_end = max(0.0, ppe_prev + op["capex"][t] - op["da"][t])
intangible_amort = (float(pa["identifiable_intangibles"]) / amort_years) * p.time_factor if pa["identifiable_intangibles"] else 0.0
intang_end = max(0.0, intang_prev - intangible_amort)
re_end = re_prev + debt_result.get("net_income", [0.0] * len(periods))[t] - debt_result.get("dividends", [0.0] * len(periods))[t]
assets = debt_result["cash"][t] + op["nwc"][t] + ppe_end + intang_end + goodwill_val + opening_other_assets
liabilities = cov["total_debt"][t] + dtl_val + opening_other_liabilities + lease_liabilities
# Equity is a book-equity plug in deterministic_export mode; the retained
# earnings line still exposes whether net income/dividends are doing work.
equity = assets - liabilities
apic = equity - re_end
ppe.append(ppe_end)
intangibles.append(intang_end)
goodwill.append(goodwill_val)
deferred_tax_liability.append(dtl_val)
other_assets.append(opening_other_assets)
other_liabilities.append(opening_other_liabilities + lease_liabilities)
retained_earnings.append(re_end)
sponsor_equity_book.append(apic)
total_assets.append(assets)
total_liabilities.append(liabilities)
total_equity.append(equity)
balance_check.append(assets - liabilities - equity)
ppe_prev = ppe_end
intang_prev = intang_end
re_prev = re_end
return {
"purchase_accounting": pa,
"cash": debt_result["cash"],
"accounts_receivable": op.get("ar", [0.0] * len(periods)),
"inventory": op.get("inv", [0.0] * len(periods)),
"accounts_payable": op.get("ap", [0.0] * len(periods)),
"net_working_capital": op.get("nwc", [0.0] * len(periods)),
"ppe": ppe,
"intangibles": intangibles,
"goodwill": goodwill,
"deferred_tax_liability": deferred_tax_liability,
"other_assets": other_assets,
"other_liabilities": other_liabilities,
"total_debt": cov["total_debt"],
"retained_earnings": retained_earnings,
"sponsor_equity_book": sponsor_equity_book,
"total_assets": total_assets,
"total_liabilities": total_liabilities,
"total_equity": total_equity,
"balance_check": balance_check,
}
def compute_exit_and_returns(plan: Dict[str, Any], periods: List[Period], op: Dict[str, List[float]], debt_result: Dict[str, Any], entry: Dict[str, float]) -> Dict[str, float]:
ex = plan.get("exit", {})
exit_year = int(ex.get("exit_year"))
# Find last period in exit year
exit_idx = max(i for i, p in enumerate(periods) if p.year == exit_year)
exit_ebitda = annualized_or_ltm_series(periods, op["ebitda"])[exit_idx]
exit_method = ex.get("method")
if exit_method == "multiple":
exit_multiple = float(ex.get("exit_multiple"))
exit_ev = exit_multiple * exit_ebitda
elif exit_method == "ev":
exit_ev = float(ex.get("exit_ev"))
else:
raise ValueError("exit.method must be 'multiple' or 'ev'")
# Net debt at exit
sched = debt_result["sched"]
debt_exit = sum(sched[tid]["end"][exit_idx] for tid in sched)
cash_exit = debt_result["cash"][exit_idx]
net_debt_adj = float(ex.get("net_debt_adjustments", 0.0))
exit_equity = exit_ev - (debt_exit - cash_exit) - net_debt_adj
mgmt = plan.get("transaction", {}).get("management_incentive", {}) or {}
mgmt_enabled = bool(mgmt.get("enabled", False))
mgmt_pool_pct = float(mgmt.get("pool_pct_fully_diluted", 0.0) or 0.0) if mgmt_enabled else 0.0
mgmt_threshold = float(mgmt.get("threshold_equity_value", 0.0) or 0.0) if mgmt_enabled else 0.0
management_proceeds = max(0.0, exit_equity - mgmt_threshold) * mgmt_pool_pct
sponsor_exit_proceeds = exit_equity - management_proceeds
# Equity cash flows: initial at t=0, then one cash flow for each operating period.
equity_check = float(entry["sponsor_equity"])
cashflows = [-equity_check]
# Add dividends for modeled periods through exit, then place exit proceeds at
# the end of the exit period. This preserves the stated hold duration.
cashflows.extend(debt_result["dividends"][: exit_idx + 1])
cashflows[exit_idx + 1] += sponsor_exit_proceeds
# Convert per-period IRR to annualized
per_period_irr = irr(cashflows[: exit_idx + 2])
if per_period_irr is None:
ann_irr = None
else:
if periods[0].frequency == "annual":
ann_irr = per_period_irr
else:
ann_irr = (1.0 + per_period_irr) ** 4 - 1.0
moic = (sum(cf for cf in cashflows[: exit_idx + 2] if cf > 0) / equity_check) if equity_check != 0 else float("inf")
entry_ebitda = float(plan.get("transaction", {}).get("entry", {}).get("entry_ebitda") or plan.get("ebitda_basis", {}).get("transaction_ebitda_used") or 0.0)
entry_multiple = (float(entry.get("entry_ev", 0.0)) / entry_ebitda) if entry_ebitda else float("nan")
exit_multiple = (exit_ev / exit_ebitda) if exit_ebitda else float("nan")
entry_net_debt = float(entry.get("debt_sources", 0.0)) - float(entry.get("opening_cash", 0.0))
exit_net_debt = debt_exit - cash_exit
dividends_total = sum(debt_result.get("dividends", [])[: exit_idx + 1])
ebitda_growth_value = (exit_ebitda - entry_ebitda) * entry_multiple if entry_multiple == entry_multiple else float("nan")
multiple_change_value = exit_ev - (exit_ebitda * entry_multiple) if entry_multiple == entry_multiple else float("nan")
net_debt_reduction_value = entry_net_debt - exit_net_debt
return {
"exit_year": exit_year,
"exit_period_index": exit_idx,
"exit_ev": exit_ev,
"exit_equity": exit_equity,
"sponsor_exit_proceeds": sponsor_exit_proceeds,
"management_proceeds": management_proceeds,
"management_pool_pct": mgmt_pool_pct,
"equity_check": equity_check,
"irr": ann_irr if ann_irr is not None else float("nan"),
"moic": moic,
"exit_ebitda": exit_ebitda,
"entry_multiple": entry_multiple,
"exit_multiple": exit_multiple,
"value_creation_ebitda_growth": ebitda_growth_value,
"value_creation_multiple_change": multiple_change_value,
"value_creation_deleveraging": net_debt_reduction_value,
"value_creation_dividends": dividends_total,
"value_creation_management_dilution": -management_proceeds,
}
def to_csv_rows(
plan: Dict[str, Any],
periods: List[Period],
scenario: str,
entry: Dict[str, float],
op: Dict[str, List[float]],
debt_result: Dict[str, Any],
cov: Dict[str, List[float]],
bs: Dict[str, Any],
ret: Dict[str, float],
) -> List[Dict[str, Any]]:
units = plan.get("meta", {}).get("units", "USD_mm")
rows: List[Dict[str, Any]] = []
def emit(statement: str, section: str, line_item: str, values: List[float]):
for p, v in zip(periods, values):
value = "" if isinstance(v, float) and math.isnan(v) else float(v)
rows.append(
{
"scenario": scenario,
"period_index": p.index,
"period_label": p.label,
"period_year": p.year,
"frequency": p.frequency,
"statement": statement,
"section": section,
"line_item": line_item,
"value": value,
"units": units,
}
)
# Income statement
emit("IS", "Income Statement", "revenue", op["revenue"])
emit("IS", "Income Statement", "gross_profit", op["gross_profit"])
emit("IS", "Income Statement", "opex", op["opex"])
emit("IS", "Income Statement", "ebitda", op["ebitda"])
emit("IS", "Income Statement", "standalone_costs", [-x for x in op["standalone_costs"]])
emit("IS", "Income Statement", "tsa_costs", [-x for x in op["tsa_costs"]])
emit("IS", "Income Statement", "stranded_costs", [-x for x in op["stranded_costs"]])
emit("IS", "Income Statement", "da", op["da"])
emit("IS", "Income Statement", "ebit", op["ebit"])
# Interest & taxes
emit("IS", "Income Statement", "cash_interest", debt_result["cash_interest"])
emit("IS", "Income Statement", "ebt", debt_result["ebt"])
emit("IS", "Income Statement", "cash_taxes", debt_result["cash_taxes"])
emit("IS", "Income Statement", "net_income", debt_result["net_income"])
# Cash flow
emit("CF", "Cash Flow", "capex", [-x for x in op["capex"]])
emit("CF", "Cash Flow", "maintenance_capex", [-x for x in op["maintenance_capex"]])
emit("CF", "Cash Flow", "growth_capex", [-x for x in op["growth_capex"]])
emit("CF", "Cash Flow", "separation_capex", [-x for x in op["separation_capex"]])
emit("CF", "Cash Flow", "delta_nwc", [-x for x in op["delta_nwc"]])
emit("CF", "Cash Flow", "one_time_cash_costs", [-x for x in op["one_time_cash_costs"]])
emit("CF", "Cash Flow", "cash_interest", [-x for x in debt_result["cash_interest"]])
emit("CF", "Cash Flow", "cash_taxes", [-x for x in debt_result["cash_taxes"]])
emit("CF", "Cash Flow", "nol_used", debt_result["nol_used"])
emit("CF", "Cash Flow", "ending_nol", debt_result["ending_nol"])
emit("CF", "Cash Flow", "fcf_after_debt_service", debt_result["fcf_after_debt_service"])
emit("CF", "Cash Flow", "dividends", [-x for x in debt_result["dividends"]])
# Balance sheet
for key in [
"cash",
"accounts_receivable",
"inventory",
"accounts_payable",
"net_working_capital",
"ppe",
"intangibles",
"goodwill",
"deferred_tax_liability",
"other_assets",
"other_liabilities",
"total_debt",
"retained_earnings",
"sponsor_equity_book",
"total_assets",
"total_liabilities",
"total_equity",
"balance_check",
]:
emit("BS", "Balance Sheet", key, bs[key])
# Opening purchase-accounting bridge, repeated at period 0 only for audit.
for key, value in bs.get("purchase_accounting", {}).items():
if not isinstance(value, (int, float)):
continue
p0 = periods[0]
rows.append(
{
"scenario": scenario,
"period_index": p0.index,
"period_label": p0.label,
"period_year": p0.year,
"frequency": p0.frequency,
"statement": "PA",
"section": "Purchase Accounting",
"line_item": key,
"value": float(value),
"units": units,
}
)
# Debt schedules
sched = debt_result["sched"]
for tid, s in sched.items():
emit("DEBT", f"Debt Schedule:{tid}", f"debt_{tid}_beg", s["beg"])
emit("DEBT", f"Debt Schedule:{tid}", f"debt_{tid}_draw", s["draw"])
emit("DEBT", f"Debt Schedule:{tid}", f"debt_{tid}_repay", s["repay"])
emit("DEBT", f"Debt Schedule:{tid}", f"debt_{tid}_amort", s["amort"])
emit("DEBT", f"Debt Schedule:{tid}", f"debt_{tid}_interest", s["interest"])
emit("DEBT", f"Debt Schedule:{tid}", f"debt_{tid}_pik", s["pik"])
emit("DEBT", f"Debt Schedule:{tid}", f"debt_{tid}_end", s["end"])
# Covenants
emit("COVENANT", "Covenants", "total_leverage", cov["total_leverage"])
emit("COVENANT", "Covenants", "net_leverage", cov["net_leverage"])
emit("COVENANT", "Covenants", "interest_coverage", cov["interest_coverage"])
emit("COVENANT", "Covenants", "fixed_charge_coverage", cov["fixed_charge_coverage"])
emit("COVENANT", "Covenants", "debt_service_coverage", cov["debt_service_coverage"])
emit("COVENANT", "Covenants", "liquidity", cov["liquidity"])
# Returns (scalar values repeated at exit period only, else blank)
exit_idx = int(ret["exit_period_index"])
for key in [
"entry_ev",
"equity_purchase",
"sponsor_equity",
"exit_ev",
"exit_equity",
"sponsor_exit_proceeds",
"management_proceeds",
"irr",
"moic",
"value_creation_ebitda_growth",
"value_creation_multiple_change",
"value_creation_deleveraging",
"value_creation_dividends",
"value_creation_management_dilution",
]:
vals = [float("nan")] * len(periods)
if key in entry:
vals[0] = float(entry[key])
if key in ret:
vals[exit_idx] = float(ret[key])
rows.extend(
{
"scenario": scenario,
"period_index": periods[i].index,
"period_label": periods[i].label,
"period_year": periods[i].year,
"frequency": periods[i].frequency,
"statement": "RETURNS",
"section": "Returns",
"line_item": key,
"value": float(vals[i]) if not math.isnan(vals[i]) else "",
"units": units,
}
for i in range(len(periods))
if vals[i] == vals[i] # not nan
)
return rows
def write_csv(path: Path, rows: List[Dict[str, Any]]) -> None:
path.parent.mkdir(parents=True, exist_ok=True)
if not rows:
raise ValueError("No rows to write")
with path.open("w", newline="") as f:
writer = csv.DictWriter(f, fieldnames=list(rows[0].keys()))
writer.writeheader()
for r in rows:
writer.writerow(r)
def _xlsx_col_letter(n: int) -> str:
"""1-indexed column number -> Excel column letters."""
out = ""
while n > 0:
n, rem = divmod(n - 1, 26)
out = chr(ord("A") + rem) + out
return out
def _xlsx_cell_ref(row_1idx: int, col_1idx: int) -> str:
return f"{_xlsx_col_letter(col_1idx)}{row_1idx}"
def _xlsx_shared_string(s: str, shared_map: Dict[str, int], shared_list: List[str]) -> int:
if s in shared_map:
return shared_map[s]
idx = len(shared_list)
shared_map[s] = idx
shared_list.append(s)
return idx
def write_xlsx(path: Path, rows: List[Dict[str, Any]], sheet_name: str = "Model") -> None:
"""Write a minimal .xlsx workbook with a single sheet.
The sheet contains a header row and then the row dicts in tabular form.
This is standard-library only (no openpyxl).
"""
path.parent.mkdir(parents=True, exist_ok=True)
if not rows:
raise ValueError("No rows to write")
headers = list(rows[0].keys())
ncols = len(headers)
nrows = 1 + len(rows) # header + data
shared_map: Dict[str, int] = {}
shared_list: List[str] = []
# Build sheet XML
sheet_rows: List[str] = []
def emit_row(row_1idx: int, values: List[Any]) -> None:
cells: List[str] = []
for col_1idx, v in enumerate(values, start=1):
if v is None or v == "":
continue
r = _xlsx_cell_ref(row_1idx, col_1idx)
# numbers (store as raw numeric); everything else as shared string
if isinstance(v, (int, float)) and not isinstance(v, bool):
cells.append(f'<c r="{r}"><v>{v}</v></c>')
else:
s = str(v)
idx = _xlsx_shared_string(s, shared_map, shared_list)
cells.append(f'<c r="{r}" t="s"><v>{idx}</v></c>')
sheet_rows.append(f'<row r="{row_1idx}">{"".join(cells)}</row>')
# Header row
emit_row(1, headers)
# Data rows
for i, r in enumerate(rows, start=2):
emit_row(i, [r.get(h, "") for h in headers])
dim = f"A1:{_xlsx_cell_ref(nrows, ncols)}"
sheet_xml = (
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
'<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
f'<dimension ref="{dim}"/>'
"<sheetData>"
+ "".join(sheet_rows)
+ "</sheetData>"
"</worksheet>"
)
# Shared strings
def si(text: str) -> str:
# Preserve whitespace only when needed
needs_preserve = text[:1].isspace() or text[-1:].isspace()
t_attr = ' xml:space="preserve"' if needs_preserve else ""
return f"<si><t{t_attr}>{escape(text)}</t></si>"
sst_xml = (
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
'<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
f'count="{len(shared_list)}" uniqueCount="{len(shared_list)}">'
+ "".join(si(s) for s in shared_list)
+ "</sst>"
)
styles_xml = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="1">
<font>
<sz val="11"/>
<color theme="1"/>
<name val="Calibri"/>
<family val="2"/>
</font>
</fonts>
<fills count="2">
<fill><patternFill patternType="none"/></fill>
<fill><patternFill patternType="gray125"/></fill>
</fills>
<borders count="1">
<border><left/><right/><top/><bottom/><diagonal/></border>
</borders>
<cellStyleXfs count="1">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
</cellStyleXfs>
<cellXfs count="1">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
</cellXfs>
<cellStyles count="1">
<cellStyle name="Normal" xfId="0" builtinId="0"/>
</cellStyles>
</styleSheet>
"""
workbook_xml = (
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
'<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
"<sheets>"
f'<sheet name="{escape(sheet_name)}" sheetId="1" r:id="rId1"/>'
"</sheets>"
"</workbook>"
)
workbook_rels_xml = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
</Relationships>
"""
root_rels_xml = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>
"""
content_types_xml = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Default Extension="xml" ContentType="application/xml"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
</Types>
"""
with zipfile.ZipFile(path, "w", compression=zipfile.ZIP_DEFLATED) as z:
z.writestr("[Content_Types].xml", content_types_xml)
z.writestr("_rels/.rels", root_rels_xml)
z.writestr("xl/workbook.xml", workbook_xml)
z.writestr("xl/_rels/workbook.xml.rels", workbook_rels_xml)
z.writestr("xl/worksheets/sheet1.xml", sheet_xml)
z.writestr("xl/sharedStrings.xml", sst_xml)
z.writestr("xl/styles.xml", styles_xml)
SHA-256: 7310341a547b535ac80972ef5b3128a232bd24e2fd87e6a6d56435cb82e08cb7