Cost Analysis on Project Fulfillment for Payment Handling Services

Photo by Kelly Sikkema on Unsplash

One of my clients is a company specializing in market research to generate insight into consumer behavior, which requires significant manpower to ensure project success. To help retailers and consumer-packaged goods (CPG) companies make data-driven decisions, managing over 200 annual projects with more than 1,200 contributors effectively is critical.

Upon the completion of specific milestones, the contributors will receive fees that are processed weekly. At this point, the payment handling service is provided as a solution of significant financial complexity to streamline operations and ensure the smooth execution of the freelancers’ fees.

The project costs could possibly fluctuate, alternating between being on, under, or over budget. Therefore, a cost analysis needs to be conducted to optimize budget oversight and ensure long-term operational sustainability.

Workflow of Project Cost Analysis

Based on the workflow above, the Purchase Order Code contains the total project budget, which is used to centralize financial tracking, such as:

1. Suppliers’ billing
2. Incentive calculation for the weekly report
3. Fee payment for freelancers

In this term, all data is processed within Google Spreadsheets, which allows the project team and stakeholders to monitor the budget health.

Data Merge

Manually opening email attachments is a time-consuming process that hinders productivity. To resolve this, we utilize Google Apps Script to automate data consolidation. By collecting all relevant documents into a single folder, the script can be executed to merge the data.

function mergeWeeklyReport() {
  const folderId = 'xxxxx'; 
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 1. This clears the master sheet so you don't get duplicates if you run it twice
  targetSheet.clear(); 

  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);

  let isFirstFile = true;

  while (files.hasNext()) {
    const file = files.next();
    const spreadsheet = SpreadsheetApp.open(file);
    const sheet = spreadsheet.getSheetByName("Project Execution Code");

    if (sheet) {
      const data = sheet.getDataRange().getValues();

      // If it's not the first file, remove the header row
      if (!isFirstFile) {
        data.shift(); 
      }

      // Append the data to the master sheet
      if (data.length > 0) {
        targetSheet.getRange(targetSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
      }

      isFirstFile = false;
    }
  }
}

The result of the data merge is displayed below.

Data merge from weekly report since 1st — 52nd week

Data Processing

The objective of data processing is to synthesize large volumes of Project Group data. This process will systematically reconcile freelancer disbursements with supplier invoices under a centralized Purchase Order Code. The flow is:

• Sort project group

=SORT(UNIQUE(FLATTEN('Weekly Report'!A2:A)))

• Sum project budget

=ARRAYFORMULA(IF(A2:A="", , SUMIF('Weekly Report'!$A$2:$A, A2:A, 'Weekly Report'!$D$2:$D)))

• Lookup Purchase Order Code

=IFERROR(TRANSPOSE(FILTER('Purchase Order Code'!V:V, 'Purchase Order Code'!B:B = A2)), "Not Exist")

Sort the repetitive data of Project Group

Because billing is issued in three terms, we track each invoice by its billing number and amount. By linking these to the Purchase Order code, we can monitor the residual budget in real-time, ensuring that expenditures remain within the approved financial scope. The flow is:

• Sort the purchase order code

=SORT(UNIQUE(FLATTEN('Project Code Collection'!E2:E)))

• Sum fee disbursed

=sum('Project Code Collection'!B2:D2)

• Lookup billing number and amount

=IFERROR(TRANSPOSE(FILTER(bill_service!P:P, TRIM(bill_service!V:V) = TRIM(A2))), "Not Exist")

Remaining amount calculation

Discussion

The transformation of the raw data transaction record is visualized as given below:

Dashboard real-time monitoring

The dashboard proves that the current condition tracking a Rp1.7 billion gap that might otherwise go unnoticed in a manual system. As an outsourcing company who provided Payment Handling service, this transparency is vital for cash flow forecasting.

Recommendations for Optimization

1. Investigate the Rp77.5 million overpaid amount to determine if it is tied to specific Project Groups that are consistently exceeding their Purchase Order limits.
2. Accelerate the billing process that ensures invoices are submitted before the end of the fiscal term to avoid a massive “bill shock” in the final term.
3. Apply automated alerts to highlight Purchase Order codes where the “Amount Overpaid” exceeds a specific percentage (e.g., 5%) of the total project budget.