KPIs

Overview

The main goal of the profitability project is to calculate net margin, and slice it by various dimensions.

One of the main challenges is to allocate common costs to specific sales. For example, some supplyers provide staggered bonuses that are triggered at a certain threshold. Or when a client orders multiple items, but pays transport cost only once, then it needs to be split up / allocated.

KPQ

How can we know what our margins are, by:
- product
- channel
- time period
- categories
- brands

KPIs

Entities

Indices

• \(s\): a supplier
  • \(b\): a brand
    • \(i\): an Item in our sales catalog (i.e. an SKU)
• \(o\): an order
  • \(l\): an order line, consisting of
    • \(q\): a quantity of an
    • \(i\): item
• \(v\): an inVoice

Margin

Gross Margin

For a single SKU \(i\) of an order \(o\), the margin is calculated as

\[ M^{gross}_{i,o} = \frac{P^{sale}_{i,o} + R^{delivery}_{i,o} - P^{cost}_{i,o}}{P^{cost}_{i,o}} \approx 25\% \]

With:

• \(M_{i,o}\) = Margin of SKU \(i\) on order \(o\).
• \(P\) = Price
• \(R\) = Shipment revenue (the shipment cost charged to the client, if any. This is on the invoice, and not on the item!)

NOTE: After feedback from Frederic we decided that the Gross Margin already takes into account the marketplace fee, handling and storage. Also all Transport costs should be included.

Net Margin

\[ M^{net}_{i,o} = \frac{P^{sale}_i + R^{delivery}_o - P^{cost}_i - F_{i,o} - T_{i,o}}{P^{cost}} \]

Where \(F\) is the marketplace, storage and handling Fee paid by the merchant

\[F_{i,o}= F^{marketplace}_{i,o} + F^{handling}_{i,o} + F^{storage}_{i,o}\]

With:

• \(F^{marketplace}_{i,o}\) : A fee charged by the market place, e.g. BOL, for each sale
• \(F^{handling}_{i,o}\) : A handling fee charged by BOL to expediate a package (Pick&Pack)
• \(F^{storage}_{i,t}\): Storage cost in BOL Warehouse, for a period of time

and \(T\) is the shipment cost defined as:

\[T_{i,o,t}=T^{I}_{i,t} + T^{II}_{o}\]

With:

• \(T^{I}_{i,t}\): Transport from Profilec Warehouse to BOL LVB
• \(T^{II}_{i,o,t}\): Transport from Warehouse or LVB to Client (last mile)

Cost Allocation

Generally speaking, we allocate the costs as follows to a item \(i\) in an order \(o\):

• if the cost is on the SKU, but not on the order (e.g. \(F^{storage}\)), then we are storing this data in the table FactSalesCost. To calculate the impact on an order line, we take the total cost per analysis period (e.g. per month) for that SKU, divided by the number of items of that SKU sold during that same period.
• if the cost is on the order, but not on the order line item (such as e.g. Shipment Cost) then we spread it pro rata by money value. These are stored redundantly in FactCustomerInvoiceLine, and we can calculate the pro rata share per order line.
• if the cost is neither on the order nor on the item (e.g. salaries, rent, etc.), then it's considered a fixed cost. We exclude it from the calculation of the net margin.

Variables

Conditions overview

Cost Price \(P^{cost}\)

\[P^{cost}_{i,o}=P^{invoice}_{i,o} - SC - BC - TBC\]

Where:

• \(P^{invoice}_{i,o}\): The cost-price for the invoice at hand for the SKU, as calculated by logic4$
• \(SC\): Sellout Conditions, defined on a SKU
• \(BC\): Brand Conditions, defined on brand
• \(TBC\): Target Brand Conditions, defined on brand

In more detail, this is:

\[P^{cost}_{i,o}=P\$^{invoice}_{i,o} \cdot( 1 - BC\%^{supplier} - BC\%^{buying-group} - TBC\%^{supplier} - TBC\%^{buying-group}) - SC\$\]

SC: Sellout Conditions

Sometimes, brands offer a sales commission in order to stimulate sales.
In the Excel-Source, we will store the price reduction per item sold, as a money amount and for a given time period.

\(SC_i\): Sales condition amount, as stored in Excel.

BC: Brand Conditions

Sometimes, a supplier is offering sales conditions on a brand instead of on an SKU. This is called a Brand Condition, or \(BC\).

The formula for the brand condition is:

\[BC_{b, y} = BC^{supplier}_{b, y}+BC^{buying-group}_{b, y}\]

for a given brand \(b\) and a year \(y\).

We store \(BC_b\) as a percentage in an Excel sheet, per brand, for a given time period.

TC: Target Back Conditions

Sometimes, brands offer a back condition that triggers when a certain sales target is reached for the brand. This is called target back condition, which we denote as \(TBC\).

There are different thresholds for the target back conditions, and this is very difficult to estimate systematically in advance. For the purpose of BI, we will track the most likely back condition per brand, before it is realised.

\[TBC_{b, y} = TBC^{supplier}_{b, y}+TBC^{buying-group}_{b, y}\]

