Changing allocation basis in midstream

[clmish]

We allocate expenses among business units (BU's) on the basis of a non-monetary measure and now I want to change to a monetary input. The method up to now has taken inputs and calculated a rolling 12-month avg and then compute that avg's proportion of the sum of all these avgs from all BU's. It works, but now I need to change the input unit of measure (U/M) from non-financial to financial. The effect is like changing denominators in that one U/M runs in the thousands, the other in the millions. I can't run 12-month rolling averages that include periods with these different inputs.

The "solution" we came up with was to calculate the proportion the input per business unit is of the whole on a monthly basis, then figure the 12-month rolling average of those percentages. This apparently nifty solution created some differences when compared to the previous method when applied to the same inputs, but the difference seemed immaterial. The flaws in this process came when we introduced more extreme inputs in test data. We saw that the values of the allocation percentages were quite different when comparing the two methods, the direct and indirect. The "Ratios First" way was much less sensitive to changes in inputs.
Is there a way to change the U/M that yields close to the same values as the original? See attached file for sample.

 

[tkhunny]

You may wish to "Normalize".

Find the last 12 rolling averages from your U/M and compare them to your BU measures. Surely there is a rational relationship, otherwise you would not be considering switching. Let's see the data and your efforts to normalize. It will be fun.

 

[clmish]

The industry I'm working is highly-regulated, so changing the allocation basis is actually subject to an application for change. Even changing the method of averaging is scrutinized carefully. That being said, the controller wants us to go back to the previous method of summing the inputs and using them directly to compute the share of the allocation for each business.

However, this decision is just for the interim. When the U/M is changed, we still need to deal with converting to a new one. What we've done before is have an increasing number of periods each month in the divisor, so at month 1, the "average" is the input divided by one, in the 2nd month, we divide by 2, etc., until at 12 months, we are back to a rolling 12-month average. In other words, we would not mix the inputs together in the averages.

There is some rational relationship between the two kinds of inputs, but it is disproportionate between the BU's, such that the allocation of costs would shift fairly dramatically from some of the units to one other one. We can't convert the units, in other words, since that would also constitute a change in the methodology.

Thanks for the input. I just figured we're dealing with a math issue here of when the average is calculated, and the normalization issue is forefront here.
clmish

 

[tkhunny]

My guess is that without a far better understanding of exactly what it is you are doing, you are unlikely to obtain much useful guidance. When your information is less proprietary, let us know.

 

[clmish]

You are correct. I understand the limitations. I was hoping there might be some general input about the use of averaging between "unlike terms" - that it shouldn't be done, basically, without some common factor between them (to convert the one to the other, like a least common denominator).
Well, thanks for the input. I've been told to focus on other things for a while anyway, so I'll probably come back to the theory of the best way to handle these data later.

 

[Denis]

We saw that the values of the allocation percentages were quite different when comparing the two methods, the direct and indirect. The "Ratios First" way was much less sensitive to changes in inputs.
Is there a way to change the U/M that yields close to the same values as the original?

I must be missing something...usually, when a "new method" is introduced, the purpose is to correct/improve
the method being replaced; if you're looking for your new method to produce SAME results, then why change?

 

[tkhunny]

Planned obsolescence?

 

[clmish]

We saw that the values of the allocation percentages were quite different when comparing the two methods, the direct and indirect. The "Ratios First" way was much less sensitive to changes in inputs.
Is there a way to change the U/M that yields close to the same values as the original?

I must be missing something...usually, when a "new method" is introduced, the purpose is to correct/improve
the method being replaced; if you're looking for your new method to produce SAME results, then why change?

Good point. The management powers-that-be have reasons of why the allocation basis should be changed involving the inclusion of non-operating business units which may otherwise be under-represented in expense allocation. So, there is a good reason to make the change. My quest is to find a way to change the underlying basis without introducing a logical error or making a "correlation equals causation" type of leap. We have also gone on record as using a 12-month rolling average of the input data, so I was looking for a way to be able to keep that aspect of the process. One accounting manager thinks we should still be able to convert the data to what we call "factors" (proportions of the business unit's input value to the sum of the like inputs for all the business units), and then take the average of those percentages. Then the underlying basis would not seem to matter. I don't think this logic is correct, however.
We do have a precedent for dealing with "new" inputs or new business units - either "backfill" data in an offline spreadsheet and start using the rolling average at the effective date, or have a gradually increasing number of periods in the averaging formula (from 1 month up to 12, eventually).
Hopefully that clarifies a bit. clmish