Model design and structure can be one of the most difficult parts of building a financial model. Typically, modellers will work from back to front when building their model. The output, or the part they want the viewer or user to see, will be at the front, calculations will go in the middle, and source data and assumptions should go at the back. The image below shows an example of what your tabs in a well-structured model might look like.
Like the executive summary, a board paper, or other report, the first few pages should contain what casual viewers need to see at a glance. If they need further information, they can dig deeper into the model.
Extract from Using Excel for Business Analysis, Chapter One
Unless a model is very small, there should be a dedicated tab worksheet for each major component of the model. Whilst by no means a prescriptive list, the following is an example of what might be included on each tab.
Cover sheet: The cover sheet contains many details about the model. Whether or not a cover sheet is necessary is an issue for debate, one can include (but is not limited to) details such as:
- A log of changes and updates to the model with date, author, change details, and their impact to the output of the model. This is important for version control.
- Flow chart of the model structure.
- Table of contents.
- Instructions on how to use the model.
- The purpose of the model for which it should be used.
- Disclaimers as to the limitations of the model, legal liability, and caveats.
- Global or key assumptions integral to the use of the model.
Input sheet: This is the only place where hard-coded data is permissible. There may be one or more input sheets if there are copious amount of data, but the input data should be laid out in logical blocks, for example: consumption data, WACC data, assets and depreciation data, inflation and indices, pricing and tariffs, assumptions and constants (such as tax rate, discount rates, concession rates).
Output, summary, and scenario sheets: These present the final outcomes. They may also contain scenario drop-down boxes, spin buttons, or checkboxes that allow the users of the model to generate their own outputs. Tables and charts summarising the outputs should be set up in such a way that they can be easily printed or used to generate reports. Ensure that these can be easily printed directly from the model, or copied or linked to other programs such as Word or PowerPoint.
Calculation or workings sheets: Split the calculation sheets logically and then, within each sheet, set them up consistently. If calculation sheets are split, ensure that the layout and formatting are as consistent as possible across all sheets.
Error check sheet: This sheet contains links to all error checks in the model. Error checks should be performed in the calculation section, but a summary of all error checks in one location means that once the model is in use, the modellers can quickly check to see if any of the error checks have been triggered.
Workbook Anatomy Issues
Matters to consider when designing the layout and structure of the model include:
A cover sheet: Is a cover sheet necessary? It’s not absolutely critical, but it is good practice to put one in. In my experience, cover sheets and instruction pages are rarely used. If you decide not to include a cover sheet, make sure that the model contains explicit instructions regarding operation, purpose, assumptions, source data, and disclaimers.
Input and output locations: Should there be a dedicated input sheet, or should the inputs and outputs be contained in one sheet? Many modelling specialists maintain that inputs, outputs, and calculations must be clearly separated, but this is not always practical. Generally, larger models should have a dedicated input sheet, while smaller models may show inputs and outputs on the same sheet. However, if you’ve created a large model with inputs and outputs on different sheets, and then want to perform a scenario analysis using a data table, you’ll need to move the inputs and outputs to the same sheet. For example, in a small model that takes inputs and generates simple tables of output data and charts in reports, the modeller may set up the sheet to have a block of input at the top of the worksheet with the calculations and charts directly underneath. You may consider splitting the charts and ratio calculations into separate worksheets to avoid the worksheet becoming too long and unwieldy.
Calculation organisation: Should calculations be on one or multiple sheets? Depending on the size of the model, all calculations could be contained within one worksheet, spread over several worksheets, or even spread over several workbook files. If the calculations become long and confusing, it makes sense to split them into logical sections. For example, they can be split by type of service, customers, financial tables, geographical location, or business segments.
Colour coding: If you decide to use colour (and I recommend that you do, or else your model will look pretty boring), make sure that the colours you use are consistent. For example, if African regions are yellow, Europe is blue, and Asia is pink, make sure that you use those same colours every time numbers for Africa, Europe, and Asia are displayed. These colours should be consistent in calculations in your model, display tables, and charts. Some companies have standard colour coding, but if your company does not, you might consider developing a standard. You could consider including a colour code key in the cover page. The use of predefined styles (found on the home tab) can make colour coding very quick and easy. Below are some commonly used colour codes that are supported by the in-built styles in Excel that you may consider adopting in your company:
- Blue font and beige background for input cells.
- Pink or gray for error checks.
- Green or orange for external links.
Formatting can be quite time consuming and using Styles is a much faster way of creating consistent formatting. The image below shows where to find the styles menu on the home tab.
Use a double border to indicate that the calculations change. In the image below, the double line shows that the formula is not consistent across the row.
Many companies have their predefined colour coding loaded as style templates, which ensures consistency in color coding in financial models.
Want to learn more about Financial Modelling? Enrol on a course, or purchase your copy of Using Excel for Business Analysis
