This article covers
What formula should be used to determine the opening balance at the start of the month?
The sales report shows the total value of recurring invoices generated for a particular period when invoices are consolidated. The invoice reports sum up all the transactions up to the latest day, including payments and adjustments.
For example, On the invoice report, if there are multiple transactions (payments, adjustments) for a specific invoice it shows the sum of all the transactions and the latest day. Specific use case is a $180 invoice was partially paid -$75 ( May 2019) and the remaining amount was written off -$105 ( January 2020). If I want to compare to what is outstanding as of May 31, 2019, it will show this invoice outstanding as $180 as it doesn’t account for partial payment of $75. Each month going forward I continue to carry this difference of $75 since I sort by PAID and until the time when the full invoice is cleared it continues to show $180 as outstanding. So how would I know to look at this schedule that $75 payment happened in May 2019 and $105 adjustment happened in Jan 2020?
To determine the multiple transactions and the time period at which the transaction happened,
Filter by “Paid on date” in the Invoices report (In this you shall see that for the use case above payment of $180 is marked as Paid. However, that is not the case since there was an adjustment of $105)
Look at Column U for adjustment amount which will mention that the amount of $105 was adjusted with respect to this invoice.
To check when the invoice was paid for $75 and when the adjustment was done for $105, you can check the details in Chargebee by searching for the given invoice. We created an invoice on the test site for $1000, paid $400 via cash and the rest was marked as Write-off. You will be able to see details of the same at the invoice level in the below image,
How would you know which invoices have multiple transactions:
There are two options to determine the invoices that have multiple transactions:
Refer to the Credit Notes sheet and filter by Column - F (Reason Code). All adjustments in the form of write-off shall have the Reason Code mentioned as Write-off in this column.
Filter the data that you are analyzing by column – U (Adjustments) in the Invoices report. If there is a value in this column, it means there are multiple transactions, however, if there is no value here—it would imply that there is only one transaction. Adjustments shall include Write-offs and Credit Note provided to the customer.
To determine the aging or the opening balance at the start of the month, apply the formula as specified below,