Calcul des résultats d'une entreprise

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


Sujets que vous pourriez aussi aimer :


Suivi des résultats d'une entreprise

Dans ce troisième exercice Excel, niveau débutant, nous poursuivons l'entraînement sur la mise en oeuvre des calculs simples. Les opérations proposées demeurent relativement triviales en effet. Mais elles n'en sont pas moins efficaces et surtout dynamiques, comme l'impose la philosophie Excel. Une fois les calculs bâtis, le modèle peut être déployé pour exploitation.

Source et présentation de l'objectif


La capture ci-dessus illustre le tableau à partir duquel nous allons devoir résoudre les calculs. Pour bien débuter, nous proposons donc de le télécharger.

• Télécharger le classeur calculs-des-benefices.xlsx en cliquant sur son lien,
• Double cliquer sur le nom du fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Ce classeur est constitué d'une seule feuille. Elle est nommée Bénéfices, comme en atteste son unique onglet, en bas à gauche de l'interface Excel.



Le tableau de cette feuille synthétise les ventes réalisées par une entreprise au cours d'une période indéterminée. L'objectif final est d'obtenir le bénéfice total dégagé. Mais des calculs intermédiaires sont nécessaires.

En colonne B, entre les lignes 5 et 11, sont énumérées les désignations des articles ayant été vendus. A ce titre, nous remarquons un petit défaut de mise en forme. Plus précisément, il s'agit d'une anomalie d'alignement. Contrairement au reste des informations de ce tableau, ces désignations ne sont pas centrées dans la hauteur de leur cellule. Il s'agira donc de corriger ce problème, en même temps que nous peaufinerons la présentation, à l'issue des calculs.



Les colonnes C et D, pour les mêmes lignes, énumèrent respectivement le prix d'achat et le prix de vente de chacun. De fait, nous devons en déduire, par calcul dynamique, la marge unitaire opérée en colonne E. Rappelez-vous, un calcul dynamique signifie qu'aucune constante ne doit être utilisée. Pour le premier article par exemple, il ne s'agira pas de réaliser l'opération : 98-62. Le calcul devra être construit sur les références des cellules, donc des variables. Ainsi, si les prix évoluent, la marge unitaire réalisée s'actualisera automatiquement. Dès lors, nous bâtirons un modèle exploitable pour d'autres périodes ou sociétés.



La colonne F recense les quantités qui ont été vendues respectivement pour ces mêmes articles. La multiplication de cette quantité par la marge unitaire calculée en amont, conduira donc au bénéfice réalisé pour chacun. Nous n'aurons plus qu'à les additionner pour produire le résultat final du bénéfice total.

Bien sûr au cours de cet exercice, nous aurons à coeur d'employer les bonnes méthodes et d'insister sur les techniques professionnelles de calculs avec Excel.

Calculer la marge unitaire
L'opération est très simple. Elle consiste à déduire le prix d'achat du prix de vente. Il s'agit donc d'une soustraction. Mais cette soustraction doit se faire sur les références des cellules pour produire un résultat dynamique. Une formule doit être construite à l'endroit où le résultat est attendu. Rappelez-vous, dans la syntaxe, lorsque nous construisons le calcul manuellement, nous devons débuter la saisie par le symbole égale (=). C'est ainsi que nous signifions à Excel d'interpréter les données et non de se contenter de les restituer.



La première formule doit être établie en cellule E5, pour la première marge unitaire. Rappelez-vous de la présence de la zone Nom, en haut à gauche de la feuille, encerclée de rouge sur cette capture. Elle rappelle sans ambiguïté la référence de la cellule sélectionnée. Sur sa droite, la longue zone de saisie correspond à la barre de formule. Elle restitue le contenu exact d'une cellule sélectionnée. Nous y reviendrons.

• Cliquer dans la première marge unitaire à calculer, soit la cellule E5, pour la sélectionner,
• Taper le symbole égal (=) pour débuter l'opération,
• Avec la souris, cliquer sur le premier prix de vente,

Ainsi, la référence de la cellule D5 s'ajoute dans la syntaxe du calcul.

• Taper le symbole moins (-) du pavé numérique pour enclencher la soustraction,
• Cliquer sur le premier prix d'achat pour l'inclure dans l'opération,

