March 18, 2012 at 10:46 pm
Comments posted to this topic are about the item Financial Rounding of Allocations
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 19, 2012 at 4:56 am
So, er, is that not fraud...?
:pinch:
March 19, 2012 at 6:56 am
If I understand the writeup correctly, the portion of the total that is not allocated to one of the contributing amounts is then allocated to just one of the records. If this is what is being done, it seems incorrect to me. I have seen plenty of situations where the unallocated portion is large enough that it is necessary to allocate the remainder across several rows, a penny at a time. Whether a cursor is needed for proper allocation or not, I do not know. However, one allocation method I find that yields good results is as follows:
For a set of records and an amount to be allocated.
Sum the records. Allocate to the first record (record amount/total_amount)*(amount_to_be_allocated) rounded to two decimal places.
Subtract the record amount from the total_amount. Subtract the amount allocated to the first record from amount_to_be_allocated.
Process each row in this manner.
On reaching the last row, the allocation is the remaining amount_to_be_allocated.
March 19, 2012 at 8:20 am
Thanks for sharing!
I was on a project where we needed to institue "penny rounding" in our detail tables so that the sum of the details in an invoicing detail report was consistent with various subtotals and grand totals. I specified how the rounding was to occur using Excel and pretty much followed the general approach you described in your article (do the "normal" math, sum the details, find the delta compared to the expected amount, and then apply the delta to the largest record). The developer I was working with created a stp to handle based on what I did in Excel, using the "messy" version that you described.
In a different context, where the delta was deemed significant, we also had a requirement to evenly "sprinkle" the delta across all items that were getting the allocation... IIRC I think that I only did that in Excel and we never implemented a program for that.
March 19, 2012 at 8:46 am
Awesome and just in time for the project I am working on. Thank you for a clever article!
March 19, 2012 at 12:30 pm
I would be inclined to simply divide each alloc by SumOfAlloc and multiply by 100 to get a proper allocation which also adds up to the total (as long as you use a suitably precise datatype for the division).
Much simpler and can be done in a single query if you want to.
Also, dosn't allocate to zeros and spreads the unallocated amount in proportion to the existing allocations.
March 19, 2012 at 12:33 pm
The problem is that, in many cases, you have to round to the nearest penny. This leads to the penny rounding problem.
March 19, 2012 at 6:11 pm
Yes, of course it is fraud. There is no defendable justification for the fudging (other than to cater for people who don't understand rounding).
Nice article from the "how to commit fraud if you really have to" perspective though.
March 19, 2012 at 6:29 pm
Where is the fraud in penny rounding as long as it is done properly?
March 19, 2012 at 6:44 pm
Thanks to all for your comments! To implement penny rounding, you simply need to change the zero (second argument of ROUND) of the "a" CTE to 2:
a AS (
SELECT d.key1, d.key2, d.Weight, d.SeqNo
,ROUND(CAST(Amount * d.Weight / w.Weight AS Money), 0) As AllocAmt
FROM d
INNER JOIN @hdr h ON h.key1 = d.key1
INNER JOIN w ON h.key1 = w.key1)
As Tom-John and Janus have noted, there are other approaches. A couple of key thoughts to keep in mind:
1. Mostly this will be used where the amounts being allocated are typically in the 1000s or 100,000s of dollars. If this allocation is performed over 100 rows, the maximum "leftover" will be 99, so the "fudged" amount represents generally less than 1% of the amount alllocated.
2. Because roughly 50% of the rows will round down and 50% will round up, in practice there will rarely be (except in contrived cases) where the leftover amount is significant.
I'm not saying this is the only way to fudge round, only that it worked for us in practice.
As to fraud, I can honestly say that I've never passed an accounting course so I'm no expert. All I know is that our accountants seemed to accept it. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 19, 2012 at 6:48 pm
The fraud isn't in the initial rounding of the details but in fudging those roundings to make the rounded details add up to the totals.
For example 100 divided by 3 is 33 1/3 (3 times). If we round to integers then these 3 values should all be represented as 33. Making one of the 33s a 34 is fraud.
March 19, 2012 at 6:52 pm
It depends on the rounding situation. I deal with situations where the following is possible:
An invoice has 100 line items of .01 each. The total is subject to VAT at 19%. There is also a requirement to produce a supporting spreadsheet of each line time with the tax of .19 allocated to each contributing row. Since you have to deal in whole pennies, 19 of the 100 line items will have a penny of tax and the rest won't. It isn't fraud. It is just math.
March 19, 2012 at 6:53 pm
"As to fraud, I can honestly say that I've never passed an accounting course so I'm no expert"
You do not have to be. Deliberately changing a number to another, incorrect value is fraud. Perhaps not legally in the sense that accountants can get away with it, but in every scientific context it would be considered so.
March 19, 2012 at 6:56 pm
If the allocation method and approach for treating unallocated remainders is transparent, how is it fraud. Again, this is a rounding problem where the sum of the allocated amounts must equal the orginal amount to be allocated.
March 19, 2012 at 7:01 pm
You do not have to be. Deliberately changing a number to another, incorrect value is fraud. Perhaps not legally in the sense that accountants can get away with it, but in every scientific context it would be considered so.
So in that case if you allocate 100 over 3 rows, each weighted at 33% and store the information in a field with 0 decimals, the resultant amount (33) is not 33.33 and thus has been changed. So is this fraud? Or in the case where you store in 2 decimals so the amount is 33.33, when in fact it should be 33.333333333. Is this fraud?
Fraud is technically a legal term and I'd be surprised if this approach violates GAAP. Scientifically it is another matter, so in that case store your data in FLOAT format. Of course, do that in accounting and incur the wrath of the accountants that will be checking the subtotals. They've got to make a living too. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply