Suivi des coûts de production Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Suivre l'évolution des coûts de production

Dans cette mise en pratique Excel, nous proposons d'exploiter les fonctions Excel de base. L'objectif est de bâtir un tableau de bord dynamique du suivi des coûts unitaires de production pour une entreprise. Cette évolution dont les calculs seront automatisés, devra permettre de faire ressortir des points remarquables sur l'évolutiondes coûts. Les dirigeants décideront alors s'il est nécessaire de relancer des appels d'offre, ou d'adapter la politique de l'entreprise pour maîtriser le budget et préserver les marges.


L'intérêt est de montrer qu'avec les notions essentielles d'Excel, n'importe quelle application peut être montée et adaptée aux besoins d'une société.

Tableau de suivi des coûts
Pour ce travail, nous récupérons un tableau déjà mis en forme et mise en page avec des techniques avancées.

• Télécharger le classeur Excel suivi-couts-productions.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

Pour les produits énumérés en colonne A et décomposés en catégories de charges, les colonnes suivantes (B à G) indiquent l'évolution des coûts unitaires, pour la période s'étalant de 2010 à 2015. Trois calculs principaux destinés à améliorer la compréhension et la lecture des données, sont proposés dans ce tableau.

En colonne H, il s'agit tout d'abord de calculer l'évolution des coûts de production entre l'année de départ (2010) et l'année de fin (2015). En colonne I ensuite, nous devons faire ressortir pour chaque charge, le montant maximum repéré au cours de la période, afin d'attirer l'oeil sur d'éventuelles charges à maîtriser rapidement. En colonne J enfin, il s'agit de dresser une conclusion conditionnelle selon l'évolution enregistrée, afin de permettre une lecture et une interprétation rapides de ce suivi des coûts. Ainsi toute personne non initiée pourra interpréter rapidement les données du suivi des charges et en tirer les conclusions nécessaires.

Calcul de l'évolution des coûts de production
Le calcul d'une évolution consiste à diviser par la valeur de début, la différence entre la valeur de fin et la valeur de départ. En d'autres termes, le calcul est le suivant :(Valeur_fin - Valeur_debut) / Valeur_debut. Le calcul de la différence doit être factorisé (entre parenthèses) car en mathématiques, la division est prioritaire sur la soustraction.

Dans les techniques professionnelles de calculs, nous avions appris à présélectionner la plage concernée avant de débuter la formule, afin de répliquer plus facilement les résultats. Mais la présentation spécifique de ce tableau de suivi, inclut des cellules vides qui ne doivent pas être intégrées. Nous choisissons donc de réaliser le calcul sur les premières et de le répliquer ensuite par Copier-Coller. Cette technique permettra de déplacer les références des cellules du calcul, comme si nous le faisions avec la poignée.

• Sélectionner les premières cellules du calcul, soit la plage H5:H7,
• Taper le symbole = du pavé numérique pour débuter le calcul,
• Ouvrir une parenthèse pour factoriser le calcul,
• Sélectionner la valeur finale de la première charge, soit la cellule G5,
• Taper le symbole Moins (-) du pavé numérique,
• Sélectionner la valeur initiale de la première charge, soit la cellule B5,
• Fermer la parenthèse de la factorisation,
• Taper le Slash (/) du pavé numérique pour la division,
• Sélectionner de nouveau la valeur initiale pour la première charge, soit la cellule B5,
• Valider le calcul par la combinaison CTRL + Entrée pour le répliquer sur toute la plage,

Les trois premières évolutions s'affichent instantanément pour les coûts unitaires de production de l'écran tactile. Il ressort que seules les charges dues à la main d'oeuvre semblent avoir été maîtrisées. Elles ont même baissé notablement, sans doute grâce à la robotisation. Les autres charges ont en revanche enregistré une progression importante, qu'il s'agira sans doute de surveiller. Il s'agit maintenant de répliquer ce calcul pour les autres produits.

• Sélectionner le premier résultat du calcul, soit la cellule H5,
• Copier l'intégralité de la cellule (Format - Contenu - Formule) par le raccourci clavier CTRL + C,
• Sélectionner les trois premières cellules de destination, soit la plage H9:H11,
• Réaliser le raccourci CTRL + V pour répliquer la cellule (Format - Contenu - Formules adaptées),
• Sélectionner le groupe des trois cellules suivantes, soit la plage H13:H15,
• Réaliser de nouveau le raccourci CTRL + V pour répliquer le calcul,

