Skip to content
TheChatArchiveThe Chat Archive
Grok 4.1 Fast· xaiHugging Face Frontier Reasoning Traces

Create a deferred revenue burn quality and forecast accuracy checks.

  1. User

    Create a deferred revenue burn quality and forecast accuracy checks.

  2. 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_col for 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!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.