There are many instances where numbers refuse to cooperate and add up to the correct penny. It can be a very frustrating experience when you are trying to create a file that can be validated and audited. Validating numbers that do not add up right is always a test.
The first thing you must do is identify why the numbers are not adding up correctly. Is it a data issue? Associated with a particular location? Rounding issues that impact the equations? Incomplete information? Incompatible data? There are any number of reasons that it won’t happen. If you cannot identify the reasons why the data is not adding you will never be able to convince someone else of the issue. Showing the causes of the error is step one.
After you show how the error was introduced you need to show the error inherent in the rest of the data you are showing. Should someone take the numbers at face value or are they at +/- some percent from what is shown? Once you convince people of the overall issues you then need to then convince them that the data you are giving them is trustworthy. Proving the current data is step 2.
Step 3 is the hardest – communicating what is going on. This is where most people go wrong. I have seen many a good analyst fall apart in the face of communicating bad data. They try to distance themselves from it instead of owning the work they spent getting it as close to correct as possible. When data is not perfect people want to know that they can trust the person who put it together. If that person does not own it there is often no way to recover even if everything else is done right.
Confidence goes a long way whether in sales, data analytics or solution development. You need to convince people that you have confidence in what you put together as well as convince them that the basis for any recommendations or progress is reliable given a set of assumptions.