Comme l'illustre la capture ci-dessus, tous les calculs sur l'évolution des coûts sont ainsi reproduits dans les cellules désignées. Il apparaît déjà évident que la majorité des charges a fortement évolué au cours des dernières années. Un axe de réflexion semble donc se dessiner si l'entreprise souhaite restée compétitive sur les produits qu'elle fabrique.

Faire ressortir les charges les plus importantes
Dans la colonne suivante, nous devons afficher un résultat statistique afin de mettre en évidence le montant le plus élevé par charge. Si ce montant correspond systématiquement à la valeur de la dernière année, nous saurons que les coûts n'ont pas été maîtrisés et que les dépenses tendent inéxorablement vers le haut. La fonction Excel qui permet d'extraire le montant le plus élevé d'une série de valeurs, est la fonction Max dont la syntaxe est la suivante : = Max(tableau_de_valeurs).

• Sélectionner les trois premières cellules du coup maximum à calculer, soit la plage I5:I7,
• Taper le symbole = du pavé numérique pour débuter le calcul,
• Sasir le nom de la fonction suivi d'une parenthèse ouvrante, soit Max(,
• Sélectionner les cellules des coûts de production de la prmeière charge, soit la plage B5:G5,
• Fermer la parenthèse de la fonction,
• Valider le calcul par CTRL + Entrée pour le répliquer sur les trois cellules,
• Sélectionner de nouveau la première cellule du calcul, soit I5,
• Réaliser le raccourci CTRL + C pour la copier,
• Sélectionner les trois cellules pour le produit suivant, soit la plage I9:I11,
• Coller la formule précédente par CTRL + V afin de la répliquer,
• Sélectionner les trois cellules pour le dernier produit, soit la plage I13:I15,
• Coller de nouveau le calcul par la combinaison de touches CTRL + V,

Tous les coûts unitaires les plus élevés sont ainsi affichés dans chaque cellule de synthèse pour chacune des charges. Mais sans la connaissance précise des produits et de leurs contraintes et coûts respectifs, il est difficile d'exploiter ces données pour en tirer une interprétation. Comme nous le disions plus haut, il serait intéressant de savoir si ces coûts maximum ont été générés la dernière année, ce qui tenderait à expliquer que l'inflation ou que les appels d'offre n'ont pas été correctement maîtrisés. Pour mettre en évdence ces indicateurs, nous proposons de les faire ressortir de façon dynamique. Cela signifie que la couleur pourrait varier, si de nouvelles données venaient à être saisies dans ce tableau, pour le suivi des coûts de production sur une autre période.

La mise en forme conditionnelle d'Excel, permet de déclencher des mises en valeurs particulières, lorsque des critères sont vérifés. Nous souhaitons faire apparaître le montant max en blanc sur fond noir par exemple, lorsque sa valeur est identique au coût de production de la dernière année. Pour ce faire :

• Sélectionner les trois premières cellules du montant maximum, soit la plage I5:I7,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, pointer sur Règles de mise en surbrillance des cellules,
• Dans la nouvelle liste qui apparaît, cliquer sur la rubrique Egal à,
• Cliquer sur la première cellule du coût de production 2015, soit G5,
• Enfoncer trois fois de suite la touche F4 du clavier jusqu'à ce que les dollars disparaissent,

En effet les dollars qui encadrent les références d'une cellule servent à figer cette dernière. Or ici, pour chaque charge, chaque montant maximum extrait doit être comparé à chacune des charges pour l'année 2015. La cellule doit donc être libre de se déplacer sur les lignes du dessous pour réaliser les comparaisons adéquates. Maintenant que nous avons défini le critère, nous devons paramétrer la mise en valeur qui sera associée lorsque le critère sera vérifié.

• Cliquer sur la flèche de la liste déroulante pour le format associé,
• En bas de la liste, choisir Format personnalisé,
• Dans l'onglet Police de la boîte de dialogue qui suit, choisir une police blanche et grasse,
• Activer ensuite l'onglet Remplissage de cette même boîte de dialogue,
• Choisir une couleur de fond noire et valider la boîte de dialogue par Ok,
• De retour sur la première boîte de dialogue, valider de nouveau par Ok,

