33 bonnes pratiques pour créer un modèle avec Excel

Maîtriser ExcelAinsi, vous maîtrisez Excel mais sauriez-vous construire un modèle Excel.
J’entends par maîtriser Excel : construire un tableau qui donne un résultat.

Donc, vous savez :
– utiliser les tableaux croisés dynamiques, la valeur cible et autres gestionnaires de scénarios,
– utiliser filtre et tri,
– construire une ou deux macros,
– et plein d’autres choses.

C’est assez pour passer pour un expert en Excel. Mais si vous passez trop de temps à produire des résultats et trop peu de temps à analyser les données, c’est qu’il vous manque une compétence essentielle :

savoir créer et tester un modèle Excel.

Caractéristiques d’une bonne modélisation :
• Votre modèle Excel est facile à utiliser.
• Vous utilisez ce modèle pour analyser des résultats.
• Le design de votre modèle le rend facile à comprendre.
• Votre modèle est fiable : il est accepté comme l’outil qui permet d’obtenir les résultats.

En construisant un modèle fiable qui va servir de référence, vous augmentez votre influence dans votre poste et dans votre service. Au contraire, une erreur de calcul peut détruire votre crédibilité, or l’étonnante flexibilité des tableaux rend les erreurs très courantes.

Avant de commencer la construction du modèle :

Il vous faut prendre en compte certains paramètres qui vous faciliteront l’exploitation de votre modèle.

1. Connaissez les caractéristiques des tableurs.

Les tableurs sont flexibles et faciles à utiliser mais il existe des inconvénients à les utiliser.

2. Définissez la portée de votre modèle.

– décidez ce qui doit être inclus et ce qui ne doit pas l’être.
– décidez du niveau de détail et trouvez un compromis.
faites approuver les décisions par les parties prenantes.

3. Définissez le niveau de complexité.

– quel niveau de précision recherchez-vous ?
– quelle est la périodicité appropriée (mensuelle, annuelle, trimestrielle, etc.) ?
quelles décisions seront-elles prises à partir du résultat et que cela implique t’il ?
les données sources sont-elles facilement accessibles ? Si elles ne le sont pas, vous devez travailler sur cette question dès maintenant.

4. Prenez en compte les utilisateurs de votre modèle.

Il y aura plusieurs utilisateurs de votre modèle:
- la personne qui effectuera la saisie : quel va être son niveau de compréhension du modèle, sera-t-elle autorisée à faire des modifications ? Lesquelles ? Moins l’utilisateur est expert, plus vous devrez guider la saisie.
– la personne ou les personnes qui utiliseront les résultats : qu’en feront-elles ?

5. Discutez les formules de calcul avec les utilisateurs.

Si vous décidez seul de la formule de calcul, vos utilisateurs la contesteront, surtout si le résultat leur est défavorable.

6. Pensez à la durée de vie de votre modèle.

– Construisez-vous un modèle qui durera longtemps ?
– Devrez-vous le modifier ? Le maintenir ?

Design

Un bon design vous facilitera la construction de votre modèle : il est plus facile de recopier une formule sur des lignes et des colonnes, plutôt que 43 fois dans une même page.
Un bon design facilitera aussi la lecture par l’utilisateur de vos données et la compréhension des tableaux. Croyez-moi, ça ne sert à rien de passer des heures sur un modèle si personne n’a envie de l’utiliser.

7. Séparez vos données.

Classez les données d’Excel en trois catégories :
– les saisies et les hypothèses,
– les données calculées,
– les résultats à communiquer.

8. Utilisez des couleurs différentes :

Les titres et les valeurs calculées : noir,
Les valeurs saisies : bleu,
Les références : vertes (par exemple une quantité pour fabriquer un produit).

9. Résistez à l’envie de mettre des couleurs inutiles.

Rajouter des couleurs peut rendre votre tableau attractif mais il le sera davantage si on comprend ce que sont les couleurs.

10. Un seul tableau par feuille.

Plusieurs tableaux par feuille rendront la navigation et la compréhension difficiles.

11. Mettez les graphiques sur une feuille séparée.

Sauf si vous avez besoin de ces graphiques lorsque vous construisez le modèle.

12. Mettez les références dans une feuille séparée.

Cela vous évitera d’avoir une feuille au design improbable.

13. Gardez la même périodicité.

Si vous devez construire un budget détaillé par mois pour N+ 1, puis un budget annuel pour N+1 et N+2, construisez votre budget sur 36 mois. Vous pourrez ainsi recopier les formules sur les 36 mois et vous garderez la saisonnalité.
Ce n’est que dans votre tableau “synthèse” ou “reporting” que vous présenterez les résultats avec des dates différentes.

