The client is an R&D and development center with 200+ employees on its payroll. Given the small size of the organization, operational activities are manually tracked and reported. One such critical internal system is expenses tracking, budgeting and forecasting for the organization. The expenses of the organization are managed through a custom portal and reported using excel sheets and PowerPoint decks.
Client’s employees and vendors submit reimbursement expenses on a custom-built portal. The expenses are reviewed, and upon verification by an admin employee, are manually added to an excel tracker.
This data is used to build PowerPoint reports for executive review. This report generation is manual, time-consuming and error-prone. In addition, the executives find it cumbersome to review expenses via a slide deck. The valuable archive of the client’s historical financial data was also not mined for insights as they resided in multiple excel spreadsheets.
The client was looking to analyze existing financial data for insights to enable data-driven decision making and developing a seamless reporting of all expenses within the organization.
Agilisium recommended that the client leverage AWS services to build a solution. A custom suite consisting of Glue, Athena and QuickSight were chosen for their easy-to-use cataloging, querying, and business intelligence features and low cost.
Agilisium took a 2-fold approach to solve the problem
1. Accurately outlining the business process
- Classifying different types of cost drivers and defining standard cost categories for each department.
- Consolidating all existing expense line items into standardized cost headers.
- Documenting the approach to be taken by the admin staff, to upload monthly expenses, into a predefined bucket in S3.
2. Creating and setting up a serverless reporting platform on QuickSight and relevant dashboards.
The client’s requirement for a BI dashboard that drove data-driven decision making for expenses tracking, budgeting and forecasting was achieved through,
- Ease of importing data: A simple web page to view, upload or delete files from S3 (web page deployed on EC2).
- Variety of visualizations: 3 dashboards and 20+ visuals comprising of narratives, charts, table content, and URL actions enable custom visualizations for easy consumption.
- Unified Interface: To access dashboards directly from QuickSight console.
- Secure access: To govern User access and authorization through Identity and Access Management (IAM).
- Better transparency in identifying cost drivers/expenses.
- Higher Efficiency of accounting operation, owing to the redefinition of the client’s expenses tracking, budgeting & forecasting needs, which brought to light many areas of improvement.
- 30% gain in productivity, as the need to manually generate reports was eliminated.
- Better ability for executives to drill down into the historical data and mine deeper Insights through flexible and powerful visualizations via the dashboards.
Importance of standardizing data categories (metadata) for easy downstream analysis and insights.
Amazon QuickSight’s powerful URL actions simplify the dashboard layout.