Analyse de la rentabilité des ventes avec Excel

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


Sujets que vous pourriez aussi aimer :


Analyse de rentabilité avec Excel

Cette mise en pratique Excel propose de dresser un modèle dynamique, permettant d'effectuer une analyse de rentabilité automatique des ventes.

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

Contexte: La société Les productions Baumont souhaite étudier la rentabilité mensuelle des spectacles qu'elle programme. Si la rentabilité du spectacle est satisfaisante, alors la société décidera de le prolonger à l'affiche, dans le cas contraire, elle le remplacera.


Cette décision devra être inscrite dans les cellules de la ligne 18 (Spectacle à prolonger). Bien sûr, il s'agit de bâtir un modèle exploitable pour tous les mois de l'année et pour toute entreprise. Cela signifie que tous les calculs doivent se réaliser automatiquement selon des contraintes définies dans un panneau de contrôle. Ce panneau de contrôle est conçu en colonnes H, I et J. Il détermine ici qu'un spectacle est considéré comme rentable à partir du moment où le montant mensuel des ventes réalisées est au moins de 75% des ventes maximales qui auraient pu être faites sur ce programme.

Mais avant d'en arriver là, des calculs intermédiaires sont à réaliser, en commençant tout d'abord par les sommes des quantités.

• Sélectionner la plage de cellules B4:F5,
• Réaliser le raccourci clavier ALT + = ou cliquer sur le bouton Somme automatique,

ALT + = est le raccourci clavier pour réaliser la somme des cellules sélectionnées. Comme les cellules vides des résultats étaient incluses dans la sélection, les résultats des sommes y ont été posés sans étape intermédiaire. Nous allons exploiter la même technique pour calculer la somme des nombres de billets vendus, selon le tarif.

• Sélectionner la plage de cellules B8:F10,
• Réaliser le raccourci clavier ALT + =,

Analyse de la rentabilité des ventes
La partie suivante du tableau propose d'effectuer des calculs incluant des valeurs remarquables, comme le nombre total de places et les ventes maximales qui auraient pu être réalisées. Elles serviront d'éléments de comparaison pour dresser l'analyse de la rentabilité des programmes. Le nombre total de places disponibles consiste à multiplier le nombre de places par le nombre de séances.

• Sélectionner l'ensemble des cellules à calculer, soit la plage B13:E13,
• Taper le symbole = pour débuter le calcul,
• Sélectionner la première cellule pour le nombre de places, soit B4,
• Taper l'étoile du pavé numérique (*) pour la multiplication,
• Sélectionner la première cellule pour le nombre de séances, soit B5,
• Valider le calcul par le raccourci CTRL + Entrée,

Comme toutes les cellules des calculs étaient présélectionnées, le raccourci CTRL +Entrée a permis, en même temps qu'il a validé le calcul, de le reproduire sur l'ensemble de la plage. Les résultats sont illustrés sur l'extrait de la capture ci-dessous.

Le calcul suivant (Total Places NON louées) consiste à inscrire, pour chaque spectacle, le nombre de places qui n'ont pas été vendues. Cette indication sera fort utile pour l'analyse de la rentabilité d'un programme. Ce calcul se déduit du précédent et des quantités de billets vendus, dans le tableau Quantités des ventes. Il s'agit de soustraire la somme des quantités vendues au résultat du total des places disponibles.

• Sélectionner l'ensemble des cellules à calculer, soit la plage B14:E14,
• Taper le symbole = pour débuter le calcul,
• Sélectionner la cellule du total des places, soit B13,
• Taper le symbole moins (-) du pavé numérique pour la soustraction,
• Saisir le nom de la fonction somme suivi d'une parenthèse ouvrante, soit somme(,
• Sélectionner les trois cellules des quantités à additionner, soit la plage B8:B10,
• Fermer la parenthèse de la fonction Somme,
• Valider le calcul par CTRL + Entrée pour le répliquer sur les cellules sélectionnées,

Cette première indication est déjà précieuse. Même si les conclusions ne sont pas encore établies dynamiquement par le calcul, nous pouvons constater que le premier spectacle ne semble pas avoir recueilli les faveurs du public. Avec un total de 2474 places non vendues sur une quantité totale possible de 4840 places, nous en concluons que plus de la moitié des places n'a pas trouvé preneur.

