""" ================================================================================ PHASE 5: EXECUTIVE DASHBOARD ================================================================================ AstraZeneca Cash Flow Forecasting - Datathon 2025 Purpose: Create comprehensive executive dashboard consolidating all key insights - Model performance summary - 6-month forecast overview - Category analysis highlights - Accuracy metrics - Risk assessment - Key recommendations This is the "master dashboard" for stakeholders """ 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') import os # 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 5: EXECUTIVE DASHBOARD") print("=" * 80) # ============================================================================= # LOAD DATA # ============================================================================= print("\n[1/6] Loading data...") # Historical 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) # Forecast data forecast_df = pd.read_csv('FORECAST_OUTPUT_FINAL.csv') forecast_df['Week_Start'] = pd.to_datetime(forecast_df['Week_Start']) # Accuracy summary accuracy_df = pd.read_csv('accuracy_summary.csv') # Category analysis category_df = pd.read_csv('category_analysis_summary.csv') # Starting balance balance_df = pd.read_csv('data_cash_balance_processed.csv') total_starting_balance = balance_df['Carryforward Balance (USD)'].sum() print(f" Historical: {len(df)} weeks") print(f" Forecast: {len(forecast_df)} weeks") print(f" Starting balance: ${total_starting_balance:,.0f}") # ============================================================================= # CALCULATE KEY METRICS # ============================================================================= print("\n[2/6] Calculating key metrics...") # Historical summary historical_total = df['Net_Cash_Flow_USD'].sum() historical_avg = df['Net_Cash_Flow_USD'].mean() historical_std = df['Net_Cash_Flow_USD'].std() # Forecast summary forecast_total = forecast_df['Net_Cash_Flow_Total_USD'].sum() forecast_avg = forecast_df['Net_Cash_Flow_Total_USD'].mean() final_balance = forecast_df['Ending_Balance_Total_USD'].iloc[-1] # Accuracy metrics avg_mae = accuracy_df['MAE_USD'].mean() / 1e6 avg_direction = accuracy_df['Direction_Accuracy_Pct'].mean() # Category insights ar_total = category_df[category_df['Category'] == 'AR']['Total'].values[0] if 'AR' in category_df['Category'].values else 0 ap_total = abs(category_df[category_df['Category'] == 'AP']['Total'].values[0]) if 'AP' in category_df['Category'].values else 0 print(f" Historical total: ${historical_total/1e6:.2f}M") print(f" Forecast total: ${forecast_total/1e6:.2f}M") print(f" Final balance: ${final_balance/1e6:.2f}M") # ============================================================================= # CREATE EXECUTIVE DASHBOARD # ============================================================================= print("\n[3/6] Creating executive dashboard...") os.makedirs('dashboard', exist_ok=True) fig = plt.figure(figsize=(24, 18)) fig.suptitle('EXECUTIVE DASHBOARD - ASTRAZENECA CASH FLOW FORECASTING\nDatathon 2025 - Comprehensive Analysis & 6-Month Forecast', fontsize=20, fontweight='bold', color=AZ_COLORS['navy'], y=0.98) # ============== ROW 1: OVERVIEW METRICS ============== # Plot 1: Key Performance Indicators ax1 = fig.add_subplot(3, 4, 1) ax1.axis('off') kpi_text = f""" KEY PERFORMANCE INDICATORS ========================== MODEL ACCURACY -------------- Direction Accuracy: {avg_direction:.0f}% Average MAE: ${avg_mae:.2f}M Threshold MAPE: 30.7% FORECAST SUMMARY ----------------- 6-Month Net CF: ${forecast_total/1e6:.2f}M Final Balance: ${final_balance/1e6:.2f}M Starting Balance: ${total_starting_balance/1e6:.2f}M HISTORICAL CONTEXT ------------------ 44 Weeks Total: ${historical_total/1e6:.2f}M Average Weekly: ${historical_avg/1e6:.2f}M """ ax1.text(0.05, 0.95, kpi_text, transform=ax1.transAxes, fontsize=10, verticalalignment='top', fontfamily='monospace', bbox=dict(boxstyle='round', facecolor=AZ_COLORS['lime_green'], alpha=0.2)) # Plot 2: Historical vs Forecast Timeline ax2 = fig.add_subplot(3, 4, 2) hist_weeks = df['Week_Number'].values hist_values = df['Net_Cash_Flow_USD'].values / 1e6 forecast_weeks = forecast_df['Week_Number'].values forecast_values = forecast_df['Net_Cash_Flow_Total_USD'].values / 1e6 forecast_lower = forecast_df['Net_Cash_Flow_Lower_95'].values / 1e6 forecast_upper = forecast_df['Net_Cash_Flow_Upper_95'].values / 1e6 ax2.plot(hist_weeks, hist_values, 'o-', color=AZ_COLORS['navy'], linewidth=2, markersize=4, label='Historical', alpha=0.8) ax2.plot(forecast_weeks, forecast_values, 'o-', color=AZ_COLORS['lime_green'], linewidth=2, markersize=4, label='Forecast') ax2.fill_between(forecast_weeks, forecast_lower, forecast_upper, color=AZ_COLORS['lime_green'], alpha=0.2, label='95% CI') ax2.axvline(x=44.5, color=AZ_COLORS['mulberry'], linestyle='--', linewidth=2) ax2.axhline(y=0, color=AZ_COLORS['graphite'], linewidth=1, linestyle='-', alpha=0.5) ax2.set_xlabel('Week Number', fontweight='bold') ax2.set_ylabel('Net Cash Flow (Millions USD)', fontweight='bold') ax2.set_title('Complete Timeline: Historical + 6-Month Forecast', fontweight='bold', fontsize=11) ax2.legend(loc='upper left') ax2.grid(True, alpha=0.3) # Plot 3: Ending Balance Projection ax3 = fig.add_subplot(3, 4, 3) end_bal = forecast_df['Ending_Balance_Total_USD'].values / 1e6 end_bal_lower = forecast_df['Ending_Balance_Lower_95'].values / 1e6 end_bal_upper = forecast_df['Ending_Balance_Upper_95'].values / 1e6 ax3.plot(forecast_weeks, end_bal, 'o-', color=AZ_COLORS['navy'], linewidth=3, markersize=6, label='Most Likely', zorder=5) ax3.fill_between(forecast_weeks, end_bal_lower, end_bal_upper, color=AZ_COLORS['light_blue'], alpha=0.4, label='95% CI') ax3.axhline(y=total_starting_balance/1e6, color=AZ_COLORS['gold'], linestyle='--', linewidth=2, label=f'Starting: ${total_starting_balance/1e6:.1f}M') ax3.axhline(y=0, color=AZ_COLORS['magenta'], linestyle='-', linewidth=2, label='Zero Threshold') ax3.set_xlabel('Week Number', fontweight='bold') ax3.set_ylabel('Ending Balance (Millions USD)', fontweight='bold') ax3.set_title('Ending Cash Balance Projection (6 Months)', fontweight='bold', fontsize=11) ax3.legend(loc='best', fontsize=8) ax3.grid(True, alpha=0.3) # Plot 4: Category Contribution ax4 = fig.add_subplot(3, 4, 4) # Top categories from historical top_categories = category_df.nlargest(8, 'Total') categories = top_categories['Category'].values values = top_categories['Total'].values / 1e6 colors_cat = [AZ_COLORS['lime_green'] if v > 0 else AZ_COLORS['magenta'] for v in values] bars = ax4.barh(categories, values, color=colors_cat, edgecolor=AZ_COLORS['navy'], linewidth=1.5) ax4.axvline(x=0, color=AZ_COLORS['graphite'], linewidth=2) ax4.set_xlabel('Total (Millions USD)', fontweight='bold') ax4.set_title('Top Categories (44 Weeks Historical)', fontweight='bold', fontsize=11) for bar, val in zip(bars, values): x_pos = val + 0.5 if val > 0 else val - 0.5 ha = 'left' if val > 0 else 'right' ax4.text(x_pos, bar.get_y() + bar.get_height()/2, f'${val:.1f}M', ha=ha, va='center', fontweight='bold', fontsize=9) ax4.grid(True, alpha=0.3, axis='x') # ============== ROW 2: ACCURACY & VALIDATION ============== # Plot 5: Accuracy Metrics Summary ax5 = fig.add_subplot(3, 4, 5) methods = accuracy_df['Validation_Method'].values mae_vals = accuracy_df['MAE_USD'].values / 1e6 dir_vals = accuracy_df['Direction_Accuracy_Pct'].values x_pos = np.arange(len(methods)) width = 0.35 bars1 = ax5.bar(x_pos - width/2, mae_vals, width, label='MAE (M USD)', color=AZ_COLORS['navy'], edgecolor='white', linewidth=1) ax5_twin = ax5.twinx() bars2 = ax5_twin.bar(x_pos + width/2, dir_vals, width, label='Direction (%)', color=AZ_COLORS['lime_green'], edgecolor='white', linewidth=1) ax5.set_xlabel('Validation Method', fontweight='bold') ax5.set_ylabel('MAE (Millions USD)', fontweight='bold', color=AZ_COLORS['navy']) ax5_twin.set_ylabel('Direction Accuracy (%)', fontweight='bold', color=AZ_COLORS['lime_green']) ax5.set_xticks(x_pos) ax5.set_xticklabels([m.replace(' ', '\n') for m in methods], fontsize=8, rotation=0) ax5.set_title('Model Accuracy Across Validation Methods', fontweight='bold', fontsize=11) ax5.tick_params(axis='y', labelcolor=AZ_COLORS['navy']) ax5_twin.tick_params(axis='y', labelcolor=AZ_COLORS['lime_green']) ax5.grid(True, alpha=0.3, axis='y') # Plot 6: Forecast Horizon Degradation ax6 = fig.add_subplot(3, 4, 6) horizons = ['1M', '2M', '3M', '4M'] horizon_mae = [1.36, 1.73, 1.90, 1.91] horizon_dir = [75.0, 62.5, 58.3, 62.5] ax6.plot(horizons, horizon_mae, 'o-', color=AZ_COLORS['navy'], linewidth=3, markersize=10, label='MAE (M USD)') ax6_twin = ax6.twinx() ax6_twin.plot(horizons, horizon_dir, 's--', color=AZ_COLORS['lime_green'], linewidth=3, markersize=10, label='Direction (%)') ax6.set_xlabel('Forecast Horizon', fontweight='bold') ax6.set_ylabel('MAE (Millions USD)', fontweight='bold', color=AZ_COLORS['navy']) ax6_twin.set_ylabel('Direction Accuracy (%)', fontweight='bold', color=AZ_COLORS['lime_green']) ax6.set_title('Accuracy Degradation by Horizon', fontweight='bold', fontsize=11) ax6.tick_params(axis='y', labelcolor=AZ_COLORS['navy']) ax6_twin.tick_params(axis='y', labelcolor=AZ_COLORS['lime_green']) ax6.grid(True, alpha=0.3) # Plot 7: Entity Forecast Summary ax7 = fig.add_subplot(3, 4, 7) entity_cols = [col for col in forecast_df.columns if col.startswith('Net_Cash_') and col.split('_')[-1] in ['TW10', 'MY10', 'ID10', 'KR10', 'PH10', 'SS10', 'TH10', 'VN20']] entities = [col.split('_')[-1] for col in entity_cols] entity_totals = [forecast_df[col].sum() / 1e6 for col in entity_cols] colors_ent = [AZ_COLORS['lime_green'] if v > 0 else AZ_COLORS['magenta'] for v in entity_totals] bars = ax7.bar(entities, entity_totals, color=colors_ent, edgecolor=AZ_COLORS['navy'], linewidth=1.5) ax7.axhline(y=0, color=AZ_COLORS['graphite'], linewidth=2) ax7.set_xlabel('Entity', fontweight='bold') ax7.set_ylabel('26-Week Total (Millions USD)', fontweight='bold') ax7.set_title('Entity-Level Forecast (6 Months)', fontweight='bold', fontsize=11) for bar, val in zip(bars, entity_totals): height = bar.get_height() ax7.text(bar.get_x() + bar.get_width()/2., height + (0.1 if height > 0 else -0.2), f'${val:.1f}M', ha='center', va='bottom' if height > 0 else 'top', fontweight='bold', fontsize=8) ax7.grid(True, alpha=0.3, axis='y') # Plot 8: Risk Assessment ax8 = fig.add_subplot(3, 4, 8) ax8.axis('off') risk_weeks = (forecast_df['Ending_Balance_Total_USD'] < 0).sum() risk_status = "HEALTHY" if risk_weeks == 0 else "AT RISK" risk_text = f""" RISK ASSESSMENT =============== LIQUIDITY STATUS ---------------- Status: {risk_status} Weeks at Risk: {risk_weeks} / 26 Final Balance: ${final_balance/1e6:.2f}M FORECAST SCENARIOS ------------------ Best Case (95%): ${forecast_df['Ending_Balance_Upper_95'].iloc[-1]/1e6:.2f}M Most Likely: ${final_balance/1e6:.2f}M Worst Case (95%): ${forecast_df['Ending_Balance_Lower_95'].iloc[-1]/1e6:.2f}M KEY INSIGHTS ------------ • AR drives 85% of inflows • Netting AP drives 52% of outflows • Model accuracy: 60% direction, $1.84M MAE • No liquidity risk detected """ ax8.text(0.05, 0.95, risk_text, transform=ax8.transAxes, fontsize=10, verticalalignment='top', fontfamily='monospace', bbox=dict(boxstyle='round', facecolor=AZ_COLORS['light_blue'], alpha=0.2)) # ============== ROW 3: CATEGORY ANALYSIS ============== # Plot 9: AR vs AP Comparison ax9 = fig.add_subplot(3, 4, 9) ar_hist = df['Net_Cash_AR'].values / 1e6 ap_hist = df['Net_Cash_AP'].values / 1e6 weeks = df['Week_Number'].values ax9.plot(weeks, ar_hist, 'o-', color=AZ_COLORS['lime_green'], linewidth=2, markersize=4, label='AR (Receivables)', alpha=0.8) ax9.plot(weeks, ap_hist, 's-', color=AZ_COLORS['magenta'], linewidth=2, markersize=4, label='AP (Payables)', alpha=0.8) ax9.axhline(y=0, color=AZ_COLORS['graphite'], linewidth=1, linestyle='--') ax9.set_xlabel('Week Number', fontweight='bold') ax9.set_ylabel('Cash Flow (Millions USD)', fontweight='bold') ax9.set_title('AR vs AP Historical Patterns', fontweight='bold', fontsize=11) ax9.legend() ax9.grid(True, alpha=0.3) # Plot 10: Category Forecast (Top 5) ax10 = fig.add_subplot(3, 4, 10) cat_forecast_cols = ['Net_Cash_AR', 'Net_Cash_AP', 'Net_Cash_Payroll', 'Net_Cash_Tax_payable', 'Net_Cash_Loan_receipt'] cat_names = ['AR', 'AP', 'Payroll', 'Tax', 'Loan'] cat_totals = [forecast_df[col].sum() / 1e6 if col in forecast_df.columns else 0 for col in cat_forecast_cols] colors_cat_f = [AZ_COLORS['lime_green'] if v > 0 else AZ_COLORS['magenta'] for v in cat_totals] bars = ax10.barh(cat_names, cat_totals, color=colors_cat_f, edgecolor=AZ_COLORS['navy'], linewidth=1.5) ax10.axvline(x=0, color=AZ_COLORS['graphite'], linewidth=2) ax10.set_xlabel('26-Week Total (Millions USD)', fontweight='bold') ax10.set_title('Category Forecast Summary', fontweight='bold', fontsize=11) for bar, val in zip(bars, cat_totals): x_pos = val + 1 if val > 0 else val - 1 ha = 'left' if val > 0 else 'right' ax10.text(x_pos, bar.get_y() + bar.get_height()/2, f'${val:.1f}M', ha=ha, va='center', fontweight='bold', fontsize=9) ax10.grid(True, alpha=0.3, axis='x') # Plot 11: Model Selection Summary ax11 = fig.add_subplot(3, 4, 11) ax11.axis('off') model_text = f""" MODEL SELECTION =============== SELECTED MODEL -------------- Algorithm: Prophet (Facebook) Seasonality: Monthly (30.5-day period) Fourier Order: 5 Confidence: 95% intervals VALIDATION APPROACH ------------------- ✓ Model Comparison (Prophet vs MA vs ExpSmooth) ✓ Forward Validation (1-month holdout) ✓ Rolling Windows (1-4 month horizons) ✓ Monthly Cross-Val (10 months tested) PERFORMANCE ----------- Direction Accuracy: {avg_direction:.0f}% Average MAE: ${avg_mae:.2f}M Threshold MAPE: 30.7% CI Coverage: 86% WHY PROPHET? ------------ • Lowest MAE ($1.84M vs $3.72M vs $4.80M) • Automatic seasonality detection • Provides confidence intervals • Business-friendly decomposition """ ax11.text(0.05, 0.95, model_text, transform=ax11.transAxes, fontsize=9, verticalalignment='top', fontfamily='monospace', bbox=dict(boxstyle='round', facecolor=AZ_COLORS['gold'], alpha=0.2)) # Plot 12: Key Recommendations ax12 = fig.add_subplot(3, 4, 12) ax12.axis('off') recommendations_text = f""" KEY RECOMMENDATIONS =================== FORECASTING ----------- 1. Use Prophet for 1-4 month forecasts (60-75% direction accuracy) 2. For 6-month forecasts, expect: • ~60% direction accuracy • ~$2.0-2.5M average error • Update monthly as new data arrives 3. Monitor confidence intervals: • 95% CI provides risk bounds • Use for scenario planning CASH FLOW MANAGEMENT -------------------- 1. AR Collection: Monitor closely (drives 85% of inflows) 2. Netting Operations: Review timing (drives 52% of outflows) 3. Liquidity: Currently healthy (no negative balance weeks) 4. Entity Monitoring: Focus on: • TW10, VN20 (largest positive) • KR10 (largest negative) NEXT STEPS ---------- • Implement monthly forecast updates • Set up automated anomaly alerts • Create entity-specific dashboards • Review netting operations timing """ ax12.text(0.05, 0.95, recommendations_text, transform=ax12.transAxes, fontsize=9, verticalalignment='top', fontfamily='monospace', bbox=dict(boxstyle='round', facecolor=AZ_COLORS['purple'], alpha=0.2)) plt.tight_layout() plt.savefig('dashboard/08_executive_dashboard.png', dpi=300, bbox_inches='tight', facecolor='white') plt.close() print(" Saved: dashboard/08_executive_dashboard.png") # ============================================================================= # FINAL SUMMARY # ============================================================================= print("\n" + "=" * 80) print("PHASE 5 COMPLETE: EXECUTIVE DASHBOARD") print("=" * 80) print(f""" EXECUTIVE DASHBOARD CREATED =========================== Dashboard Components: 1. Key Performance Indicators (KPI summary) 2. Historical + Forecast Timeline (complete view) 3. Ending Balance Projection (6-month outlook) 4. Category Contribution (top drivers) 5. Accuracy Metrics (validation summary) 6. Forecast Horizon Degradation (accuracy over time) 7. Entity Forecast Summary (8 entities) 8. Risk Assessment (liquidity status) 9. AR vs AP Comparison (category patterns) 10. Category Forecast Summary (top 5 categories) 11. Model Selection Summary (why Prophet) 12. Key Recommendations (action items) FILES CREATED: - dashboard/08_executive_dashboard.png (master dashboard) ==> Ready for Phase 7 (Analysis & Recommendations) and Phase 8 (Documentation) """)