Excel : Conserver les zéros d’une référence numérique

Bonjour, je vous propose aujourd’hui une solution à un problème que l’on rencontre lorsqu’on travaille avec des références numérique commençant par un zéro et que l’on souhaite manipuler en tant que chaîne de texte.

Exemple concret :

Nous avons ci-dessus 15 références au format CSV (du texte séparé par des virgules ou points virgules). C’est un format très employé encore aujourd’hui car il permet d’extraire des données d’une base de données sans limite au nombre de lignes/colonnes qui sont extraites.

Il existe dans Excel un outil qui permet de convertir les données de ce format au format tableur (cela s’appelle d’ailleurs « données > convertir »). Le problème qui se pose dans le cas où les références que l’on a sont au format numérique est que les zéros d’une donnée chiffrée disparaissent. En effet pour Excel les chiffres 002347 et 2347 ont la même valeur, il remplace donc 002347 par 2347.

On fait donc un « données / convertir » ou « data / text to columns »

Le menu nous invite à choisir si les données sont séparées par un marqueur particulier (virgule, point virgule, tiret, ou en fait n’importe quel caractère peut être utilisé pour séparer des données) ou séparées selon un espacement prédéfini. Dans notre cas ce sont des points virgule (semicolon en anglais) :

Voici le résultat : les références que nous avons converties au format tableur se retrouvent sans leur zéro initial. Il y a une perte d’information. Imaginons une table avec beaucoup de données, où l’on a les références 002344 et 02347 et 2347, pour Excel il s’agira de la même référence. Cela peut prêter à sourire, mais ce cas de figure arrive dans la vraie vie.

 

L’astuce consiste dans le menu à sélectionner l’option « text » et à sélectionner (en bas en noir) toutes les colonnes que l’on souhaite convertir avec un format de destination « texte ».

 

A la fin de l’opération, la table convertie ressemble à ceci :

Remarquez le petit triangle vert en haut à gauche, il invite l’utilisateur s’il le souhaite (en l’occurence nous ne le souhaitons pas) à convertir les données en nombre. En l’occurrence nous souhaitions faire exactement l’inverse.

Ci-dessous le fichier excel que j’ai utilisé pour les captures d’écran

excel-conserver-les-zeros-dune-reference-numerique

Rdv au prochain article

Création d’un bridge (graphique en cascade) sur Excel

Bonjour à tous !

Au menu du jour, la création d’un graphique cascade sur Excel.

Pourquoi ce post ? Une importante partie du métier de financier est consacrée à communiquer sur des données chiffrées. Un des incontournables de la boîte à outils du financier est le graphique en cascade, qui permet de manière immédiate et parlante de montrer le passage des chiffres d’une période à une autre (ie une hausse ou une baisse), et quels éléments ont contribué à la différence entre les deux périodes. Seulement, si on ne l’a jamais fait, trouver la technique peut être un peu long, d’où l’idée de vous proposer ce petit tutoriel.

Afin de rendre les choses plus parlantes, nous allons prendre l’exemple d’un constructeur automobile. Le résultat de ladite société est passé de 3Mds EUR à 2.4Mds EUR entre 2014 et 2015, soit une diminution de 600 millions, et le passage de 3Mds à 2.4Mds peut être expliqué par quatre événements principaux :

• Une diminution des ventes sur les anciens modèles contribuant pour -1Mds EUR à une diminution du C.A.
• Le rappatriement d’une partie des véhicules d’un modèle comportant un défaut de construction, qui correspond à une charge de -0.2Mds EUR
• Le paiement d’une amende pour non-respect des conditions de travail dans une usine, pour un montant de -0.2Mds EUR
• Le lancement d’un nouveau modèle, contribuant à +0.8Mds d’augmentation des ventes

TOTAL : +1 – 0.2 – 0.2 + 0.8 = -0.6

Une fois incorporés au graphique, le résultat devrait ressembler à ceci :

Voyons comment construire ceci sur Excel :

Si ce post vous a plu, je vous propose de voir comment déterminer l’effet prix et l’effet volume ici

 

Calcul de l’effet prix et de l’effet volume

Bonjour à tous !

Aujourd’hui, nous allons voir un grand classique du contrôle de gestion : l’analyse de l’effet prix et de l’effet volume.

Imaginons l’exemple d’une entreprise dont le contrôle de gestion souhaite analyser la contribution d’un produit au chiffre d’affaires entre deux périodes. Dans cet exemple, le prix augmente ainsi que les volumes, et le but sera d’expliquer pour combien l’augmentation du C.A. est due à la hausse des prix, et pour combien elle est due à la hausse des volumes.

Dans notre exemple, le contrôle de gestion effectue son analyse entre 2014 et 2015 :

