Étape 1 : insérez les formules
1 Nous partirons de l’exemple suivant : vous organisez des conférences pendant quatre mois et vous avez le choix entre trois salles nommées Flandres, Bretagne et Aquitaine. Pour chacune, le loueur vous 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. Votre objectif est de trouver la salle la moins chère pour l’ensemble de la période.2 Dans l’exemple illustré ci-dessous, pour le mois de janvier, vous prévoyez 2 365 participants. Pour ce mois, le forfait est de 1 200,00 euros auxquels il faut ajouter 0,14 euro par participant.3 Dans la cellule B4, saisissez la formule = B2*B3, puis, en B7, la formule = B4+B6. Vous obtenez en B7 un prix total, pour janvier, de 1 531,10 euros, soit 2 365 x 0,14 + 1 200. Les seules valeurs que vous pouvez modifier sont les effectifs de participants des différents mois (les chiffres sur fond bleu dans notre exemple). Quant aux lignes 3 et 6 (sur fond orange), ce sont les tarifs imposés par le loueur que vous ne pouvez bien sûr pas changer.4 Commençons par la salle Flandres. Saisissez le nombre de participants (ligne 2) ainsi que les tarifs du loueur (lignes 3 et 6) pour février, mars et avril. Enfin, recopiez la formule B7 sur le champ C7:E7. Et, en F7, saisissez la formule =SOMME(B7:E7) pour obtenir le budget total.
Étape 2 : créez le premier scénario
1 Nous allons créer le scénario Flandres. Activez le menu Données, cliquez sur Analyse de scénarios, puis choisissez Gestionnaire de scénarios.2 Dans la fenêtre qui apparaît, cliquez sur Ajouter. Dans le champ Nom du scénario, tapez Flandres et validez. Il faut maintenant préciser quelles sont les cellules concernées par ce scénario. Dans notre cas, il s’agit des deux champs : B3:E3 pour les prix par personne, et B6:E6 pour les forfaits mensuels. Dans le champ Cellules variables, tapez B3:E3;B6:E6. Le cas échéant, tapez un petit texte explicatif dans le champ Commentaire, puis validez par OK. Ignorez les caractères “ $ ” qu’Excel a ajoutés aux adresses de calcul.3 Le tableur vous propose alors de modifier les valeurs de chacune des cellules concernées. Utilisez les barres de défilement pour accéder aux valeurs masquées. Dans notre cas, comme les huit cellules contiennent déjà les bonnes valeurs, il vous suffit de valider par OK, puis de cliquer sur X pour fermer le gestionnaire de scénarios.
Étape 3 : ajoutez une hypothèse
1 Vous allez maintenant modifier les contenus des lignes 3 et 5 pour y mettre les chiffres de la salle Bretagne. Dans les cellules B3 à E3, saisissez respectivement 0,11 euro, 0,10 euro, 0,10 euro et 0,12 euro. Enfin, dans les cellules B6 à E6, saisissez 1 100, 1 200, 1 150 et 1 400. Cliquez de nouveau sur Analyse de scénarios, Gestionnaire de scénarios. Vous voyez maintenant apparaître le scénario Flandres.2 Cliquez sur Ajouter. Donnez à ce scénario le nom Bretagne et cliquez sur OK. Dans le champ Cellules variables, tapez B3:E3;B6:E6, puis validez deux fois par OK. Vous avez maintenant deux scénarios visibles. Cliquez sur X.3 Enfin, refaites les mêmes opérations pour créer le scénario associé à la salle Aquitaine avec les valeurs suivantes : 0,13 euro, 0,15 euro, 0,26 euro et 0,15 euro pour les cellules B3 à E3. Pour les cellules B6 à E6, vous saisirez respectivement 1 150 euros, 1 300 euros, 1 100 euros et 900 euros, comme sur l’écran ci-contre (nous y avons temporairement masqué les lignes 4 et 5 pour des raisons de lisibilité).
Étape 4 : sélectionnez un scénario au choix
Le travail fastidieux est terminé ! Vous pouvez maintenant activer immédiatement un scénario, en d’autres termes, recopier dans les “ bonnes ” cellules de la feuille de calcul les valeurs associées à ce scénario. Pour cela, ouvrez le Gestionnaire de scénarios, sélectionnez-en un et cliquez sur Afficher : les valeurs qu’il contient sont aussitôt copiées dans la feuille.
Étape 5 : affichez une synthèse
Vous allez maintenant comparer vos hypothèses et voir avec quelle salle vous obtenez le budget minimal. Rappelons que ce budget figure en F7. Ouvrez le Gestionnaire de scénarios et cliquez sur Synthèse. Cochez Synthèse de scénarios. Dans le champ Cellules résultantes, tapez F7 et validez par OK.Excel ajoute à votre classeur une nouvelle feuille nommée Synthèse de scénarios. Vous y constatez immédiatement qu’avec les effectifs actuels, c’est la salle Aquitaine qui offre le tarif le plus bas. Remarquez que la synthèse de scénarios utilise le mode Plan d’Excel, que nous vous avons présenté il y a quelques semaines (voir Micro Hebdo n° 667 page 46).
Étape 6 : modifiez les valeurs initiales
Revenez sur votre feuille de données. Simulons que, au mois de mars, vous attendez non plus 328, mais 5 000 personnes. Tapez cette valeur en D2. Ouvrez de nouveau le Gestionnaire de scénarios, puis cliquez sur Synthèse et directement sur OK (Excel a conservé les paramètres utilisés la dernière fois). Cette fois, c’est la salle Bretagne qui offre le meilleur tarif. Vous pouvez maintenant réaliser la puissance de cet outil !
🔴 Pour ne manquer aucune actualité de 01net, suivez-nous sur Google Actualités et WhatsApp.
