Matrix multiplication in Power Query or Power Pivot?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
(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

<tbody>
</tbody>








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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top