Modeling Deferred Revenue for Startups
The Average Contract Value (ACV) for your startup is getting bigger and closer to your next fundraising round's target. As a result, you start to wonder how to accurately present each contract paid upfront by your clients on your three statements (Income Statement, Balance Sheet, and Statement of Cash Flows). So you are well-prepared when it comes time to present these financials during fundraising.
In Pry, you do not have to dread the complicated spreadsheet structure and formulas. As you follow this tutorial, you will be amazed by how easy modeling deferred revenue can be.
We will use a blank workspace for this tutorial to see how the deferred revenue model is flown to three statements without mixing with other forecasts.
After you understand how the deferred revenue model works in Pry, you can reference our setup guide to learn about importing data to Pry and our financial projections guide to creating your entire financial plan in Pry.
In this tutorial, we will walk through a deferred revenue model for one service contract for a recurring service that is paid upfront.
When you sign a service contract for a recurring service, here are the common terms stipulated on the contract:
- Total Contract Value - The total payment for your company's services from your client
- Contract Start Date - When your company starts rendering services to your client
- Duration - How long your company needs to provide services to the company given the Total Contract Value.
- Payment Schedule - When and how frequent your client pays for the services
We will make the following assumptions for this tutorial:
- Total Contract Value: $5,000
- Contract Start Date: July, 2022
- Duration: 12 months
- Payment Schedule: June, 2022 | All 12 months upfront | Paid at contract signing
Additionally, we will also assume the services rendered are equal in value for each of the 12 months.
To create these assumptions in Pry, go to "Models", create a new model, and name it "Deferred Revenue Model - Service Contract". (More on model creation and management in Pry here)
Once we have the new model, find the Inputs column and create two inputs correspondingly to our assumptions above. To create an input, click on the "+" sign on the top right corner of the Inputs column and pick the appropriate input type. Afterwards, name the inputs to match the assumptions.
With the assumptions created in Pry, we will now calculate the outputs for each financial statement. Let us go over the expected output for each financial statement first, in chronological order:
- In June 2022 (Payment Date), there should be no revenue recorded for this contract on your Income Statement as you have not yet started rendering services to your clients. And thus, this payment you receive from the client is Unearned Revenue or Deferred Revenue.
- So, to record this inflow of cash, in June 2022, Deferred Revenue under Liabilities and Cash under Assets on the Balance Sheet will increase by the Total Contract Value, $5,000, paid by the client (i.e., debit Cash: $5,000, credit Deferred Revenue:$5,000).
- This also means In June 2022, there will be a $5,000 cash inflow on your Statement of Cash Flows.
Now, let us create these entries in Pry. Simplicity is part of the beauty of Pry - to make all three entries listed above, you only need to create one formula for and one output to Deferred Revenue. All the rest will be automatically updated. We will find out how shortly.
The one logic you need to define using Pry's formula feature in this step is: On the Payment Date, the increase in account balance for Deferred Revenue is the Total Payment Amount, $5,000, i.e., the formula returns $5,000. There will be no change in all other months, i.e., the formula returns zero.
To create this formula for Deferred Revenue, click on the blue "+" sign on the top right corner of the Formulas column and change the formula's name to "Deferred Revenue on Payment Date" for easy reference. Type in the formula as shown in the screenshot below.
(If you wonder how each element of this formula works in Pry, check out this Models Formula Reference sheet).
You can validate the formula's outputs by clicking on the shaded box that says "Jun 2022" and selecting another month. Again, the formula will return zero as expect.
Next, we just need to link the output of this formula to Deferred Revenue on the Balance Sheet.
Before we link the output, let us ensure we have a Deferred Revenue account under Liabilities on the Balance Sheet. First, go to Financials.
Scroll down to Financial Report and scroll until you see Liabilities (under the shaded row Change in Assets). Click on the arrow next to Liabilities to expand this section. If you do not see Deferred Revenue in this section, hover over Liabilities, and when three horizontal dots appear, click on them and select "+ Add Sub-Account." Then, rename this account to Deferred Revenue by hovering over the newly created account, clicking on the three horizontal dots, and selecting "Rename Account."
Now, we will come back to Models and our Deferred Revenue Model - Service Contract model. To create the link between our Deferred Revenue on Payment Date formula and Deferred Revenue on the Balance Sheet, click on the "+" sign in the top right corner of the Outputs column. In the first dropdown, select Deferred Revenue on Payment Date. And in the second dropdown, select Deferred Revenue.
We will then check if all the aforementioned financial statement accounts have been updated. Go back to Financials and scroll down to Financial Report. Scroll through Financial Report; there is no revenue recorded in the Income Statement portion of the report. When we scroll down to the bottom of the Financial Report, there is a Change in Cashflow of $5,000 for June 2022. You can also automatically generate reports for Income Statement and Cashflow Report separately in Pry (here is how). All the linked outputs will stay intact when you create the reports. Note that Pry uses the indirect method to generate Cashflow Report. So the view is different from what you see in an earnings call.
Then, let us check the Balance Sheet. To get to the Balance Sheet, click on the dropdown arrow next to Financial Repor and select Balance Sheet. Once there, we will find the Assets and Liabilities balances have been automatically adjusted for Deferred Revenue (i.e., debiting Assets and crediting liabilities by $5,000).
Note, Pry uses the account name Bank instead of Cash on the Assets side of the Balance Sheet. We shall deem them interchangeable for the rest of the tutorial.
- Starting July 2022 (Contract Start Date) for 12 months (Duration), we should expect Income or Revenue on the Income Statement to be $416.67 each month (i.e., $5,000 divided by 12 months, since we are assuming the service rendered each month is equal in value).
- For the same period, Deferred Revenue should decrease by the same amount (so the Balance Sheet will balance).
- And for this period, there should be no cash flow related to this contract.
This time, we need to create two formulas and two outputs to incorporate these financial statement entries: One formula and one output each for Income or Revenue (Income Statement) and Deferred Revenue (Balance Sheet).
For Income or Revenue on Income Statement, we would like the formula to return the Total Contract Value divided by Contraction Duration in Months ($5,000/12 months = $416.67 per month) each month for the Contract Duration (12 consecutive months) starting in Contract Start Date month (July 2022). In other words, from July 2022 to June 2023, the formula should return $416.67 and $0 for other months.
We will create a new formula and name it "Revenue from Contract" and translate the above logic into Pry formulas, as shown in the screenshot below.
We are using a nested If/Then/Else formula here. And the MONTH_DIFF() function returns the difference in months between the two month variables passed to the function; the function will return a negative number is the second month variable is smaller/earlier than the first month variable.
Then, for the Deferred Revenue formula, we will set it to be the opposite of Revenue from Contract since Deferred Revenue is to be debited or decreased here. Create a new formula and call it "Deferred Revenue through Contract Duration" and in the formula box, type in a negative sign, then call Revenue from Contract function (shown in the screenshot below).
We can validate both formulas by clicking on the shaded box with the month and year ("July 2022") and changing it to a different month to see if the formulas return $416.67 and -$416.67, respectively, for the 12 months starting July 2022 and $0 for any other months.
Lastly, we create two outputs in the Outputs column to link the Income Statement and Balance Sheet outputs to their prospective financial statements. In the first dropdown, select the formula's name, and in the second dropdown, select the financial statement account's name.
By now, we should have mapped all the financial statement entries for our deferred revenue model. So let us check our financial statements.
First stop, we will look at Financial Report on Financials. Scrolling down, we can find the Income row in the Income Statement portion of the report showing $416.67 from July 2022 to June 2023 and $0 for all other months. And the Change in Cashflow row in the Cashflow Report portion of the report shows $5,000 for June 2022 and $0 for other months. Both the entries for both rows are precisely we are expecting.
Then, we will switch to the Balance Sheet and check Assets, Liabilities, and Equity. The Assets balance shows $5,000 starting June 2022, the Payment Date, and stays at the same level for the coming month, as we expect since we are only modeling the deferred revenue for this one contract. The Liabilities balance is $5,000 for June 2022 and decreases by $416.67 each month until it decreases to $0 in June 2023. The Equity balance increases by $416.67 each month starting from $0 in June 2022 until June 2023, when the total balance grows to $5,000 and stays flat afterward. So the sum of Liabilities and Equity balances is $5,000 starting June 2023, matching that of the total Assets balance. Our Balance Sheet is balanced.
Now, we conclude our tutorial on deferred revenue modeling for a service contract with recurring monthly services. Of course, you can always duplicate this model for contracts with varying Total Contract Value, Contract Start Date, Contract Duration, and Payment Schedule. The only changes you need to make are the inputs or assumptions in the Inputs column.