Spreadsheet: 821000d1678379926-model-to-allocate-capacity-planning-for-machines-line-utilisaton
Sheet: Machine loading (DOH)

 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31
 |  | Product Group allocation | Product Group allocation | Product Group allocation | Product Group allocation | Product Group allocation | Product Group allocation | Product Group allocation | Product Group allocation | Product Group allocation | Product Group allocation | Product Group allocation | Product Group allocation | Closing stock | Closing stock | Closing stock | Closing stock | Closing stock | Closing stock | Closing stock | Closing stock | Closing stock | Closing stock | Closing stock | Closing stock | Production (pieces) | Production (pieces) | Production (pieces) | Production (pieces) | Production (pieces) | Production (pieces) | Production (pieces) | Production (pieces) | Production (pieces) | Production (pieces) | Production (pieces) | Production (pieces) | Production (Days required) | Production (Days required) | Production (Days required) | Production (Days required) | Production (Days required) | Production (Days required) | Production (Days required) | Production (Days required) | Production (Days required) | Production (Days required) | Production (Days required) | Production (Days required) | Output per 24hr (pieces) | Machine efficiency (%) | DOH Target | DOH Target | DOH Target | DOH Target | DOH Target | DOH Target | DOH Target | DOH Target | DOH Target | DOH Target | DOH Target | DOH Target | 1 DOH = X Pieces | 1 DOH = X Pieces | 1 DOH = X Pieces | 1 DOH = X Pieces | 1 DOH = X Pieces | 1 DOH = X Pieces | 1 DOH = X Pieces | 1 DOH = X Pieces | 1 DOH = X Pieces | 1 DOH = X Pieces | 1 DOH = X Pieces | 1 DOH = X Pieces | Stock level needed (pieces) | Stock level needed (pieces) | Stock level needed (pieces) | Stock level needed (pieces) | Stock level needed (pieces) | Stock level needed (pieces) | Stock level needed (pieces) | Stock level needed (pieces) | Stock level needed (pieces) | Stock level needed (pieces) | Stock level needed (pieces) | Stock level needed (pieces)
Product Group | Machine | January | February | March | April | May | June | July | August | September | October | November | December | January | February | March | April | May | June | July | August | September | October | November | December |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Output per 24hr (pieces) | Machine efficiency (%)
1234 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 4 | 0.8 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26
1234 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 4 | 0.8 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26
4444 | 3 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3 | 0.8 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26
4444 | 4 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 5 | 0.8 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26
4444 | 5 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 7 | 0.8 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26
5555 | 1 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8 | 0.8 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26
5555 | 3 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 9 | 0.8 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26
 |  | Production days available | Production days available | Production days available | Production days available | Production days available | Production days available | Production days available | Production days available | Production days available | Production days available | Production days available | Production days available | Machine Utilisation | Machine Utilisation | Machine Utilisation | Machine Utilisation | Machine Utilisation | Machine Utilisation | Machine Utilisation | Machine Utilisation | Machine Utilisation | Machine Utilisation | Machine Utilisation | Machine Utilisation | Shortfall (Pieces) | Shortfall (Pieces) | Shortfall (Pieces) | Shortfall (Pieces) | Shortfall (Pieces) | Shortfall (Pieces) | Shortfall (Pieces) | Shortfall (Pieces) | Shortfall (Pieces) | Shortfall (Pieces) | Shortfall (Pieces) | Shortfall (Pieces)
 | Machine
 | 1 | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31
 | 2 | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31
 | 3 | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31
 | 4 | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31
 | 5 | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31
 |  |  | Stock on hand
 |  |  | 1234 | 100
 |  |  | 4444 | 10
 |  |  | 5555 | 100
 |  |  | Questions
 |  | 1 | How can I allocate produciton onto machine 2 (for material group 1234) as machine 1 is full? (Without a manual intervention like in cells C4:N5)
 |  | 2 | How can I then smooth producton month to month via formula/code