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