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
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