andreworia/sensitivity-tables

Tableaux de Sensibilité (Une et Deux Variables)

Quand l'utiliser

À utiliser lorsqu'un modèle stratégique ou financier présente un résultat clé unique et que vous souhaitez montrer comment ce résultat évolue en fonction d'un ou deux moteurs (variables d'entrée) sur une plage de valeurs. Les moteurs typiques sont le prix, le volume, le taux d'actualisation, la croissance, le taux de désabonnement (churn) ou le coût unitaire. Cette compétence CONSTRUIT un fichier .xlsx téléchargeable à partir de zéro dans l'application Claude (openpyxl plus numpy), avec des formules en direct et une passe de recalcul et de vérification. Il ne s'agit pas du module complémentaire Claude pour Excel.

Ce qu'elle construit

Un onglet Sensitivity unique qui se rattache à un modèle existant. Il référence les cellules d'entrée et la cellule de sortie du modèle, dispose des grilles à une et deux variables, et applique une échelle de couleurs au bloc de résultats. Si le modèle source se trouve sur une autre feuille, la grille s'y connecte via des références entre feuilles pour rester en direct.

Flux de construction

1. Identifier la cellule de sortie (par exemple Model!B40 contenant la VAN) et les une ou deux cellules d'entrée qui la pilotent (par exemple Model!B6 taux d'actualisation, Model!B7 croissance).
2. Confirmer que la sortie est une chaîne de formules en direct retournant aux entrées. Si la sortie est codée en dur, arrêter et câbler d'abord le modèle ; un tableau de données sur une constante retourne la constante.
3. Choisir des plages d'entrée autour du cas de base (par exemple taux d'actualisation de base 10 %, balayage de 6 % à 14 % par pas de 2 %).
4. Construire la structure native du tableau de données Excel : formule d'angle, axes d'entrée et plage matricielle {=TABLE(r,c)} (voir spécifications).
5. Construire ÉGALEMENT la grille calculée par le code comme solution de secours robuste : réévaluer le modèle en Python pour chaque combinaison d'entrées et écrire les valeurs résultantes sous forme de matrice étiquetée.
6. Appliquer une échelle de couleurs à 3 couleurs au bloc de résultats et étiqueter les deux axes avec les noms des cellules d'entrée.
7. Ajouter le bloc de vérifications.
8. Recalculer le classeur dans un moteur réel (LibreOffice en mode tête).
9. Vérifier l'absence d'erreurs de formule (#REF!, #DIV/0!, #VALEUR!, #NOM?), confirmer que les vérifications sont réussies, puis livrer le fichier .xlsx.

Spécifications onglet par onglet

Onglet Sensitivity, bloc à une variable

Choisissez une direction de disposition. Pour des entrées descendant le long d'une colonne :

• B3 : la formule de sortie, référençant la sortie en direct : =Model!B40. C'est la cellule d'angle.
• A4:A10 : les valeurs d'entrée balayées (entrées en bleu), par exemple 6%, 8%, 10%, 12%, 14%.
• B4:B10 : la colonne des résultats du tableau de données.
• Sélectionner A3:B10, puis Données > Analyse d'hypothèses > Table de données, en laissant la cellule d'entrée de ligne vide et en définissant la cellule d'entrée de colonne sur Model!B6. Excel écrit {=TABLE(,Model!B6)} dans B4:B10.
• openpyxl peut écrire la chaîne de formule matricielle =TABLE(,Model!B6) dans le bloc et la formule d'angle =Model!B40, mais les valeurs ne se remplissent qu'après recalcul dans un moteur réel.

Solution de secours calculée par le code pour le même bloc (recommandée lors de la génération via du code), écrite dans une matrice étiquetée en dessous, par exemple à partir de A14 :

• A14 : étiquette "Taux d'actualisation". B14 : "VAN (calculée)".
• Pour chaque valeur d'entrée dans A15:A21, Python définit Model!B6 à cette valeur, recalcule la logique du modèle en numpy, et écrit la VAN résultante comme une valeur simple dans la colonne B.
• Conserver les deux : le tableau de données natif prouve qu'il est en direct ; la matrice calculée garantit des nombres remplis même avant que l'utilisateur n'ouvre Excel.

Onglet Sensitivity, bloc à deux variables

• Cellule d'angle E3 : =Model!B40 (la sortie en direct).
• E4:E10 : valeurs d'entrée de ligne DESCENDANT le bord gauche (premier moteur, par exemple croissance 0%, 1%, ... 6%).
• F3:L3 : valeurs d'entrée de colonne TRAVERSANT la première ligne (second moteur, par exemple taux d'actualisation 6% ... 14%).
• Sélectionner E3:L10, Données > Analyse d'hypothèses > Table de données, Cellule d'entrée de ligne = Model!B7 (le moteur transversal), Cellule d'entrée de colonne = Model!B6 (le moteur descendant). Excel remplit F4:L10 avec {=TABLE(Model!B7,Model!B6)}.
• Attention à l'orientation : la cellule liée à la PREMIÈRE LIGNE va dans Cellule d'entrée de ligne ; la cellule liée à la COLONNE DE GAUCHE va dans Cellule d'entrée de colonne. Les intervertir transpose le résultat silencieusement.
• Solution de secours calculée par le code : une double boucle en Python sur les deux axes, écrivant la matrice de valeurs dans un bloc étiqueté (en-têtes d'axe en vert s'ils proviennent du modèle, en bleu s'il s'agit de valeurs littérales de balayage).