Deux cellules sur les trois apparaissent déjà en valeur indiquant que 2/3 des montants maximum pour ce premier produit, correspondent aux valeurs de la dernière année. La mise en valeur visuelle fait donc ressortir ce qui n'était pas forcément évident de comprendre au premier coup d'oeil. La même règle doit être reproduite sur les deux autres plages des deux autres produits. Mais plutôt que de bâtir de nouveau la règle qui consistera à faire ressortir les montants max équivalents aux montants de la dernière année, nous pouvons reproduire ce format dynamique, comme s'il sagissait de n'importe quelle mise en forme.

• Sélectionner la première cellule du format dynamique, soit I5,
• Double cliquer sur le pinceau (Reproduire la mise en forme), du ruban Accueil,
• Sélectionner alors la première des deux plages de cellules, soit I9:I11,
• Sélectionner ensuite la dernière plage de cellules, soit I13:I15,
• Enfoncer la touche Echap pour désactiver l'outil Reproduire la mise en forme,

Comme nous l'apprend la formation sur les trucs et astuces dans Excel, un double clic sur l'outil Reproduire la mise en forme, permet de reproduire en cascade les attributs de format, y compris ceux de la mise en forme conditionnelle. Un simple clic sur cet outil ne permet de reproduire les attributs qu'une seule fois.

Comme l'illustre la capture ci-dessous, le format dynamique fait ressortir instantanément une proportion importante de valeurs seuilles. Il tend à confirmer que les coûts de production ne sont pas maîtrisés puisque majoritairement, les montants les plus importants sont ceux de la dernière année. De plus, souvenez vous que la mise en valeur ainsi appliquée variera si les montants des coûts de production évoluent.


Analyse de l'évolution des coûts de production
L'entreprise estime qu'une information est à remonter si l'évolution de ses coûts de fabrication entre 2010 et 2015 excède les 15%, indiqués en cellule B16. Cette cellule est volontairement utilisée comme référence pour que les calculs soient dynamiques et que les contraintes puissent être modifiées à tout moment. Pour que cette information, destinée à simplifier l'interprétation des données, puisse être inscrite selon le cas en colonne J, nous devons exploiter la fonction Excel conditionnelle SI. Sa syntaxe est la suivante :=SI(Critere_a_verifier ; Quoi_faire_Alors ; Quoi_faire_Sinon). Le critère consiste à comparer le calcul de l'évolution des coûts au seuil de référence inscrit en B16. S'il est vérifié (Quoi_faire_Alors), nous devons inscrire une information textuelle sans équivoque dans la cellule correspondante, afin de livrer les conclusions sur l'analyse de l'évolution des coûts de production. Dans le cas contraire (Quoi_faire_Sinon), nous choisissons de laisser la cellule vide.

