finance-ops-reporting

name	finance-ops-reporting
description	SOP for Crescent Finance Ops branch reporting, CBA compensation forecasting, and theatre payroll control tasks against the remote Finance Ops API.

Crescent Finance Ops Reporting SOP

This skill covers three task families served by one read-only HTTP API:

1. Finance — branch monthly-close packages and regional management views (income statement, growth, ratios, rankings).
2. Compensation — ensemble (orchestra) current-year summaries and multi-year board forecasts from rate books, rosters, and scenarios.
3. Payroll — weekly touring-production payroll control (per-service pay, premiums, vacation, guarantees, CBA conflict flags).

Each task ships three payload files. Read all three before computing:

• prompt.txt — domain + what to produce.
• payloads/request_memo.json — the target entity id (branch/region/ensemble/production), periods/years, scenario id, and a *_focus list of what to report. memo_note text is flavor/distractor — ignore unrelated names (e.g. "Harbor North draft workbook"); always reconcile against the live API data for the requested target id.
• payloads/answer_template.json — the authoritative contract: required_top_level_keys (return exactly these keys), field_types (exact nested key names + shapes), and a description line with rounding/ordering rules. Mirror its key names and nesting verbatim.

1. Remote API access

• Base URL: <remote-env-url> (ignore any base_url inside payloads such as 127.0.0.1:* — they are wrong).
• All reads are HTTP GET. Fetch with curl -s "<base>/<endpoint>".
• Endpoints by domain:
  • Finance: /api/finance/branches, /api/finance/period-map, /api/finance/accounts, /api/finance/records (filters ?branch_id= ?region= ?account=).
  • Compensation: /api/compensation/rate-book, /api/compensation/rosters?ensemble_id=, /api/compensation/scenarios.
  • Payroll: /api/payroll/rate-book, /api/payroll/productions?production_id=.
  • Discovery: /api/manifest (entity ids, record counts), /health.
• The compensation and payroll rate books contain a business_rules array — read it; the formulas below are derived from it.

Data shapes

