SUT - Structure
An important motivation for the flexibility of NA Builder is the recognition that NA structures are typically not fully fixed. Take the example of the SUT. This structure consists of two tables, the supply table and the use table, and each table consists of a fixed overall structure:
But within this fixed structure there are many elements that can vary between SUTs, such as:
The number of activities and products
The kinds of taxes and margins
The level of detail in the consumption and capital formation categories
Templates
Na Builder allows for just such flexibility. Its concept of a “template” captures the idea of a fixed structure. In fact, the application comes with pre-defined templates for the supply and use tables, e.g. the one for the use table can be inspected in the template builder:
Each element in the black partition grid represents a partition, i.e. a “simple” table containing a classification on the header (columns) and a classification on the stub (rows). As can be seen, the selected partition “IC” (intermediate consumption) contains a “new classification” with 1 element on the stub and the Nace_2d classification with 85 items on the header. The latter classification was already available and had been “attached” to the template, by selecting it in the list box with available classifications and then clicking on the button “Add to header”. The alternative way is illustrated for the stub classification for which no pre-existing classification was available, and which can be created here directly by specifying the desired number of classification items.
Similarly, other classifications could have been specified in advance, e.g. for CPA products, for the types of taxes and margins and for the types of consumption and capital formation categories. These classifications can be similarly attached to the corresponding partitions in the template for the use table, as for the case of intermediate consumption. Note that the formula partitions (denoted by “F” in the partition grid) - containing the various aggregation sums and other formulas - have already been made and since these do not depend on the details of the classifications, these will remain intact whatever classifications are selected.
Specifying a SUT in NA Builder therefore consists of:
Specifying the relevant classifications
Specifying a suitable template with all the formulas in place (in the above case selecting a pre-defined one)
Attaching the classifications to the data partitions of the template
Implementations of templates in sheets
Once this is done, one or more actual SUT tables can be made by the program by “translating” the abstract template with its data and formula partitions into concrete Excel tables with sum formulas. This process consists of two steps. First, specifying a sheet definition, e.g.:
Here, the above use table template has been named “tUse” (and the template for the supply table “tSup”). The actual use table is defined as “Use_Init”. It is a data sheet based on the use table template and it uses the supply table (“Sup_Init”) as “parameter”, so that total supply from this table can be used in the use table to show the discrepancies between the two tables. Parameters are specified as part of the template, e.g.
t_1_2 'Total' : + <{1}Data_1_1>
Here formula partition "t_1_2" of template tUse carrying the label "Total" in implemented sheets will refer to partition "Data_1_1" of the sheet passed as parameter 1 which is "Sup_Init" in the above example. If there were a second parameter (passed to the template in the cell to the right of the one containing "Sup_Init") this would be known to the template as "{2}". And so on for any number of parameters.
Once the two tables have been specified, they can be “translated” by the program into Excel tables in a process called “implementation”. These are the actual tables where data can be entered and edited in the usual way.
The same templates can be used for other sheet specifications as well. E.g.:
Here the tUse template is also used for the sheets Use_Defl, Use_Man and Use_Tot. Note that the Use_Tot sheet is a sheet of type “Calc” which - after implementation - will contain the appropriate Excel sum formulas adding the Use_Defl and Use_Man sheets, both of which are again data sheets.
Formulas
Although the Use_Defl sheet is a data sheet the idea of this sheet is that it contains deflated values of the use table. It could have been made into a Calc sheet as well, with the deflation formula specified in a similar way as the Use_Tot sheet sum formula earlier. Instead, we can achieve the same with regular formulas, e.g. the one for the intermediate consumption table which in the formula editor is given as in “NAB” (NA Builder) or “Xls” (Excel) format :
Note that formulas in NA Builder have names and are specified as combinations of sheets (Use_Defl), partitions (here “IC” has been renamed into “P2”, the SNA transaction code for IC), the row code of the CPA classification (CPA_A01) and the column code of the Nace classification (A01). Note also that the formula specification has the values 0 to copy down and to the right respectively. This means that the formula will be set up for the whole IC table, no matter how many product and activity codes there are. This is the reason why classification codes rather than Excel addresses are used: to make the formula independent of the chosen classification. When classifications are changed, the formula can remain as it is.
The idea of the Use_Man sheet is to provide a place for data edits. Changing values in Use_Init directly would remove the original values, whereas in this setup the final values in Use_Tot can always be traced back to the original values in Use_Init and the edits (as changes to the original values) in Use_Man. This idea can be expanded with separate sheets, either for different data sources, for data for different institutional sectors or for different data editing rounds. Rather than fixing a setup once and for all, the flexibility of NA Builder allows for many different designs, all based on the same template tUse.
Suppose that after editing we have built up the final Use table in Use_Tot. The activity totals of IC may then need to be “constrained” to given (published) totals from annual national accounts. We can again use formulas (not shown here) to achieve this in a new sheet Use_C. If the use table has been balanced in sheet Use_Tot, this is unlikely to be the case after the constraining. Suppose we carry out another round of manual balancing in sheet Use_man2, where we at least remove the largest (row) discrepancies. To remove the final discrepancies, we use the RAS procedure. To implement this strategy, we could specify the following sheets, again all based on the tUse template:
Here the Use_man2 sheet has been added to “absorb” any remaining small discrepancies after the RAS procedure.
Aggregating sheets
Once data have been loaded or edited, it may be useful to look at the data at more aggregated level. Sheets such as Use_ToTFin can be aggregated using “bridges” which combine “correspondences” for products and activities in the case of the SUT. An example is the following bridge B1, which combines correspondences for activities and products:
A correspondence such as “A_Detailed>A65” is a mapping from the detailed classification “A_Detailed” to the aggregated classification “A65” assigning to each code from the detailed classification a code in the aggregated classification.
Once the correspondences have been specified and collected in the bridge B1 the following sheet specification can be used to specify sheet “Use_A”, which – after implementation – will contain the appropriate sum formulas to show the use table at the aggregated level:
Aggregation and calculation sheets may also be mixed. An example is the following SUT compilation structure. This structure consists of a number of data, calculation and aggregation “layers” for both the supply table and the use table. Each layer is implemented in a separate sheet representing the table at a particular level of detail:
5 digit = 5 digit CPA, 3 digit NACE (“level 5”)
3 digit = 3 digit CPA, 3 digit NACE (“level 3”)
Division = 2 digit = 2 digit CPA, 2 digit NACE (“level 2”)
Section = CPA at section level, NACE at section level (“level 1”)
A6 = CPA at P6 level, NACE at A6 level (“level 0”)
The prototype implementation for this example is specified as follows:
The most detailed sheets in this example at CPA 5 digit level may become quite big, so if the total sheet "S5" is a regular Calc sheet it will contain lots of SUM formulas. Since the underlying data sheets "S5_1" and "S5_2" may only be sparsely filled, many of these SUM formulas will not be triggered and will remain zero. Hence, it is more efficient to only setup a SUM formula when needed. This can be achieved with a "CalcM" sheet.
Sheet Layout
Sheet definitions can contain layout definitions which allow for some elementary formatting of implemented sheets. In the example below the string "(BW72:CA73)[K1]" will assign a black color to this particular range (meaning it is "undefined" in the SDMX representation)
