Situation

The client is a global investment firm that owns and operates industrial facilities across multiple plant locations. ProsperSpark had worked with this client before, building Power Query-based tools to split Labour and GNA costs across eight location files. That work delivered what was promised, and the client moved on with a cleaner system.

When they came back in 2025, the problem was familiar but bigger. The finance team managed costs across those facilities using a centralized Excel workbook, but the allocation logic had not kept pace with how the business operated. Labor and GNA costs needed to be split across two plant groups, then distributed further across individual locations. That logic still lived in manual steps rather than in the workbook itself. Analysts were doing the math by hand or working around the file rather than through it.

The Environmental & Safety tab did not exist yet. Those costs were not being tracked in the same structured way as labor and GNA, which created a gap in how the team could report and review across cost categories.

The access issue made things harder. Salary data lived in the labor tab, and there was no way to control who could see it. Anyone with access to the file could view individual compensation details, which created a real compliance and confidentiality concern.

Before the team could trust the workbook for reporting or planning, those three problems needed to be addressed together: the allocation logic, the missing cost category, and the access controls.

Solution

ProsperSpark worked through the existing workbook structure and rebuilt the core allocation and reporting framework in several connected phases.

1. 1. Enhanced the Labor tab. The updated tab allocates labor costs across two plant groups, "Well" and "River Neen," using a 50/50 split, then distributes those costs further across the individual plants within each group. The allocation logic was moved into the workbook itself, using SUMPRODUCT calculations and updated macros so the distribution happens automatically rather than manually.
   2. Updated the GNA tab. The same group-based allocation logic applied to labor was implemented in the GNA tab, so both cost categories now follow the same distribution structure and feed downstream plant rollups consistently.
   3. Designed and built the Environmental & Safety tab. Using the GNA tab as a structural template, ProsperSpark built a new Environmental & Safety tab from scratch. It supports the same group and plant-level cost distribution logic, bringing the third major cost category into the same reporting framework.
   4. Rebuilt plant-level rollups. All plant tabs were updated to pull summarized values automatically from Labor, GNA, and Environmental & Safety inputs. Categories are clearly separated for each plant, so finance teams can review by location without manually tracing source data.
   5. Implemented role-based access controls. A login-based permission system was built using VBA to distinguish between admin and general users. Admins can view and manage sensitive data, including individual salary details in the labor tab. General users see everything else but are blocked from the restricted content. Admins also have the ability to grant access to others when needed.
   6. Testing and training. ProsperSpark tested accuracy and usability across the solution and provided up to two hours of calls and training to support the handoff.

Results

The finance team now has a workbook that does the allocation work for them. Labor, GNA, and environmental and safety costs are distributed across plant groups and individual locations automatically, which removes the manual steps that were creating risk and slowing down the reporting process.

Plant-level rollups pull from the updated source tabs, so totals stay consistent without requiring anyone to trace formulas or update summaries by hand. Adding the Environmental & Safety tab brought the third major cost category into the same structure, so the team can now report across all three in a unified way.

The access control system resolved the confidentiality problem. Salary data is now protected behind role-based permissions, so sensitive information is only visible to the people who need it, with no workarounds required.

The return engagement itself is worth noting. The client came back because the first project worked. That history meant the 2025 scope could move faster, with less time spent on discovery and more time spent solving the right problems. When a client trusts the work enough to bring a harder problem back to the same team, that's the outcome that matters most.

Estimated Metric Improvements

No formal before-and-after operational metrics were provided. The estimates below are grounded in the scope completed and the structure of the allocation problem.

• • Eliminated 16+ manual allocation calculations per reporting cycle. With costs split across two plant groups and distributed to 8+ individual plant locations across Labor and GNA, the allocation math was substantial and repeated every cycle. The updated workbook handles all of it automatically. (Estimate based on scope structure)
  • Estimated 2-4 hours saved per reporting cycle on allocation prep and rollup reconciliation. With Labor, GNA, and Environmental & Safety now feeding plant tabs automatically, the manual steps of distributing costs and verifying totals are largely removed. (Estimate; actual savings depend on reporting frequency and team size)
  • Environmental & Safety cost tracking went from absent to fully integrated. This is not a time savings estimate but a capability that did not exist before, bringing a third major cost category into the same reporting structure as Labor and GNA. (Confirmed by scope)
  • Salary data visibility reduced from all file users to authorized admins only. Before the engagement, anyone with access to the workbook could view individual compensation data. The VBA-based login system closed that gap entirely. (Confirmed by scope)