SUT - Balancing

Since the SUT is an elaboration of the production account presenting details by both products and activities, we can compare supplies and uses by both product and by activity:

Balancing a SUT can be defined as making both the activity balances and the product balances zero at the same time for all activities and products. The RAS procedure can be used for this but only if discrepancies are small. If this is not the case manual edits will need to be made to the individual parts of the SUT. We will now show a number of different ways this can be done in NA Builder.

Single data edits

First, we can edit single data cells. After selecting the cell and starting the data editor (with <ctrl>E) we can e.g. raise the number with 5%:

The NA Builder coding for this cell can be seen in the caption: {Use_C; Use; CPA_A02; HH} i.e. it resides in the table in sheet “Use_C”, in partition “Use”, with product code “CPA_A02” and transaction code “HH” (for final household consumption).

Note that the edit can also be made in another sheet, keeping the original value in place. This would make sense only if there is a “total” sheet, where the data sheet and the edit sheet are summed up. Once the edit has been made, it can be “undone” by clicking on “Last”:

Instead of a single cell, the edit can also be made on a total (formula) cell:

In this case all cells in the (intermediate consumption = IC) row will be lowered with 5%.

This also works for the whole IC table:

Here all cells in the IC table will be adjusted on a pro rata basis so that the IC table sum will become 2150000.

Rule based edits

Processing a big table like IC can be done much more efficiently (and hence faster) with a PRORATE rule which can be carried out with the “rule wizard”, which can be started from the “Rls” application sheet and which guides one through the following three screens:

If it is foreseen that the rule will be used again, the rule can be saved as well.

The PRORATE rule is very versatile and can be used in many different ways. It distributes on a pro rata basis a given range (in the first argument) using the distribution in given in the second argument to a third range (in argument 3); in case this last range is not specified the results will go to the first range; the value in argument 4 specifies what will happen with the results: 1 = add, -1 = subtract, 2 = add differences, -2 = subtract differences, 3 = replace with differences); when argument 5 is set to 1 results are subtracted from te first range.

Other useful data editing rules are:

ABSORB: Distributes on a pro rata basis a given range (in the first argument) over a series of other (target) ranges (specified in the remaining arguments, as many as needed); for each of the target ranges the change can be added or subtracted or the original value replaced with the "operation" parameter (operation = 1, -1 ,3 ); with the "sheet" parameter the results can be written to the same range on a different sheet, keeping the original range fixed. An example target specification is: "Tbl2!E5:G5 [3] [Tbl3]" where the range from sheet Tbl2 is updated in sheet Tbl3.

DISAGGR: This rule will enable data at a particular level of aggregation (say NACE / CPA 2-digit) to be disaggregated proportionally to a lower level of aggregation (say NACE / CPA 3-digit), using the correspondences in the bridge table that connects both levels of aggregation for given "model" ranges with the bridged sheets in arg.2 (aggr.) and arg.3 (details); the range to disaggregate is in arg.1.; the results will go to arg.4.; the parameters in arg.5 and arg.6 are the same as in the pro rate rule.

Here is an example:

Another rule which uses correspondences as contained in a particular bridge is the RECODE rule, which applies the bridge in arg3 to the data in arg1 and writes the results in arg2. In effect this sets up the sheet in arg2 as an AGGR sheet and converts the SUM formulas to values and makes the sheet a DATA sheet.

Batch edits

If not all cells of the IC row need to be adjusted, but only some of them, there is the “Batch edit” option. An example for the CPA_A02 row, where we have selected all cells in the row greater than 1000, sorted in descending order. There were three such cells found, and we can pro rate the new value 2150 over them as follows:

View based single edits

Another method for balancing this row is to make a “view” for the row (with <ctrl>W):

In this automatically created presentation (on sheet “Vw”) we can directly edit individual cells with the data editor (as before), but now the cell in the underlying sheet Use_C which is viewed here will be edited (and the view formula refreshed). In this way individual cells in any row (or column) can be edited.

View based batch edits

In case edits need to be made to more than one cell in a view, we can use the view editor, e.g. to allocate the value 2150 over the four selected cells:

Views can also be “linked”, e.g. presenting similar rows from different tables. The view editor will then show the data for all linked rows (or columns). Particular formulas (in any sheet) can be added as “watch” so that the effect of data changes can be monitored without leaving the editor.