Blog

Keep up to date with the latest news and ideas from Numeritas and the world of financial and business modelling.

News

How to build a financial model from scratch

TAGS: Best Practice, Model Build, Excel

DATE: Tue 13 Jun 2017
BY: Metin Ibrahim

How to build a financial model from scratch

So you’ve just been told that a model is required and you’ve been lucky enough to be chosen to build it. You enthusiastically open up Excel ready to jump right in, when you face the initial panic of dealing with a completely blank workbook. The mind goes blank and the fear creeps in. Where do I start? How am I going to create something that even remotely compares to spreadsheets already in my organisation?

Just like a blank canvas, looking at a new spreadsheet can be surprisingly scary to deal with - there are so many endless possibilities it can be a daunting place to start. However, keeping just a few key things in mind can really help in those early stages of a model build; whether you’re building a model for conventional business modelling, management reporting, or data analysis purposes.

1/ Figure out what you want your model to do

Ok, so I know this might sound a bit obvious, but you’d be surprised how many people start work in Excel before properly thinking about what the end result will be. This not only wastes time in the model build process, but can also lead to a clouding of the model process and logic flow. What are the key pieces of information that your model will need to reflect? How much detail does the model need to go into and how best is all the data analysed and reported?

Spend some time before you start figuring out what you want the model to achieve, and how it will need to show those all-important end results. This will help focus your mind on which assumptions and calculations need to be present and should help you avoid any unwanted or superfluous calculations.

2/ Keep things separated

I can’t stress the importance of this enough. Keeping things clearly separated is key in creating an easily understandable, adaptable and transparent financial model. Split out all your assumptions from your calculations and your calculations from your results. They don’t necessarily need to be held on separate worksheets but there should at least be distinct sections for each category.

Ideally your spreadsheet should read like a book; starting with the assumptions entered into the model, moving through to the calculations, and ending in the model results. This can significantly help improve the usability of your model, especially in regards to areas such as data analytics or more conventional business modelling.

3/ Avoid hardcoding like the plague!

If financial model builds had seven deadly sins, hardcoding would certainly be at the top of the list. Burying a constant number inside a formula not only makes your spreadsheet inflexible but it hides clarity, making it hard to follow, and can cause the model to return incorrect results without the user being fully aware of the issue.

Yes, your organisation might have always had a market penetration rate of 25%, but that doesn’t mean that this will always be the case going forward. Instead of just multiplying your formula by 0.25, set up an input cell for this rate and get your formula to refer to this cell. That way the user can immediately see what assumptions are affecting the model results and will give your model greater functionality by giving the user the option to flex this number.

4/ Keep things simple

One of the biggest misconceptions in Excel is that having a formula that includes multiple functions (whilst taking up multiple lines in the formula bar) somehow shows you are an expert model builder. How can it not? You’ve just calculated everything in one monster of a formula. The problem is that no one else has any idea what’s going on inside that cell or how the results have been calculated. Also, what happens if there needs to be a change in the way the cell is calculated? Unpicking the formula could be quite a time consuming process.

Always try and break down the model logic into small, easily digestible chunks so that it is obvious what each formula is doing. Try to keep this in mind – if you were to print out your spreadsheet, would the logic of each calculation be obvious to the person reading it even though they wouldn’t be able to see the formula behind it? If not, it’s likely that the calculations you’ve used are too complex and could likely be broken down into smaller, more manageable, segments.

Keeping things simple also has the additional benefit of reducing any further costs you may incur when it’s finally time to get a financial model audit or when you feel it’s necessary for your model to undergo a proper model diagnostic.

5/ Keep things consistent

Financial models can quickly develop into quite large and ungainly beasts and, as they grow, it becomes more and more important to ensure that a consistent approach is used in the model build process.

If using a timeline, for example, ensure the timeline starts in the same column on every sheet. Order your assumptions so they reflect the layout of both the calculation and report sheets. Also, aid the user by formatting each type of cell differently to help distinguish them from each other - standard industry practice dictates that input cells should be highlighted yellow, for example.

Keeping things consistent not only helps the user navigate their way around the model but also helps to give it that professional edge, providing the user with comfort that the model has been built with thought, care and accuracy in mind.

What next?

So now you know how to put a pencil sketch on your blank canvas. If you want to turn it into a Picasso click below to watch the video:

Watch a video about best practice modelling

Have your say

We love to hear what you think. please note that comments are moderated so there might be a slight delay. Your email address will not be published.

Comments

No comments to display, be the first! Leave a comment in the box above.