Excel model to affect Outbound Capacity Constraints

M1donne

New Member
Joined
Apr 7, 2014
Messages
44
Hi all,

Happy bank holiday from the UK.

I'm trying to create an Excel model which tracks my outbound capacity from a warehouse and identifies where the capacity is forecast to be exceeded, and adjusts the outbound volume to the weeks where there is spare capacity.

[TABLE="width: 1355"]
<tbody>[TR]
[TD]At the moment I have the data as follows..

[TABLE="width: 1355"]
<tbody>[TR]
[TD]week[/TD]
[TD][/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]Outbound [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD]Outbound Constraint[/TD]
[TD][/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]Capacity[/TD]
[TD][/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-15000[/TD]
[TD="align: right"]-15000[/TD]
[TD="align: right"]-25000[/TD]
[TD="align: right"]30000[/TD]
[/TR]
[TR]
[TD]Constrained Supply[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-15000[/TD]
[TD="align: right"]-15000[/TD]
[TD="align: right"]-25000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Cumulative CS[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-15000[/TD]
[TD="align: right"]-30000[/TD]
[TD="align: right"]-55000[/TD]
[TD="align: right"]-55000[/TD]
[/TR]
</tbody><colgroup><col><col span="19"></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Essentially I have breached my outbound capacity in weeks 49,50,51 and 52 and need to make use of spare capacity in the weeks with capacity.


Any thoughts or initial suggestions would be massively appreciated.

Regards [/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col span="19"></colgroup>[/TABLE]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
you are using jargon that you are familiar with, we are not. Is outbound a known future order that is filled ? what is outbound constraint and constrained supply?
are you making anything in week 35 or is 50000 the max you could make if you chose to ?
 
Upvote 0
you are using jargon that you are familiar with, we are not. Is outbound a known future order that is filled ? what is outbound constraint and constrained supply?
are you making anything in week 35 or is 50000 the max you could make if you chose to ?

Than you

In simple terms I need to make sure every week is no more than 50,000 (outbound row) by increasing the weeks that are less than 50,000 to ensure the total outbound is never greater than the maximum.

What I need to work out is a calculation that looks ahead across the future weeks and identifies where the maximum is exceeded and suggest weeks (with spare capacity) that can be increased.

Kind regards
 
Upvote 0
sorry - still don't understand - can you make up an example with say only 5 months and explain it again...
 
Upvote 0
sorry - still don't understand - can you make up an example with say only 5 months and explain it again...

Hi there,

Thanks for the reply (and patience :-))

In the below example I have weeks 45 to 52

I need to ship from my warehouse 20,000 in week 45, 25,000 in week 46, 35,000 in week 47, 50,000 in week 48

In each of these weeks I can only ship (send out) a maximum of 50,000 units which is fine as they are all less or equal to the 50,000

However from week 49 I need to ship more than I physically can (the 50,000 capacity) so in week 49 I need to find a week where I can ship the 15,000 units earlier (than week 49), equally in week 50 and 51 I have the same issue, therefore I need to fine weeks where I can utilise spare capacity of 55,000 (15,000 from week 49, 15,000 from week 50 and 25,000 from week 51).

The challenge I have is trying to determine (systemically) which weeks I need to increase the outbound ship to make sure for the weeks that are greater than the constraint, I do not exceed capacity - essentially I need to smooth the outbound flow.

[TABLE="width: 715"]
<tbody>[TR]
[TD]week[/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]Ideal to ship[/TD]
[TD][/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD]Outbound Constraint[/TD]
[TD][/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]Spare Capacity[/TD]
[TD][/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-15000[/TD]
[TD="align: right"]-15000[/TD]
[TD="align: right"]-25000[/TD]
[TD="align: right"]30000[/TD]
[/TR]
</tbody><colgroup><col><col span="9"></colgroup>[/TABLE]

Regards
 
Upvote 0
[TABLE="width: 1216"]
<colgroup><col span="19"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]max of 10 per week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]original position[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]week1[/TD]
[TD]week2[/TD]
[TD]week3[/TD]
[TD]week4[/TD]
[TD]week5[/TD]
[TD]week6[/TD]
[TD]week7[/TD]
[TD]week8[/TD]
[TD]week9[/TD]
[TD]week10[/TD]
[TD]week11[/TD]
[TD]week12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]15[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]13[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]after macro is run[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]week1[/TD]
[TD]week2[/TD]
[TD]week3[/TD]
[TD]week4[/TD]
[TD]week5[/TD]
[TD]week6[/TD]
[TD]week7[/TD]
[TD]week8[/TD]
[TD]week9[/TD]
[TD]week10[/TD]
[TD]week11[/TD]
[TD]week12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]For j = Cells(7, 18) To Cells(8, 18) Step -1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"] If Cells(10, j) > 10 Then GoTo 10 Else GoTo 20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]10 Cells(10, j - 1) = Cells(10, j - 1) - (10 - Cells(10, j))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Cells(10, j) = 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]20 Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
See if this does what you want.
Formula in B4 is copied across to I4.
Cell J4 needs to be blank, or at least not contain a numerical value.

