Anonymous
9

# Rounding in Management accounts pack - quick fix?

Looking to eliminate/reduce the manual processing time of solving rounding

• ### Emailing payment details to clients

Hi all,

Our management accounts packs are linked to a number of detailed disclosures, similar to annual financials. Does anyone have a way of automating the "true" rounding difference (i.e. difference of 1 that aren't arising due to use of pence). We have used =ROUND to get items down to 0 DP, but always spend time chasing £1-£2 round notes to ensure they agree throughout our packs.

Any help appreciated,

Thanks

### Replies (9)

By dejaneiro2005
19th Feb 2019 15:02

Rather than using Excel formula to round, keep the complete unrounded value throughout. Then present rounded numbers by changing the cell format to zero decimal places.

Thanks (0)
By Duggimon
19th Feb 2019 15:46

Per your example, £5.47 + £5.48 + £10.48 = £21.43 would be displayed as £5 + £5 + £10 = £21

That's the £1 rounding difference that presumably needs to be eliminated.

Thanks (0)
By Chris334
19th Feb 2019 16:00

Quote:

Per your example, £5.47 + £5.48 + £10.48 = £21.43 would be displayed as £5 + £5 + £10 = £21

That's the £1 rounding difference that presumably needs to be eliminated.

Correct, this is the issue. There must be an easier way of fixing these things than manually flying round tabs to find an answer :)!
Thanks (0)
By Duggimon
19th Feb 2019 16:07

Unfortunately I've never found one, you either hard round all the figures, so the totals change, or don't, so the totals are correct but look wrong.

Maybe there's some kind of Superman 3 solution where all the rounding errors go in to your bank account and everyone's happy.

Thanks (1)
By ArranP
21st Feb 2019 22:20

add another category to the list

Cat A £5 ( £5.47 )
Cat B £5 ( £5.48 )
Cat C £10 ( £10.48 )
Cat R £1 ( ( A+B+C ) - ( round (A) + round (B) + round(C) )
Tot £21 (£21.43 )

or add it it to one of the existing categories.

Cat A £1 ( £5.47 ) + £1 ( ( A+B+C ) - ( round (A) + round (B) + round(C) )
Cat B £5 ( £5.48 )
Cat C £10 ( £10.48 )
Tot £21 (£21.43 )

Thanks (0)
By atleastisoundknowledgable...
19th Feb 2019 16:51

I just + or - a few quid to purchases, travel or sundry ...

Thanks (0)
By paulwakefield1
19th Feb 2019 18:10

This is a perennial problem.

Outrageous suggestion: If they are just management accounts, does anybody care about the rounding differences apart from the terminally [***] (and all accountants of course)? You could even have a note saying that small rounding differences may exist.

Suggestion 2: I mention this with real timidity because it is by far and away the most dangerous function in Excel and should come with a "Here be dragons" warning. Use Precision as displayed. But be totally confident you understand the risks!

Suggestion 3: If this is a casting issue, use one item as a formula driven balancing figure. The risk is it could also sweep up a big error.

Thanks (1)
By paulwakefield1
19th Feb 2019 18:11

Ah - the censor has struck. Let's substitute "pedantic" for the asterisks.

Thanks (0)
By tom123
20th Feb 2019 08:19

I tend to base my reports around pivot tables - which helps consistency of data.

Format to currency with no pence, and leave things to it.

Thanks (0)