Advanced Excel Modeling - Table Types
Post date: Jul 24, 2018 10:39:46 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 our last section we converted our Input sections to tables but, admittedly, there wasn't much of anything happening that was dynamic. We will change that in this post with the introduction of the Item and Process table types.
Properties
Items
Labels
Constants
Rates
Validation
Process
Integrity
Lesson Objectives
At the end of this lesson you will be able to:
Name the basic table types.
Identify which structural section they belong to (Inputs, Processes, Outputs)
Explain each table type's purpose
Table Types
Over the years I found model tables can be categorized into a handful of types. Each table type fits one of a few model roles such as capturing user inputs that apply to the entire model, or user inputs that apply to instances of items, or model constants, etc. Each table type has characteristics. Knowing which table type to use for which situation can speed model development and result in model consistency which makes maintaining models much easier.
User Input Tables
These are model inputs maintained by users and are found in the inputs section
Properties
Purpose: Provide users a single place to change base model properties (often referred to as assumptions) which are single values that may not be closely related other values in this table. Grouping them into a single object (table) makes finding them easy which helps guide users to where they can make changes.
Columns: Property tables have three columns.
N
V
U
The first column contains the property's name. This name will become an Excel Name and so must be unique within the model. In particular it cannot duplicate the first column in this table, the Labels table, or Constants table.
The second column contains the property's value. The value column is what users can change so I apply Excel's Input style to it in order to open it up for user entry in protected worksheets. I also apply data validation to limit user entries to what's reasonable and to help explain to users how this value impacts their model.
The third column contains the value's Units of Measure, Format String or Code Description.
To maintain model transparency we must label:
Numeric values with:
Context, such as "Loan Amount"
And Units of Measure such as "USD" unless a # is purely a number with no units
Compound values, such as dates, times, GPS coordinates, etc, with:
Context, such as "Loan Start"
And a format string such as "mm/dd/yyyy"
Text values with context such as "Monetary Units".
Labels
Purpose: Provide users a place to change some of the models labeling such as monetary units or data formats. Grouping them into a single object (table) makes finding them easy which helps guide users to where they can make changes. Label tables also provide modelers a means to avoid data sync errors and simplify maintenance.
Columns: Label tables have two columns.
N
L
The first column contains the property being labeled. This name will become an Excel Name and so must be unique within the model. In particular it cannot duplicate the first column in this table, the Labels table, or Constants table.
The second column contains the property's label that will be displayed where ever this label is needed. The label column is what users can change so I apply Excel's Input style to it in order to open it up for user entry in protected models. I also apply data validation to limit user entries to what's reasonable and to help explain to users how this value impacts their model.
Items
Purpose: Provide a single place for users to maintain multiple instances of an entity like accounts, customers, products, positions, etc.. Each item table is for one entity type and each item table row is an instance of that entity type
Columns: Item tables have at least two columns but usually several more than that.
#
N
A...
The first column contains the item's ID (#) which is automatically assigned by the formula: =Row()-Row([#Headers]).
The second column is the item's name or brief description. This, and all subsequent columns are open to the user for entry and thus I apply Excel's Input style to them as-well-as data validation
Subsequent columns contain the item's distinctive attributes
This is where models can get dynamic because item tables can grow or shrink to accommodate as many instances of an entity as needed without requiring formula changes. Later on we will semi-automate growing or shrinking tables using Power Query and later still we will show some simple VBA to fully automate this.
Associations
Purpose: Provide a single place to maintain attributes related to 2 ore more items.
Columns: Association tables have one key column for each item key (there must be at least 2 different item types) and one column for each attribute tied to the item groups
#
#...
A...
The first column contains the first item type's item ID (#) which is automatically assigned by the formula: =MOD(INT((ROW()-ROW([#Headers])-1)/()),Rows(Item(n)))+1 where Item(n) is this column's item type and Item(n+1) is the next item type and so on.
The next column(s) contain the next item type's item ID (#. The last item ID # is assigned by the formula: =MOD(Row()-Row([#Headers])-1, Item(n)) + 1 where Item(n) is this column's item type.
Subsequent column(s) contain the item grouping's distinctive attribute(s)
Modeler Input Tables
These are model inputs maintained by the modeler. Though these are inputs I move them to a worksheet other than the one users interact with. This is for two reasons. First, only the modeler can maintain these table; thus, Excel's Input style is not applied to any of them and second, these tables' contents are of little interest to anyone but modelers, model reviewers, and model auditors.
Constants
Purpose: Like the Properties table in the User Inputs section this table holds single values we can name and use in downstream calculations or outputs. Unlike the User Inputs Properties table the Constants table's values are entered by the modeler and notusers.
Constant tables provide modelers two important features. One features is values entered in this table can replace literals in formulas to maintain transparency. The other feature is it keeps constants in one place which we can refer to where needed to avoid data sync errors and simplify maintenance.
Columns: Constants tables have three columns:
N
V
U
The first column contains the constant's name. This name will become an Excel Name and so must be unique within the model. In particular it cannot duplicate the first column in this table, the Labels table, or Properties table.
The second column contains the constant's value.
The third column contains the value's Units of Measure, Format String if value is a compound value like a date or GPS coordinate, or Code Description if value is a code.
Validation
Purpose: Provide lists for dropdowns in data validation. They also provide a description which can be helpful when selecting a code or displaying what the code represents by name. Each validation table holds values for one list
Columns: Validation tables usually have two columns:
C
D
This contains a unique (to this table) code. This code will be used where needed in the process tables.
This contains the code's description. This description is often used in dropdown lists for users to choose from as well as in output tables because the are easiest to understand.
Sometimes validation tables have three columns with the first used for a category. This is one way to handle dependent dropdown lists. (see: Jon Acampa's tutorial)
Rates
Purpose: Provide brackets for rates commonly found in tax tables and commission schedules. Rate tables must be sorted in ascending sequence on the first column to accommodate non-exact matches
Columns: Rate tables have at least two columns:
[#]
V
This contains the minimum value for each bracket range
This contains an attribute associated with the bracket. There may be several attributes in a rate table.
Process Tables
These tables are our model's calculation engine. They are found in our model's Process section which I prefer to keep on separate worksheet away from user and modeler entries
Calculated Properties
Purpose: Like the Properties table in the User Inputs section this table holdssingle values we can name and use in downstream calculations or outputs. Unlike the User Inputs Properties table the Calculated Properties table's value columnonly contains formulas.
Columns: Calculated Properties tables have three columns:
N
V
U
The first column contains the calculated property's name. We can make this name an Excel Name in which case itmust be unique within the model.
The second column contains the calculated value.
The third column contains the value's Units of Measure, Format String if value is a compound value like a date or GPS coordinate, or Code Description if value is a code.
Process
Purpose: Provide the calculation engine for our model. Process tables are made entirely of formulas or data from Power Query tables.
Complex models will have multiple process tables in which case some of the formulas pull values from other process tables.
Columns: Process tables have at least two columns but usually several more than that.
#
fx
fx...
The first column contains the item's ID (#) which is automatically assigned by the formula: =Row()-Row([#Headers]).
The second column is usually a period date or item name.
Subsequent columns contain additional formulas required by our model
This is where models can get dynamic because process tables can grow or shrink to accommodate as many periods and item instances as needed without requiring formula changes. Later on we will semi-automate growing or shrinking tables using Power Query and later still we will show some simple VBA to fully automate this.
Output Tables
These tables present results to our users. They allow users the ability to 'play' with results without the possibility that they will invalidate model calculations.
Pivot Tables
Purpose: Provide a place for users to sort, filter, or graph process table results without any chance of violating our model's integrity.
Columns: Varied.
Integrity Checks
Purpose: Alert everyone to model problems.
Columns: Integrity Check tables have two columns.
Ăľ
C
Contains a formula to check for some error. The formula should return TRUE (passed check) or FALSE (did not pass check). We then apply conditional formatting setting cell backgrounds to green when TRUE and red when FALSE
Contains countermeasure instructions on what to do when check failed and may have the text hyperlinked to where the problem is or can be remidied.
Lab
Let us see how to use some of these table types.
Payton's new requirements need to compare at least three loans. Our model must be able to expand beyond 3 to some undetermined number. for each loan we can have different fees, terms, and rates This requires changes to our properties table, the addition of an item table, and a rewrite of our process table.
Property Table
Most of what was in our properties table will be moved to a loan items table leaving just a few entries.Modify your properties table to look like this. Be sure to apply the appropriate styles, number formats, data validation, and units of measure.
Formulas and Styles Specification
Data Validation Specification
Item Table
Item tables group multiple instances of one thing. This model's item table will hold our loan properties. Each loan will have its own record.
Create an item table using the image at right and specifications listed below as guides. Name it tblLP: Be sure to put the UOMs above numeric columns needing them.
This table will expand or contract to accommodate as many loans as our customer wants. There are a few ways to automate expanding and contracting this table but for now we will handle it manually which requires a special formula in the ID column:
=Row()-Row([#Headers])
This nifty little formula generates row numbers for our table. In English it reads:
Formula Fragment
Row()
Row([#Headers])
Description
This formula's cell row
This formula's table header row
Subtracting the header's row # from the formula's row # yields the table's row # which we will use as our Loan ID. Now remember that when tables expand column formulas expand with them so no matter how many rows we add to this table we will always have a Loan ID for each loan.
Formulas and Styles Specification
Data Validation Specification
Process Tables
Process tables only contain formulas. They are our 'calculation engine'. For this model we need a record for each loan as shown below.:
This table will also expand or contract to accommodate as many loans as our customer wants. As before, there are a few ways to automate expanding and contracting this table but for now we will handle it manually. Because we are handling this manually we will need the ROW() function like we did in tblLP and we will make repeated use of INDEX(). Here is one of them:
=INDEX(tblLP[Interest Rate],[@[Loan '#]])
Create the process table as shown. Name it tblProcess. Use the Formulas and Styles Specification table as a guide. Be sure to apply units of measure above columns containing numeric values. Add as many rows as indicated by the Loans property in the tblMP.
Formulas and Styles Specification
Assignments
Complete Lab
Complete Quiz
Quiz
What are the advantages of using tables?
They improve model structure by grouping elements into an Excel object
They are automatic dynamic ranges allowing us to grow our model as needed
They auto extend formulas, styles, and number formats
They provide automatic naming
They offer banded rows and automated total rows
The can front end Power Query, Power Pivot, and Power BI
All of the above
None offer the above
What is THE difference between a property and an item table type
UOM column
Multiple instances of something
Formulas
What is THE distinguishing factor for a process table type?
Multiple Instances
Nothing but formulas
INDEX() formula
Which table type would we use to support multiple scenario entries?
Properties
Labels
Items
Constants
Rates
Validation
Process
Integrity Checks
Which table type is sole purpose is to add transparency to formulas?
Properties
Labels
Items
Constants
Rates
Validation
Process
Integrity Checks
Which table type must be sorted?
Properties
Labels
Items
Constants
Rates
Validation
Process
Integrity Checks
Which table type confirms things like we have the right number of records in our tables?
Properties
Labels
Items
Constants
Rates
Validation
Process
Integrity Checks
Which table type makes sure user entries are one of a select list of valid values?
Properties
Labels
Items
Constants
Rates
Validation
Process
Integrity Checks
Which Excel function is best for bringing item table values into a process table?
VLOOKUP()
INDEX()
OFFSET()
Which Excel function is best for providing item IDs?
ROW()
COUNTA()
+ operator with reference to the cell above
Which table types do we useFORMULAS > Defined Names >Create form Selectionover first 2 columns?
Properties, Labels, Constants
Properties, Items, Validation
Process, Rates, Integrity Checks
Summary
Table types help us structure our models by forcing us to think about which properties apply to everything, which properties apply to instances of something and tables that are strictly for calculations which must be protected.