At last we meet.
I have been meaning to write this book for more years than I care to remember and you have decided that maybe there is a better way of building financial models that doesn’t involve trying to find Balance Sheet errors at 2am on a Saturday morning. It has always frustrated me that there’s never been a really practical book - heck, maybe a manual - that helps newcomers and the experienced alike to build better financial models. I just hope this one ticks some, if not all, of the boxes for you.
You may feel a little daunted looking at both the title and the thickness of this book. Hey, you’re worried - I’ve had to write this thing! For those of you unlucky enough to have met me, you’ll know I get bored very easily and you’ll also know I have a truly terrible sense of humour. Therefore, let’s make a pact: I am going to make this as easy and as practical a read as I can - and you’re going to tolerate my jokes. Deal..?
So what’s this book about? Well, I am going to assume that you, dear reader, have a basic understanding of Excel: I assume you are alive, you know how to open Excel, you know where the Ribbon is, you have used a keyboard before and that you can both read and type (sometimes even at the same time). I am also going to assume your work requires you to work in “finance” and that you have to work with financial projections or forecasts (most likely, you are charged with their preparation). This is who I am aiming this book at.
The plan is therefore as follows:
•Key Excel functions: Before we go anywhere, let’s do a refresher on the key functions most commonly required in financial modelling. That way, we are all on the same page. For the more advanced amongst you, may I suggest you read this section as well, as there’s stuff in here that many just don’t appreciate.
•Key Excel functionalities: There are other attributes that we need to take for granted too. In this section, I will discuss key functionalities such as absolute referencing, number formatting, conditional formatting, Data Tables, data validation, range names, hyperlinks and the like. OK, these features will be taken out of context, but it will make for an easier read when we talk about building the model.
•“Best Practice” methodology: There’s so much literature out there on this hotly-debated topic. Many academics and practitioners alike get hot under the collar just thinking about a model’s flexibility (maybe I could have phrased that better, but I did warn you about my sense of humour). I have even been involved in writing some of these said texts, but hey, I was young and I needed the money... However, we do need a conceptual framework and I propose something very simple - something I call CRaFT.
•Layout tips: Everyone always ploughs straight into Excel and seldom gives thought as to how to put a worksheet - never mind a workbook - together. Where should a heading go? Why? Should we use a convention for sheet tabs? Should we be pedantic about spacing? Citing units? Formatting? Copying? (The answer is yes; otherwise this will be a very short section.).
•Time series analysis: If we are going to build a model, we will have to work with dates - and that’s perhaps not quite as straightforward as you might think. In this section, I will explain how dates ought to be constructed and why - including periodicity issues - and where they should be positioned both in a worksheet and within the workbook to avoid errors.
•Error checks: Talking of errors, error checks are often added as an afterthought in a model. They shouldn’t be. In this section, I will explain why they should be at the forefront of your model development and implementation will just make your life - and the model user’s life - easier.
•Base template: No, I don’t mean, here’s a model I used last time and I will add a row here, delete a column there and deal with the #REF! errors and other model integrity issues when someone points them out to me. No; I accept all models are different, but they do share common attributes. It’s this foundation which I can translate into a base template to use at the outset of developing a financial model.
•Financial statement theory: It’s no secret that it was the phrases “double entry” and “working with models” that attracted me to this profession. How disappointed was I? On a serious note though, I want to revisit the key outputs of a financial model to fully understand what “three-way integrated” means and the ramifications for the modeller. Further, I actually go back to understand what is an Income Statement, a Balance Sheet and a Cash Flow Statement. Yes, you may know what they are - but I want to do it from the perspective of understanding the purpose of each statement so that it guides you in determining the order of building a financial model. No matter what you build, the derived order may be applied to all future model developments.
•Control accounts: How often have I mentioned debits and credits so far? Who said I haven’t? I just did in the first sentence. Debits and credits are the accountants’ way of keeping the mystery alive in finance. Hey, I think the bigger mystery more commonly encountered is, why doesn’t my Balance Sheet balance? If you choose to use control accounts, Balance Sheet errors will become a thing of the past.
•Example of a model build: Oh yes, might be an idea to actually build a model. This may be a small paragraph here, but it’s a big part of the book as we explain the four methods of model input, calculation tips etc. building in the order derived in the Financial statement theory section.
•Reviewing the model: Here, I talk about the difference between a “self-review” and a “model audit” and why the latter is very important. I present some common tips and tricks for checking your models - without any fancy add-in software - and even leave you with a suggested checklist and a discussion on ratio analysis.
Providing someone reads this book, future texts will cover “further reading”, such as what-if analysis, debt and the cash waterfall, valuations modelling and other corporate finance stories including mergers and acquisitions and project finance.
Before I proceed, let me stress one last thing: this book is a practical book. There are lots of supporting Excel models to play with and use, grouped by chapter / section, to visualise the important concepts discussed here. So no excuses, make sure you are sitting comfortably and open up Excel. There’s examples aplenty and the best way to understand is to do. Enjoy!