Hello and welcome to a new series from the Excel Community's Financial Modelling committee, in which we will work through the chapters of our Financial Modelling Code and explain how each element translates into practice. You can read the Code here or watch our free webinar about it.
This week's post is written by Kyle Chaning-Pearce. Kyle is the founder and director at Finmodo, a training firm providing financial modeling courses to students, analysts and associates around the world. Prior to this, Kyle was head of training EMEA at Mazars Global Infrastructure Finance and Corality, where he developed financial models, performed advisory services, and trained finance professionals.
The layout and structure of a model is arguably the most important piece of a model. It is the foundation on which the rest of the model is built: the model infrastructure. And, for the purposes of this article, we can view this infrastructure from two different perspectives, the breaking out of the different functions of the model into different sheets – I.e. the workbook infrastructure; and then the layout within the sheets themselves, the worksheet infrastructure. Just like a city where there are arterial routes which determine how different suburbs are connected, each suburb should still have organisational principles within them. After all, you wouldn’t want a school located next to a jail. You want your model to be thought out from the get-go; a New York, not a haphazard Los Angeles.
So, with this framework in mind; let’s proceed with the most important workbook infrastructure principles.
Workbook infrastructure: Separating the functionality of a model:
The model is made up of different modules, which interact to calculate the desired outputs. For example, here’s what the key modules in a Corporate Model might look like:
Each block in the diagram (e.g. Scenarios, Inputs, Timing, Outputs, Operations) represents a module. In terms of model infrastructure, you’ll often find that each module is broken out on a separate worksheet (although sometimes calculations modules may be combined for example Debt & Equity might form a Funding worksheet).
Also please note; this diagram is not exhaustive. There may be a few extra modules depending on what you’re modelling – for example a Project Finance model will likely have a dedicated construction module, as it will be required to calculate Sources & Uses of funds.
Regardless, if you zoom out, you’ll often see this more generally represented as:
Inputs, Scenarios, Setup & Actuals in the former diagram become “Inputs” in the simplified diagram, Calculations are as contained in the dotted box, and Financial Statements & Outputs become “Outputs” in the simplified diagram.
The Code contains a range of guidance on how to layout a workbook, I want to highlight four modules (featured in blue in the diagram above) I think are fundamental when laying down those arterial routes:
Those three are the big-ticket layout and structure items from a workbook perspective. Let’s now delve into each sheet, and investigate the important things to get right:
Worksheet infrastructure: Within each calculation sheet:
As above, I’ve picked out three areas that I think are vital for organising the layout and content of your model’s metaphorical suburbs. Per the diagram above, these represent within each worksheet (e.g. operations) – and therefore within each module & block.
Standardising the timing block in terms of features and location on each calculation sheet, will greatly assist model transparency. As a side note, flags (to be covered in a future blog series) are a workhorse in a financial model; they greatly reduce complexity and they increase transparency of any timing related calculation.
The following are suggestions for how you might structure a model:
Col A:B One or two columns used as “framing” columns. This is helpful for:
Col C:D Col C for labelling (which basically everyone does anyway), and column D for units (which unfortunately is often missed).
Col E:I [columns before the timeline calculations]. I suggest reserving these columns for fixed input call-ups and intermediate or feeder calculations. These items are very useful in de-risking a model. Let's say you have a fixed price for the next 10 years. Instead of linking calculations directly to the inputs page, you might consider using cols E:I to link in the fixed price for display purposes. By being able to immediately see the constituent feeder elements of a calculation, a user is much more likely to be able to quickly understand the calculation. Additionally, some intermediate calculations may be required – for example if inputs are in sq ft (square feet), and your calculations need to be in sq m (square metres) - this area can be used to transform inputs into sq m, prior to doing it in the time-series calculation (columns J onwards in the above example).
Those are some of the big-ticket items for building a beautifully laid out model. If you’re building a small/medium sized model; these will all help you. If you’re building a large model, these are so fundamental, that you almost won’t be able to build or navigate a large model without them! There are other elements in the Financial Modelling Code’s guidance on layout, such as avoiding multiple-workbook models wherever possible, but that’s all we have time for now.
Part 3: Layout & Structure - Navigation