Manufacturing Capacity Planner

[GavinRoe]

Hi,

I am seeking a Power Query solution with the following, greatly appreciate any assistance.

I am building a manufacturing capacity planning tool for a manufacturing plant using Power Query (in excel), to allocate Orders into a master schedule, allocating the manufacturing hours required for each order(s), identified as a Product ID, against the produciton (manufacturing) capacity hours available for a manufacturing plant. The end goal been the establishment of the Product ID`s manufacturing completion date, whereby the Start Date is mannually assigned from a master order table in Excel and the completion date will be an output of the Power Query calculations.

Two query`s have been created thus far as follows:

1.)"Assembly_102 Capacity Req" contains the columns Product ID, Production Start Date and Production Hours Required. Where the Prroduction Start Date is mannually assigned from a master table in Excel (containing all open orders) and the Product ID and Porduction Hours Required are sourced from other data sources.

2.)"Assembly_102 Calculations" contains the columns Hours Available.1, Date and Week No., the source of which is a master query containing the master available capacity (in hours) built using data from various sources. 

Therein there are two scenarios`s:

a.)An order`s required hours is greater than one day`s capacity so the Production Hours Required will need to be allocated (deducted) across more than one day.

b.) An order`s required hours is less than one day`s capacity, meaning a second (or more) order can comence on the same start datet but both orders may complete on different dates.

In "Assembly_102 Calculations", I need to allocate (deduct) the Production Hours Required covering both scenarios above, for each order (Product ID), from query "Assembly_102 Capacity Req", comencing on the Production Start Date and establishing the completion date of the order (Product ID). The aproach required would be to deduct the hours required from each row ( date forward) and in turn keep deducting the balance of the hours required from each row until all required hours have been allocated. The end goal been the establishment of the Product ID`s completion date. Since we already have the start date ,we then will have the Order start and Order Completion date. 

"Assembly_102 Capacity Req":

"Assembly_102 Calculations":

[GavinRoe]

Hi,

We have an ERP system however, the resource planning module in this is very basic and not suiting to our needs, this would meet our requirments until perhaps a more sophisticated ERP system one day. I have been sucessfull in Power Query to allocate capacity, looking out 10 days forward. To do so, in a nutshell, I sum grouped the total hours by date in  "Assembly_102 Capacity Req", then merged it into "Assembly_102 Calculations" by date, there after the applied step " Merge Assembly_102 Capacity Required" (see below snip), I simply minused the required hours column from the available hours column, then used a comination fo an index column to offsetthe results (balance of hours) column. Then repeated this 9 more times. This gave me a 10 day period to allocate ordes against available capacity. 

However I`ve been unsucessfull at establishing the order (Product ID) completion date. I have not been successfull at making the connection between the Product ID`s specific hours required and the Prodct ID itself in this process. Whats happening is, the Prodcut ID is seperated before the merge, due to 2 or more orders on the same start date which lead me to group as outlined above. 

[lbendlin]

I am building a manufacturing capacity planning tool for a manufacturing plant using Power Query (in excel),

Any particular reason for not using an off-the-shelf tool that is designed for capacity planning?