Analyser plusieurs hypothèses

Avant de prendre une décision, il est courant de comparer plusieurs hypothèses comme des prévisions ou des offres commerciales. Bien entendu, le tableur semble l’outil idéal pour faciliter ce genre de comparaisons. Mais il présente une lacune : impossible de stocker plusieurs valeurs différentes dans une même cellule. Comment, par conséquent, comparer des offres dont les tarifs sont différents ? La solution réside dans le gestionnaire de scénarios.

Posez le problème

Nous partirons de l’exemple suivant : vous organisez des conférences et vous avez le choix entre trois salles : Victor Hugo, Debussy et Blaise Pascal. Pour chacune, le loueur demande une somme mensuelle, différente pour chaque salle et variable d’un mois sur l’autre et un forfait par participant, lui aussi variable. Dans l’exemple illustré ci-dessous, vous prévoyez 2 365 participants pour le mois de janvier. Le fixe est de 1 200 euros et le forfait par participant de 0,14 euro. Vous aurez donc à payer 2 365 x 0,14 + 1 200, soit 1 531,10 euros (cellule B7). Les seules valeurs que vous puissiez modifier sont les effectifs de participants des différents mois (les chiffres indiqués en vert). Quant aux chiffres imposés par les loueurs (les lignes 3 et 5), ils sont illustrés ici sur un fond bleu.

Créez le premier scénario

Nous supposons que les chiffres affichés sont ceux de la salle Victor Hugo. Avec Excel 2007, activez le menu Données, cliquez sur Analyse de scénarios, puis choisissez Gestionnaire de scénarios. Avec Excel 2000, XP et 2003, déroulez le menu Outils, Gestionnaire de scénarios (la suite des opérations est identique pour ces deux versions d’Excel). Cliquez sur le bouton Ajouter. Dans le champ Nom du scénario, tapez Victor Hugo. Vous devez maintenant préciser les cellules prises en compte par ce scénario. Dans notre cas, il s’agit de deux champs B3:G3 pour les prix par personne, et B5:G5 pour les forfaits mensuels. Dans le champ Cellules variables, tapez B3:G3;B5:G5 puis validez par OK (ne tenez pas compte des caractères “ $ ”). Excel vous propose alors de modifier les valeurs de chacune des cellules concernées. Comme elles contiennent déjà les bonnes valeurs, validez par OK puis cliquez sur X pour fermer le gestionnaire de scénarios.

Ajoutez des hypothèses

Modifiez maintenant les contenus des lignes 3 et 5 pour intégrer les chiffres de la salle Debussy. Dans les cellules B3 à G3, saisissez respectivement 0,11 euro, 0,10 euro, 0,10 euro, 0,12 euro, 0,14 euro et 0,14 euro. Enfin, dans les cellules B5 à G5, saisissez 1 100, 1 200, 1 150, 1 400, 1 500 et 1 600. Déroulez de nouveau Outils, Gestionnaire de scénarios, cliquez sur le bouton Ajouter. Donnez à ce scénario le nom Debussy. Dans le champ Cellules variables, tapez B3:G3;B5:G5 puis validez deux fois par OK et cliquez sur X. Enfin, créez de la même façon le scénario Blaise Pascal avec les valeurs affichées ci-dessous (pour plus de clarté, nous avons masqué les lignes 2 et 4 sur cet écran).

Sélectionnez votre scénario

Dans le Gestionnaire de scénarios, il vous suffit de sélectionner un scénario et de cliquer sur Afficher : les valeurs qu’il contient sont immédiatement copiées dans la feuille.

Affichez une synthèse

Il est maintenant possible de comparer vos hypothèses et de visualiser la salle la moins chère (le total à payer, pour le semestre, se situe en H7). Déroulez Outils, Gestionnaire de scénarios et cliquez sur Synthèse. Dans le champ Cellules résultantes, tapez H7 et validez par OK. Excel ajoute à votre classeur un onglet nommé Synthèse de scénarios. Vous constatez alors ? avec les effectifs actuels ? que la salle Blaise Pascal offre le tarif le plus bas.

Modifiez les valeurs initiales

Mettons que vous attendez non plus 328, mais 8 000 personnes en mars. Modifiez, dans ce cas, la cellule D2 puis refaites les étapes du paragraphe précédent. C’est maintenant la salle Debussy qui offre le meilleur tarif.