• Sélectionner les trois premières cellules du calcul, soit la plage J5:J7,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse ouvrante, soit SI(,
• Sélectionner la première évolution des coûts à comparer, soit la cellule H5,
• Enfoncer le symbole supérieur (>) suivi du symbole égal (=) pour traduire la comparaison Supérieur ou égale,
• Sélectionner la cellule de référence, soit B16,
• Enfoncer la touche F4 du clavier pour la figer,

En effet, lorsque nous répliquerons le calcul sur les cellules du dessous, chacune des évolutions devra être comparée à cette seule et même cellule de référence. Elle ne doit donc pas se déplacer avec le calcul. C'est la raison pour laquelle nous la figeons afin de la transformer en référence absolue.

• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,
• Saisir le texte Augmentation trop importante entre guillemets,
• Taper un nouveau point-virgule pour passer à l'argument Sinon de la fonction Si,
• Ouvrir et fermer les guillemets pour laisser la cellule vide lorsque le critère n'est pas vérifié,
• Fermer la parenthèse de la fonction SI pour terminer la formule,
• Valider la calcul par CTRL + Entrée pour le répliquer sur les cellules du dessous,

Les interprétations des coûts de production fournis par le raisonnement de la fonction Si sont livrés dans les trois premières cellules. Pour le premier produit, deux charges sur trois sont déjà jugées comme excessives. L'examinateur non initié pourrait donc se contenter de river les yeux sur les conclusions fournies par le calcul de cette dernière colonne, pour tirer les conclusions adaptées aux contraintes de l'entreprise.

Nous notons certains défauts de mise en forme. D'une part la colonne n'est pas suffisamment large pour accueillir son contenu et d'autre part, les alignements ne sont pas homogènes.

• Double cliquer à l'intersection des étiquettes de colonnes J et K en haut de la feuille,

Cette action a pour effet d'ajuster la largeur de la colonne J à son contenu le plus large.

• Sélectionner ensuite la plage de cellules J5:J7,
• Cliquer sur le bouton Centrer de la section Alignement du ruban Accueil,

Il ne reste plus qu'à répliquer la logique du calcul sur les deux autres plages de cellules de la colonne J.

• Sélectionner la cellule J5,
• Réaliser le raccourci CTRL + C pour copier l'intégralité de la cellule,
• Sélectionner la plage de cellules J9:J11,
• Réaliser le raccourci CTRL + V pour répliquer la logique de calcul,
• Dans la foulée, sélectionner la plage de cellules J13:J15,
• Réaliser de nouveau le raccourci CTRL + V pour reproduire les résultats de la formule,

Le raisonnement est en effet répliqué sur chacune des cellules, en adaptant les références du calcul, pour livrer les interprétations correspondantes.


La densité des remarques livrées par l'analyse conditionnelle de la fonction SI, est relativement importante. Ce résultat semble indiquer que l'entreprise doit prendre des mesures si elle souhaite corriger le tir et rester compétitive.

Tous les calculs que nous avons bâtis font référence à des variables, soit à d'autres cellules de la feuille de calcul. En conséquence, ce modèle est portable et adaptable à toute entreprise. Chaque modification de donnée, conduira à une mise à jour des calculs, de l'analyse de la fonction SI et des mises en valeur du format dynamique. Si vous modifiez par exemple le seuil de tolérance de la cellule B16 en saisissant 20% à la place de 15%, vous remarquez que les conclusions livrées par l'analyse, sont toutes autres. Dans ce contexte en effet, l'entreprise semble ne pas avoir d'action majeur à envisager.


Pour parfaire l'aspect visuel dynamique de notre modèle, nous pourrions ajouter un format dynamique sur les calculs de l'évolution des coûts de production, en colonne H. Il s'agirait de faire ressortir en blanc sur fond noir, toutes les variations supérieures ou égales au seuil indiqué en cellule B16. Nous renforcerions ainsi la pertinence de l'analyse et la simplicité de lecture du tableau. Pour ce faire :

• Sélectionner la première plage de cellules, soit H5:H7,
• En maintenant la touche CTRL enfoncée, sélectionner les plages H9:H11 et H13:H15,

Cette technique permet d'englober dans une même sélection, des plages de cellules non contigües. Elles sont en effet toutes concernées par la même règle de mise en forme conditionnelle.

• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, pointer sur Règles de mise en surbrillance des cellules,
• Dans la nouvelle liste qui apparaît, cliquer sur Autres règles,
• Dans la boîte de dialogue qui suit, choisir l'opérateur Supérieure ou égale à,
• Cliquer dans la zone de saisie située sur sa droite pour l'activer,
• Puis, cliquer sur la cellule B16 de référence,

Cette fois nous conservons les dollars qui encadrent les références de la cellule. En effet, chaque évolution de la sélection doit être comparée à cette même valeur, dont les références ne doivent pas se déplacer. Maintenant que nous avons défini le critère (La valeur de la cellule est-elle supérieure ou égale à la valeur de référence inscrite en B16 ?), nous devons paramétrer la mise en valeur dynamique associée.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans l'onglet Police de la nouvelle boîte, choisir un style gras et une couleur blanche,
• Puis, activer l'onglet Remplissage de cette même boîte de dialogue,
• Choisir une couleur de fond noire et valider par Ok,
• De retour sur la précédente boîte, valider le format dynamique en cliquer sur Ok,

Visuellement, les résultats rendus par l'analyse du modèle que nous avons construit, sont encore plus pertinents et efficaces. Une fois encore, tous ces réglages sont parfaitement dynamiques et automatisés. Si vous modifiez de nouveau le seuil de tolérance à 20%, vous constatez que la majorité des couleurs noires d'alerte disparaissent. Le tableau de bord de suivi des coûts de production est donc parfaitement adaptable.