Le ratio d’écoulement des stocks, également appelé Days Inventory Outstanding (DIO), mesure la durée de conservation au sein de l’entreprise d’un produit.

Plus le ratio est élevé, moins bonne est la situation pour l’entreprise car elle est obligée d’avancer l’argent jusqu’à ce qu’un acheteur vienne à acheter le produit.

Cash is king !

Une entreprise commence par dépenser avant de vendre et d’espérer dégager une marge. L'approvisionnement, la production, le stockage et la distribution représentent des coûts qu’elle doit être en mesure de financer.

La gestion des flux financiers est une stratégie d’approvisionnement à part entière. La direction financière a pour rôle de permettre le financement en avance. À charge pour le supply chain manager de savoir expliquer ses besoins et de participer activement à l’amélioration du cash flow de l’entreprise.

Pour embrasser pleinement cette dimension, le manager supply chain se doit de maîtriser les indicateurs de la direction financière. Le premier d’entre eux est certainement le BFR : encours moyens des créances clients et stocks moyens d’un côté; dettes fournisseurs de l’autre.

Chacune des composantes du BFR est analysée en tenant compte de son délai d'écoulement.

Pourquoi calculer le ratio d’écoulement des stocks ?

Le ratio d’écoulement des stocks mesure la durée de conservation au sein de l’entreprise d’un produit. C'est une mesure de liquidité et aussi un indicateur de l'efficacité opérationnelle et financière d'une entreprise.

Un ratio faible en nombre de jours indique qu'une entreprise est en mesure de transformer rapidement son inventaire en vente. Ce peut être le signe d’une entreprise efficace en termes de gestion des stocks et de performance des ventes.

Un ratio élevé en nombre de jours indique qu'une entreprise n'est pas en mesure de transformer rapidement son inventaire en vente. Cela peut être dû à de mauvaises performances de vente ou à des stocks trop importants.

Avoir trop de stocks inutilisés est préjudiciable à une entreprise car les stocks peuvent devenir obsolètes et parce qu’ils mobilisent une trésorerie importante d’autre part.

Le ratio d’écoulement des stocks est un indicateur. Le nombre de jours dans l’absolu n’est ni bon ni mauvais tant qu’il n’est pas comparé avec :

Celui d’autres entreprises du même secteur
Celui de la période précédente
Celui d’autres produits ou famille de produits comparable

C’est donc bien l’écart entre deux indicateurs comparables qui donnera l’information attendue. Pour cela, les deux indicateurs doivent concerner des périodes comparables et avoir été calculé de la même manière.

DAX ou Power Query ?

J’ai eu l’idée de cet article en lisant le livre Dax Cookbook de Greg Deckler. L’auteur explique comment calculer cet indicateur en DAX à partir des ventes hebdomadaires et des niveaux de stocks.

Si la construction de la mesure est relativement simple, elle nécessite de bien maîtriser plusieurs notions avancées en DAX.

Je vous propose d’effectuer le même calcul mais avec Power Query. Une manière d’appliquer la célèbre maxime de Mattew Roche (Roche’s maxim) : “Data should be transformed as far upstream as possible, and as far downstream as necessary”.

Lorsqu’on se pose la question DAX ou Power Query, Mattew Roche nous réponds de toujours choisir Power Query si Power Query est capable de le faire.

Power Query est plus en amont que DAX dans la chaïne de transformation des données
Les données calculées sont chargées à chaque actualisation du rapport
La logique du rapport est simplifiée et donc plus facile à maintenir
Le rapport sera probablement plus performant

Calculer cet indicateur en DAX pourrait se justifier si son résultat dépendait du contexte et des interactions de l’utilisateur. Son calcul en DAX deviendrait logique car il serait nécessaire.

Nous allons utiliser Power Query pour calculer le ratio d’écoulement des stocks au niveau global d’une entreprise ou d’un entrepôt pour chaque semaine de la période.

En cinq étapes, vous allez pouvoir calculer automatiquement le ratio d’écoulement des stocks avec Power Query et l’exploiter dans vos reports Excel ou Power BI.

Nous utiliserons un seul fichier source comportant les coûts des marchandises vendues et le niveau de stocks pour chaque semaine.

Comme souvent en supply chain, les formules de calcul ne présentent pas de difficulté :

Ecoulement des stocks = Stock moyen / Coût des marchandises vendues (COGS) * nombre de jours dans la période

Exprimé en nombre de jours, son interprétation par les équipes supply est souvent jugée plus facile.

Importer les données de votre fichier dans Power Query

La première étape consiste à charger les données du fichier Excel.

Créer un nouveau fichier Power BI
Cliquez sur le bouton Import data from Excel
Sélectionner le fichier et cliquer sur Ouvrir
Dans la fenêtre Navigator, sélectionner la feuille Data et cliquer sur Transform Data

Les données de la feuille Excel sont chargées dans Power Query. Nous avons pour chaque semaine, le coûts des articles expédiés (Sales COGS) et le niveau de stock à la fin de la semaine (Ending Inventory)

Calculer le stock en début de période

Reprenons la fomule de calcul du ratio d’écoulement des stocks.

Stock moyen / Coût des articles vendues (COGS) /  * nombre de jours dans la période

Pour calculer le numérateur (stock moyen), nous devons calculer la moyenne des articles en stocks pendant la période.

Stock moyen = (Stock en début de période + stock en fin de période) / 2

