By Ian Bennett FCA

We’ve all done something difficult once and found it a useful experience, but one we wouldn’t want to repeat. In financial modelling, this might push us to develop a template model.

A great template is a pre-built financial model that contains most of the IP and calculations you could ever need. Its secret is simplicity. It may be an investment template that takes a relatively small number of inputs and produces beautiful, consistent and easy to understand dashboards for investment committees. Even though lots of work is required to prepare the inputs, and these are unique to each deal, you could hope to save many hours in the future when you need to prepare, review and interpret each investment.

This sounds logical but in reality, you’re unlikely to get the return you’re seeking.

Templates promise huge time savings

Changing an established model is very risky. This is where we find most errors when performing model audits. So, to avoid making changes the world’s best modellers follow an approach that means they build the model right first time. This requires doggedly chasing down and removing ambiguity, and to document the model with utter clarity in a specification. Get this right and you’re on the home straight before you put your fingers on the keyboard.

But this takes time. It would usually take a couple of weeks, but for a template model it will take much longer. You need to consult with everyone, document, clarify, and document and clarify again. I once spent a week reaching agreement with parties from around the world on how a client’s global fund would calculate the cash-on-cash multiple. 

You get the idea.

Every template needs updates

At the outset, you plan for the template to work without amendment nine times out of 10. By the time you’ve finished the specification, you hope for seven out of 10, and by the time the build and testing is finished it’s closer to five times. And it’s become the most complex model anyone has ever seen. A month later your company’s strategy/regulation/systems change, and you have to amend the template almost every time you use it. So now you spend your life making changes to the world’s most complex model, desperately trying to remember why the model does what it does. And yes, it’s always you doing it, because no one else understands the model and making changes is very risky!

An error can easily arise, for instance, where you need to add a new revenue line. Knowing every place in the model that must be tweaked is very hard, but even if you do, silly errors are common. If you add a new row above a subtotal but fail to update the subtotal, for instance, the new line doesn’t get included in the SUM.

The learning experience

Financial modellers are natural and enthusiastic problem-solvers. Building the perfect model for the first leveraged buyout deal is a real thrill and the experience gained is enormous. Developing a template model is even better. You have to consult much more widely, ask new questions, and understand every possible permutation of what might happen to your business in the future. If you develop that template, you have the substance of the business in your head and the learning experience can’t be underestimated.

Anybody who follows you and uses your template model misses out on both of those experiences. In fact, it’s worse than that: those people won’t take ownership of someone else’s template and they will likely blame your template when it’s wrong. They will treat it like a black box. A good template should outlive you. More commonly, it will be shelved soon after you leave. To you it’s a great tool; to everyone else, it’s “Pete’s complex template”.

Snowballing templates

Even worse than a cumbersome template is an ex-cumbersome template that is now a model that is rolled over from deal to deal to deal, growing like a snowball.

And this is all before we get to dynamic templates, in Excel and other technologies, that use application interfaces and libraries to build a new model. (These endeavours deserve a separate article.)

Starting from scratch 

Financial modeller Dan Mayoh helps design financial modelling’s world championships, ModelOff. He doesn’t advocate the use of templates beyond a pre-formatted workbook outline, unless he wrote the template himself and knows exactly how to modify it. “And even then, I’m not using so much a dedicated template as I am simply using parts of a past model as a starting point for small specific tasks,” he says. “Generally, I try to produce bespoke models for professional jobs.”

I couldn’t agree more. Our team at PwC Australia has built hundreds of models, so there are many previous models we could tailor for each new situation. But we always start from scratch, for two reasons:

* You have to understand the calculations if you do them yourself.

* No two situations are exactly the same. You always have to make more changes than you imagine to a model you don’t understand.

However, we do have a template from which to start every model. It contains formatting and basic hygiene items and some timelines – but not a single further formula.

A good template should outlive you. More commonly, it will be shelved soon after you leave.

Conclusion

Template models take a long time to develop, are overly complex, need to be amended more often than people would like to admit, are risky to change and have a very short lifespan. And when the next person comes along, they’ll rebuild it anyway.

But they can also give you great control over how a model is used and standardise processes. If that makes a template worth the effort, then embark on a long specification phase and make the most of the benefits while you have them. 

Ian Bennett leads PwC Australia’s Deals Modelling team and has more than 17 years’ experience as a professional financial modeller.