2014
Prix de vente : 12.50 EUR
Quantité vendue : 40 000 unités

2015
Prix de vente : 14.00 EUR
Quantité vendue : 50 000 unités

C.A. 2014 : 12.50 * 40 000 = 500 000
C.A. 2015 : 14.00 * 50 000 = 700 000

Nous avons un chiffre d’affaires en hausse, de 700 000 – 500 000 = 200 000 EUR

Les prix ayant augmenté, ainsi que les volumes de vente, il est difficile de dire quel a été le plus important contributeur des deux à la hausse totale du chiffre d’affaires, il faut donc procéder à une décomposition.
Voyons maintenant la mécanique, et en bonus un moyen mnémotechnique simple pour se rappeler de la formule. Soit Q1 et P1 respectivement les quantité et prix en période 1 (ici, en 2014), et Q2 et P2 respectivement les quantité et prix en période 2 (ici, en 2015)
Effet Prix : on calcule la variation de prix en valeur que l’on mutliplie par la quantité initiale (on fait varier le prix, et on fige la quantité)

EP = (P2 – P1) * Q1
Effet Volume : on calcule la variation de volume en nombre d’unités que l’on mutliplie par le prix initial (on fait varier la quantité, et on fige le prix)

EV = (Q2 – Q1) * P1
Effet croisé : c’est la limite de cette analyse, il y a, avec cette méthode, toujours une partie résiduelle de la variation qui peut être expliquée à la fois par la variation de prix et la variation de quantité. On peut alors réinclure ce montant dans l’effet prix ou volume, le partager entre les deux au prorata, ou l’analyser tel quel à part.

EC = (Q2 – Q1) * (P2 – P1)

Après calcul, nous avons donc une hausse totale du chiffre d’affaires de 200 000 EUR expliquée pour 60 000 EUR par la hausse des prix, et pour 125 000 par la hausse des volumes. 15 000 EUR d’effet croisé sont imputables à la fois aux prix et aux volumes. Le total des trois est bien équivalent à la variation totale : (125 + 60 + 15 = 200). Les volumes de vente ont donc soutenu la croissance du chiffre d’affaires de ce produit, davantage que la hausse des prix.

Bonus : Astuce Mnémotechnique

Calculer un Taux de Rendement Interne (TRI) avec Excel et la valeur cible

Bonjour,

Aujourd’hui nous allons voir comment calculer un Taux de Rendement Interne (TRI) avec un tableur. Il n’existe pas de formule directe et universelle (nous y reviendrons dans un autre post) pour calculer un TRI, aussi l’utilisation d’une calculatrice financière ou d’un tableur reste la méthode la plus simple pour le calculer.

Petit rappel, le TRI est le taux actuariel pour lequel la valeur actuelle nette d’une série de flux est nulle. Nous prendrons pour les besoins de ce post une série de flux avec un TRI existant.

Considérons donc un projet sur cinq ans, qui va nécessiter une sortie de cash de -250k EUR, puis va générer de manière régulière 80k EUR les années suivantes L’échéancier non actualisé ressemble donc à ceci :

Insérons maintenant une ligne supplémentaire en-dessous, en actualisant les mêmes flux, cette fois avec un taux d’actualisation de 2%, par exemple :

Avec le détail de la formule

Nous obtenons bien une valeur actuelle nette de 127 (127.08 précisément), qui ne nous intéresse pas spécialement mais prouve que le fichier marche:

Cette étape ne sert qu’à construire le calcul dans Excel, nous allons immédiatement remplacer la cellule en jaune avec le taux, par le TRI, et pour ce faire, on accède à la fonction valeur cible par le menu « données », dans le bandeau Excel :

L’idée est d’utiliser une fonction très pratique de résolution d’équation d’excel : la valeur-cible. Il faut maintenant paramétrer la valeur recherchée dans la petite boîte de dialogue. En l’occurrence, le TRI annule la VAN, donc on indique à Excel que l’on veut que la VAN, en cellule C6 soit égale à la valeur 0, et la cellule à faire varier pour obtenir ce résultat est la cellule de mon taux d’actualisation, en F9 :

Nous obtenons bien un taux d’actualisation, pour lequel la somme des flux actualisés est nulle : nous venons de trouver le TRI de ce projet. Nous pouvons l’interpréter comme le taux d’actualisation maximum pour lequel le projet sera encore rentable.

Vous pouvez utiliser la valeur-cible pour résoudre de nombreux problèmes, dès lors que la solution de l’équation est unique. Excel utilisant une méthode de résolution par itérations, il s’arrêtera sur le premier résultat trouvé, aussi s’il y en a plusieurs ce ne sera pas la meilleure méthode, il faudra passer par une autre méthode de calcul.

Le fichier à télécharger : exemple_TRI

A un prochain post !