Transaction summary, automated and comprehensive in Excel
The bank statement analysis from PDFs with our software is comprehensive and free. An Excel template has been built with our clients over 10+ years to cover all major areas of a bank statement investigation, and is still evolving to incorporate more refined description cleaning and entity analysis. This is relevant for credit review, insolvency and accounting professionals looking for a summary report of bank transactions.
Bank statements for multiple accounts can be extracted together quickly using ‘folder mode’ to process all files from each bank in one click. Then, multiple output files can be merged together for a combined summary highlighting round/recurring/reversed transactions with key words/days/months. This analysis delivers a dynamic insight into several key areas of the banking period, including the dependency on certain customers or suppliers, without the need to first specify which third parties should be analysed, or the associated acronyms used by the bank.
After the transaction history is extracted from your PDF bank statements into Excel, the data is validated so 100% accurate data is summarised in the Excel analysis template. The analysis is split into key areas, covering reversed transactions, key months and recurring transactions. Here is some more detail:
Keywords
Each unique word in each description cell is counted, and the associated transaction amount is accumulated. Either the keywords that appear the most number of times or the keywords that has the greatest monetary total can be shown to the user. For each keyword, total debits and credits, and the earliest and latest date on which the keyword appeared are shown.
This delivers a dynamic insight into the dependency on certain customers or suppliers, without the need to specify which third parties should be analysed, or the associated acronyms used by the bank.
Categorise transactions
Transactions with key parties can be identified via user defined keywords appearing in the transaction narrative. A full summary of each category is provided, including the total debits and credits and the number of transactions.
Transactions within each category are separated into new tabs - here they can be reviewed and added to, automatically affecting the summary mentioned.
Days
Whilst analysing the daily totals for a company in a state of increasing or decreasing operations, importance can be incorrectly placed on the days at the beginning or the end of the period. Therefore, the StatementReader summary uses a rolling average of daily totals over a three month period to identify which days have extreme activity, and may require further analysis.
Months
The months with the most and least activity, by number of transactions and total amount flowing through the bank account, is concisely summarised in four tables.
Seasonal trends and reliance on certain known market cycles or macro factors can quickly be isolated using this analysis.
Round amounts
One table shows the largest transactions that appear as a round amount. This is defined as a number greater than 1,000 that has no remainder when divided by 100.
Our users enjoy this feature to quickly identify potentially suspicious transactions or informal loan arrangements.
Recurring amounts
The StatementReader summary shows the amounts that appear many times, together with the description of the first transaction, and the period over which the transactions occurred. This information is sorted by most occurrences and largest total amounts in two separate tables.
Repetition in the amount that is paid out can be helpful to identify recurring transactions that do not fit into a monthly pattern, yet their consistency perhaps illustrates a dependent financial relationship, for incomings or outgoings.
Level of materiality
Some calculations are used to outline the materiality level of the transactions analysed.
User defined search
Once all transactions are in Excel, you can use 'advanced filter' feature in Excel available on the 'data' menu to only show certain transactions, or copy them elsewhere.
Reversed transactions
Once configured with a day and/or amount variance, transactions coming in and out of an account can be separated for the user to see easily. This includes instances where one credit transaction equals the total of two debit transactions (and vice versa).
With this feature the automated analysis can identify potentially artificially inflated revenue figures by identifying monies following through between related bank accounts.
Excluded transactions
Loan drawdowns should be excluded when assessing a company’s income. These transactions are identified using defined keywords that appear in the bank statement narrative.
End of day balances
The end of day balance is computed for each day, this feeds several monthly and overall average balance calculations including one based on the balance as at the 5th, 15th and 25th day of each month.
Monthly activity summary
Revenue is summarised by month, and if the change exceeds a defined percentage these months can be shaded red for the user. The summary of monthly credits and debits includes total amounts and number of transactions.
Bounced cheques
Cheques and fees can be separated for the user to assess how dependable the accountholder has proven themselves to be.
Bespoke analysis output structure
The Excel analysis output format has been built by the StatementReader team, and therefore is flexible to your needs. Please provide an example of the desired format for us to discuss this in more detail.
Get started by downloading StatementReader today.