The idea
Journal entries are debit and credit transactions recorded in the general ledger. Auditors perform the testing on the journal entries to ensure the transaction is recorded appropriately. The common pain point in the journal entry testing is time taken to input of various pivot tables repeatedly as required by the tests. Hence, the purpose of the python program is to improve the efficiency of the testing by automatic output of the test results.
The design
For the convience of the users, sample data input, is attached through the link below. The sample data input includes the general ledger (GL) and the trial balance (TB), is created for a unreal company and filled with random numbers. The Financial year 2021 for the company starts from March 2021 to March 2022, with the functional currency USD.
Therefore, users can upload the sample input to test the coding and make adjustments on their own input according to the sample format when they run the coding for their own engagements.
After the preparation of data input and data cleaning, to perform the journal entry testing, the program provides different ways for users.
- Testing Completeness
The general ledger (GL) is supposed to record and reflect all the transactions in the Financial Year. Therefore, the test is designed to sum up the totals in GL accounts, which pertain to the movements, and add the movements to the opening balance of the accounts in trial balance. And the results should be the same amounts as the the ending balance of the accounts as recorded in trial balance (TB).
Hence, the test calculates the differences for users so that they can easily look through the non-zero balances and perform further investigations.
- Select Journal Entries for Testing
To use the professional judgement in determining the nature of the entries, auditors select specific entries and evaluate. Thus 6 tests for users as stated below are designed in the program.
Infrequent Used Accounts Test
Selected accounts with less than 5 entries.
Keywords Test
With a default keyword list, extract the entries the description of which contain words in the keyword list.
Recurring Number Test
Select entries with amounts containing recurring numbers.
Large Debits to Revenue Test
Select entries whose GL account code pertains to evenue and debit amount > threshold.
Large PnL Test
Select PnL related entries with the abosulte value of the transaction amount above the threshold.
Blank Entry Test
Select entries if there's any blank in Account Code", Journal Number, Description or Transaction Date.
The process
When running the program, there’re several mannual inputs required. To start with, the general ledger and trial balance are supposed to be uploaded in CSV format as the file input.
Then in the completeness test, the PnL accounts will be required since the program will set the opening balance of PnL items to 0 to be consistent with their nature.
When it comes to keywords test and recurring number test, the users will be asked if they would like to proceed with the default lists as stored in the program. If not, they will be able to input their own lists for the entry selections.
The last input will be triggered in large debits to revenue test. In this test, the users are able to input the revenue account codes and the threshold, which align with their own engagements in the financial year.
After that, an output will be generated automatically and downloaded to the user’s drive as an Excel spreadsheet. This makes it more convenient for auditors to move or copy the tabs to their working papers directly and leave more time for them to perform the assessments.
