Harness The Power of Excel 05 – Dastardly Rounding Errors

Back (almost to schedule) to present my fifth article in my role as Expert Excel Tamer Paula Turk.

A short topic this month and here we answer the question of rounding errors within Excel. We cover off with a simple example how 3+3+4 can equal 9, 10 or 11 according to the levels of rounding within each individual number.

INTRODUCTION

The most common source of frustration and a lack of credibility for senior managers is related to rounding errors in Executive summary reports.

Clear cut explanations of rounding errors is one of the most overlooked and sought after areas of corporate finance expertise.

Data is combined at many levels – and at some point, you will get rounding errors.

At some point your rounded cost lines and rounded revenue lines may not add up to the rounded profit line.

A WORKING EXAMPLE

The Mental Maths of it All

Mental maths ability is an incredibly highly valued skill. Being able to add a series of up to twenty numbers in your head allows you to ring off a stream of numbers and check for roundings. And it is most definitely a learnable skill.

With number puzzles like Sudoku and numerous other training programmes, mental maths agility is definitely a learnable trait.

Let’s Add it up - The Mystic Arts of Roundings

Here, I will present two sequences of numbers and you decide on whether they are thousands or millions or something in between.

Set 1:

2.6 + 2.6 + 3.8 = 9.0

3 + 3 + 4 = 10.0 (All items rounded UP)

Set 2:

3.4 + 3.4 + 4.2 = 11.0

3 + 3 + 4 = 10.0 (All items rounded DOWN)

In this very simple example, we can get the true answers of 9.0 and 11.0 to both show as 10.0.

Those of us with a flair for maths can quickly work out why we have differences - others may not be able to over-ride the fact that the numbers do not appear to add up.

HOW TO MINIMISE ROUNDINGS?

As you know, I always like to present solutions to the problems that we face in using Excel.

A key to keeping the executives happy is for your senior management team to have high trust and credibility when presenting the numbers.

In most organizations there will be a large set of rounding calculations going on. The strange paradox is that the more numbers you have the easier it is to deal with rounding errors.

Maths Agility

Being able to anticipate roundings is key in ensuring they don't appear. The trick is in knowing (instantaneously) where the dastardly 0.5 comes in and how to get around it.

Also, another crucial skill is in knowing how to create whole numbers where (say) 0.9 and 0.1 or 0.8 and 0.2 add up conveniently to 1.0 in both cases.

Accepting Roundings

This is more a cultural shift and not expecting every set of rounded numbers to fully add up. This has always been the challenge.

One way is to have the un-rounded numbers appear in a separate analysis or be at the foot of the high level presentation.

SUMMARY

Excel is a great tool, however rounding errors need careful management.

Without a holistic view as to where the numbers come from, as well as the audience that they are going to, you cannot do proper justice in identifying rounding errors.

Concentrate on identifying existing finance team members that have the mental agility to quickly review high level reports.

To solve the problem I presented, determine which variables need to be fixed and which are flexible...

So, in the first example, one of the 2.6 values will have to be rounded down to 2.4.

In the second example, one of the 3.4 variables will have to be rounded up to 3.6.

Only minor changes and yet it allows the rounded sequences to add up to that as presented in the detailed sequences.

And that is all for now from the mystical arts of roundings!

?????? ?????? ???????? ???? ?????????? ?????? ???? ?????????? ????????

?????????? ???????????? ?????? ???????? ??????????

1. Whether you run a large private or public enterprise, or operate in a small start-up, you can optimize your use of Excel. ?By using the tips provided in this series, I will save you time and save you money, plus reduce team frustration. All of which all leads to vastly increased profits.
2. Ask how I can help your team to introduce best practices from the ground up, create better reports and models, reduce the dangers of data misunderstandings, and generate huge savings of both time and money.
3. With a love of maths, maths, maths, and more maths I'm always keen to get my head around your numbers and understand your data. Plus, I'm extrovert too - very much the talk of the office and a great team-mate. Great for building up inter-departmental synergies.
4. Putting some fun into business. It's all very serious - and the numbers tell everything. And yet how we choose to deliver potentially bad news – be it with an open presence or a closed presence – determines hugely how quickly solutions can be developed and used.

?

• https://www.dhirubhai.net/in/paula-turk/
• ?? UK 07773 368195
• ?? INT +44 (0) 7773 368195
• [email protected]

?

?? Paula Turk 2024 - All Rights Reserved