La référence de la cellule C5 s'intègre ainsi dans la syntaxe de la formule.

• Valider le calcul par le raccourci clavier CTRL + Entrée,

Cette technique est importante et intéressante. Elle permet de valider un calcul tout en conservant la cellule active. L'objectif est de pouvoir agir sur cette dernière dans la foulée, sans devoir la resélectionner. Bien sûr, nous aurions pu valider seulement avec la touche Entrée. Mais dans ce cas, nous aurions activé la cellule du dessous.



Nous obtenons le premier résultat tout à fait cohérent, en considérant le prix d'achat et le prix de vente. La barre de formule, encerclée de rouge sur la capture, rappelle la syntaxe exacte du calcul posé. Dans le même temps, la cellule affiche son résultat. Comme nous l'avons dit à maintes reprises, on ne doit jamais refaire deux fois le même calcul dans Excel. Nous devons le répliquer. Et ce processus est rendu possible grâce aux références des cellules incluses dans la formule. L'opération est réalisée sur la ligne 5. En demandant à Excel de le reproduire sur les lignes du dessous, il adaptera l'indice de ligne des cellules impliquées. C'est ce que l'on appelle les références relatives. Et c'est la poignée d'une cellule qui permet de répliquer sa formule en la tirant dans un sens bien précis. Souvenez-vous, cette poignée est matérialisée par un petit carré noir situé en bas à droite de la cellule active. Elle est encerclée de rouge sur la capture ci-dessus. Lorsque vous pointez dessus, le curseur de la souris se transforme en une croix noire. Jusqu'alors, nous avions appris à tirer cette poignée dans une direction, en cliquant et glissant à l'aide de la souris. Mais il est temps de découvrir une astuce intéressante.

• Placer le curseur de la souris sur la poignée de la cellule E5,
• Puis, double cliquer,

Excel détecte automatiquement les bornes du tableau. De fait, sans tirer la poignée, il réplique la logique du calcul sur les lignes du dessous, jusqu'à la dernière. Cette méthode est particulièrement intéressante pour les tableaux longs, dont le contenu dépasse la hauteur de l'écran.

Tous les résultats sont cohérents et semblent s'être adaptés ligne à ligne.

• Cliquer sur la dernière marge calculée, soit la cellule E11 pour la sélectionner,

A l'origine, pour la première marge unitaire, nous avons bâti le calcul suivant : =D5-C5. En consultant la barre de formule de la cellule E11, vous constatez qu'il s'est transformé en : =D11-C11. La formule a été répliquée 6 lignes plus bas. Donc, par le jeu des références relatives, Excel a automatiquement impliqué dans le calcul, les cellules situées 6 lignes plus bas. Il en va de même pour les autres cellules intermédiaires, selon leur position.

• Sélectionner le prix de vente de ce dernier article, soit la cellule D11 sur sa gauche,
• Saisir le nombre 17 directement à la place de l'ancienne valeur 16,
• Puis, valider par la touche Entrée,

Instantanément, vous constatez que la marge unitaire s'est actualisée, confirmant que le calcul est dynamique. Comme les autres calculs que nous allons bâtir le seront aussi, notamment pour les bénéfices, les mises à jour s'opèreront en cascade. Le fait de modifier un prix d'achat ou un prix de vente, modifiera automatiquement la marge unitaire. Comme le bénéfice sera calculé sur cette dernière, il s'actualisera donc automatiquement en cascade.

Prenez-soin en revanche de ne pas être tenté de modifier la valeur d'une cellule de calcul, comme celles de la colonne E. Vous détruiriez la formule en place. C'est pourquoi, conventionnellement pour un modèle, nous protégeons les cellules des calculs à l'issue de la conception. Nous avions démontré ce procédé dans la formation sur la facturation automatisée de clients.

