Formatting and Design Matters!

Formatting and Design Matters Featured Image

You might think that as long as the financial model works and makes sense that it’s okay to not care about how the formatting looks like. Wrong! Formatting matters a lot especially if you want the users to choose your model. The fact is – and we have proof of that – well formatted and designed financial model spreadsheet sell much better than spreadsheets with bad formatting and design.

By using Excel, don’t settle with its simple default style and make use of its functionalities to better organize and format the worksheets properly. Cases such as extra cells, too much spacing, not enough spacing, determining headers, which alignment is appropriate, the layout of sections, etc., sometimes the users don’t want to deal with such things.

Another thing to remember is to avoid merge cell alignment setting. In Excel, selecting columns and/or rows gets confused when models have cells merged across them and unmerging is time-consuming and can cause referencing problems. The latter point is the most common problem faced by financial modelers in relation to merging cells. The ‘merge cells’ option is only useful for the formatting and graphical representation to the summary tables, key output and representation sheets, etc. However, this can also be achieved by using center-across-selection cell formatting.

Formatting Aspects

Here are the formatting aspects that you need to consider to ensure that your financial model templates will be well-received by users.

  1. Apply consistent Fonts and Color Codes – consistency is key to create a template which is pleasing to the eye. Not only will it look professional, but it will also not look complicated and easier to read and understand.
  2. Avoid using colors which hurt the eye (red, yellow) – it is best to use color coding that doesn’t hurt the eye. Some users can’t tolerate extreme colors which are too eye-catching and bright which make it hard for them to read the model or read for a long time. Therefore, it is important to pay attention to your color coding throughout your template.
  3. Make it clear what unit (USD or Volume) each row is – when dealing with numbers, you need to ensure to mark the right unit for each row. By simply adding a column designated for the units, it will help the users determine which is which. 
  4. Create Graphs for quick understanding – with graphs, it is easier to get an overview of the results calculated within the model. Most users often prefer financial model templates that includes graphs and charts in them.
  5. Create an Executive Summary section with the key outputs of your model – this is one of the most important component of your template. Depending on how presentable you formatted your Executive Summary, the more likely it is to sell. Not only will you need to include graphs but also you need a layout where you include the key outputs of your model in a uniform manner. Ensure that it is not complicated and easy to understand by any kind of user.
  6. Explain all abbreviations you are using – not all users have the technical knowledge when it comes to financial modeling, hence, some jargons that finance-related users are used to might confuse some users. Therefore, you need to dedicate a section explaining all the abbreviations are using. This is turn will be greatly appreciated by the users of the template and help you get more sales.
  7. Structure your model clearly by inserting clear titles in the respective sections – each section is straightforward so structure it clearly by naming it appropriately. You don’t need to overcomplicate things and simply use a clear title according to what the section is.
  8. Format the columns’ width appropriately to show the number – though this can be dealt with by the user, it is still best to accommodate them by doing it yourself before selling the template. According to our experience, most of the customers come back asking why the figures are not showing or why the columns for the numbers are too compacted. In some cases, this could be also a problem with the unit and resolution of the user which might cause the model to show some errors. Therefore, to avoid such cases, ensure that you placed enough space for the figures to show up properly, the same goes with the titles.
  9. Choose the appropriate alignment all throughout the model – for titles or variables, left alignment or center, depending on what component it is, are preferable. As for the outputs or numbers, such as money, right alignment is better. Ensure that the alignment is uniform all throughout the template.

The above are just the most basic aspects that we think are useful when it comes to customizing your template to sell better. Though not mandatory, we encourage you as one of our vendors to follow these formatting aspects but of course, if you have an idea that’s even better or something you might want us to add, please do tell us by making a comment below.

General Workbook Design Principles

You might have already known the FAST Standard, but for those who doesn’t, the FAST Standard is a set of rules on the structure and detailed design of spreadsheet-based models. It provides a route for countless modelers to create a clear and good model design which were tested to be highly accepted and welcomed by users. FAST stands for: Flexible, Appropriate, Structured, and Transparent. These four standards are critical factors that a good model design should follow according to the experience of industry practitioners and countless trial and error over time.

According to the FAST standards [Link], good modeling practice begins with an explicit and purposeful structure applied with consistent discipline at the workbook level. The high-level layout of a model must reflect the requirements of two fundamentally different groups of interested parties. Meaning, the financial model design must cater to both users and modelers. But, you must also keep in mind that the FAST Standards can be very particular with details and technicality, which can be a hindrance to make your template user-friendly.

After all, Less is More! The lesser the number of worksheets you include in the template, the better and convenient it is for the user. Minimize your calculations to a point and do not overcomplicate things. With our years of experience selling financial model templates, most users prefer templates that are not complicated to edit and customize. Therefore, though it is important to comply according to the standards, you must always keep in mind that the simpler your model is, the more likely it will sell.

The following FAST Standard principles are applied generally for worksheets in a model which we recommend for you to consider when building financial models.

A: Separate worksheets by type: Foundation, Workings, Presentation and Control.

  1. Foundation – this includes the worksheets for inputs, time period, and indexation factors, which are the model’s main components and serves as the foundation of the whole model. Without it, the model can’t function nor even completed. When using a financial model template, re-designing the elements of the foundation are the most critical operations which could ruin the flow of logic as well as the risk of the financial model not functioning properly. Ensure that you have the right know-how as well as a substantial financial modeling skill.
  2. Workings – this consist of the inner workings in a worksheet which is the calculations leading to the outputs. Basically, these are the worksheets that contain all the formula and links on how you came up with the results. It is important that your financial model is fully transparent to the user so that they will understand the logic behind the calculation as well as be able to understand the model easier.
  3. Presentation – this consist of worksheets for Financial Statements, Charts and Graphs, Primary Commercial Inputs, and Executive Summary. In other words, this represents the financial model’s dashboard. Most often, customers tend to choose a financial model with a dashboard that follows a logic that easier to understand. The more user-friendly your dashboard is, the more likely your financial model template is to sell.
  4. Control – these set of worksheets are dedicated for calculating the sensitivities and scenarios, changetracking, list of pending changes, versioning, and table of contents. This is the financial model’s main controlling tool and status indicator. With Excel, it is easier to keep track of the changes, managing the whole framework, indicating which sheets or sections are functional or not, as well as using features for error checks, saving different versions of the model, running simulations with different scenarios and checking the sensitivity of the model. Not all financial models must have these control drivers, but to provide a quality model template for users, it is a great practice to add these extra worksheets.

B: Maintain consistent column structure across all sheets.

You need to set up a standard column definition and apply this across all sheets if at all possible. For example, the column used for labels, constants, units, and the first column of a given time series can usually be conformed across all sheets in a model. According to our experience, the customers often prefer financial model templates which have a consistent column structure and formatting. Not only does it make their financial modeling tasks easier, but it will also give them a worksheet that’s presentable and easier to read.

C: Be consistent with your model’s time ruler except when you need multiple time resolutions.

Each worksheet in the financial model should have the same time periods e.g. weekly, monthly, quarterly, and annually. Not only will it avoid confusion for users, but it will provide a uniform set of worksheets which uses the same column for the start of the time period with the same length. This practice will vastly improve the readability of the financial model and also helps with reducing serious errors which were missed during the review process. If you’re using different time period frames, then ensure that the primary time ruler covers the secondary rulers.

D: Replicate links to increase formula comprehension and navigational efficiency.

Manually inputting formulas repeatedly is too time-consuming. It is best to proliferate links to make it easier to locate the precedent formulas which in turn increase the transparency of the financial model. Though it is a good practice to link formulas, please keep in mind that you must NOT create chain links. Chain links will impair the navigational effectiveness of links which often wastes the time of the user just to locate the original calculation. Always remember that links should point back to the original source calculation. The more convenient it is for the user to customize the financial model template, the higher the chance that the user will likely use your other/next templates again.

E: Use a color scheme to distinguish the inputs and outputs.

In our case, we usually mark our inputs as blue and outputs as black. This will help the users to distinguish which cells they need to enter their data. Though there isn’t any standard on which color of choice to mark the inputs and outputs, the color scheme chosen depends on the financial modeler or creator of the model. This practice is a must for financial modeling practitioners to reduce the incremental effort of determining which is which in the model.

F: Calculate only once.

This principle is related to the rule mentioned in D which is to replicate and linking formulas. It is important to note down that a given calculation should only appear once in a model. Basically, create an original source formula which will be then used throughout the financial model, BUT always direct the link back to the source calculation and not repeating it.

G: Avoid the use of macros if possible and never use circular references.

Not all users’ units support macros, hence, avoid using it if possible. It is always best to keep the calculations simple and the model easier to read and edit for users of any kind. Also, never use circular references which turns the model not accurate and unreliable.

If you want your financial model templates to sell better, we recommend for you to practice these set of principles which we have proven to be useful and helpful with building quality templates that users often prefer.

Conclusion

With our years of experience selling financial model templates, we know how important it is to have a well-designed and well-formatted template. The Significance of Spreadsheet Design and Formatting is clear to see and has also been stressed by other professionals. Although the way Excel financial model templates are built varies from modeler to user, we hope that this article helped you determine which points (according to FAST Standard) needed to be placed importance to. We have also compiled a list of Best Practices in Excel Financial Modeling which we have seen over the years and found to be very useful when building financial models in Excel. You can also check out our guideline on How to Build a Complete Financial Model with Excel.

If you are looking for financial model templates that you can use as a reference to build your own version, you can look through our list of financial models by Industry and by Use Case. These templates are downloaded by different kind of users from different countries such as the USA, UK, Canada, Australia, Japan, and many more who were in need of assistance with their financial modeling tasks.