Using split and splat with integers ?

Etienne CAUSSE

Hi all,

I am currently facing a very delicate problem with a budget application I'm working on.

The objective of this app, in line with Board key features, is to use split and splat functionality and previous year data to provide a detailed budget file (by SKU, Sales Force and geography) that will then be uploaded to another system.

We met a big issue recently, which is the result of several factors:

• the target system expects integers for quantities, and Cost of Sales is recalculated  from this rounded quantity
• in my Board app, I keep "floating" quantities to make split & splat easier, then I apply a rounding function at the very end of the process.
• In a specific company, we found out that the result of the simulation ended in too many quantities below 1. Therefore the rounding effects were much more significant than planned.

 

So I have a general question as I am sure I am not the only one with this issue : how do you manage your process when you want integer quantities as a result of a multi-dimensional split & splat process ?

 

Thanks for your contributions

Etienne

Unknown

So, we have to first accept that split and splat cannot natively solve this challenge. It sounds like you’ve already added a process to reconcile some boundary cases, which works for most of the data. Now, you’ve found another boundary case that does not correct for the rounding error inthe desired manner. I would add a second process to do a pass on the same data, making a second check. Rounding can be very tricky. Comparing the values to YTD rounded, annual rounded or prior period rounded values may help you correct these remaining errors. 

 

Since split and splat are not the challenge, the challenge is how do you want to reconcile the errors introduced by rounding to integers. 

 

Good luck

Etienne CAUSSE

Hi Bob Gill

Thanks for your input. Indeed I agree split & splat cannot natively solve this and we have to live with that.
I'm interested in various methods to compensate. You mentioned comparing the values to YTD rounded, annual rounded or prior period rounded values: how do you implement that in practice ? In correction dataflows ? With interactive screens and specific algorithms to show to the user the gaps ?

 

Etienne

Unknown

Hi Etienne CAUSSE,

   Good question! Let me describe my typical scenarios... Many large companies report their financials in thousands or millions as part of regulatory reporting requirements. This rounding introduces error. It also happens when performing currency translations for financial reporting, which may also be reported as rounded. Consider the following tables. The first shows values and calculated values. The second shows the formulae.

 

 

  Looking at the above data, you'll see each revenue stream rounds to 50, but rounding the total produces a different result than rounding each item and summing them up. Typically, I don't give users the opportunity to select how to fix rounding errors, so it will be handled by a process (run on clicking a button) or algorithm. 

 

  Here are some recommendations when considering how to deal with rounding errors:

• If we want subtotals to line up with the elements above, round the elements first, and then subtotal the rounded values.
• If we want to report information in other currencies, sum the YTD value first, then round it. If you need to calculate period activity, subtract the prior period YTD rounded value. That is inline with most accounting practices, and ensures the rounding error is applied to the period closest to when it is introduced

 

In the original question, Etienne CAUSSE is asking about spreading to integers. The rounding error is the same, but in the reverse direction. With split and splat, we enter the aggregate value and want to allocate it to items so it sums to the value we entered, with the added complication that each child must be a whole number. Before adding my comments, I should note that this may not be expected by users. Ideally, when a user enters 151, it should be equally spread, but with integers, we do not want that. That introduces the same rounding error. If I need to allocate user input across leaf level entities, I first save the user data to a cube at the grain of user input, the use a process to allocate it out to the leaves. This might require several steps in the allocation to correctly balance or correct the rounding error.

Björn Reuber

Hi,

 

some years ago we had a similar challange (we needed an Integer allocation to weeks, cause only "full" pieces can be procuded and it's not possible to  have 1/3 of a piece in a week).

 

Without starting a deep analysis phase I would solve the problem having different cubes, procedures.

Cube A: Single/double for DataEntry

Cube B: Single/double having the rounded result

Cube C: Integer (higher planning level): containing the "real" number of pieces (C=A)

 

Than you can compare cube B and C (on a certain level, which needed to be defined), here you can get an Adjustment Factor D (Integer, D= C-B). These numbers than you have to allocate to a different element

 

For some "rounding"/conversion Problem from floating point to int I sometimes use this example:

 

If you only have the collumns without DataEntry/decimal digits you would assume that board is calculating in a wring way, cause 8*0=4 and 8*1=5, so you have to be careful, about the roudning effect, cause it can be sometimes hard to understand for the customer, what is the real problem

 

 

For the allocating on integer you will get a different problem. For example if you have a simple case:

Parent Element with 3 Child elements. Every child = 1, so the parent is 3. if you now change the parent element to 4, to which child element you want to add the additional figure?

 

Regards

Björn