Calculer les bénéfices réalisés
Nous l'avons évoqué précédemment, le calcul du bénéfice consiste à multiplier la marge unitaire par la quantité vendue. Là encore, les cellules doivent être impliquées dans l'opération par leurs références. Ainsi, nous pourrons reproduire la logique sur la hauteur de la colonne G. Mais cette fois, nous proposons d'employer une méthode professionnelle très efficace et différente de la précédente. Nous l'avons d'ailleurs préconisée lors des deux exercices précédents. Elle consiste à présélectionner toutes les cellules dans lesquelles doivent apparaître les résultats. En l'occurrence ici, il s'agit de la plage G5:G11. Puis elle consiste à construire la formule comme si elle était dédiée uniquement à la première d'entre elles (G5). Mais la méthode de validation par la combinaison CTRL + Entrée, permet de répliquer la logique en adaptant les références dans le sens de déplacement de la plage.

• Sélectionner le premier bénéfice à calculer, soit la cellule G5,
• Puis, tout en maintenant le bouton gauche de la souris enfoncé, glisser jusqu'à G11,

Nous sélectionnons ainsi la plage de cellules G5:G11.



Comme vous le constatez, la première cellule de cette plage n'est pas grisée comme les autres. Pourtant, elle est bien sélectionnée elle aussi. Excel indique qu'il s'agit de la cellule active dans cette plage. De fait, nous allons pouvoir mettre en oeuvre la technique professionnelle énoncée précédemment.

• Taper le symbole égal (=) pour initier la formule,

Comme vous le remarquez, il s'inscrit dans la première cellule (G5) de cette plage sélectionnée. Nous devons poursuivre cette logique en bâtissant le calcul avec les cellules de la même ligne.

• Cliquer sur la première marge unitaire, soit la cellule E5 pour l'inscrire dans la syntaxe,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Cliquer sur la première quantité, soit la cellule F5 pour l'intégrer dans la syntaxe,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Instantanément, tous les bénéfices sont calculés. Cette méthode est encore plus rapide et efficace que la dernière. Nous n'avons pas eu besoin de tirer la poignée. Mais la méthode de la poignée est tout à fait fonctionnelle et aurait conduit au même résultat.

• Sélectionner le dernier bénéfice calculé, soit la cellule G11,

Bien que la méthode utilisée ait été différente, la remarque est la même que précédemment, fort heureusement. La formule que nous avons bâtie à l'origine implique les cellules de la ligne 5 : =E5*F5. En répliquant la logique par le raccourci clavier sur cette plage présélectionnée, les références ont suivi le déplacement pour impliquer les cellules situées 6 lignes plus bas : =E11*F11.

Il nous reste à trouver la somme des bénéfices. Comme nous l'avons appris lors des exercices précédents, lorsque l'addition fait intervenir un grand nombre de valeurs, il est préférable d'exploiter la somme automatique d'Excel. Ici, la cellule du résultat n'est pas collée au reste du tableau. Nous ne pouvons donc pas exploiter la technique consistant à présélectionner toutes les valeurs avec la cellule du résultat. Nous devons décomposer la formule. La somme automatique, comme son nom l'indique, se charge d'inscrire le symbole égal. Nous ne devons donc pas l'ajouter manuellement.

• Sélectionner la cellule du bénéfice total à calculer, soit G13,
• Tout à fait à droite du ruban Accueil, cliquer sur le bouton Somme automatique,

Comme l'illustre la capture ci-dessus, le bouton Somme automatique est matérialisé par une lettre M orientée à 90 degrés. Il s'agit en fait de la lettre grecque Sigma. L'info-bulle nous rappelle que cette somme automatique peut être appelée par le raccourci clavier Alt + =. Excel inscrit automatiquement sa proposition de calcul dans la cellule où elle est appelée. La syntaxe débute par le symbole égal (=) suivi du nom de la fonction. Toute fonction requiert que les éléments à calculer lui soient passés entre ses parenthèses. Et précisément, entre les parenthèses, Excel désigne la plage de cellules G5:G12. Souvenez-vous, les deux points (:) sont importants dans la syntaxe. Ils signifient : De G5 à G12. Mais cette proposition n'est pas tout à fait correcte. C'est pourquoi la formule n'est pas validée, elle est en attente de correction potentielle par l'utilisateur. Et c'est ce que nous allons faire. Excel étend la plage proposée jusqu'à la cellule adjacente à celle du résultat. Or, la cellule G12 est en dehors du tableau et elle est vide. Nous pourrions la laisser mais nous sommes puristes.

