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
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
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
Create names over the Properties, Labels, Constants, and Process sections.
Challenge! Create all names at one time.
Replace all cell references with named references.
Create an Invoice for Payton
Assignments
Read BXL's Helper add-in
Read Illegal Excel Name Characters
Complete Lab
Complete Quiz
Quiz
Documentation can be found in:
Self-documenting formulas
Our model's documentation section
Cell comments
Value labels
Data validation input messages
None of the above
All of the above
Named references provide a means to:
Make constants transparent
Prevent some data-synch errors
Prevent some formula entry errors
None of the above
All of the above
Named references must be unique to the model
True
False
It depends
Named references, to be transparent, must be the same as their reference cell's label
True
False
It depends
Summary
Names turn Excel's computerese, cryptic formulas into more transparent, more human readable procedural statements