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:

allocation_of_overheads1_keys

The first allocation of overheads uses a rounding correction so that all summands accurately sum up on support cost centre level:

allocation_of_overheads1_result

The second allocation of overheads also uses a rounding correction so that all support cost centres get accurately distributed to products:

allocation_of_overheads2_keys

The final result:

allocation_of_overheads2_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]