How to Calculate Remaining Inventory when SKUs are repeated?

[Abhishek Adhikari]

Hi All,

This is my first post, so apologies for any errors in the post.

Problem
I have a situation where I need to calculate the available stock for an SKU and throw a comment "Full Kit" if the available stock will cover the order and "Non Full Kit" if the available stock doesnt fulfill the order. In my initial logic, I started getting the output correctly.

But soon after a situation arose, where due to specific customer requirements, the same SKU can be repeated in the same Sales Order. For example

• Current Stock - 150 for SKU A
• First Sales Order
  • requirement 1 - 50
  • requirement 2 - 30
  • requirement 3 - 40
• Second Sales Order - requirement 50

By a simple Vlookup of the SKU available quantity against requirement would be 150 and hence the formula will through "Full Kit".

However, in reality the requirement will remaining inventory will flow as follows

• First Sales order
  • requirement 1 - 50, Balance Stock - 100, Status "Full Kit"
  • requirement 2 - 30, Balance Stock - 70, Status "Full Kit"
  • requirement 3 - 40, Balance Stock - 30, Status "Full Kit"
• Second Sales order - requirement 50, Balance Stock 30, Status "Non Full Kit"

Since I would be using a system generated report sorting it every time the time as per Sales Order number and SKU and then manually calculating doesnt make sense (like in case of creating a Reducing Balance Loan Emi Calculator)

What I have done so far

I have created a sequence formula where, for a particular Warehouse, if a particular SKU gets repeated more than once, the next column throws an incremental number based on the Concatenated value of SKU and Warehouse. For example as below and so on an so forth.

• Line 1, SKU 1, Occurrence 1
• Line 2, SKU 2, Occurrence 1
• Line 3, SKU 3, Occurrence 1
• Line 4, SKU 1, Occurrence 2
• Line 5, SKU 1, Occurrence 3

The Help Needed

A logic, where based on the Occurrence Number and SKU, the quantity of the available inventory should automatically be picked up from the difference of the Stock available and previous Occurrence Requirement.

 

[r1c1]

@Abhishek Adhikari

Welcome to Chandoo.org forums and thanks for posting your question.

You can set up an extra column called "Actual Stock Quantity" and derive the quantity by looking at particular SKU, Stock qty (that you got from warehouse stock report) and how many orders are already fulfilled on that SKU prior to that line item. This can be done with a SUMIFS formula that uses a mix of relative and absolute references, like this:

=D3-SUMIFS($C$2:C2,$B$2:B2,B3,$I$2:I2,"Full Kit")

Here D3 is your Stock Qty column
Column C has Order Qty
Column B has SKU codes
Column I has Full Kit / Non Full Kit info.

Once such a quantity is calculated, then you can decide for current line whether we can fulfill order or not by using an IF formula,

=IF(G3>=C3,"Full Kit","Non Full Kit")

G3 is order qty
C3 is Actual Stock Qty - the new column we added.

Please find attached workbook that shows these calculations.

You can discard the occurrence number calculations as they are not needed.

 

[Abhishek Adhikari]

Thanks for the quick reply. Could you please attach the file? I'll meanwhile try to recreate it based on your response. But I just want to be sure.

 

[r1c1]

Oops.. I thought the workbook was attached. Here you go.