Excel Workbook
ABCDEFGHIJK
1week4546474849505152Total
2Ideal to ship2000025000350005000065000650007500020000355000
3Outbound Constraint5000050000500005000050000500005000050000
4Ship this amount3500050000500005000050000500005000020000355000
Capacity







Another example

Excel Workbook
ABCDEFGHIJK
1week4546474849505152Total
2Ideal to ship200002500035000510001000650007500020000292000
3Outbound Constraint5000050000500005000050000500005000050000
4Ship this amount2000025000360005000041000500005000020000292000
Capacity (2)
 
Last edited:
Upvote 0
See if this does what you want.
Formula in B4 is copied across to I4.
Cell J4 needs to be blank, or at least not contain a numerical value.

Capacity

ABCDEFGHIJK
week
Ideal to ship
Outbound Constraint
Ship this amount

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 140px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 23px;"><col style="width: 54px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]

[TD="align: right"]Total[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="align: right"]20000[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]20000[/TD]

[TD="align: right"]355000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: right"]35000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]20000[/TD]

[TD="align: right"]355000[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B4=MIN(B3,SUM(B2:$I2)-SUM(C4:$J4))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4






Another example

Capacity (2)

ABCDEFGHIJK
week
Ideal to ship
Outbound Constraint
Ship this amount

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 140px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 54px;"><col style="width: 30px;"><col style="width: 58px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]

[TD="align: right"]Total[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="align: right"]20000[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]51000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]20000[/TD]

[TD="align: right"]292000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: right"]20000[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]36000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]41000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]20000[/TD]

[TD="align: right"]292000[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B4=MIN(B3,SUM(B2:$I2)-SUM(C4:$J4))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Hi there,

That works perfectly, thank you so much.

Can you please (in laymans terms) explain what the calculation is doing?

Regards
 
Upvote 0
That works perfectly, thank you so much.
That's good. You're welcome.


Can you please (in laymans terms) explain what the calculation is doing?
Doing is easier than explaining, but I'll try. :)


Excel Workbook
ABCDEFGHIJ
1week4546474849505152
2Ideal to ship200002500035000510001000650007500020000
3Outbound Constraint5000050000500005000050000500005000050000
4Ship this amount2000025000360005000041000500005000020000
Capacity (2)



Using this example, look first at the yellow cell, F4
=MIN(F3,SUM(F2:$I2)-SUM(G4:$J4))

The red part in this formula sums the green cells (161,000 being the amount we want to ship in the last 4 weeks) and subtracts the sum of the blue cells (120,000 being the amount we will ship in the final 3 weeks). This difference is 41,000, being the amount we need to ship in the 4th last week to get to target. The MIN part of the formula is a check to limit the answer in this cell to the lesser of what we need to ship (41,000) and what we could possibly ship (50,000)

As a second example, look at the amber cell, B4.
=MIN(B3,SUM(B2:$I2)-SUM(C4:$J4))
The sum of the whole top row is 292,000 (total to ship) the sum of the cells to the right (C4:J4) is 272,000 (what we will ship in those last 7 weeks). The difference is 20,000, which is below the limit of 50,000, so that's what we ship in week 45.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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