Choix des plages d'axes

• Centrer chaque axe sur la valeur du cas de base de sorte que la base se trouve au milieu de la ligne et de la colonne, ce qui rend significative la vérification de la cellule centrale.
• Utiliser un nombre impair d'étapes (5, 7 ou 9) afin qu'une seule cellule centrale corresponde exactement au cas de base plutôt que de se trouver entre deux cellules.
• Garder les tailles de pas uniformes sur chaque axe (incrément constant) pour que la grille se lise comme un gradient propre et que l'échelle de couleurs soit interprétable.
• Pour les taux, balayer en points absolus (8%, 10%, 12%), pas en pourcentage relatif de la base, pour éviter de confondre les lecteurs.

Notes de construction openpyxl

• Écrire la cellule d'angle comme une vraie chaîne de formule : ws["B3"] = "=Model!B40". Ne pas coller la valeur numérique actuelle de la sortie.
• Pour le tableau de données natif en matrice, openpyxl peut stocker le texte =TABLE(...), mais un moteur réel doit recalculer pour remplir le bloc ; traiter la matrice comme un espace réservé jusqu'à ce que l'étape de recalcul s'exécute.
• Lors de l'écriture de la solution de secours calculée, définir les valeurs des données avec Python (ws.cell(row=r, column=c, value=float(npv))) et les garder visuellement distinctes du bloc en direct avec une note d'en-tête telle que "instantané calculé".
• Appliquer l'échelle de couleurs avec openpyxl.formatting.rule.ColorScaleRule sur le bloc de résultats, en mappant le min à une couleur, le max à une autre via un point médian.

Conventions de formules et de mise en forme

• Police bleue pour les entrées (les valeurs d'axes balayées que vous saisissez).
• Police noire pour les formules dans l'onglet.
• Police verte pour les liens entre onglets tels que =Model!B40.
• Pas de valeurs en dur là où une formule est requise : la cellule d'angle doit être =Model!B40, jamais le nombre du cas de base saisi.
• Une formule cohérente par ligne ou colonne : tout le bloc du tableau de données est une seule matrice ; la solution de secours calculée utilise une expression Python appliquée à l'ensemble de la grille.
• Formats de nombre : taux en 0,0%, résultats monétaires en # ##0 (avec espace comme séparateur de milliers pour la locale fr), ratios en 0,00. Les valeurs des axes correspondent à l'unité de la cellule d'entrée qu'elles remplacent.

Vérifications

• La cellule d'angle est égale à la sortie en direct : =(B3=Model!B40) renvoie VRAI.
• Axes étiquetés avec les noms des cellules d'entrée (une étiquette de texte au-dessus ou à côté de chaque axe nommant le moteur et son adresse de cellule).
• Le centre de la grille correspond au cas de base : la cellule de la grille à la ligne et à la colonne du cas de base est égale à la sortie actuelle du modèle, =(<cellule_centrale>=Model!B40) VRAI.
• Pour la solution de secours calculée, la cellule aux entrées de base est égale à la cellule du tableau de données natif aux mêmes entrées (à l'arrondi près).

Recalculer et vérifier

Sauvegarder avec openpyxl, puis recalculer en mode tête (par exemple libreoffice --headless --convert-to xlsx) pour que =TABLE(...) et les liens entre feuilles se remplissent. Recharger et scanner chaque cellule pour #REF!, #DIV/0!, #VALEUR!, #NOM?. Confirmer que les trois vérifications renvoient VRAI. Si une erreur ou FAUX apparaît, corriger la référence ou la plage et boucler jusqu'à ce que ce soit propre, puis livrer.

Entrées à recueillir

• Chemin d'accès au modèle source et adresse exacte de la cellule de sortie.
• La ou les deux adresses de cellules d'entrée à balayer.
• Valeurs des cas de base et plage souhaitée avec le pas pour chaque axe.
• Unités et formats de nombre préférés pour les axes et la sortie.

Exemple

Modèle hypothétique : Model!B40 = VAN du projet, pilotée par Model!B6 taux d'actualisation (base 10%) et Model!B7 croissance du chiffre d'affaires (base 3%). Le bloc à une variable balaie le taux d'actualisation de 6% à 14% par pas de 2%, donnant une VAN de (par exemple) 1 210 / 980 / 760 / 560 / 380 en unités hypothétiques. Le bloc à deux variables croise la croissance de 0% à 6% avec le taux d'actualisation de 6% à 14% ; la cellule centrale à croissance 3% et taux d'actualisation 10% égale Model!B40 de 760, confirmant que le cas de base se trouve au milieu de la grille.