We store \(TBC_{b,y}\) in an Excel sheet, per brand and per year.

Delivery Revenue \(R^{delivery}\)

This is the cost charged to and paid by the client. We need to split the delivery cost if an order contains multiple line items. We allocate pro-rata, based on the sales price, such that for line item \(i\) in an order, we have:

\[R^{delivery}_{i}=R^{delivery}_{o}\cdot (\frac{Q_i\cdot P^{sale}_{i}}{OrderValue})\]

Where:

• \(P^{sale}_{i}\): The sales price of item / SKU \(i\)
• \(OrderValue\): The total amount of the order, in EUR
• \(R^{delivery}_{o}\): Cost of shipment of the order, charged to the client

The applicable transport cost charged to the client depends on the channel. However, we can get it centralised from logic4 (versending).

Note that, for BI purpose, all costs and prices are always without VAT.

Fees \(F\)

Marketplace Fees \(F^{marketplace}_{LVB}\)

The Market Place Fee is charged by BOL, and is only appliccable for items stored in LVB. It is available per item via API.

Handling Fees \(F^{handling}\)

The handling fee depends on the Warehouse (LVB or own Warehouse).

LVB \(F^{handling}_{LVB}\)

The BOL handling fee (Pick & Pack) is available per item via API.

LVB is the drop shipping service from Bol. The merchant pays various fees for the service.

There are six different BOL warehouses. They charge for storage, based on the size.

Warehouse \(F^{handling}_{WH}\)

For shipments handled by Profilec, we define a fixed cost per SKU category (Excel).

Storage Fees

\(F^{storage}_{LVB}\)

The storage fee applied by BOL/LVB is available per item.

TODO: Do they report it by item sold? Probably not, but it's per item stored. If that's the case, then we need to apply some sort of FIFO or LIFO accounting of the items sold.

\(F^{storage}_{WH}\)

The storage fee for shipments stored in the Warehouse is defined as a fixed cost per SKU category. We store it in an Excel.

Transport fees \(T\)

Warehouse \(T^{I}\)

TODO: Need to understand this better. In the consulting, we discussed the different BOL sizes, and an exception list per SKU. But it's not clear how this relates to the channels.

Last Mile \(T^{II}\)

Q: How do we know which shipment has which type?
A: We know from logic4.

Post NL to BE \(T^{II}_{Post NL -> BE}\)

We get the cost from Excel.

Post NL to NL \(T^{II}_{Post NL -> NL}\)

For deliveries to NL, Post NL charges a seasonality surcharge in high season.

We get the cost from Excel (and the type from logic4).

\(T^{II}_{dynalogic}\)

We have a price list depending on size and service. As a matter of fact, we have different service levels from dynalogic, such as:
- shipment to curb
- shipment into house
- installation in house

However, for now we ignore the service level. We just take the price from an Excel.

LVB \(T^{II}_{LVB}\)

We take the cost from BOL and split pro rata based on sales price

\[T^{II}_{i}=T_{II}^{order}\cdot (\frac{Q^i\cdot P_{sale}^{i}}{OrderValue})\]

Overview Table: Channel, Storage, and shipment Method

Channel	Storage	Delivery Methods	\(F^{marketplace}\)	\(F^{handling}\)	\(F^{storage}\)	\(T^{I}\)	\(T^{II}\) (last mile)
Webshop	Profilec WH	Post NL	0	\(F^{handling}_{WH}\)	\(F^{storage}_{WH}\)		\(T^{II}_{Post NL -> BE}\) or \(T^{II}_{Post NL -> NL}\)
Webshop	Profilec WH	Dynalogic	0	\(F^{handling}_{WH}\)	\(F^{storage}_{WH}\)		\(T^{II}_{dynalogic}\)
Webshop	Distributor WH	Lanckriet	0	0	0		\(T^{II}_{lanckriet}\)
Bol	LVB	LVB	\(F^{marketplace}_{LVB}\)	\(F^{handling}_{LVB}\)	\(F^{storage}_{LVB}\)		\(T^{II}_{LVB}\)
Bol	Profilec WH	Post NL, Dynalogic	0	\(F^{handling}_{WH}\)	\(F^{storage}_{WH}\)		\(T^{II}_{Post NL -> BE}\) or \(T^{II}_{Post NL -> NL}\)
Bol	Distrutor WH	Lanckriet	0	\(F^{handling}_{WH}\)	\(F^{storage}_{WH}\)		\(T^{II}_{Post NL -> BE}\) or \(T^{II}_{Post NL -> NL}\)
Shop	Profilec WH	Client	0	0	\(F^{storage}_{WH}\)		0
Shop	Profilec WH	Profilec Van	0	\(F^{handling}_{WH}\)	\(F^{storage}_{WH}\)		0 (because fixed cost)
Shop	Distributor WH	Lanckriet	0	0	0		\(T^{II}_{lanckriet}\)

QUESTION: Does it ever occur that we deliver from Profilec WH -> BOL WH/LVB?