excel: comment s'assurer de l'intégrité de ses calculs?

Sophie Marchand, M.Sc., CPA, CGA Experte en modélisation d’affaires Blogue: moncherwatson.wordpress.com Cell.: 514-605-7112 Courriel: [email protected]


Comment s’assurer de l’intégrité de ses calculs dans Excel?

Par Sophie Marchand, M.Sc., CPA, CGA



Qui suis-je?

Sophie MarchandM.Sc. Finance et CPA, CGAModèles financiers dans ExcelTableaux de bord dans ExcelAutres outils de gestion dans… Excel!Blogues:

http://lecfomasque.com http://moncherwatson.wordpress.com

http://lecfomasque.com/

http://moncherwatson.wordpress.com/



Qui suis-je?

Profil LinkedInSophie Marchand

Groupes LinkedInGroupe de partage d’outils de travail

Modélisation financièreCFO du Québec

Excel et VBA – En français

Comptes twitterMon Cher Watson

3

http://www.linkedin.com/in/marchandsophie

http://www.linkedin.com/groups/Groupe-partage-doutils-travail-3849697?trk=myg_ugrp_ovr





http://www.linkedin.com/groups/Mod%C3%A9lisation-Financi%C3%A8re-4624961?trk=myg_ugrp_ovr



http://www.linkedin.com/groups/CFO-Qu%C3%A9bec-4625095?trk=myg_ugrp_ovr

http://www.linkedin.com/groups/Excel-VBA-fran%C3%A7ais-4625047?trk=myg_ugrp_ovr

http://www.linkedin.com/groups/Excel-VBA-fran%C3%A7ais-4625047?trk=myg_ugrp_ovr

https://twitter.com/Mon_cher_watson



Formations offertes

Excel : Intermédiaire et avancéExcel : Tableaux de bordExcel : Modèles financiers budgétairesExcel : PowerPivotFormations privées et formations publiquesWebinairesConsulter notre calendrier: http://lecfomasque.com/activites-a-venir/

4

http://lecfomasque.com/activites-a-venir/



Plan de présentation

IntroductionNormes (standards) dans l’entrepriseMeilleures pratiques d’affairesDocumentation des modèles ExcelValidation de l’entrée de donnéesTotaux de contrôleAutomatisation des calculsProtection des cellules et des fichiersAudit des modèles ExcelFormationConclusion



Introduction

Plus de 80% des modèles financiers et opérationnels élaborés dans Excel comportent des erreurs importantes causant des écarts majeurs dans les résultats

Histoires d’horreur dans Excelhttp://www.eusprig.org/horror-stories.htm

http://www.eusprig.org/horror-stories.htm




Introduction




Introduction

Erreurs les plus souvent rencontréesErreurs dans l’écriture des formules utiliséesFormules liées à des plages de cellules incomplètes ou non localiséesFormules liées à des plages de cellules trop grandesFormules liées à de mauvaises cellulesValeurs entrées comme du texteMauvaises formules



Normes



Normes

Établir des normes à suivre pour l’élaboration des fichiers Excel dans l’entreprisePermettre à tous les employés de comprendre tous les fichiers ExcelPermettre à tous les “développeurs” de modifier adéquatement les fichiers existantsPermettre la pérennité des fichiers Excel après le départ des développeursPermettre à un nouvel employé de se familiariser plus rapidement avec tous les fichiers Excel de l’entreprise



Meilleures pratiques d’affaires

Séparer les différentes sections du modèle (inputs, outputs, plages de données de référence, documentation, etc.)Conférer un format particulier à chaque type de donnée, selon sa vocation (Input, output, cellule liée, etc.), en évitant d’utiliser trop de couleursInsérer des titres de colonnes et des titres de lignesPrésenter des tables faciles à comprendreAfficher les unités de mesure




Article de référence:

Modélisation financière

: Concepts de base

