Abstract
When allocating overhead costs to products you often encounter the fact that the resulting sum of allocated overheads does not equal the original cost sum. Due to rounding differences you frequently face a little cent difference. In this case the user defined function RoundToSum can help.
A real-life Example
We present an allocation of overheads where all individual cent values accurately add up to their intermediate or final sums.
First you define how the overheads have to be allocated to support cost centres:
The first allocation of overheads uses a rounding correction so that all summands accurately sum up on support cost centre level:
The second allocation of overheads also uses a rounding correction so that all support cost centres get accurately distributed to products:
The final result:
This correct allocation of overheads you will be able to enter into a general ledger.
Examples of Use
MrExcel.com (External link!) Excel not subtracting properly causing un-rounding. Note: The final subtraction mentioned in this thread seems to suffer from a floating point glitch. But if RoundToSum had been applied during the calculation process as shown here this issue would not even exist.
ExcelForum.com (External link!) Correcting Rounding Error for Costs. Note: A simple round as suggested in this thread is generally not sufficient. RoundToSum should have been applied.
Download
Please read my Disclaimer.
Allocation_of_Overheads.xlsm [34 KB Excel file, open and use at your own risk]