Planning for and analyzing Accounts Receivable
Accounts Receivable on the Balance Sheet represent the money your customers owe you for goods or services they have purchased from you.
For example, let's say I have a company that sells pies, the Pie Company, and I sell 50 of my pot pies to a grocery store, Checker Grocery, at $10 each for a total sale of $500. When I send Checker Grocery an invoice for $500, the accounts receivable balance for The Pie Company will increase by $500. When Checker Grocery subsequently pays the invoice, the accounts receivable balance for The Pie Company will go down by $500 and cash will increase by $500.
The effect of an invoice not being paid in the same month the sale is made has a critical impact on cash. As such, it is very important that Accounts Receivable is analyzed and planned for. The following are common methods used to understand and plan your Accounts Receivable balance.
This article includes an overview of the following areas related to Accounts Receivable:
Accounts Receivable Turnover Ratio
The AR Turnover Ratio tells us how efficiently our company is collecting revenue by calculating the number of times AR is collected over a given period.
For example, let's look at a month with $15,000 in net sales and our AR balance is $6,000 at the beginning of the month $9,000 at the end of the month. Our Accounts Receivable Turnover Ratio would be 2 ($15,000/($6,000+$9,000)/2)).
Typically a higher AR Turnover Ratio means that your company is effectively collecting revenue whereas a lower AR Turnover Ratio means that your collection policies may be ineffective. This ratio can also be used to forecast Accounts Receivable in plan periods.
To calculate your AR Turnover ratio, follow these steps:
-
Create a new line in a Custom Table for Accounts Receivable Turnover Ratio.
Optionally include reference lines for Net Credit Sales & Accounts Receivable GL Accounts.
Net Credit Sales are typically calculated as total Revenue less Discounts, Returns & Allowances. In this example, total Revenue represents Net Credit Sales.
-
Add a Global Driver to calculate the Accounts Receivable Turnover Ratio.
The driver should divide Net Credit Sales by the monthly average Accounts Receivable Balance.
Input 1: Total Revenue
Input 2: To calculate the monthly average, we want to use the beginning balance + the ending balance divided by 2. This is achieved by referencing the prior month-ending balance + the current month-ending balance of the GL account Accounts Receivable.
Note, that the Range for the Driver will need to be added as a custom range and the Reduction should be set to Avg.
Global Driver: Update the Start Date to Min Date and the End Date to Cutover Date to only calculate for Actuals periods.
Days Sales Outstanding (DSO)
DSO tells us the average number of days it takes for us to collect on an invoice after a Sale is made. For example, let's look at a month with 30 days where we had $3,000 in sales and our AR balance is $6,000. Our Average Daily Sales would be $100 ($3,000/30) and our DSO would be 60 ($6,000/$100).
To calculate your DSO, follow these steps:
-
Create new lines in a Custom Table for the Number of Days in the Month, DSO & Average Daily Sales. Optionally include reference lines for the Sales & Accounts Receivable GL accounts.
-
Populate Days in the Month for Actuals & Plan periods. Calendar days in a month or business days in a month can be used.
-
Add a Global Driver to calculate Average Daily Sales.
The Global Driver should divide Sales (Input 1) by Days in Month (Input 2). Update the Start Date to Min Date so that the Driver calculates for Actuals & Plan periods.If Sales fluctuate month to month, a rolling 2 or 3-month Average can be used by adjusting the Range on the Driver & Rate references in the Driver calculation.
-
Add a Driver to calculate DSO (you can also use Global Driver if the same DSO formula should be applied for all the Plans).
The driver should divide Accounts Receivable (Input 1) by Average Daily Sales (Input 2).
Driver formula can be based on the Average Daily Sales from This Month or T3M, T6M, etc.
Planning for Accounts Receivable
Method 1: Net 0, 30 or 60 Delayed Days for Collection
With this method, we assume that the current month's sales will be collected the same month (0 days), next month (30 days), or 2 months from the sale (60 days).
This is the default method available for planning Accounts Receivable in Jirav. To configure which account should be used to project AR and whether you would like to apply a 0, 30 or 60-day collection, go to Settings ⚙️ > Forecast.
Click here to learn more about using the system A/R Forecasting Methods.
Method 2: Trended Percent of Revenue
With this option, Accounts Receivable is planned by calculating historical A/R as a % of Revenue- based on trailing months average- and multiplying by Forecasted Revenue.
Once you choose this A/R Forecasting method, the system will prompt you to select the desired A/R account for forecasting and the corresponding trailing months.
This is the default method available for planning Accounts Receivable in Jirav. To configure which account should be used to project AR and whether you would like to apply a Trended percent of Revenue, go to Settings ⚙️ > Forecast.
Click here to learn more about using the system A/R Forecasting Methods.
Method 3: Days Sales Outstanding (DSO)
DSO can also be used as a method to forecast the future AR balance. Actual DSO can be calculated as described above and then used as a reference point when forecasting Accounts Receivable for the planned period.
To calculate AR using the DSO method in Jirav, follow the steps below. Note, that these steps assume DSO has already been calculated as described above.
Please note, that if the AR forecast should be based on the DSO calculation, a System Default A/R Forecasting should be set up as None:
-
Add a Balance Sheet Driver Increase for Accounts Receivable.
To add a Balance Sheet Driver, navigate to Plan Drivers by selecting the fx from the left menu, locate the Balance Sheet section, and select + Add Driver.
This Balance Sheet Driver should multiply Average Daily Sales * DSO from the Custom Table.
The range for DSO from the Custom Table should reference a prior period so as to not be circular. For example, you might choose to reference actual DSO from the prior month or a trailing 3-month average.
Name: A/R Increase
Output: Assets $ - Accounts Receivable - Driver Increase - None as Rounding - Frequency: 1
Driver: Custom Tables - Balance Sheet - Avg Daily Sales
fx: x
Rate: Custom Tables - Balance Sheet - DSO - Ranges as Last Month - Reduction as Sum
-
Add a Balance Sheet Driver Decrease for Accounts Receivable.
Because the DSO method plans for the AR Ending Balance rather than the net change, a Driver should be added to decrease AR by the Prior month's balance.
Set the Output to Driver Decrease and the Driver to Subtotal AR from the prior month.
Name: A/R Decrease
Output: Assets $ - Accounts Receivable - Driver Decrease - None as Rounding - Frequency: 1
Driver: Assets $ - Accounts Receivable - Line Type as Subtotal - Range as Last Month and Reduction as Sum
fx: x
Rate: Constant 1
- Verify if the added AR Balance Sheet Drivers are calculating as expected.
This can be done by reviewing the Custom Table if Reference lines were used:
or by going to the Assets section of planning:
or by viewing a Balance Sheet Report: