Cash Flow modeling in SQL

People love financial modeling in Excel. It’s easy, fast and flexible, and Excel’s functions allow you to quickly tweak your financial model. However, when your data volume starts to get very large, a spreadsheet can quickly break down.

The Model

We have a basic model that might look like this in a spreadsheet software:

Given a set of past or projected cash flows and an opening balance, we want to calculate the closing and opening balance for each period.

The Closing Balance (CB) will be equal to the Opening Balance (OB) plus Revenue, less the Fixed Costs and the Variable Costs. The Opening Balance (OB) will be equal to the previous period’s Closing Balance (CB).

Now let’s bring the calculation to SQL!

Calculating in SQL

To avoid a recursive solution, which is slow and complicated, we’ll start by calculating the cumulative revenue less the costs. Calculating Change in Opening and Closing Balances

Let’s start by calculating change in balance:

Change = Revenue – Fixed Costs – Variable Costs

The SQL looks like this:

select
  revenue - fixed_costs - variable_costs as change
from 
  transactions

Calculating Cumulative Change

Next, we want to calculate the cumulative of this number. In MySQL you can do this easily with stored variables, and in PostgreSQL you can use window functions. We’ll use a solution that works in both DBs: joining the table with itself.

with change as (
  select 
    month
    , revenue - fixed_costs - variable_costs as change
  from 
    transactions
)
select 
  c1.month as month
  , sum(c2.change) as cumulative_change
from 
  change c1 
  join change c2
    on c1.month >= c2.month

The change CTE calculates the change between opening balance and closing balance for each month. By joining the result to itself with c1.month >= c2.month, we take a copy of all the results of the change query where the month is less than or equal to a given month, and join it to the original month in c1.

This means that for c1.month = February 2016, we join c2.month = February 2016 and January 2016. For c1.month = March 2016, we will join with the rows where c2.month = March 2016, February 2016 and January 2016.

Calculating Opening and Closing Balances

Now that we’ve got the cumulative change for each period, we’ll add that change to the opening balance to get the closing balance.

The closing balance for each period is calculated as the initial opening balance plus the change each period. The opening balance for each period is calculated as the initial opening balance plus the cumulative change in the last period.

select 
  cc_closing.month as month
  , t1.initial_ob + cc_closing.cumulative_change as closing_balance
  , t1.initial_ob + cc_opening.cumulative_change as opening balance
from
  (select max(cash_on_hand_ob) as initial_ob from transactions) t1
  join cumulative_change cc_closing 
    on true
  join cumulative_change cc_opening 
    on cc_closing.month = cc_opening.month + interval ‘1 month’

Putting it all Together

Putting it all together, our final query looks like this

with change as (
  select 
    month
    , revenue - fixed_costs - variable_costs as change
  from transactions
),
cumulative_change as (
select 
  c1.month as month
  , sum(c2.change) as cumulative_change
from 
  change c1 
  join change c2
  on c1.month >= c2.month
)
select 
  cc_closing.month as month
  , t1.initial_ob + cc_closing.cumulative_change as closing_balance
  , t1.initial_ob + cc_opening.cumulative_change as opening balance
from
  (select max(cash_on_hand_ob) as initial_ob from transactions) t1
  join cumulative_change cc_closing 
    on true
  join cumulative_change cc_opening 
    on cc_closing.month = cc_opening.month + interval ‘1 month’

The Result

Using the final query, our results look like this:

And voilà! Now you can bring your cash flow and financial modeling to SQL when it becomes too big for your spreadsheet.

Tags: data team