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.



<h2 class="wp-block-heading">The Model</h2>



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



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/cashflow-1.png" alt="Cash flow chart" class="wp-image-76908"/></figure>



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!



<h2 class="wp-block-heading">Calculating in SQL</h2>



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 &#8211; Fixed Costs &#8211; Variable Costs



The SQL looks like this:



<pre class="wp-block-code"><code>select
 revenue - fixed_costs - variable_costs as change
from 
 transactions </code></pre>



<h2 class="wp-block-heading">Calculating Cumulative Change</h2>



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.



<pre class="wp-block-code"><code>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</code></pre>



The change CTE calculates the change between opening balance and closing balance for each month. By joining the result to itself with c1.month &gt;= 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.



<h2 class="wp-block-heading">Calculating Opening and Closing Balances</h2>



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.



<pre class="wp-block-code"><code>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’</code></pre>



<h2 class="wp-block-heading">Putting it all Together</h2>



Putting it all together, our final query looks like this



<pre class="wp-block-code"><code>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’</code></pre>



<h3 class="wp-block-heading">&nbsp;The Result</h3>



Using the final query, our results look like this:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/cashflow-2.png" alt="Cash flow 2" class="wp-image-76903"/></figure>



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

Cash Flow modeling in SQL

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article