Toujours à titre de comparaison, nous souhaitons ensuite calculer la valeur remarquable du montant maximum des ventes. Ainsi, plus le montant réel des ventes s'en écartera, plus nous en conclurons que la rentabilité baisse. Le montant maximum se calcule en considérant que toutes les places ont été vendues et ce, au tarif plein.

• Sélectionner toutes les cellules des montants à calculer, soit la plage B15:E15,
• Taper le symbole = pour débuter le calcul,
• Sélectionner la cellule du total des places disponibles, soit B13,
• Taper le symbole * du pavé numérique pour la multiplication,
• Sélectionner la cellule du tarif plein, soit J14,
• Puis, enfoncer la touche F4 du clavier pour la figer,
• Enfin, réaliser le raccourci CTRL + Entrée pour valider le calcul et le reproduire sur la plage,

Comme nous avons reproduit ce calcul sur les colonnes de droite, il a été nécessaire de figer une cellule de référence dans le calcul. La touche F4 du clavier a pour effet d'ajouter un dollar devant l'indice de colonne de la cellule ainsi qu'un dollar devant son indice de ligne ($J$14). Sans cette action, en répliquant ce calcul sur la droite, la multiplication aurait fait références à K4 puis L4 et ainsi de suite, au fur et à mesure du déplacement.

Pour que l'analyse de la rentabilité de chaque spectacle puisse être établie, nous avons désormais besoin de calculer le montant réel des ventes, pour chaque spectacle. Nous pourrons ensuite comparer ces valeurs avec les résultats remarquables précédemment calculés. Il s'agit ici de multiplier chaque nombre de places vendues par leur tarif correspondant, puis d'ajouter les valeurs entre elles.

• Sélectionner toutes les cellules des montants à calculer, soit la plage B16:E16,
• Taper le symbole = pour débuter le calcul,
• Sélectionner la cellule du nombre de places au tarif plein, soit B8,
• Taper le symbole * pour la multiplication,
• Sélectionner la cellule du prix au tarif plein, soit J14,
• Pour les mêmes raisons que précédemment, enfoncer la touche F4 afin de la figer,
• Taper le symbole + du pavé numérique pour l'addition,
• Sélectionner la cellule du nombre de places au tarif sénior, soit B9,
• Taper le symbole * de la multiplication,
• Sélectionner la cellule du tarif sénior, soit J15,
• Enfoncer la touche F4 du clavier pour la figer en référence absolue,
• Taper le symbole + de l'addition,
• Sélectionner la cellule du nombre de places au tarif jeune, soit B10,
• Taper le symbole * de la multiplication,
• Cliquer sur la cellule du tarif jeune, soit J16,
• Enfoncer de nouveau la touche F4 afin de fixer cette valeur,
• Valider le calcul par CTRL + Entrée pour le reproduire sur la plage de cellules,

Tous les résultats sont ainsi répliqués sur l'ensemble des cellules présélectionnées grâce au raccourci clavier mais aussi et surtout, grâce aux références absolues (F4) qui ont permis de figer dans le calcul, les cellules du tableau de bord.

Même si la conclusion n'est pas encore écrite noire sur blanc dans la dernière ligne prévue à cet effet, il apparaît évident que le premier spectacle par exemple, n'est pas rentable. L'écart entre le montant des ventes réalisées et le montant maximum qui aurait pu être atteint, est trop important.

• Sélectionner la plage de cellules B13:E16 pour réaliser les sommes,
• Réaliser le raccourci ALT + = pour afficher les résultats dans les cellules vides,

Analyse conditionnelle de la rentabilité des ventes
En ligne 18, il s'agit d'indiquer la mention OUI lorsqu'un programme est considéré comme rentable et NON dans le cas contraire. Bien sûr il s'agit de considérer les contraintes indiquées dans le panneau de contrôle afin que tous les calculs et leurs interprétations soient dynamiques et automatiques. Selon le panneau de contrôle justement, un spectacle est considéré comme rentable lorsque le montant des ventes réalisées dépasse 75% du montant des ventes maximum. Ce critère doit être vérifié par une fonction Si qui permettra d'envisager les deux cas pour l'analyse de la rentabilité.

