06 — FP&A Intelligence: Forecasting, Budgeting, Scenarios & Variance
Domain: Financial planning, predictive analytics, scenario modeling, variance analysis Dependencies: 01-Data (schema), 02-AI/ML (forecasting engine), 05-Core Ops (actuals data) Outputs: Calculation engines, planning models, variance algorithms, report specifications
ROLE
You are a Senior FP&A Systems Architect with deep expertise in financial planning engines, driver-based modeling, rolling forecasts, and AI-powered variance analysis. You have designed planning platforms used by Fortune 500 CFO offices and understand the workflows that make the difference between a spreadsheet replacement and a decision-support system.
OBJECTIVE
Design the complete FP&A intelligence layer — the capabilities that transform Avivatec from a transactional accounting system into a market-leading planning and analysis platform. This is the primary competitive differentiator against Datarails, Mosaic, Anaplan, Planful, and Cube.
DELIVERABLES
D1. Forecasting Engine
Forecast Types:
| Type | Data Source | Model | Horizon | Refresh |
|---|---|---|---|---|
| Revenue forecast | fact_gl_transactions (revenue accounts) | NeuralProphet + XGBoost ensemble | 12/18/24 months | Nightly |
| Expense forecast | fact_gl_transactions (expense accounts) | NeuralProphet (per cost center) | 12/18/24 months | Nightly |
| Cash flow forecast | Net of AR aging + AP aging + scheduled | Deterministic + probabilistic | 13 weeks + 12 months | Real-time |
| Headcount forecast | HRIS integration + planned hires | Linear + step function | 12/24 months | Weekly |
| Pipeline forecast | CRM integration (Salesforce, HubSpot) | Weighted pipeline + ML | 6/12 months | Daily |
Forecast Workflow:
Historical Actuals → Feature Engineering → Model Selection → Training
→ Backtesting → Production Forecast → Confidence Intervals
→ Variance vs. Budget → Narrative Explanation → Distribution
Key Requirements:
- Automatic model selection per time-series (evaluate MAPE across algorithms)
- Ensemble option: weighted combination of top-N models
- Continuous learning: retrain nightly on new actuals, drift detection triggers full retrain
- Forecast accuracy dashboard: MAPE/RMSE/MAE by forecast type, quality scoring (A/B/C/D)
- Forecast explanation: "Revenue forecast increased 5% due to Q3 seasonal pattern and 12% YoY growth trend"
- Scenario overlay: adjust forecast with manual assumptions without destroying model output
- Forecast lock: freeze a forecast version for board presentation while models continue training
D2. Budgeting & Annual Planning Engine
Planning Methodologies:
- Top-down: CFO sets company targets → auto-distribute to departments by historical ratio
- Bottom-up: Department heads submit budgets → roll up with consolidation
- Top-down/Bottom-up reconciliation: Compare and iterate until convergence
- Zero-based budgeting (ZBB): Every line item requires justification from zero base
- Driver-based: Link operational KPIs to financial outcomes
Budget Workflow:
Planning Cycle Setup → Template Distribution → Department Entry
→ Manager Review → Rollup & Consolidation → Executive Review
→ Iteration → Board Approval → Lock & Publish
Requirements:
- Multi-year planning: annual detail + 3-5 year strategic view
- Budget templates by department type and industry vertical
- Calendar spreading: annual amounts → monthly with seasonality patterns
- Formula engine: Excel-like formulas in web UI (SUM, IF, VLOOKUP, custom)
- Version control: unlimited versions with diff comparison
- Approval workflows: multi-level with delegation and deadline enforcement
- Budget locking: approved budgets become read-only, amendment process for changes
- What-if sandbox: modify budget assumptions without affecting approved version
D3. Scenario Modeling & What-If Analysis
Scenario Types:
- Base case: Most likely outcome (current forecast)
- Best case: Optimistic assumptions (parameterized)
- Worst case: Pessimistic assumptions (parameterized)
- Custom scenarios: User-defined assumption sets
Modeling Engine:
Assumption Set → Driver Relationships → P&L Recalculation
→ Balance Sheet Impact → Cash Flow Projection → KPI Dashboard
Requirements:
- Unlimited scenario creation without model duplication (assumptions-only storage)
- Real-time recalculation: change a driver → instant P&L/BS/CF update (< 2s)
- Driver-based planning: link operational KPIs to financial outcomes
- Example: headcount → salary expense → benefits → office space → total OPEX
- Example: pipeline deals → win rate → revenue → COGS → gross margin
- Side-by-side comparison matrix (up to 5 scenarios simultaneously)
- Sensitivity analysis: auto-vary one assumption ±10/20/30%, show impact range
- Monte Carlo simulation: 1000+ iterations for probability distributions
- Scenario sharing: publish scenarios for team review with commenting
- Version history: every scenario change tracked with rollback capability
D4. Variance Analysis Engine
Variance Types:
| Comparison | Description | Frequency |
|---|---|---|
| Budget vs. Actual (BvA) | Approved budget vs. GL actuals | Monthly |
| Forecast vs. Actual (FvA) | Latest forecast vs. GL actuals | Monthly |
| Period over Period (PoP) | Current vs. prior month/quarter/year | Monthly |
| Forecast vs. Forecast (FvF) | Current forecast vs. prior forecast | Weekly |
| Scenario vs. Scenario | Compare any two scenarios | On-demand |
AI-Powered Variance Explanation:
Detect Variance → Quantify Impact → Decompose Drivers
→ Generate Natural Language Explanation → Route Alert
Requirements:
- Automatic variance detection: absolute ($) and relative (%) thresholds
- Drill-down: variance → account → entity → transaction → source document
- Driver decomposition: "Revenue missed by $50K: $30K volume, $15K price, $5K mix"
- AI narrative generation: personalized by audience
- CFO: strategic summary, key risks, action items
- Controller: detailed by account, reclass recommendations
- Board: executive overview with visualization
- Multi-language narratives: PT-BR, EN-US, ES
- Alert routing: threshold-exceeded → Slack/Email/WhatsApp/SMS via Apprise
- Variance trend tracking: is the gap widening or narrowing over time?
- Action item assignment: link variance to corrective actions with owners and deadlines
D5. Consolidation Engine
Requirements:
- Multi-entity consolidation with elimination entries
- Intercompany transaction identification and auto-elimination
- Currency translation: current rate, temporal, monetary/non-monetary methods
- FX gain/loss calculation (realized and unrealized)
- Minority interest calculation
- Consolidation journal entries with full audit trail
- Consolidated P&L, Balance Sheet, Cash Flow Statement
- Segment reporting by entity, region, product line
- Consolidation calendar with deadlines and status tracking
D6. Reporting & Board Books
Standard Reports:
- DRE / Income Statement (Brazilian GAAP format + US GAAP format)
- Balance Sheet
- Cash Flow Statement (direct and indirect methods)
- Aging Reports (AR and AP)
- Trial Balance
- Budget vs. Actual with variance commentary
- Rolling Forecast vs. Budget
- KPI Dashboard (customizable by role)
Board Book Generation:
- AI-generated executive narrative
- Visualization auto-selection (charts, tables, waterfall, bridge)
- PDF export with consistent branding
- Version control and approval workflow
- Scheduled distribution (email, Slack, WhatsApp)
CONSTRAINTS
- All calculations must be deterministic and reproducible
- Forecast explanations must trace to source GL transactions
- Budget approval status must be immutable once locked
- Multi-currency: all planning supports native currency and base currency simultaneously
- Consolidation must handle circular references (intercompany dividends → retained earnings)
- Performance: full P&L recalculation < 2s, 5-year forecast generation < 30s
RESEARCH QUESTIONS
- What is the optimal architecture for a real-time scenario recalculation engine (graph-based vs. spreadsheet-style)?
- How should the driver-based planning engine handle circular dependencies (e.g., revenue → tax → net income → reinvestment → revenue)?
- What is the best approach for Monte Carlo simulation in a multi-tenant web application (server-side vs. client-side WebWorkers)?
- How to design the AI narrative generation prompt for CFO-quality variance commentary?
- What consolidation engine architecture handles 50+ entities with complex ownership structures?