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

This is under construction.

Click this link for the course's start

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

1. Re-estimate your project and complete a new SOW
2. Open your model and save it under a new name
3. 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

1. Read Using Structured References with Excel's tables
2. Read Jon Pelteir on Structured Referencing
3. Complete Lab
4. Complete quiz

Quiz

1. SOWs identify:
  1. What was agreed upon
  2. What customers ask for is a change or part of the original project
  3. What determines that a project is complete
  4. All of the above
  5. None of the above
2. Which option creates a table?

- 1. CTRL-T
  2. CTRL-L
  3. Ribbon option Insert > Table
  4. All of the above
  5. None of the above

1. Table names must be unique to...

- 1. Each worksheet
  2. The entire Workbook

1. There can only be one table on each worksheet.

- 1. TRUE
  2. FALSE

1. Tables are fully dynamic. We can add as many rows or columns to them as we wish.

- 1. TRUE
  2. FALSE

1. Formulas, formats, conditional formatting and data validation automatically extend to new rows.

- 1. TRUE
  2. FALSE

1. When we add a new row we must use Excel's Refresh All option to update table total rows.

- 1. TRUE
  2. FALSE

1. Which structured reference identifies column headings?

- 1. Headings
  2. #Headings
  3. #Headers
  4. [#Headers]

1. An @ symbol in a structured reference indicates

- 1. The same table column
  2. The same table row
  3. 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.

Self-Documenting Formulas

Introduction

Input, Item and Process Table Types

Page updated

Google Sites

Report abuse