Model Structure

Table Based Modeling (TBM) mimics large scale computer systems that handle problems too complex or too large for Excel. This allows TBM to inherit those systems' unmatched flexibility making complex software possible. Foundational to these systems is the IPO model structure. 

The input–process–output (IPO) model, or input-process-output pattern, is a widely used approach in systems analysis and software engineering for describing the structure of an information processing program or other process. Many introductory programming and systems analysis texts introduce this as the most basic structure for describing a process.[1][2][3][4] 

From: IPO Model - Wikipedia. Retrieved 4/7/2022: https://en.wikipedia.org/wiki/IPO_model

IPO, in the software context, stands for Input-Process-Output model. In Excel's implementation of the IPO model, inputs should be kept separate from processes to prevent accidental change of formulas. And outputs should be kept separate from processes to allow any number of lists, reports, or charts over the same calculation set. Essentially, the process is the single version of truth that all outputs pull from. Another way to look at it is process does the thinking without regard to appearance and outputs formats process results for humans. 

At the end of this section you will understand the IPO model and TBM's step-by-step approach to implementing it using Excel's objects (workbooks, worksheets, tables, etc.). 

IPO in the Excel Context

In the Excel context we can generally describe Inputs, processes, and outputs as these structural concepts:

Inputs

Inputs are cells with dependents (other cells reference input cells) and no precedents (input cells do not reference other cells). This includes:

Named Constants

These are values used in formulas for things that simply never change. Examples include: days in a week, months in a year, Pi, etc.

Named Labels

These are labels that repeat within the model. Examples include currency (USD, GBP, EUR, etc.) and date formats (MM/DD/YYYY, YYYY=MM-DD. etc.) 

Named Properties

These are individual values used in formulas for things that are not supposed to change in this model. Examples might be: hours in a workday, periods in the fiscal calendar, etc. 

Imported Tables

These are tables from external systems. Examples include actuals, currency conversion rates, sales history, etc.

Assumptions

These are individual values keyed by the user. Examples include project start date, project duration, opening balances, etc.

Items and Item Attributes

These are sets of values keyed by the user for things which can have instances. Examples include; Products and their attributes; Regions and their attributes; customers and their attributes, etc.

Processes

Processes are cells (or queries) with dependents and precedents. This includes:

Property Calculations

These are formulas that calculate a single result. Examples include the project end date calculated from end users' entered assumptions of project start and project duration. 

Dimension/Item Calculations

These are formula sets related to all instances of an item or dimension. An example might be a product's labor cost calculated from a standard labor rate (property) and each product's standard labor hours (items). 

Association Calculations

These are formula sets that calculate the combinations of two or more item sets. An example might be a product's profit calculated from each product's costs subtracted from a market sector's price less a region's distribution costs.

Period Calculations

These are formula sets that calculate values over time. An example could be projected sales for each month based on a customer's average purchases multiplied by a seasonality factor.

Outputs

Outputs are cells (or queries) and other objects (like PivotTables or PivotCharts) that have precedents but no dependents. This includes:

Statements

These are dynamic arrays, queries, or PivotTables arranged in a specific order and format required for formal reporting. Examples include Profit and Loss Statements, Balance Sheets, and Cash flows. 

Summaries

These are dynamic arrays, queries, or PivotTables that can be sliced or sorted interactively by the end user. These are most often used to frontend pivot charts in dashboard visualizations. 

KPI/Financial Ratios

These are single value formulas to convey the overall desirability of the model. These could include: debt service coverage ratio; earnings per share, operating ratio; etc.

Charts and Slicers

These are graphic visualizations of outputs. If a slicer is applied, these can be interactive and used for analytical activities. Visualizations can include: waterfall charts, tornado charts, pie charts, heatmaps, etc. 

Structural Concepts vs Structural Objects

We just learned about the IPO's structural concepts. In TBM we take those structural concepts and place them in Excel's structural objects. Excel's structural objects include:

Cells

Cells are the 'atomic' (cannot be subdivided) object in Excel. We can group cells into other objects such as...

Tables

Tables are groups of cells arranged in rows and columns that, together, represent a set of something. A set can be people, companies, products, invoices, etc. Each row represents an instance of the set. An instance would be a person, a company, a product, an invoice. Each instance has attributes. If our table contains people, then each person would have attributes such as their name, age, pay rate, etc. Attributes are arranged in table columns. 

Data Model

Tables can be related to other tables in the Data Model. An example might be relating a customer table to a sales table and by relating these two tables we can group sales by each customer's region (assuming region is an attribute of each customer). 

PivotTables

PivotTables are groups of cells that aggregate detail data from a table or from multiple tables in the data model 

Pivot Charts

Pivot Charts are PivotTable visualizations.

Worksheets

Worksheets are containers for cells and any of the other objects mentioned above.

Workbooks

Workbooks are containers for one or more worksheets.

Placing IPO Concepts into Excel Objects

For Excel modeling, we typically add two more sections: Documentation and Integrity Checks. We then organize all conceptual structural elements into various Excel objects as described below.

Documentation Section

This section typically contains a Cover worksheet and a Table of Contents worksheet. The Table of Contents is contained in an Excel table. Following this section is the Inputs section.

Inputs Section

This section typically contains:

Modeler's inputs worksheet which contains tables for named constants, named labels, named properties, and Data Validations.

Imported data worksheet  with each worksheet containing one import table.

User inputs worksheet(s) where user entered assumptions are in one table and user entered items are in their own table and may be on the same or other worksheets.

Process Section

This section is our calculation engine. It contains as few worksheets as practical to facilitate formula auditing. Process worksheets contain nothing but labels and formulas. Absolutely no literals or inputs are allowed. On these one or more worksheets are:

Calculated Properties table which contains formulas that calculate a results from assumptions. An example might be the calculated project end date based on the assumed start date and the assumed duration. Several such calculations can be placed into this table and then the results named (using Ctrl-Shift-F3) so they can be used transparently in other calculations or outputs.

Dimension/Item Calculation tables which contain calculations for a single item set. An example might be a product's labor cost calculated from a standard labor rate (property) and each product's standard labor hours (items). 

Association Calculation tables which contain calculations for related tables. An example might be a product's landed cost calculated from the product's (item) cost plus the product's weight times a Region's (item) shipping rate.

Period Calculation tables  which contain formula sets that calculate values over time. An example could be projected sales for each month based on a customer's average purchases multiplied by a seasonality factor.

Outputs Section

This section typically contains:

A worksheet for each formal statement PivotTable

A worksheet for summary PivotTable, queries or dynamic arrays, 

A dashboard worksheet containing KPIs, ratios, charts and slicers.

Integrity Checks Section

This section contains a single worksheet  where all integrity checks can be found. This worksheet contains two tables. One is for integrity check properties, and the other is for integrity checks tests and results.