Calculate number of MOQ and SPQ to cover demand ?

[klong]

Hi,

Trying to come up with a single formula [not VBA] to calculate how many MOQ (Minimum Order Quantity) and SPQ (Standard Pack Quantity) I need to order to satisfy demand on a row. The MOQ is usually a multiple of th SPQ i.e. 4 x 500 piece SPQs = 1 x MOQ = 2,000 pieces.

Example ("/" used to seperate column data for below example)
Demand / MOQs / SPQs / Total Order
<=2,000 / 1 / 0 / 2,000
2,001 / 1 / 1 / 2,500
2,501 / 1 / 2 / 3,000

I am trying to figure out how to do it in a single column but I'm struggling with a really long winded formula at the moment and thought I'd put it out there !

Column A = MOQ
Column B = SPQ
Column C = Demand
Column D = Order Quantity ([a x MOQ] + [b x SPQ])

I can do it but need additional colums to calculate a & b used in Columd D first !

It needs to be a formula so I can easily recalculate once I change Demand in column C.

Any pearls of wisdom would be greatly appreciated.

Regards,
Ken

 

[venkatesa kumar]

Hi Ken,
Pls find the below Formula to order by calculating SPQ & MOQ's ,I will take one day to find the solution of this SPQ & MOQ.
Hope i will be help ful both of us.

IF(To be ordered Qty<=MOQ,ROUNDUP(To be ordered Qty/MOQ,0)*MOQ,ROUNDUP(To be ordered Qty/SPQ,0)*SPQ

<tbody>
</tbody>

Examples by used this formula :

SPQ	MOQ	To be Ordered	To Be ordered with MOQ
1000	2000	4800	5000
1000	2000	3600	4000
100	500	600	600
750		600	750
10000	30000	34200	40000
10000	20000	3600	20000
5000		600	5000

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Tks & regards
Venkateesa KumarM
Vortex

 

[venkatesa kumar]

Hi Ken,
Pls find the below Formula to order by calculating SPQ & MOQ's ,I will take one day to find the solution of this SPQ & MOQ.
Hope i will be help ful both of us.
& A good news by using this formula #Div/0 Error won't come.

=IFERROR(IF(To be ordered Qty<=MOQ,ROUNDUP(To be ordered Qty/MOQ,0)*MOQ,ROUNDUP(To be ordered Qty/SPQ,0)*SPQ),"0")

<tbody>
</tbody>

Examples by used this formula :

SPQ	MOQ	To be Ordered	To Be ordered with MOQ
1000	2000	4800	5000
1000	2000	3600	4000
100	500	600	600
750		600	750
10000	30000	34200	40000
10000	20000	3600	20000
5000		600	5000

<tbody>
</tbody>

Tks & regards
Venkateesa KumarM
Vortex