Analyze and evaluate accounts receivable over a 5 year period with this tool. All information can be reviewed graphically over time to provide additional context. Eight key ratios are calculated off of a few inputs. These ratios include Days Sales in Trade AR, AR Turnover, and Bad Debt Expense as a Percentage of Net Sales.
Accounts Receivable Analysis
Microsoft Excel® 2007 or later
Read it Here
There are two tabs included within this download, a picture of each is included above. The first tab – Aging Analysis allows you to evaluate the trend of an accounts receivable aging over a 5 year period. The information input into the first tab will automatically update the next tab. This will be used to develop key ratios associated with accounts receivable. The only additional information necessary is Net Sales, Allowance for Bad Debt, and Bad Debt Expense. Note, all formulas are in blue.
When you begin entering information (completing the items in red) this information will flow through to other portions of this template. For example, once you enter the date, all other dates are automatically calculated. This information is carried over to the next tab.
There are a total of 8 key ratios or measures of AR that can be evaluated on the Analytics tab. Each of these can be viewed graphically, as shown in the picture to the right. Included within the chart area is a drop down box, allowing you to select the measure of interest.
At the bottom of each tab is an area referred to as ‘Tickmarks’ – this area provides space to document analysis conducted associated with any unusual or significant activity identified.