Skip to main content

Controller Reporting Pack (v3) - DRAFT SPECS

STATUS: DRAFT / STUDY PHASE This document consolidates all reporting requirements, dashboard concepts, and KPIs discussed in previous sessions.

1. Vision: The "Controller Cockpit"​

The goal is to provide a single Zoho Page ("Dashboard") with 3 distinct tabs serving different stakeholders.

Tab 1: Executive Overview (The "Health Check")​

Target: CFO / Head of Controlling

Purpose: Immediate view of budget consumption and forecasted landing.

Widget 1: KPI Cards (Top Row)

[!TIP] VERIFIED CONFIGURATION: Formulas validated via simulation.

Global Variable Strategy: To avoid manual updates every year, use a Page Parameter named p_fy (Fiscal Year Name).

Implementation Method: Zoho Analytics Embedding (Iframe).

  • Rationale: Superior design flexibility and SQL aggregation power.

Card Metrics:

  • Total Budget: Sum of approved_amount.
  • % Committed: (Sum(committed_amount) / Sum(approved_amount)) * 100.
  • Forecast Variance: Sum(closing_forecast) - Sum(approved_amount).

Widget 2: Budget Burn-down (Line Chart)

  • X-Axis: Months (Oct - Sep).
  • Lines:
    1. Cumulative Budget: Linear projection or phased budget.
    2. Cumulative Committed: Actual commitment curve.
    3. Cumulative Spent: Actual cash/invoice flow.
  • Goal: Detect early if we are spending faster than planned.

Widget 3: CAPEX vs OPEX (Donut Chart)

  • Simple split of the Total Budget.

Target: Strategic Planning

Purpose: Analyze evolution of spending over fiscal years.

Widget 1: Strategic Pivot (The Core Report)

[!TIP] VERIFIED CONFIGURATION: This path has been validated via Python simulation.

Report Type: Pivot Table (from Budget_Versions)

Row Hierarchy (Group By):

  1. Budget_Bucket.stream.display_label (Renamed: "Stream")
  2. Budget_Bucket.bucket_name (Renamed: "Bucket")
  3. expense_type (Renamed: "Type")

Column Hierarchy:

  1. fiscal_year (Renamed: "FY")

Data (Values):

  • approved_amount (Sum) -> Label: "Budget"
  • committed_amount (Sum) -> Label: "Committed"
  • spent_amount (Sum) -> Label: "Spent"

Filters (Criteria):

  • Status != "Archived" (To avoid double counting history)

Widget 2: Vendor Spend Evolution (Stacked Bar)

  • X-Axis: Fiscal Years.
  • Y-Axis: Total Invoiced.
  • Stack: Top 5 Vendors (Others grouped as "All Other").

Tab 3: Operational Control​

Target: Operational Controller / Procurement

Purpose: Variance analysis on specific vendors and contract execution.

Report 1: Vendor & Contract Performance

  • Rows: Provider > Contract Code.
  • Columns:
    • Total Contract Value
    • Invoiced to Date
    • Remaining to Invoice (Total - Invoiced)
    • Gap Analysis (Visible if Status=Closed): Savings to release.

Report 2: The "Savings Hunter"

  • Filter: Status = Closed AND Total Value > Total Invoiced.
  • Action: Identify "Zombie Commitments" that can be released to the budget pool.

Report 3: "Zombie Contracts"

  • Filter: Status = Open/In Delivery AND Last Invoice Date > 6 months ago.
  • Goal: Identify dormant contracts.

2. Special Requirements​

3.1. Net vs Gross View (Intercompany)​

Linked to recharge_logic_v1.md

  • Toggle: The Dashboard should ideally have a toggle or separate section to switch between:
    • Gross View: Total Cash Out (Treasury).
    • Net View: P&L Impact (Controlling) - Excludes amounts recharged to other entities.

3.2. Accruals (Ratei/Risconti)​

  • Concept: Difference between "Invoiced" (Billed) and "Incurred" (Service Period).
  • Metric: Accruals Scope = Committed (Ordered) - Invoiced (Billed).
  • Use Case: Estimating Work In Progress (WIP) at month-end.

3. Implementation Priorities​

  1. Strategic Pivot (Quick Win): Leverage existing data to build the Multi-Year Stream view.
  2. KPI Cards: Build calculated metrics on top of Budget_Versions.
  3. Burn-down Chart: Requires a specific ZML (Zoho Markup Language) snippet or a helper form to store snapshot history (if Creator doesn't support "As Of" reporting natively).