Forecast cash flow for the next 6 months

The problem

You need to predict when cash will be tight so you can plan grant applications timing, delay non-essential expenditure, or have difficult conversations with the board early. Your current approach is just looking at the bank balance and hoping, which doesn't give you enough warning.

The solution

Build a simple time-series forecast using historical income and expenditure patterns, known committed costs, and expected grant payments. Use either a spreadsheet with forecast functions (beginner) or Python with the Prophet library (intermediate) to project forward 6 months with confidence intervals.

What you get

A month-by-month cash flow forecast for the next 6 months showing expected income, expenditure, and closing balance. Includes best/worst case scenarios and highlights when reserves might drop below your minimum threshold. Updated monthly with actuals vs forecast tracking.

Before you start

• At least 12 months of historical bank transaction data (24+ months is better)
• List of known future commitments (staff costs, rent, confirmed grant payments)
• Confirmed or expected grant payment schedules
• Your target minimum reserves level (e.g., 3 months operating costs)

When to use this

• Planning when to submit grant applications (to ensure funds arrive when needed)
• Board reporting on financial sustainability and runway
• Scenario planning for growth, contraction, or loss of a major funder
• Deciding whether you can afford to hire before grant confirmation
• Early warning system to trigger reserves policy discussions

When not to use this

• Organisation less than 12 months old (insufficient history for meaningful forecast)
• Highly unpredictable income (e.g., 90% from spontaneous public fundraising events)
• Income is entirely project-based with no pipeline visibility
• You need hour-by-hour or daily cash flow (this is for monthly forecasting)

Steps

1. 1

   Export and categorise historical transactions

   Export 24+ months of bank transactions from your accounting system. Before uploading to Google Colab, remove any sensitive personal data (names, descriptions with identifiable details). Categorise into meaningful groups: unrestricted donations, restricted grants, earned income, staff costs, premises costs, programme costs, overheads. If not already categorised, consider using an LLM prompt or simple lookup table to speed up the categorisation - this is often the biggest hurdle for time-poor staff. The categorisation is the foundation of your forecast.

2. 2

   Identify recurring patterns

   Look for monthly, quarterly, and annual cycles in your data. Do certain grants always pay in March? Is November always a strong fundraising month? Are summer months always quiet? Plot your income and expenditure on a timeline to spot visual patterns. Note seasonal effects.

3. 3

   List known future commitments

   Create a forward-looking list of everything you know is coming: staff salaries (monthly), rent (monthly/quarterly), confirmed grant payments with dates, planned campaigns, annual software renewals. Include both income and costs. This is your baseline forecast.

4. 4

   Build forecast model (Spreadsheet approach)

   Create a spreadsheet with columns for each month (next 6 months). Rows for income categories and expenditure categories. Use FORECAST.LINEAR or TREND functions for variable income. Add your known commitments. Calculate monthly net position and cumulative cash. Add conditional formatting to highlight when reserves drop below threshold.

5. 5

   Build forecast model (Python approach)(optional)

   Use Prophet library to model income and expenditure separately. Prophet handles seasonality well. Fit model on historical data, generate 6-month forecast with confidence intervals. Combine income and expenditure forecasts to get net cash flow. Export to CSV for easier sharing with non-technical colleagues.

6. 6

   Run scenarios

   Create 'what if' scenarios: What if grant X is delayed by 2 months? What if grant Y is rejected? What if we hire that new role? What if fundraising is 20% below forecast? Model these scenarios to understand your risk exposure and when you'd need to take action.

7. 7

   Set up monthly review process

   Each month: update actuals, compare actual vs forecast, investigate variances, update forward assumptions. Refine your forecast based on what you're learning. Present to senior team or board. This review discipline is what makes forecasting valuable, not just the initial model.

Example code

import pandas as pd
from prophet import Prophet
import matplotlib.pyplot as plt

# Load historical transaction data
df = pd.read_csv('transactions.csv', parse_dates=['date'])

# Prepare income data for Prophet (requires 'ds' and 'y' columns)
income_df = df[df['amount'] > 0].groupby('date')['amount'].sum().reset_index()
income_df.columns = ['ds', 'y']

# Fit Prophet model
income_model = Prophet(
    yearly_seasonality=True,
    weekly_seasonality=False,
    daily_seasonality=False
)
income_model.fit(income_df)

# Create future dataframe (6 months = 180 days)
future_income = income_model.make_future_dataframe(periods=180, freq='D')
forecast_income = income_model.predict(future_income)

# Aggregate to monthly
forecast_income['month'] = pd.to_datetime(forecast_income['ds']).dt.to_period('M')
monthly_income = forecast_income.groupby('month').agg({
    'yhat': 'sum',
    'yhat_lower': 'sum',
    'yhat_upper': 'sum'
}).reset_index()

print("Monthly Income Forecast:")
print(monthly_income.tail(6))  # Show next 6 months

# Repeat for expenditure...
# Then combine income and expenditure forecasts to get net cash flow

Tools

Resources