• Sélectionner toutes les cellules de l'analyse à réaliser, soit la plage B18:E18,
• Taper le symbole = pour débuter le calcul comme toujours,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit SI(,
• Sélectionner la cellule des ventes réalisées, soit B16,
• Taper le slash du pavé numérique (/) pour la division,
• Sélectionner la cellule des ventes max, soit B15,
• Taper le symbole supérieur (>) pour la comparaison,
• Sélectionner la cellule du taux de rentabilité, soit J18,
• Puis, enfoncer la touche F4 du clavier pour la figer dans le calcul,
• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,

La formule à ce stade est la suivante :

=si(B16/B15 > $J$18;

Nous n'avons pour l'instant défini que le critère. Nous comparons le ratio des ventes réalisées (B16/B15) à celui imposé par le seuil de rentabilité du tableau de bord (J18). Nous avons une fois de plus figé cette cellule pour pouvoir reproduire le calcul sur toute la plage, sans déplacer cet élément de référence pour la comparaison.

• Saisir le texte OUI entre guillemets ('OUI'),
• Taper un point-virgule pour passer à l'argument Sinon de la fonction Si,
• Saisir le texte NON entre guillemets ('NON'),
• Fermer la parenthèse de la fonction Si,
• Valider le calcul par le raccourci CTRL + Entrée pour le répliquer sur toute la plage,

Exploitation du tableau de bord - Format dynamique
Afin que les résultats sautent aux yeux et que le tableau de bord soit parfaitement fonctionnel, nous souhaitons faire ressortir les résultats importants visuellement. Mais cette mise en valeur doit s'adapter aux valeurs des cellules qui, liées aux calculs, sont susceptibles d'évoluer aux prochaines mises à jour, pour les spectacles suivants. Nous souhaitons faire ressortir en couleur les mentions OUI et les noms des spectacles associés. Il s'agit donc de poser une mise en forme conditionnelle sur les plages de cellules B3:E3 et B18:E18. Les titres en ligne 3 seront impactés en fonction des valeurs testées (si égales à OUI) en ligne 18. Comme nous l'avions appris dans la formation Excel pour réaliser des synoptiques, il est tout à fait possible de régler un format dynamique qui réagit en fonction des valeurs de cellules externes.

• Sélectionner les cellules à mettre en valeur dynamiquement soit les plages de cellules B3:E3 et B18:E18,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue, sélectionner le dernier type : Utiliser une formuleÂ…,
• Cliquer alors dans la zone de saisie située en dessous, pour y placer le point d'insertion,
• Taper le symbole = et cliquer sur la cellule B18 pour la désigner,
• Enfoncer la touche F4 du clavier pour conserver le dollar seulement devant l'indice de ligne,
• Taper le symbole = suivi du texte OUI entre guillemets (='OUI'),
• Cliquer alors sur le bouton Format situé juste en dessous,
• Dans la boîte de dialogue qui suit, attribuer une police grasse et rouge,
• Puis activer l'onglet Remplissage et sélectionner une couleur orange pâle,
• Valider la boîte de dialogue pour revenir sur la précédente,
• Puis cliquer sur Ok pour valider la seconde boîte de dialogue.

De retour sur la feuille, vous remarquez que les spectacles dont l'analyse a confirmé la rentabilité, apparaissent en valeur, selon les réglages définis par le biais du bouton Format.


Nous associons une mise en forme conditionnelle aux cellules portant la mention OUI et à celles qui leur sont liées (=B$18='OUI'). En effet les cellules des titres des spectacles ne porteront jamais la mention OUI. Mais en figeant la cellule en ligne (B$18), nous demandons que le critère ne soit vérifié que sur la ligne 18 figée et qu'il soit répliqué, s'il est vérifié, sur la ligne 3.

Nous avons donc construit un tableau de bord intéressant pour effectuer l'analyse de la rentabilité des ventes dans le secteur culturel. Comme tous les calculs sont liés entre eux et aux contraintes du panneau de contrôle, ils se mettent à jour dès lors que les valeurs changent. L'application est donc parfaitement portable.

En C8, si vous modifiez la valeur 1823 par 1600 pour le nombre de places vendues en tarif plein, vous remarquez que les calculs de la colonne se mettent à jour. L'analyse de la rentabilité en ligne 18 révèle désormais que ce spectacle n'est plus rentable. De fait, le résultat et son titre retrouvent leur mise en forme d'origine grisée, puisqu'il n'y a plus lieu de le faire ressortir.