http://www.lecfomasque.com/modelisation-financiere-concepts-de-base/




http://blog.modelcom.com/modelisation-financiere-concepts-de-base/





Modélisation financière dans

Excel: Structure d’un bon

modèle

http://www.lecfomasque.com/modelisation-financiere-excel-structure-dun-bon-modele/








http://blog.modelcom.com/modelisation-financiere-excel-structure-dun-bon-modele/






Excel: Structure d’une page efficace

http://www.lecfomasque.com/modelisation-financiere-dans-excel-structure-dune-page-efficace/









http://blog.modelcom.com/modelisation-financiere-dans-excel-structure-dune-page-efficace/






Excel: Les formats et les styles

http://blog.modelcom.com/modelisation-financiere-dans-excel-les-formats-et-les-styles/













Excel: Les hypothèses

http://blog.modelcom.com/modelisation-financiere-dans-excel-les-hypotheses/













Excel: Analyses de sensibilité

http://www.lecfomasque.com/modelisation-financiere-dans-excel-analyses-de-sensibilite/







http://blog.modelcom.com/modelisation-financiere-dans-excel-analyses-de-sensibilite/




Ne pas cacher d’information, au mieux, utiliser la fonctionnalité “grouper”Sauver fréquemment sous différentes versionsPrévoir l’évolution du modèle (insérer des espaces supplémentaires pour des données similaires)Traiter les fichiers liés avec beaucoup de soin




Un truc par rapport aux fichiers liés

http://moncherwatson.wordpress.com/2013/07/11/excel-fichiers-lies/




Insérer une seule formule par colonne, par ligne, par plage

Insérer une seule fois chaque input dans le modèleNe pas insérer de “hardcoding” dans les formules

ATTENTION AUX TRIANGLES VERTS!



Documentation

Insérer des instructions claires dans le fichierComment circulent les données dans le modèle (Smart Art,

Shapes) – voir menu “Insertion”Comment repérer les cellules d’inputComment faire l’entrée de donnéesComment faire des modifications

Outils pour la documentationTexte avec captures d’écrans (snipping tool, greenshot,

vidéos/tutoriels)Insertion de commentaires dans les cellules (choisir l’option

Afficher)



Documentation



Documentation

ARTICLE DE RÉFÉRENCE:

Ajouter une vidéo dans Excel

http://moncherwatson.wordpress.com/2012/05/16/ajouter-une-video-dans-excel/











Validation entrée de données

Échelles de valeursEntiersDécimaux

ListesDatesTempsLongueur de textePersonnalisé




Article de référence:Astuce

Excel: Validation de données

(Data validation)

http://moncherwatson.wordpress.com/2011/03/14/astuce-excel-validation-de-donnees/







Article de référence:Validation de données

alternatives

http://moncherwatson.wordpress.com/2013/07/15/excel-validation-de-donnees-alternatives/






Totaux de contrôle

Insérer des totaux de contrôleEx.: Actif balance avec Passif dans le bilan

Ajouter du format conditionnel pour faire ressortir les erreursAjouter une feuille de validation du modèle au complet (reprendre tous les totaux de contrôle du modèle et indiquer à l’utilisateur/modélisateur là où il y a des erreurs)



Totaux de contrôle



Totaux de contrôle


Modèles financier budgétaire dans

Excel: Téléchargement

libre

http://www.lecfomasque.com/modele-budgetaire-excel/









http://blog.modelcom.com/modele-budgetaire-excel/



Automatisation/Standardisation

Comprendre les messages d’erreurs dans Excel#N/A, #REF!, #NAME?, #DIV/0!, #NULL!, #VALUE!, #NUM!

Briser les formules complexes en de plus petites formules (calculs intermédiaires)Utiliser des tables (option “mettre sous forme de tableau”)Utiliser des champs nommésUtiliser des fonctions avancées (si/ou, index/equiv, decaler, somme.si.ens., etc.)

