Periscope Data
The world’s fastest visualization platform for data analysts.

Cash Flow Modelling in SQL

Cash Flow Modelling in SQL
September 23, 2015

People love financial modelling 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 in a spreadsheet software might look like this:

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 Openening 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 modelling to SQL when it becomes too big for your spreadsheet.

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.