Why are so many accountants so bad at building financial models? You wouldn’t be too impressed if a brick layer couldn’t use a trowel; its not enough for them to know what a good wall looks like, they have to be able to build it efficiently. So why do some accountants struggle so much with financial modelling?
Perhaps you’re reading this and you’re not an accountant and you’ve built some questionable financial models of your own for your business. Well you’re excused from this rant, after all it might take an accountant a long time to build a questionable wall.
Financial models range from incredibly simple to highly complex. I want to talk about the everyday financial models that businesses use. Every company I have worked with, without fail, no matter what the size, has had spreadsheets that have become unwieldy, inefficient, inaccurate, or not understood. Why does this happen?
- Turnover of staff – everyone adds their little bit.
- Someone asks for a bit more information, so we add something in.
- Processes are not documented.
- Built by people that lack experience.
- Manual inputs.
- Hidden rows and columns.
- Links to external documents that nobody has ever looked at fixing.
- Too many tabs.
- Someone was in a rush.
- The spreadsheet has always been done this way.
- That’s how I was told to do it.
I think every accountant can resonate, and every non-finance person, manager, business owner has probably at some point thought, “why does it take so long to get that number?”.
When these sorts of issues are embedded in the day-to-day tasks of a businesses processes, its highly likely they are losing a significant number of team hours managing them. And the worst point is you might still be lacking any confidence of accuracy. Your team are probably also maxed out with managing it all, so kiss goodbye to the bigger objectives you want to achieve.
Here are a few tips to building financial models that I try to stick to that could help your business.
What are your inputs and your key sources of data. Make sure these can be dropped in easily from source and try and confine hard coded inputs to one designated area of your model.
- Stop hiding columns and rows. Please stop! Group them instead.
- Beware linking to separate files. If you don’t keep tight control of your links, you run the risk of inaccuracy.
- Build in checks and balances to make life easier. If these are built in, imbalances and errors become quick to spot and to resolve.
- VLOOKUP is not enough excel knowledge to build financial models. If this is your go-to formula, seek training or support.
- Try to keep formulas simple. Not always possible but adding extra columns or rows to break out the formulas can help readability.
- Think logically about the steps between your raw data/inputs and your final outputs. There is normally one or two steps of interpreting your raw data, then calculating the required output. Plan these steps clearly and make the steps simple to follow.
- Freeze your top rows and your left columns. I can’t remember every column heading so I don’t want to have to keep scrolling up to check. Just freeze them.
This obviously doesn’t give much insight; at worst its just a rant, but hopefully if you can take on any of these thoughts it might just help you improve your modelling. A bit.
If any of this resonates in your business and you need a bit of extra support improving it, feel free to reach out.