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

Under construction.

Click this link for the course's start

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.

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.

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.

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:

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:

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:

[#]

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:

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...

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.

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

1. Complete Lab
2. Complete Quiz

Quiz

1. What are the advantages of using tables?
  1. They improve model structure by grouping elements into an Excel object
  2. They are automatic dynamic ranges allowing us to grow our model as needed
  3. They auto extend formulas, styles, and number formats
  4. They provide automatic naming
  5. They offer banded rows and automated total rows
  6. The can front end Power Query, Power Pivot, and Power BI
  7. All of the above
  8. None offer the above
2. What is THE difference between a property and an item table type

- 1. UOM column
  2. Multiple instances of something
  3. Formulas

1. What is THE distinguishing factor for a process table type?

- 1. Multiple Instances
  2. Nothing but formulas
  3. INDEX() formula

1. Which table type would we use to support multiple scenario entries?

- 1. Properties
  2. Labels
  3. Items
  4. Constants
  5. Rates
  6. Validation
  7. Process
  8. Integrity Checks

1. Which table type is sole purpose is to add transparency to formulas?

- 1. Properties
  2. Labels
  3. Items
  4. Constants
  5. Rates
  6. Validation
  7. Process
  8. Integrity Checks

1. Which table type must be sorted?

- 1. Properties
  2. Labels
  3. Items
  4. Constants
  5. Rates
  6. Validation
  7. Process
  8. Integrity Checks

1. Which table type confirms things like we have the right number of records in our tables?

- 1. Properties
  2. Labels
  3. Items
  4. Constants
  5. Rates
  6. Validation
  7. Process
  8. Integrity Checks

1. Which table type makes sure user entries are one of a select list of valid values?

- 1. Properties
  2. Labels
  3. Items
  4. Constants
  5. Rates
  6. Validation
  7. Process
  8. Integrity Checks

1. Which Excel function is best for bringing item table values into a process table?

- 1. VLOOKUP()
  2. INDEX()
  3. OFFSET()

1. Which Excel function is best for providing item IDs?

- 1. ROW()
  2. COUNTA()
  3. + operator with reference to the cell above

1. Which table types do we useFORMULAS > Defined Names >Create form Selectionover first 2 columns?

- 1. Properties, Labels, Constants
  2. Properties, Items, Validation
  3. 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.

Dynamic Modeling

Introduction

Model Integrity Checks

Page updated

Google Sites

Report abuse