Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

Documenting Excel Models Best Practices

Best practices in building financial models in Excel

Documenting Excel Models Best Practices

When working on large and complicated financial models in Microsoft Excel, it can be quite challenging to document them clearly for the users’ ease of use and understandability.  We will discuss here several Excel models best practices and other useful tips and tricks that users can apply to maintain and audit your models more efficiently.  Excel offers a variety of functions that allow users to better organize their workbooks and contents.

 

Model Structure

Financial modeling always involves the insertion of multiple inputs into the models in order to generate certain desired outputs.  Most people tend to group all components on a single worksheet for simplicity.  Sometimes it is more suitable to separate inputs, process, and outputs into different worksheets or groupings as part of Excel models best practices.

The documentation method clearly defines the purpose of each worksheet/grouping, so users can quickly navigate and find the information they need rather than searching through a long, single-page model.

 

Documenting Excel Models Best Practices

 

Titles for Worksheets, Charts, and Tables

In Excel models best practices, it is always advisable to give titles to all worksheets, charts, and tables in your Excel models to clarify their purposes.  The titles should be meaningful and precise, so users can grasp the main point at first glance.

 

Excel Models Best Practices - Tip #1

 

Hyperlinks

We can use hyperlinks in a financial model to allow quick access to another section of the workbook or important information.  Hyperlinks are commonly used in the table of contents to provide linkage to different worksheets/sections, but they can also be used to refer to context information or formulas that are more sensitive.

 

Labeling Values

When structuring the inputs section or any other parts of your financial models, you should make sure all the values are given labels because using unlabeled values in formulas and calculations can cause confusion and inefficiency in modeling.  Sometimes, it is also useful to provide additional information about the cell values, such as units of measure, a brief description of the values, or other specific assumptions.

 

Excel Models Best Practices - Tip #2

 

Cell Comments

When you would like to include a detailed description of a cell value or explanation for a calculation, you might insert a comment in the cell.  It would create a small red triangle in the top right corner of the cell and displays a comment box when your mouse hovers over the cell.

 

Excel Models Best Practices - Tip #3

 

Structured References

When you create an Excel table, structured references are automatically applied in replacement of cell references when you enter a formula in another table column.  Referring to the example below, instead of showing “=D2-E2” in cell F2, the column names (“Sales Amount” and “Unit Cost”) are used as structured references to calculate the profit.

 

Excel Models Best Practices - Tip #4

 

You can also use structured references elsewhere in the Excel worksheet outside of a table.  The formula will show a combination of the table name (Ex. “ProductSale”) and column names (Ex. “Sales Amount”, “Unit Cost”).  The references make it easier to locate tables in a large workbook and to understand what the values actually refer to.

 

Excel Models Best Practices - Tip #5

 

Data Validation

We can use data validation in financial models to add restrictions to the types of data that can be entered into the input section.  Data validation essentially provides information about the cell values and helps maintain standardized cell formats.  One of the most common data validation uses is to create a drop-down list.

The most efficient way to create a drop-down list is to create your own list and give the list a name.  In the example below, we make a list of products in cells A2:A6 and name it “ProductType.”  When you use data validation to build a drop-down list, you can simply type the name defined for the list in the Source box, following an equal sign.  Using a table allows you to add or remove items from the list and the drop-down list will automatically update the content.

 

Excel Models Best Practices - Tip #6

 

Beside drop-down lists, you can also add other types of data validation to restrict cell inputs to a whole number, decimal number within limits, date within a range of dates, text of a specified length, etc.  In addition to input restrictions, you can add input messages or error alerts to point out invalid data.

 

Documenting Excel Models Best Practices - Tip #7

 

More Resources

Thank for reading this guide to Excel Models Best Practices. CFI is the official global provider of the Financial Modeling & Valuation Analyst (FMVA)™ certification designed to transform anyone into a world-class financial analyst. To learn more, check out these relevant resources:

  • Building a Financial Model in Excel
  • List of Excel Functions
  • Excel Shortcuts for PC and Mac
  • Advanced Excel Formulas

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!