A common modelling task facing SaaS companies is to build out Deferred Revenue and Accounts Receivable based on Contract Length and Month of Payment. Let’s look at how to approach this in Causal.
First step is calculating the Booked ARR in the period. This should include New, Expansion and Renewal. If there are historical periods not in the model then Causal needs to know when the contracts started in those periods were booked and up for renewal. One method is to roll the model backwards and include the actuals for the variables above, the other option is to bring in Booked ARR up for Renewal in the correct forecast periods and apply the renewal/expansion logic to these contracts in the appropriate period.
Now it’s time to build out the Deferred Revenue and Accounts Receivable variables. My preferred method is to separate out the P/L and Cash lines, and have an Ending Balance for each variable like this:
The formulas for the variables will follow this logic:
Practical example for a 12 month contract for $1,200 and a 2 month delay in italics.
- Revenue for the periods up till payment month should build the AR balance as the customer is receiving a service without paying for it, creating a receivable for the company.
$200 of revenue will be booked which builds an AR balance of $200 over the first two months of the contract.
- Upon cash being received, the AR balance will be netted off, one month of cash and revenue is recognised in month of payment and the remaining cash flows into the Deferred Revenue balance.
$1200 cash is received in month 3, $200 cash is credited against the AR balance, $100 is recognised as cash received for $100 revenue in month 3, $900 remaining cash builds up the Deferred Revenue balance.
- For each remaining month of the contract, revenue per month is debited off the Deferred Revenue Balance until the contract end where the Deferred Revenue balance nets to nil and the revenue in the contract is all recognised as earned alongside the cash.
Each remaining month sees $100 revenue recognised, reducing the Deferred Revenue balance by $100, with the end of the contract materialising in $1,200 revenue recognised and a $1,200 cash balance.
So how do we build this in Causal:
The formula looks at the contract that will be paid in the period and apportions the amount towards Deferred Revenue i.e. Booked ARR from current period minus months of payment delay as this is when Cash will be received, multiplied by the proportion of the Cash that flows into Deferred Revenue i.e. (total contract length - payment delay) / 12.
The formula looks back at the period of the contract length behind the current period + 1, up to the (current period - payment delay in months). Therefore, the inclusive period totals to the months per contract where Revenue is debited off the Deferred Revenue balance…
Sums the Cash movement and P/L movement with the previous Deferred Revenue balance.
This formula adds up the Revenue for the periods in the contract where cash is not received yet i.e. from the (current period - payment delay + 1) up to the current period and divides by 12 calculate Revenue from Booked ARR.
This formula looks at the period relative to the current period that Cash will be collected for, i.e. current period minus payment delay in months and multiplies by the number of months payment is delayed for.
Sums the Cash movement and P/L movement with the previous Accounts Receivable balance.
Assume now there are 3 different types of contract, Annual, Bi-Annual and Quarterly, with respectively 1, 2 and 4 payments throughout a 12 month contract. This requires some adjustments to our formulae above.
It is worth making a new variable called Total Invoicing to calculate when cash is received based on each contract type to simplify the subsequent formulae below:
The remaining formulae follow the same logic as above, only segmented for the new payment term categories.
There we have it! A model that will calculate our Accounts Receivable and Deferred Balance for numerous different payment terms and a payment delay on the contract!
The benefit of laying out the model like this is that the work required to build out the P/L and Cash Flow is already done! The combined P/L movements will add to the Revenue in the period and the combined Cash movements are the Cash Flows if you wanted to build a Direct Cash Flow.
However, there are multiple ways to approach this task so if anyone has any suggestions please post below!