Google Cloud web app comparing actual departmental expenses with targets for an automotive parts manufacturer in Thailand

How the brief came about

This requirement arose because actuals and budget targets sat in separate sets of Excel report files. Actuals were in a detailed actual expense report, while targets were in a file controlling expenses by account and department. Before displaying anything, the system therefore had to match the figures from both sets of files by department.

This system is part of a project in which the customer wanted to consolidate internal data from multiple sources into a single database on GCP (Google Cloud Platform, Google's cloud service). The expense-tracking system therefore had to run in the customer's own GCP. In the same package of work, we also developed other modules on the central database on Google Cloud that consolidates KPIs, defects, sales and profit.

Requirements and constraints

The system had to use the customer's existing Excel report files as input and run in the customer's GCP. The main requirements and constraints were as follows.

• Read the actuals file and target file uploaded by users, then match actuals with targets by Department Code.
• Display daily figures and the month's cumulative totals against target, and summarise results by department.
• Assign departments to 3 groups, Direct, Indirect and SGA, according to the department grouping file defined by the customer.
• Deploy in the customer's GCP, subject to the organisation's information security (IS) policy.
• Column names, department names and department codes in the source files must match. During the project, the customer had to adjust columns and add department codes in some sets of files first.

How does this system read existing Excel files and compare actuals with targets?

This system reads the Excel report files uploaded by users, totals the figures by department, compares them with targets and displays them as charts and tables in a web browser. It can therefore use the existing set of files as input, but the files must keep the same format every time, because the system reads by column name and department code.

As required, the system uses the existing set of Excel files as input and runs in the customer's GCP. It is deployed as Docker (a self-contained program package that can be moved between machines) on Cloud Run, with data stored in Cloud SQL (a database on Google Cloud) and files stored in Google Cloud Storage (GCS, file storage on Google Cloud).

1. 1

   Upload the actuals file and target file

   On the KPI (key performance indicator) target-setting page, named KPI Target Setting, users upload the actual expense report file and the file controlling expenses by account and department that serves as the target. The system stores the files in GCS and the data it reads in Cloud SQL.

2. 2

   View daily figures against target

   The Expense Budget Control As of Today page shows daily expenses and the month's cumulative total against target, split into the Direct and Indirect department groups. Bars on the chart turn red when actuals exceed target, and users can filter by account, department or expense item.

3. 3

   Summarise by department with assessment results

   The Expense Summary page shows the target, actuals, variance and ratio for each department, split into Direct, Indirect and SGA. The system gives O when actuals do not exceed target and X when they do. Users can press the Edit button to record an analysis for each department in the table.

4. 4

   View overall KPI results on one page

   The page summarising current and monthly KPI results pulls targets and actuals from the expense pages and shows status by colour. Users can click an assessment result to open a details page.

Deliverables

We delivered a web app deployed in the customer's GCP, along with a system summary document at project closure. How each page works is covered in the steps of the previous section.

• A web app in the customer's GCP, with the KPI Target Setting, Expense Budget Control As of Today and Expense Summary pages, and a page summarising current and monthly KPI results
• System summary document at project closure

Process and change requests

This work involved change requests (Change Requirement) from development through to after project closure. We recorded each request as a change request document. The documented sequence of work was as follows.

1. September 2023, during development: users asked to add a monthly expense report page for all departments. We recorded this as a change request.
2. September 2023: project closed with a system summary document.
3. After project closure: data accuracy checked and corrections made based on user comments (feedback).
4. December 2023: latest change request prepared for the Expense Summary page, for example reason categories to support comments (over budget, spending out of period, savings achieved, or on budget).

Do the figures on the web page match the original Excel report?

The project closure document of September 2023 concluded that the expense-tracking web app read values from the user's sample file (June 2023 data) that matched the Excel report. The test used a sample file from the user, then compared the results on the web page with the original Excel report.

During testing, some calculated results did not match the user's expected answers, so both parties agreed a new acceptance method. After project closure, we continued to check data accuracy with the user. This case has no measured business figures, so we report only the results supported by documents.

What we learned

This work offers 3 principles for systems that read existing report files and display the results on the web.

• All sets of files must share a common key before data is matched. In this work, department names in the grouping file did not match the input files, so the system used the department code for matching, and the customer first aligned the source files.
• Agree the expected answers and acceptance method before testing begins. If the expected answers and comparison method are still unclear, mismatched calculation results will mean agreeing a new acceptance method during the project, as happened in this work.
• Requests for new views may arise during development and should be recorded in change request documents separate from the original scope. In this work, the request for an expense report page covering all departments arose during development, and we recorded it as a change request.

How this approach can apply to your work

If your organisation already keeps expense reports and budget targets in Excel files, you can use the same approach as this case: upload the existing files, match actuals with targets by department code, then display results by day, month and department. What needs adapting is the file format, department grouping, calculation formulas and where the system is deployed.

• Prepare real sample files with the correct calculation results (expected answers), and agree clear acceptance criteria.
• Agree the cumulative total and cumulative target formulas, and how departments are assigned to groups, before development begins.
• Prepare the organisation's cloud permissions and security policies if the system must be deployed in the organisation's own cloud.
• Set up a process for receiving change requests from the start, because requests for additional report pages may arise during development, as in this case.