| | | |
| Background
|
|
|
Parliament adopts a budget where funds are appropriated for governmental organisations
(agencies) for various purposes.
Each agency is expected to perform its tasks within the limits
of funds it is vested with.
In order to follow and control the use of funds agencies establish official accounts
for appropriated funds in accounting systems.
|
|
|
Agencies may wish to follow use of funds in more detail than
on the level of official account codes.
They can extend the official account codes [1].
E.g. if the original account code is 030201 (Operational expenses),
based on it the agency can build such codes as 0302011 (Salaries),
0302012 (Procurements), 0302013 (Travelling) etc.
During the fiscal year accounting entries are made
using these detailed account codes.
|
|
| Audit problem
|
|
|
An auditor is expected to assess whether use of the aforementioned funds
has complied with the budget limits.
A prerequisite for the assessment is comparing funds factually used
with budget appropriations.
The auditor has records where official account codes have been extended.
In order to compare their sums with the budget figures
he/she must truncate agency's records to the length of official accounts.
In the agency this is done by the accounting system.
The auditor wants to make this by means of audit software.
|
|
|
Let us assume, in addition, the following:
|
| - |
Some - not all - of the original account codes have been extended.
|
| - |
Extension of an account varies from 0 to 2 characters, never resulting to
account codes longer than 8 characters.
|
| - |
Official account codes together with budget figures are in 'BUDGET' (a file),
in a field 'ACCOUNT', length of which varies from 6 to 8 characters.
|
| - |
Figures about factual use of appropriated funds are in 'REALIZED' (a file),
where length of account codes vary from 6 to 7 characters [Example: 2].
|
|
| Solution #1
|
|
| 1. Two virtual fields are appended in the REALIZED. |
Two virtual character fields, 'ACCOUNT7' and 'ACCOUNT8',
both 8 characters long, are appended in 'REALIZED'.
They get values from 'ACCOUNT' by means of truncation with equations
@left(ACCOUNT;7) and @left(ACCOUNT;8), respectively.
|
| 2. 'ACCOUNT' in 'BUDGET' is joined three times
with the three corresponding ACCOUNT fields of 'REALIZED' |
2.1. First, 'ACCOUNT' in 'BUDGET' (secondary file)
is joined with fields of the 'REALIZED' (primary file).
The key for join is 'ACCOUNT' in both files.
The match is "All records in the primary file".
As 'ACCOUNT' field already exists in 'REALIZED', the field 'ACCOUNT'
that existed in 'BUDGET gets a name 'ACCOUNT1' in the resulting 'JOIN' (a new file).
|
| 2.2. Next, 'ACCOUNT' in 'BUDGET' (secondary file)
is joined the fields of 'JOIN' (primary file).
The key for join is 'ACCOUNT7' in 'REALIZED' and 'ACCOUNT' in 'BUDGET'.
The match is "All records in the primary file".
As an 'ACCOUNT' and 'ACCOUNT1' already exist in 'REALIZED',
the field 'ACCOUNT' in 'BUDGET' gets a name 'ACCOUNT2' in the resulting 'JOIN1' (a new file).
|
| 2.3. Last, 'ACCOUNT' in 'BUDGET' (secondary file)
is joined with the fields of 'JOIN1' (primary file).
The key for join is 'ACCOUNT8' in REALIZED and 'ACCOUNT' in 'BUDGET'.
The match is "All records in the primary file".
As an 'ACCOUNT', 'ACCOUNT1' and 'ACCOUNT2' already exist in 'REALIZED',
the field 'ACCOUNT' in 'BUDGET' gets a name 'ACCOUNT3' in the resulting 'JOIN2' (a new file).
|
| 3. A virtual field combining values of the three ACCOUNT fields is
appended in 'REALIZED' |
A virtual character field, 'ACCOUNT_B', length 8 characters, is appended in 'JOIN2';
its equation: @if(ACCOUNT=ACCOUNT8; ACCOUNT8; @if(ACCOUNT=ACCOUNT7;
ACCOUNT7; ACCOUNT)) [3].
|
| 4. Summarization of 'REALIZED' |
The fields of 'JOIN2' containing transaction amounts are summarized
in terms of 'ACCOUNT_B'. The new file 'SUMMARIZATION' contains amounts
about realized use of funds.
|
| 5. A file for comparision |
Finally, 'SUMMARIZATION' (primary file) in joined with 'BUDGET' (secondary file).
The resulting new file 'JOIN3' has the following fiedls;
'SUMMARIZATION': 'ACCOUNT_B' and a field containing balance of funds used;
'BUDGET': 'ACCOUNT' and a field containing the budged figures.
When the match takes place using "All records in both files"
all relevant accounts will ne included in 'JOIN3'.
|
|
|
| Solution #2
|
|
|
Less work-intensive solution to the same problem is innovative application of
'ImpAny' macro.
|
|
[1] Naturally they must be able to report on the level of official account codes;
this requirement is fulfilled with reporting functionality of the accounting systems.
[2] Some examples about extending official account codes.
| Account code in BUDGET file |
Account code in REALIZED file
|
| 030201 (Operational expenses) |
030201 (Operational expenses)
0302011 (Salaries of staff)
03020111
(Salaries of permanent staff)
03020112 (Wages of temporary staff)
03020113 (Remunerations)
03020114 (Social security expenses)
03020115 (Other staff expenses)
0302012 (Procurements)
0302013 (Travelling expenses)
0302014 (Other expenses)
|
030202 (Constructing buildings)
0302021 (Scheme 1)
0302022 (Scheme 2) |
030202 (Constructing buildings)
0302021 (Scheme 1)
03020211 (Project A in scheme 1)
03020212 (Project B in scheme 1)
0302022 (Scheme 2)
|
030203 (Aid schemes)
0302031 Aid scheme 1)
0302032 Aid scheme 2) |
030203 (Aid schemes)
03020311 (Aid scheme 1, part A)
03020312 (Aid scheme 1, part B)
0302032 (Aid scheme 2)
|
|
|
|
[3] This means that longer codes must be preferred in joining.
If no corresponding code with maximal length exist, the next longest code is preferred
and so on.
|