Count Back method for Debtor days calculation

[Former Member]

Hi Forum,

Has anybody implemented the 'count back' method for calculation of debtor days in BPC ? I would be interested to understand any experiences ?

Brian

[Former Member]

Tim, thanks very much for your response. Are you saying that we do not perform this calculation in BPC and create and excel model to do it and then input the answer back into BPC ?

This is for Actual data. Sales and trade debtors will be tracked at the lowest level. We need to aggregate these numbers up to a divisional level then perform the calculation. If you do calculate this in BPC how long would it take to develop this calc typically ?

We are thinking of doing this using MDX but the challenge is trying to make this dynamic I think (ie no hardcoding for months etc...)

Any more input would be welcome.

Brian

[Former Member]

I only meant to use Excel to model the calculation first -- once you have that agreed upon, it's probably best to then build the calc in BPC scripting logic. But the Excel model makes it easier to break down the complete requirement into discrete calculation steps.

The time it takes to do that, all depends on the details of the requirements, plus your level of familiarity with BPC scripting logic.

I would definitely avoid MDX, and use a combination of business rules & SQL script logic, if you're running BPC Microsoft.

In SQL script logic, to aggregate up to the divisional level (or whatever level you wish to calculate it at), you'll need some strategy for having base members in this dimension, which somehow represent the divisions, where you can store data. A down-and-dirty method for this is to choose the first base member under each division as the place you store the data. Or, you can create separate "division_base" members. Either way, you'll probably want to use member properties to reference each of the "real" base members to that one in which you store the division-level data.

If you get stuck on a particular calc requirement, post back here with what you've written so far, and what you're trying to do.

[Former Member]

I've done a few of these, and my first recommendation is to build the calculation model in Excel, to ensure that your users are fully in agreement on how the calculation needs to work. Be sure to account for every dimension of your application in that Excel model.

In your case, is this for actual data, or plan data? The calculation is reversed depending on which data is the input (sales or A/R or the days assumption) and which is the output.

Also, in a planning application, you need to consider what category of data provides the prior-year values for the periods leading up to the first month of the time periods that are being planned. E.g., if we're planning 2010 budget right now, where do we get the sales number for December 2009? For an actuals-only system, maybe this isn't a concern.

Finally, determine at what level(s) of your entity dimension (and any other dimensions where you're tracking sales & working capital) you will calculate this for. If it's always base members, that makes the calculation easier. But that may not meet the business requirement.