Pipeline Review

Pipeline Review

Workflow purpose

Deliver a weekly pipeline scorecard that the investments lead, asset-management director, and executive can read in one sitting to answer: which deals advanced, which stalled, where is the capital-need concentration, what is the IC load next session, and which blocking data-quality issues from Dealpath need resolution before the roll-up can be trusted. The workflow composes stage-by-stage counts, dollar-weighted exposure, and stalled-deal aging into a single dashboard; it recommends no deal-level action — every action remains with the deal team.

Trigger conditions

• Explicit: "weekly pipeline review", "pipeline scorecard", "pipeline dashboard for Monday", "where are we on IC load this cycle".
• Implicit: Dealpath adapter emits a pipeline-stage transition for 3+ deals in a week; week-close calendar event; IC calendar approaches within one cycle.
• Recurring: weekly, end-of-week close; also on-demand ahead of executive check-ins.

Inputs (required / optional)

Outputs

Required context

asset_class=residential_multifamily, scope = portfolio (or a declared subset of assets / funds). Segment, form_factor, and lifecycle are not required at the deal grain but may be used to filter — the workflow defaults to all-segments roll-up if unspecified. market filter optional for market-scoped cuts. org_id required whenever approval-threshold bands or staffing bands are read.

Process

1. Freshness + DQ gate. Load Dealpath adapter output; evaluate dq_rules.yaml. If dp_completeness_ic_record or dp_freshness_deals returns a blocker, surface it at the top of the pack; refuse to claim any stage roll-up that the blocker affects. Warn on dp_handoff_lag.
2. Resolve canonical keys. Join Dealpath deal.asset_id to canonical asset_id via master_data/asset_crosswalk.yaml. Resolve market via market_crosswalk.yaml. Flag any unresolved rows; exclude from roll-up and list in DQ appendix.
3. Stage scorecard. Bucket active deals by canonical pipeline stage (sourcing, loi_signed/under_loi, psa_signed/under_psa, under_dd, ic_review, ic_approved, debt_term_sheet / under_financing, pre_close, close / closed, funded). Compute count and dollar-weighted exposure (deal.total_cost or equivalent from Dealpath contract).
4. Velocity. For each stage transition seen this week, compute pipeline_velocity_days per deal and roll up the stage median. Compare against overlay band; color within / below / above.
5. Stalled-deal scan. For each active deal, compute days-in-current-stage. Compare against overlay-defined stalled threshold per stage. Flag; include owner, last touch, next step note from Dealpath.
6. IC-prep load. Count deals currently at ic_review or ic_approved pending final conditions; compare to overlay IC capacity band. Cross-link to workflows/investment_committee_prep/ if next IC is within one cycle.
7. Debt term sheet variance. For deals at debt_term_sheet / under_financing, compare indicated terms (coupon, advance rate, DSCR test, covenant set) vs. last underwritten assumption. Compute debt_term_sheet_variance; flag deals outside overlay band.
8. Retrade-risk screen. Flag deals where any of the following hold: DD findings outside overlay tolerance, seller extension requested, market shift since LOI beyond overlay threshold, multi-party hesitation on recent key-date. List with evidence pointer. Cross-link to workflows/pre_close_deal_tracking/ for closing-week deals.
9. Capital-need concentration. Roll equity and debt capital requirements up by stage-weighted probability; call out any single deal exceeding overlay concentration band.
10. Compose narrative memo. One paragraph per stage change category: advancing, stalled, debt-variance, retrade-risk, DQ-blocked. Cite every claim to a metric slug.
11. Confidence banner. List adapter as_of_date, DQ outcome status (pass / warn / blocker), overlay status tags (starter, sample, approved), and any references that fell back to prior period.

Metrics used

See frontmatter metrics_used. All metric slugs for this workflow are proposed (proposed: true) — no deal-pipeline metric has landed in _core/metrics.md yet. The workflow surfaces computed values with the proposed flag on every output.

Reference files used

• reference/connectors/adapters/dealpath_deal_pipeline/normalized_contract.yaml
• reference/connectors/adapters/dealpath_deal_pipeline/dq_rules.yaml
• reference/connectors/deal_pipeline/schema.yaml
• reference/connectors/master_data/asset_crosswalk.yaml
• reference/connectors/master_data/market_crosswalk.yaml
• reference/derived/role_kpi_targets.csv
• reference/normalized/approval_threshold_defaults.csv

Escalation points

