Calculate DIFOT - %On Time & % Late

Hi

I am trying to Calculate my supplier DIFOT ( delivered in full on time ) % for individual line items on Purchase orders. I may have up to 3 shipments of various quantities for a single item. I am trying to come up with a formula that works out the percentage of stock for a line item that is either On-Time or Late. the Formula needs to look at the On-time % score for each line item shipment that I have already calculated and take them into account.

The line item DIFOT score was based on;

• Actual Shipment date is before or up to 5 working days after the PO EXW date = 0% OT
• Actual Shipment date is between 6 - 10 working days after the PO EXW date = 15% LATE
• Actual Shipment date is between 11 - 20 working days after the PO EXW date = 50% LATE
• Actual Shipment date is greater than 21 working days after the PO EXW date = 100% LATE


The percentage of qty on time needs to be based on;


The sum of qty shipped that is (equal to 0%)= Line shipped on-time qty
The sum of Qty that is (>= 15%) = Line not shipped on-time qty.


I have attached a sample spreadsheet of where I have got to with formulas so far, but I cannot get the correct answers for every scenario. For example if some of the on-time percentage scores are blank for the second and third shipment fields because there was only 1 shipment I get the wrong answer.

Any Help would be appreciated!

Chris

I have adjusted a few of the formulas based on my understanding of the parameters, and have noted these changes on the worksheet. For example I changed the formula in O2 and down to

Please Login or Register  to view this content.

Copy of DIFOT Sample Calculations.xlsx

Let me know if you have any questions.

Hi JeteMC,

Thank you so much. That has fixed my problem!

Cheers, Chris

Your welcome and thanks for the feedback. Please use the thread tools link above your first post and mark the thread 'Solved'. Hope that you have a good day.

Not able to open the sheet since it is protected.