During a board meeting I volunteer to attend, the treasurer presented financial projections that showed our fund balance would grow from under $2M to over $7M in 5 years. People were excited and pleasantly surprised by the large growth. I was skeptical. There were sheets full of calculations, but I didn’t need to understand each cell to know the calculations required a deeper look.

About 10 minutes later, I found the error. The treasurer had added the principal and earnings from each year to the principal rather than just adding earnings. Instead of growing by ~$5M, our fund balance would grow by ~$500K.

It’s easy to make mistakes when working through complicated calculations involving many assumptions and different formulas. I spent the first few years of my consulting career wrestling with spreadsheets with too many sheets to be able to see the titles.

How do we do detailed, complex work without making simple mistakes? We must see the forest and the trees. We have to zoom in and get the detailed calculations and assumptions right, and then zoom out and place the results in a broader context.

Seeing the Trees

When it comes to getting the details right, we can use 3 tools:

Build in error checks: In most complicated quantitative analyses, there are multiple ways to get to the same number. For example, if you sum the column and the row in a table, you should generally get the same response. Or if you calculate a number using a formula and then also sum the numbers that make it up, you should get the same answer. Running the calculation both ways and comparing the answers is a way to error check your spreadsheet.

You can also do this when calculations should add up to 100%. Look for creative ways to build error checks into your calculations.

Use completion checklists: When calculating a number requires 15 steps, it can be hard to check your work without doubting that you missed a mistake. This is where a completion checklist – a list of tasks you must do to consider your work reviewed – can help.

Surgeon Atul Gawande explains the power of checklists to help with the most complicated tasks in The Checklist Manifesto. Build a completion checklist for your calculations and then run through every task on the list before considering it ready for sharing.

Avoid manual data entry: If your plan is to re-enter formulas into multiple cells or manually enter numbers into your spreadsheet, pause. You can copy and paste values or formulas from any cell to any other range of cells. You can also drag formulas from one cell to the next using the bottom right corner of the cell – or use keyboard shortcuts like CTRL+D (fill down) or CTRL+R (fill right).

If you have the data in a pdf, use free online PDF to Excel converters like ilovepdf.com or Adobe’s online services. These work pretty well for simple tables. If you have your data in a document or email and pasting it puts everything in one cell or one column of cells, use Excel’s text to columns feature to split out values from one cell into a table.

Using these 3 tools can help you avoid detailed mistakes by reducing the opportunity to make mistakes and increasing the rigor of your reviews of your own work.

Seeing the Forest

Seeing the forest means putting the number in the context of a bigger picture. It can be done quickly without spiraling into the depths of spreadsheet. The other day I was in a conversation where someone said our county brings in $20 billion in property taxes. This triggered alarm bells.

I know our county budget is only $710 million, which made this immediately impossible. But I also remembered that the state of California’s budget was in the $200 billion range. We are 1 of 58 counties and in the smallest third. Could just our property taxes be 10% of the state’s full budget? Not a chance. The person had mistaken a “b” for a “m”. Our property taxes are in the $20 million range.

In this example, I put their number in the context of the County and State budgets – and could immediately see they were wrong.

In the investment example from earlier, I put the returns in the context of the Rule of 72, which says 72 divided by the rate of return equals the number of years it takes to double your investment. The Rule of 72 predicts it would take 14.4 years to double your investment with an annual return of 5%. The Board treasurer suggested our investment would more than triple in 5 years. Impossible.

The key to putting the number in context is finding a benchmark. Here are 4 ways you can find benchmarks:

Internal reference: You can use past performance as a benchmark to catch mistakes. If your Sales Director projects that sales will grow by 8% this year, but they have only grown by 4% annually for the last 5 years, she better offer a good explanation for how she’ll double sales growth this year.

External reference: Sometimes your best bet is to look outside your company or experience for a benchmark. For example, if your Human Resources Director wants to offer a brand-new college graduate an entry-level job in your division for $48/hour, you might compare it to your state’s minimum wage. The offer is 3 times higher than California’s minimum wage, making it a generous offer depending on industry.

Expand Out: In other cases, you need to manipulate the number to get a benchmark that makes sense. To expand out, multiply the number in question by another number to get to a figure you can easily compare. In the hiring example above, calculate the annual pay for the $48 hourly wage: $48/hour * 2,080 hours = $99,840/year. You know your frontline managers make $100,000, so this would be way too high.

Consider another example. Your Division Manager recommends giving his 125 people a 5% raise. You know that the average compensation in his division is $75,000, making this a $468,750 increase in costs. Last year, the division had a net profit of $500,000. Without an increase in profitability, this raise would eliminate almost all of the division’s profit.

Drill Down: Rather than expanding out, you can drill down, typically to find a rate that is easier to benchmark. For example, imagine a friend tells you they ran a marathon last weekend in 1 hour and 30 minutes. You don’t know if that is a good time, but you know how fast people can run a mile. In this case, you calculate the per mile pace: 1:30 hours / 26.2 miles = 3:26 minutes/mile. You know the fastest mile time is around 4 minutes per mile, so you call your friend’s bluff.

Here’s another example: Your Production Manager projects they’ll produce 2,000 head gaskets next month. That is 100 gaskets per day. You know that the most you’ve produced in day is 80 gaskets, so this seems unlikely.

For each of these examples, there could be a detailed spreadsheet to back up the individual’s recommendation. But seeing the forest enables you to skip the spreadsheet and sense-check their numbers with simple mental math you can do in less than 5 minutes. This math won’t get you to the right answer, but it will keep you and those you work with from sharing answers that are clearly wrong.

Avoiding Mistakes

It’s important we catch mistakes – particularly egregious ones – before they impact our organizations. Part of the way we do this is by seeing the trees – getting the details right – by building in error checks, using completion checklists, and avoiding manual data entry. The other way we do this is by seeing the forest – putting the numbers in a broader context – whether that be by comparing them to an internal, external, expanded, or drilled-down benchmark.

Practice these behaviors routinely and they will become automatic. You’ll discover mistakes without intentionally looking for them and you’ll save your organizations money and hardship.