• Sélectionner uniquement les bénéfices à additionner, soit la plage G5:G11,
• Puis, valider la formule à l'aide de la touche Entrée,

Le raccourci CTRL + Entrée n'est pas nécessaire ici. En effet, ce calcul ne doit pas être répliqué. Donc, il n'est nul besoin de conserver la cellule active.



Nous obtenons en effet la somme dynamique des bénéfices précédemment calculés. Ce résultat de synthèse est précieux. Il offre une lecture transversale du tableau. Sa simple indication permet de savoir si les résultats sont satisfaisants ou non. Ensuite, le détail peut être étudié au cas par cas pour prendre les mesures qui s'imposent.

• Sélectionner le premier prix d'achat, soit la cellule C5,
• Saisir le nombre 60 à la place du nombre 62 et valider par Entrée,

Comme vous le constatez, nos calculs sont parfaitement bâtis et respectent la philosophie Excel. A validation de la modification, la marge calculée en E5 s'est actualisée pour considérer ce nouveau prix. Comme elle est elle-même impliquée dans le calcul du bénéfice, celui-ci s'est instantanément mis à jour en G5. Et comme il appartient aux variables conduisant au calcul du bénéfice total, il s'est lui-même actualisé en cascade en G13.

Formatage et présentation des données
L'apparence d'un tableau Excel ne doit jamais être négligée. Si les alignements sont homogènes, si la présentation est aérée et si les données numériques sont correctement formatées, la lecture et l'interprétation des informations s'en trouvent grandement clarifiées.

Toutes ces valeurs numériques, hormis la colonne des quantités, doivent être formatées en Euro. Souvenez-vous et c'est fondamental, une unité de mesure ou une devise ne doit jamais être écrite dans une cellule. Si tel est le cas, l'information de cellule est transformée en un texte. Et aucun calcul ne peut se faire sur des textes. Nous devons donc appliquer le format de l'Euro. Un format s'applique aux cellules sélectionnées. Nous proposons de regrouper toutes les plages concernées dans une même sélection, malgré la présence intercalée de la colonne F des quantités. Nous l'avons déjà démontré, c'est la touche CTRL du clavier qui permet d'additionner des sélections.

• En glissant la souris, sélectionner tout d'abord la plage de cellules C5:E11,
• Tout en maintenant la touche CTRL enfoncée, sélectionner la plage des bénéfices G5:G11,
• Toujours avec la touche CTRL enfoncée, cliquer sur la cellule G13 du bénéfice total,

Nous réunissons dans une même sélection toutes les cellules concernées par le formatage en Euro. La dernière cellule d'une multi-sélection n'apparaît pas grisée. C'est ainsi.

• Réaliser maintenant le raccourci clavier CTRL + M,

Instantanément, toutes les valeurs sélectionnées sont formatées en Euros. Outre le symbole de l'Euro, vous constatez l'apparition de décimales. Cette précision est inutile dans notre cas. Nous ne manipulons que des nombres entiers. Elle alourdit la présentation. Nous devons donc les supprimer. Un espace fait office de séparateur de millier pour simplifier la lecture des grandes valeurs.

Cette combinaison de touches CTRL + M, M pour Money, est le raccourci du bouton Format Nombre Comptabilité dans la section Nombre du ruban Accueil. A droite de cette section d'ailleurs, vous notez la présence de deux outils permettant de manipuler les décimales. Le premier ajoute de la précision tandis que le second la réduit.

• Cliquer deux fois de suite sur le bouton Réduire les décimales,

Les décimales disparaissent et l'apparence du tableau s'en trouve allégée. Sachez que ces formatages ne concernent que l'affichage. Si vous réduisez deux fois les décimales d'un nombre comme 15,15 par exemple, vous obtenez la valeur 15. Mais Excel continue de considérer la valeur d'origine, pour ses calculs notamment, soit 15,15.

En début de formation, nous avions évoqué un défaut d'alignement dans la hauteur, pour les désignations de la colonne B.

• Sélectionner la plage de cellules B5:B11,
• Dans la section Alignement du ruban Accueil, cliquer sur le bouton Aligner au centre,

Tous les textes sont désormais parfaitement centrés dans la hauteur de la cellule.