Equation Editor
CCH Audit Automation incorporates a powerful equation editor that allows you to define any number of equations for each client. A master set of equations can also be created in each of the master packs and new clients will get a copy of these when they are created.
The system allows you to divide equations into three categories:
| Audit evidence | Equations based on the results for the year and other non-financial data that provide audit evidence. |
| Client data | Entries created to allow you to enter non-financial data relating to the client. These will not in themselves provide direct audit evidence but can be used as the basis of equations that will provide audit evidence. For example, number of sales persons, size of factory, etc. |
| Background data | Entries created to allow you to enter general non-financial data for use in calculations. For example, Retail Price Index, average wage, etc. |
Each category is represented by a tab in the Stored equation dialog.
To launch the equation editor, select Equation editor from [Analytical tools] from the Planning menu. The Stored equations dialog will appear listing any equations that are currently defined for the client.
Stored equation dialog
To view the list of equations recorded for client data and background data, click on the tab bearing its name and it will be brought to the front. As you extend your analytical review the list of audit evidence equations will grow in length. As can be seen, each equation is identified by a code and they are presented in alphabetical order. You can scroll through the list using the scroll bars and the navigation buttons. You can also jump to a point in the code list by typing the first few characters of a code in the Go to edit box. Finally, you can use the [Pick] button to search the list of equations by name.
You can also scroll the list to the right to see details of the formula and results for each equation.
From this dialog you can add, edit and delete equations, as well as display and print them. It is also possible to view the results in graphical format and copy these graphs to other Windows applications.
Adding an equation
To add an equation, click on the [Add] button and a blank Equation form dialog will appear.
This dialog allows you to create the equation entry, select the tab on which it is to appear and, if it forms audit evidence indicate the audit area that it relates to. The following is a description of each field in the dialog:
Code: The code is a unique identifier for the question and can be up to 20 characters. The code can include spaces and is alphanumeric. We would recommend that all codes start with an alpha character and that you use the underscore in place of a space. This will ensure that the system does not become confused if you use the equation in the formula of another equation and also makes the resulting formulas easier to read.
Name: The name is purely for identification and information and can be up to 50 characters in length.
Formula: The formula is optional. If you do not enter a formula then the system will allow you to enter data direct into the results field and will never overwrite the information that you have entered. This enables you to use the equations as a means of storing client data for each of the 10 years that CCH Audit Automation can record. You can then use this information for analytical review. This type of entry would normally be stored on the Client data or Background data tabs. Where you enter a formula, you can either type it in or use the right mouse button and pick lists to insert account numbers and equation codes. The formula may be up to 150 characters and details are given below of how it is constructed. Once you have entered a formula you will no longer be able to enter information in the results fields as it becomes a calculated equation.
Fixed precision: This checkbox allows you to determine if the results of a calculated equation are calculated to the maximum precision of 8 decimal places or to a specified number of decimal places. If the text box is ticked then the number is taken from the next field.
Number of DP: Enter in this field the number of decimal places to which the results are to be calculated, from 0 to 8. You can use the scroll buttons to vary the number displayed.
Current year only: If this checkbox is ticked the system will only calculate the result for the current year. If it is not ticked the result will be calculated for all years for which there is valid data.
Results: Results for each of the 10 years. If this is a calculated equation the calculated results will be shown here, if no formula has been entered you may type the figures for each year in the results boxes.
Comments: Any amount of descriptive information that you wish to enter in respect of the equation. This could be used to explain variations or to explain the formula used.
Tab: The three radio buttons appearing in this group box allow you to select the tab on which the equation will appear. You may change the position of the equation at any time after it has been created.
Audit area: Enter here the audit area for which the equation is providing evidence. If it is not providing direct evidence then enter area 0 for None. A drop-down list is provided of the audit areas defined in the master pack to which the client is attached.
Buttons are also available in the Toolbar as follows:
Clicking on this button will result in a graph of the question results being presented. The graph is in the same format as that presented when you click on the [Graph] button from the Stored equation dialog and the features available are described below.
[At planning]: The system stores two sets of results for each year for the equations. The results that existed at the point that you recorded planning as completed and the current results based on the current TB or the data adjusted for audit journals. If you have signed off planning then this button will be active and you can click on it to switch for viewing the results based on the current data to those that existed when planning was completed. You can also view and print graphs in respect of both sets of results. All reports listing the equation results include the results at planning and, where the figures are different, the current results.
[Calculate]: Clicking on this button will cause the equation to be recalculated to reflect any changes that you have made.
After you have entered the information in respect of the equations, click on [Store] and the new entry will be added to the list. The dialog will clear and you can now enter the details of the next equation. If you start to create a new equation and decide that you wish to abandon it part way through, click on [Abort] and the dialog will be cleared to allow you to start again.
When you have added details of all the new equations, close the dialog by clicking on [Close] and the Stored equations dialog will be refreshed so that the new equations are presented in the lists.
Editing an equation
To edit an equation, either double click on it or highlight it in the Stored equation dialog and click on [Edit]. The Equation form dialog will be displayed with the selected equation presented. You may edit any of the information shown and click on the [Calculate] button to see the revised results. After you have made your changes you can either click on [Store] to save them, or click on [Abort] if you wish to close the dialog without changing the original data.
On returning to the Stored equation dialog, if you stored the equation the system will show "Recalculation required" in red to the right of the [Pick] button. See below for details on how to do this.
Deleting an equation
To delete an equation, highlight it in the Stored equation dialog and click on [Delete]. The system will ask you to confirm that you wish to delete the highlighted equation and, if you click on [Yes] it will be removed from the list.
Printing or displaying a list of equations
You can print or display the equations listed on the tab currently displayed by clicking on the [Print] button or on the [Display]
button. On selecting the option to print, the system will present the standard Windows printer dialog to allow you to select the printer to user. On selecting the option to display, the system will proceed to calculate and display the equations with no further user input.
Recalculating all equations
The [Calculate] button on the Stored equation dialog is provided to allow you to force a recalculation of the equation list if you wish to ensure that the results currently displayed are fully updated with any changes you have made. As described below, you can build equations that use the results of other equations. If you change the formula or the entered results of an equation referred to by another equation the calculated results of the dependent equation will not be updated until the equation list is recalculated. To warn you of this possibility, the system displays "Recalculation required" whenever there is a possibility that the list displayed may not contain the most up to date results. If you wish to update the results shown, click on the [Calculate] button.
The system will automatically recalculate the list when you request a display or print of the equations, and when you close the Stored equation dialog. Hence, you only need to use the [calculate] button if you wish to ensure that the information listed in the Stored equation dialog is correct.
Displaying a graph of an equation
As indicated above, you can display a graph of an equation when it is presented in the Equation form dialog. You can also see this graph without opening that dialog by highlighting the equation in the list and clicking on the [Graph] button. You can only display the graph of the current results from this dialog, to see the graph of the planning results you have to load the equation in the Equation form. On selecting the [Graph] button, the graph of the equation will be displayed using the Graph dialog described on page 111. When this dialog is presented with the results of stored equations, the information is shown as a bar chart but no trend line is present and the trend radio and [Restrict] buttons are not shown. An additional button is included called [Bar/Line] and this allows you to toggle between a bar and a line graph of the data. In all other respects the dialog operates as described on page 111.
Separate settings are maintained for the format of the trend analysis and equation graphs. To reset the format of the equation graphs, use the [Restore to defaults] button on the Stored equation dialog.
Composing a formula
As indicated, each equation can either contain user entered results, only allowed if you have not entered anything in the Formula edit box, or it can contain a formula drawing on information from the trial balance, references to other equations and constant data.
The formula can contain the following:
Operators
Exponential (^ or **)
Multiplication (* or MULT)
Division (/ or DIV)
Modulus (% or MOD)
Addition (+)
Subtraction (-)
Function
Absolute value (ABS)
ArcTangent (ARC)
Cosine (COS)
Exponential (EXP)
Fractional (FRAC)
Integer (INT)
Natural logarithm (LN)
Sine (SIN)
Square (SQR)
Square root (SQRT)
Accounts data
A_99999
Account number 99999 for each of the 10 years being calculated.
A_88888:99999
Sum of accounts 88888 to 99999 for each of the 10 years being calculated.
A_88888:99999$9
Sum of accounts 88888 to 99999 for a specified year based on the current year of the client's records - i.e. an absolute year reference of 9 years prior to the current year.
A_88888:99999~9
Sum of accounts 88888 to 99999 for the year which is 9 years prior to the year being calculated. I.e. a relative year reference for each year. E.g. if the reference is ~1 then when 1997 is being calculated, it is a reference to 1996, for 1996 a reference to 1995, etc.
D_ or C_
Used in place of A_ in the above codes to indicate debit only or credit only. Where D_ is used, only those accounts with debit balances will be included in the extraction and where C_ is used only those with credit balances will be included.
E99
E followed by a number from 1 to 999 can be appended to the above definitions to indicate that the data extracted should be from the data entered for a particular TB. Where the optional module is taken, multiple TBs can be defined, see click here, and this instruction is used in conjunction therewith. E.g. A_1:19E3 adds together the sales figures entered into TB number 3.
C99
C followed by a number from 1 to 999 can be appended to the above definitions to indicate that the data extracted should be from the converted data for a particular TB. E.g. A_1:19C3 adds together the converted sales figures in TB number 3.
Reference to other equation codes
AAAAAAAA
The result of the equation called AAAAAAAA for each of the 10 years calculated.
AAAAAAAA$9
The results of the equation called AAAAAAAA for a specified year based on the current year of the client's records - i.e. an absolute year reference of 9 years prior to the current year.
AAAAAAAA~9
The results of equation called AAAAAAAA for the year which is 9 years prior to the year being calculated. I.e. a relative year reference for each year. E.g. if the reference is ~1 then when 1997 is being calculated, it is a reference to 1996, for 1996 a reference to 1995, etc.
Constant number: An absolute number that is entered as part of the formula, e.g. 100.
Brackets: The system can handle any number of brackets nested to any depth. Including brackets allows you to determine the order in which elements of the calculation are carried out. For items that are not in brackets, the order of calculation is based on the following order of precedence:
Functions
Exponential
Multiplication, division and modulus
Addition and subtraction
For operators that have equal precedence, the order of calculation is left to right. By using brackets, you can change the order as items in brackets are calculated first. The order of precedence for items within the brackets follows the normal rules.
For example, 3 - 4 / 2 will be evaluated as 4 / 2 first and then the result deducted from 3 with a result of 1, but (3 - 4) / 2 will be evaluated as 3 - 4 first and the result divided by 2 with a result of -0.5.
Examples
Set out below are a number of examples to demonstrate the functionality of the equation editor.
| Equation name | Description | Formula |
| AREA | Factory area in square feet created with no formula and the area entered in the results fields. | |
| UNIT_PROD | Units of production created with no formula and entered data | |
| SALES_PERSONS | The number of sales persons created with no formula and entered data | |
| UNIT_PER_AREA | Unit production per square foot of factory space is the number of units produced divided by the area of the factory. | UNIT_PROD / AREA |
| AVERAGE_COST | Average cost of unit production is the sum of accounts 50 to 99 divided by the number of units produced. | A_50:99 / UNIT_PROD |
| AVERAGE_SALE | Average sales price is the sum of accounts 1 to 19 divided by the number of units sold. | A_1:19 / UNIT_SOLD |
| SALES_PER_PERSON | Average sales per sales person is the sum of accounts 1 to 19 divided by the number of sales persons. | A_1:19 / SALES_PERSONS |
| PERC_SOLD | Percentage of production sold is the number of units sold divided by the number produced multiplied by 100. | UNIT_SOLD / UNIT_PROD * 100 |
| P_MOVE_SALES | Percentage movement in sales from the previous year to this year is the sum of accounts 1 to 19 for the year being calculated minus the sum of accounts 1 to 19 for the previous year divided by the sum of accounts 1 to 19 for the previous year multiplied by 100. Note the use of brackets to force the difference calculation prior to the division | (A_1:19 - A_1:19~1) / (A_1:19~1) * 100 |
| P_MOVE_UCOST | Percentage movement in the average cost of production per unit from last year to this is the sum of accounts 50 to 99 for the current year divided by the unit production minus the same calculation for the previous year divided by the same calculation for the previous year multiplied by 100. | (A_50:99 / UNIT_PROD) - (A_50:99~1 / UNIT_PROD~1) / (A_50:99~1 / UNIT_PROD~1) * 100 |
| P_MOVE_USALES | Percentage movement in average sales price per unit from last year to this is the same concept as the previous calculation but using the sales accounts and units sold. | (A_1:19 / UNIT_SOLD) - (A_1:19~1 / UNIT_SOLD~1) / (A_1:19~1 / UNIT_SOLD~1) * 100 |
| INCREASE_SALES | Increase in sales is the sum of accounts 1 to 19 for the current year minus the sum of the same accounts for three years ago. This equation would be restricted to calculate only for the current year. | A_1:19 - A_1:19$3 |
Referring to non-existent equations
If you create an equation that refers to another equation and then delete the equation referred to, or enter a name that does not represent an internal function or a valid equation reference, the system will not be able to retrieve the data to use when it evaluates the equation.
In this case it will pause and ask you to enter the data from the keyboard. This can be quite tedious as it will request the information for each year and for all references to the name that is not found. If you enter the data, evaluation will proceed and the correct results will be produced, however, you can cancel the requests for data at any time, by clicking on [Cancel] and the system will continue to evaluate the equations that it can. Bear in mind that the results of any equations that referred to the non-existent name will be unpredictable and do not represent valid data. You should therefore correct the cross reference before continuing.