Summary

The purpose of the pre-analysis completeness check is to verify that the following equation holds true for all the accounts found on both the GL and the TB:

Opening balance + GL activity = Closing balance

Methods

We have 2 recommended methods of running the pre-analysis completeness check.

1. Via the MindBridge data page: refer to Pre Analysis Completeness Check (General Ledger) 
   • Please note that this method would be required if your GL files exceed the Excel 1M row limit
2. Using Excel to calculate the balances. 

Running the completeness check using Excel - Formatting the Trial Balance

To run the completeness check in Excel, we will need to first determine the TB activity for the year. This is done by subtracting the opening balance from the closing balance:

Activity = Closing Balance – Opening Balance

We would recommend exporting the ungrouped version of the HAÜ for this purpose.

Sample of what a standard ungrouped HAÜ looks like:

Prior to calculating the activity, ensure to change the beginning balances for all profit and loss accounts to 0. Check out our guide on Zeroing the Opening Balance Entries for Revenue and Expenses Accounts for a detailed explanation on how to achieve this.

Our next step is to create the activity column. To do so, click into the first empty column in row 2 which in our case is cell F2. Now we will want to subtract cell D2 containing the beginning balance from cell C2 which contains the ending balance. Type in =C2-D2 in cell F2 then press Enter. This will calculate the TB activity for the first row.

We will now want to calculate the same for the remainder of the rows. To do so, we can simply double click on the bottom right corner of the cell where the small green rectangle is. This will fill down the formula we used in cell F2 to all the other rows.

You might notice that some of the cells have “##########” instead of a number, this is simply because the number is too big to properly display. To fix this, we can expand the length of the column by double clicking on the right handle of the column letter “F”, once done column F should look like the following.

To finalize things, add a meaningful column header to Cell F1 i.e. Activity then save your file.

We are now done with preparing the TB portion of the completeness check requirements.

Let’s now move onto the GL portion.

Running the completeness check using Excel - Formatting the General Ledger

We will want to sum up the GL account activities for each account. One way to do is to create a pivot table. With your cursor placed inside your GL data; navigate to the “Insert” ribbon then click on “PivotTable” as seen below.

Leaving everything as default in the resulting window and press “OK”

This will create a new sheet with the following interface.

From the PivotTable Fields you see on your right, we will want to drag the “Account Number” column into the “Rows” field and the “Amount” column(s) into the “Values” field.

This will sum up the “Amount” columns for each of the “Account Number” columns. If you have both Soll and Haben columns we will want to convert these two columns into one. To do so, navigate to the “PivotTable Analyze” ribbon then click on “Fields, Items, & Sets” dropdown followed by “Calculated Field…”.

In the resulting window, we will want to change the Formula to the following format =Name of Soll column – Name of Haben column. In our case our formula would be = Soll – Haben. Although not required, we should also update the Name field for clarity, for our example, we’ve updated the name to “Betrag”. Now press OK.

This will create our new Betrag column as part of the pivot table.

Verifying the GL activity equates to the TB Activity

We now have both our TB activity and GL activity ready. We will want to verify that these activities match. To do so, we will need to pull the two activities columns into the same spreadsheet. One way to accomplish this is to use the VLOOKUP formula. I would recommend pulling the GL activity into the HAÜ.

Going back to the HAÜ file, click into row 2 of an empty column, we will use cell G2 for our example.

The VLOOKUP formula takes in 4 arguments separated by a comma.

1. The column we are looking up against, this column need to exist in both files/tables
2. The range of where to look up the column used in 1 in the other file/table
3. The column number of the data you would like to retrieve
4. The option to look up using an approximate or exact value. We will almost always want to do an exact look up which is denoted by the number “0”.

In our example, our formula will look like the following: =VLOOKUP(A2,'[FormattedV2_GDPdU 2022.xlsx]Sheet2'!$A:$D,4,0) – equivalent German version: =SVERWEIS(A2;'[FormattedV2_GDPdU 2022.xlsx]Sheet2'!$A:$D;4;0)

For additional training on VLOOKUP please refer to the official Microsoft guide at the following link: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

Now that we've pulled in the GL activity to our HAÜ file, pull down the formula to the rest of the rows, once done your HAÜ file will look something like the following:

Our last step is to compare the HAÜ activity to the GL. To do so, click on an empty column, in our example, column H and type in =ROUND(F2,2)=ROUND(G2,2) and fill down. In this formula we are rounding the activities to 2 digits then comparing the two.

Any TRUE values will mean that the account activity on the HAÜ/TB is equal to the activity on the GL. Any FALSE values means that they do not equal and #N/A would mean that the account is not found on the GL.

Anything else on your mind? Chat with us or submit a request for further assistance.

Related articles

• Formatting GDPdU: Data Checklist
• Zero Opening Balance Entries for Revenue and Expenses Accounts