Tobias Meyer SAP BI Blog. At the moment it is very quiet about new blog post. This is maybe the summer break everybody talks about. R2KuVYitHkk/hqdefault.jpg' alt='Dso Calculation Template Excel' title='Dso Calculation Template Excel' />I am at the final phase of my diploma thesis and have no time to do cool stuff and also SAP doesnt release Analysis. Office 2. 5. It is still in ramp up and nothing new at the moment. So I could only say, my current project is a BW on HANA and I am testing how HANA SQL Script AMDP works and how fast we could build some stuff. First tests show a transformation with routines. The same transformation with AMDP ABAP Managed Database Procedures only need between 1. This is really impressive. So stay tuned, if SAP will release Analysis Office 2. By using the STechno. Find out more. OK. I need the formula to calculate that. Excel General Budeting for AR using DSO Results 1 to 3 of 3 Budeting for AR using DSO. LinkBack. LinkBack URL About. DSO Days Sales Outstanding Analysis. The DSO analysis is also an analysis of customer payment history. This analysis is always made within a specific period. Aging analysis is helping accountants since ages and beyond doubt this is one the those reports that are prepared almost every period to track both receivables and. Making Aging Analysis Reports Using Excel How To. Aging analysis is helping accountants since ages and beyond doubt this is one the those reports that are prepared almost every period to track both receivables and payables. If you want to learn more about aging or ageing analysis do read my explanation What is age, aged, ageing or aging analysis If you are new to this analysis tool and dont know what it does I strongly recommend reading the above mentioned explanation as only then you will grab the concept of what we are going to do and why we are going to do. This is what our end result at the end of this tutorial looks like Download Fully Worked Excel file. You can download the fully worked file free of cost but if you like the effort and gained something valuable then you can set the price whatever you deem fit and pay. No more missed important software updates UpdateStar 11 lets you stay up to date and secure with the software on your computer. Use Excel to Fix Your Broken AR Measure of Days Sales Outstanding in. Days Sales Outstanding in Receivables DSO. DSO calculation. If you want to download it for free then simply set the price to 0. Enjoy Download Now. In very few words and if I try to define aging analysis in context of receivables or debtors, then it is an analysis that helps me determine when certain sales invoices are falling due. And more importantly since how long a certain receivables are outstanding. First aspect helps me determine expected cash flows. Second helps me determine where recovery department must concentrate its efforts. A dash at the data and requirements. Open up the workbook you downloaded and its a fairly simple data consisting of four very fragrant debtors. Each has several invoices against its name with different due dates. We want to classify invoices as follows Not due The invoices which that has not fallen due yet. The invoices that are past due for days between 1 to 3. The invoices that are past due for days between 3. The invoices that are past due for days between 6. Understanding the approach. So it seems simple and it is simple if you know how to go about IF function and how to make multiple IF statements using AND function. Its all part of todays discussion. But as I always like to raise the bar a little higher than requirement so I will going one step further and will do aging analysis using the slabs give above i. So lets get started Watch a walkthrough OR continue reading for something additionalYou can either watch the following video to prepare basic aging analysis report OR continue reading to learn additional techniques involving conditional formatting and sparklines Adding column headers. We need to add few headings here to accommodate our requirement. So in cell. E1 type Days outstanding. F1 type Not due. G1 type 0 3. H1 type 3. 1 6. I1 type 6. J1 type 9. 0 days. Giving Data Wings MeaningNumber crunching Time Step 1 In cell E5 put the following formula press Enter IFTODAY C5,TODAY C5,0This formula checks that if todays date is later or greater than the date mentioned in cell C5 the deduct todays date from the date in cell C2 to calculate the number of days. However, if todays date is earlier than the date in C5 then put 0 as a result. Step 2 To apply the same formula in order to calculate the days for all the invoices simple double click the fill handler and it will populate the cells immediately of column E. Giving Data even more meaning Although we have calculated number of days and manager can easily sort and filter the column to find the invoices exceeding certain number of days. But using conditional formatting feature in Excel we can create a sort of heatmap to show values with long outstanding period as red and the ones that are in favourable time range as blue or similar color. By adding colors to the data, its much easier to identify and focus on important aspects of data. Follow these steps to accomplish it. Step 1 Select the values in Column E that you just calculated using formula. You can make selection with mouse or using keyboard shortcuts. To learn how to select using shortcuts read this 4. Excel navigational shortcuts to make accounting job super efficient. Adventure Journal Wordpress Theme. Step 2 Go to Home tab Styles group click the conditional formatting drop down click new rule. A new dialogue box will appear. Step 3 Make sure the first option is selected. From the format style drop down selection menu select 3 color scale. This will enable three shades possibility. Step 4 From type drop down menu select number option under each of the three categories i. Step 5 Set the values as following minimum 0midpoint 6. Step 6 Select the colors you like for minimum, midpoint and maximum values. I chose blue for minimum, beige for midpoint and red for maximum. You can select any that suits your need and liking. Click OKJoys now you can instantly see what invoice needs immediate attention. It seems this companys recovery department is doing nothing at allAging ReportFinding the lazy ones Now the next part which is our actual requirement i. Follow along Step 1 In cell F5 put the following formula IFE50,D5,0This formula checks that if value in cell E5 is not equal to zero then fetch the value in cell D5 as this column is not due and this way the value of invoice will be inserted here as zero in cell E5 means it hasnt even fallen due. Step 2 In cell G5 of column 0 3. IFC5lt TODAY,IFTODAY C2lt 3. D5,0,0The above formula is an example of nested IF statements in which we basically have IF function within IF function. In words the above formula will be said as follows If date in cell C5 is earlier then the date than today then check if the difference between today and date in cell C5 is less than or equal to 3. If it is less than or equal to 3. D5. If not equal or less than 3. And also if today is later than the date in cell C5 then put zero. Once the formula is in place. Press Enter and double click the fill handle to apply the same formula down the column covering all the invoices. Following animation helps. Step 3 Next column is of 3. This is a bit challenging as we two conditions to meet check if the invoice is past due for days upto 6. In other words it must exclude the invoices that has fallen due for in last 3. If we dont add second condition then it will include those invoices as well that have fallen in last 3. I ask it for all the invoices that has fallen due in last 6. So I have to tell Excel to exclude such invoices. To do this put the following formula in cell K5 IFANDTODAY C5lt 6. TODAY C5 3. D5,0The above formula is an example of multiple IF statements or multiple criteria where each criteria is joined using AND function. AND function does literally the same thing as the and does in language. So the above formula will be read like this in plain English If difference between todays date and the date in cell C5 is less than or equal to 6. AND the difference between todays date and the date in cell C5 is greater than 3. D5 otherwise put 0. This can also be said like this fetch the value from cell D5 if both of the following conditions are met if difference between todays date and the date in cell C5 is less than or equal to 6. ANDif the difference between todays date and the date in cell C5 is greater than 3. Once the formula is in place press Enter and double click or drag the fill handle to apply the formula to rest of the invoices. Step 4 Next is 6. Following is the formula that you will put in cell I2. Press Enter and drag the fill handle to populate the column with formula IFANDTODAY C5lt 9. TODAY C5 6. D5,0Step 5 Under column 9.