Hands-on SUT

The purpose of this section is to introduce NA Builder to first-time users interested in working with supply use tables (SUT). This second case will set up a real (small) SUT.

To work through this case, the following files should be available:

• Course_Data.xlsx

• Course_example.xlsx

• Course_Settings.xlsx

These files should be contained in the folder “\Course”. This folder should be contained in the folder where the NA Builder application is present.

Case 2 - Working with an Existing Framework: A Simple SUT

Load the settings as prepared in the settings specification “COURSE” (don’t forget to first remove the old framework and to remove all settings and data as before).

Select this name in the combo box, then verify that the correct description, file name and file path are in the blue cells. Then click on “Load settings”.

Check out the following structural elements.

Templates

These can be found in the sheet “Templ”. There are 9 templates specified. One can use the “edit” feature to inspect some of them (place the cell pointer on a template name first).

Partitions

Data partitions from the templates are specified in the sheet “Part”:

Classifications and classifications items

Classifications used for the data partitions are specified in the sheet “Clas”:

Classifications items are specified in the sheet “ClasIt”:

Implementations

Stored in the sheet “Impl”, which is the central location where the actual sheets are specified. Use “Edit” to inspect the sheets.

Note on sheet types: sheets can be of the following types::

• Data = data sheet

• Aggr = aggregation sheet (using bridge)

• Calc = calculation sheet, using a sheet calculation formula)

• CalcM = same as Calc, but sheet formula only setup if there is data in the formula scope (i.e. no formula giving zero)

• Ext = external: sheet including formulas defined externally (e.g. for complicated sector accounts) (“..” in front of a folder is shorthand for application folder)

• Blank = empty sheet

Set up this framework by clicking on “Create framework” on the “SB” sheet (this may take a while! Look at the statusbar on the lower left of the screen to check progress).

The sheets “Sup1” and “Use1” contain the SUT for period T-1, and “Sup2”, “Use2” for period T. Some balancing takes place for period T, resulting in the final use table in “Use2b”. GDP by the three aproaches and some additional information are given in the sheet “ChkCur” (based on an “external” template and a sheet of type “Ext”). Price indexes for the supply and use tables are in the sheets “PIdxS” and “PIdxU” respectively. The deflated supply and use tables are in the sheets “SupCP” and “UseCP” (sheets of type “Calc”). Some additional data needed for balancing the CP tables are in the sheet “UseCPadj”. The sum of “UseCP” and “UseCPadj” is in “UseCPfin” (sheet of type “Calc”). GDP by the three aproaches in CP and some additional information are given in the sheet “ChkCon” (same external template as before). Implicit value, price and volume indexes for the supply table are in “SVIdx”, “SPIdx” and “SQIdx”, and for the use table in “UVIdx”, “UPIdx” and “UQIdx”, all implemented by type “Calc” sheets.

Of course all these sheets are still empty. Data can be loaded as before (copy / pasting), but there is an automated way by using the sheet “Files” (part of the settings):

Data are in the datasets “Data” and “Data2”. Load these datasets to DataIn by clicking on “Import all”. Then on the sheet “DataIn” click on “Load DataIn into Framework”, as before.

A few new features have been used as well:

Ad hoc formulas

These are collected on the formula sheet “Frml” (formulas can be made in situ, and then captured with <ctrl>E). Note that formulas are not given in terms of EXCEL rows and columns, but in the format: sheet, partition, row clas.item, column clas.item.

Click on “Set up all” to implement the specified formulas.

Next to these ad hoc formulas there are also

• template formulas (the yellow formula partitions)

• sheet formulas (defined for type = "Calc" sheets)

Scripts

Some labels need to be inserted in the sheets “VwProd” and “ViewAct” where many of the above formulas go. This is done with a script containing a few LABEL rules. Go to the script sheet “Scpt”:

Place the cell pointer on the first script, and click “Play script”.

Special views

Check the sheet VwProd: here you see columns of the various tables (by product). To see how this works enter the value 3 into cell E6 (the third activity in the activity classification). This will generate all relevant columns for the third activity, construction.

Similarly, the sheet “VwAct” will give the various rows (by activity and other columns of the tables) for a particular product. Type 6 in cell E6 (the sixth product in the product classification) and you get the the rows for construction.

Note that the aggregations in the index sheets are according to the CP principles: Laspeyres volume indexes and Paasche price indexes. Verify that value indexes are equal to the product of volume and price indexes.

Note that these views are entirely generated by formulas and these are unrelated to the views in sheet "Vw" which can be generated with <ctrl>W.

Aggregations

Finally, check out the sheets “SAggr” and “UAggr”. Here the supply and use tables for T-1 have been aggregated to 4x3 formats, using “bridge” specifications on the bridge “Brdg” sheet:

These bridges make use of “correspondences” on the “Cor” sheet:

This is done using a type “Aggr” sheet definition on the “Impl” sheet.

Summary

The purpose of this case is not to show how to make structure and define a framework as contained in the settings file. This is development work. Rather we look at the program from a user perspective, The important steps are:

• Loading the framework from the settings file

• Setting up the framework (sheets, ad hoc formulas, label script)

• Loading the two datasets (first to DataIn, then into the framework)

• Insepcting the results

Of course all data work has been done, and the system is already balanced so there is nothing else to do. But in real life the user would have to do the balancing by him or herself. This can be done by manually changing data in the various sheets (meanwhile keeping an eye on the various summary views and implicit indexes). But for big tables (the example is rather small) it may be useful to use batch editing using special features of the program and dedicated rules which are available (and collecting them in scripts). More information on SUT balancing can be found here.