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 Planning- Global Net 0, 30 or 60 Delayed Days for Collection
- Global Trended % of Revenue
- DSO
- Accounts Receivable Turnover Ratio
- Non-Standard Net Delayed Days for Collection, e.g., Net 45 (coming soon!)
Accounts Receivable Planning
Global Net 0, 30 or 60 Delayed Days for Collection
With this method, we assume that the current month's total revenue will be collected the same month (0 days), next month (30 days), or 2 months from the sale (60 days).
To enable this method, go to Settings ⚙️ > Forecast and select A/R FORECASTING METHOD: Delayed days for Collection. You'll then be prompted to configure which account should be used to project AR and whether you would like to apply a 0, 30 or 60-day collection.
This setting has a global impact, meaning these settings will apply to all non-POR plans within your Jirav account.
Click here to learn more about using the system A/R Forecasting Methods.
Global Trended Percent of Revenue
Accounts Receivable is planned by taking a trailing monthly average of the calculated historical total A/R as a % of revenue and then multiplying that ratio by the current month's revenue forecast.
To configure this method, go to Settings ⚙️ > Forecast and select A/R FORECASTING METHOD: Trended percent of Revenue. You'll then be prompted to configure which account should be used to project AR and how many months of actuals should be used for the trailing average A/R to Revenue %.
This setting has a global impact, meaning these settings will apply to all non-POR plans within your Jirav account.
Click here to learn more about using the system A/R Forecasting Methods.
Days Sales Outstanding (DSO)
Accounts Receivable is planned based on an assumed average number days it will take the company to collect an invoice after a sale is made.
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 plan AR based on DSO, follow the steps below. Note, that this method is implemented on a per-plan basis rather than being applied globally.
- Disable System A/R Forecasting
Navigate to Settings ⚙️ > Forecast and select A/R FORECASTING METHOD: None
Be aware that the System A/R Forecasting Method has a global impact and will affect all plans that are not specifically designated as PORs. If there are any plans where you wish to maintain the system A/R settings, designate them as PORs before taking this step.
- Define Custom Table
To plan Accounts Receivable based on DSO, the formula should be (Revenue / Days in Month) * DSO. To accommodate this in Jirav, we need to add a custom line to calculate Average Daily Sales, i.e., Revenue / Days in Month, and either a custom line or an assumption for DSO. When building custom, supporting calculations like this, it is recommended to organize the calculations in a Custom Table.
Create new lines in a Custom Table for Average Daily Sales and DSO and Reference rows for the Revenue and Accounts Receivable GL accounts. It should look similar to the one below when completed.
- Calculate Average Daily Sales
Navigate to the Custom Table defined in Step 2 and add a Global Driver to calculate Average Daily Sales. The Global Driver should divide Revenue by a constant of 30 to represent days in the month. Update the Start Date to Min Date so that the Driver calculates for Actuals & Plan periods.Pro Tip: 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.
- Calculate Historical DSO
Calculate historical DSO for Actuals by adding a Global Driver to DSO. The Global Driver should divide Accounts Receivable by Average Daily Sales. Update the Start Date to Min Date and the End Date to Cutover Date so that the Driver calculates only for Actuals periods.
- Add a Planned DSO
Add a forecasted DSO to the plan by adding a Custom Plan Driver that looks at a trailing 3-month average of historical DSO.
Pro Tip: There are various methods to consider when populating planned DSO. For instance, you can utilize an input assumption, refer to a 6-month trailing average, or use the previous month's actuals as an alternative to the 3-month average.
- Forecast Accounts Receivable
Now that we've configured all the supporting calculations, plan for Accounts Receivable using Balance Sheet Drivers. Navigate to Drivers from the Plans area, locate the Balance Sheet section, and select + Add Driver.
This Balance Sheet Driver should be an Increase to Accounts Receivable multiply Average Daily Sales * DSO from the Custom Table.
In Jirav, the default setting for Balance Sheet accounts is to start with the previous month's balance and then make adjustments for increases and/or decreases. Because our increase driver reflects the ending balance for Accounts Receivable, we also need to include a decrease driver for AR to offset the prior month's balance and reduce the overall balance.
- Check Your Work
Accounts Receivable should now be forecasted based on the assumed, planned DSO. You can quickly confirm that Accounts Receivable (AR) is calculating correctly by performing any of the following checks:
(1) Review the Custom Table
(2) Review the Assets Plan Table
(3) View the Balance Sheet Report:
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.