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
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.
- Last, you will get the desired result.
- Excel Formula to Calculate Number of Days Between Today and Another Date
- How to Calculate Average Tenure of Employees in Excel
- Minus Number of Days or a Date from Today in Excel
- How to Calculate Expiration Date with Excel Formula
- How to Use DateDiff Function in Excel VBA (5 Examples)
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.
- 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 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.
- 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.
- Second, in the E5 cell insert the following formula.
- 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.
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.
- How to Add Days to a Date in Excel Excluding Weekends (4 Ways)
- How to Calculate Age on a Specific Date with Formula in Excel
- How to Add Years to a Date in Excel (3 Easy Ways)
- Add 7 Days to a Date in Excel (3 Methods)
- How to Add 6 Months to a Date in Excel (2 Easy Ways)
- How to Add 30 Days to a Date in Excel (7 Quick Methods)