Advanced Excel Modeling - Self Documenting Formulas

Post date: Jul 19, 2018 11:05:00 PM

For the professional modeler Excel's advanced features improve transparency and efficiency while reducing complexity and risk. They also provide greater flexibility, scalability and connectivity. These benefits grow in value as models become more complex and connected to other models, data sources and people.

In the previous section we discussed helping users make good entries and protecting our model integrity from unauthorized changes. This section discusses a technique to make models easier to understand and maintain: Naming

Lesson Objectives

At the end of this lesson you will be able to:

- Explain why documentation is important to the professional modeler
- Explain how names make formulas easier to read and understand.
- Explain how to easily make names that match their labeling and why that is important
- Apply named values to your models

This is under construction.

Click this link for the course's start

Names vs. Cell Addresses

We love Excel because it's so easy. Just create formulas by clicking on cells to include their values and insert operators and/or functions. Unfortunately, this creates a cryptic mess like this:

=PMT(B7/12, B11, -F7)

To make sense of this we must decipher those cell addresses, find them, and hope that next to each cell is a label providing that value's context (meaning). We can do better. We can make formulas that look like this:

=PMT(Interest_Rate/MPY, Term, -Amount_Borrowed)

When the cell's name is the same as the cell's label then we can skip the steps of deciphering and finding the cell's address because the cell's label is its name and its value can be evaluated in the formula using F9. This makes formulas using names more transparent. It also makes cells using names self-documenting

Self Documenting Formula

A formula that explains itself by replacing cell references (computer centric) with descriptive names (human centric) sufficient for humans to understand what the formula is doing and for what purpose.

One other advantage names have over cell references is they are easier to type and harder to mis-type thanks to Excel's autocomplete feature. Mis-type a cell reference and Excel blissfully accepts it. Mis-type a named reference and Excel complains bitterly.

Documentation is critical when the possibility exists that someone must work with us on our model or when someone may have to modify our model months later - even if that someone is the originator.

Making Names the Easy Way

Making names can be easy if we follow a few simple rules:

Label Positions

Unique Names

Place label immediately left of the cell or, if in a table, in column headings.

Names must be unique so the cell labels must be unique too.

Creating names that are identical to their labels is simple. Highlight the first two columns of each section (without the heading) and use menu option: Formulas > Create from Selection. Check Left column then click OK.

I use BXL's Helper add-in (free) to create and manage names as well as to perform other common modeling tasks.

Lab

1. Create names over the Properties, Labels, Constants, and Process sections.
  - Challenge! Create all names at one time.
2. Replace all cell references with named references.
3. Create an Invoice for Payton

Assignments

1. Read Define and use names in formulas
2. Read BXL's Helper add-in
3. Read Illegal Excel Name Characters
4. Complete Lab
5. Complete Quiz

Quiz

1. Documentation can be found in:
  1. Self-documenting formulas
  2. Our model's documentation section
  3. Cell comments
  4. Value labels
  5. Data validation input messages
  6. None of the above
  7. All of the above
2. Named references provide a means to:

- 1. Make constants transparent
  2. Prevent some data-synch errors
  3. Prevent some formula entry errors
  4. None of the above
  5. All of the above

1. Named references must be unique to the model

- 1. True
  2. False
  3. It depends

1. Named references, to be transparent, must be the same as their reference cell's label

- 1. True
  2. False
  3. It depends

Summary

Names turn Excel's computerese, cryptic formulas into more transparent, more human readable procedural statements

Styles and Protection

Introduction

Dynamic Modeling