Enjoy complimentary shipping and easy returns on all USA and AU orders

The Arcis Man

5 secrets of great financial modelling

11 October 2019

By Dermott J. McVeigh

 

“Life is really simple, but we insist on making it complicated”

Confucius

 

The most effective financial models follow five common rules and as a result are practical and useable.  Their builders are seen as having the right blend of commercial and technical knowledge and inevitably become a core part of the management team.  So let’s have a look at these five rules:

 


1. Logic is everything

The biggest mistake people make when they try to build even the simplest of financial models is that they don’t fully understand how the business they’re trying to model actually works.  If you can’t explain it on a blank sheet of paper, then you’re not ready to open a spreadsheet to start modeling.  Starting before you’re ready inevitably results in a model that has severe structural problems and will likely need to be rebuilt.

So before you start, take a piece of paper and map out the main drivers that affect the performance of the business you’re looking at.  Include these 5 things:

If you’ve got a good grasp of the business’ operations you have the foundation to start modeling but please don’t ignore the second rule.


2. Keep it simple

Building a financial model is not your opportunity to show off your Excel formula skills. The key to building a successful model is to keep everything as simple as possible.  As you build several small layers of complexity, you’ll quickly have a complex model.  If any of your steps are too complex, the model will be unusable by anyone else and even you will struggle to retrace your steps next time you open it.

Make each step as simple as possible.  If you can easily break a step into two parts, do it.  Set your workings out in a logical manner so that one calculation leads to the next.  Remember math class at school?  There were no prizes for doing the sums in your head!

You need to assume that the next person to use your model won’t be you and won’t know what was going on in your head when you built it.  If it’s not logical with simple steps at each stage of your calculation process, the user will quickly give up and rather than getting recognized as a modeling guru, you’ll be branded as an un-commercial technician.


3. The format isn’t just for looks

Some people might think that using a consistent color scheme, font, and cell format is a waste of time.  They’re wrong.  Making sure your model looks tidy with neat formatting is crucial for most people to be able to understand it.  It doesn’t look neat and consistent, other users will quickly switch off and assign it to the corporate archives.  Here are some tips:

  1. Think about how you want people to read each sheet. You should design each page to lead the user’s eye to where you want them to start and then lead them down the page.  Try to avoid leading the user to scroll right as this breaks their train of thought

  2. Headings should be clear with a soft colouring and consistent formatting. Be careful with freezing frames, only do this to freeze information that’s relevant to the whole page.

  3. Think about naming each sheet. It should be logical and describe what the sheet is for.

If your model looks good, other users will be much more inclined to spend the time to understand it.  They’ll also find it much easier to follow what you’ve done.  You don’t need to be a graphic designer, just don’t let all your hard work get wasted because it looks unprofessional.  This is especially important for the output section of your model and you should invest some time to think through how the output dashboard will look.


4. Structure – keeping your house in order

A financial model needs structure, just like a house needs different rooms.  Your model should have three completely separate components:

  1. Input – this is where all the assumptions that drive the model are kept in a logical order

  2. Calculation – this is the engine room of the model where all the workings are performed

  3. Output – hey presto, the results of all your work are displayed on as many output sheets as necessary.

Keeping these components separate is one of the golden rules of modeling.  You need to resist the temptation to mix them when you are in the build phase as this will cause you major headaches later.  Just as you wouldn’t mix the bathroom and kitchen in a house, don’t mix your model.


5. Deadly modeling sins

I’ve seen all manner of modeling sins in my time, but there are two that stand out for their ability to confuse users and camouflage themselves:

So here’s a thought – don’t do it.  Regardless of how insignificant the formula driver is, put it on your main assumptions sheet.

It’s a poor practical joke and an even worse modeling practice.  The only conceivable exception is if you’re constructing a data table.  Otherwise, don’t do it.

So there you have it. Modeling isn’t that hard after all.  If you understand the business you’re looking at then you don’t need a PhD in Microsoft Excel.  Just keep it simple, logical and looking good.  Be disciplined with your structure and don’t commit one of the deadly modeling sins.  Like anything, the more you do it, the better you’ll get.

If you would like to learn more about financial modeling using Microsoft Excel, please click here to download our Financial modeling manual (developed in partnership with Avior Consulting).

You may also like...

Instagram post 2279028231498590252_12522535744 The Arcis Journal is your tool to turn your goals into reality by breaking them down into small actionable tasks over a 12-week period.  Committing to them in pen is the first step to committing to them in person.
Instagram post 2275921308419009054_12522535744 Juan is born out of a highly polished black resin case which has then been overlayed by a rose gold influenced cog based design that represents the wheels of industry. A timepiece of this stature has a strong, weighted feel for the man making his mark.
Instagram post 2273964325738512512_12522535744 Is this do-it from home fitness app going to make me look like Thor?! 😱  Our team put ‘Centr’ to the test, see what they had to say in our latest article. 🖤  Link in our bio ✌️ #fitness #workout #wellness
Instagram post 2268843068340761832_12522535744 Have you watched our latest video on IGTV? 🖤
Instagram post 2267422048027192928_12522535744 Stay tuned! Our YouTube channel is now live, please show us some love and subscribe! 🖤  The link is in our bio!
Instagram post 2250567948404978709_12522535744 Crashing through a forcefield requires momentum. 
Start now with the Arcis Journal & 12 Week Planner.  #pursueyourownlegend
Instagram post 2247695313702585486_12522535744 The ultimate travel companion.  Weekend away or short business trip, the Arcis travel bag is yours in either brown or black full grain leather.  #pursueyourownlegend
Instagram post 2246329728506856220_12522535744 Business ready.  The tools to help you make your mark no matter the occasion.  #pursueyourownlegend
Instagram post 2238545590211194361_12522535744 One of the world’s most famous cave dining concepts, Grotto Palazzese, Italy, gives you the rare opportunity to dine in an atmospheric cave inlet on Puglia’s dramatic coastline. Who would you take here for a Valentine’s date to remember? 🖤 #arcismen
Instagram post 2237034020956209326_12522535744 It’s all in the details🖤 
#arcismen #pursueyourownlegend
Instagram post 2236299066270017435_12522535744 Our next must do Valentine’s Day restaurant – London’s @clos_maggiore 
Voted the world’s most romantic restaurant it is immediately recognisable by their white cherry blossom covered celling and gorgeous open logo fireplace.
Instagram post 2235390195514868578_12522535744 This month’s goals laid out. Time to go get em  Shop the collection now.  #Arcismen #pursueyourownlegend
Instagram post 2232673890122629668_12522535744 What does a timepiece say about a man? 
It reveals the innermost mystery of a man. His style, his character, his ambition, his fears. 
What will your timepiece say about you? 
Explore our range of Swiss Made timepieces, each created to tell a different story.  The Valentine’s Day Gift that says so much.