Analysis of Accounts Receivable financing in Excel
In the analysis of accounts receivable, some tasks that may seem complex at first glance often turn out to be simple upon closer inspection. All you need is to understand their essence and use Excel for their solution. We'll learn how to extract from the general list those clients whose debt exceeds the legal costs.
Calculation of Overdue Days
The situation where it's necessary to identify clients with outstanding debt can arise in a company that operates with payment deferrals. For instance, goods are shipped on the 1st, and the buyer is given a 2-week payment extension until the 15th. Let's create a basic report of accounts receivable in Excel to understand the principle.
In cell B2, the current date is entered not as numbers but as a formula, so that the document always displays the current date when opened. The column with shipment dates is in DATE format, while the debt amounts are in financial format.
To calculate the number of overdue days for accounts receivable, subtract the actual payment due date from the current date. Let's add another column where we'll input a simple formula: add the payment deferral days to the shipment date. Then, drag the formula down to the end of the table.
From the accounts, we find that, for example, Skyward Treasures should have paid by February 4th, but today is March 3rd. Meanwhile, Serene Seasons Shop has 9 more days to settle their payment, as their deadline is March 12th.
Now, let's calculate the number of overdue days, ensuring we change the format of the cells in the new column to numeric.
That is, we subtracted the payment date from the current date to determine the number of overdue days. Notice that cell B1 is absolute (enclosed in $ symbols), so when dragging the formula, it remains unchanged. By the way, we have two negative values, indicating that Serene Seasons Shop and Crystal Clear Creations still have 9 and 1 days, respectively, for timely payments before becoming overdue.
Calculation of Penalty for the Overdue Period
An overdue client should not go unpunished. Therefore, we impose a penalty, which is 0.1% for each overdue day. Multiply 0.1% by the debt amount and the number of overdue days.
We'll hide two clients with no debt, highlighted in red, but won't remove them from the list. This ensures that when reopening this document in a week, the debt will be recalculated automatically. Highlight both rows, right-click, and choose HIDE.
The disrupted sequence of rows reminds us of two other clients.
Calculation of Refinancing Rate on the Calculation Day
The second method of interest calculation on the debt amount depends on the refinancing rate on the calculation day. Suppose it's 10%. Multiply this rate by the number of overdue days and the debt amount, divided by 365.
We see that the penalties in this calculation are lower than adding 0.1% for each overdue day. Hence, we conclude which interest calculation method is more beneficial to specify in contracts.
Identifying Unscrupulous Clients
Now, the main task is to identify the targeted clients. First, sum the debt and penalties charged at 0.1%.
Assuming legal costs amount to $5,000 per client, let's calculate the total we can retrieve by suing those with debts exceeding $5,000. For this, use the SUMIFS function in cell H16:
=SUMIFS(H4:H15,H4:H15,">5000",G4:G15,">=0")
The first argument searches within a range for the criterion. The second is the criterion itself (>5000). The third is the summation range (coincides with the first). Remember to subtract hidden rows for Strigunova and Malyshov (H12 and H13). The total is $75,406.
To quickly identify those to sue, utilize the IF function in a new column.
Download a template for analyzing Accounts Receivables in Excel
Read the formula as: if the total debt exceeds $5,000 (H4>5000), then display "to sue." Otherwise, display blank. This way, we've identified clients with debt surpassing legal costs.
