Delivery performance

Background

The Supply Chain Manager would like you to present supplier delivery performance for Q1 2019. You choose to present delivery performance as the KPI Perfect order fulfillment. You define the KPI as right time and right quantity. Right time is further defined as deliveries that are not too late. Early deliveries are considered acceptable. You extract the following report containing:

Purchase orders placed Q1 2019 (data source: ERP report)
Goods receipts for the same orders (data source: ERP report)

Tip: Perfect order criteria

Perfect orders can be defined according to the criteria: right product, right quantity, right state, right place, right time, right customer, right cost.

Download data file − Case delivery performance

Exercises

Exercise 1 [IF, Logical operators]

Create a new column Right time? in the Purchase orders sheet Table with a formula that returns:

TRUE if Goods receipt date is less than or equal to Requested delivery date.
Else FALSE.

Exercise 2 [IF, Logical operators]

Create a new column Right quantity? in the Purchase orders sheet Table with a formula that returns:

TRUE if Purchase order quantity is equal to Goods receipt quantity.
Else FALSE.

Exercise 3 [IF, Logical operators, AND]

Create a new column Perfect order? in the Purchase orders sheet Table with a formula that returns:

TRUE if Right time? and Right quantity? is TRUE.
Else FALSE.

Exercise 4 [Style and structure, COUNTIFS, Conditional Formatting]

Create a new Calculation Sheet named Delivery performance.
Add a Table listing all the suppliers.
Add a column, Purchase order lines count, showing the number of purchase order lines per supplier.
Add a column, Perfect purchase order lines count, showing the number of purchase order lines without deviation.
Add a column, Perfect order fulfillment supplier, a KPI showing in percentage the amount of order lines that has been delivered without deviation.
Add Conditional Formatting for the Order line fill rate column with a scale showing green, yellow and red.

Solution: Exercise 1

Table: TablePurchaseOrders

Table Header: Right time?

= IF([@[Goods receipt date]]<=[@[Requested delivery date]];TRUE;FALSE)

Solution: Exercise 2

Table: TablePurchaseOrders

Table Header: Right quantity?

= IF([@[Purchase order quantity]]=[@[Goods receipt quantity]];TRUE;FALSE)

Solution: Exercise 3

Table: TablePurchaseOrders

Table Header: Perfect order?

= IF(AND([@[Right time?]];[@[Right quantity?]]);TRUE;FALSE)

Solution: Exercise 4

The quickest way to get all suppliers in a Table is to copy a sheet, remove duplicates and remove redundant data.

Table: TableDeliveryPerformance

Table Header: Purchase order lines count

= COUNTIFS(
TablePurchaseOrders[Supplier name];[@[Supplier name]]
)

Count with criteria Supplier name.

Table: TableDeliveryPerformance

Table Header: Perfect purchase order lines count

= COUNTIFS(
TablePurchaseOrders[Supplier name];[@[Supplier name]];
TablePurchaseOrders[Perfect order?];TRUE
)

Count with criteria Supplier name and Perfect order.

Table: TableDeliveryPerformance

Table Header: Perfect order fulfillment supplier

= ([@[Sales order lines count]]-[@[Backorders count]])/[@[Sales order lines count]]

Formula to calculate the KPI Perfect order fulfillment supplier.

Use Conditional Formats with Scales.

Download solutions file − Case delivery performance solutions

Excel Learning Resources

Courses

Delivery performance

Solution: Exercise 1

Solution: Exercise 2

Solution: Exercise 3

Solution: Exercise 4

About

Policies