andreworia/inputs-calcs-outputs-design

Conception Inputs-Calcs-Outputs

Quand l'utiliser

Utilisez cette compétence lors de la construction ou de la restructuration de tout modèle Excel qui doit être partagé, audité ou maintenu dans le temps. C'est l'outil adapté lorsqu'un modèle a des hypothèses mélangées à des formules, lorsqu'il n'est pas clair quelles cellules sont des entrées par rapport aux calculs, ou lorsqu'un client ou un relecteur a signalé que le modèle est "difficile à suivre". C'est également le point de départ pour tout nouveau modèle où le principe des trois onglets sera appliqué de zéro.

Ce qu'il fait

Produit la structure de conception complète pour trois zones distinctes : un onglet Inputs (toutes les hypothèses modifiables par l'utilisateur, clairement étiquetées et colorées en bleu), un ou plusieurs onglets Calculations (toute la logique de formules, sans nombres codés en dur, référençant uniquement l'onglet Inputs et les autres onglets Calc), et un onglet Outputs (pas de calculs, uniquement des références aux résultats finaux des Calc, formaté pour la communication). Inclut une liste de vérification des conventions pour vérifier la conformité.

Méthode

1. Comprendre le principe des trois onglets. La règle fondamentale est : séparez ce que vous contrôlez (entrées) de la façon dont le modèle fonctionne (calculs) de ce que vous présentez (sorties). Cette séparation signifie : tout utilisateur peut modifier les hypothèses sans toucher aux formules ; tout relecteur peut auditer la logique sans chercher à travers les cellules fusionnées ; toute version mise à jour peut être remise sans briefing. C'est la discipline structurelle la plus importante en modélisation financière.

2. Auditer le modèle existant pour les violations. Si vous restructurez un modèle existant, effectuez d'abord un audit. Dans Excel, utilisez Ctrl+` pour basculer en affichage des formules. Identifiez : (a) les nombres codés en dur dans les formules (par exemple, =A1*0,15 où 0,15 devrait être une entrée), (b) les cellules de formule dans l'onglet Inputs, (c) les données brutes mélangées aux résultats calculés sur le même onglet. Signalez chaque violation avant de reconcevoir.

3. Concevoir l'onglet Inputs. L'onglet Inputs est le seul endroit où les nombres sont codés en dur. Concevez-le avec la structure suivante :

   Bloc d'en-tête en haut : Nom du modèle, version, date, nom de l'analyste.

   Sections d'hypothèses, une par module analytique. Chaque section comporte : une ligne d'en-tête de section en gras avec un fond foncé, puis des lignes pour chaque hypothèse avec trois colonnes : Étiquette de l'hypothèse (texte, aligné à gauche) | Valeur (fond bleu, aligné à droite, modifiable) | Unité ou Note (texte, gris, aligné à droite, par ex., "% par an", "USD 2024").

   Exemples de sections : Hypothèses de revenus, Hypothèses de coûts, Structure du capital, Paramètres macro/marché, Facteurs opérationnels.

   Règles pour l'onglet Inputs : aucune formule dans la colonne Valeur (uniquement des nombres codés en dur), chaque hypothèse de cet onglet doit avoir une étiquette, ne laissez jamais un nombre sans étiquette. Appliquez un fond bleu à chaque cellule de Valeur. Verrouillez toutes les cellules non-valeur.

4. Gérer les entrées calculées. Certaines "entrées" sont dérivées d'autres entrées (par exemple, le point médian d'une plage, ou une moyenne pondérée de deux taux). Elles vont sur l'onglet Inputs dans une section distincte "Hypothèses dérivées", clairement marquées comme formules (police noire, pas de fond bleu). Cela les rend visibles pour l'utilisateur tout en signalant qu'il ne s'agit pas de cellules à saisie libre.

5. Concevoir les onglets Calculations. Un onglet Calc par module analytique. Règles pour les onglets Calc :

   Pas de nombres codés en dur dans les formules. Chaque constante doit référencer l'onglet Inputs.

   Les en-têtes de colonnes sont des périodes (années, trimestres, mois) ou des catégories.

   Les en-têtes de lignes sont des postes (par exemple, Revenus, COGS, Bénéfice brut).

   Chaque formule référence soit : l'onglet Inputs (pour les hypothèses), l'onglet Lookup (pour les données de référence), ou une autre cellule du même onglet Calc ou d'un onglet Calc frère (pour les calculs intermédiaires).

   Utilisez des formules simples et lisibles. Évitez les instructions IF imbriquées sur plus de deux niveaux. Au lieu de cela, décomposez la logique complexe en lignes d'aide.

   Appliquez une police noire sur fond blanc à toutes les cellules de formule. Ajoutez des en-têtes de ligne en fond gris.

