Nothing is more satisfying than having all your accounts balance after some careful calculations! This guide was designed as a deep dive into how a clinic can reconcile their accounts receivable from the end of the previous period to the number shown at the end of the current period.
We do want to mention right away that this reconciliation step is not mandatory as Jane’s A/R report will provide you with a live representation of which invoices are still outstanding as of a given date.
With that said, we understand that some clinics are interested in further exploring how money is flowing in and out of their business, so we wanted to capture all of the different moving parts that one must consider when working with this financial information.
We recommend chatting with your friendly neighbourhood bookkeeper or accountant if you’d like a better understanding of these numbers.
📍Note: Since this is a bit of a longer guide, we wanted to emphasize that you’ll need to follow both #1 - Using the Right Formula and #2 - Turn on Invoice Reversals, in order for A/R to properly reconcile in your account. The section “How to Arrive at this Calculation” takes a closer look at the importance of using the right formula, but is not required reading!
1️⃣ Use the Right Formula
The A/R Reconciliation Formula
Here it is, the golden formula for reconciling your accounts receivable between two periods.
Opening A/R + Sales - Transactions + Closing Credits - Opening Credits - Credit Memos Created + Tips = Closing A/R
Let’s quickly go over what each of these variables represents, and where you can find this information in Jane.
This number represents the final A/R value for the end of the previous period. For example, if you were trying to reconcile your A/R for the month of February, you would be looking for the A/R amount for January 31st.
In Jane: This would be the total A/R shown on your Accounts Receivable Report run for the end of the past period.
This goes hand-in-hand with the opening A/R number and represents the final A/R value for the end of the current period you are working with. If you are reconciling the month of February, this would be the A/R as of February 28th.
In Jane: Same as above— run the Accounts Receivable report for the end of the current period.
This represents the total value of new invoices generated within the period of interest, based on the invoiced date (more on that a bit later). For example, in the month of February, we billed out a total of $10,000.00 worth of new invoices for the clinic’s services and products.
In Jane: Run the Sales Report for the entire period and filter by the Invoice Date (rather than the Purchase Date).
This is the total value of payments that had been received during the period to pay off outstanding balances.
In Jane: Run the Transactions Report for the period you are working with and use the Total value (summing all the payment methods).
This represents all of the outstanding credit that is available on patient’s accounts that could be used to potentially pay off an invoice balance. This figure includes both credits that are a result of an unapplied payment, as well as any credit memos (that are not backed by a transaction).
In Jane: Run the Credit Report for the last date of the previous period.
This is the other half of the credit puzzle— this is the amount of credit that is still outstanding at the end of the period we are working with. This helps determine the change in credit over time.
In Jane: Run the Credit Report for the last date of the current period.
Credit Memos Created
Since credit memos are not backed by a particular transaction that was collected by the clinic, we’ll want to make sure that we account for them since they contribute towards the general credit amounts.
In Jane: Run the Credit Memo Report for the period that you are working with and use the number shown in the Total column.
If you have Tips enabled in your account (under Settings > Billing Settings), we’ll also want to take these into account.
In Jane: Run the Billing Summary Report for the period and scroll down to the Tips section
💭 How to Arrive at this Calculation
This section will go into a bit more detail on why each of these different values should be considered, building towards the final formula. Feel free to skip this section if you have a good grasp of how these numbers work together.
Alrighty, let’s get started. There is a very fundamental principle at work here which will act as our starting place: I had this amount owing to start with. I increased the amount owing this period but also collected a handful of payments. When I add what I billed out and payments that I took in to what I started with, I should get the current amount owing.
This starting principle can be summarized as:
Opening A/R + Sales - Transactions = Closing A/R
Hopefully, this part is fairly straight-forward and intuitive!
The Effect of Credit (Unapplied Payments)
Now, let’s start adding a few more layers here. Jane’s A/R report is a list of all invoices that a patient has outstanding, and does not take into account any account credit the patient might have. Account credit represents a payment that was previously collected but has not yet been applied to an invoice.
The previous formula assumes that any payment that has been collected is applied to an invoice right away. We know that this might not always be the case (we might take payments in advance, or have overcharged a patient). For that reason, we also have to factor in the change in credit over time.
💭Here’s an example to illustrate the importance of credits:
At the end of March, the clinic’s A/R was $500. Of this $500, Alice had a $30 invoice owing on her account. There was also $50 of credit on her account as well, but by the end of March, the payment had not been connected (applied) to the invoice.
Let’s say in the month of April, no new invoices were generated and no new payments were received. However, Alice used some of her $50 credit to pay for her $30 invoice.
At the end of April, it would appear that the A/R had reduced to $470 (-$30), but no new payments or invoices were involved. With the starting formula, we’d end up with something like this which wouldn’t make any sense:
❌$500 + $0 - $0 = $470
So, this is where that credit comes in… let’s update the formula to reflect this:
Opening A/R + Sales - Transactions + Closing Credits - Opening Credits = Closing A/R
If we were to plug in the numbers into this updated formula, the two sides of the equation now balance out.
✅$500 + $0 - $0 + $20 - $50 = $470
Effect of Credit Memos
Credit memos are a special type of payment method because they do not create a transaction record in Jane, and are designed to represent a payment that was not actually collected.
Due to their unique nature, we also have to be mindful of these types of credits when reconciling our A/R.
💭Let’s look at another example with credit memos thrown into the mix:
At the end of March, our clinic’s A/R is $500. During the month of April, no new transactions or payments are collected. However, midway through April, a credit memo of $50 is created for Alice. This generates an account credit on her account, although no transaction actually took place. No invoices were paid during the period, so the final A/R at the end of April is still $500. However, since credit memos are included in the credit total we pull from the Credit Report, this is what happens with our formula in its current state:
❌$500 + $0 - $0 + $50 - $0 = $550 (when the A/R should be remain at $500)
However, we can’t rule out credit memos completely, because they can still be used to pay for an invoice. We don’t want to factor in newly created credit memos as transactions, but we will want to consider any credit memos used during the period. Here is our updated formula:
Opening A/R + Sales - Transactions + Closing Credits - Opening Credits - Credit Memos Created = Closing A/R
And now our calculation from before should now balance out.
✅$500 + $0 - $0 + ($50 - $0) - $50 = $500
Just to make sure we’re on the right track, let’s see what happens with our updated formula if Alice were to use her $50 credit memo in full to pay off an invoice during April (we would expect A/R to go down by $50). This means at the beginning of the period, the Credit Report would have shown $50, but at the end there would be no credit available to use.
✅$500 + $0 - $0 + ($0 - $50) - $0 = $450
Effect of Tips
If your clinic allows for tips, you’ll also want to account for these in your final calculation. While a tip is a part of the payments you’ve received, they do not have a corresponding invoice that makes up the Sales total. In other words, we’ll want to make sure to factor them in so that everything balances out.
💭Time to look at one final example, now with tips:
At the end of March our A/R is $500, no new invoices are generated, but a tip was collected for $10. This would show up in Jane as a $10 transaction, although there isn’t a corresponding invoice. Final A/R at the end of April has NOT changed, since the tip payment was not used to pay an already outstanding invoice. With our current formula, we get a confusing outcome:
❌$500 + $0 - $10 + 0 - 0 - 0 = $490 (when it should be $500)
You know what time it is now… time to update our formula to also take tips into consideration, which will bring us to the final iteration of the A/R formula we introduced at the beginning of this guide.
Opening A/R + Sales - Transactions + Closing Credits - Opening Credits - Credit Memos Created + Tips = Closing A/R
As one final check, let’s plug things in and make sure everything balances out:
✅$500 + 0 - $10 + 0 - 0 - 0 + $10 = $500
2️⃣ Turn on Invoice Reversals
Great! Now that we are confident that we’re using the right formula, we’ll want to also make sure that the numbers that we are using make sense with this calculation.
If you are unfamiliar with Invoice Reversals, we have a whole guide dedicated to them which you can read here: Understanding Invoice Reversals
There is also a companion guide that covers setting the Reconciliation Date, which will enable invoice reversals in your account: Working with the Reconciliation Date
In particular, we’ll want to make sure that the values that we are using for the opening and closing A/R, as well as the Sales amounts are historically accurate (and therefore make sense to use with this formula).
As a bit of context, by default Jane’s reports display live information. In other words, if any changes are made to an invoice, Jane will replace the old line item with a new line item reflecting the updated information. Just for emphasis, this means that the original line item will no longer be recorded anywhere in the report.
This benefit of this style of reporting is that all of the data that you are seeing is active data in the account, so you know that you are seeing the current status of any particular value at a given point in time. It also makes the data look rather consistent and neat, making it quite approachable for users who find reporting a bit overwhelming.
With that said, this type of reporting is not compatible with the reconciliation that we might are looking to do here, because there is no historical record of line items that existed at a previous time.
By setting a reconciliation date which enables invoice reversals, any changes that are made to an invoice will be tracked. Instead of replacing the original line item, Jane will initiate a reversal (a negative line item for the same value) and then generate a new line item representing the updated invoice. You will be able to read all about this in further detail in the guides above, but we just wanted to provide you with the necessary context for these next sections.
📍Note: Keep in mind that Invoice Reversals will start to track as soon as you turn on the reconciliation date. However, turning this setting on will not retroactively display invoice reversals for changes that had been made prior to the reconciliation date enabled.
WITHOUT Invoice Reversals:
Let’s look at an example at why the A/R reconciliation calculation will not balance if we do not have invoice reversals enabled.
Imagine that a brand new clinic invoiced their first patient for $100 as of April 30th, but didn’t collect a payment from them yet. This means that the A/R at the end of the month would be $100. The next day on May 1st, they realize they forgot to apply their grand opening discount of 10% and added it to the invoice when they remembered.
What happens here is that without invoice reversals, the invoice for April 30th has been replaced for an updated May 1st invoice of $90. This means going back to the A/R report in April 30th will now show $0 worth of A/R on that date, but $90 for May 1st.
This appears to be a $90 increase in A/R between the months, but is not associated with a corresponding sale during that period.
WITH Invoice Reversals:
Now, let’s consider the same scenario but with invoice reversals enabled in your account. Since the original April 30th invoice has been modified, Jane will add a negative -$100 line item on May 1st when the change was made, and a subsequent +$90 invoice to represent the newly updated price.
This is different from the previous example because the A/R report for April 30th will still have the original $100 line item recorded, and the A/R report for May 1st will display $90.
One of the main changes is that when considering the Sales, there will be a corresponding change of -$10 that we can point to which will match up with the -$10 change in A/R between the periods. This is the information that isn’t quite captured when invoice reversals are not enabled.
A friendly reminder that since the Accounts Receivable report is based on an appointment or product’s invoice date, that you’ll want to use the invoice date version of the Sales Report to ensure that all of these reversals are captured correctly.
Still have a few questions about some of the numbers that you’re seeing in your reports? Feel free to reach out to us at firstname.lastname@example.org and we’d be happy to guide you in the right direction.