Formulas to measure KPIS for different types of Shipments we organize

jorge.pacheco

I have a shipping platform built in Smartsheet, people can submit their request through this platform and different teams gets involved.

Shipping Method:
Export Airfreight
Export Sea freight
Hand Carry
Import/Return Request
Documents

Teams Involved:
Requestor
Regulatory
Warehouse
Logistics

I have been asked to measure our KPIs from the day the request was created until the day it was processed.

Currently, I have these formulas for each team involved:

=IF([Logistics Completion Date]@row = "", "", IF(NETWORKDAYS([Logistics Completion Date]@row, [WHS Completion Date]@row) - 1 < 2, "Pass", "Fail"))

=IF([WHS Completion Date]@row = "", "", IF(NETWORKDAYS([RA Approval Date]@row, [WHS Completion Date]@row) - 1 < 3, "Pass", "Fail"))

=IF([RA Approval Date]@row = "", "", IF(NETWORKDAYS([Date Requested]@row, [RA Approval Date]@row) - 1 < 2, "Pass", "Fail"))

This will show me in percentage if it has passed the KPI or not.

According to this formula, 100% means pass, and 0% means Fail

Two questions:
1. Do you have a better way to measure these KPIs? if so what would be a better way than 100% or 0%?

2. How can I improve this formula to include it by Shipping Method? I believe it would be a complex formula.

These would be the requirements:

If Export Airfreight from the date requested until Shipping Status has been completed if there is more than 7 days it is a Fail 0%, anything below 7 would be a "Pass" 100%.

if Export Sea freight from the date requested until Shipping Status has been completed if there is more than 15 days it is a Fail 0%, anything below 15 would be a "Pass" 100%.

Also, you can see that in the column MISC KPI it shows #INVALID, the reason is that WHS team does not get involved.

Here's the formula in that column:

=IF([Logistics Completion Date]@row = "", "", IF(NETWORKDAYS([Logistics Completion Date]@row, [WHS Completion Date]@row) - 1 < 2, "Pass", "Fail"))

How can I include the Export Sea Freight here so the formula works?

if Hand Carry this one could be a bit more complex because there is a departure day. This is for people who need to travel and carry products. From the date requested until Shipping Status has been completed if there is more than 3 days it is a Fail 0%, anything below 3 would be a "Pass" 100%.

Would the Departure Date need to be included?

Also, you can see that in the column MISC KPI it shows #INVALID, the reason is that WHS team does not get involved.

Here's the formula in that column:
=IF([Logistics Completion Date]@row = "", "", IF(NETWORKDAYS([Logistics Completion Date]@row, [WHS Completion Date]@row) - 1 < 2, "Pass", "Fail"))

How can I include the Hand Carry here so the formula works?

WHS completion is not involved.

Import/Return Request from the date requested until Shipping Status has been completed if there is more than 7 days it is a Fail 0%, anything below 7 would be a "Pass" 100%.

Here's the formula in that column:=IF([Logistics Completion Date]@row = "", "", IF(NETWORKDAYS([Logistics Completion Date]@row, [WHS Completion Date]@row) - 1 < 2, "Pass", "Fail"))

How can I include the Import/Return Request here so the formula works?

WHS completion is not involved.

If Documents from the date requested until Shipping Status has been completed if there is more than 7 days it is a Fail 0%, anything below 7 would be a "Pass" 100%.

WHS completion is not involved.

The idea is to include all the KPIs in just one column instead of different columns:

Also, one last question:

You can see that ther are 100% information in column "Target for Logistics" and "Overall Action Target"

100% means passed when "Logistics completion date" has been added and 0% is a Fail.

The problem I have is that you can see that it is showing 100% just because the date hasn't been included in "Logistics completion date".

I don't want this because it will be considered as a Pass when it hasn't even been completed.

This is the current formula:

=IF([Logistics Process Time]@row > 5, 0, 1)

Is there a way we can do something like:

Show 100% when it is less than 5 Days?
Show 0% when it was more than 5 Days?

but when it is in the process and hasn't been completed, can we show something like "Pending to be processed" or something like that? so it won't show 100%.?

And I want to do the same for the column "Overall Process Time"

=IF([Overall Process Time]@row > 5, 0, 1)


I know this might be complex but your help would be so appreciated.

Let me know if you need to clarify something that maybe I didn't explain properly.

The formulas added here were suggested by the community in the past, I am terrible at formulas, so here's me asking for help again :)

Thanks in advance.

Paul Newcome

Try something along the lines of:

=IF([Completion Date]@row <> "", IF(AND([Shipping Method]@row = "This method", NETWORKDAYS(……….) - 1 < ##), "Pass", "Fail"), "Pending")