Excel pour contrôleurs de gestion : Comment optimiser la production d'une entreprise avec Excel ?

Dans ce tutoriel, je vais vous montrer comment résoudre des équations numériques sur Excel de manière simple et efficace.

En effet, dans le monde professionnel, la prise de décision se base souvent sur des calculs complexes qui peuvent être synthétisés par équations mathématiques à une ou plusieurs inconnues qu'Excel peut résoudre relativement facilement.

Pour cela, nous allons découvrir plusieurs méthodes, comme la méthode de « La différence gauche-droite », qui est une méthode de résolution manuelle des équations à une inconnue. Ensuite, nous verrons comment utiliser les outils « Valeur Cible » et « Solveur » d'Excel pour résoudre des équations de plus en plus complexes.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons partir d’un exemple assez simple.

Une entreprise cherche à déterminer le nombre d'unités de production nécessaires pour atteindre un seuil de rentabilité.

Les coûts fixes de l'entreprise sont de 10 000 €, s’ajoutent les coûts variables unitaires de 5 €.

Le prix de vente unitaire est de 10 €.

Pour déterminer le nombre d'unités de production nécessaires pour atteindre le seuil de rentabilité, nous devons donc résoudre l'équation suivante :

 10 000 € + 5 € * X = 10 € * X 

où X est le nombre d'unités de production.

2. La méthode de la « Différence Gauche-Droite »

Pour déterminer à partir de combien d'unités vendues l'entreprise commencera à générer de la marge brute, nous allons tout d'abord découvrir la méthode de la « Différence Gauche-Droite ».

Il s'agit d'une méthode de résolution manuelle d'une équation à une inconnue qui consiste à placer les termes de l'équation du côté gauche et les constantes du côté droit.

Ensuite, on procède à une série de manipulations algébriques pour isoler l'inconnue.

Cette méthode peut être très utile pour résoudre des équations simples et est particulièrement adaptée à une résolution manuelle lorsqu'on ne dispose pas d'un logiciel de calcul ou lorsque l'on souhaite rapidement obtenir une estimation de la solution.

En utilisant cette méthode, nous pourrons déterminer le seuil de rentabilité de l'entreprise en calculant le point d'équilibre entre les coûts et les recettes.

Pour cela, nous allons mettre en place un tableau de quatre colonnes :

• X : corresponds ici au nombre d’unités vendues
• GAUCHE : correspond ici au coût total (coût fixe + coût variable)
• DROITE : correspond ici au revenu total
• DIFFÉRENCE : correspond ici au montant du profit, c’est-à-dire de la différence entre le revenu total et le coût total

Ensuite, sur la première ligne, nous allons pouvoir saisir les informations suivantes :

• X : pour commencer, nous pouvons saisir « 1 »
• GAUCHE : nous saisissons la formule : =$B$6+$B$7*A13
• DROITE : nous saisissons la formule : = A13*$B$8
• DIFFÉRENCE : nous saisissons la formule : =C13-B13

Ainsi, si l’entreprise n’est en mesure de produite qu’une seule unité, le déficit sera alors de -9995€.

Pour déterminer la valeur X représentant le seuil de rentabilité, nous allons devoir continuer l’analyse.

Pour cela, nous effectuons un copier-coller de cette ligne :

Puis nous modifions la formule de la colonne X pour que celle-ci soit l’incrémentation de la valeur précédente :

 =A13+1 

Ensuite, nous allons étendre cette formule sur une grande plage de cellule, en procédant de la manière suivante :

• Nous sélectionnons puis copions cette deuxième ligne,
• Ensuite, nous dans la zone des noms, nous saisissions les coordonnées de la cellule A10000 pour l’activer rapidement
• Ensuite, nous appuyons sur les touches [Ctrl]+[Maj]+[flèche du haut] pour sélectionner toute la place des cellules A14 jusqu’à A10000 :

• Il ne reste plus qu’à appuyer sur [Ctrl]+[V] pour dupliquer les calculs :

Maintenant, nous allons pouvoir dérouler la liste jusqu’à obtenir la valeur de X qui permet d’obtenir une différence égale à zéro :

Le seuil de rentabilité sera donc atteint lorsque l’entreprise aura produit 2000 unités.

Pour retrouver rapidement la ligne correspondante, nous pouvons utiliser l’outil de recherche d’Excel en appuyant sur les touches [Ctrl]+[F] :

Nous saisissons « 0 » dans le champ « Rechercher », puis nous déroulons le menu des options avancées en appuyant sur le bouton « [Options >>] ».

