← Files Investment BankingARCHIVED FILE
skills/merger-model-builder/scripts/runtime/skill_core
78.1 KB · Oct 2, 2026 · 00:27 UTC
"""Deterministic merger model engine.
Design goals:
- standard-library only
- no network calls
- no hidden randomness
- pure calculation functions where practical
- deterministic XLSX and markdown artifacts
"""
from __future__ import annotations
import copy
import json
import math
import zipfile
from pathlib import Path
from typing import Any, Dict, Iterable, List, Optional, Tuple
from xml.sax.saxutils import escape
ALLOWED_STATUS = {"decision-grade", "senior-review-ready", "screen-grade", "not-decision-ready", "blocked"}
REQUIRED_SOURCE_CATEGORIES = {"financials", "share_count", "offer_terms", "financing", "synergies", "purchase_accounting"}
LOW_CONFIDENCE_LABELS = {"estimate", "assumption", "placeholder", "unsupported"}
HIGH_CONFIDENCE_LABELS = {"signed_agreement", "filed", "audited", "reviewed", "vdr", "financing_commitment", "accounting_memo", "tax_memo"}
PARTIAL_CONTEXT_WARNING = "Screen-grade only; placeholder assumptions used."
TOLERANCE = 1e-4
MATERIAL_INPUT_REQUIREMENTS = [
{
"item": "Acquirer standalone financials and market data",
"categories": ["financials", "share_count"],
"paths": ["acquirer.share_price", "acquirer.diluted_shares", "acquirer.cash", "acquirer.debt", "acquirer.net_income", "acquirer.eps"],
"why_needed": "Needed to calculate standalone EPS, pro forma share issuance, lost cash interest, financing capacity, and ownership dilution.",
"fallback_method": "Use the latest user-provided or filing/consensus acquirer case; if unavailable, use a clearly labeled placeholder case from the facts provided.",
},
{
"item": "Target standalone financials",
"categories": ["financials", "share_count"],
"paths": ["target.offer_price", "target.diluted_shares", "target.cash", "target.debt", "target.book_equity", "target.net_income", "target.standalone_ebitda"],
"why_needed": "Needed for equity value, transaction enterprise value, target earnings contribution, leverage context, and purchase accounting bridge support.",
"fallback_method": "Use VDR, CIM, or user-provided target metrics; if unavailable, estimate from supplied offer terms and label the assumption as placeholder.",
},
{
"item": "Offer terms and consideration mix",
"categories": ["offer_terms"],
"paths": ["transaction.offer_price", "consideration.cash_percent", "consideration.stock_percent", "consideration.other_percent"],
"why_needed": "Needed to size cash/stock consideration, premium, shares issued, sources and uses, and pro forma ownership.",
"fallback_method": "Use the terms stated by the user; if a term is absent, infer only from the provided transaction description and label it as placeholder.",
},
{
"item": "Financing terms",
"categories": ["financing"],
"paths": ["financing.new_debt", "financing.cash_used", "financing.debt_interest_rate", "financing.lost_cash_interest_rate", "transaction.fees.financing_fees"],
"why_needed": "Needed to calculate new interest expense, lost interest income, fee amortization, cash funding, and EPS accretion/dilution.",
"fallback_method": "Use commitment papers or current financing guidance; if unavailable, use a best-estimate financing case based on the supplied mix and label it as placeholder.",
},
{
"item": "Purchase accounting and tax assumptions",
"categories": ["purchase_accounting"],
"paths": ["purchase_accounting.target_book_equity", "purchase_accounting.intangible_assets", "purchase_accounting.ppe_step_up", "purchase_accounting.deferred_tax_rate", "transaction.tax_rate"],
"why_needed": "Needed for goodwill, identifiable intangibles, DTL, amortization, depreciation, GAAP EPS, and adjusted EPS bridge.",
"fallback_method": "Use accounting/tax memo inputs where available; if unavailable, estimate PPA buckets from deal value and clearly flag preliminary/provisional treatment.",
},
{
"item": "Synergies, dis-synergies, and integration costs",
"categories": ["synergies"],
"paths": ["synergies.cost_synergies", "synergies.revenue_synergies", "synergies.dis_synergies", "synergies.integration_costs", "synergies.revenue_synergy_margin", "synergies.realization_basis"],
"why_needed": "Needed to distinguish true operating improvement from accounting/financing effects and to calculate synergy breakeven.",
"fallback_method": "Use management/integration workplan inputs; if unavailable, build a conservative placeholder ramp from user facts and test zero-synergy downside.",
},
]
def load_json(path: Path) -> Dict[str, Any]:
return json.loads(path.read_text())
def write_json(path: Path, obj: Any) -> None:
path.parent.mkdir(parents=True, exist_ok=True)
path.write_text(json.dumps(obj, indent=2, sort_keys=False))
def as_float(value: Any, default: float = 0.0) -> float:
if value is None or value == "":
return default
return float(value)
def as_bool(value: Any, default: bool = False) -> bool:
if value is None:
return default
if isinstance(value, bool):
return value
if isinstance(value, str):
return value.strip().lower() in {"1", "true", "yes", "y"}
return bool(value)
def clamp_rate(rate: float) -> float:
return max(0.0, min(1.0, rate))
def pct(x: Optional[float]) -> str:
if x is None or (isinstance(x, float) and math.isnan(x)):
return "n/a"
return f"{x * 100:.1f}%"
def money(x: Optional[float], units: str = "") -> str:
if x is None or (isinstance(x, float) and math.isnan(x)):
return "n/a"
return f"{x:,.1f}{' ' + units if units else ''}"
def period_value(mapping: Dict[str, Any], period: str, default: float = 0.0) -> float:
if not isinstance(mapping, dict):
return default
if period in mapping and mapping[period] is not None:
return float(mapping[period])
# fallback to exact year extraction when keys are FY2026E vs 2026
digits = "".join(ch for ch in period if ch.isdigit())
for key in (digits, f"FY{digits}", f"FY{digits}E"):
if key in mapping and mapping[key] is not None:
return float(mapping[key])
return default
def value_at_path(obj: Dict[str, Any], path: str) -> Any:
cur: Any = obj
for part in path.split("."):
if not isinstance(cur, dict) or part not in cur:
return None
cur = cur.get(part)
return cur
def has_material_value(value: Any) -> bool:
if value is None or value == "":
return False
if isinstance(value, dict):
return bool(value) and any(has_material_value(v) for v in value.values())
if isinstance(value, list):
return bool(value) and any(has_material_value(v) for v in value)
return True
def path_is_satisfied(plan: Dict[str, Any], path: str) -> bool:
if has_material_value(value_at_path(plan, path)):
return True
if path in {"transaction.offer_price", "target.offer_price"}:
return has_material_value(value_at_path(plan, "transaction.equity_purchase_price"))
return False
def get_periods(plan: Dict[str, Any]) -> List[str]:
periods = plan.get("periods") or []
if not isinstance(periods, list) or not periods:
raise ValueError("plan.periods must be a non-empty list")
return [str(p) for p in periods]
def source_labels_for_category(plan: Dict[str, Any], category: str) -> List[str]:
labels: List[str] = []
for src in plan.get("source_basis", []) or []:
cats = src.get("categories", []) if isinstance(src, dict) else []
if category in cats:
labels.append(str(src.get("label", "")))
return labels
def source_ids_for_category(plan: Dict[str, Any], category: str) -> List[str]:
ids: List[str] = []
for src in plan.get("source_basis", []) or []:
cats = src.get("categories", []) if isinstance(src, dict) else []
if category in cats:
ids.append(str(src.get("id", "")))
return ids
def primary_source_label(plan: Dict[str, Any], category: str) -> str:
labels = source_labels_for_category(plan, category)
return labels[0] if labels else "missing"
def primary_source_id(plan: Dict[str, Any], category: str) -> str:
ids = source_ids_for_category(plan, category)
return ids[0] if ids else "missing"
def normalize_plan(plan: Dict[str, Any], skill_root: Optional[Path] = None) -> Dict[str, Any]:
"""Return a normalized deep copy. Do not invent conclusion-changing defaults."""
out = copy.deepcopy(plan)
out.setdefault("meta", {})
out["meta"].setdefault("currency", "USD")
out["meta"].setdefault("units", "USD_mm")
out["meta"].setdefault("accounting_basis", "unknown")
out.setdefault("source_basis", [])
out.setdefault("sensitivities", {})
# Ensure fee fields exist with zeros only inside an already provided fees block.
fees = out.setdefault("transaction", {}).setdefault("fees", {})
for key in ["transaction_fees", "financing_fees", "equity_issuance_fees", "debt_tender_premiums", "bridge_fees", "consent_fees", "break_fees", "transfer_taxes"]:
fees.setdefault(key, 0.0)
# Ensure optional fair-value fields have explicit numeric zeros; these do not change the conclusion unless supplied.
ppa = out.setdefault("purchase_accounting", {})
for key in ["existing_goodwill", "ppe_step_up", "inventory_step_up", "other_fair_value_adjustments", "deferred_revenue_adjustment", "nci_fair_value", "previously_held_interest_fair_value", "contingent_consideration_fair_value"]:
ppa.setdefault(key, 0.0)
ppa.setdefault("intangible_assets", [])
# Normalize optional boolean flags.
tx = out.setdefault("transaction", {})
tx.setdefault("allow_unbalanced_consideration_mix", False)
tx.setdefault("include_transaction_fees_in_gaap_eps", True)
tx.setdefault("transaction_expense_tax_deductible", True)
cons = out.setdefault("consideration", {})
cons.setdefault("other_consideration_value", 0.0)
syn = out.setdefault("synergies", {})
syn.setdefault("revenue_synergy_margin", 0.0)
syn.setdefault("integration_costs_excluded_from_adjusted_eps", True)
return out
def build_timeline(plan: Dict[str, Any]) -> List[Dict[str, Any]]:
return [{"index": i, "period": p} for i, p in enumerate(get_periods(plan))]
def scenario_config(plan: Dict[str, Any], scenario_name: str) -> Dict[str, Any]:
cfg = copy.deepcopy((plan.get("scenarios") or {}).get(scenario_name, {}))
defaults = {
"description": scenario_name,
"acquirer_net_income_factor": 1.0,
"target_net_income_factor": 1.0,
"target_ebitda_factor": 1.0,
"synergy_factor": 1.0,
"dis_synergy_factor": 1.0,
"integration_cost_factor": 1.0,
"debt_rate_delta": 0.0,
"tax_rate_delta": 0.0,
"purchase_price_factor": 1.0,
"share_price_factor": 1.0,
"cash_percent_override": None,
}
defaults.update({k: v for k, v in cfg.items() if v is not None})
return defaults
def validate_consideration_mix(plan: Dict[str, Any], scenario: Optional[Dict[str, Any]] = None) -> Dict[str, float]:
cons = plan.get("consideration", {})
other_pct = as_float(cons.get("other_percent"), 0.0)
if scenario and scenario.get("cash_percent_override") is not None:
cash_pct = as_float(scenario.get("cash_percent_override"), 0.0)
stock_pct = max(0.0, 1.0 - cash_pct - other_pct)
else:
cash_pct = as_float(cons.get("cash_percent"), 0.0)
stock_pct = as_float(cons.get("stock_percent"), 0.0)
return {"cash_percent": cash_pct, "stock_percent": stock_pct, "other_percent": other_pct, "mix_sum": cash_pct + stock_pct + other_pct}
def target_equity_purchase_price(plan: Dict[str, Any], scenario: Dict[str, Any]) -> float:
tx = plan.get("transaction", {})
tgt = plan.get("target", {})
explicit = tx.get("equity_purchase_price")
if explicit is not None:
base = float(explicit)
else:
offer = float(tx.get("offer_price") if tx.get("offer_price") is not None else tgt.get("offer_price"))
base = offer * float(tgt.get("diluted_shares"))
return base * as_float(scenario.get("purchase_price_factor"), 1.0)
def compute_sources_uses(plan: Dict[str, Any], scenario_name: str = "base") -> Dict[str, Any]:
scenario = scenario_config(plan, scenario_name)
tgt = plan.get("target", {})
tx = plan.get("transaction", {})
cons = plan.get("consideration", {})
fin = plan.get("financing", {})
fees = tx.get("fees", {})
mix = validate_consideration_mix(plan, scenario)
equity_value = target_equity_purchase_price(plan, scenario)
cash_consideration = equity_value * mix["cash_percent"]
stock_consideration = equity_value * mix["stock_percent"]
other_consideration = equity_value * mix["other_percent"] + as_float(cons.get("other_consideration_value"), 0.0)
target_debt = as_float(tgt.get("debt"), 0.0)
target_cash = as_float(tgt.get("cash"), 0.0)
required_min_cash = as_float(tx.get("required_min_cash"), 0.0)
target_cash_used = max(target_cash - required_min_cash, 0.0) if as_bool(fin.get("use_target_cash"), False) else 0.0
target_debt_refinanced = target_debt if as_bool(tx.get("refinance_target_debt"), True) else 0.0
fee_values = {k: as_float(v, 0.0) for k, v in fees.items()}
transaction_fees = fee_values.get("transaction_fees", 0.0)
financing_fees = fee_values.get("financing_fees", 0.0)
equity_issuance_fees = fee_values.get("equity_issuance_fees", 0.0)
bridge_fees = fee_values.get("bridge_fees", 0.0)
debt_tender_premiums = fee_values.get("debt_tender_premiums", 0.0)
consent_fees = fee_values.get("consent_fees", 0.0)
break_fees = fee_values.get("break_fees", 0.0)
transfer_taxes = fee_values.get("transfer_taxes", 0.0)
uses = {
"cash_consideration": cash_consideration,
"stock_consideration": stock_consideration,
"other_consideration": other_consideration,
"target_debt_refinanced": target_debt_refinanced,
"transaction_fees": transaction_fees,
"financing_fees": financing_fees,
"equity_issuance_fees": equity_issuance_fees,
"bridge_fees": bridge_fees,
"debt_tender_premiums": debt_tender_premiums,
"consent_fees": consent_fees,
"break_fees": break_fees,
"transfer_taxes": transfer_taxes,
}
sources = {
"new_debt": as_float(fin.get("new_debt"), 0.0),
"acquirer_cash_used": as_float(fin.get("cash_used"), 0.0),
"target_cash_used": target_cash_used,
"stock_consideration": stock_consideration,
"other_consideration": other_consideration,
}
total_uses = sum(uses.values())
total_sources = sum(sources.values())
other_debt_like = as_float(tx.get("other_debt_like_items"), 0.0)
transaction_ev = equity_value + target_debt + other_debt_like - target_cash
undisturbed_price = as_float(tgt.get("undisturbed_price"), 0.0)
offer_price = equity_value / max(as_float(tgt.get("diluted_shares"), 0.0), TOLERANCE)
premium = (offer_price / undisturbed_price - 1.0) if undisturbed_price > 0 else None
return {
"scenario": scenario_name,
"scenario_config": scenario,
"mix": mix,
"equity_purchase_price": equity_value,
"offer_price": offer_price,
"premium": premium,
"transaction_ev": transaction_ev,
"uses": uses,
"sources": sources,
"total_uses": total_uses,
"total_sources": total_sources,
"balance_delta": total_sources - total_uses,
"target_cash_used": target_cash_used,
"target_debt_refinanced": target_debt_refinanced,
}
def compute_purchase_accounting(plan: Dict[str, Any], sources_uses: Dict[str, Any]) -> Dict[str, Any]:
ppa = plan.get("purchase_accounting", {})
tgt = plan.get("target", {})
target_book_equity = as_float(ppa.get("target_book_equity"), as_float(tgt.get("book_equity"), 0.0))
existing_goodwill = as_float(ppa.get("existing_goodwill"), 0.0)
intangible_assets = ppa.get("intangible_assets", []) or []
total_intangibles = sum(as_float(x.get("fair_value"), 0.0) for x in intangible_assets if isinstance(x, dict))
annual_intangible_amortization = 0.0
intangible_rows: List[Dict[str, Any]] = []
for asset in intangible_assets:
if not isinstance(asset, dict):
continue
fv = as_float(asset.get("fair_value"), 0.0)
life = as_float(asset.get("amortization_years"), 0.0)
amort = fv / life if life > 0 else 0.0
annual_intangible_amortization += amort
intangible_rows.append({"name": asset.get("name", "intangible"), "fair_value": fv, "life": life, "annual_amortization": amort})
ppe_step_up = as_float(ppa.get("ppe_step_up"), 0.0)
ppe_life = as_float(ppa.get("ppe_step_up_life"), 0.0)
ppe_incremental_depreciation = ppe_step_up / ppe_life if ppe_life > 0 else 0.0
inventory_step_up = as_float(ppa.get("inventory_step_up"), 0.0)
other_fv_adj = as_float(ppa.get("other_fair_value_adjustments"), 0.0)
deferred_revenue_adj = as_float(ppa.get("deferred_revenue_adjustment"), 0.0)
fair_value_stepups = total_intangibles + ppe_step_up + inventory_step_up + other_fv_adj + deferred_revenue_adj
provided_dtl = ppa.get("deferred_tax_liability")
dtl_rate = as_float(ppa.get("deferred_tax_rate"), 0.0)
deferred_tax_liability = as_float(provided_dtl) if provided_dtl is not None else max(fair_value_stepups, 0.0) * dtl_rate
identifiable_net_assets = target_book_equity - existing_goodwill + fair_value_stepups - deferred_tax_liability
consideration_transferred = sources_uses["equity_purchase_price"] + as_float(ppa.get("contingent_consideration_fair_value"), 0.0)
nci = as_float(ppa.get("nci_fair_value"), 0.0)
prev = as_float(ppa.get("previously_held_interest_fair_value"), 0.0)
goodwill = consideration_transferred + nci + prev - identifiable_net_assets
bridge_delta = goodwill - (consideration_transferred + nci + prev - identifiable_net_assets)
return {
"target_book_equity": target_book_equity,
"existing_goodwill": existing_goodwill,
"intangible_assets": intangible_rows,
"total_intangibles": total_intangibles,
"annual_intangible_amortization": annual_intangible_amortization,
"ppe_step_up": ppe_step_up,
"ppe_incremental_depreciation": ppe_incremental_depreciation,
"inventory_step_up": inventory_step_up,
"other_fair_value_adjustments": other_fv_adj,
"deferred_revenue_adjustment": deferred_revenue_adj,
"fair_value_stepups": fair_value_stepups,
"deferred_tax_liability": deferred_tax_liability,
"identifiable_net_assets": identifiable_net_assets,
"consideration_transferred": consideration_transferred,
"nci_fair_value": nci,
"previously_held_interest_fair_value": prev,
"goodwill": goodwill,
"bridge_delta": bridge_delta,
"bargain_purchase_gain": abs(goodwill) if goodwill < 0 else 0.0,
"measurement_period_status": ppa.get("measurement_period_status", "unknown"),
}
def compute_financing_effects(plan: Dict[str, Any], sources_uses: Dict[str, Any], scenario_name: str, periods: List[str]) -> Dict[str, Any]:
cfg = scenario_config(plan, scenario_name)
fin = plan.get("financing", {})
tx = plan.get("transaction", {})
fees = tx.get("fees", {})
debt_rate = max(0.0, as_float(fin.get("debt_interest_rate"), 0.0) + as_float(cfg.get("debt_rate_delta"), 0.0))
lost_cash_rate = max(0.0, as_float(fin.get("lost_cash_interest_rate"), 0.0))
new_debt = sources_uses["sources"].get("new_debt", 0.0)
cash_used = sources_uses["sources"].get("acquirer_cash_used", 0.0) + sources_uses["sources"].get("target_cash_used", 0.0)
interest_expense = new_debt * debt_rate
lost_interest = cash_used * lost_cash_rate
fee_years = max(as_float(fin.get("fee_amortization_years"), 1.0), TOLERANCE)
financing_fee_amort = as_float(fees.get("financing_fees"), 0.0) / fee_years
transaction_fees = as_float(fees.get("transaction_fees"), 0.0)
per_period: Dict[str, Dict[str, float]] = {}
for i, period in enumerate(periods):
per_period[period] = {
"new_debt": new_debt,
"debt_interest_rate": debt_rate,
"interest_expense": interest_expense,
"lost_cash_interest": lost_interest,
"financing_fee_amortization": financing_fee_amort,
"transaction_fees": transaction_fees if i == 0 else 0.0,
"financing_drag_pretax": interest_expense + lost_interest + financing_fee_amort,
}
return {"debt_interest_rate": debt_rate, "lost_cash_interest_rate": lost_cash_rate, "fee_amortization_years": fee_years, "per_period": per_period}
def compute_synergy_effects(plan: Dict[str, Any], scenario_name: str, periods: List[str]) -> Dict[str, Any]:
cfg = scenario_config(plan, scenario_name)
syn = plan.get("synergies", {})
synergy_factor = as_float(cfg.get("synergy_factor"), 1.0)
dis_factor = as_float(cfg.get("dis_synergy_factor"), 1.0)
integration_factor = as_float(cfg.get("integration_cost_factor"), 1.0)
tax_rate = clamp_rate(as_float(syn.get("tax_rate"), as_float(plan.get("transaction", {}).get("tax_rate"), 0.0)) + as_float(cfg.get("tax_rate_delta"), 0.0))
revenue_margin = as_float(syn.get("revenue_synergy_margin"), 0.0)
include_revenue = as_bool(syn.get("include_revenue_synergies_in_base"), True)
per_period: Dict[str, Dict[str, float]] = {}
for period in periods:
cost = period_value(syn.get("cost_synergies", {}), period, 0.0) * synergy_factor
revenue = period_value(syn.get("revenue_synergies", {}), period, 0.0) * synergy_factor if include_revenue else 0.0
revenue_contribution = revenue * revenue_margin
dis = period_value(syn.get("dis_synergies", {}), period, 0.0) * dis_factor
integration = period_value(syn.get("integration_costs", {}), period, 0.0) * integration_factor
net_pretax = cost + revenue_contribution - dis
per_period[period] = {
"cost_synergies": cost,
"revenue_synergies": revenue,
"revenue_synergy_contribution": revenue_contribution,
"dis_synergies": dis,
"net_pre_tax_synergies": net_pretax,
"integration_costs": integration,
"after_tax_synergy_contribution": net_pretax * (1 - tax_rate),
"after_tax_integration_costs": integration * (1 - tax_rate),
"tax_rate": tax_rate,
}
return {"tax_rate": tax_rate, "per_period": per_period}
def compute_pro_forma_shares(plan: Dict[str, Any], sources_uses: Dict[str, Any], scenario_name: str) -> Dict[str, Any]:
cfg = scenario_config(plan, scenario_name)
acq = plan.get("acquirer", {})
tgt = plan.get("target", {})
cons = plan.get("consideration", {})
acq_shares = as_float(acq.get("diluted_shares"), 0.0)
acq_share_price = as_float(acq.get("share_price"), 0.0) * as_float(cfg.get("share_price_factor"), 1.0)
stock_consideration = sources_uses["uses"].get("stock_consideration", 0.0)
exchange_ratio = cons.get("exchange_ratio")
if stock_consideration <= TOLERANCE:
shares_issued = 0.0
elif exchange_ratio is not None:
shares_issued = as_float(tgt.get("diluted_shares"), 0.0) * as_float(exchange_ratio)
else:
shares_issued = stock_consideration / acq_share_price if acq_share_price > 0 else float("nan")
pf_shares = acq_shares + shares_issued if not math.isnan(shares_issued) else float("nan")
acq_ownership = acq_shares / pf_shares if pf_shares and not math.isnan(pf_shares) else float("nan")
target_ownership = shares_issued / pf_shares if pf_shares and not math.isnan(pf_shares) else float("nan")
return {
"acquirer_existing_shares": acq_shares,
"acquirer_share_price": acq_share_price,
"stock_consideration": stock_consideration,
"shares_issued_to_target": shares_issued,
"pf_diluted_shares": pf_shares,
"acquirer_ownership": acq_ownership,
"target_ownership": target_ownership,
"ownership_sum": acq_ownership + target_ownership if not math.isnan(acq_ownership) and not math.isnan(target_ownership) else float("nan"),
}
def compute_pro_forma_net_income(plan: Dict[str, Any], scenario_name: str, ppa: Dict[str, Any], financing: Dict[str, Any], synergies: Dict[str, Any], shares: Dict[str, Any], periods: List[str]) -> Dict[str, Any]:
cfg = scenario_config(plan, scenario_name)
acq = plan.get("acquirer", {})
tgt = plan.get("target", {})
tx = plan.get("transaction", {})
ppa_plan = plan.get("purchase_accounting", {})
syn_plan = plan.get("synergies", {})
tax_rate = clamp_rate(as_float(tx.get("tax_rate"), synergies.get("tax_rate", 0.0)) + as_float(cfg.get("tax_rate_delta"), 0.0))
exclude_integration = as_bool(syn_plan.get("integration_costs_excluded_from_adjusted_eps"), True)
exclude_amort = as_bool(ppa_plan.get("adjusted_eps_excludes_intangible_amortization"), False)
include_tx_fees_gaap = as_bool(tx.get("include_transaction_fees_in_gaap_eps"), True)
tx_fee_deductible = as_bool(tx.get("transaction_expense_tax_deductible"), True)
transaction_fee_tax_effect = (1 - tax_rate) if tx_fee_deductible else 1.0
per_period: Dict[str, Dict[str, float]] = {}
for i, period in enumerate(periods):
acquirer_ni = period_value(acq.get("net_income", {}), period, 0.0) * as_float(cfg.get("acquirer_net_income_factor"), 1.0)
target_ni = period_value(tgt.get("net_income", {}), period, 0.0) * as_float(cfg.get("target_net_income_factor"), 1.0)
acquirer_eps = period_value(acq.get("eps", {}), period, 0.0)
if acquirer_eps == 0.0:
acquirer_eps = acquirer_ni / max(as_float(acq.get("diluted_shares"), 0.0), TOLERANCE)
fin_p = financing["per_period"][period]
syn_p = synergies["per_period"][period]
intangible_amort = ppa.get("annual_intangible_amortization", 0.0)
ppe_depr = ppa.get("ppe_incremental_depreciation", 0.0)
inventory_step = ppa.get("inventory_step_up", 0.0) if i == 0 else 0.0
purchase_accounting_pretax = intangible_amort + ppe_depr + inventory_step
after_tax_ppa = purchase_accounting_pretax * (1 - tax_rate)
after_tax_intangible_amort = intangible_amort * (1 - tax_rate)
after_tax_financing = fin_p["financing_drag_pretax"] * (1 - tax_rate)
tx_fees_after_tax = fin_p["transaction_fees"] * transaction_fee_tax_effect if include_tx_fees_gaap else 0.0
after_tax_synergy = syn_p["after_tax_synergy_contribution"]
after_tax_integration = syn_p["after_tax_integration_costs"]
pf_gaap_ni = acquirer_ni + target_ni + after_tax_synergy - after_tax_financing - after_tax_ppa - after_tax_integration - tx_fees_after_tax
adjusted_addbacks = 0.0
if exclude_integration:
adjusted_addbacks += after_tax_integration
# Transaction fees are always treated as one-time in adjusted EPS when included in GAAP.
adjusted_addbacks += tx_fees_after_tax
if exclude_amort:
adjusted_addbacks += after_tax_intangible_amort
# Inventory step-up is a one-time PPA cost; add back in adjusted view.
adjusted_addbacks += inventory_step * (1 - tax_rate)
pf_adjusted_ni = pf_gaap_ni + adjusted_addbacks
pf_shares = shares.get("pf_diluted_shares", float("nan"))
pf_gaap_eps = pf_gaap_ni / pf_shares if pf_shares and not math.isnan(pf_shares) else float("nan")
pf_adjusted_eps = pf_adjusted_ni / pf_shares if pf_shares and not math.isnan(pf_shares) else float("nan")
gaap_acc_dil = pf_gaap_eps / acquirer_eps - 1.0 if acquirer_eps else float("nan")
adjusted_acc_dil = pf_adjusted_eps / acquirer_eps - 1.0 if acquirer_eps else float("nan")
per_period[period] = {
"acquirer_net_income": acquirer_ni,
"target_net_income": target_ni,
"acquirer_standalone_eps": acquirer_eps,
"after_tax_synergy_contribution": after_tax_synergy,
"after_tax_financing_drag": after_tax_financing,
"after_tax_purchase_accounting_drag": after_tax_ppa,
"after_tax_integration_costs": after_tax_integration,
"after_tax_transaction_fees": tx_fees_after_tax,
"pf_gaap_net_income": pf_gaap_ni,
"adjusted_addbacks": adjusted_addbacks,
"pf_adjusted_net_income": pf_adjusted_ni,
"pf_diluted_shares": pf_shares,
"pf_gaap_eps": pf_gaap_eps,
"pf_adjusted_eps": pf_adjusted_eps,
"gaap_accretion_dilution": gaap_acc_dil,
"adjusted_accretion_dilution": adjusted_acc_dil,
"tax_rate": tax_rate,
"purchase_accounting_pretax_expense": purchase_accounting_pretax,
"intangible_amortization": intangible_amort,
"ppe_incremental_depreciation": ppe_depr,
"inventory_step_up_expense": inventory_step,
}
return {"tax_rate": tax_rate, "per_period": per_period}
def run_single_scenario(plan: Dict[str, Any], scenario_name: str) -> Dict[str, Any]:
periods = get_periods(plan)
sources_uses = compute_sources_uses(plan, scenario_name)
ppa = compute_purchase_accounting(plan, sources_uses)
financing = compute_financing_effects(plan, sources_uses, scenario_name, periods)
synergies = compute_synergy_effects(plan, scenario_name, periods)
shares = compute_pro_forma_shares(plan, sources_uses, scenario_name)
pro_forma = compute_pro_forma_net_income(plan, scenario_name, ppa, financing, synergies, shares, periods)
return {
"scenario": scenario_name,
"sources_uses": sources_uses,
"purchase_accounting": ppa,
"financing": financing,
"synergies": synergies,
"shares": shares,
"pro_forma": pro_forma,
}
def run_scenarios(plan: Dict[str, Any]) -> Dict[str, Any]:
names = ["base", "downside", "upside"]
return {name: run_single_scenario(plan, name) for name in names}
def with_scenario_override(plan: Dict[str, Any], scenario_name: str, overrides: Dict[str, Any]) -> Dict[str, Any]:
out = copy.deepcopy(plan)
scen = out.setdefault("scenarios", {}).setdefault(scenario_name, {})
scen.update(overrides)
return out
def run_sensitivities(plan: Dict[str, Any], primary_period: Optional[str] = None) -> List[Dict[str, Any]]:
periods = get_periods(plan)
period = primary_period or (periods[1] if len(periods) > 1 else periods[0])
sens = plan.get("sensitivities", {}) or {}
rows: List[Dict[str, Any]] = []
def add_case(case_group: str, variable: str, value: float, p: Dict[str, Any]) -> None:
res = run_single_scenario(p, "base")
pf = res["pro_forma"]["per_period"][period]
rows.append({
"case_group": case_group,
"variable": variable,
"value": value,
"period": period,
"pf_adjusted_eps": pf.get("pf_adjusted_eps"),
"adjusted_accretion_dilution": pf.get("adjusted_accretion_dilution"),
"pf_gaap_eps": pf.get("pf_gaap_eps"),
"gaap_accretion_dilution": pf.get("gaap_accretion_dilution"),
})
for v in sens.get("synergy_factors", []):
add_case("synergy", "synergy_factor", float(v), with_scenario_override(plan, "base", {"synergy_factor": float(v)}))
for v in sens.get("debt_rate_deltas", []):
add_case("debt_rate", "debt_rate_delta", float(v), with_scenario_override(plan, "base", {"debt_rate_delta": float(v)}))
for v in sens.get("cash_percentages", []):
add_case("consideration_mix", "cash_percent", float(v), with_scenario_override(plan, "base", {"cash_percent_override": float(v)}))
for v in sens.get("premium_factors", []):
add_case("premium", "purchase_price_factor", float(v), with_scenario_override(plan, "base", {"purchase_price_factor": float(v)}))
for v in sens.get("tax_rates", []):
base_tax = as_float(plan.get("transaction", {}).get("tax_rate"), 0.0)
add_case("tax_rate", "tax_rate", float(v), with_scenario_override(plan, "base", {"tax_rate_delta": float(v) - base_tax}))
for v in sens.get("share_price_factors", []):
add_case("share_price", "share_price_factor", float(v), with_scenario_override(plan, "base", {"share_price_factor": float(v)}))
return rows
def compute_synergy_breakeven(plan: Dict[str, Any], primary_period: Optional[str] = None) -> Dict[str, Any]:
periods = get_periods(plan)
period = primary_period or (periods[1] if len(periods) > 1 else periods[0])
no_syn_plan = with_scenario_override(plan, "base", {"synergy_factor": 0.0})
res = run_single_scenario(no_syn_plan, "base")
pf = res["pro_forma"]["per_period"][period]
pf_shares = pf["pf_diluted_shares"]
standalone_eps = pf["acquirer_standalone_eps"]
required_ni = standalone_eps * pf_shares
deficit = required_ni - pf["pf_adjusted_net_income"]
tax_rate = pf.get("tax_rate", as_float(plan.get("transaction", {}).get("tax_rate"), 0.0))
required_pretax_synergy = max(0.0, deficit / max(1 - tax_rate, TOLERANCE))
return {
"period": period,
"required_pre_tax_synergy_for_adjusted_eps_neutrality": required_pretax_synergy,
"standalone_eps": standalone_eps,
"pf_adjusted_eps_without_synergies": pf["pf_adjusted_eps"],
}
def source_coverage(plan: Dict[str, Any]) -> Dict[str, Any]:
present = set()
labels_by_category: Dict[str, List[str]] = {}
for src in plan.get("source_basis", []) or []:
if not isinstance(src, dict):
continue
label = str(src.get("label", ""))
for cat in src.get("categories", []) or []:
present.add(str(cat))
labels_by_category.setdefault(str(cat), []).append(label)
missing = sorted(REQUIRED_SOURCE_CATEGORIES - present)
low_conf = sorted({cat for cat, labels in labels_by_category.items() if any(label in LOW_CONFIDENCE_LABELS for label in labels)})
return {"present_categories": sorted(present), "missing_categories": missing, "labels_by_category": labels_by_category, "low_confidence_categories": low_conf}
def identify_missing_inputs(plan: Dict[str, Any]) -> List[Dict[str, Any]]:
"""Return missing or low-confidence inputs that should be requested from the user."""
coverage = source_coverage(plan)
missing_items: List[Dict[str, Any]] = []
for req in MATERIAL_INPUT_REQUIREMENTS:
categories = [str(c) for c in req["categories"]]
missing_categories = [cat for cat in categories if cat in coverage["missing_categories"]]
low_confidence_categories = [cat for cat in categories if cat in coverage["low_confidence_categories"]]
labels: List[str] = []
for cat in categories:
labels.extend(coverage["labels_by_category"].get(cat, []))
missing_fields = [path for path in req["paths"] if not path_is_satisfied(plan, path)]
if not missing_categories and not low_confidence_categories and not missing_fields:
continue
problems: List[str] = []
if missing_categories:
problems.append("missing source categories: " + ", ".join(missing_categories))
if low_confidence_categories:
problems.append("low-confidence source categories: " + ", ".join(low_confidence_categories))
if missing_fields:
problems.append("missing fields: " + ", ".join(missing_fields))
current_treatment = "; ".join(problems)
if labels:
current_treatment += f"; evidence labels: {', '.join(sorted(set(labels)))}"
missing_items.append({
"item": req["item"],
"source_categories": ", ".join(categories),
"missing_fields": ", ".join(missing_fields),
"evidence_labels": ", ".join(sorted(set(labels))) if labels else "missing",
"why_needed": req["why_needed"],
"current_treatment": current_treatment,
"user_ask": f"Do you have source support for {req['item'].lower()}?",
"fallback_method": req["fallback_method"],
})
return missing_items
def partial_context_warning(missing_inputs: List[Dict[str, Any]]) -> str:
if missing_inputs:
return PARTIAL_CONTEXT_WARNING
return ""
def missing_inputs_to_rows(missing_inputs: List[Dict[str, Any]]) -> List[List[Any]]:
rows = [["item", "source_categories", "missing_fields", "evidence_labels", "why_needed", "current_treatment", "user_ask", "fallback_method"]]
if not missing_inputs:
rows.append(["No missing or low-confidence material inputs detected", "", "", "", "", "", "", ""])
return rows
for item in missing_inputs:
rows.append([
item.get("item", ""),
item.get("source_categories", ""),
item.get("missing_fields", ""),
item.get("evidence_labels", ""),
item.get("why_needed", ""),
item.get("current_treatment", ""),
item.get("user_ask", ""),
item.get("fallback_method", ""),
])
return rows
def _date_to_tuple(s: str) -> Optional[Tuple[int, int, int]]:
try:
parts = s.split("-")
if len(parts) != 3:
return None
return (int(parts[0]), int(parts[1]), int(parts[2]))
except Exception:
return None
def _date_ord(t: Tuple[int, int, int]) -> int:
# simple enough for stale-date diagnostics, not actual calendar math
y, m, d = t
return y * 372 + m * 31 + d
def compute_checks(plan: Dict[str, Any], results: Dict[str, Any], sensitivities: List[Dict[str, Any]]) -> Tuple[Dict[str, Any], List[str], List[str]]:
hard_failures: List[str] = []
warnings: List[str] = []
base = results["base"]
su = base["sources_uses"]
mix = su["mix"]
shares = base["shares"]
ppa = base["purchase_accounting"]
checks: Dict[str, Any] = {}
checks["sources_equal_uses"] = {"ok": abs(su["balance_delta"]) <= 0.01, "delta": su["balance_delta"]}
if not checks["sources_equal_uses"]["ok"]:
hard_failures.append(f"sources and uses do not balance; delta is {su['balance_delta']:.4f}")
allow_mix_override = as_bool(plan.get("transaction", {}).get("allow_unbalanced_consideration_mix"), False)
checks["consideration_mix_sum"] = {"ok": allow_mix_override or abs(mix["mix_sum"] - 1.0) <= 0.0001, "sum": mix["mix_sum"]}
if not checks["consideration_mix_sum"]["ok"]:
hard_failures.append(f"consideration mix sums to {mix['mix_sum']:.4f}, not 1.0000")
stock_used = mix["stock_percent"] > 0.0001
checks["stock_consideration_share_support"] = {"ok": (not stock_used) or (shares.get("pf_diluted_shares") and not math.isnan(shares.get("pf_diluted_shares"))), "pf_shares": shares.get("pf_diluted_shares")}
if not checks["stock_consideration_share_support"]["ok"]:
hard_failures.append("stock consideration is used but pro forma shares cannot be calculated")
pf_eps_ok = True
for scenario_name, res in results.items():
for period, vals in res["pro_forma"]["per_period"].items():
if math.isnan(vals.get("pf_adjusted_eps", float("nan"))) or math.isnan(vals.get("pf_gaap_eps", float("nan"))):
pf_eps_ok = False
checks["pf_eps_calculable"] = {"ok": pf_eps_ok}
if not pf_eps_ok:
hard_failures.append("pro forma EPS cannot be calculated for all scenarios and periods")
checks["purchase_accounting_bridge_reconciles"] = {"ok": abs(ppa.get("bridge_delta", 0.0)) <= 0.01, "delta": ppa.get("bridge_delta", 0.0)}
if not checks["purchase_accounting_bridge_reconciles"]["ok"]:
hard_failures.append("purchase accounting bridge does not reconcile")
ownership_sum = shares.get("ownership_sum", float("nan"))
checks["ownership_sums_to_100pct"] = {"ok": not math.isnan(ownership_sum) and abs(ownership_sum - 1.0) <= 0.0001, "sum": ownership_sum}
if not checks["ownership_sums_to_100pct"]["ok"]:
hard_failures.append("pro forma ownership does not sum to 100%")
coverage = source_coverage(plan)
checks["required_source_categories_present"] = {"ok": not coverage["missing_categories"], **coverage}
if coverage["missing_categories"]:
hard_failures.append("missing required source categories: " + ", ".join(coverage["missing_categories"]))
if coverage["low_confidence_categories"]:
warnings.append("low-confidence evidence labels are used for: " + ", ".join(coverage["low_confidence_categories"]))
# Sensitivity directionality.
syn_rows = sorted([r for r in sensitivities if r["case_group"] == "synergy"], key=lambda r: r["value"])
if len(syn_rows) >= 2:
ok = syn_rows[-1]["pf_adjusted_eps"] + 1e-9 >= syn_rows[0]["pf_adjusted_eps"]
checks["synergy_directionality"] = {"ok": ok, "low_factor_eps": syn_rows[0]["pf_adjusted_eps"], "high_factor_eps": syn_rows[-1]["pf_adjusted_eps"]}
if not ok:
hard_failures.append("accretion/dilution directionality fails: higher synergies lower adjusted EPS")
else:
checks["synergy_directionality"] = {"ok": True, "note": "insufficient sensitivity rows"}
rate_rows = sorted([r for r in sensitivities if r["case_group"] == "debt_rate"], key=lambda r: r["value"])
if len(rate_rows) >= 2:
ok = rate_rows[-1]["pf_adjusted_eps"] <= rate_rows[0]["pf_adjusted_eps"] + 1e-9
checks["debt_rate_directionality"] = {"ok": ok, "low_rate_eps": rate_rows[0]["pf_adjusted_eps"], "high_rate_eps": rate_rows[-1]["pf_adjusted_eps"]}
if not ok:
hard_failures.append("accretion/dilution directionality fails: higher debt cost increases adjusted EPS")
else:
checks["debt_rate_directionality"] = {"ok": True, "note": "insufficient sensitivity rows"}
# Senior warnings.
syn = plan.get("synergies", {})
if syn.get("realization_basis") == "immediate":
warnings.append("synergies are treated as immediate; senior review should test phased realization")
if not any(period_value(syn.get("integration_costs", {}), p, 0.0) > 0 for p in get_periods(plan)):
warnings.append("integration costs are zero or missing; synergy economics may be overstated")
if as_bool(syn.get("integration_costs_excluded_from_adjusted_eps"), True):
warnings.append("integration costs are excluded from adjusted EPS; review cash cost and payback separately")
if as_float(plan.get("transaction", {}).get("fees", {}).get("transaction_fees"), 0.0) > 0:
warnings.append("one-time transaction fees are excluded from adjusted EPS but included in GAAP view")
if plan.get("purchase_accounting", {}).get("deferred_tax_liability") is None:
warnings.append("deferred tax liability is model-derived from fair-value step-ups and should be confirmed by tax/accounting advisors")
if plan.get("purchase_accounting", {}).get("measurement_period_status", "").lower() in {"preliminary", "unknown", "provisional"}:
warnings.append("purchase accounting is preliminary/provisional")
if primary_source_label(plan, "financing") in LOW_CONFIDENCE_LABELS:
warnings.append("financing terms are placeholders or low-confidence assumptions")
# Share price/share count source-date consistency.
relevant_dates: List[int] = []
for src in plan.get("source_basis", []) or []:
if not isinstance(src, dict):
continue
cats = src.get("categories", []) or []
if "share_count" in cats or "market_data" in cats:
dt = _date_to_tuple(str(src.get("date", "")))
if dt:
relevant_dates.append(_date_ord(dt))
if len(relevant_dates) >= 2 and max(relevant_dates) - min(relevant_dates) > 45:
warnings.append("share price/share count source dates may conflict; verify market data as-of dates")
# Accretive only with synergies warning.
periods = get_periods(plan)
primary_period = periods[1] if len(periods) > 1 else periods[0]
base_acc = base["pro_forma"]["per_period"][primary_period]["adjusted_accretion_dilution"]
no_syn = run_single_scenario(with_scenario_override(plan, "base", {"synergy_factor": 0.0}), "base")
no_syn_acc = no_syn["pro_forma"]["per_period"][primary_period]["adjusted_accretion_dilution"]
checks["accretive_without_synergies"] = {"ok": no_syn_acc >= 0, "adjusted_acc_dil_without_synergies": no_syn_acc, "base_adjusted_acc_dil": base_acc}
if base_acc >= 0 and no_syn_acc < 0:
warnings.append("deal is accretive only after synergies in the primary period")
if base["pro_forma"]["per_period"][primary_period]["gaap_accretion_dilution"] < 0 and base_acc > 0:
warnings.append("deal is accretive on adjusted EPS but dilutive on GAAP EPS in the primary period")
return checks, hard_failures, warnings
def determine_model_status(plan: Dict[str, Any], hard_failures: List[str], warnings: List[str]) -> str:
if hard_failures:
return "not-decision-ready"
labels = [str(src.get("label", "")) for src in plan.get("source_basis", []) if isinstance(src, dict)]
if any(label in {"placeholder", "unsupported"} for label in labels):
return "screen-grade"
if any(label in {"estimate", "assumption"} for label in labels):
return "screen-grade"
if warnings:
return "screen-grade"
review_standard = str(plan.get("meta", {}).get("review_standard", "")).lower()
if review_standard == "decision-grade" and all(label in HIGH_CONFIDENCE_LABELS for label in labels):
return "decision-grade"
return "senior-review-ready"
def build_p0_handoff(plan: Dict[str, Any], results: Dict[str, Any], breakeven: Dict[str, Any], model_status: str, output_dir: Path, warnings: List[str], hard_failures: List[str], missing_inputs: Optional[List[Dict[str, Any]]] = None, include_report_md: bool = True) -> Dict[str, Any]:
periods = get_periods(plan)
primary_period = periods[1] if len(periods) > 1 else periods[0]
base = results["base"]
acc: Dict[str, Any] = {}
for scen, res in results.items():
pf = res["pro_forma"]["per_period"][primary_period]
acc[scen] = {
"period": primary_period,
"gaap_accretion_dilution": pf["gaap_accretion_dilution"],
"adjusted_accretion_dilution": pf["adjusted_accretion_dilution"],
"pf_gaap_eps": pf["pf_gaap_eps"],
"pf_adjusted_eps": pf["pf_adjusted_eps"],
}
su = base["sources_uses"]
shares = base["shares"]
top_risks = hard_failures[:3] + warnings[:5]
output_paths = {
"model_xlsx": str(output_dir / "model.xlsx"),
"plan_json": str(output_dir / "plan.json"),
"run_log_json": str(output_dir / "run_log.json"),
}
if include_report_md:
output_paths["report_md"] = str(output_dir / "report.md")
return {
"deal_name": plan.get("meta", {}).get("deal_name"),
"transaction_value": {"equity_purchase_price": su["equity_purchase_price"], "enterprise_value": su["transaction_ev"]},
"premium": su.get("premium"),
"consideration_mix": su.get("mix"),
"pf_ownership": {"acquirer_ownership": shares.get("acquirer_ownership"), "target_ownership": shares.get("target_ownership")},
"accretion_dilution": acc,
"synergy_breakeven": breakeven,
"financing_assumptions": {
"new_debt": su["sources"].get("new_debt"),
"acquirer_cash_used": su["sources"].get("acquirer_cash_used"),
"target_cash_used": su["sources"].get("target_cash_used"),
"debt_interest_rate": base["financing"].get("debt_interest_rate"),
},
"top_risks_and_caveats": top_risks,
"missing_input_request": {
"posture_warning": partial_context_warning(missing_inputs or []),
"items": missing_inputs or [],
},
"model_status": model_status,
"output_paths": output_paths,
}
def assumption_rows(plan: Dict[str, Any]) -> List[List[Any]]:
rows = [["category", "source_id", "evidence_label", "date", "priority", "description", "categories"]]
for src in plan.get("source_basis", []) or []:
rows.append([
"source_basis",
src.get("id", ""),
src.get("label", ""),
src.get("date", ""),
src.get("priority", ""),
src.get("description", ""),
", ".join(src.get("categories", []) or []),
])
rows.append(["meta", "deal_name", "user_provided", "", "", plan.get("meta", {}).get("deal_name", ""), ""])
rows.append(["meta", "accounting_basis", "user_provided", "", "", plan.get("meta", {}).get("accounting_basis", ""), ""])
return rows
def to_model_rows(plan: Dict[str, Any], results: Dict[str, Any], sensitivities: List[Dict[str, Any]], checks: Dict[str, Any], hard_failures: List[str], warnings: List[str], breakeven: Dict[str, Any], model_status: str, missing_inputs: Optional[List[Dict[str, Any]]] = None) -> Dict[str, List[List[Any]]]:
units = plan.get("meta", {}).get("units", "")
periods = get_periods(plan)
primary_period = periods[1] if len(periods) > 1 else periods[0]
sheets: Dict[str, List[List[Any]]] = {}
missing_inputs = missing_inputs or []
posture_warning = partial_context_warning(missing_inputs)
# Output summary.
summary = [["metric", "base", "downside", "upside", "units", "notes"]]
if posture_warning:
summary.extend([
["Posture warning", posture_warning, "", "", "", "before relying on any metric table"],
["Missing / low-confidence input count", len(missing_inputs), "", "", "count", "see MISSING_INPUTS"],
["Ask user for", "; ".join(item.get("item", "") for item in missing_inputs[:8]), "", "", "", "request these source items before upgrading posture"],
["Fallback policy", "If unavailable, update assumptions using the best estimate supported by the provided facts and label the source as estimate or placeholder.", "", "", "", ""],
["", "", "", "", "", ""],
])
for metric_key, label in [
("pf_adjusted_eps", "PF adjusted EPS"),
("adjusted_accretion_dilution", "Adjusted accretion/dilution"),
("pf_gaap_eps", "PF GAAP EPS"),
("gaap_accretion_dilution", "GAAP accretion/dilution"),
]:
row = [label]
for scen in ["base", "downside", "upside"]:
row.append(results[scen]["pro_forma"]["per_period"][primary_period][metric_key])
row += ["x" if "EPS" in label else "%", primary_period]
summary.append(row)
su = results["base"]["sources_uses"]
shares = results["base"]["shares"]
summary.extend([
["Model status", model_status, "", "", "", ""],
["Equity purchase price", su["equity_purchase_price"], "", "", units, "base case"],
["Transaction enterprise value", su["transaction_ev"], "", "", units, "base case"],
["Premium", su.get("premium"), "", "", "%", "base case"],
["Acquirer ownership", shares.get("acquirer_ownership"), "", "", "%", "base case"],
["Target ownership", shares.get("target_ownership"), "", "", "%", "base case"],
["Synergy breakeven", breakeven.get("required_pre_tax_synergy_for_adjusted_eps_neutrality"), "", "", units, breakeven.get("period")],
["Hard failures", len(hard_failures), "", "", "count", "; ".join(hard_failures[:3])],
["Warnings", len(warnings), "", "", "count", "; ".join(warnings[:3])],
])
sheets["Executive Summary"] = summary
sheets["ASSUMPTIONS"] = assumption_rows(plan)
sheets["MISSING_INPUTS"] = missing_inputs_to_rows(missing_inputs)
# Standalone.
standalone = [["scenario", "period", "company", "line_item", "value", "units", "evidence_label", "source_id", "notes"]]
for period in periods:
standalone.extend([
["base", period, "acquirer", "net_income", period_value(plan.get("acquirer", {}).get("net_income", {}), period), units, primary_source_label(plan, "financials"), primary_source_id(plan, "financials"), "standalone"],
["base", period, "acquirer", "eps", period_value(plan.get("acquirer", {}).get("eps", {}), period), "x", primary_source_label(plan, "share_count"), primary_source_id(plan, "share_count"), "standalone"],
["base", period, "target", "net_income", period_value(plan.get("target", {}).get("net_income", {}), period), units, primary_source_label(plan, "financials"), primary_source_id(plan, "financials"), "standalone"],
["base", period, "target", "ebitda", period_value(plan.get("target", {}).get("standalone_ebitda", {}), period), units, primary_source_label(plan, "financials"), primary_source_id(plan, "financials"), "standalone"],
])
standalone.extend([
["base", "close", "acquirer", "diluted_shares", as_float(plan.get("acquirer", {}).get("diluted_shares")), "mm", primary_source_label(plan, "share_count"), primary_source_id(plan, "share_count"), ""],
["base", "close", "acquirer", "cash", as_float(plan.get("acquirer", {}).get("cash")), units, primary_source_label(plan, "financials"), primary_source_id(plan, "financials"), ""],
["base", "close", "acquirer", "debt", as_float(plan.get("acquirer", {}).get("debt")), units, primary_source_label(plan, "financials"), primary_source_id(plan, "financials"), ""],
["base", "close", "target", "diluted_shares", as_float(plan.get("target", {}).get("diluted_shares")), "mm", primary_source_label(plan, "share_count"), primary_source_id(plan, "share_count"), ""],
["base", "close", "target", "cash", as_float(plan.get("target", {}).get("cash")), units, primary_source_label(plan, "financials"), primary_source_id(plan, "financials"), ""],
["base", "close", "target", "debt", as_float(plan.get("target", {}).get("debt")), units, primary_source_label(plan, "financials"), primary_source_id(plan, "financials"), ""],
])
sheets["STANDALONE"] = standalone
# Sources and uses.
su_rows = [["scenario", "side", "line_item", "value", "units", "evidence_label", "source_id", "notes"]]
for scen, res in results.items():
scenario_su = res["sources_uses"]
for k, v in scenario_su["uses"].items():
su_rows.append([scen, "uses", k, v, units, primary_source_label(plan, "offer_terms"), primary_source_id(plan, "offer_terms"), ""])
for k, v in scenario_su["sources"].items():
cat = "financing" if k in {"new_debt", "acquirer_cash_used", "target_cash_used"} else "offer_terms"
su_rows.append([scen, "sources", k, v, units, primary_source_label(plan, cat), primary_source_id(plan, cat), ""])
su_rows.extend([
[scen, "check", "total_uses", scenario_su["total_uses"], units, "model", "", ""],
[scen, "check", "total_sources", scenario_su["total_sources"], units, "model", "", ""],
[scen, "check", "balance_delta", scenario_su["balance_delta"], units, "model", "", "sources minus uses"],
[scen, "valuation", "equity_purchase_price", scenario_su["equity_purchase_price"], units, primary_source_label(plan, "offer_terms"), primary_source_id(plan, "offer_terms"), ""],
[scen, "valuation", "transaction_enterprise_value", scenario_su["transaction_ev"], units, primary_source_label(plan, "offer_terms"), primary_source_id(plan, "offer_terms"), ""],
[scen, "valuation", "premium", scenario_su.get("premium"), "%", primary_source_label(plan, "offer_terms"), primary_source_id(plan, "offer_terms"), ""],
])
sheets["SOURCES_USES"] = su_rows
# Purchase accounting.
ppa_rows = [["scenario", "period", "section", "line_item", "value", "units", "evidence_label", "source_id", "notes"]]
for scen, res in results.items():
p = res["purchase_accounting"]
for k in ["target_book_equity", "existing_goodwill", "total_intangibles", "ppe_step_up", "inventory_step_up", "other_fair_value_adjustments", "deferred_revenue_adjustment", "deferred_tax_liability", "identifiable_net_assets", "consideration_transferred", "nci_fair_value", "previously_held_interest_fair_value", "goodwill", "bargain_purchase_gain"]:
ppa_rows.append([scen, "close", "purchase_accounting", k, p.get(k), units, primary_source_label(plan, "purchase_accounting"), primary_source_id(plan, "purchase_accounting"), ""])
for asset in p.get("intangible_assets", []):
ppa_rows.append([scen, "close", "intangible_asset", asset.get("name"), asset.get("fair_value"), units, primary_source_label(plan, "purchase_accounting"), primary_source_id(plan, "purchase_accounting"), f"life={asset.get('life')} yrs; amort={asset.get('annual_amortization'):.4f}"])
for period in periods:
inv = p.get("inventory_step_up", 0.0) if period == periods[0] else 0.0
ppa_rows.extend([
[scen, period, "purchase_accounting", "intangible_amortization", p.get("annual_intangible_amortization"), units, primary_source_label(plan, "purchase_accounting"), primary_source_id(plan, "purchase_accounting"), "annual"],
[scen, period, "purchase_accounting", "incremental_ppe_depreciation", p.get("ppe_incremental_depreciation"), units, primary_source_label(plan, "purchase_accounting"), primary_source_id(plan, "purchase_accounting"), "annual"],
[scen, period, "purchase_accounting", "inventory_step_up_expense", inv, units, primary_source_label(plan, "purchase_accounting"), primary_source_id(plan, "purchase_accounting"), "first period only"],
])
sheets["PURCHASE_ACCOUNTING"] = ppa_rows
# Financing.
fin_rows = [["scenario", "period", "line_item", "value", "units", "evidence_label", "source_id", "notes"]]
for scen, res in results.items():
for period, vals in res["financing"]["per_period"].items():
for k, v in vals.items():
fin_rows.append([scen, period, k, v, units if "rate" not in k else "%", primary_source_label(plan, "financing"), primary_source_id(plan, "financing"), ""])
sheets["FINANCING"] = fin_rows
# Synergies.
syn_rows = [["scenario", "period", "line_item", "value", "units", "evidence_label", "source_id", "notes"]]
for scen, res in results.items():
for period, vals in res["synergies"]["per_period"].items():
for k, v in vals.items():
syn_rows.append([scen, period, k, v, units if "rate" not in k else "%", primary_source_label(plan, "synergies"), primary_source_id(plan, "synergies"), ""])
sheets["SYNERGIES"] = syn_rows
# Pro forma and accretion.
pf_rows = [["scenario", "period", "line_item", "value", "units", "evidence_label", "source_id", "notes"]]
acc_rows = [["scenario", "period", "line_item", "value", "units", "evidence_label", "source_id", "notes"]]
for scen, res in results.items():
for period, vals in res["pro_forma"]["per_period"].items():
for k, v in vals.items():
pf_rows.append([scen, period, k, v, units if "eps" not in k and "rate" not in k and "dilution" not in k else ("x" if "eps" in k else "%"), "model", "", ""])
acc_rows.extend([
[scen, period, "standalone_acquirer_eps", vals.get("acquirer_standalone_eps"), "x", primary_source_label(plan, "financials"), primary_source_id(plan, "financials"), ""],
[scen, period, "pf_gaap_eps", vals.get("pf_gaap_eps"), "x", "model", "", ""],
[scen, period, "pf_adjusted_eps", vals.get("pf_adjusted_eps"), "x", "model", "", ""],
[scen, period, "gaap_accretion_dilution", vals.get("gaap_accretion_dilution"), "%", "model", "", ""],
[scen, period, "adjusted_accretion_dilution", vals.get("adjusted_accretion_dilution"), "%", "model", "", ""],
])
acc_rows.append(["base", breakeven.get("period"), "synergy_breakeven", breakeven.get("required_pre_tax_synergy_for_adjusted_eps_neutrality"), units, "model", "", "pre-tax synergy required for adjusted EPS neutrality"])
sheets["PRO_FORMA"] = pf_rows
sheets["ACCRETION_DILUTION"] = acc_rows
# Ownership.
own_rows = [["scenario", "line_item", "value", "units", "evidence_label", "source_id", "notes"]]
for scen, res in results.items():
for k, v in res["shares"].items():
own_rows.append([scen, k, v, "%" if "ownership" in k else "mm", primary_source_label(plan, "share_count"), primary_source_id(plan, "share_count"), ""])
sheets["OWNERSHIP"] = own_rows
# Sensitivities.
sens_rows = [["case_group", "variable", "value", "period", "pf_adjusted_eps", "adjusted_accretion_dilution", "pf_gaap_eps", "gaap_accretion_dilution"]]
for row in sensitivities:
sens_rows.append([row.get("case_group"), row.get("variable"), row.get("value"), row.get("period"), row.get("pf_adjusted_eps"), row.get("adjusted_accretion_dilution"), row.get("pf_gaap_eps"), row.get("gaap_accretion_dilution")])
sheets["SENSITIVITY"] = sens_rows
# Checks.
check_rows = [["check", "ok", "value", "notes"]]
for k, v in checks.items():
if isinstance(v, dict):
ok = v.get("ok", "")
value = json.dumps({kk: vv for kk, vv in v.items() if kk != "ok"}, sort_keys=True)
else:
ok = ""
value = v
check_rows.append([k, ok, value, ""])
for f in hard_failures:
check_rows.append(["hard_failure", False, f, "breaks decision readiness"])
for w in warnings:
check_rows.append(["warning", True, w, "review before senior use"])
sheets["CHECKS"] = check_rows
return sheets
def render_report(plan: Dict[str, Any], results: Dict[str, Any], checks: Dict[str, Any], hard_failures: List[str], warnings: List[str], breakeven: Dict[str, Any], model_status: str, output_dir: Path, missing_inputs: Optional[List[Dict[str, Any]]] = None, include_report_md: bool = True) -> str:
meta = plan.get("meta", {})
units = meta.get("units", "")
periods = get_periods(plan)
primary_period = periods[1] if len(periods) > 1 else periods[0]
base = results["base"]
su = base["sources_uses"]
shares = base["shares"]
ppa = base["purchase_accounting"]
missing_inputs = missing_inputs or []
posture_warning = partial_context_warning(missing_inputs)
lines: List[str] = []
lines.append(f"# {meta.get('deal_name', 'merger model')} — merger model summary")
lines.append("")
if posture_warning:
lines.append(f"> **{posture_warning}**")
lines.append("")
lines.append("Before relying on the metric tables below, ask the user whether they have the missing or low-confidence support listed here. If they do not, proceed only by updating the model assumptions from the best available facts provided and keeping those assumptions labeled as `estimate` or `placeholder`.")
lines.append("")
lines.append("| information still needed | current treatment | fallback if unavailable |")
lines.append("|---|---|---|")
for item in missing_inputs:
lines.append(f"| {item.get('item', '')} | {item.get('current_treatment', '')} | {item.get('fallback_method', '')} |")
lines.append("")
lines.append("## 1) executive summary")
base_pf = base["pro_forma"]["per_period"][primary_period]
lines.append(
f"{meta.get('acquirer', 'acquirer')} buying {meta.get('target', 'target')} implies equity purchase price of "
f"{money(su['equity_purchase_price'], units)} and transaction EV of {money(su['transaction_ev'], units)}. "
f"Base-case adjusted accretion/dilution in {primary_period} is {pct(base_pf['adjusted_accretion_dilution'])}; "
f"GAAP accretion/dilution is {pct(base_pf['gaap_accretion_dilution'])}. "
f"PF ownership is {pct(shares['acquirer_ownership'])} existing acquirer holders / {pct(shares['target_ownership'])} target holders. "
f"Model status: **{model_status}**."
)
lines.append("")
lines.append("## 2) transaction snapshot")
lines.append("| metric | value |")
lines.append("|---|---:|")
lines.append(f"| equity purchase price | {money(su['equity_purchase_price'], units)} |")
lines.append(f"| transaction EV | {money(su['transaction_ev'], units)} |")
lines.append(f"| premium | {pct(su.get('premium'))} |")
lines.append(f"| cash / stock / other mix | {pct(su['mix']['cash_percent'])} / {pct(su['mix']['stock_percent'])} / {pct(su['mix']['other_percent'])} |")
lines.append(f"| shares issued to target | {shares['shares_issued_to_target']:,.1f} mm |")
lines.append(f"| PF diluted shares | {shares['pf_diluted_shares']:,.1f} mm |")
lines.append("")
lines.append("## 3) sources and uses")
lines.append("| item | uses | sources |")
lines.append("|---|---:|---:|")
all_keys = sorted(set(su["uses"].keys()) | set(su["sources"].keys()))
for k in all_keys:
u = su["uses"].get(k, "")
s = su["sources"].get(k, "")
lines.append(f"| {k} | {money(u, units) if u != '' else ''} | {money(s, units) if s != '' else ''} |")
lines.append(f"| **total** | **{money(su['total_uses'], units)}** | **{money(su['total_sources'], units)}** |")
lines.append(f"| balance check: sources minus uses | {money(su['balance_delta'], units)} | |")
lines.append("")
lines.append("## 4) purchase accounting")
lines.append("| bridge item | value |")
lines.append("|---|---:|")
for k in ["target_book_equity", "existing_goodwill", "total_intangibles", "ppe_step_up", "inventory_step_up", "deferred_tax_liability", "identifiable_net_assets", "consideration_transferred", "goodwill"]:
lines.append(f"| {k} | {money(ppa.get(k), units)} |")
lines.append(f"| annual intangible amortization | {money(ppa.get('annual_intangible_amortization'), units)} |")
lines.append(f"| incremental PPE depreciation | {money(ppa.get('ppe_incremental_depreciation'), units)} |")
if ppa.get("measurement_period_status"):
lines.append(f"\nPurchase accounting status: **{ppa.get('measurement_period_status')}**.")
lines.append("")
lines.append("## 5) financing and pro forma ownership")
fin = base["financing"]["per_period"][primary_period]
lines.append("| metric | value |")
lines.append("|---|---:|")
lines.append(f"| new debt | {money(su['sources'].get('new_debt'), units)} |")
lines.append(f"| debt interest rate | {pct(base['financing'].get('debt_interest_rate'))} |")
lines.append(f"| annual interest expense | {money(fin.get('interest_expense'), units)} |")
lines.append(f"| lost cash interest | {money(fin.get('lost_cash_interest'), units)} |")
lines.append(f"| acquirer ownership | {pct(shares.get('acquirer_ownership'))} |")
lines.append(f"| target ownership | {pct(shares.get('target_ownership'))} |")
lines.append("")
lines.append("## 6) EPS accretion/dilution")
lines.append("| scenario | period | standalone EPS | PF GAAP EPS | GAAP acc./dil. | PF adjusted EPS | adjusted acc./dil. |")
lines.append("|---|---|---:|---:|---:|---:|---:|")
for scen in ["base", "downside", "upside"]:
pf = results[scen]["pro_forma"]["per_period"][primary_period]
lines.append(f"| {scen} | {primary_period} | {pf['acquirer_standalone_eps']:.2f} | {pf['pf_gaap_eps']:.2f} | {pct(pf['gaap_accretion_dilution'])} | {pf['pf_adjusted_eps']:.2f} | {pct(pf['adjusted_accretion_dilution'])} |")
lines.append("")
lines.append("## 7) synergies and breakeven")
syn = base["synergies"]["per_period"][primary_period]
lines.append("| item | value |")
lines.append("|---|---:|")
for k in ["cost_synergies", "revenue_synergies", "revenue_synergy_contribution", "dis_synergies", "net_pre_tax_synergies", "integration_costs", "after_tax_synergy_contribution"]:
lines.append(f"| {k} | {money(syn.get(k), units)} |")
lines.append(f"| required pre-tax synergy for adjusted EPS neutrality | {money(breakeven.get('required_pre_tax_synergy_for_adjusted_eps_neutrality'), units)} |")
lines.append("")
lines.append("## 8) sensitivities and what breaks")
if hard_failures:
lines.append("Hard failures:")
for f in hard_failures:
lines.append(f"- {f}")
else:
lines.append("No hard failures were detected by the machine checks.")
if warnings:
lines.append("\nSenior review warnings:")
for w in warnings[:12]:
lines.append(f"- {w}")
else:
lines.append("\nNo warnings were detected.")
lines.append("")
lines.append("## 9) QA, source posture, and limitations")
coverage = checks.get("required_source_categories_present", {})
lines.append(f"Required source categories present: {', '.join(coverage.get('present_categories', []))}.")
if coverage.get("missing_categories"):
lines.append(f"Missing source categories: {', '.join(coverage.get('missing_categories', []))}.")
generated_files = [output_dir / "model.xlsx", output_dir / "plan.json", output_dir / "run_log.json"]
if include_report_md:
generated_files.append(output_dir / "report.md")
lines.append("Generated files: " + ", ".join(f"`{path}`" for path in generated_files) + ".")
lines.append("")
lines.append("This deterministic `model.xlsx` export is suitable for screening and senior review. Use `scripts/build_banker_formula_workbook.py` only when a live formula workbook template is requested.")
return "\n".join(lines) + "\n"
# XLSX writer utilities.
def _xlsx_col_letter(n: int) -> str:
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 _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 _clean_sheet_name(name: str) -> str:
invalid = set('[]:*?/\\')
clean = ''.join('_' if ch in invalid else ch for ch in name)[:31]
return clean or "Sheet"
def _sheet_xml(rows: List[List[Any]], shared_map: Dict[str, int], shared_list: List[str]) -> str:
nrows = max(1, len(rows))
ncols = max(1, max((len(r) for r in rows), default=1))
sheet_rows: List[str] = []
for row_idx, row in enumerate(rows, start=1):
cells: List[str] = []
for col_idx, v in enumerate(row, start=1):
if v is None or v == "":
continue
ref = _xlsx_cell_ref(row_idx, col_idx)
if isinstance(v, bool):
cells.append(f'<c r="{ref}" t="b"><v>{1 if v else 0}</v></c>')
elif isinstance(v, (int, float)) and not isinstance(v, bool):
if isinstance(v, float) and (math.isnan(v) or math.isinf(v)):
idx = _shared_string("n/a", shared_map, shared_list)
cells.append(f'<c r="{ref}" t="s"><v>{idx}</v></c>')
else:
cells.append(f'<c r="{ref}"><v>{v}</v></c>')
else:
idx = _shared_string(str(v), shared_map, shared_list)
cells.append(f'<c r="{ref}" t="s"><v>{idx}</v></c>')
sheet_rows.append(f'<row r="{row_idx}">{"".join(cells)}</row>')
dim = f"A1:{_xlsx_cell_ref(nrows, ncols)}"
return (
'<?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}"/>'
'<sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>'
'<sheetData>' + ''.join(sheet_rows) + '</sheetData>'
'</worksheet>'
)
def write_xlsx(path: Path, sheets: Dict[str, List[List[Any]]], sheet_name: str = "Model") -> None:
"""Write a deterministic multi-sheet .xlsx workbook with values only."""
path.parent.mkdir(parents=True, exist_ok=True)
if not sheets:
raise ValueError("No sheets to write")
shared_map: Dict[str, int] = {}
shared_list: List[str] = []
sheet_xmls: List[Tuple[str, str]] = []
used_names: set = set()
for raw_name, rows in sheets.items():
name = _clean_sheet_name(raw_name)
base = name
i = 1
while name in used_names:
suffix = f"_{i}"
name = (base[:31 - len(suffix)] + suffix)
i += 1
used_names.add(name)
sheet_xmls.append((name, _sheet_xml(rows, shared_map, shared_list)))
def si(text: str) -> str:
preserve = ' xml:space="preserve"' if text[:1].isspace() or text[-1:].isspace() else ""
return f"<si><t{preserve}>{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>'
)
sheets_xml = ''.join(f'<sheet name="{escape(name)}" sheetId="{idx}" r:id="rId{idx}"/>' for idx, (name, _) in enumerate(sheet_xmls, start=1))
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>' + sheets_xml + '</sheets></workbook>'
)
rels = []
for idx, _ in enumerate(sheet_xmls, start=1):
rels.append(f'<Relationship Id="rId{idx}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet{idx}.xml"/>')
style_id = len(sheet_xmls) + 1
shared_id = len(sheet_xmls) + 2
rels.append(f'<Relationship Id="rId{style_id}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>')
rels.append(f'<Relationship Id="rId{shared_id}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>')
workbook_rels_xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' + ''.join(rels) + '</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>'
overrides = [
'<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+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"/>',
]
for idx, _ in enumerate(sheet_xmls, start=1):
overrides.append(f'<Override PartName="/xl/worksheets/sheet{idx}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>')
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"/>' + ''.join(overrides) + '</Types>'
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>
"""
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)
for idx, (_, xml) in enumerate(sheet_xmls, start=1):
z.writestr(f"xl/worksheets/sheet{idx}.xml", xml)
z.writestr("xl/sharedStrings.xml", sst_xml)
z.writestr("xl/styles.xml", styles_xml)
def build_model(plan: Dict[str, Any], output_dir: Path, include_report_md: bool = True) -> Dict[str, Any]:
normalized = normalize_plan(plan)
results = run_scenarios(normalized)
periods = get_periods(normalized)
primary_period = periods[1] if len(periods) > 1 else periods[0]
sensitivities = run_sensitivities(normalized, primary_period)
breakeven = compute_synergy_breakeven(normalized, primary_period)
checks, hard_failures, warnings = compute_checks(normalized, results, sensitivities)
model_status = determine_model_status(normalized, hard_failures, warnings)
missing_inputs = identify_missing_inputs(normalized)
p0_handoff = build_p0_handoff(normalized, results, breakeven, model_status, output_dir, warnings, hard_failures, missing_inputs, include_report_md=include_report_md)
sheets = to_model_rows(normalized, results, sensitivities, checks, hard_failures, warnings, breakeven, model_status, missing_inputs)
report = render_report(normalized, results, checks, hard_failures, warnings, breakeven, model_status, output_dir, missing_inputs, include_report_md=include_report_md)
run_log = {
"model_status": model_status,
"workbook_mode": "deterministic_export",
"source_basis": normalized.get("source_basis", []),
"hard_failures": hard_failures,
"warnings": warnings,
"missing_input_request": {
"posture_warning": partial_context_warning(missing_inputs),
"items": missing_inputs,
},
"assumptions": {
"primary_period": primary_period,
"accounting_basis": normalized.get("meta", {}).get("accounting_basis"),
"purchase_accounting_measurement_period_status": normalized.get("purchase_accounting", {}).get("measurement_period_status"),
"workbook_mode": "deterministic_export",
},
"checks": checks,
"p0_handoff": p0_handoff,
}
return {"plan": normalized, "results": results, "sensitivities": sensitivities, "breakeven": breakeven, "checks": checks, "hard_failures": hard_failures, "warnings": warnings, "missing_inputs": missing_inputs, "model_status": model_status, "p0_handoff": p0_handoff, "sheets": sheets, "report": report, "run_log": run_log}
SHA-256: 7df758f6968bf20e35e3e753b1c3894caab667e4bd79de337292e823a596035d