Spot financial sustainability risks early
The problem
Financial crises happen because you don't spot the warning signs early enough. Your reserves look fine today but in 6 months you'll hit a cash flow crisis. That big grant ends in 8 months and you haven't planned for it. Income trends are declining but it's gradual enough that you don't notice. By the time the board sees the problem, it's urgent not preventable.
The solution
Build a forecasting model that learns from your historical income and expenditure patterns. It projects forward: reserves trajectory, cash flow stress points, where grant dependency creates risk, seasonal income variations. You get early warnings: 'Reserves will drop below 3 months in Q3 based on current burn rate', 'Income trend declining 5% year-on-year', 'Major grant cliff-edge in 8 months with no replacement pipeline'. Time to act while you have options.
What you get
Financial sustainability dashboard showing: projected reserves for next 12 months, cash flow forecast highlighting stress months, grant dependency risks (% of income from single sources ending soon), income trend (growing/stable/declining), and early warning alerts. 'Alert: Reserves projected to fall below board policy minimum (£50k) in September unless income improves or costs reduce. Current burn rate: £8k/month.'
Before you start
- At least 2-3 years of monthly income and expenditure data
- Grant income tracked separately with end dates
- Reserves/cash balance history
- Understanding of your financial cycle (seasonal patterns, payment timing)
- A Google account for Colab
- Basic Python skills or willingness to adapt example code
- Check your organisation permits uploading financial data to cloud platforms like Colab (consider anonymising donor names if included)
When to use this
- You want to spot financial problems before they become crises
- Your reserves are tight and you need to monitor sustainability closely
- You have significant grant dependency and need to track cliff-edges
- Your income is variable and you struggle to forecast cash flow
When not to use this
- You have less than 2 years of financial data - not enough to identify patterns
- Your income is genuinely unpredictable (early-stage fundraising with no track record)
- You don't act on financial warnings anyway - data without action wastes time
- Your financial situation is so stable that forecasting adds no value
Steps
- 1
Gather historical financial data
Export monthly data for past 2-3 years: total income, total expenditure, reserves balance, grant income separately, donations, earned income. The more history, the better the model can learn your patterns (seasonal variation, growth trends, income volatility).
- 2
Identify your financial patterns
Look at the data: is income seasonal (high in Dec, low in summer)? Are there regular payment cycles (quarterly grants)? Is expenditure lumpy (annual costs) or smooth? Understanding your patterns helps you evaluate if the model's forecasts make sense.
- 3
Build income forecast
Use Prophet (the example code) to forecast income for next 12 months. It learns seasonal patterns, growth trends, and regular cycles from your history. The forecast shows expected income trajectory with confidence intervals (best case, worst case scenarios).
- 4
Build expenditure forecast
Same approach for costs: forecast expenditure based on historical patterns. If you're growing (hiring staff, expanding programmes), adjust the forecast upwards. If you're cutting costs, adjust down. The model gives you baseline, you add known changes.
- 5
Project reserves trajectory
Combine income and expenditure forecasts to project reserves: current reserves + forecast income - forecast expenditure = projected reserves in 3 months, 6 months, 12 months. Flag when reserves drop below board policy minimum or operational safety threshold.
- 6
Identify grant dependency risks
List grants ending in next 12 months and their amounts. Calculate: what % of income does each represent? If top 3 grants are 60% of income and all end within 6 months with no replacements in pipeline, you've got a cliff-edge. The model can't predict new grants, but it shows the gap if they don't materialise.
- 7
Spot income trends
Is income growing, stable, or declining year-on-year? The model extracts trend from seasonal noise. A gradual 5% annual decline is invisible month-to-month but compounds to a crisis. Spotting the trend early gives you time to reverse it.
- 8
Generate early warnings
Create alerts: 'Reserves projected to fall below £X by [month]', 'Income trending down X% annually', 'Grant worth £X ends in Y months with no replacement'. Present these to leadership monthly. Early warnings while you have time to act - fundraise, restructure, or cut costs strategically.
- 9
Test scenarios(optional)
Use the model to test what-ifs: 'If we hire 2 staff, how does reserves trajectory change?', 'If that grant bid succeeds, does it solve the cash flow gap?', 'If we cut programme costs 10%, when are we sustainable?' Scenario planning with data, not guesswork.
- 10
Update monthly(optional)
Each month, add actual income/expenditure and regenerate forecasts. Are you tracking forecast or diverging? If actual income is below forecast, investigate why. If reserves are draining faster than projected, act now. The model gets more accurate as it learns from more data.
Example code
Forecast financial sustainability
This forecasts income, expenditure, and reserves to identify sustainability risks. Adapt to your financial data structure.
# Install Prophet if not already available
# !pip install prophet
import pandas as pd
import numpy as np
from prophet import Prophet
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
# Load historical financial data
# Columns: date, total_income, total_expenditure, reserves_balance
financials = pd.read_csv('financial_history.csv')
financials['date'] = pd.to_datetime(financials['date'])
print(f"Loaded {len(financials)} months of financial data")
print(f"Date range: {financials['date'].min()} to {financials['date'].max()}")
if len(financials) > 0:
print(f"Current reserves: £{financials['reserves_balance'].iloc[-1]:,.0f}")
else:
print("Warning: No financial data loaded")
# Prophet requires columns named 'ds' (date) and 'y' (value)
# 1. Forecast income
income_data = financials[['date', 'total_income']].rename(
columns={'date': 'ds', 'total_income': 'y'}
)
income_model = Prophet(
yearly_seasonality=True,
weekly_seasonality=False,
changepoint_prior_scale=0.05 # How flexible the trend is
)
income_model.fit(income_data)
# Forecast 12 months ahead
future_income = income_model.make_future_dataframe(periods=12, freq='M')
income_forecast = income_model.predict(future_income)
# 2. Forecast expenditure
expenditure_data = financials[['date', 'total_expenditure']].rename(
columns={'date': 'ds', 'total_expenditure': 'y'}
)
expenditure_model = Prophet(
yearly_seasonality=True,
weekly_seasonality=False,
changepoint_prior_scale=0.05
)
expenditure_model.fit(expenditure_data)
future_expenditure = expenditure_model.make_future_dataframe(periods=12, freq='M')
expenditure_forecast = expenditure_model.predict(future_expenditure)
# 3. Project reserves
current_reserves = financials['reserves_balance'].iloc[-1]
# Get forecasts for future months only
future_months = income_forecast[income_forecast['ds'] > financials['date'].max()]
projected_reserves = []
reserves = current_reserves
for idx, row in future_months.iterrows():
month_date = row['ds']
# Get matching expenditure forecast
exp_matches = expenditure_forecast[expenditure_forecast['ds'] == month_date]
if len(exp_matches) == 0:
continue # Skip if no matching expenditure forecast
exp_row = exp_matches.iloc[0]
income = row['yhat'] # Predicted income
expenditure = exp_row['yhat'] # Predicted expenditure
# Update reserves
reserves = reserves + income - expenditure
projected_reserves.append({
'date': month_date,
'projected_reserves': reserves,
'forecast_income': income,
'forecast_expenditure': expenditure,
'net_position': income - expenditure
})
reserves_df = pd.DataFrame(projected_reserves)
# 4. Identify risks
print("\n" + "="*60)
print("FINANCIAL SUSTAINABILITY ANALYSIS")
print("="*60)
# Reserves policy check
reserves_minimum = 50000 # Adjust to your policy
months_below_minimum = reserves_df[reserves_df['projected_reserves'] < reserves_minimum]
if len(months_below_minimum) > 0:
first_month_at_risk = months_below_minimum.iloc[0]
print(f"\n⚠️ ALERT: Reserves Risk")
print(f" Projected to fall below minimum (£{reserves_minimum:,}) in {first_month_at_risk['date'].strftime('%B %Y')}")
print(f" Projected reserves at that point: £{first_month_at_risk['projected_reserves']:,.0f}")
else:
print(f"\n✅ Reserves healthy: Staying above minimum (£{reserves_minimum:,}) for next 12 months")
# Income trend
recent_income = financials['total_income'].tail(12).mean()
historical_income = financials['total_income'].head(12).mean()
income_trend = ((recent_income - historical_income) / historical_income) * 100
print(f"\n📊 Income Trend: {income_trend:+.1f}% year-on-year")
if income_trend < -3:
print(f" ⚠️ Income declining - investigate causes")
elif income_trend > 5:
print(f" ✅ Income growing")
else:
print(f" → Income stable")
# Average monthly burn rate
avg_burn = financials['total_expenditure'].tail(6).mean()
months_of_reserves = current_reserves / avg_burn
print(f"\n💰 Current Financial Position:")
print(f" Reserves: £{current_reserves:,.0f}")
print(f" Average monthly burn: £{avg_burn:,.0f}")
print(f" Months of reserves: {months_of_reserves:.1f}")
if months_of_reserves < 3:
print(f" ⚠️ Less than 3 months reserves - high risk")
elif months_of_reserves < 6:
print(f" ⚠️ Less than 6 months reserves - moderate risk")
else:
print(f" ✅ Healthy reserves cushion")
# Cash flow pressure points
deficit_months = reserves_df[reserves_df['net_position'] < 0]
if len(deficit_months) > 0:
print(f"\n📉 Cash Flow Pressure Points:")
print(f" {len(deficit_months)} months projected to run deficit")
print(f"\n Largest deficits:")
worst_months = deficit_months.nlargest(3, 'net_position', keep='first')
for _, month in worst_months.iterrows():
print(f" {month['date'].strftime('%B %Y')}: £{month['net_position']:,.0f}")
# Next 6 months summary
print(f"\n📅 Next 6 Months Projection:")
print(f"{'Month':<15} {'Income':<12} {'Expenditure':<12} {'Net':<12} {'Reserves':<12}")
print("-" * 60)
for _, month in reserves_df.head(6).iterrows():
print(f"{month['date'].strftime('%b %Y'):<15} "
f"£{month['forecast_income']:>10,.0f} "
f"£{month['forecast_expenditure']:>10,.0f} "
f"£{month['net_position']:>10,.0f} "
f"£{month['projected_reserves']:>10,.0f}")
# Save results
reserves_df.to_csv('financial_forecast.csv', index=False)
print(f"\nFull forecast saved to financial_forecast.csv")
# Plot
fig, axes = plt.subplots(2, 1, figsize=(12, 10))
# Plot 1: Income vs Expenditure
ax1 = axes[0]
ax1.plot(income_forecast['ds'], income_forecast['yhat'], label='Forecast Income', color='green')
ax1.fill_between(income_forecast['ds'],
income_forecast['yhat_lower'],
income_forecast['yhat_upper'],
alpha=0.2, color='green')
ax1.plot(expenditure_forecast['ds'], expenditure_forecast['yhat'], label='Forecast Expenditure', color='red')
ax1.axvline(financials['date'].max(), color='gray', linestyle='--', label='Current')
ax1.set_title('Income vs Expenditure Forecast')
ax1.set_xlabel('Date')
ax1.set_ylabel('Amount (£)')
ax1.legend()
ax1.grid(True, alpha=0.3)
# Plot 2: Reserves Projection
ax2 = axes[1]
ax2.plot(financials['date'], financials['reserves_balance'], label='Historical Reserves', color='blue')
ax2.plot(reserves_df['date'], reserves_df['projected_reserves'],
label='Projected Reserves', color='blue', linestyle='--')
ax2.axhline(reserves_minimum, color='red', linestyle=':', label=f'Minimum (£{reserves_minimum:,})')
ax2.set_title('Reserves Trajectory')
ax2.set_xlabel('Date')
ax2.set_ylabel('Reserves (£)')
ax2.legend()
ax2.grid(True, alpha=0.3)
plt.tight_layout()
plt.savefig('financial_sustainability_forecast.png', dpi=150)
print("\nCharts saved to financial_sustainability_forecast.png")
print("\n" + "="*60)
print("NEXT STEPS:")
print("="*60)
print("1. Share forecast with treasurer and leadership team")
print("2. Investigate any concerning trends or pressure points")
print("3. Develop action plans for sustainability risks")
print("4. Update monthly with actual financials")
print("5. Test scenarios (new grants, cost changes) using the model")Tools
Resources
At a glance
- Time to implement
- days
- Setup cost
- free
- Ongoing cost
- free
- Cost trend
- stable
- Organisation size
- small, medium, large
- Target audience
- ceo-trustees, operations-manager, data-analyst
All tools are free. All processing runs locally. Initial setup takes a day (gathering data, building model). Update monthly with new data for fresh forecasts. Prevents financial crises that cost far more than the time invested. Early warning of cash flow issues gives you time to fundraise, cut costs, or restructure.