Ensuite, nous sélectionnons « Valeurs » dans la liste « Regarder dans », et nous cochons l’option « totalité de contenu de la cellule ».

Il ne reste plus qu’à appuyer sur le bouton « Suivant » pour qu’Excel sélectionne automatiquement la cellule correspondante.

Nous pouvons également retrouver les informations en utilisant des formules.

Ainsi pour retrouver le nombre d’unités à produire pour atteindre le seuil de rentabilité, nous pouvons utiliser la fonction INDEX-EQUIV.

En effet, la fonction INDEX-EQUIV est particulièrement adaptée dans ce cas-ci, car elle permet de renvoyer la valeur d'une cellule dans un tableau en fonction d'une condition spécifique.

Dans notre cas, nous souhaitons retrouver le nombre d'unités de production nécessaires pour atteindre le seuil de rentabilité, c'est-à-dire la première cellule de notre tableau où la colonne DIFFÉRENCE est égale à zéro :

 =INDEX(A13:A10000;EQUIV(0;D13:D10000;0)) 

3. L’outil « Valeur Cible »

Une autre option permettant d’obtenir rapidement le nombre d’unités à produire pour atteindre le seuil de rentabilité est d’utiliser l’outil « Valeur Cible ».

   3.1. Qu'est-ce que la Valeur Cible ?

L'outil « Valeur Cible » est une fonctionnalité d'Excel qui permet de résoudre numériquement une équation en modifiant une variable pour atteindre un objectif spécifique. En d'autres termes, il s'agit de trouver la valeur d'une cellule qui permet d'obtenir un résultat spécifique dans une autre cellule.

   3.2. Comment utiliser la Valeur Cible ?

Pour utiliser l'outil « Valeur Cible », suivez les étapes suivantes :

• Cliquez sur l'onglet « Données » dans le ruban d'Excel.
• Dans le groupe « Prévisions », cliquez sur l'option « Valeur Cible «.

Dans la boîte de dialogue « Valeur Cible «, spécifiez les champs suivants :

• « Cellule à définir » : ce champ correspond à la cellule dont la valeur doit être modifiée pour atteindre l'objectif. Dans notre cas, il s'agit de la cellule contenant le nombre d'unités de production (cellule E6).
• « Valeur à atteindre » : ce champ correspond à l'objectif à atteindre. Dans notre cas, il s'agit de la valeur zéro, qui correspond au point d'équilibre entre les coûts et les recettes (cellule E9).
• « Cellule à modifier » : ce champ correspond à la cellule qui doit être modifiée pour atteindre l'objectif. Dans notre cas, il s'agit de la cellule contenant le profit (cellule E8).

Puis nous validons en appuyant sur « OK » pour exécuter la recherche de Valeur Cible.

Excel va maintenant modifier la valeur de la cellule contenant le nombre d'unités de production jusqu'à ce que le profit soit égal à 0.

   3.3. Résultats de la fonction « Valeur Cible »

Après quelques instants, Excel affichera une boîte de dialogue indiquant que la Valeur Cible a été trouvée. Nous cliquons sur « OK » pour fermer la boîte de dialogue.

Dans notre exemple, Excel a trouvé que le nombre d'unités de production nécessaire pour atteindre le seuil de rentabilité est de 2000 unités.

La cellule E6 a été mise à jour avec cette valeur, et les cellules E7, E8 et E9 ont été recalculées en conséquence.

Nous avons donc utilisé la fonction « Valeur Cible » pour résoudre numériquement une équation et trouver le nombre d'unités de production nécessaire pour atteindre le seuil de rentabilité.

En utilisant cette technique, nous pouvons résoudre de nombreuses autres équations dans Excel, que ce soit pour des problèmes financiers, comptables, scientifiques ou autres.

4. Résoudre des équations à deux inconnues en utilisant Excel

Dans les exemples précédents, nous avons utilisé Excel pour résoudre des équations simples, avec une inconnue unique nommée X.

Mais Excel peut également être utilisé pour résoudre des équations à deux inconnues.

Pour ce faire, nous allons utiliser la fonctionnalité Solver d'Excel.

Pour illustrer le fonctionnement du « Solveur », nous allons partir de l’exemple suivant :

• Une entreprise dispose de deux machines, A et B, qui peuvent être utilisés pour produire deux produits différents, X et Y.
• La machine A nécessite 2 heures pour produire une unité de X et 2 heures pour produire une unité de Y.
• La machine B nécessite 3 heures pour produire une unité de X et 1 heure pour produire une unité de Y.
• Le nombre total d’heures machine disponible est de 1200
• Nous disposons de 600 heures de temps de production sur la machine A et de 800 heures de temps de production sur la machine B.
• Les bénéfices par unité pour les produits X et Y sont de 5 € et 4 €, respectivement.

