Embedded Files
Inputs contain all values that have no precedents. I would say they do not contain formulas but some people create inputs like this: =1. Technically, that's a formula; but, it has no precedents. Inputs can be created by modelers, external data inputs, or end users.
Modelers as part of model configuration. These are things that are used by the model and never change (while in use by end users). Because they never change, they are constants. These things are categorized as named constants, named labels, data validation tables, and configuration settings that customize a model template for a particular situation.
Imports from external systems which can include actuals, historical records, stock values, exchange rates, etc.
End users which can include basic model assumptions such as start date, number of periods, rates, etc. End users may also set item/dimension instance values such as each product's production cost, or each region's delivery costs.
Above is the Constants worksheet to our 3 way model example. It contains two tables that, at least in this model, only the modeler should change. I segregate modeler inputs from end user inputs by worksheet. That usually means I have a modeler inputs worksheet, and one or more end user inputs worksheets. This allows me to, if desired, hide values from end users, and to apply appropriate protection levels to worksheets preventing unintended or unauthorized changes that would invalidate auditor certifications.
At the end of this section you will know what the various modeler inputs are, how to organize them in tables, and how to easily name their values so we can use them transparently in formulas.
Named Constants Table
Named Constants Table
Many model standards frown upon literals used in formulas. We do as well. To be totally TBM compliant we replace formula literals with named constants. The constants in this table include:
DPY which is an abbreviation for Days Per Year. Wherever we need the days per year in a formula, we will use DPY instead of 365
Tolerance is used by integrity checks when comparing two calculated totals. Excel sometimes adds tiny fractions to calculations which we must accept when determining if two calculations equal.
Periods is how many periods this model covers. In some cases we may want to move this to the end user inputs section so they can add to the model as desired.
Start Year is the year for period one. If the imports were tied to a refreshable power query, we may want to move this to the end users inputs section so they can determine how much history to load.
Forecast Period Start is how many years from start date to use formulas rather than actuals (imported data).
Debt Opening is the opening balance of our debt control account. To make our model more flexible, we should consider importing this from our ERP instead of keying it
PPE Opening is the opening balance of our Plant Property and Equipment control account. To make our model more flexible, we should consider importing this from our ERP instead of keying it
NOTE!
To create named constants we:
Place values in a Properties Table and label them. A Properties Table is a special type of Code Table. Properties tables have three columns. The Name (which is the code), Value, and UOM (Units of Measure).
Create all of these names at one time using Formulas > Create from Selection or keyboard shortcut Ctrl-Shift-F3.
For more on this subject click Cell References and Names.
Named Labels Table
Named Labels Table
Labels are critical to model transparency. where transparency is defined as:
Level of Effort required to access information
Values without labels are data. For example, if we see a value 12, do we know what it is? Is it a inches in a foot? Months in a year? Donuts? To understand what 12 is, we need context. If we add a label like "December", 12 becomes information because we now have the context to the data and understand what it means. Values with proper context are transparent because by just looking at the label and value, we have access to information. Values without proper context are opaque because our access to information is cutoff.
The Named Labels Table is a code table with just two columns: Name (which is the code), and Display (what will become the label where ever it is needed).
Like our constants table, we use keyboard shortcut Ctrl-Shift-F3 (Tab: Formulas > Button: Create from Selection) to create named ranges matching this table's Name column and pointing to the associated cell.
Once the labels are entered and named, where ever we have dates we can label them by placing =Date_Format above the table column so users will know how to read dates. Without it, will they now what 3/1/2022 is? March 1st or January 3rd?
Pivot Format Spacers Table
Pivot Format Spacers Table
This table provides blank lines in Pivot Tables which as used as Financial Statements.
The above are all the modeler inputs used in our 3 way model example. In general, the first two tables are in every project. The Pivot Format Spacers table is only used when spacing is required by Financial Statements created in PivotTables. Below are some common tables found in a Constants worksheet.
Validation Tables
Validation Tables
This model does not have any user inputs that should be restricted to a list. But if it did, we would place all validation tables in the Constants worksheet and they would look like the months table at right. Validation tables typically have one or two columns. The first column contains the code which is almost always what is contained in the data validation list. If needed, a second column contains a description. It is also possible for validation tables to contain additional attributes. In the case of a Months table we might add a seasonal demand factor.
Configuration Tables
Configuration Tables
This model does not have any configuration tables. In projects that include VBA, I typically have several tables that instruct VBA how to do things like navigate between worksheets, expand tables, position charts, add drilldown, control who has access to what, and more. At right is an example configuration table for navigation. It lists the worksheet's name and a more user friendly name that end users could select from a ribbon dropdown.
Page updated
Google Sites
Report abuse