Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

You can download the practice workbook from here.


What Are Days Sales Outstanding in Excel?

Days Sales Outstanding is one of the most used units. It is mainly used to measure how much efficacious 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


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

We’ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to calculate days outstanding in excel on your own. The steps are


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. Follow the process

Dataset to Calculate Days Outstanding in Excel


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 the C9 cell insert the following formula.
=(C5+C6)/2

Inserting Formula to Calculate Days Outstanding in Excel

  • Last, you will get the desired result.

Result to Calculate Days Outstanding in Excel

Read More: How to Calculate Overdue Days in Excel (4 Easy Ways)


Similar Readings


Step 3: Calculation of Days Sales Outstanding

Next, we want to calculate the Days Sales Outstanding in excel. To do so, we have to follow the below steps.

  • In addition, in the C10 cell insert the following formula.
=(C9/C7)*365

Inserting Formula to Calculate Days Outstanding in Excel

  • Finally, you will get the desired result.

DSO final result to Calculate Days Outstanding in Excel

Read More: How to Count Date Occurrences in Excel (3 Suitable Examples)


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 to reclaim 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.
  • Making 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. So, if a company has lower sales amount at the beginning and end 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 days 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:

  • At first, 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.

Dataset to Calculate Days Outstanding in Excel

  • Second, in the E5 cell insert the following formula.
=DAYS(D5,C5)

  • Third, after pressing the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

  • Last, you will get the desired result.

Read More: Excel Formula to Count Days from Date (5 Easy Methods)


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 the ExcelDemy 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.


Related Articles

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo