Financial modelling course tips

Training company FTA Ltd answers a recent question raised on its financial modelling course. Here's the question, asked by a recent course delegate: "What do you do if you are asked to manipulate a large complex financial model? Here's the answer...

There's plenty of scope for error if you've been asked to manipulate a large complex model that you haven't created yourself - an advanced model that you can not be completely familiar with. Our approach would have you following the CYA financial modelling method. That's the Cover Your AXXX advanced financial modelling method. Here are some further thoughts about how to CYA if you find you're given the horrible task of manipulating a large complex financial model.

1. Dodge the bullet. If you are presented with a complex inflexible model, and asked to run scenarios or make complex modifications, the advanced CYA financial modelling method would have you stopping for a second, and just considering whether there's any scope to push the modifications back to the person who originally built the model. Perhaps the model was originally built by a consultant who might be happy to make the changes for you. Perhaps it was built by someone who has transferred into another department, and can be persuaded to spend a little more time modifying the financial model for its current purpose. There's no shame in that. It's no poor reflection on your modelling skills. Large complex models take a lot of time to understand and modify. If you plough on, perhaps under time pressure, because of the complexity of the model there's a real risk that you disrupt the model - without even realising it. If you've got the opportunity to get input from the person who originally created the model do that. Make the argument that, because they understand the model, it's going be a more efficient use of everyone's time. Because they understand the model, there's less of a chance that a mistake will be made with your name on it. That's CYA financial modelling.

2. Make a note: next time, get it right first time. We admit, maybe it's too late for this one, but wouldn't it have been nice if, when the financial model were built, it had been built with the flexibility to run the scenarios or modifications that are needed right now? Wouldn't it have been better if, when the terms of reference for the original model build were developed, those terms required the financial model to be able to run the business case that is quite obviously needed right now. Yes maybe it's too late for this one, but you could at least ask the (almost rhetorical) question: "What did the original terms of reference say about the model's ability to run this business case?". The answer to that question, although obvious at this late stage, might get people around you admitting that the model should have been designed to run this case from the start, and perhaps agreeing that the person who originally built the model is best placed to make the current set of modifications. Even if none of that does any good, maybe the discussion will at least make people appreciate the importance of good model design, and getting it right from the start. If the model wasn't right from the start last time, perhaps the lesson can be learned for next time. So that at least we don't all find ourselves in this horrendous situation again - being asked to make a complex set of adjustments to a complex financial model.

3. Flex the large horrible model very simply. If you are presented with the large horrible complex inflexible financial model, and asked to run a new case on it, take a second to step back from the detail of the adjustments you are being asked to make. Think about the real purpose behind the adjustments. For example, rather than changing every price and volume that contribute to the business's revenues, think about the real purpose here. Despite what case we run, do we really have a great deal of certainty about exact prices and volumes? Is what we're really interested in the potential for total revenues to change? Instead of modelling detailed prices and volumes, couldn't we just scale total revenues up or down by a set %? If you've stopped to consider the real purpose behind the modifications, then you might be lucky enough to be able to model the modifications relatively simply. Instead of going through the model changing individual prices and volumes, perhaps you can very simply insert an extra line that scales total revenues up and down by a certain %. Making a simple adjustment will save time plus reduce the risk of you making a mistake in how you are flexing the original complex financial model.

4. Start again. It's very hard to manipulate someone else's large complex model. Sometimes it is just easier to start again and start with a new model. Stop and think about the modifications that you're being asked to make right now. Are they very detailed changes you're making, or are they high level changes? If you're being asked to make relatively high level changes, maybe you'd be better creating a simpler model (one less prone to error) designed purely to illustrate the impact of the case you're being asked to run right now. Yes you are bypassing all that previous work, but you'll end up with a model that you actually understand, removing the complexity of the old financial model as a source of error. Just have a think about it before you jump in. Would you be better off starting again?

5. Bite the bullet. OK OK. We're working in real world financial modelling. A world where sometimes we can't dodge the bullet and the model wasn't built right first time. Although you'd rather just start the model again, or flex the existing model very simply, maybe you really haven't got any choice. Maybe, no matter what you've suggested, someone is yelling at you just to make the required changes. There's no getting around it, you're going to have to set aside enough time to make all the tiny little changes needed, and make sure they're tracking through the financial model correctly. And in making the changes, be very careful to document exactly what changes you have made and what your basis for making the changes has been. That's what the CYA method would have you doing but sometimes, no matter how creatively you've tried to approach an advanced financial modelling problem, there's no easy answer!

FTA Ltd is a provider of financial modelling course and other related training for accountancy, law and banking and finance professionals.