• Stalled-deal aging breaches overlay threshold: deal_team_lead -> investments_lead -> asset_management_director.
• Debt market shift material (coupon spread or advance rate outside overlay band across multiple deals): investments_lead -> executive.
• DQ blocker from Dealpath adapter: data_platform_team -> investments_lead; roll-up paused for affected stages.
• Pipeline velocity outside band two consecutive cycles: investments_lead -> executive.

Required approvals

None. The pack is informational / recommendation severity. Any deal-level action surfaced here is owned by the originating workflow (deal team, capital sourcing, IC prep), which carries its own approval gate.

Failure modes

1. Rolling up deals without checking dp_completeness_ic_record first. Fix: DQ gate is step 1; blocker halts the affected roll-up.
2. Reporting stalled-deal counts without overlay-defined thresholds. Fix: overlay as_of_date and band cited; no thresholds hardcoded in prose.
3. Merging deal counts across canonical stages after informal stage renaming in Dealpath. Fix: map_dealpath_stage is the single mapping; unmapped stages flagged as DQ conformance blockers.
4. Double-counting deals in retrade-risk and stalled-deal lists. Fix: deals appearing on both lists are explicit; union reported with dedup.
5. Reporting capital-need concentration without stage-weighted probability. Fix: probability applied; un-probabilitized figures not published.
6. Missing adapter freshness check. Fix: dp_freshness_deals evaluated; stale feed surfaces banner and escalates.

Edge cases

• Stalled deal: days-in-stage exceeds overlay threshold — listed with owner, last activity, next-step note, and escalation path. If stalled > 2x threshold, escalates to investments_lead.
• Retrade in progress: deal shows PSA executed + DD findings + price renegotiation signal — flagged on retrade list with evidence pointer; counted separately from stalled.
• Declined-then-resurrected deal: deal previously dropped or IC-declined that returns to active — surfaced with prior-decision pointer; treated as new stage entry for velocity purposes.
• Deal renamed after IC: name change post-ic_approved tracked via dp_renamed_after_approval; roll-up uses stable deal_id, surfaces prior name alias.
• Multi-asset deal / portfolio acquisition: one Dealpath deal_id mapping to multiple canonical assets via property_master_crosswalk (manual_override) — listed with all target assets; capital-need counted once at deal level, exposure breakdown per asset.
• Cross-fund allocation: deal tagged with multiple target funds — per-fund allocation carried through; concentration check applied per fund.
• Debt market shift: indicated coupon moves > overlay threshold week-over-week — flagged across affected deals; escalates to investments_lead.
• Partial closing (equity closes before debt or vice versa): shown as two stage positions; deal not claimed closed until both transitions recorded.
• Condition unresolved at close: ic_approved with open conditions whose deadline lapses — flagged, cross-linked to approval request, escalates per approval_matrix.
• Late legal entity setup: canonical legal_entity_id not populated post-close beyond overlay lag — surfaced as handoff warning (dp_handoff_lag); cross-linked to workflows/acquisition_handoff/ once it exists.

Example invocations

1. "Produce the weekly pipeline review for Monday. Flag anything stalled past our 14-day LOI threshold."
2. "Pipeline scorecard, portfolio-wide, as of Friday close. Highlight IC load for the next session and any debt variance."
3. "What's retrade risk on this quarter's pipeline? Evidence list, not opinions."
4. "Pipeline cut, Southeast markets only. Capital-need concentration by fund."
5. "Run the pipeline review with focus on deals sourced in the last 30 days versus the rest."

Example outputs

See examples/example_pipeline_review_output.md for the full artifact shape.

Output — Stage scorecard (abridged)

Stages. sourcing (12 deals), under_loi (5), under_psa (3), under_dd (4), ic_review (2), ic_approved (3), under_financing (2), pre_close (1), closed (1 this week).

Velocity. pipeline_velocity_days (stage median) within band for sourcing, under_loi; above band for under_dd (flagged).

Stalled. 2 deals past overlay threshold — DP_DEAL_014 (under_loi, 21 days) and DP_DEAL_027 (under_dd, 34 days). Owners + next-step notes listed.

IC load. 2 on docket next cycle; overlay capacity band = 3; within capacity.

Debt variance. 1 deal outside band — DP_DEAL_009 coupon indicated at +55bps over UW assumption.

Retrade risk. 1 flag — DP_DEAL_027 (DD finding on roof scope; seller extension requested).

DQ banner. dp_freshness_deals: pass. dp_completeness_ic_record: pass. dp_handoff_lag: warn (1 closed deal, 4 days lag). Adapter as_of_date: sample / starter.

Confidence banner. References: dealpath_deal_pipeline@2026-04-14 (sample), asset_crosswalk@2026-04-15 (sample), role_kpi_targets@2026-03-31 (starter).