The problem is that no one else has any idea whatвЂ™s going on inside that cell or how the results have been calculated. How can it not? YouвЂ™ve just calculated everything in one monster of a formula. 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.

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. 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. 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. 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. If financial model builds had seven deadly sins, hardcoding would certainly be at the top of the list. This can significantly help improve the usability of your model, especially in regards to areas such as data analytics or more conventional business modelling. 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. They donвЂ™t necessarily need to be held on separate worksheets but there should at least be distinct sections for each category. Split out all your assumptions from your calculations and your calculations from your results.

Keeping things clearly separated is key in creating an easily understandable, adaptable and transparent financial model.

I canвЂ™t stress the importance of this enough. 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. 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. 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? This not only wastes time in the model build process, but can also lead to a clouding of the model process and logic flow. 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. 1/ Figure out what you want your model to do 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. 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. Where do I start? How am I going to create something that even remotely compares to spreadsheets already in my organisation? The mind goes blank and the fear creeps in.

Financial model builder