Does anyone have a good way to calculate the weeks of supply based on the current available inventory? I’m trying to add this to a GI and my thought was to base it on the qty sold over the past 60 days; taking that qty divided by 8.57 (the approximate # of weeks that are in 60 days) would give me the qty sold per week. Then I’d need to divide the qty available by the qty sold per week to get the # of weeks supply. But I can’t figure out how to make that work in a formula.
Solved
How to calculate weeks of supply
Best answer by mikeho
In case anyone else can use it, our new VAR was able to assist in producing a calculation that worked for our needs:
Average Weekly Sales:
=IIF(DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today())>0,Sum(IIf( [INTran.TranType] = 'INV', [INTran.Qty], 0)) / DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today()), 0)
Weeks of Supply:
=IIF((IIF(DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today())>0,Sum(IIf( [INTran.TranType] = 'INV', [INTran.Qty], 0)) / DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today()), null))<>0, MAX([INLocationStatus.QtyAvail])/(IIF(DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today())>0,Sum(IIf( [INTran.TranType] = 'INV', [INTran.Qty], 0)) / DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today()), null)), 0 )
+4If you make a condition on the GI to look for @Today - 60 based on tran dates then it should keep a running calculation based on the current date.
Does this sound like what you’re trying to build?
1 person likes this
Hi
+13Hi
Attaching GI which is developed to see the movement of Qty on hand and sold on a weekly basis
Also, you can search with a data filter range. It may help you
Acumatica Functional Consultant & Customer Experience Supervisor
1 person likes this
Thank you for the GI you provided; unfortunately, that doesn’t really help me calculate weeks of supply in a field/column.
Anyone else have any suggestions?
Here’s what I’ve come up with to determine the Average Weeks of Supply:
=Sum(IIf( [INTran.TranType] = 'INV', [INTran.Qty], 0)) / DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today())Basically taking the total qty sold and dividing that by the number of weeks between the 1st receipt date and today. Returns accurate results from what I can tell.
Where I’m currently stuck is I’m trying to now take the total qty available using Max( [INLocationStatus.QtyAvail], ) and trying to divide that by the above formula. Problem is I’m getting a “Divide by Zero” error and not sure how to work around that.
In case anyone else can use it, our new VAR was able to assist in producing a calculation that worked for our needs:
Average Weekly Sales:
=IIF(DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today())>0,Sum(IIf( [INTran.TranType] = 'INV', [INTran.Qty], 0)) / DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today()), 0)
Weeks of Supply:
=IIF((IIF(DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today())>0,Sum(IIf( [INTran.TranType] = 'INV', [INTran.Qty], 0)) / DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today()), null))<>0, MAX([INLocationStatus.QtyAvail])/(IIF(DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today())>0,Sum(IIf( [INTran.TranType] = 'INV', [INTran.Qty], 0)) / DateDiff( 'week', Min(SWITCH( [INTran.TranType] = 'RCP', [INTran.TranDate]), ), Today()), null)), 0 )
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.