6. Structurer une disposition standard d'onglet Calc. Chaque onglet Calc suit cette disposition : (Ligne 1) Titre de l'onglet et énoncé de l'objectif. (Ligne 2) En-têtes de colonnes (années ou périodes). (Ligne 3 et suivantes) Lignes de calcul, regroupées en sections avec des en-têtes de section en gras. (Dernières lignes) Lignes de résumé ou de sortie qui seront référencées par l'onglet Outputs. Ajoutez une bordure fine autour des lignes de sortie pour signaler qu'elles sont référencées ailleurs.

7. Appliquer la règle d'absence de code en dur dans les onglets Calc. Utilisez Rechercher et Remplacer d'Excel pour vérifier la présence de nombres codés en dur dans les formules. Appuyez sur Ctrl+H, cliquez sur Options, cochez "Rechercher dans : Formules". Recherchez les valeurs codées en dur courantes (0,25, 12, 1000). Signalez chaque occurrence. Remplacez par une référence à une cellule Inputs, en créant une nouvelle ligne d'hypothèse sur l'onglet Inputs si elle n'existe pas.

8. Concevoir l'onglet Outputs. L'onglet Outputs a une règle : pas de calculs. Il contient uniquement des références à des cellules des onglets Calc (en utilisant des formules = qui pointent vers des cellules spécifiques) et du formatage. Il peut contenir des graphiques (les graphiques référencent les données des onglets Calc), des tableaux formatés (valeurs extraites via des références de cellules) et des blocs de texte narratif (dans des zones de texte ou des cellules fusionnées). Il doit ressembler à une page de rapport finie, pas à une feuille de calcul de travail.

9. Structurer la disposition de l'onglet Outputs. Utilisez une structure en grille : section supérieure pour les tuiles KPI (4 à 6 indicateurs clés, chacun dans une zone formatée), section centrale pour le tableau récapitulatif (résultats clés par année ou scénario), section inférieure pour les graphiques. Appliquez un formatage professionnel : pas de grille visible, bordures nettes, palette de couleurs de l'entreprise.

10. Établir des conventions de références croisées entre onglets. Lorsqu'une cellule d'un onglet Calc référence une cellule de l'onglet Inputs, la formule doit être claire. Bonne pratique : utilisez le nom de l'onglet explicitement dans la formule (par exemple, =Inputs!B12 plutôt que simplement =B12). Pour les plages nommées, la formule est encore plus claire : =inp_RevenueGrowthY1. Utilisez toujours des références absolues ($) lors du référencement des entrées depuis les onglets Calc pour éviter des décalages accidentels de référence relative.

11. Appliquer la protection. Protégez les onglets Calculations (autorisez uniquement la sélection des cellules déverrouillées). Sur l'onglet Inputs, verrouillez tout sauf les cellules de Valeur bleues. Sur l'onglet Outputs, verrouillez tout (aucune édition). Utilisez un mot de passe simple ou activez simplement la protection de la feuille sans mot de passe pour empêcher les modifications accidentelles.

12. Créer la liste de vérification des conventions. Après avoir conçu le modèle, vérifiez la conformité par rapport à cette liste :

    • Toutes les valeurs d'hypothèses sont sur l'onglet Inputs, pas intégrées dans les formules.
    • Toutes les cellules de la colonne de valeur de l'onglet Inputs ont un fond bleu.
    • Aucune formule d'un onglet Calc ne contient de nombre codé en dur.
    • Aucune formule n'apparaît dans l'onglet Outputs (uniquement des références de cellules).
    • Tous les onglets Calc référencent l'onglet Inputs par son nom (Inputs!refcell ou plage nommée).
    • L'onglet Outputs n'a pas de grille visible et est prêt à imprimer.
    • Les trois zones d'onglets sont colorées dans la barre d'onglets.
    • Un onglet Légende documente les conventions.

Entrées

• Description de l'objectif du modèle et des modules analytiques
• Liste des hypothèses clés qui pilotent le modèle (pour remplir la structure de l'onglet Inputs)
• Liste des sorties clés requises (pour concevoir l'onglet Outputs)
• État actuel du modèle (nouvelle construction vs restructuration d'un modèle existant)
• Version d'Excel (affecte les fonctionnalités disponibles pour la protection et la dénomination)

Format de sortie

Un cahier des charges complet de conception à trois onglets avec :

1. Conception de l'onglet Inputs : Liste des en-têtes de section, disposition des colonnes (Étiquette, Valeur, Unité), et un tableau de chaque hypothèse clé avec sa valeur par défaut, son unité et son affectation de section.
2. Conceptions des onglets Calc : Une section par onglet Calc, montrant la structure des lignes (postes), la structure des colonnes (périodes), et les lignes qui référencent les Inputs et celles qui sont des calculs internes.
3. Conception de l'onglet Outputs : Description de la disposition en grille avec les positions des tuiles KPI, la structure du tableau et les descriptions des graphiques.
4. Modèles de formules de références croisées : Exemples écrits de la façon dont les onglets Calc référencent l'onglet Inputs.
5. Liste de vérification des conventions : 12 à 15 éléments.