""" ================================================================================ PHASE 2A: OPTION B1 - ROLLING WINDOW CROSS-VALIDATION ================================================================================ AstraZeneca Cash Flow Forecasting - Datathon 2025 Purpose: Assess how Prophet forecast accuracy degrades over time - Test 1, 2, 3, 4 month forecast horizons - Analyze degradation pattern - Extrapolate expected accuracy for 6-month production forecast Rolling Windows: - Window 1: Train weeks 1-28 -> Test weeks 29-32 (1 month) - Window 2: Train weeks 1-28 -> Test weeks 29-36 (2 months) - Window 3: Train weeks 1-28 -> Test weeks 29-40 (3 months) - Window 4: Train weeks 1-28 -> Test weeks 29-44 (4 months) """ import pandas as pd import numpy as np import matplotlib.pyplot as plt import seaborn as sns from datetime import datetime import warnings warnings.filterwarnings('ignore') from sklearn.metrics import mean_absolute_error, mean_squared_error from prophet import Prophet from scipy import stats # AstraZeneca Color Palette AZ_COLORS = { 'mulberry': '#830051', 'lime_green': '#C4D600', 'navy': '#003865', 'graphite': '#3F4444', 'light_blue': '#68D2DF', 'magenta': '#D0006F', 'purple': '#3C1053', 'gold': '#F0AB00' } print("=" * 80) print("PHASE 2A: OPTION B1 - ROLLING WINDOW CROSS-VALIDATION") print("=" * 80) # ============================================================================= # HELPER FUNCTIONS # ============================================================================= def symmetric_mape(y_true, y_pred): """Calculate Symmetric MAPE (handles zero values better)""" numerator = np.abs(y_true - y_pred) denominator = (np.abs(y_true) + np.abs(y_pred)) / 2 mask = denominator != 0 if mask.sum() > 0: return np.mean(numerator[mask] / denominator[mask]) * 100 return np.nan def train_prophet(train_data, forecast_periods, test_dates): """Train Prophet and generate forecasts""" prophet_df = pd.DataFrame({ 'ds': train_data['Week_Start'], 'y': train_data['Net_Cash_Flow_USD'] }) model = Prophet( yearly_seasonality=False, weekly_seasonality=False, daily_seasonality=False, seasonality_mode='additive', interval_width=0.95, changepoint_prior_scale=0.05 ) model.add_seasonality(name='monthly', period=30.5, fourier_order=5) model.fit(prophet_df) future = pd.DataFrame({'ds': test_dates}) forecast = model.predict(future) return forecast['yhat'].values, forecast['yhat_lower'].values, forecast['yhat_upper'].values # ============================================================================= # LOAD DATA # ============================================================================= print("\n[1/7] Loading weekly cash flow data...") df = pd.read_csv('data_cleaned_weekly.csv') df['Week_Start'] = pd.to_datetime(df['Week_Start']) df = df.sort_values('Week_Start').reset_index(drop=True) print(f" Total weeks: {len(df)}") # ============================================================================= # DEFINE ROLLING WINDOWS # ============================================================================= print("\n[2/7] Defining rolling window validation strategy...") # Training ends at week 28 (allows testing up to week 44 = 16 weeks = 4 months) train_end = 28 # 28 weeks of training data windows = [ {'name': '1 Month', 'test_weeks': 4, 'end_week': 32, 'color': AZ_COLORS['lime_green']}, {'name': '2 Months', 'test_weeks': 8, 'end_week': 36, 'color': AZ_COLORS['light_blue']}, {'name': '3 Months', 'test_weeks': 12, 'end_week': 40, 'color': AZ_COLORS['gold']}, {'name': '4 Months', 'test_weeks': 16, 'end_week': 44, 'color': AZ_COLORS['magenta']} ] print(f" Training period: Weeks 1-{train_end}") for w in windows: print(f" {w['name']}: Test weeks {train_end+1}-{w['end_week']} ({w['test_weeks']} weeks)") # ============================================================================= # RUN ROLLING WINDOW VALIDATION # ============================================================================= print("\n[3/7] Running rolling window validation...") train_df = df.iloc[:train_end].copy() results = [] for window in windows: print(f"\n Training for {window['name']} horizon...") # Get test data test_end = window['end_week'] test_df = df.iloc[train_end:test_end].copy() # Train Prophet and get predictions predictions, lower_95, upper_95 = train_prophet( train_df, window['test_weeks'], test_df['Week_Start'] ) # Calculate metrics y_test = test_df['Net_Cash_Flow_USD'].values mae = mean_absolute_error(y_test, predictions) rmse = np.sqrt(mean_squared_error(y_test, predictions)) smape = symmetric_mape(y_test, predictions) # CI coverage ci_coverage = np.mean((y_test >= lower_95) & (y_test <= upper_95)) * 100 results.append({ 'Horizon': window['name'], 'Test_Weeks': window['test_weeks'], 'MAE': mae, 'RMSE': rmse, 'SMAPE': smape, 'CI_Coverage': ci_coverage, 'Predictions': predictions, 'Lower_95': lower_95, 'Upper_95': upper_95, 'Actuals': y_test, 'Test_Week_Numbers': test_df['Week_Number'].values, 'Color': window['color'] }) print(f" MAE: ${mae/1e6:.2f}M, SMAPE: {smape:.1f}%, CI Coverage: {ci_coverage:.0f}%") # ============================================================================= # ANALYZE DEGRADATION # ============================================================================= print("\n[4/7] Analyzing forecast degradation pattern...") results_df = pd.DataFrame([{ 'Horizon': r['Horizon'], 'Test_Weeks': r['Test_Weeks'], 'MAE': r['MAE'], 'RMSE': r['RMSE'], 'SMAPE': r['SMAPE'], 'CI_Coverage': r['CI_Coverage'] } for r in results]) # Linear regression on MAE degradation x = results_df['Test_Weeks'].values y_mae = results_df['MAE'].values slope_mae, intercept_mae, r_value, p_value, std_err = stats.linregress(x, y_mae) # Calculate degradation rate mae_per_week = slope_mae mae_per_month = slope_mae * 4 print("\n Degradation Analysis:") print(f" MAE increases by ${mae_per_week/1e6:.3f}M per week") print(f" MAE increases by ${mae_per_month/1e6:.2f}M per month") print(f" Linear fit R-squared: {r_value**2:.3f}") # Extrapolate to 6 months (26 weeks) extrapolated_6m_mae = intercept_mae + slope_mae * 26 print(f"\n Extrapolated 6-Month MAE: ${extrapolated_6m_mae/1e6:.2f}M") # SMAPE degradation y_smape = results_df['SMAPE'].values slope_smape, intercept_smape, _, _, _ = stats.linregress(x, y_smape) extrapolated_6m_smape = intercept_smape + slope_smape * 26 print(f" Extrapolated 6-Month SMAPE: {extrapolated_6m_smape:.1f}%") # ============================================================================= # CREATE VISUALIZATIONS # ============================================================================= print("\n[5/7] Creating visualizations...") import os os.makedirs('dashboard', exist_ok=True) fig = plt.figure(figsize=(20, 16)) fig.suptitle('PHASE 2A: OPTION B1 - Rolling Window Cross-Validation\nAstraZeneca Cash Flow Forecasting', fontsize=16, fontweight='bold', color=AZ_COLORS['navy'], y=0.98) # ------ Plot 1: MAE by Forecast Horizon ------ ax1 = fig.add_subplot(2, 3, 1) horizon_labels = [r['Horizon'] for r in results] mae_values = [r['MAE']/1e6 for r in results] colors = [r['Color'] for r in results] bars = ax1.bar(horizon_labels, mae_values, color=colors, edgecolor=AZ_COLORS['navy'], linewidth=2) for bar, val in zip(bars, mae_values): ax1.text(bar.get_x() + bar.get_width()/2, bar.get_height() + 0.05, f'${val:.2f}M', ha='center', fontweight='bold', fontsize=11) ax1.set_ylabel('MAE (Millions USD)', fontweight='bold') ax1.set_title('MAE by Forecast Horizon', fontweight='bold') ax1.set_ylim(0, max(mae_values) * 1.2) # ------ Plot 2: SMAPE by Forecast Horizon ------ ax2 = fig.add_subplot(2, 3, 2) smape_values = [r['SMAPE'] for r in results] bars = ax2.bar(horizon_labels, smape_values, color=colors, edgecolor=AZ_COLORS['navy'], linewidth=2) for bar, val in zip(bars, smape_values): ax2.text(bar.get_x() + bar.get_width()/2, bar.get_height() + 2, f'{val:.0f}%', ha='center', fontweight='bold', fontsize=11) ax2.set_ylabel('SMAPE (%)', fontweight='bold') ax2.set_title('SMAPE by Forecast Horizon', fontweight='bold') ax2.set_ylim(0, max(smape_values) * 1.2) # ------ Plot 3: Degradation Curve with Extrapolation ------ ax3 = fig.add_subplot(2, 3, 3) weeks = [r['Test_Weeks'] for r in results] ax3.scatter(weeks, mae_values, c=[r['Color'] for r in results], s=200, zorder=5, edgecolors=AZ_COLORS['navy'], linewidths=2) # Linear fit line x_line = np.linspace(0, 30, 100) y_line = (intercept_mae + slope_mae * x_line) / 1e6 ax3.plot(x_line, y_line, '--', color=AZ_COLORS['navy'], linewidth=2, label='Linear Fit') # Extrapolation to 6 months ax3.scatter([26], [extrapolated_6m_mae/1e6], c=AZ_COLORS['mulberry'], s=250, marker='*', zorder=6, label=f'6-Month Extrapolation: ${extrapolated_6m_mae/1e6:.2f}M') ax3.axvline(x=16, color='gray', linestyle=':', linewidth=1, label='Validation End') # Labels for points for i, (w, m, h) in enumerate(zip(weeks, mae_values, horizon_labels)): ax3.annotate(h, (w, m), textcoords="offset points", xytext=(0,10), ha='center', fontsize=9) ax3.set_xlabel('Forecast Horizon (Weeks)', fontweight='bold') ax3.set_ylabel('MAE (Millions USD)', fontweight='bold') ax3.set_title('Degradation Curve: MAE vs Horizon', fontweight='bold') ax3.legend(loc='upper left') ax3.grid(True, alpha=0.3) ax3.set_xlim(0, 30) # ------ Plot 4-7: Actual vs Forecast for each window ------ for i, r in enumerate(results): ax = fig.add_subplot(2, 4, 5 + i) weeks_plot = r['Test_Week_Numbers'] ax.fill_between(weeks_plot, r['Lower_95']/1e6, r['Upper_95']/1e6, color=r['Color'], alpha=0.3) ax.plot(weeks_plot, r['Actuals']/1e6, 'o-', color=AZ_COLORS['navy'], linewidth=2, markersize=8, label='Actual') ax.plot(weeks_plot, r['Predictions']/1e6, 's--', color=r['Color'], linewidth=2, markersize=6, label='Prophet') ax.set_xlabel('Week', fontweight='bold') ax.set_ylabel('Cash Flow ($M)', fontweight='bold') ax.set_title(f'{r["Horizon"]} Horizon\nMAE: ${r["MAE"]/1e6:.2f}M, SMAPE: {r["SMAPE"]:.0f}%', fontweight='bold', fontsize=10) ax.legend(loc='best', fontsize=8) ax.grid(True, alpha=0.3) ax.axhline(y=0, color='gray', linestyle='-', linewidth=1) plt.tight_layout() plt.savefig('dashboard/02_option_b1_rolling_validation.png', dpi=300, bbox_inches='tight', facecolor='white') plt.close() print(" Saved: dashboard/02_option_b1_rolling_validation.png") # ============================================================================= # CREATE DETAILED COMPARISON FIGURE # ============================================================================= fig2, axes = plt.subplots(1, 2, figsize=(14, 6)) fig2.suptitle('Forecast Accuracy Degradation Analysis', fontsize=14, fontweight='bold', color=AZ_COLORS['navy']) # Left: MAE degradation ax_left = axes[0] ax_left.scatter(weeks, mae_values, c=colors, s=200, zorder=5, edgecolors=AZ_COLORS['navy'], linewidths=2) ax_left.plot(x_line, y_line, '--', color=AZ_COLORS['navy'], linewidth=2) ax_left.scatter([26], [extrapolated_6m_mae/1e6], c=AZ_COLORS['mulberry'], s=300, marker='*', zorder=6) ax_left.set_xlabel('Forecast Horizon (Weeks)', fontweight='bold', fontsize=12) ax_left.set_ylabel('MAE (Millions USD)', fontweight='bold', fontsize=12) ax_left.set_title('MAE Degradation', fontweight='bold', fontsize=12) ax_left.grid(True, alpha=0.3) # Add labels for i, (w, m, h) in enumerate(zip(weeks, mae_values, horizon_labels)): ax_left.annotate(h, (w, m), textcoords="offset points", xytext=(5,5), fontsize=10, fontweight='bold') ax_left.annotate('6-Month', (26, extrapolated_6m_mae/1e6), textcoords="offset points", xytext=(5,5), fontsize=10, fontweight='bold', color=AZ_COLORS['mulberry']) # Right: Summary table ax_right = axes[1] ax_right.axis('off') summary_text = f""" ROLLING WINDOW VALIDATION SUMMARY ================================= Training Period: Weeks 1-28 +------------+--------+----------+----------+---------+ | Horizon | Weeks | MAE | RMSE | SMAPE | +------------+--------+----------+----------+---------+ | 1 Month | 4 | ${results_df.iloc[0]['MAE']/1e6:>5.2f}M | ${results_df.iloc[0]['RMSE']/1e6:>5.2f}M | {results_df.iloc[0]['SMAPE']:>5.0f}% | | 2 Months | 8 | ${results_df.iloc[1]['MAE']/1e6:>5.2f}M | ${results_df.iloc[1]['RMSE']/1e6:>5.2f}M | {results_df.iloc[1]['SMAPE']:>5.0f}% | | 3 Months | 12 | ${results_df.iloc[2]['MAE']/1e6:>5.2f}M | ${results_df.iloc[2]['RMSE']/1e6:>5.2f}M | {results_df.iloc[2]['SMAPE']:>5.0f}% | | 4 Months | 16 | ${results_df.iloc[3]['MAE']/1e6:>5.2f}M | ${results_df.iloc[3]['RMSE']/1e6:>5.2f}M | {results_df.iloc[3]['SMAPE']:>5.0f}% | +------------+--------+----------+----------+---------+ DEGRADATION ANALYSIS ==================== MAE increases by: ${mae_per_month/1e6:.2f}M per month Linear fit R-squared: {r_value**2:.3f} 6-MONTH EXTRAPOLATION ===================== Expected MAE: ${extrapolated_6m_mae/1e6:.2f}M Expected SMAPE: {extrapolated_6m_smape:.0f}% Confidence: {'High' if r_value**2 > 0.7 else 'Moderate' if r_value**2 > 0.5 else 'Low'} """ ax_right.text(0.05, 0.95, summary_text, transform=ax_right.transAxes, fontsize=10, verticalalignment='top', fontfamily='monospace', bbox=dict(boxstyle='round', facecolor=AZ_COLORS['lime_green'], alpha=0.2)) plt.tight_layout() plt.savefig('dashboard/02_option_b1_degradation_analysis.png', dpi=300, bbox_inches='tight', facecolor='white') plt.close() print(" Saved: dashboard/02_option_b1_degradation_analysis.png") # ============================================================================= # SAVE RESULTS # ============================================================================= print("\n[6/7] Saving results...") results_df.to_csv('option_b1_results.csv', index=False) print(" Saved: option_b1_results.csv") # ============================================================================= # FINAL SUMMARY # ============================================================================= print("\n" + "=" * 80) print("PHASE 2A COMPLETE: OPTION B1 - ROLLING WINDOW VALIDATION") print("=" * 80) print(f""" ROLLING WINDOW VALIDATION RESULTS: Training Period: Weeks 1-{train_end} ({train_end} weeks) +------------+--------+----------+----------+---------+ | Horizon | Weeks | MAE | SMAPE | CI Cov | +------------+--------+----------+----------+---------+ | 1 Month | 4 | ${results_df.iloc[0]['MAE']/1e6:>5.2f}M | {results_df.iloc[0]['SMAPE']:>5.0f}% | {results_df.iloc[0]['CI_Coverage']:>4.0f}% | | 2 Months | 8 | ${results_df.iloc[1]['MAE']/1e6:>5.2f}M | {results_df.iloc[1]['SMAPE']:>5.0f}% | {results_df.iloc[1]['CI_Coverage']:>4.0f}% | | 3 Months | 12 | ${results_df.iloc[2]['MAE']/1e6:>5.2f}M | {results_df.iloc[2]['SMAPE']:>5.0f}% | {results_df.iloc[2]['CI_Coverage']:>4.0f}% | | 4 Months | 16 | ${results_df.iloc[3]['MAE']/1e6:>5.2f}M | {results_df.iloc[3]['SMAPE']:>5.0f}% | {results_df.iloc[3]['CI_Coverage']:>4.0f}% | +------------+--------+----------+----------+---------+ DEGRADATION ANALYSIS: - MAE increases by ${mae_per_month/1e6:.2f}M per month - Linear fit R-squared: {r_value**2:.3f} - Pattern: {'Gradual, Predictable' if slope_mae > 0 else 'Stable'} 6-MONTH PRODUCTION FORECAST EXPECTATION: - Expected MAE: ${extrapolated_6m_mae/1e6:.2f}M - Expected SMAPE: {extrapolated_6m_smape:.0f}% - Confidence in extrapolation: {'High' if r_value**2 > 0.7 else 'Moderate' if r_value**2 > 0.5 else 'Low'} FILES CREATED: - dashboard/02_option_b1_rolling_validation.png - dashboard/02_option_b1_degradation_analysis.png - option_b1_results.csv ==> NEXT STEP: Phase 2B - Option B2 (Monthly Cross-Validation) """) # Save report report = f""" # OPTION B1 VALIDATION REPORT # Rolling Window Cross-Validation # Generated: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')} ## 1. Methodology Rolling window validation with fixed training period (weeks 1-{train_end}). Tested 4 forecast horizons: 1, 2, 3, and 4 months. ## 2. Results | Horizon | Test Weeks | MAE | RMSE | SMAPE | 95% CI Coverage | |---------|------------|-----|------|-------|-----------------| | 1 Month | 4 | ${results_df.iloc[0]['MAE']:,.0f} | ${results_df.iloc[0]['RMSE']:,.0f} | {results_df.iloc[0]['SMAPE']:.1f}% | {results_df.iloc[0]['CI_Coverage']:.0f}% | | 2 Months | 8 | ${results_df.iloc[1]['MAE']:,.0f} | ${results_df.iloc[1]['RMSE']:,.0f} | {results_df.iloc[1]['SMAPE']:.1f}% | {results_df.iloc[1]['CI_Coverage']:.0f}% | | 3 Months | 12 | ${results_df.iloc[2]['MAE']:,.0f} | ${results_df.iloc[2]['RMSE']:,.0f} | {results_df.iloc[2]['SMAPE']:.1f}% | {results_df.iloc[2]['CI_Coverage']:.0f}% | | 4 Months | 16 | ${results_df.iloc[3]['MAE']:,.0f} | ${results_df.iloc[3]['RMSE']:,.0f} | {results_df.iloc[3]['SMAPE']:.1f}% | {results_df.iloc[3]['CI_Coverage']:.0f}% | ## 3. Degradation Analysis - MAE increases by ${mae_per_month:,.0f} per month - Linear fit R-squared: {r_value**2:.3f} ## 4. 6-Month Extrapolation - Expected MAE: ${extrapolated_6m_mae:,.0f} - Expected SMAPE: {extrapolated_6m_smape:.1f}% ## 5. Conclusion Prophet demonstrates predictable degradation pattern. 6-month forecast is feasible with expected MAE of ${extrapolated_6m_mae/1e6:.2f}M. """ with open('VALIDATION_OPTION_B1_REPORT.md', 'w') as f: f.write(report) print(" Saved: VALIDATION_OPTION_B1_REPORT.md")