Excel model to affect Outbound Capacity Constraints

[M1donne]

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.

At the moment I have the data as follows.. week 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 Outbound 10000 10000 15000 20000 25000 35000 50000 65000 65000 75000 20000 Outbound Constraint 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 Capacity 50000 50000 50000 50000 50000 50000 50000 40000 40000 35000 30000 25000 15000 0 -15000 -15000 -25000 30000 Constrained Supply 0 0 0 0 0 0 0 0 0 0 0 0 0 0 -15000 -15000 -25000 0 Cumulative CS 0 0 0 0 0 0 0 0 0 0 0 0 0 0 -15000 -30000 -55000 -55000 <tbody> </tbody><colgroup><col><col span="19"></colgroup>

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

<tbody>
</tbody><colgroup><col><col span="19"></colgroup>

 

[oldbrewer]

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 ?

 

[M1donne]

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

 

[oldbrewer]

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

 

[M1donne]

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.

week		45	46	47	48	49	50	51	52
Ideal to ship		20000	25000	35000	50000	65000	65000	75000	20000
Outbound Constraint		50000	50000	50000	50000	50000	50000	50000	50000
Spare Capacity		30000	25000	15000	0	-15000	-15000	-25000	30000

<tbody>
</tbody><colgroup><col><col span="9"></colgroup>

Regards

 

[oldbrewer]

						max of 10 per week
						original position
			week1	week2	week3	week4	week5	week6	week7	week8	week9	week10	week11	week12
			4	8	10	8	12	10	9	15	6	8	13	9
						after macro is run										15
			4	5	6	7	8	9	10	11	12	13	14	15			4
			week1	week2	week3	week4	week5	week6	week7	week8	week9	week10	week11	week12
			6	10	10	10	10	10	10	10	7	10	10	9
						macro
						For j = Cells(7, 18) To Cells(8, 18) Step -1
						If Cells(10, j) > 10 Then GoTo 10 Else GoTo 20
						10 Cells(10, j - 1) = Cells(10, j - 1) - (10 - Cells(10, j))
						Cells(10, j) = 10
						20 Next j
						End Sub

<colgroup><col span="19"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H	I	J	K
1	week	45	46	47	48	49	50	51	52		Total
2	Ideal to ship	20000	25000	35000	50000	65000	65000	75000	20000		355000
3	Outbound Constraint	50000	50000	50000	50000	50000	50000	50000	50000
4	Ship this amount	35000	50000	50000	50000	50000	50000	50000	20000		355000
Capacity

Another example

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	week	45	46	47	48	49	50	51	52		Total
2	Ideal to ship	20000	25000	35000	51000	1000	65000	75000	20000		292000
3	Outbound Constraint	50000	50000	50000	50000	50000	50000	50000	50000
4	Ship this amount	20000	25000	36000	50000	41000	50000	50000	20000		292000
Capacity (2)

 

[M1donne]

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

	A	B	C	D	E	F	G	H	I	J	K
1	week	45	46	47	48	49	50	51	52		Total
2	Ideal to ship	20000	25000	35000	50000	65000	65000	75000	20000		355000
3	Outbound Constraint	50000	50000	50000	50000	50000	50000	50000	50000
4	Ship this amount	35000	50000	50000	50000	50000	50000	50000	20000		355000

<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>
</tbody>

Spreadsheet Formulas

Cell Formula 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)

	A	B	C	D	E	F	G	H	I	J	K
1	week	45	46	47	48	49	50	51	52		Total
2	Ideal to ship	20000	25000	35000	51000	1000	65000	75000	20000		292000
3	Outbound Constraint	50000	50000	50000	50000	50000	50000	50000	50000
4	Ship this amount	20000	25000	36000	50000	41000	50000	50000	20000		292000

<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>
</tbody>

Spreadsheet Formulas

Cell Formula 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

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H	I	J
1	week	45	46	47	48	49	50	51	52
2	Ideal to ship	20000	25000	35000	51000	1000	65000	75000	20000
3	Outbound Constraint	50000	50000	50000	50000	50000	50000	50000	50000
4	Ship this amount	20000	25000	36000	50000	41000	50000	50000	20000
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.