http://moncherwatson.wordpress.com/2012/04/05/excel-si-et-ou/



http://moncherwatson.wordpress.com/2011/04/28/fonction-excel-indexequiv-indexmatch/

http://moncherwatson.wordpress.com/2011/04/28/fonction-excel-indexequiv-indexmatch/

http://moncherwatson.wordpress.com/2011/05/24/fonction-excel-decaler-offset/

http://moncherwatson.wordpress.com/2012/07/24/excel-sumifs-countifs/

http://moncherwatson.wordpress.com/2012/07/24/excel-sumifs-countifs/




Article de référence:Excel: Maîtrisez-vous bien les messages d’erreurs?

http://moncherwatson.wordpress.com/2013/07/19/excel-messages-erreur/











Article de référence:Découvrez la magie des tableaux Excel

http://moncherwatson.wordpress.com/2011/11/03/excel-tables/








Article de référence:Astuce Excel: Nommer une plage de cellules

http://moncherwatson.wordpress.com/2011/05/02/astuce-excel-nommer-une-plage-de-cellules/











Protection du fichier

Protection des cellulesProtection des feuillesProtection des fichiers

Article de référence:Excel: Protégez vos

données

http://moncherwatson.wordpress.com/2012/11/08/truc-excel-protegez-vos-donnees/









Audit de fichiers Excel

Logiciels d’audithttp://www.operisanalysiskit.com/http://www.jkp-ads.com/RefTreeAnalyser.asphttp://www.auditexcel.co.za/product/spreadsheet-testi

ng-tool/#%2EUdaowzuW-Sp

http://www.spreadsheetdetective.com/

http://www.operisanalysiskit.com/

http://www.operisanalysiskit.com/

http://www.jkp-ads.com/RefTreeAnalyser.asp

http://www.jkp-ads.com/RefTreeAnalyser.asp

http://www.auditexcel.co.za/product/spreadsheet-testing-tool/



http://www.spreadsheetdetective.com/




Auto-auditF2Fonctionnalités “repérer les dépendants” et “repérer

les antécédents”Fenêtre espion (Watch window)F5 ou “Atteindre” “cellules” (go to special)




Article de référence:Fonctionnalités

Excel pour tester vos formules

http://moncherwatson.wordpress.com/2011/06/21/fonctionnalites-excel-pour-tester-vos-formules/









Article de référence:Excel: Utilisez-vous la

fenêtre espion?

http://moncherwatson.wordpress.com/2012/05/23/excel-fenetre-espion-watch-window/











Article de référence:Astuce Excel : Go to

http://moncherwatson.wordpress.com/2011/03/23/astuce-excel-go-to/






Audit par un tiersCollègue de travailSuperviseurConsultant externe spécialisé

Faites auditer vos modèles financiers gratuitement

http://www.lecfomasque.com/contact/audit-modele-financier/











Formation

Maintenir ses compétences à jour par la formationÉvolution des fonctions et fonctionnalités à travers les

diverses versions d’ExcelFormation Excel sur les meilleures pratiques d’affaires

Menu d’aide Excel

http://moncherwatson.wordpress.com/formations/



Conclusion

Excel est un outil très flexibleIl peut donc être utilisé pour élaborer des outils très performantsEn même temps, mal utilisé, il peut produire des erreurs monumentalesC’est pourquoi il est important de comprendre les types d’erreurs possibles et apprendre à maîtriser l’outil et les meilleures pratiques d’affairesSinon, mieux vaut avoir recours à un logiciel ou un expert pour auditer ses outils Excel



Conclusion

Sophie Marchand, M.Sc., CPA, CGA514-605-7112http://www.moncherwatson.wordpress.comhttp://www.lecfomasque.com

http://www.moncherwatson.wordpress.com/

http://www.lecfomasque.com/

http://www.lecfomasque.com/

excel: comment s'assurer de l'intégrité de ses calculs?

Economy & Finance