andreworia/scenario-manager

Gestionnaire de scénarios (Base / Bull / Bear)

Quand l'utiliser

À utiliser lorsqu'un modèle doit basculer proprement entre les cas Base, Bull et Bear, pilotés par un petit ensemble d'hypothèses, et que vous souhaitez que chaque cas soit visible et vérifiable sur une seule feuille. Cette compétence CONSTRUIT un fichier .xlsx téléchargeable à partir de zéro dans l'application Claude (openpyxl plus validation de données), avec un sélecteur en direct, des extractions d'hypothèses via formules, un tableau de comparaison et une passe de recalcul et vérification. Il ne s'agit pas du module complémentaire Claude pour Excel.

Ce qu'elle construit

Cinq zones : un onglet Cover (sélecteur et résumé), un onglet Scenario Inputs (chaque moteur sous Base, Bull, Bear), des liens en direct VERS le modèle pour que les hypothèses extraient le cas actif, un onglet de comparaison Outputs (résultats clés pour les trois cas côte à côte) et un onglet Checks.

Flux de construction

1. Lister les moteurs qui diffèrent selon les cas (par exemple croissance, prix, attrition, capex) et trouver la cellule d'hypothèse de chaque moteur dans le modèle.
2. Construire le bloc Scenario Inputs contenant les valeurs Base, Bull, Bear pour chaque moteur.
3. Construire une cellule de sélecteur unique (1, 2 ou 3) avec une liste déroulante de validation de données.
4. Réécrire chaque cellule d'hypothèse du modèle pour extraire le cas actif via CHOOSE ou INDEX faisant référence au sélecteur.
5. Construire l'onglet Outputs comparant les résultats clés pour les trois cas (calcul parallèle, voir spéc.) afin que tous les cas s'affichent simultanément quel que soit le sélecteur.
6. Ajouter l'onglet Checks.
7. Recalculer le classeur dans un moteur réel (LibreOffice sans interface graphique).
8. Vérifier zéro erreur de formule (#REF!, #DIV/0!, #VALEUR!, #NOM?), confirmer que les contrôles passent, puis livrer le fichier .xlsx.

Spécifications onglet par onglet

Onglet Cover

• B2 : cellule du sélecteur. Appliquer une liste de validation de données 1,2,3 (ou une liste nommée Base, Bull, Bear mappée à 1/2/3).
• B3 : nom du cas actif, =CHOOSE(B2,"Base","Bull","Bear").
• Un petit résumé des résultats qui lit la sortie du modèle en direct sous le cas actif, par exemple B6 =Model!B40.

Onglet Scenario Inputs

Une ligne par moteur, une colonne par cas :

• A nom du moteur, B Base, C Bull, D Bear (saisies bleues), E valeur active (formule noire).
• La valeur active est extraite avec le sélecteur. Avec CHOOSE : E5 =CHOOSE(Cover!$B$2,B5,C5,D5).
• Équivalent avec INDEX : E5 =INDEX(B5:D5,Cover!$B$2). INDEX est plus propre lorsque le nombre de cas peut augmenter ; CHOOSE est plus explicite pour trois cas fixes.
• Copier la formule choisie sur chaque ligne de moteur pour que la colonne soit cohérente.

Liens vers le modèle

• Chaque cellule d'hypothèse du modèle pointe vers la valeur active, par exemple Model!B6 =ScenarioInputs!E5.
• Ne jamais laisser le modèle contenir une hypothèse saisie qu'un scénario devrait contrôler ; la valeur doit circuler sélecteur -> colonne active -> modèle.
• Conserver le lien avec la couleur verte de référence croisée pour que les réviseurs voient que l'hypothèse est pilotée, non saisie.

Onglet Outputs (comparaison côte à côte)

Afficher chaque cas simultanément, pas seulement le cas actif. Deux modèles robustes :

• Calcul parallèle (préféré pour la vérifiabilité) : reproduire les quelques formules de sortie trois fois, chacune alimentée par la colonne Base, Bull ou Bear directement plutôt que par la colonne active. Par exemple, la VAN sous Bull recalcule la chaîne de sortie en utilisant ScenarioInputs!C5:C12 au lieu de E. Cela montre les trois sans toucher au sélecteur.
• Lecture par cas : si le modèle est volumineux, documenter que les cellules de comparaison capturent la sortie en direct pour chaque cas en réglant le sélecteur sur 1, 2, 3 à tour de rôle lors de l'étape de recalcul et en collant chaque valeur capturée ; noter clairement quelles cellules sont des instantanés capturés par rapport à des valeurs en direct.
• Disposition : lignes = indicateurs clés (Revenus, EBIT, VAN, TRI), colonnes = Base, Bull, Bear, plus une colonne d'écart Bull-moins-Base et Bear-moins-Base.

Onglet Checks

• Sélecteur dans la plage : =AND(Cover!B2>=1,Cover!B2<=3,Cover!B2=ENT(Cover!B2)) VRAI.
• Chaque moteur a les trois cas remplis : =NB.VIDE(ScenarioInputs!B5:D12)=0 VRAI.
• La colonne active correspond au sélecteur : pour un moteur repéré, =(ScenarioInputs!E5=CHOOSE(Cover!B2,ScenarioInputs!B5,ScenarioInputs!C5,ScenarioInputs!D5)) VRAI.

Notes de construction avec openpyxl

• Ajouter la liste déroulante avec openpyxl.worksheet.datavalidation.DataValidation(type="list", formula1='"1,2,3"'), puis ws.add_data_validation(dv) et dv.add(ws["B2"]).
• Écrire chaque cellule de valeur active et de lien comme une chaîne de formule, jamais comme la valeur numérique résolue ; la valeur n'apparaît qu'après recalcul.
• Si vous utilisez des cas nommés dans la liste déroulante, conserver un mappage masqué pour que le sélecteur se résolve toujours en un entier pour CHOOSE/INDEX ; mélanger texte et index dans un seul sélecteur casse la formule.
• Remplir la cellule du sélecteur avec un arrière-plan distinct et une bordure épaisse pour que les réviseurs voient immédiatement la cellule de contrôle unique.

Vérification de sens

• Bull ne devrait pas produire un résultat pire que Base sur l'indicateur principal, et Bear ne devrait pas surpasser Base ; si c'est le cas, les valeurs de cas d'un moteur sont probablement interverties entre les colonnes.
• Ajouter une vérification informelle facultative sur l'onglet Outputs comparant le signe de chaque écart à la direction attendue, afin qu'une colonne inversée soit détectée lors du recalcul plutôt qu'en révision.

Conventions de format et de mise en forme

• Police bleue pour les valeurs saisies Base/Bull/Bear et la cellule du sélecteur.
• Police noire pour les formules internes à l'onglet (la colonne CHOOSE/INDEX de valeur active, les calculs de sortie).
• Police verte pour les liens de référence croisée comme =ScenarioInputs!E5 à l'intérieur du modèle.
• Pas de valeurs en dur là où une formule est attendue : la colonne active et les hypothèses du modèle sont toujours des formules, jamais la valeur de cas retapée.
• Une formule cohérente par ligne ou colonne : le même motif CHOOSE/INDEX dans la colonne active ; la même formule de sortie dans les colonnes de comparaison.
• Formats de nombre : taux 0.0%, devise #,##0, multiples 0.00x. Mettre en évidence la cellule du sélecteur avec un remplissage pour qu'il soit évident où basculer.

Compromis par rapport au Gestionnaire de scénarios intégré

Le Gestionnaire de scénarios intégré d'Excel stocke les valeurs de cas dans une boîte de dialogue, invisibles sur la feuille, écrase les saisies en direct lorsqu'elles sont affichées et se désynchronise facilement du modèle. Un bloc de basculement visible maintient chaque cas sur la grille, rend l'extraction active une formule traçable et permet aux réviseurs de voir les trois résultats côte à côte sans ouvrir de menu. Il est plus vérifiable et résiste à la copie, à la révision et au contrôle de version.

Vérifications

Voir l'onglet Checks ci-dessus : sélecteur entre 1 et 3 et entier, aucune cellule de cas vide, valeur active égale au cas du sélecteur. Les trois doivent renvoyer VRAI.

Recalcul et vérification

Enregistrer avec openpyxl, recalculer sans interface graphique (par exemple libreoffice --headless --convert-to xlsx) pour que CHOOSE/INDEX et les liens entre feuilles se résolvent. Recharger, rechercher les #REF!, #DIV/0!, #VALEUR!, #NOM?. Basculer le sélecteur sur 1, 2 et 3 et confirmer que les sorties changent et que la vérification active tient pour chaque réglage. Corriger et répéter jusqu'à propreté, puis livrer.

Entrées à recueillir

• Chemin d'accès au modèle et adresse de la cellule d'hypothèse pour chaque moteur.
• Valeurs Base, Bull et Bear pour chaque moteur.
• Les indicateurs de sortie clés à comparer et leurs cellules.
• Si la comparaison doit être en calcul parallèle (en direct) ou des instantanés capturés.

Exemple

Moteurs hypothétiques : croissance des revenus (Base 4 %, Bull 8 %, Bear 1 %), marge brute (Base 55 %, Bull 58 %, Bear 50 %). Sélecteur Cover!B2 = 2 sélectionne Bull, donc ScenarioInputs!E5 =CHOOSE(2,4%,8%,1%) = 8% circule vers Model!B6. L'onglet Outputs affiche la VAN de (par exemple) 760 Base, 1 140 Bull, 410 Bear en unités hypothétiques, avec un écart Bull-moins-Base de 380, le tout visible simultanément.