DSO and DBO Calculation

[Former Member]

Has anyone by any chance got any suggestions or examples they would be happy to share about the best way to calculate DBO (Days Billing Outstanding) or DSO (Days Sales Outstanding) using the exhaustion method?

Much appreciated...

[Former Member]

First, make sure you agree with the client on precisely how they want to calculate DSO. Build the calc model in Excel, and confirm the precise math requirements.

If you application includes plan categories of data, be sure to go through all the scenarios of which category is the "opening category" -- for example, the January 2009 budget category uses Jan 2009 budget sales, but also needs to look back to December 2008, if the A/R is greater than the sales. So, in which category of data do you look for the Dec 2008 sales? You'll likely need some special case handling for this.

Plus, you need to set up working days or accounting days per month, if the calc needs to be accurate. (If they're OK with an assumption of 30 days in each period, that makes it a bit easier.)

And if you can hold the client to a rule such as "the DSO will never be more than 60 days" this means you can build the logic so that it only has to look back in the current month & one month prior. Or if they say 90 days, then 2 months prior. If they say 180 days, ooohhh.... maybe time to find another client! But seriously, you don't want to build the logic to do extra work unless it has to, and you will need to build those loops for each extra month it needs to look back.

I always build this type of calc using SQL logic. I'd recommend using some intermediate staging accounts, so you can trouble-shoot along the way. For example, I could imagine using an account called ratio of current-month AR to current-month sales. If it's less than 1, then my DSO is simply [that percentage] * [number of days in the month. If it's greater than 1, then it's [number of days in the month] + [some more complex stuff having to do with the prior month's sales].

If you post the specific requirements that you need to implement, perhaps we can give a specific example.

Regards,

Tim

[Former Member]

..I want to add that this is in BPC 5.1 SP1 and we are trying MDX in the account dimension but the formula is just too long!