(Like many of you) I've been given a task to update an externally-created spaghetti-link spreadsheet. In this case it is a pro-forma to estimate the cost-per-pound of linen going through a proposed laundry. In essence it is a series of matrices through which the linen volume moves.

First is a binary matrix showing which types of linen go through various tasks and procedures:

Tunnel Washer Presser Dryer Folder
Sheets 1 1 1 1
Blankets 1 1
Towels 1 1
etc








Then subsequent matrices (really scalars I suppose since we're only interested in one column at a time) give information on the capacity of the machines and tasks, how many FTEs are required, utility usage, machine cost + installation etc. from which we can also add up how many machines are required and at what cost. Then it's a matter of multiplying the pounds and pieces of linen across the various matrices to sum everything. (One might argue that the original binary matrix is unnecessary, but it seems the simplest way to turn a cost on or off if we decide that blankets should go through the presser.)

The problem with the existing workbook is that is wasn't built for significant time differentials. It can assume (say) a 3% increase in pounds submitted to the laundry each year, but it assumes full capacity in year 1 of the estimate. If we build the laundry to (round numbers) 100 million lbs/year but only have 50MM in year one and 75MM in year 3, is it better to build the building shell for capacity but only buy equipment as needed?

So before I go down this road I'm wondering if others have encountered similar requirements and whether they have performed most of the transformations in Power Query or Power Pivot. I don't know that it matters greatly - getting the correct throughput estimates for each machinery type is obviously a bigger deal - but I would like insight from those who have been there already.

Thanks!