Advanced Excel Modeling - Getting Dynamic
Post date: Jul 22, 2018 2:24:02 AM
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 completed our project and delivered it to Payton. But projects, especially 'simple projects' tend to grow. We live in a dynamic universe. It is constantly changing. So why would we expect our models, which are representations of real world entities, to be static?
Lesson Objectives
At the end of this lesson you will be able to:
Explain the differences between static and dynamic models
Explain when we need dynamic models
Explain how to create dynamic models
Make your model dynamic
Changing Requirements
The meeting
Payton tested the model and after playing with it said, "This is good but I want to consider several different loans."
"No problem," you explain, "Just change the loan parameters."
"I get that," Payton says. "But I want to see them side by side. I have at least three different loans I want to evaluate."
"All fixed rate loans?" you ask.
"Yes," Payton assures us.
"We can handle that but it requires a change order." You hand a copy of the signed SOW to Paton.
Payton examines the agreement as you explain, "The original SOW was for modeling one loan at a time. Comparing multiple loans is more complicated."
"How complicated?"
Your first reaction is to blurt out an estimate but you've under estimated projects before and had to live with the consequences. "I'll draw up a Change Order for your review and email it to you later today. Until then, here is the invoice for work completed thus far."
Project Re-Planning
Clients almost never know exactly what they want. Nor do we. How many times when modeling for ourselves have we changed our model from what we originally envisioned to something else. We should expect this. We created our SOW because we anticipated the need to convince our client that what we delivered was what was agreed upon. Otherwise some clients expect us to bring in their new request within the original estimate.
After some thought we design a new model with these requirements
Objective
Calculate a variable number of fixed rate loan's total cash outlay and costs
Required Outputs
For each loan output:
Loan Identifier
Total Payments
Total Interest
Required Inputs
Loan Amount
Number of Loans and for each loan
Loan Identifier
Annual Percentage Rate
Loan Term
Required Process
For each loan calculate:
Monthly Payment
Total Payments
Total Interest
This all looks very familiar with one major difference: Variable number of loans
How to Make Dynamic Models
Handling multiple scenarios is a common requirement. The traditional approach is to reserve enough cells to handle more scenarios than will ever be required. The problem with that approach is if we guess too few the model won't work and if we guess too many our model becomes bloated and sluggish. More advanced modelers use dynamic named range formulas. That solves the problem of too few or too many but dynamic named range formulas are complicated and easily broken. Excel solved this problem with an elegant solution: Tables.
Table Advantages
Tables started back in Excel 2003 where they were known as lists. In Excel 2007 they got structured references which were noticeably improved in Excel 2010. Tables include all these features:
Advantage
Dynamic
Structured References
Auto-Extensions
Auto-Totals
Banded Rows and Columns
Hide Headers
Formula Consistency
Header Changes Propagate to Formulas
Power Query
Slicers and Filters
Description
Table are fully dynamic ranges without the need for dynamic ranged name formulas.
Tables come with several automatic named range sections. They are:
Structured References can be combined:
When we add rows to tables, formulas, formats, conditional formatting and data validation automatically extend from the rows above to the rows below.
We can select an option to add a total row to the bottom of tables.
Another option is alternate row and/or column shading which, when used subtly, aids the eye in linking cells in the same row/column.
We have the option to make the header row vanish. It's still there but it doesn't occupy any cells which allows us the opportunity to use formulas for column headers though the structured references are still linked to the hidden headers.
We have the option to require all formulas in a given column be identical
We can change a column heading and the name change will change in all formulas referencing that column.
Power Query reads and outputs tables. MS Query also outputs tables.
We can add slicers and auto-filters to tables.
With so many great features it is a wonder that everybody doesn't use them.
I use them almost exclusively. In the previous diagrams of Payton's simple project you may have noticed the various sections look like tables. In my model, they are. Structured sections do not need to be tables but tables add a level of structure by gluing the cells of a section together allowing us to easily move entire sections simply by moving their table. We can even import tables into new models as modules. I do this with Workbooks, worksheets, tables, and VBA using MAKE (A free but complicated utility for assembling 90% complete Models from components in seconds.).
Creating Tables
Creating tables is easy. Click any cell in a data list then press CTRL-T (or CTRL-L). This displays the Create Table dialog. Make sure the range Excel selected is correct and if your table has headers (it should) make sure to check My table has headers. Then click OK.
At this point the cell you clicked on is in a new table. Whenever we click a cell inside a table Excel displays the Table Tools ribbon's Design tab and on the left side of the Table Tools Design tab is the Table Name box. We can change the table's name simply by typing the new name into this box. Make sure the table name is unique to the project.
Lab
Re-estimate your project and complete a new SOW
Open your model and save it under a new name
Convert Input sections in your model to tables. Use the table below to name them*.
*
I precede table names with tbl so I can instantly recognize such names in name manager or the name box as belonging to a table. I follow tbl with the table's label which I place two cells above the table. I have an exception to this policy when table labels use more than one word. In this case I abbreviate the label using the first letters of each word, use that abbreviation as the table name and place that abbreviation in parenthesis in the table's label.
Assignments
Complete Lab
Complete quiz
Quiz
SOWs identify:
What was agreed upon
What customers ask for is a change or part of the original project
What determines that a project is complete
All of the above
None of the above
Which option creates a table?
CTRL-T
CTRL-L
Ribbon option Insert > Table
All of the above
None of the above
Table names must be unique to...
Each worksheet
The entire Workbook
There can only be one table on each worksheet.
TRUE
FALSE
Tables are fully dynamic. We can add as many rows or columns to them as we wish.
TRUE
FALSE
Formulas, formats, conditional formatting and data validation automatically extend to new rows.
TRUE
FALSE
When we add a new row we must use Excel's Refresh All option to update table total rows.
TRUE
FALSE
Which structured reference identifies column headings?
Headings
#Headings
#Headers
[#Headers]
An @ symbol in a structured reference indicates
The same table column
The same table row
The same worksheet row
Summary
Modeling real world entities often involves adding things like more periods, more customers, more products, more options, etc. We should expect changes. We should design models to accommodate change. Excel's tables are designed to accommodate dynamic models much easier and their structured references help make formulas self-documenting.