Summary:
A financial management solution written for a local church to manage a particular set of accounts receivable and payable.
The solution was based on Excel VBA and User Forms and included: a new data model to link accounts payable and receivable; extensive form code to enforce the process and validate input; Pivot Tables with slicers plus specially written search and filter functions; reconciliation and reporting functions. Data from the old spreadsheet was migrated using Get & Transform (Power Query) functions and VBA code to extract information from cell comments.
The old spreadsheet with colours and cell comments and poorly structured data
Requirement:
The requirement was for a spreadsheet to manage the bookings and payments for baptisms, weddings and funerals to replace one that grown up over time but was no longer fit for purpose.
Challenges:
The old spreadsheet had been handed from person to person over time and had gradually become less and less usable. A lot of time was spent on manual data entry, reconciliation and error correction. It had become a hindrance to the process rather than an aid.
The quality of data was very poor: for example, some information was recorded using cell colours and comments. No formal process was being followed and all procedures were in people’s heads. Quarterly and annual reconciliation and reporting done manually.
The main sheet with Pivot Table reports and pop-up User Form windows
Solution:
The solution was a custom built VBA system based on Excel that had 4 user forms and 92 procedures. The main sheet had search and summary functions to make it easy to identify payments and processing outstanding. Events and payments were edited via a user form dialog, launched from the main sheet. New reconciliation and reporting functions were also written.
The Reports page showing Cash and Bank Account transactions.
Design Highlights:
Get & Transform (aka Power Query) queries were used to extract and transform data, along with VBA written to extract cell comments and interpret cell colours. VBA user forms provided extensive data validation and ensured that the agreed process was followed. The main sheet used Pivot Tables with slicers and specially written search and filter functions, enabling the user to readily identify payments and transactions outstanding. New reconciliation and reporting functions were also based on Pivot Tables.