• finance/records: list of rows, one per (branch, account). Each row has branch_id, region_id, account, and values = a dict mapping period label ("M1".."M24") to a number. There is no period field; index by values[period].
• period-map: maps each period (M1..M24) to a fiscal_year. M1–M12 = FY2024, M13–M24 = FY2025. (FY#### label = "FY"+fiscal_year.)
• accounts: each has account, category (revenue/cogs/sga/allocations/operating), metric_type (currency/count).
• compensation/rosters: list of employees with weeks_by_quarter (Q1..Q4 weeks), years_of_service, title (or null), overscale_weekly, combined_overscale_includes_title (bool), notes.
• compensation/scenarios: dict keyed by scenario id; each has year_plus_1 and year_plus_2 blocks with mws_growth, overscale_growth, seniority_growth, title_pct_multiplier.
• payroll/productions: list with one production object containing schedule (services) and roster (musicians with flags + assigned_service_ids).

2. Output conventions (from every template description)

• Currency → round to 2 decimals. Percent / ratio / growth → round to 4 decimals. (A ratio like 24.59% is stored as 0.2459; a per-unit currency like ARPU is a dollar value rounded to 2 dp.)
• Lists use ascending stable ids unless a rank field says otherwise. branch_ids ascending; per_musician ordered by musician_id; conflict_flags sorted alphabetically.
• Return exactly the required_top_level_keys, with the nested key names from field_types. Period labels are the raw "M##" strings; fiscal-year labels are "FY2024"/"FY2025".
• Include a category/field only "when applicable" if the template says so (e.g. omit substitute_adjustment when zero; per-musician categories maps only nonzero categories).

3. FINANCE domain (branch close + regional view) — VALIDATED

Income-statement line definitions (sum the listed accounts over the chosen period set)

• revenue = product_revenue + service_revenue
• cogs = direct_materials_cogs + direct_labor_cogs
• gross_margin = revenue − cogs
• sga = sales_sga + admin_sga + occupancy_sga
• allocations = shared_service_allocations
• ebitda = gross_margin − sga − allocations

A "period set" is either a single month (e.g. ["M24"]) or all 12 periods of a fiscal year (FY2024 = M1..M12, FY2025 = M13..M24). Sum each account's values across the period set, then combine.

Derived metrics (FY-level)

• ebitda_margin = FY ebitda / FY revenue (4 dp).
• arpu = FY revenue / SUM of monthly active_customers over the FY. Use the summed count, NOT the average (validated: average is wrong).
• sales_per_labor_headcount = FY revenue / SUM of monthly labor_headcount over the FY. Sum, not average (validated in both the branch and regional tasks).
• revenue_growth_pct = (FY2025 revenue − FY2024 revenue) / FY2024 revenue (4 dp). ebitda_growth_pct analogous.
• Month-over-month (mom_revenue_variance): amount = current-month revenue − prior-month revenue; pct = amount / prior-month revenue (4 dp). The current/prior months come from the memo (close_period/prior_period).

Regional rollups & rankings

• A region's branch set = all branches whose region_id matches (ascending ids). Get region_id from /api/finance/branches.
• Region FY aggregates (revenue/sga/allocations/ebitda) = sum of per-branch income statements.
• Region ebitda_margin / sales_per_labor_headcount use region-summed revenue, ebitda, and summed labor headcount.
• top_ebitda_branch_id / bottom_ebitda_branch_id = branch with max / min FY2025 ebitda in the region.
• region_reconciliation_variance = region total ebitda − sum(branch ebitda) = 0.00 (it ties out; this field tests that you aggregated consistently).
• ebitda_rank_desc (branch within region) = 1 for highest FY ebitda, descending.

Cross-branch rankings (all 12 branches)

• "Sales growth" rank = by revenue-growth PERCENT (FY2025 vs FY2024), descending. NOT absolute dollar growth (validated: absolute-amount ranking is wrong). Rank 1 = highest %.
• top_sales_growth_branch_id = branch with highest FY revenue-growth %.
• top_arpu_branch_id = branch with highest ARPU (ranking is identical under sum or average since the /12 is a constant).

Finance SOP

1. Read memo: target branch/region id, close + prior month, comparison years, focus list.
2. GET branches, period-map, accounts, and records (filter by branch_id or region).
3. Build period sets (single month, FY2024, FY2025). Compute income statement per the line defs.
4. Compute derived metrics (sum-based ARPU & sales-per-labor; margins/growth at 4 dp).
5. Compute region context (sum branches, rank) and cross-branch rankings (growth % and ARPU).
6. Assemble exactly the template keys; round currency 2 dp, ratios 4 dp.

4. COMPENSATION domain (summary + forecast) — VALIDATED ENGINE

Rate book (/api/compensation/rate-book) gives minimum_weekly_scale (MWS, e.g. 2520), pay_types (ordered list), seniority_weekly bands, title_premium_pct by title, current_year, and business_rules.

Per-employee weekly pay components (each multiplied by weeks worked that quarter, from weeks_by_quarter)

• Minimum Weekly Scale = MWS × weeks.
• Titled Position Premium = title_premium_pct[title] × MWS × weeks. Suppress entirely (0) when combined_overscale_includes_title is true — the title premium is already folded into overscale per side letter (rate-book rule).
• Seniority = seniority_weekly band amount for the employee's years_of_service × weeks. Bands (min–max years → weekly): 0–4 → 0; 5–9 → 48; 10–14 → 82; 15–19 → 126; 20–24 → 170; 25+ → 215 (read the actual table; last band has max_years: null).
• Overscale = overscale_weekly × weeks.

Aggregation

• quarter_totals[Qn] = sum over all employees of all four pay components for that quarter (using that quarter's weeks).
• annual_pay_type_totals[paytype] = sum across all quarters/employees for that pay type.
• annual_total = sum of all pay-type totals (= sum of quarter totals).
• largest_pay_type = pay type with the largest annual total (typically Minimum Weekly Scale).
• pay_types (list) = the rate book's pay_types order. current_year = rate book current_year. roster_count = len(roster).
• combined_overscale_employee_count = # employees with combined_overscale_includes_title == true.
• partial_quarter_employee_count = # employees with any quarter's weeks != 13 (a full quarter is 13 weeks; partials are flagged in notes).

Forecast (Year+1, Year+2) — scenario-driven

Pick the scenario by scenario_id from /api/compensation/scenarios. Apply growth compounding across forecast years:

• Add +1 year of service for Year+1 and +2 years for Year+2 to each employee's years_of_service before selecting the seniority band (rate-book rule). Band jumps from this progression are a major driver.
• MWS(Year+k) = MWS × Π(1 + mws_growth) over years 1..k (compound).
• Seniority weekly(Year+k) = band(adjusted yos) × Π(1 + seniority_growth) compound. (Both the band change AND the growth multiplier apply — dropping the multiplier is wrong.)
• Overscale weekly(Year+k) = overscale_weekly × Π(1 + overscale_growth) compound.
• Title premium uses MWS(Year+k) × pct × Π(title_pct_multiplier) compound (still suppressed when combined-overscale).
• annual_totals: current / year_plus_1 / year_plus_2. growth_rates: year_plus_1_vs_current = (y1−cur)/cur; year_plus_2_vs_year_plus_1 = (y2−y1)/y1 (4 dp).
• largest_growth_pay_type = the pay type with the largest PERCENT growth from current → Year+2 (NOT absolute dollars). Seniority often wins because of band progression. (Validated: absolute-dollar basis is wrong.)
• year_plus_2_quarter_totals / year_plus_2_pay_type_totals computed for the Year+2 forecast.
• combined_overscale_employee_count / partial_quarter_employee_count as in the summary.

Note: the forecast numbers above match the live data to within a couple of fields; the compounding-growth-on-bands method is the best-validated reading. Apply growth multipliers AND the +years-of-service band rule together, compounding each year.

Compensation SOP

1. Read memo: ensemble id, scenario id (forecast only), focus list.
2. GET rate-book, rosters?ensemble_id=, and (forecast) scenarios.
3. Compute current-year per-employee components → quarter/pay-type/annual totals.
4. (Forecast) Re-run for Year+1/Year+2 with +yos band rule and compounding scenario growth.
5. Count combined-overscale and partial-quarter employees.
6. Assemble exact template keys; currency 2 dp, growth 4 dp.

5. PAYROLL domain (weekly production) — STRUCTURAL FIELDS VALIDATED; pay math best-effort

Rate book (/api/payroll/rate-book): service_rates, premium_pct, service_time_limits, conflict_thresholds, weekly_guarantee, and business_rules. Production has schedule (services with service_type, duration_hours, start_time, end_time) and roster (musicians with assigned_service_ids and boolean flags principal, lead, quartet, electronic, substitute, vacation_eligible, integer doubles).

Service counts & conflict flags — VALIDATED (compute these exactly)

• service_counts = count of each service_type string across the schedule.
• conflict_flags (sorted, deduped) using only these enum values:
  • REHEARSAL_EARLY_START — any Rehearsal start_time earlier than conflict_thresholds.rehearsal_earliest_start (09:00).
  • REHEARSAL_LATE_END — any Rehearsal end_time later than rehearsal_latest_end (18:30).
  • SERVICE_OVER_TIME_LIMIT — any service whose duration_hours exceeds service_time_limits[service_type].
  • SOUND_CHECK_DURATION_MISMATCH — a sound-check service whose duration_hours != its named hours (1hr Sound Check → 1.0, 2hr Sound Check → 2.0).
• top_paid_musician_id = musician with the highest weekly total (relative ordering is robust even if absolute pay is uncertain).

Pay math — apply the rate-book business_rules literally (authoritative source)

Per musician, over their assigned_service_ids:

• Base service pay: Performance, Audit, and Sound Check are per-service flat rates (service_rates[service_type]). Rehearsal is hourly (service_rates["Rehearsal"] × hours) with a three-hour minimum call (use max(duration_hours, 3)).
• Premiums (premium_pct) are applied to base service pay before vacation: principal_or_lead 15% (if principal or lead), quartet 15%, electronic 25%, concertmaster 20%. Doubles: 25% for the first extra instrument + 10% for each additional (doubles count): first_double once + additional_double × (doubles−1). Report the doubles amount under the separate doubles category and all other premiums under premium.
• Vacation = 4% of (base service pay + premiums) when vacation_eligible is true.
• Weekly guarantee adjustment = weekly_guarantee (2082) − base service pay, applied only to guaranteed regular players (non-substitutes) whose base service pay is below the guarantee.
• A musician's total = base + premiums + doubles + vacation + guarantee_adjustment (+ substitute_adjustment if any). per_musician.categories maps only nonzero categories.
• category_totals keys: performance, audit, rehearsal, sound_check, premium, doubles, vacation, guarantee_adjustment, and substitute_adjustment when applicable. weekly_total = sum of all musician totals.

CAUTION: in training, the structural fields (service_counts, conflict_flags, top_paid_musician_id) verified correct, but the exact monetary figures did not fully reconcile — the precise base/premium/vacation/guarantee interaction has unresolved nuance (candidates to reconsider for a new task: whether premiums/doubles apply to all base vs performance-only base, whether over-time-limit rehearsals are capped at the limit, and whether the guarantee compares base vs base+premium). Default to the literal rate-book wording above (premiums on full base service pay, rehearsal at actual hours with 3-hour minimum, guarantee on base service pay), since the rate book is the authoritative spec, and double-check arithmetic against the rate-book rules.

Payroll SOP

1. Read memo: production id, focus list.
2. GET payroll rate-book and productions?production_id=.
3. Compute service_counts and conflict_flags (validated logic above) first.
4. For each musician (ordered by musician_id): sum base service pay by category, apply premiums/doubles, vacation, then guarantee; build nonzero categories; total.
5. Roll up category_totals and weekly_total; pick top_paid_musician_id.
6. Assemble exact template keys; currency 2 dp; sort flags; order musicians by id.

6. General checklist for an unseen task in this group

1. Identify the domain from prompt.txt (finance / compensation / payroll) and the target id + focus from request_memo.json.
2. Open answer_template.json; list the exact required_top_level_keys and nested field_types — build your output to match key-for-key.
3. Fetch only the relevant endpoints (remote base URL); ignore distractor names in memo_note.
4. Apply the validated domain formulas above (period→FY mapping, income-statement lines, sum-based ARPU/sales-per-labor, percent-based growth rankings, compensation pay components with combined-overscale/partial-quarter rules, compounding forecast growth with +years-of-service, payroll per-service/premium/vacation/guarantee and conflict flags).
5. Round: currency 2 dp, percent/ratio/growth 4 dp. Order lists by ascending stable id unless a rank says otherwise; sort conflict flags alphabetically; include "when applicable" fields only when nonzero.
6. Emit a single JSON object with exactly the template's top-level keys.