Financial Training Associates 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 financial model that you haven't created yourself - an advanced model that you can't be completely familiar with. Here are some thoughts about how to approach the challenge if you find you've got the horrible task of manipulating a large complex advanced financial model.
1. 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 were developed, those terms required the financial model to be able to run the business case that is 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 financial 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.
2. Dodge the bullet. If you are presented with a complex inflexible financial model, and asked to run scenarios or make complex modifications, stop for a second, and consider whether there's any scope to push the modifications back to the person who originally built the model. Perhaps the model was built by a consultant who might be happy to make the changes for you. Perhaps the financial model was built by someone who has transferred into another department, and can be persuaded to spend a little more time making the modifications needed. There's no shame in that. It's no poor reflection on your financial 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 it - without even realising what you've done. If you have the opportunity to get input from the person who originally created the model, make use of 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. 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 large 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? 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 changes relatively simply. Instead of going through the financial 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 financial 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, we admit it. We're all working in real world financial modelling. A world where sometimes where the model wasn't built right first time and we can't dodge the bullet. 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 make all the tiny little changes needed, and make sure they're tracking through the financial model correctly. And in making those changes, you’re going to have to be very careful to document exactly what changes you have made and what your basis for making the changes has been. That’s how you’re going to protect yourself here, remembering that, no matter how creatively you've tried to approach the problem of advanced financial modelling, sometimes there's just no easy answer!