Situation

The client is a global investment firm that owns and operates industrial facilities across multiple plant locations. ProsperSpark first worked with the firm in 2022, building Power Query tools that split labor and G&A costs across eight location files. That work included audit tables showing each cost split by month, description, and cost code, so the team could review how every cost was allocated.

When the firm came back in 2025, the problem was familiar but bigger. The finance team managed costs across its facilities in a central Excel workbook, and the allocation logic had not kept pace with the business. Labor and G&A costs needed to be split across two plant groups, then distributed to individual plants within each group. That math still happened in manual steps outside the workbook, which added time to every reporting cycle and left more room for error.

Environmental & Safety costs had no tab of their own. They weren't tracked in the same structured way as labor and G&A, which left a gap when the team needed to report across cost categories.

Access was the third issue. Salary data lived in the labor tab, and anyone with access to the file could view individual compensation details. That created a confidentiality concern.

The team needed all three problems addressed together: the allocation logic, the missing cost category, and the access controls.

Solution

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

1. 1. Enhanced the Labor tab. The updated tab allocates labor costs across two plant groups, then distributes those costs to the individual plants within each group. The allocation now happens inside the workbook, so analysts no longer do the math by hand.
   2. Updated the G&A tab.The G&A tab now uses the same group-based allocation logic as the labor tab, so both cost categories follow the same structure and feed the plant rollups the same way.
   3. Designed and built the Environmental & Safety tab. Using the G&A tab as a template, ProsperSpark built a new Environmental & Safety tab with the same group and plant-level distribution. This brought a third cost category into the same reporting structure.
   4. Rebuilt plant-level rollups. Every plant tab now pulls summarized values from the labor, G&A, and Environmental & Safety tabs. Each category stays separate, so the finance team can review costs by location without tracing back to source data.
   5. Implemented role-based access controls.A VBA login system separates admin users from general users. Admins can view individual salary details in the labor tab and can grant access to others. General users can work with everything else in the workbook but can't see salary data.
   6. Testing and training. ProsperSpark tested accuracy and usability, then supported the handoff with calls and training.

Results

The finance team now has a workbook that handles cost allocation for them. Labor, G&A, and Environmental & Safety costs are distributed across plant groups and individual plants automatically, which removes the manual steps that slowed down each reporting cycle.

Plant-level rollups pull directly from the source tabs, so totals stay consistent without anyone updating summaries by hand. With Environmental & Safety costs added, the team can report across all three cost categories in one structure.

Salary data now sits behind a login. General users no longer see individual compensation details, and admins control who gets access.

The client came back to the same team for a harder problem. The 2025 project could build on what ProsperSpark already knew about the firm's cost structure from the 2022 work.