The problem

Due to the Trust’s size (and associated data volume) and complexity, reconciliations were very time consuming, we needed to work smarter, otherwise we had very little time for the important elements of critical review and correction. We automated the following using VBA within Excel:

• accruals – NHS
• accruals – commercial
• prepayments – NHS
• prepayments – commercial
• deferred income – NHS
• deferred income – commercial
• income received in advance – NHS
• income received in advance – commercial
• bank – general account
• bank – payments account
• the monthly board report
• analysis of trial balance (TB) against budgets.

The challenge

How we might create a one click system for each of the above reconciliations and reports so we could save time for finance staff and ensure focus on critical review and correction? How we might ensure teams could maintain these reports and reconciliations even if staff changed roles so that they are not person dependent?

The outputs

Faster, effective reconciliations with instructions that could be used by anyone needing to run them. On-demand Board reporting tool. Improved ability to identify errors and more time for error resolution and analytical review.

The outcomes

The outcomes include better quality reconciliations with highlighted anomalies, allowing for more time to investigate these anomalies and conduct analytical reviews. Consequently, this provides the financial accounts team with the opportunity to provide feedback to those responsible for journal postings, thereby reducing the error rate in the future. Additionally, the Board reporting process has become more effective and efficient, empowering users to carry out tasks independently without relying on others who might not be available. The enhanced ability to identify and resolve anomalies further contributes to improving the overall quality of the produced accounts.