1️⃣ Use check-sums

Your reputation is built over years and ruined in seconds. For example, a presentation to the board of directors with wrong numbers can be a big hit to your career. So, you need to have a way to check your work in Excel before you press SEND.

Check-sums are formulas that calculate results differently and compare that to what’s shown in the table. If it doesn’t match, there is likely a mistake somewhere.

2️⃣ Separate inputs from outputs

If you mix cells that contain data copied from somewhere else (such as from an ERP system) with cells that have formulas, mistakes are bound to happen. Say you are approaching an important deadline, and - unexpectedly - your business partners gave you a new assumption. To avoid issues, I’d recommend having all your input cells in a separate tab. That way you minimize the risk of accidentally breaking a formula.

3️⃣ Use Index/Match or X-lookup instead of V-lookup

V-lookup has some big disadvantages. For example, if a column gets added to your source table, your formula may show the wrong result. And it won’t even give you an error message! That’s because V-lookup is a static cell reference. Index/match and X-lookup are dynamic, so they adjust automatically if there is a change in the source.

4️⃣ Avoid complicated formulas

Your Excel models need to accomplish two things:

You need to be able to make changes as quickly as possible so that you can answer questions on the spot.
Someone else needs to be able to take it over when you are sick or otherwise unable to respond.

If you have nested IF() statements all over the place, achieving both objectives gets more difficult. Always think about the easiest way to get to the desired results. For example, multiple helper columns may be easier to navigate than a single long formula.

5️⃣ Rely on macros only when necessary

Macros can be a big time-saver, for example, when you have to consolidate multiple reports, and you don’t have a sound FP&A system to rely on. But they also break easily because they aren’t dynamic. And they are difficult to understand for anyone who wasn’t involved in creating them. Few people know how to read VBA code. So try to use them only when there is no other way.

6️⃣ Frequently save under a new name

There is nothing worse than finally finishing a big Excel model just to realize that you must have made a mistake a few days ago. It’s best practice to save the model under a new name, whenever you make a big update. That way it’s easy to “roll back” the last version that was error-free and take it from there.

7️⃣ Document assumptions

Even the best Excel model fails the test of time if no one understands where the assumptions came from after a few weeks. Always add a tab that lists the most important inputs (you can link to them) and states:

a) Who provided the assumption

b) When was it last updated

c) Which facts or data points is it based on

If you’d like to learn more about FP&A, I offer help in three ways:

1️⃣ Subscribe to my free newsletter “FP&A Tuesday” here.

2️⃣ Listen to 75 bite-sized mini lessons and get FP&A advice on the go.

3️⃣ Join my live online course FP&A Bootcamp to master FP&A in two weeks.

The 7 Excel habits of highly successful FP&A Analysts