Service level

Background

There has been some problems with out of stock situations during last year, 2019. You want to analyze the extent of the problems. You have extracted:

Opening stock balance for 2019 (data source: WMS).
Stock transactions inbound and outbound for 2019 (data source: WMS).

Download data file − Case service level

Exercises

Exercise 1 [Sort]

Sort the Table in Transactions sheet on Transaction date (oldest to newest) and Material.

Exercise 2 [IF]

Create a new column Transaction quantity + / – in the Transactions sheet Table with a formula that returns:

Outbound transaction quantity (to customers) as negative.
Inbound transaction quantity (from suppliers) as positive.

Exercise 3 [IF, VLOOKUP, MATCH, OFFSET]

Create a new column Stock balance backorders in the Transactions sheet Table with a formula that returns:

Opening stock balance if this row is the first row for a material.
Stock balance from previous row if this row contains the same material as previous row.

Exercise 4 [IF]

Create a new column Stock balance in the Transactions sheet Table with a formula that returns:

Zero (0) if Stock balance backorders contains numbers less than zero.
Stock balance backorders value if Stock balance backorders does not contain numbers less than zero.

Exercise 5 [VLOOKUP]

Create a new column Supplier name in the Transactions sheet Table with a formula that returns Supplier name for the materials.

Exercise 6 [Style and structure]

Change cell styles of all formulas to the Calculation cell style.

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

Create a new Calculation Sheet named Service level.
Add a Table listing all the suppliers.
Add a column, Sales order lines count, showing the number of sales order lines per supplier.
Add a column, Backorders count, showing the number of sales order lines that has a negative stock balance per supplier.
Add a column, Order line fill rate, a KPI showing in percentage the amount of order lines that has not been delivered complete directly from stock per supplier.
Add Conditional Formatting for the Order line fill rate column with a scale showing green, yellow and red.

Solution: Exercise 1

The Table must be sorted first on Transactions data (oldest to newest) and then on Material (order does not matter).

Solution: Exercise 2

Table: TableTransactions

Table Header: Transaction quantity + / –

= IF([@[Order type]]=trackOrderType;
[@[Transaction quantity]]*-1;
[@[Transaction quantity]]
)

Solution: Exercise 3

First the formula compares this row to previous row for Material.

If it this row is not the same as previous row, it means (if the Table is sorted correctly) that this is the first time this material occurs. That means fetch the opening stock balance for the year via VLOOKUP, then add or deduct the transaction quantity.

If this row is the same as previous row, iterate. That is take the stock balance from previous row, then add or deduct the transaction quantity.

Note: When a formula is long and complex it might be of help to divide the formula into several columns. In this case however, we optimize the calculation speed of formula if we only do VLOOKUP when needed via the IF Function. This is especially important when doing VLOOKUP in even larger amounts of data.

Table: TableTransactions

Table Header: Stock balance backorders

= IF([@[Material]]<>OFFSET([@[Material]];-1;0);
VLOOKUP([@Material];TableStockBalance;
MATCH(TableStockBalance[[#Headers];[Stock balance]];TableStockBalance[#Headers];0);
0)+[@[Transaction quantity + / -]];
OFFSET([@[Stock balance backorders]];-1;0)+[@[Transaction quantity + / -]]
)

Solution: Exercise 4

If we would like to calculate stock value for instance, it is important to remove backorders.

Table: TableTransactions

Table Header: Stock balance

= IF([@[Stock balance backorders]]<0;
0;
[@[Stock balance backorders]]
)

Solution: Exercise 5

Table: TableTransactions

Table Header: Supplier name

= VLOOKUP([@Material];TableStockBalance;
MATCH(TableStockBalance[[#Headers];[Supplier name]];TableStockBalance[#Headers];0);
0)

Solution: Exercise 6

Go to Home > Styles > Calculation.

Solution: Exercise 7

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

Table: TableServiceLevel

Table Header: Sales order lines count

= COUNTIFS(
TableTransactions[Supplier name];[@[Supplier name]];
TableTransactions[Order type];trackOrderType
)

Count with criteria Supplier name and Order type SO.

Table: TableServiceLevel

Table Header: Backorders count

= COUNTIFS(
TableTransactions[Supplier name];[@[Supplier name]];
TableTransactions[Order type];trackOrderType;
TableTransactions[Stock balance backorders];"<"&0
)

Count with criteria Supplier name, Order type SO and stock balance less than zero (0).

Table: TableServiceLevel

Table Header: Order line fill rate

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

Formula to calculate the KPI Order line fill rate.

Use Conditional Formats with Scales.

Download solutions file − Case service level solutions

Excel Learning Resources

Courses

Service level

Solution: Exercise 1

Solution: Exercise 2

Solution: Exercise 3

Solution: Exercise 4

Solution: Exercise 5

Solution: Exercise 6

Solution: Exercise 7

About

Policies