Chapter 2: Architecting the input
In this chapter, we will understand in depth the right input architecture for financial models with dynamic arrays.
Input architecture is an extremely critical part of DA models. The decisions made at this stage determine whether a model can scale efficiently, remain stable under user interactions, and support the advanced calculation techniques discussed in later chapters.
​
A lack of dynamic front-end within spreadsheets makes it extremely difficult to satisfy the dual objective of a robust backend and a very friendly front-end. This chapter tries to balance the two so that the compromises neither affect the stability of the model nor be a major deterrent for user adoption.
​
Scope
The guidance in this chapter focuses specifically on practices where inputs are directly entered into the spreadsheet. While this guide encourages importing data directly from source systems and remains open to use of forms and PowerApps, which enhance user experience and control, these topics fall outside the scope of this version.
​
Certain standard practices for input, including keeping the input in a separate cell and distinguishing them with different colour codes, continue to be relevant while building financial models with dynamic arrays.
​
However, certain additional guidelines are relevant for DA models, which are outlined here.
Definitions and Terminologies
In the context of this guide, the input variables are classified into four categories. The terminology that is used, and their meaning are as under:
​
-
Scalars: These variables have only one value and is kept in a single cell. For instance, the WACC variable in most financial models are static across time and is, thus, an example of a scalar input.
-
Vectors or one-dimensional data: These variables have multiple values and are presented as a list of values either in a single row or single column. The size of the list can increase (or, in fewer cases, decrease). For example, when projecting long-term financial data, analysts may apply distinct sales growth rates to each forecasted period. As the number of periods under consideration increases, the list will increase in size. The forecasted sales growth rates, in this case, is a vector variable. Vectors are very common in financial modelling.
Exhibit 1: Example of vector data set
​ -
Multi-dimensional data: This refers to variables that have two or more dimensions, and the data can grow on any of the dimensions. For example, if an analyst needs to input bill of materials (BOM) data for a multi-product company, the input may spread across multiple rows and columns, as shown in Exhibit 2:
Exhibit 2: BOM – an example of a multi-dimensional data
-
​
​
​
​
​​​
​
​
​
​
​
​
​​
