• Reduces the chance of inadvertent deletion of cells: The main technical risk with a scalar input cell is that it can get deleted and throw #REF! error, in linked cells. Preventing deletion of rows or columns reduces this risk.

Note: Readers should note preventing deletion alone is not fail proof for #REF! error. When a cell that is cut from elsewhere is pasted in an unlocked cell, it causes the same problem.

• Reduces ambiguities: Although unit conversion factors themselves do not vary, ambiguities can occur when these values are entered directly into a cell. For instance, multiplying a volume by 12 may not clearly indicate whether monthly figures are being annualised or quantities are being converted from dozens to units. By placing such constants in a separately labelled cell, the purpose becomes clear and transparent to the reader.
• Prohibits unwarranted changes: The purpose of keeping unit conversion factors in a separate protected sheet, away from standard input, is to ensure that people do not inadvertently change their values.

The other alternative is to directly store these values in a name under the Name Manager. Since the names are out of sight, there is less chance of inadvertent changes. But this reduces transparency of the model and does not fail proof any intentional manipulations of the variables.

• Easy access: Cell names can be easily called with help of IntelliSense from any sheet by the modeler without having to navigate to the source sheet.
• Improved readability: When a formula uses a descriptive cell name, it makes it easy for users to understand what the cell represents without having to navigate away to the source sheet and read the labels. For instance, let us say the WACC is in input sheet C8. An end user who is trying to understand the logic can easily understand what =1/(1+_WACC) is doing without having to navigate to the input sheet and checking what does C8 represent.
• Reduces vulnerability in programs: When you are referring to a cell in VBA or Scripts, references to cell names are not vulnerable when rows/columns are inserted or deleted. This improves integrity of the model.

Note: Naming cells has some drawbacks, too; users must follow clear naming conventions to prevent confusion, especially in models with many variables. This version of the document does not cover naming best practices.

• Improves readability: The default names given by MS Excel are non-descriptive. A custom name that is descriptive, therefore, would make it easy to understand a formula. For instance, a cell reference such as =IS_History[Gross Margin] clearly tells us that we are extracting gross margin from historical period and not forecasts.
• Easy access: When the names are descriptive, it is easy to recollect and type the table name directly in a cell, further aided by IntelliSense, without having to manually navigate to each table and select them.

For instance, let us say there is an IS_Table with historical financials with nine fields, including one for the timeline and another eight for eight different line items, we may insert a 10th column to check whether all the first 9 columns are filled with a formula such as this:

=COUNT(IS_Table[@Year]:[PAT])=(COLUMNS(IS_Table)-1)

This formula would return TRUE only if all the input columns are duly filled.

• Prevents stray rows from affecting calculation: When working with tables it is common that an end user may add additional rows to the table either inadvertently, or intentionally to serve as a place holder. The formulae referring to the table can filter out such stray rows using the validation column.

For instance, one may add a column in income statement to check whether all columns of balance sheet for the same period have been filled up.

• Allows synchronization of calculations: When a calculation requires data from multiple tables (say asset turnover ratio), the multi-table validation columns allow us to filter out data sets when corresponding data is not available in another data set.

For instance, a calculated PAT column helps analysts check the accuracy of entered financials by comparing computed number with reported number. Likewise, using a moving average of sales growth can guide arriving at a projection assumption. Including these calculations alongside input data can be beneficial.

• Balances user experience with rigid structure: Allowing calculated columns in input table to allow figures flowing elsewhere improves the end user experience while keying information despite the rigid architecture.

For instance, while technically it does not create an issue if all historical data including income statement, balance sheet, and cash flow items are kept in one table, modelers should consider keeping them in separate tables.

• Improves user experience during input: When data of multiple entities are mashed up in a single table, it is likely to be overwhelming to enter the input data in the table.

Note: This specific guidance pertains only to tables where input is fed. It does not concern itself with tables that are imported from external sources using any ETL tools.

For instance, in the segmental data illustration in Exhibit 6, each combination of geographic and business can be given a unique name. Another table can be created which provides details of what geography and business segment are present in the unique name. This is shown in Exhibit 7, where we have one “Segment table” that gives a unique name to segments along with its details. In the segmental data, the user would enter the unique segment name and have the system auto populate the geography and business segments using XLOOKUP.

Although the segmental data is not “Normalized” in the strictest sense as it does contain the redundant columns, this approach reduces the redundancy from input perspective.

• Balance user experience with rigid structure: Multi-dimensional data necessarily needs to be in long form format, which is laborious and needs redundant inputs. By normalizing the structure, we reduce the number of redundant input variables that need to be typed.

• Flexibility: Structured Tables have rigid structures and mixed nature data may not fit in easily. Tabulated scalars allow flexibility.
• Allows Intuitive flow: Other technically sound possibilities, such as organizing each nature of data in separate tables may affect intuitive flow for the end users. For instance, in the example in Exhibit 3, debt and preference shares details could have been kept in a structured table, while the weight of equity is calculated elsewhere, outside of the table. However, such an approach would reduce the intuitiveness of the flow, especially if the analyst is trying to optimize weights to minimize WACC.
• Easy to read: Tabulating these scalars together as a group rather than treating them as independent scalar variables make it easy for the readers to understand the variables better.

Important Note: Tabulated scalars do not allow dynamic referencing as well as a Structured table and is more vulnerable to user action such as inserting or deleting rows. The guide, therefore, recommends users to be very selective in choosing to use tabulated scalar structure over a Structured table and treat the structure as an exception than a norm.