Jump to content


Photo

Calculate a targeted DSO (Days sales outstanding)

Accounting pivot table

  • Please log in to reply
1 reply to this topic

#1 Stefanie

Stefanie

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 06 November 2016 - 09:38 AM

Hi,

 

First thank you for your support with this issue.

 

I have been asked to provide an AR Balance and Cash Collection Forecast based on targeted revenue. Eventually I will also have to calculate the so called "targeted DSO". DSO is an accounting term for how many days a sale stays unsettled. Days-Sales-Outstanding.

Example: If my sales volume per day is $100 and the total amount of my Accounts Receivables (AR Balance) is $4200 then my average payment term is 42 days = DSO

 

A general formula for the DSO is: Total Accounts Receivables / Annual Sales x 360 days.

 

I have been given the monthly targeted Sales for 2017 and need now to calculate 

- the possible monthly AR balance

- the cash collection

based on the different payment terms of the country in comparison with the payment history.

 

Some country have 30 days payment term and some other 60, 90, 120 not considering their payment behavior.

I would need to run an automate report that captures all these parameters and provides me

a.) an AR balance per month

b.) a cash collection per month

 

 

Example:

 

Country A has a payment term of 30 days (1 month). In January the targeted sales are $100 which generates an AR balance of $100 (not considering previous). In February the sales will be $150 which will bring the AR to $100 + $150 to $250. As the country has a payment term of 30 days country will pay the sales of January = $100 and so on = thus the DSO for January will be 30 days. Please see attachment

What formula should I use to simplify this calculation capturing the real amount of payment term days?

 

Hope you can help me.

 

Thank you Stefanie 

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 07 November 2016 - 11:55 AM

Hi Stefanie,

 

The first thing I would suggest is rearranging your data into a layout that will be easier to apply formulas and pivot tables to. The layout you have currently is more of a summarized layout that you would expect to generate from a table of data, rather than a table of data that you would generate a summary from.

 

Here's how it looks:

 

DSO1.png

 

Now that the data has been rearranged, it's much easier to apply formulas to it. I don't think I completely understand the formula that you're trying to create. You mentioned the formula Total Accounts Receivables / Annual Sales x 360 days, but I don't understand how the Payment Term applies to this.

 

I also don't understand how the AR is being calculated or what the meaning of the Cash and Sales columns are. It looks like Sales is a running total, but it doesn't seem to add up correctly if that's the case. I also don't understand where the initial values are coming from if this is the case.

 

If you are trying to take the sales figures and create a summary by month, a pivot table is probably the best route. Pivot tables can very easily take data of this kind and summarize it by month or by country.

 

You can see how to create a pivot table in the video lesson: Expert Skills Lesson 5-1 Create a pivot table.

 

I have attached a copy of your workbook with the data rearranged and a pivot table added showing sales by month:

 

Attached File  Example AR and Cash calculation 2.xlsx   15.15KB   64 downloads

DSO2.png

 

I'm sorry I couldn't be more helpful, but I hope this is a good starting point. If you can clarify what you're trying to achieve a bit more I am sure I can be more helpful, so please feel free to reply.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users