Service KPI

[bluetaurean]

Hello,

Trying to put a service KPI (reaction time) from when orders are placed.

a. there is a lot size per customer in number of pallets - 24 or 22 or 20 etc
b. everyday orders arrive from various customers in various pallet multiples which will be more or less than a lot size
c. whenever the cumulative order sizes crosses the lot size for the customer a full order is logged with the last date as the reference point. the full order will be multiple of lot sizes only.

reaction time is measured from the time the full order is in place and the difference between the full order date and shipment date - not part of this file.

i need help in creating table c from in the inputs a and b. refer attached for as a draft example.

 

[NARAYANK991]

Hi ,

Is VBA acceptable ?

Narayan

 

[Nebu]

Hi:

Since I was working on it.

I thought there is a flaw in your logic, why are you not considering the products while estimating the pallets. I have considered the product as well. The orange shaded cells you decide you threshold, the grey colored is the output. Again this is just based on my understanding. If this is not what you are not looking for let us know.

Thanks

 

[bluetaurean]

@NARAYANK991 - yes indeed a vba solution is preferred.

@Nebu - yes it would be great if we can consider product as well. however the lot size is at the customer level not the product level else the smaller volume products will never get shipped. Effectively the truck gets loaded on a order date priority basis (firstcome first serve) however the date that is to be considered is the date when it the total load reaches the lot size for the customer. a load can any combination of products. thanks for your help.

 

[Nebu]

Hi:

I have revised the code as per your need.

Thanks

 

[NARAYANK991]

Hi ,

As far as I have understood this problem , the spillover , each time a lot size is completed , needs to be carried forward.

That is the reason a formula-based solution is difficult.

I would request OP to clarify what the output will be with the following input data for one customer , whose lot size is 24 :

01-05-2016 - 54
01-05-2016 - 54
01-05-2016 - 54
01-05-2016 -- 1
01-05-2016 - 54
01-05-2016 -- 2
01-05-2016 - 45

Narayan

 

[bluetaurean]

@ Naryan - Yes the spillover has to be carried forward.

@Nebu - thanks for your help. in this solution, if i change the number to 22 or 46 the output is less than it should be. Perhaps some tweaking in the code is needed.

 

[Nebu]

Hi:

I am not sure what you meant by the output is less. This is what I got after changing the numbers.
Note: You have to clear the output column before running the code.

Thanks

 

[bluetaurean]

@Nebu Please refer attached example where the first number is 46. in this case the total orders for customer A are 144 pallets, only 96 get to the next table as schedule. Also will it be possible to add the product to this table to show what product mix the load is made of. attached file shows what the out put would be

 

[Nebu]

Hi:

The product details can be brought in but it won't make much sense because of the spill over from the previous stock , say for eg: for Customer A on 1/5/2016 you can send 48 pallets but it is split into 24 pallets of apple and 24 pallets of oranges. If you want to include products you will have to fix the threshold for customer lot for each customer/product combination, and change the sumif formula. Anyhow, I have fixed the code without considering the product.

Note: For customer A for 1/05/2016 the load is split into two rows of 24 each, this is because of the differences in the product as i mentioned above.

Thanks

 

[bluetaurean]

Thanks Nebu. this is very helpful.