Executive Summary
The project
builds an Operating Expenses (OE) report management and analysis tool for the
finance department in the bank I worked for. Each month, staffs in the finance
department receive over 100 replies regarding each individual cost center’s OE
in standard Excel format. They then manually copy and paste all replies to one
master worksheet, perform data analysis, search and update some cost entries
as needed. This VBA tool reduces manual workload by automating report
consolidation, exception report generation, variance analysis and follow-up
processes. It also provides access control to ensure data security.
Below is a high-level workflow of the OE report management process:
- User login verification (user name: finadmin; password: 123456)
- Consolidate selected OE reports to a master worksheet
- Generate exception reports to monitor not-yet-replied or not-confirmed items
- Generate pivot table to analyze expense variances
- Search cost information by inputting “Cost Center ID” and “OE ID”
- Update cost information and confirmation status through a user form
No comments:
Post a Comment