How to Calculate Days Outstanding in Excel (With Easy Steps)

This tutorial will demonstrate how to calculate days outstanding in Excel. When you are in the business field, it requires lots of effort to reclaim cash from clients. So, we need a parameter to measure this retrieving cash capacity of the company on an annual basis. This capacity helps to understand the overall effectiveness of the company. So, it is very important to learn how to calculate days outstanding in Excel.

What Are Days Sales Outstanding in Excel?

Days Sales Outstanding is mainly used to measure how successful a company is in terms of reclaiming cash from consumers. The day’s sales outstanding can easily show the number of days needed to reclaim cash from clients on average. After that, one can easily decide the overall company’s retrieving capacity.

The formula is:

Days Sales Outstanding (DSO) = (Average Accounts Receivable / Revenue) * 365 Days

How to Calculate Days Outstanding in Excel: Step-by-Step Procedures

We’ll use a sample dataset to explain the example easily. If you follow the steps correctly, you should learn how to calculate days outstanding in Excel.

Step 1: Arranging Dataset

In this case, our first step is to calculate days outstanding in Excel using the existing dataset. For this instance, we have Particulars in column B, and Amount (in millions) in column C. After making the dataset, you will able to use this for other purposes.

Step 2: Calculating Average Receivable

Now, we want to calculate the Average Receivable amount in Excel. To do so, we have to follow the below steps.

First, in cell C9 insert the following formula.

=(C5+C6)/2

You will get the desired result.

Step 3: Calculation of Days Sales Outstanding

Next, we want to calculate the Days’ Sales Outstanding in Excel.

In addition, in cell C10 insert the following formula.

=(C9/C7)*365

Finally, you will get the desired result.

How to Predict High and Low Days Sales Outstanding in Excel

For a company, it is very important to predict the high and low days sales outstanding in Excel. It can help decide whether the company is capable of reclaiming the cash from its clients or not. But according to it, we will have two results. One is Low days sales outstanding and the other is High days sales outstanding.

Low Days Sales Outstanding: If the company reclaims a lower amount of cash credit in comparison to the net credit, then the overall days’ sales outstanding will be lower than it needs.
High Days Sales Outstanding: If the company reclaims a higher amount of cash credit in comparison to the net credit, then the overall days’ sales outstanding will be higher.

If the company has a lower DSO then it needs to work on its overall maintenance and if the company has a higher DSO then it should try to maintain it.

How to Use Days Sales Outstanding in Industry

Every company uses this DSO parameter to measure its overall cash-reclaiming performance. In the clothing industry or in the credit industry or any industry which is related to cash retrievement from clients then they must use DSO. It helps them to decide where the maintenance of the sales must be taken care of. But, in various cases, the DSO doesn’t work properly such as in a business that runs seasonally or quarterly. In these cases, there normally remain a lot more conditions that need to be considered. Otherwise, the DSO will give the wrong prediction that can harm the company.

Lowering Days Sales Outstanding in Excel

Companies that have a high level of DSO performance in the industry, need to lower their DSO value. The methods for lowering Days Sales Outstanding in Excel are:

Rejecting payments through credit.
Identifying customers who have delayed paying the payment in due time.
Make sure about the customer verification through a proper background check.

With these steps, we can easily reduce the DSO level.

Limitations of Days of Sales Outstanding in Excel

Though the DSO predicts the values with very much accuracy, it has also some limitations of its own such as:

It won’t give proper value if you compare the values of companies that have a different proportion of credit sales.
The formula mainly depends on opening and closing receivable amounts. If a company has a lower sales amount at the beginning but has done very good business in the middle portion, then the DSO won’t show the correct prediction as it notices only the values of the start and end.
Another big problem with the formula is that it doesn’t give a clear definition of the proportion of credit in terms of sales. Sometimes because of that, the DSO prediction becomes misleading.

Using Excel Formula to Count Days From Date to Date

We can also count the day difference from date to date in Excel by using the DAYS function. In the Excel sheet, we store the progress of various work and projects. To track those projects or get any estimated time you can use the DAYS function. The Excel DAYS function is used to calculate the number of days between two dates. The steps of this method are.

Steps:

Our first step is to create a dataset. For this instance, we have Employee Name in column B, Starting Date in column C, Ending Date in column D, and Total Days in Column E. We will use this dataset to explain and understand the whole process easier.

In cell E5 insert the following formula.

=DAYS(D5,C5)

After pressing the Enter button, you will get the result.
Use the Fill Handle to apply the formula to all the desired cells.

Last, you will get the desired result.

Download Practice Workbook

You can download the practice workbook from here.

Calculating Days Outstanding.xlsx

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to learn how to calculate days outstanding in Excel. We will be glad to know if you can execute the task in any other way. Follow our website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.

<< Go Back to Days Between Dates | Calculate Dates | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!