Dans les données sources, nous avons bien la valeur du stock en fin de semaine (Ending Inventory). Il nous manque celui du début de semaine. Nous savons que le stock de début de période est égale au stock de fin de la période précedente.

Premier problème : Power Query travaille à la ligne. Il n’a aucune connaissance des valeurs de la ligne précédente. Plusieurs techniques peuvent être utilisée pour récupérer la valeur du stock de la semaine précédente. Nous allons voir comment y arriver en utilisant uniquement l’interface de Power Query .

Le principe est le suivant : nous allons indexer nos lignes ; une fois en partant de 0 et une autre fois en partant de 1. Puis, nous allons fusionner la table avec elle-même en utilisant les 2 colonnes d’index comme colonnes de jointure. Et ainsi, récupérer pour chaque ligne de notre table, la valeur du stock de la semaine précédente.

Commençons par créer les 2 colonnes d’index.

• Dans le menu Add Column, sélectionnez Index Colum From 0.

• Répeter l’opération. Mais cette fois-ci, sélectionnez Index Column From 1.

Nous pouvons maintenant fusionner la table avec elle-même en utilisant les 2 colonnes d’index comme colonnes de jointure.

Dans le menu Home > Combine, Sélectionner Merge Queries
Dans la fenêtre Merge, la table Data est déjà affichée comme première table
Sélectionnée la table Data (Current) comme deuxième table
Cliquez sur la colonne Index de la première table, puis sur la colonne Index.1 de la deuxième table puis cliquez sur Ok
Développez la nouvelle colonne Added Index1 en ne selectionnant que la colonne Ending Inventory
Trier la table (Sort Ascending) sur la colonne Week

La première semaine n’ayant pas de semaine précédente, la valeur de notre nouvelle colonne est à null. Nous allons estimer la valeur du stock en début de première semaine en ajoutant à la valeur de fin de semaine la valeur de la demande pour cette semaine.

Dans le menu Add Column > General, cliquez sur Custom Column
Dans la fenêtre Custom Column, nommez la colonne Beginning Inventory
Entrez la formule suivante et cliquez sur OK

if [Ending Inventory.1]=null then [Ending Inventory]+[#"Sales (COGS)"] else [Ending Inventory.1]

Changer le type de données de la colonne en Whole number
Supprimer les deux colonnes d’index et la colonne Ending Inventory.1 dont nous n’avons plus besoin

Calculer le stock moyen pour chaque semaine

Avec le niveau de stock en début et fin de chaque semaine, nous pouvons maintenant calculer le stock moyen.

Dans le menu Add Column > General, cliquez sur Custom Column
Dans la fenêtre Custom Column, nommez la colonne Average Inventory
Entrez la formule suivante et cliquez sur OK

([Beginning Inventory]+[Ending Inventory])/2

Changer le type de données de la colonne en Decimal number

Calculer le coût moyen des marchandises vendues

Nous allons calculer le coût moyen des marchandises vendues et ramener sa valeur en face de chaque semaine. Nous pourrons ensuite très facilement calculer le ratio d’écoulement des stocks pour chaque semaine par une simple division ligne à ligne.

Encore une fois, Power Query travaille à la ligne. Dans une colonne personnalisée (Custom Column), nous allons entrer une formule qui va nous permettre de récupèrer la liste des valeurs de la colonne Sales (COGS) et d’en calculer la moyenne.

La liste de valeurs dont nous voulons calculer la moyenne est la colonne Sales (COGS) et la fonction qui va nous permettre de calculer le coût moyen des marchandises vendues est la fonction List.Average(). Elle prends comme argument une liste et renvoie la moyenne de ces valeurs.

Pour extraire les valeurs de la colonne Sales (COGS), la syntaxe à utiliser est la suivante : List.Average(NomTable[NomColonne] où le nom de la table est le nom de l’étape de transformation (Applied Steps) précédente : #"Changed Type2" et le nom de la colonne : [#"Sales (COGS)"]

Dans le menu Add Column > General, cliquez sur Custom Column
Dans la fenêtre Custom Column, nommez la colonne Average Demand
Entrez la formule suivante et cliquez sur OK

List.Average(#"Changed Type2"[#"Sales (COGS)"])

Changer le type de données de la colonne en Decimal number

Calculer le ratio d’écoulement des stocks

Nous avons calculer le stock moyen et le coût moyen des marchandises vendues. La dernière étape consiste simplement pour chaque semaine à diviser le stock moyen (Average Inventory) par le coût moyen des marchandises vendues (Average Demand) multiplié par le nombre de jours dans la période (7).

Dans le menu Add Column > General, cliquez sur Custom Column
Dans la fenêtre Custom Column, nommez la colonne DoS pour Days of Supply
Entrez la formule suivante et cliquez sur OK

[Average Inventory]/[Average Demand]*7

Changer le type de données de la colonne en Whole number
Supprimer les colonnes Beginning Inventory, Average Inventory et Average Demand qui nous ont servi au calcul du ratio d’écoulement des stocks

Dans notre table, nous avons calculer le ratio d’écoulement des stocks en nombre de jours que nous allons pouvoir utiliser dans notre report.

Chaque semaine, vous n’avez plus qu’à remplacer le fichier source avec le fichier de la nouvelle extraction. Et vos données se mettront à jour automatiquement et dynamiquement par un simple clic sur le bouton Actualiser.

Et maintenant, à vous de jouer ! Prenez vos données et suivez les étapes pas à pas pour construire votre propre fichier.