Grok 4.1 Fast· xai
Frontier Reasoning Traces
Create a deferred revenue burn quality and forecast accuracy checks.
- User
Create a deferred revenue burn quality and forecast accuracy checks.
- Assistant
import pandas as pd import numpy as np from typing import Dict, List, Optional, Tuple # Sample data schema assumption: # - DataFrame 'financials' with columns: ['period'] (e.g., '2024-01'), ['actual_rev'], ['forecast_rev'], # ['deferred_bal_start'] (beginning deferred balance), ['recognized_rev'] (revenue recognized in period), # ['new_deferred'] (new deferred added from bookings in period). # # Example data generation for demo: def generate_sample_data() -> pd.DataFrame: dates = pd.date_range('2023-01-01', '2024-12-01', freq='MS').strftime('%Y-%m') np.random.seed(42) data = { 'period': dates, 'actual_rev': np.random.uniform(100, 500, len(dates)).cumsum() / 10, 'forecast_rev': np.random.uniform(100, 500, len(dates)).cumsum() / 10 * 1.05, # Slightly optimistic 'deferred_bal_start': np.random.uniform(1000, 5000, len(dates)), 'recognized_rev': np.random.uniform(50, 200, len(dates)), 'new_deferred': np.random.uniform(100, 300, len(dates)) } df = pd.DataFrame(data) df['deferred_bal_start'] = df['deferred_bal_start'].cumsum() - df['new_deferred'].cumsum() * 0.9 # Simulated return df # ============================================================================= # 1. DEFERRED REVENUE BURN QUALITY CHECKS # ============================================================================= # Measures: # - Burn Rate: recognized_rev / deferred_bal_start (e.g., healthy SaaS: 5-10% monthly for annual contracts) # - Burn Consistency: Std dev of burn rate over last 12 months < 2% # - Burn Coverage: Cumulative burn over 12 months >= 90% of starting deferred cohort # Flags: 'PASS' (good), 'WARN' (monitor), 'FAIL' (action needed) def deferred_revenue_burn_checks(df: pd.DataFrame, period_col: str = 'period', burn_rate_threshold: float = 0.05, # Min 5% monthly burn consistency_threshold: float = 0.02, # Max 2% std dev coverage_threshold: float = 0.90) -> Tuple[pd.DataFrame, Dict]: """ Computes deferred revenue burn quality metrics and pass/fail checks. Returns: - metrics_df: DataFrame with computed metrics and checks per period. - summary: Dict with overall status and key insights. """ df = df.copy() df[period_col] = pd.to_datetime(df[period_col] + '-01') df = df.sort_values(period_col).reset_index(drop=True) # Burn Rate df['burn_rate'] = df['recognized_rev'] / df['deferred_bal_start'].replace(0, np.nan) df['burn_rate_check'] = np.where(df['burn_rate'] >= burn_rate_threshold, 'PASS', 'FAIL') # Rolling Burn Consistency (last 12 months std dev) df['burn_consistency'] = df['burn_rate'].rolling(12, min_periods=3).std() df['burn_consistency_check'] = np.where(df['burn_consistency'] <= consistency_threshold, 'PASS', 'WARN') # Cohort-style Burn Coverage (simplified: trailing 12-mo cumulative burn / bal_start) df['cum_burn_12m'] = df['recognized_rev'].rolling(12, min_periods=1).sum() / df['deferred_bal_start'] df['burn_coverage_check'] = np.where(df['cum_burn_12m'] >= coverage_threshold, 'PASS', 'FAIL') # Overall period check df['overall_burn_quality'] = np.select( [df['burn_rate_check'] == 'PASS', (df['burn_rate_check'] == 'FAIL') & (df['burn_consistency_check'] == 'PASS')], ['PASS', 'WARN'], default='FAIL' ) # Summary recent = df.tail(12) summary = { 'avg_burn_rate': recent['burn_rate'].mean(), 'burn_consistency_avg': recent['burn_consistency'].mean(), 'coverage_12m': recent['cum_burn_12m'].iloc[-1], 'pass_rate': (recent['overall_burn_quality'] == 'PASS').mean(), 'status': 'PASS' if recent['overall_burn_quality'].eq('PASS').mean() > 0.8 else 'WARN' } return df[['period', 'burn_rate', 'burn_rate_check', 'burn_consistency', 'burn_consistency_check', 'cum_burn_12m', 'burn_coverage_check', 'overall_burn_quality']], summary # ============================================================================= # 2. FORECAST ACCURACY CHECKS # ============================================================================= # Measures: # - MAPE: Mean Absolute Percentage Error |forecast - actual| / actual # - Bias: (forecast - actual) / actual (positive = over-forecast) # - Accuracy Score: 1 - MAPE (higher better) # Flags: 'PASS' (<5% MAPE), 'WARN' (5-15%), 'FAIL' (>15%) def forecast_accuracy_checks(df: pd.DataFrame, period_col: str = 'period', mape_pass: float = 0.05, mape_warn: float = 0.15) -> Tuple[pd.DataFrame, Dict]: """ Computes forecast accuracy metrics and pass/fail checks. Returns: - metrics_df: DataFrame with computed metrics and checks per period. - summary: Dict with overall status and key insights (trailing 12 months). """ df = df.copy() df[period_col] = pd.to_datetime(df[period_col] + '-01') df = df.sort_values(period_col).reset_index(drop=True) # Per-period metrics df['mape'] = np.abs(df['forecast_rev'] - df['actual_rev']) / df['actual_rev'].replace(0, np.nan) df['bias'] = (df['forecast_rev'] - df['actual_rev']) / df['actual_rev'].replace(0, np.nan) df['accuracy_score'] = 1 - df['mape'] # Checks conditions = [ df['mape'] < mape_pass, df['mape'] <= mape_warn ] choices = ['PASS', 'WARN'] df['forecast_check'] = np.select(conditions, choices, default='FAIL') # Rolling averages (trailing 12 months) df['mape_12m_avg'] = df['mape'].rolling(12, min_periods=3).mean() df['bias_12m_avg'] = df['bias'].rolling(12, min_periods=3).mean() # Summary (trailing 12 months) recent = df.tail(12) summary = { 'mape_12m_avg': recent['mape_12m_avg'].iloc[-1], 'bias_12m_avg': recent['bias_12m_avg'].iloc[-1], 'pass_rate': (recent['forecast_check'] == 'PASS').mean(), 'status': 'PASS' if recent['mape_12m_avg'].iloc[-1] < mape_pass else ( 'WARN' if recent['mape_12m_avg'].iloc[-1] < mape_warn else 'FAIL' ) } return df[['period', 'mape', 'bias', 'accuracy_score', 'forecast_check', 'mape_12m_avg', 'bias_12m_avg']], summary # ============================================================================= # USAGE EXAMPLE # ============================================================================= if __name__ == "__main__": # Generate sample data financials = generate_sample_data() print("Sample Data Head:") print(financials.head()) # Run Deferred Revenue Burn Checks burn_metrics, burn_summary = deferred_revenue_burn_checks(financials) print("\n=== DEFERRED REVENUE BURN QUALITY ===") print(burn_metrics.tail()) print("Summary:", burn_summary) # Run Forecast Accuracy Checks forecast_metrics, forecast_summary = forecast_accuracy_checks(financials) print("\n=== FORECAST ACCURACY ===") print(forecast_metrics.tail()) print("Summary:", forecast_summary)Key Features & Customization
-
Deferred Revenue Burn Quality:
- Burn Rate: Flags if recognition is too slow (e.g., <5% monthly).
- Consistency: Low variance ensures predictable cash flow.
- Coverage: Ensures deferred rev is fully recognized over time.
- Thresholds are configurable (e.g., for ratable vs. upfront recognition).
-
Forecast Accuracy:
- MAPE/Bias: Standard metrics with rolling averages for trends.
- Handles zero-division safely.
-
Assumptions: Monthly data; adjust
period_colfor quarterly. Input DataFrame matches the schema. -
Integration: Plug into Airflow/DBT for monitoring, or Streamlit/Looker for dashboards.
-
Extensibility: Add email alerts if
summary['status'] == 'FAIL'.
Elementary analysis complete. These checks will detect issues in your revenue recognition and planning. Provide your data schema for tailored SQL!
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.