Ces données sont synthétisées dans les cellules :

Ici, l’objectif est donc de déterminer le nombre d'unités de X et Y qui doivent être produites pour maximiser le bénéfice total, en supposant que toute unité produite sera vendue.

Pour résoudre ce problème, nous allons utiliser le « Solveur » d'Excel.

Celui-ci va nous présenter les nombres d’heures optimales à saisir dans les cellules dédiées pour optimiser l’utilisation des machines.

Tout d’abord, nous allons devoir activer le « Solveur »

Pour cela, suivez les étapes suivantes :

• Cliquez sur l'onglet « Fichier » dans le ruban Excel
• Cliquez sur « Options"
• Dans la fenêtre « Options Excel », cliquez sur « Compléments » dans le menu de gauche
• Dans le champ « Gérer », nous sélectionnons « Compléments Excel » puis cliquez sur le bouton « Atteindre"
• Cochez la case « Solveur » Ajouter-ins » puis cliquez sur « OK"
• Cliquez sur « OK » dans la fenêtre « Options Excel » pour valider l'installation du « Solveur »

Une fois que le « Solveur » est installé, nous pourrons l’utiliser en suivant les étapes suivantes :

• Cliquez sur l'onglet « Données » dans le ruban Excel
• Cliquez sur le bouton « Analyse de scénarios"
• Dans le menu déroulant, nous sélectionnons « Solveur »"

Le paramétrage du « Solveur » s’effectue en trois temps.

Tout d’abord, nous allons devoir sélectionner la cellule que nous allons souhaiter optimiser, il la cellule F20 dans laquelle se trouve le montant du bénéfice réalisé par la vente de tous les produits vendus :

Nous spécifions également que cette valeur doit être définie à sa valeur maximale.

Ensuite, dans le champ « Cellules variables », nous venons sélectionner les dans lesquelles le « Solveur » va calculer les durées optimales de production de chaque machine, pour chaque produit. Il s’agit des cellules B18 à C19.

Puis, pour finir, nous allons pouvoir renseigner toutes les contraintes à appliquer en fonction du problème présenté :

Ici, nous avons bien trois contraintes :

• La machine A doit être utilisée au maximum 600 heures
• La machine B doit être utilisée au maximum 800 heures
• Les deux machines ne peuvent pas être utilisées plus de 1200 heures

Une fois que nous avons terminé de renseigner les contraintes à mettre en place, nous pouvons valider en appuyant sur le bouton « Résoudre ».

Après avoir appuyé sur le bouton [Résoudre], le « Solveur » d'Excel analyse notre problématique et présente le résultat de son optimisation : pour maximiser la marge bénéficiaire, l'entreprise doit utiliser 400 heures de machines A pour produire des produits X et 8 heures de machine B pour produire des produits Y, générant ainsi une marge de 4200€ de bénéfice.

Nous avons plusieurs options à ce stade :

• Valider la modification proposée en sélectionnant l’option « Conserver la solution du « Solveur «,
• Rétablir les données d’origine en sélectionnant l’option « Rétablir les valeurs d’origine »,
• Revenir sur la fenêtre de paramétrage du « Solveur » pour le modifier, en cochant l’option « Retourner dans la boite de dialogue Paramètres du « Solveur «,
• Afficher un « Rapport du plan » après validation (dans ce cas, il faut sélectionner le rapport dans la fenêtre au-dessus),
• Enregistrer le scénario pour pouvoir l’appeler par la suite depuis la fenêtre.

Dans notre exemple, nous choisissons de conserver la solution et d'afficher le rapport de plan. Nous appuyons sur la touche [OK] pour continuer.

Le rapport de plan affiche le détail des calculs dans un nouvel onglet « Rapport de plan », qui contient toutes les informations relatives à la solution optimale trouvée par le « Solveur ». Le rapport contient notamment :

• La cellule « objectif », qui est la cellule contenant la formule à optimiser (ici, la marge bénéficiaire totale),
• Les cellules variables, qui sont les cellules contenant les valeurs à ajuster pour trouver la solution optimale (ici, les quantités de production pour chaque article),
• Les contraintes, qui sont les limites à respecter pour chaque variable (ici, les temps de production et les quantités minimales à produire),
• Les résultats de l'optimisation, qui sont les valeurs optimales des variables et de la cellule objectif.