ARR Reporting in a Spreadsheet
While ARR does approximate revenue, it is still a normalization value and therefore you will be hard pressed to find an ARR data field or function in any GL or finance system. Few billing platforms include ARR, favoring MRR if they have either (SaaSOptics maintains both MRR and ARR).
Unless your finance system has a rev rec module, it will not have a Contract Object, and therefore will not likely track subscription start and end dates, which means "cancellation" actions and churn are difficult to report on in your finance system.
Without support for ARR and cancellations in your finance system, most turn to Excel to track and measure ARR and churn. While SaaSOptics now has significantly evolved tools for ARR reporting, we understand well how to track this in spreadsheets as a spreadsheet was the seed from which SaaSOptics sprang (not technically of course, just conceptually).
To perform core ARR calculations, it is easiest to start with a simple "status" or state spreadsheet. Your xls will include basic information needed to report on the present state of each contract or subscription. This approach works well for a few dozen customers, but its value quickly evaporates as a company grows. Ultimately, this approach does not provide information to report on changes, and what is fundamentally interesting about a subscription company is the change or rate of change.
In short order, most organizations move to a transaction or subscription ledger approach, one that mimics a simple database that captures each subscription action (new booking, upgrade, renewal) as a record in the spreadsheet. To calculate ARR Churn, you need to report on cancellations. A cancellation is the equivalent of the absence of a Renewal. However, measuring a cancellation using an "absence of data" is extremely difficult in Excel. In other words, you need some form of a cancellation record that you can measure. You either tag transactions to indicate the transaction did not renew, or you add a Cancellation transaction with a value (for bookings loss) and an ARR value.
The best-practice approach to creating cancellation records is to record the cancellation in the same period as you would record it if it were a renewal. Doing so enables you to consistently measure renewals and churn, a mathematical must since churn is simply 1 minus Renewal Rate.
By way of illustration, a subscription ends on July 31. If it renews, the start date of the new term is August 1, and therefore the renewal date for ARR calculations is August 1. If it doesn't renew, i.e. it cancels, there is a tendency to report the Cancellation (especially within the Sales function) as of the end date. However, in doing so, you are reporting the cancellation in a different period. The Cancellation should be recorded on Aug 1, which is the same date and therefore in the same period as the renewal had it renewed.
The typical ARR report performance report includes ARR totals broken out by the following classes: New, Renewal, Expansion/Upgrade, Contraction/Downgrade, and Lost as shown in this SaaSOptics ARR Momentum Report.
While the data management and xls formulas become increasingly complicated as your business and reporting needs grow, calculation of New and Lost are typically the easiest and can usually be calculated using a data field or flag to indicate the class of a record. In early stage subscription businesses, you can also use data fields/flags to ind
icate the class of a transaction. However, as the volume of data grows and the complexity of transactions increases, this becomes increasingly complicated. Mid-term subscription changes for quantity, products, value, and term end dates all create immediate havoc with the formulas used to calculate Expansion, Contraction and Renewals.