Pensez aux utilisateurs de votre modèle

14. Expliquez toutes les valeurs saisies.

Faites des commentaires, ajoutez une colonne avant les colonnes de chiffres qui doivent être saisis.

15. Indiquez les unités.

Les unités rendront votre tableau plus lisible.

16. Enlevez la grille.

Une fois que vous avez terminé, enlevez la grille, cela rendra votre tableau plus professionnel.

17. Prévoyez une feuille de reporting.

Cette feuille synthétisera tous les résultats et vous pourrez l’imprimer facilement.

18. Pensez au design.

Le design de vos feuilles de saisie doit donner une idée de ce que sera la feuille des résultats.

19. Travaillez sur le modèle des résultats.

Demandez aux utilisateurs des résultats leur participation sur la construction de la feuille des résultats qui leur sera transmise :
- la comprennent-ils facilement ?
– comporte-t-elle tous les éléments dont ils ont besoin ?

20. Faites un diagramme.

Avant de commencer à travailler, faites un diagramme et discutez-en avec les utilisateurs du modèle.
Les données de saisie ==> calculs ==> résultats

Les calculs

21. Construisez une page d’erreur.

Sur cette page, écrivez toutes les erreurs qui pourraient empêcher votre modèle de fonctionner.
Par exemple, si vous devez saisir les comptes de résultat et les bilans des deux dernières années, vérifiez que :
Valeur nette des immobilisations fin de l’Année 1
+ Nouvelles immobilisations
– Amortissements Année 2
– Cessions nettes des immobilisations
= Valeur nette des immobilisations fin de l’année N+2

22. Vérifiez qu’il n’y a pas de références circulaires.

Elles peuvent être faciles à ignorer, Excel vous les indique dans la barre d’état.

23. Faites des calculs intermédiaires.

Vous pourrez masquer la colonne ou la ligne. Ces calculs vous permettront de comprendre ce qui est faux dans votre tableau.

24. Rédigez une table des calculs.

Evidemment, une telle table prend du temps : rédigez-la pour des modèles complexes ou inhabituels. C’est une bonne idée de la rédiger chaque fois que vous utilisez Excel pour calculer des prix de revient.

25. Utilisez une formule par rangée ou par colonne.

Vous gagnerez du temps et vous calculerez la synthèse ailleurs.

Testez votre modèle.

26. testez avec 1

Il est difficile de tester un modèle avec zéro dans toutes les cellules de saisie. Remplacez-les par 1 et voyez si une anomalie apparaît.

27. Utilisez des valeurs extrêmes.

Vous pouvez aussi utiliser une valeur très élevée ou une valeur très faible dans votre tableau. Les résultats vous semblent-ils cohérents ?

28. Testez avec des résultats connus.

Utilisez des résultats déjà connus ou des résultats obtenus avec un autre modèle. Obtenez-vous les mêmes résultats ?

Votre modèle est terminé mais n’oubliez pas :

29. Protégez votre tableau.

Seules les cellules de saisie (bleu) doivent rester non protégées. Protégez toutes les autres.

30. Documentez-le

Sur la première feuille de votre classeur, expliquez à quoi sert le modèle et comment les résultats sont obtenus.

Quelques grands principes à retenir.

31. Evitez d’utiliser les macros.

Si quelque chose se passe mal, il y a très peu de chances que l’utilisateur puisse s’en sortir seul.
Votre modèle ne sera pas transparent pour votre utilisateur puisqu’il ne pourra pas retracer les étapes des calculs. Enfin, les macros ne sont pas toujours portables et votre modèle sur un autre ordinateur peut ne pas fonctionner correctement.
En résumé, si les fonctionnalités d’Excel ne suffisent pas, c’est peut-être le signe qu’Excel n’est pas l’outil adéquat.

32. N’entrez jamais une valeur dans une formule.

Par exemple : Ventes N = Ventes N-1 x 3%
3% est une hypothèse, traitez-la comme telle : créez une cellule pour saisir ce chiffre.

33. Utilisez les raccourcis clavier d’Excel.

N’utilisez pas la souris qui ralentit votre travail, utilisez les raccourcis .

En résumé, si vous devez créer un modèle Excel complexe (pour des coûts de revient par exemple), suivez les mêmes étapes que le ferait un programmeur :
• Rédigez un cahier des charges reprenant les besoins auxquels doit répondre votre modèle,
• Construisez votre tableau en étant rigoureux,
• Testez-le,
• Modifiez-le et maintenez-le.
Cet article est le premier article d’une série sur les tableurs :

  1. Manque de temps ? Supprimez le tableur !
  2. 33 bonnes pratiques pour créer un modèle avec Excel
my finance