""" ================================================================================ PHASE 3: OPTION C - PRODUCTION 6-MONTH FORECAST ================================================================================ AstraZeneca Cash Flow Forecasting - Datathon 2025 Purpose: Generate final competition deliverable using validated Prophet model - Train Prophet on ALL 44 weeks of historical data - Forecast 26 weeks ahead (Nov 2025 - Apr 2026) - Generate confidence intervals and probabilistic scenarios - Create FORECAST_OUTPUT_FINAL.csv Expected Output: - Weekly Net Cash Flow forecast (26 weeks) - Weekly Ending Cash Balance forecast - Confidence intervals (80% and 95%) - Probabilistic scenarios (Best/Most Likely/Worst case) """ import pandas as pd import numpy as np import matplotlib.pyplot as plt import seaborn as sns from datetime import datetime, timedelta import warnings warnings.filterwarnings('ignore') from sklearn.metrics import mean_absolute_error, mean_squared_error from prophet import Prophet 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 3: OPTION C - PRODUCTION 6-MONTH FORECAST") print("=" * 80) # ============================================================================= # LOAD DATA # ============================================================================= print("\n[1/9] Loading data...") # 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) # Starting balances balance_df = pd.read_csv('data_cash_balance_processed.csv') print(f" Historical data: {len(df)} weeks") print(f" Date range: {df['Week_Start'].min().date()} to {df['Week_Start'].max().date()}") print(f" Starting balance entities: {len(balance_df)}") # Total starting balance total_starting_balance = balance_df['Carryforward Balance (USD)'].sum() print(f" Total starting balance: ${total_starting_balance:,.2f}") # Entity balances entity_balances = balance_df.set_index('Name')['Carryforward Balance (USD)'].to_dict() print(f"\n Entity Starting Balances (USD):") for entity, balance in entity_balances.items(): print(f" {entity}: ${balance:,.0f}") # ============================================================================= # FORECAST CONFIGURATION # ============================================================================= print("\n[2/9] Configuring forecast parameters...") FORECAST_WEEKS = 26 # 6 months TRAIN_WEEKS = len(df) # All historical data (44 weeks) LAST_WEEK_DATE = df['Week_Start'].max() LAST_WEEK_NUMBER = df['Week_Number'].max() # Generate future dates (weekly frequency) future_dates = pd.date_range( start=LAST_WEEK_DATE + timedelta(weeks=1), periods=FORECAST_WEEKS, freq='W-MON' ) print(f" Training period: Weeks 1-{TRAIN_WEEKS} (Dec 30, 2024 - Oct 27, 2025)") print(f" Forecast period: Weeks {TRAIN_WEEKS+1}-{TRAIN_WEEKS+FORECAST_WEEKS} ({future_dates[0].date()} - {future_dates[-1].date()})") print(f" Forecast horizon: {FORECAST_WEEKS} weeks (6 months)") # ============================================================================= # BUILD PRODUCTION PROPHET MODEL - TOTAL CASH FLOW # ============================================================================= print("\n[3/9] Building production Prophet model (Total Cash Flow)...") # Prepare training data prophet_df = pd.DataFrame({ 'ds': df['Week_Start'], 'y': df['Net_Cash_Flow_USD'] }) # Initialize Prophet with optimized settings (from validation phases) model_total = Prophet( yearly_seasonality=False, # Not enough data for yearly patterns weekly_seasonality=False, # Already aggregated to weekly daily_seasonality=False, # Not applicable seasonality_mode='additive', interval_width=0.95, # 95% confidence intervals changepoint_prior_scale=0.05, # Moderate flexibility (validated) mcmc_samples=0 # Fast training (no MCMC) ) # Add monthly seasonality (AP/AR payment cycles ~ 4 weeks) model_total.add_seasonality( name='monthly', period=30.5, fourier_order=5 ) # Fit the model print(" Training Prophet on all historical data...") model_total.fit(prophet_df) print(" Model trained successfully!") # ============================================================================= # GENERATE TOTAL CASH FLOW FORECAST # ============================================================================= print("\n[4/9] Generating total cash flow forecast...") # Create future dataframe future_df = pd.DataFrame({'ds': future_dates}) # Make predictions forecast_total = model_total.predict(future_df) # Extract forecast components forecast_values = forecast_total['yhat'].values forecast_lower_95 = forecast_total['yhat_lower'].values forecast_upper_95 = forecast_total['yhat_upper'].values # Calculate 80% CI (narrower) # Prophet's interval_width=0.95 gives 95% CI, approximate 80% CI ci_ratio = 1.28 / 1.96 # z-score ratio for 80% vs 95% forecast_std = (forecast_upper_95 - forecast_lower_95) / (2 * 1.96) forecast_lower_80 = forecast_values - (1.28 * forecast_std) forecast_upper_80 = forecast_values + (1.28 * forecast_std) print(f" Forecast generated for {len(forecast_values)} weeks") print(f" Total Net Cash Flow Forecast Range:") print(f" Most Likely: ${forecast_values.sum()/1e6:.2f}M") print(f" Best Case (95%): ${forecast_upper_95.sum()/1e6:.2f}M") print(f" Worst Case (95%): ${forecast_lower_95.sum()/1e6:.2f}M") # ============================================================================= # BUILD ENTITY-LEVEL FORECASTS # ============================================================================= print("\n[5/9] Building entity-level forecasts...") # Entity columns from historical data entity_columns = [col for col in df.columns if col.startswith('Net_Cash_')] entity_columns = [col for col in entity_columns if col.split('_')[-1] in entity_balances.keys()] entity_forecasts = {} for entity in entity_balances.keys(): col_name = f'Net_Cash_{entity}' if col_name in df.columns: print(f" Forecasting {entity}...", end='') # Prepare entity data entity_prophet_df = pd.DataFrame({ 'ds': df['Week_Start'], 'y': df[col_name] }) # Build entity model model_entity = Prophet( yearly_seasonality=False, weekly_seasonality=False, daily_seasonality=False, seasonality_mode='additive', interval_width=0.95, changepoint_prior_scale=0.05 ) model_entity.add_seasonality(name='monthly', period=30.5, fourier_order=5) model_entity.fit(entity_prophet_df) # Forecast forecast_entity = model_entity.predict(future_df) entity_forecasts[entity] = { 'yhat': forecast_entity['yhat'].values, 'yhat_lower': forecast_entity['yhat_lower'].values, 'yhat_upper': forecast_entity['yhat_upper'].values } print(f" Done! Total: ${forecast_entity['yhat'].sum()/1e6:.2f}M") else: print(f" WARNING: {entity} not found in data, skipping...") print(f"\n Entity forecasts completed: {len(entity_forecasts)}/8") # ============================================================================= # BUILD CATEGORY-LEVEL FORECASTS # ============================================================================= print("\n[6/9] Building category-level forecasts...") # Category columns from historical data category_columns = ['Net_Cash_AR', 'Net_Cash_AP', 'Net_Cash_Netting_AP', 'Net_Cash_Netting_AR', 'Net_Cash_Payroll', 'Net_Cash_Tax_payable', 'Net_Cash_Loan_receipt', 'Net_Cash_Loan_payment', 'Net_Cash_Other_receipt'] category_forecasts = {} for cat_col in category_columns: if cat_col in df.columns: cat_name = cat_col.replace('Net_Cash_', '') print(f" Forecasting {cat_name}...", end='') # Prepare category data cat_prophet_df = pd.DataFrame({ 'ds': df['Week_Start'], 'y': df[cat_col] }) # Build category model model_cat = Prophet( yearly_seasonality=False, weekly_seasonality=False, daily_seasonality=False, seasonality_mode='additive', interval_width=0.95, changepoint_prior_scale=0.1 # Slightly more flexible for categories ) model_cat.add_seasonality(name='monthly', period=30.5, fourier_order=3) model_cat.fit(cat_prophet_df) # Forecast forecast_cat = model_cat.predict(future_df) category_forecasts[cat_name] = { 'yhat': forecast_cat['yhat'].values, 'yhat_lower': forecast_cat['yhat_lower'].values, 'yhat_upper': forecast_cat['yhat_upper'].values } print(f" Done! Total: ${forecast_cat['yhat'].sum()/1e6:.2f}M") print(f"\n Category forecasts completed: {len(category_forecasts)}") # ============================================================================= # CALCULATE ENDING CASH BALANCES # ============================================================================= print("\n[7/9] Calculating ending cash balances...") # Get last known cumulative cash flow last_cumulative = df['Cumulative_Cash_Flow'].iloc[-1] print(f" Last historical cumulative: ${last_cumulative:,.0f}") # Total ending balance calculation # Ending Balance = Starting Balance + Last Cumulative + Cumulative Forecast ending_balance_total = [] ending_balance_lower = [] ending_balance_upper = [] cumulative_forecast = 0 cumulative_lower = 0 cumulative_upper = 0 for i in range(FORECAST_WEEKS): cumulative_forecast += forecast_values[i] cumulative_lower += forecast_lower_95[i] cumulative_upper += forecast_upper_95[i] # Ending balance = starting + historical cumulative + forecast cumulative end_bal = total_starting_balance + last_cumulative + cumulative_forecast end_bal_lower = total_starting_balance + last_cumulative + cumulative_lower end_bal_upper = total_starting_balance + last_cumulative + cumulative_upper ending_balance_total.append(end_bal) ending_balance_lower.append(end_bal_lower) ending_balance_upper.append(end_bal_upper) ending_balance_total = np.array(ending_balance_total) ending_balance_lower = np.array(ending_balance_lower) ending_balance_upper = np.array(ending_balance_upper) print(f"\n Final Ending Balance (Week {TRAIN_WEEKS + FORECAST_WEEKS}):") print(f" Most Likely: ${ending_balance_total[-1]:,.0f}") print(f" Best Case (95%): ${ending_balance_upper[-1]:,.0f}") print(f" Worst Case (95%): ${ending_balance_lower[-1]:,.0f}") # Check for liquidity risk risk_weeks = np.where(ending_balance_total < 0)[0] if len(risk_weeks) > 0: print(f"\n [!] LIQUIDITY WARNING: Negative balance in weeks: {[w + TRAIN_WEEKS + 1 for w in risk_weeks]}") else: print(f"\n [OK] No liquidity risk detected (all balances positive)") # Entity ending balances entity_ending_balances = {} for entity, starting_bal in entity_balances.items(): if entity in entity_forecasts: entity_cumulative = np.cumsum(entity_forecasts[entity]['yhat']) # Get entity's historical cumulative entity_col = f'Net_Cash_{entity}' if entity_col in df.columns: entity_hist_cumulative = df[entity_col].sum() entity_ending = starting_bal + entity_hist_cumulative + entity_cumulative entity_ending_balances[entity] = entity_ending # ============================================================================= # CREATE FORECAST OUTPUT FILE # ============================================================================= print("\n[8/9] Creating forecast output file...") # Build the output dataframe output_data = { 'Week_Number': list(range(TRAIN_WEEKS + 1, TRAIN_WEEKS + FORECAST_WEEKS + 1)), 'Week_Start': future_dates, 'Week_End': [d + timedelta(days=6) for d in future_dates], # Net Cash Flow - Total 'Net_Cash_Flow_Total_USD': forecast_values, 'Net_Cash_Flow_Lower_95': forecast_lower_95, 'Net_Cash_Flow_Upper_95': forecast_upper_95, 'Net_Cash_Flow_Lower_80': forecast_lower_80, 'Net_Cash_Flow_Upper_80': forecast_upper_80, # Ending Balance - Total 'Ending_Balance_Total_USD': ending_balance_total, 'Ending_Balance_Lower_95': ending_balance_lower, 'Ending_Balance_Upper_95': ending_balance_upper, } # Add entity columns for entity in entity_balances.keys(): if entity in entity_forecasts: output_data[f'Net_Cash_{entity}'] = entity_forecasts[entity]['yhat'] if entity in entity_ending_balances: output_data[f'Ending_Balance_{entity}'] = entity_ending_balances[entity] # Add category columns for cat_name, cat_data in category_forecasts.items(): output_data[f'Net_Cash_{cat_name}'] = cat_data['yhat'] # Risk flag output_data['Risk_Flag'] = (ending_balance_total < 0).astype(int) # Create DataFrame output_df = pd.DataFrame(output_data) # Format dates output_df['Week_Start'] = output_df['Week_Start'].dt.strftime('%Y-%m-%d') output_df['Week_End'] = output_df['Week_End'].dt.strftime('%Y-%m-%d') # Save to CSV output_df.to_csv('FORECAST_OUTPUT_FINAL.csv', index=False) print(f" Saved: FORECAST_OUTPUT_FINAL.csv ({len(output_df)} rows, {len(output_df.columns)} columns)") # Also save ending balance forecast separately balance_output = pd.DataFrame({ 'Week_Number': output_data['Week_Number'], 'Week_Start': output_df['Week_Start'], 'Ending_Balance_Total_USD': ending_balance_total, 'Ending_Balance_Lower_95': ending_balance_lower, 'Ending_Balance_Upper_95': ending_balance_upper, 'Risk_Flag': output_data['Risk_Flag'] }) balance_output.to_csv('ending_balance_forecast.csv', index=False) print(" Saved: ending_balance_forecast.csv") # ============================================================================= # CREATE VISUALIZATIONS # ============================================================================= print("\n[9/9] Creating visualizations...") os.makedirs('dashboard', exist_ok=True) # ============== FIGURE 1: MAIN PRODUCTION FORECAST DASHBOARD ============== fig = plt.figure(figsize=(20, 16)) fig.suptitle('PHASE 3: OPTION C - PRODUCTION 6-MONTH FORECAST\nAstraZeneca Cash Flow Forecasting - Datathon 2025', fontsize=18, fontweight='bold', color=AZ_COLORS['navy'], y=0.98) # ------ Plot 1: Historical + Forecast Timeline ------ ax1 = fig.add_subplot(2, 2, 1) # Historical data hist_weeks = df['Week_Number'].values hist_values = df['Net_Cash_Flow_USD'].values / 1e6 # Forecast data forecast_weeks = np.array(output_data['Week_Number']) forecast_vals = forecast_values / 1e6 forecast_lower = forecast_lower_95 / 1e6 forecast_upper = forecast_upper_95 / 1e6 ax1.plot(hist_weeks, hist_values, 'o-', color=AZ_COLORS['navy'], linewidth=2, markersize=6, label='Historical', zorder=5) ax1.plot(forecast_weeks, forecast_vals, 'o-', color=AZ_COLORS['lime_green'], linewidth=2, markersize=6, label='Forecast (Most Likely)', zorder=4) ax1.fill_between(forecast_weeks, forecast_lower, forecast_upper, color=AZ_COLORS['lime_green'], alpha=0.2, label='95% Confidence Interval') # Vertical line at forecast start ax1.axvline(x=TRAIN_WEEKS + 0.5, color=AZ_COLORS['mulberry'], linestyle='--', linewidth=2, label='Forecast Start') ax1.set_xlabel('Week Number', fontweight='bold') ax1.set_ylabel('Net Cash Flow (Millions USD)', fontweight='bold') ax1.set_title('Complete Timeline: Historical + 6-Month Forecast', fontweight='bold', fontsize=12) ax1.legend(loc='upper left') ax1.grid(True, alpha=0.3) ax1.axhline(y=0, color='gray', linestyle='-', linewidth=0.5) # ------ Plot 2: Ending Balance Projection ------ ax2 = fig.add_subplot(2, 2, 2) end_bal_vals = ending_balance_total / 1e6 end_bal_lower = ending_balance_lower / 1e6 end_bal_upper = ending_balance_upper / 1e6 ax2.plot(forecast_weeks, end_bal_vals, 'o-', color=AZ_COLORS['navy'], linewidth=3, markersize=8, label='Most Likely', zorder=5) ax2.fill_between(forecast_weeks, end_bal_lower, end_bal_upper, color=AZ_COLORS['light_blue'], alpha=0.4, label='95% Confidence Interval') # Add starting balance reference ax2.axhline(y=total_starting_balance/1e6, color=AZ_COLORS['gold'], linestyle='--', linewidth=2, label=f'Starting Balance: ${total_starting_balance/1e6:.1f}M') # Add zero line (insolvency threshold) ax2.axhline(y=0, color=AZ_COLORS['magenta'], linestyle='-', linewidth=2, label='Insolvency Threshold') ax2.set_xlabel('Week Number', fontweight='bold') ax2.set_ylabel('Ending Balance (Millions USD)', fontweight='bold') ax2.set_title('Ending Cash Balance Projection (6 Months)', fontweight='bold', fontsize=12) ax2.legend(loc='best') ax2.grid(True, alpha=0.3) # ------ Plot 3: Probabilistic Fan Chart ------ ax3 = fig.add_subplot(2, 2, 3) # Calculate additional percentiles forecast_std = (forecast_upper_95 - forecast_lower_95) / (2 * 1.96) p10 = forecast_values - (1.28 * forecast_std) # 10th percentile p90 = forecast_values + (1.28 * forecast_std) # 90th percentile p25 = forecast_values - (0.67 * forecast_std) # 25th percentile p75 = forecast_values + (0.67 * forecast_std) # 75th percentile # Plot fan chart ax3.fill_between(forecast_weeks, forecast_lower_95/1e6, forecast_upper_95/1e6, color=AZ_COLORS['light_blue'], alpha=0.3, label='95% CI') ax3.fill_between(forecast_weeks, p10/1e6, p90/1e6, color=AZ_COLORS['light_blue'], alpha=0.4, label='80% CI') ax3.fill_between(forecast_weeks, p25/1e6, p75/1e6, color=AZ_COLORS['light_blue'], alpha=0.5, label='50% CI') ax3.plot(forecast_weeks, forecast_vals, 'o-', color=AZ_COLORS['navy'], linewidth=3, markersize=6, label='Median Forecast', zorder=5) ax3.set_xlabel('Week Number', fontweight='bold') ax3.set_ylabel('Net Cash Flow (Millions USD)', fontweight='bold') ax3.set_title('Probabilistic Fan Chart (Uncertainty Growth)', fontweight='bold', fontsize=12) ax3.legend(loc='best') ax3.grid(True, alpha=0.3) ax3.axhline(y=0, color='gray', linestyle='-', linewidth=0.5) # ------ Plot 4: Summary Table ------ ax4 = fig.add_subplot(2, 2, 4) ax4.axis('off') # Calculate summary statistics total_forecast = forecast_values.sum() avg_weekly = forecast_values.mean() min_weekly = forecast_values.min() max_weekly = forecast_values.max() # Create scenario summary best_case_total = forecast_upper_95.sum() worst_case_total = forecast_lower_95.sum() summary_text = f""" PRODUCTION FORECAST SUMMARY =========================== FORECAST CONFIGURATION ---------------------- Historical Period: Weeks 1-{TRAIN_WEEKS} (44 weeks) Forecast Period: Weeks {TRAIN_WEEKS+1}-{TRAIN_WEEKS+FORECAST_WEEKS} (26 weeks) Model: Prophet with monthly seasonality NET CASH FLOW FORECAST (26 Weeks) ---------------------------------- Total (Most Likely): ${total_forecast/1e6:>10.2f}M Total (Best Case): ${best_case_total/1e6:>10.2f}M Total (Worst Case): ${worst_case_total/1e6:>10.2f}M Average Weekly: ${avg_weekly/1e6:>10.2f}M Min Weekly: ${min_weekly/1e6:>10.2f}M Max Weekly: ${max_weekly/1e6:>10.2f}M ENDING BALANCE (Week {TRAIN_WEEKS+FORECAST_WEEKS}) ---------------------------------- Starting Balance: ${total_starting_balance/1e6:>10.2f}M Final (Most Likely): ${ending_balance_total[-1]/1e6:>10.2f}M Final (Best Case): ${ending_balance_upper[-1]/1e6:>10.2f}M Final (Worst Case): ${ending_balance_lower[-1]/1e6:>10.2f}M RISK ASSESSMENT --------------- Weeks with Risk: {len(risk_weeks)} (balance < $0) Liquidity Status: {'AT RISK' if len(risk_weeks) > 0 else 'HEALTHY'} FILES GENERATED --------------- • FORECAST_OUTPUT_FINAL.csv • ending_balance_forecast.csv """ ax4.text(0.05, 0.95, summary_text, transform=ax4.transAxes, fontsize=9, verticalalignment='top', fontfamily='monospace', bbox=dict(boxstyle='round', facecolor=AZ_COLORS['lime_green'], alpha=0.2)) plt.tight_layout() plt.savefig('dashboard/04_option_c_production_forecast.png', dpi=300, bbox_inches='tight', facecolor='white') plt.close() print(" Saved: dashboard/04_option_c_production_forecast.png") # ============== FIGURE 2: ENTITY-LEVEL FORECASTS ============== fig2, axes = plt.subplots(2, 4, figsize=(20, 10)) fig2.suptitle('Entity-Level 6-Month Forecasts\nAstraZeneca Cash Flow Forecasting', fontsize=14, fontweight='bold', color=AZ_COLORS['navy']) entities = list(entity_forecasts.keys()) for idx, entity in enumerate(entities[:8]): ax = axes[idx // 4, idx % 4] # Historical hist_col = f'Net_Cash_{entity}' if hist_col in df.columns: ax.plot(hist_weeks, df[hist_col].values/1e6, 'o-', color=AZ_COLORS['navy'], linewidth=1.5, markersize=3, alpha=0.7, label='Historical') # Forecast ax.plot(forecast_weeks, entity_forecasts[entity]['yhat']/1e6, 'o-', color=AZ_COLORS['lime_green'], linewidth=2, markersize=4, label='Forecast') ax.fill_between(forecast_weeks, entity_forecasts[entity]['yhat_lower']/1e6, entity_forecasts[entity]['yhat_upper']/1e6, color=AZ_COLORS['lime_green'], alpha=0.2) ax.axvline(x=TRAIN_WEEKS + 0.5, color=AZ_COLORS['mulberry'], linestyle='--', linewidth=1) ax.axhline(y=0, color='gray', linestyle='-', linewidth=0.5, alpha=0.5) total_forecast_entity = entity_forecasts[entity]['yhat'].sum() ax.set_title(f'{entity}\nTotal: ${total_forecast_entity/1e6:.1f}M', fontweight='bold', fontsize=10) ax.set_xlabel('Week', fontsize=8) ax.set_ylabel('M USD', fontsize=8) ax.tick_params(axis='both', labelsize=7) ax.grid(True, alpha=0.3) plt.tight_layout() plt.savefig('dashboard/04_entity_forecasts.png', dpi=300, bbox_inches='tight', facecolor='white') plt.close() print(" Saved: dashboard/04_entity_forecasts.png") # ============== FIGURE 3: CATEGORY-LEVEL FORECASTS ============== n_cats = len(category_forecasts) n_rows = (n_cats + 2) // 3 fig3, axes = plt.subplots(n_rows, 3, figsize=(18, 4*n_rows)) fig3.suptitle('Category-Level 6-Month Forecasts\nAstraZeneca Cash Flow Forecasting', fontsize=14, fontweight='bold', color=AZ_COLORS['navy']) axes = axes.flatten() if n_rows > 1 else [axes] if n_cats == 1 else axes for idx, (cat_name, cat_data) in enumerate(category_forecasts.items()): if idx >= len(axes): break ax = axes[idx] # Historical hist_col = f'Net_Cash_{cat_name}' if hist_col in df.columns: ax.plot(hist_weeks, df[hist_col].values/1e6, 'o-', color=AZ_COLORS['navy'], linewidth=1.5, markersize=3, alpha=0.7, label='Historical') # Forecast ax.plot(forecast_weeks, cat_data['yhat']/1e6, 'o-', color=AZ_COLORS['lime_green'], linewidth=2, markersize=4, label='Forecast') ax.fill_between(forecast_weeks, cat_data['yhat_lower']/1e6, cat_data['yhat_upper']/1e6, color=AZ_COLORS['lime_green'], alpha=0.2) ax.axvline(x=TRAIN_WEEKS + 0.5, color=AZ_COLORS['mulberry'], linestyle='--', linewidth=1) ax.axhline(y=0, color='gray', linestyle='-', linewidth=0.5, alpha=0.5) total_forecast_cat = cat_data['yhat'].sum() ax.set_title(f'{cat_name}\nTotal: ${total_forecast_cat/1e6:.1f}M', fontweight='bold', fontsize=10) ax.set_xlabel('Week', fontsize=8) ax.set_ylabel('M USD', fontsize=8) ax.tick_params(axis='both', labelsize=7) ax.grid(True, alpha=0.3) # Hide empty subplots for idx in range(len(category_forecasts), len(axes)): axes[idx].axis('off') plt.tight_layout() plt.savefig('dashboard/04_category_forecasts.png', dpi=300, bbox_inches='tight', facecolor='white') plt.close() print(" Saved: dashboard/04_category_forecasts.png") # ============== FIGURE 4: LIQUIDITY RISK HEATMAP ============== fig4, ax = plt.subplots(figsize=(16, 6)) # Create weekly risk levels risk_levels = [] for bal in ending_balance_total: if bal < 0: risk_levels.append(4) # Crisis elif bal < 1e6: risk_levels.append(3) # Concerning elif bal < 5e6: risk_levels.append(2) # Moderate else: risk_levels.append(1) # Healthy # Create heatmap data risk_data = np.array(risk_levels).reshape(1, -1) # Custom colormap from matplotlib.colors import LinearSegmentedColormap colors = [AZ_COLORS['lime_green'], AZ_COLORS['gold'], '#FFA500', AZ_COLORS['magenta']] cmap = LinearSegmentedColormap.from_list('risk', colors, N=4) im = ax.imshow(risk_data, cmap=cmap, aspect='auto', vmin=1, vmax=4) # Labels ax.set_xticks(range(FORECAST_WEEKS)) ax.set_xticklabels([f'W{w}' for w in range(TRAIN_WEEKS+1, TRAIN_WEEKS+FORECAST_WEEKS+1)], rotation=45, ha='right', fontsize=8) ax.set_yticks([]) # Add balance values as text for i in range(FORECAST_WEEKS): text_color = 'white' if risk_levels[i] > 2 else 'black' ax.text(i, 0, f'${ending_balance_total[i]/1e6:.1f}M', ha='center', va='center', fontsize=7, fontweight='bold', color=text_color) ax.set_title('Liquidity Risk Calendar (26-Week Forecast)\nGreen=Healthy (>$5M) | Yellow=Moderate ($1-5M) | Orange=Concerning ($0-1M) | Magenta=Crisis (<$0)', fontweight='bold', fontsize=12, color=AZ_COLORS['navy']) ax.set_xlabel('Forecast Week', fontweight='bold') plt.tight_layout() plt.savefig('dashboard/04_liquidity_risk_calendar.png', dpi=300, bbox_inches='tight', facecolor='white') plt.close() print(" Saved: dashboard/04_liquidity_risk_calendar.png") # ============================================================================= # FINAL SUMMARY # ============================================================================= print("\n" + "=" * 80) print("PHASE 3 COMPLETE: OPTION C - PRODUCTION 6-MONTH FORECAST") print("=" * 80) print(f""" PRODUCTION FORECAST SUMMARY =========================== FORECAST PERIOD: Start: Week {TRAIN_WEEKS+1} ({future_dates[0].date()}) End: Week {TRAIN_WEEKS+FORECAST_WEEKS} ({future_dates[-1].date()}) Duration: {FORECAST_WEEKS} weeks (6 months) NET CASH FLOW FORECAST: +--------------------+---------------+ | Scenario | Total (26w) | +--------------------+---------------+ | Best Case (95%) | ${best_case_total/1e6:>10.2f}M | | Most Likely | ${total_forecast/1e6:>10.2f}M | | Worst Case (95%) | ${worst_case_total/1e6:>10.2f}M | +--------------------+---------------+ ENDING CASH BALANCE (Week {TRAIN_WEEKS+FORECAST_WEEKS}): +--------------------+---------------+ | Scenario | Balance | +--------------------+---------------+ | Starting Balance | ${total_starting_balance/1e6:>10.2f}M | | Best Case (95%) | ${ending_balance_upper[-1]/1e6:>10.2f}M | | Most Likely | ${ending_balance_total[-1]/1e6:>10.2f}M | | Worst Case (95%) | ${ending_balance_lower[-1]/1e6:>10.2f}M | +--------------------+---------------+ ENTITY FORECASTS (Total 26 weeks):""") for entity in entity_forecasts.keys(): total_ent = entity_forecasts[entity]['yhat'].sum() print(f" {entity}: ${total_ent/1e6:>8.2f}M") print(f""" CATEGORY FORECASTS (Total 26 weeks):""") for cat_name, cat_data in category_forecasts.items(): total_cat = cat_data['yhat'].sum() print(f" {cat_name}: ${total_cat/1e6:>8.2f}M") print(f""" RISK ASSESSMENT: Weeks with negative balance: {len(risk_weeks)} Liquidity status: {'AT RISK' if len(risk_weeks) > 0 else 'HEALTHY'} FILES CREATED: - FORECAST_OUTPUT_FINAL.csv (main deliverable) - ending_balance_forecast.csv - dashboard/04_option_c_production_forecast.png - dashboard/04_entity_forecasts.png - dashboard/04_category_forecasts.png - dashboard/04_liquidity_risk_calendar.png ==> PHASE 3 COMPLETE! Ready for presentation. ==> NEXT STEP: Phase 4 - Category Deep-Dive (AR/AP Analysis) """) # Save production forecast report report = f""" # PRODUCTION FORECAST REPORT # Option C - 6-Month Forecast # Generated: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')} ## 1. Executive Summary Prophet model trained on 44 weeks of historical data to forecast 26 weeks ahead. Forecast period: Week {TRAIN_WEEKS+1} ({future_dates[0].date()}) to Week {TRAIN_WEEKS+FORECAST_WEEKS} ({future_dates[-1].date()}) ## 2. Net Cash Flow Forecast | Scenario | Total (26 weeks) | |----------|------------------| | Best Case (95%) | ${best_case_total:,.0f} | | Most Likely | ${total_forecast:,.0f} | | Worst Case (95%) | ${worst_case_total:,.0f} | ## 3. Ending Cash Balance | Scenario | Week {TRAIN_WEEKS+FORECAST_WEEKS} Balance | |----------|------------------| | Starting Balance | ${total_starting_balance:,.0f} | | Best Case (95%) | ${ending_balance_upper[-1]:,.0f} | | Most Likely | ${ending_balance_total[-1]:,.0f} | | Worst Case (95%) | ${ending_balance_lower[-1]:,.0f} | ## 4. Entity Forecasts | Entity | 26-Week Total | |--------|---------------| """ for entity in entity_forecasts.keys(): total_ent = entity_forecasts[entity]['yhat'].sum() report += f"| {entity} | ${total_ent:,.0f} |\n" report += f""" ## 5. Risk Assessment - Weeks with negative balance: {len(risk_weeks)} - Liquidity status: {'AT RISK' if len(risk_weeks) > 0 else 'HEALTHY'} ## 6. Model Configuration - Algorithm: Prophet - Seasonality: Monthly (30.5-day period, Fourier order 5) - Confidence intervals: 95% - Changepoint prior scale: 0.05 """ with open('FORECAST_OUTPUT_REPORT.md', 'w') as f: f.write(report) print(" Saved: FORECAST_OUTPUT_REPORT.md")