How to Calculate Days Delinquent Sales Outstanding in Excel

Days delinquent sales outstanding (DSO) is a measure of how long it takes a company to collect its accounts receivables, or the money owed by its customers for the goods or services they purchased on credit. A high DSO indicates that the company is taking too long to collect its debts, which may affect its cash flow and working capital. A low DSO means that the company is collecting its debts quickly, which may indicate efficient credit management and customer satisfaction.

In this article, we will explain how to calculate DSO in Excel, using different methods and formulas. We will also provide a basic theory behind DSO, and a scenario to illustrate how to use Excel to calculate DSO and analyze the results.

Basic Theory of DSO

DSO is calculated by dividing the accounts receivable balance by the total credit sales, and multiplying the result by the number of days in the period. The formula is:

DSO=Total Credit SalesAccounts Receivable​×Number of Days

The accounts receivable balance is the amount of money that the company is owed by its customers at the end of the period. The total credit sales are the sales that the company made on credit, or without receiving immediate payment, during the period. The number of days is the length of the period, usually a month, a quarter, or a year.

The DSO can be interpreted as the average number of days that the company takes to collect its receivables. For example, if the DSO is 45 days, it means that the company collects its debts, on average, within 45 days after making the sale.

The DSO can be compared with the company’s credit terms, or the number of days that the company allows its customers to pay their invoices. For example, if the company’s credit terms are 30 days, and the DSO is 45 days, it means that the company is not collecting its debts within the agreed time, and that some customers are paying late. This may indicate a problem with the company’s credit policy, collections process, or customer relationship.

The DSO can also be compared with the industry average, or the DSO of other companies in the same sector. This can help to assess the company’s performance and competitiveness. For example, if the industry average DSO is 40 days, and the company’s DSO is 45 days, it means that the company is taking longer than its peers to collect its debts, which may put it at a disadvantage.

How to Calculate DSO in Excel

There are different ways to calculate DSO in Excel, depending on the data available and the level of detail required. Here are some of the most common methods:

Method 1: Using IF with Subtraction Formula

This method is suitable when you have the invoice date and the payment date for each transaction, and you want to calculate the DSO for each customer or invoice. The formula is:

=IF(Payment Date>Invoice Date,Payment Date-Invoice Date,0)

This formula checks if the payment date is later than the invoice date, and if so, it subtracts the invoice date from the payment date to get the number of days that the payment was overdue. If the payment date is earlier or equal to the invoice date, it returns zero, meaning that the payment was not overdue.

To use this method, follow these steps:

1. Create a new worksheet and input your invoice date, payment date, customer name, and invoice amount in four columns.
2. In the fifth column, insert the formula =IF(Payment Date>Invoice Date,Payment Date-Invoice Date,0) and copy it down for all the rows.
3. In the sixth column, insert the formula =Invoice Amount*Overdue Days and copy it down for all the rows. This will give you the weighted overdue days for each invoice, which is the product of the invoice amount and the overdue days.
4. To calculate the DSO for each customer, use the SUMIF function to sum up the invoice amounts and the weighted overdue days for each customer, and then divide the weighted overdue days by the invoice amounts. The formula is:

=SUMIF(Customer Name,Customer Name,Weighted Overdue Days)/SUMIF(Customer Name,Customer Name,Invoice Amount)

5. To calculate the overall DSO for the period, use the SUM function to sum up the total invoice amounts and the total weighted overdue days, and then divide the total weighted overdue days by the total invoice amounts. The formula is:

=SUM(Weighted Overdue Days)/SUM(Invoice Amount)

6. Format the cells to show DSO in days.
7. Save your worksheet, and you’re done!

Here is an example of how the worksheet may look like:

The DSO for each customer is:

The overall DSO for the period is 15.5 days.

Method 2: Utilizing IF and DAY Functions

This method is similar to the previous one, but it uses the DAY function to extract the day of the month from the invoice date and the payment date, and then subtracts them to get the overdue days. This method is useful when you have the invoice date and the payment date in the same month, and you want to calculate the DSO for each customer or invoice. The formula is:

=IF(DAY(Payment Date)>DAY(Invoice Date),DAY(Payment Date)-DAY(Invoice Date),0)

This formula checks if the day of the payment date is greater than the day of the invoice date, and if so, it subtracts the day of the invoice date from the day of the payment date to get the number of days that the payment was overdue. If the day of the payment date is less than or equal to the day of the invoice date, it returns zero, meaning that the payment was not overdue.

To use this method, follow the same steps as in the previous method, but replace the formula in the fifth column with the one above.

Here is an example of how the worksheet may look like:

Method 3: Using AVERAGEIF Function

This method is useful when you have the invoice date and the payment date for each transaction, and you want to calculate the average DSO for each month. The formula is:

=AVERAGEIF(Invoice Date,Month Criteria,Overdue Days)

This formula calculates the average of the overdue days for the invoices that match the month criteria. The invoice date is the date when the invoice was issued, the month criteria is the month that you want to calculate the DSO for, and the overdue days is the number of days that the payment was overdue, calculated using the previous methods.

To use this method, follow these steps:

1. Create a new worksheet and input your invoice date, payment date, customer name, and invoice amount in four columns.
2. In the fifth column, insert the formula =IF(Payment Date>Invoice Date,Payment Date-Invoice Date,0) or =IF(DAY(Payment Date)>DAY(Invoice Date),DAY(Payment Date)-DAY(Invoice Date),0) and copy it down for all the rows, depending on whether the invoice date and the payment date are in the same month or not.
3. In another column, insert the formula =MONTH(Invoice Date) and copy it down for all the rows. This will give you the month number for each invoice date, from 1 to 12.
4. In another column, insert the formula =TEXT(Invoice Date,"mmm-yyyy") and copy it down for all the rows. This will give you the month name and year for each invoice date, such as Jan-2024, Feb-2024, etc.
5. To calculate the average DSO for each month, use the AVERAGEIF function to average the overdue days for the invoices that match the month number. The formula is:

=AVERAGEIF(Month Number,Month Number,Overdue Days)

6. Format the cells to show DSO in days.
7. Save your worksheet, and you’re done!

Here is an example of how the worksheet may look like:

The average DSO for each month is:

Method 4: Using Pivot Table

This method is convenient when you have a large amount of data, and you want to calculate the DSO for different categories, such as customer, product, region, etc. A pivot table is a tool that allows you to summarize and analyze data in a table format, by grouping, filtering, and aggregating the data.

To use this method, follow these steps:

1. Create a new worksheet and input your invoice date, payment date, customer name, invoice amount, and any other categories that you want to analyze in separate columns.
2. In another column, insert the formula =IF(Payment Date>Invoice Date,Payment Date-Invoice Date,0) or =IF(DAY(Payment Date)>DAY(Invoice Date),DAY(Payment Date)-DAY(Invoice Date),0) and copy it down for all the rows, depending on whether the invoice date and the payment date are in the same month or not. This will give you the overdue days for each invoice.
3. Select all the data, and go to the Insert tab, and click on Pivot Table. A dialog box will appear, asking you to choose where to place the pivot table. You can choose to create a new worksheet or use an existing one. Click OK.
4. A blank pivot table will be created, along with a pivot table fields pane, where you can drag and drop the fields that you want to include in the pivot table. To calculate the DSO, you need to do the following:
   • Drag the category that you want to group the data by, such as customer name, product, region, etc., to the Rows area. This will create a row for each value in that category.
   • Drag the invoice amount field to the Values area. This will sum up the invoice amounts for each category value. You can change the aggregation function from Sum to Average, Count, or any other function that you want, by clicking on the drop-down arrow next to the field name, and choosing Value Field Settings.
   • Drag the overdue days field to the Values area. This will sum up the overdue days for each category value. You can change the aggregation function from Sum to Average, Count, or any other function that you want, by clicking on the drop-down arrow next to the field name, and choosing Value Field Settings.
   • To calculate the DSO, you need to divide the sum of the overdue days by the sum of the invoice amounts, or the average of the overdue days by the average of the invoice amounts, depending on the aggregation function that you chose. To do this, you need to create a calculated field, by clicking on the Analyze tab, and choosing Fields, Items & Sets, and then Calculated Field. A dialog box will appear, asking you to name the calculated field, and enter the formula. You can name the calculated field as DSO, and enter the formula as =Overdue Days/Invoice Amount or =Overdue Days/Invoice Amount, depending on the aggregation function that you chose. Click OK.
   • The calculated field will appear in the Values area, and the pivot table will show the DSO for each category value. You can format the cells to show DSO in days.
5. You can also add filters, slicers, or timelines to the pivot table, to further refine your analysis. For example, you can filter the data by month, year, or any other criteria, by dragging the invoice date field to the Filters area, and choosing the values that you want to include or exclude. You can also add a slicer or a timeline, by clicking on the Insert tab, and choosing Slicer or Timeline, and selecting the fields that you want to slice or filter by. A slicer or a timeline will appear on the worksheet, where you can select or deselect the values that you want to include or exclude in the pivot table.
6. Save your worksheet, and you’re done!

Here is an example of how the pivot table may look like, with customer name as the category, and invoice date as the filter:

Method 5: Using SUMPRODUCT Function

This method is useful when you have the accounts receivable balance and the total credit sales for each month, and you want to calculate the DSO for the whole year. The formula is:

=SUMPRODUCT(Accounts Receivable,Days in Month)/SUM(Total Credit Sales)

This formula calculates the weighted average of the accounts receivable balance for each month, multiplied by the number of days in that month, and then divides it by the sum of the total credit sales for the year. This gives the DSO for the year, based on the average accounts receivable balance and the average credit sales.

To use this method, follow these steps:

1. Create a new worksheet and input the month name, the accounts receivable balance, the total credit sales, and the number of days in each month in four columns.
2. In another column, insert the formula =SUMPRODUCT(Accounts Receivable,Days in Month)/SUM(Total Credit Sales) and press Enter. This will give you the DSO for the year.
3. Format the cell to show DSO in days.
4. Save your worksheet, and you’re done!

Here is an example of how the worksheet may look like:

The DSO for the year is 45.83 days.