Editions ENI

Modélisation financière avec Excel

De l’analyse à la prise de décision

Collection
Solutions Business

Table des matières

 Table des matières
1
Avant-propos

Introduction
A. À qui s’adresse ce livre? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
B. L’analyse financière . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
C. Excel, un outil pour penser avec les chiffres. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
D. Règles pour faciliter l’utilisation d’un modèle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
1. Utiliser les zones nommées . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2. Saisir les variables une seule (et unique) fois . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
3. Mettre en place un système d’aide à la vérification. . . . . . . . . . . . . . . . . . . . . . . . 23
4. Utiliser des renvois grâce aux liens hypertextes . . . . . . . . . . . . . . . . . . . . . . . . . . 27
5. Documenter le modèle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

Partie 1
Analyse financière avec Excel

Chapitre 1-0
Méthode d’analyse financière
A. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
B. Démarche d’analyse financière proposée . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
C. Le contexte de l’entreprise étudiée . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

Chapitre 1-1
Compte de résultat, soldes intermédiaires de gestion
et capacité d’autofinancement
A. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
B. La notion de compte de résultat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
1. Les éléments du compte de résultat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
2. Le résultat comptable n’est pas équivalent au flux de trésorerie . . . . . . . . . . . . 44
© Editions ENI - All rights reserved

3. Mise en pratique. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
C. Du compte de résultat aux soldes intermédiaires de gestion . . . . . . . . . . . . . . . . . . 47
1. Structure et objectif des soldes intermédiaires de gestion. . . . . . . . . . . . . . . . . . 47
2. Explication et mise en place des différents soldes. . . . . . . . . . . . . . . . . . . . . . . . . 48
a. La marge commerciale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
b. La production de l’exercice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
c. La valeur ajoutée. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
 Modélisation financière avec Excel
2 De l’analyse à la prise de décision
d. L’excédent brut d’exploitation (E.B.E) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
e. Le résultat d’exploitation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
f. Le résultat courant avant impôt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
g. Le résultat exceptionnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
h. Le résultat de l’exercice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
i. Plus-values et moins-values sur cessions d’éléments d’actif . . . . . . . . . . . . . 61
3. Amélioration de la lisibilité des soldes intermédiaires de gestion. . . . . . . . . . . . 63
4. Mise en place d’un système d’aide à la vérification . . . . . . . . . . . . . . . . . . . . . . . . 64
a. Test d’égalité au niveau des produits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
b. Test d’égalité au niveau des charges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
D. Retraitements facultatifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
1. Le retraitement du crédit-bail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
2. Le retraitement du personnel intérimaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
E. La capacité d’autofinancement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
1. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
a. La méthode additive, à partir de l’excédent brut d’exploitation . . . . . . . . . . 67
b. La méthode soustractive, à partir du résultat de l’exercice . . . . . . . . . . . . . . 68
F. Documentation et ergonomie du document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

Chapitre 1-2
Du bilan au bilan fonctionnel
A. La notion de bilan comptable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
B. Les cycles et équilibre financier de l’entreprise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
1. Le cycle d’investissement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
2. Le cycle d’exploitation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
3. La trésorerie nette . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
4. Quelle est la trésorerie idéale ?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
C. Le bilan fonctionnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
1. Les reclassements à effectuer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
2. Exemple : cas d’une dette fournisseur. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
3. Exemple : cas d’une créance client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
D. Mise en place du bilan fonctionnel avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
1. Mise en place des listes déroulantes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
2. Insertion des colonnes de reclassement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
3. Sélection des éléments de reclassement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
E. Création du bilan fonctionnel et utilisation de la formule Somme.si. . . . . . . . . . . . 87
 Table des matières
3
F. Mise en place du retraitement des écarts de conversion . . . . . . . . . . . . . . . . . . . . . . 91
1. Nommage des cellules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
2. Retraitements des écarts de conversion actif. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
3. Retraitements des écarts de conversion passif . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
4. Copie des formules dans l’onglet bilan_N_1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
5. Report des écarts de conversion dans le bilan fonctionnel . . . . . . . . . . . . . . . . . 92
6. Calcul de la structure financière. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
7. Retraitements facultatifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
G. Documentation et ergonomie du document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94

Chapitre 1-3
Synthèse et analyse par les ratios
A. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
B. Analyse de l’activité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
1. Chiffre d’affaires. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
2. Immobilisations nettes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
3. Excédent brut d'exploitation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
4. La capacité d’autofinancement (C.A.F) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
5. Le résultat de l’exercice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
C. Les ratios de rentabilité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
1. Le taux de rentabilité globale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
2. Le taux de rentabilité financière . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
D. La structure financière. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
E. Les ratios de délais . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
1. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
F. Calcul de la variation entre les deux exercices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
1. Mise en valeur des éléments négatifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104

Chapitre 1-4
Synthèse et présentation des résultats
A. Interprétation des chiffres et commentaire d’analyse . . . . . . . . . . . . . . . . . . . . . . . 109
© Editions ENI - All rights reserved

1. Description de la situation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109

2. Recommandation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
B. Pistes pour une mise en forme et une présentation orale efficace . . . . . . . . . . . . . 110
1. Une image vaut mille mots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
2. Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
a. Mise en forme graphique des données relatives à l’activité . . . . . . . . . . . . . 111
b. Mise en forme graphique des données relatives à la structure financière . . 117
 Modélisation financière avec Excel
4 De l’analyse à la prise de décision

Partie 2
La modélisation financière comme outil de prévision

Chapitre 2-1
Modélisation financière avec Excel
A. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
1. Mise en œuvre d’une modélisation financière avec Excel . . . . . . . . . . . . . . . . . 124
B. Présentation du cas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
C. Mise en forme des hypothèses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
1. Sur le niveau des ventes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
a. Scénarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
b. Recherche du nombre de glaces vendues par jour
selon le scénario choisi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
c. Recherche du nombre de cafés journaliers selon le scénario choisi . . . . . . 128
d. Saisonnalités . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
2. Hypothèse financière. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
a. Sur les prix et les coûts unitaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
b. Calcul des dotations aux amortissements . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
c. Provenance et utilisation des fonds liés au projet . . . . . . . . . . . . . . . . . . . . . 131
D. Plan de remboursement de l’emprunt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
1. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
E. Mise en place du compte de résultat prévisionnel . . . . . . . . . . . . . . . . . . . . . . . . . . 136
1. Calcul du chiffre d’affaires mensuel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
a. Concernant l’activité glaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
b. Concernant l’activité café . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
c. Chiffre d’affaires total . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
2. Calcul du salaire mensuel du serveur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
3. Calcul du loyer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
4. Calcul du coût d’énergie/électricité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
5. Calcul du coût des consommables concernant l’activité glaces . . . . . . . . . . . . . 138
6. Calcul du coût des consommables concernant l’activité café. . . . . . . . . . . . . . . 138
7. Calcul du coût d’exploitation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
8. Calcul de l’excédent brut d’exploitation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
9. Calcul des dotations aux amortissements mensuels du matériel . . . . . . . . . . . 139
10.Intérêt de l’emprunt. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
11.Résultat prévisionnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
 Table des matières
5
F. Le plan de trésorerie prévisionnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
1. Calcul de la trésorerie à l’ouverture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
a. Marchandises stockées . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
b. Coût de la machine à café . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
c. Coût de la machine à glace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
2. Emprunt bancaire. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
3. Épargne personnelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
4. Trésorerie nette à l’ouverture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
5. Calcul de la trésorerie prévisionnelle mensuelle . . . . . . . . . . . . . . . . . . . . . . . . . 144
6. Flux de trésorerie liée à l'exploitation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
a. Résultat prévisionnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
b. Amortissements et provisions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
c. Amortissement de l’emprunt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
7. Calcul de la TVA à décaisser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
a. Calcul de la TVA collectée . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
b. Calcul de la TVA déductible sur immobilisation . . . . . . . . . . . . . . . . . . . . . . 147
c. Calcul de la TVA déductible sur achats de biens et services . . . . . . . . . . . . . 147
d. Calcul de la TVA à décaisser mensuelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
e. Calcul du crédit de TVA mensuel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
f. Situation de TVA : Crédit de TVA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
g. Situation de TVA : TVA à décaisser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
h. Règlement de la TVA à décaisser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
i. Report de la TVA à décaisser dans le plan de trésorerie prévisionnelle . . . 149

Chapitre 2-2
Synthèse des principaux éléments du modèle
A. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
B. Report des principaux éléments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
1. Chiffre d'affaires prévisionnel annuel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
C. Graphiques représentant l’évolution mensuelle de chaque élément . . . . . . . . . . . 155
1. Graphique de l’évolution mensuelle du chiffre d’affaires. . . . . . . . . . . . . . . . . . 155
a. Amélioration du graphique . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
© Editions ENI - All rights reserved

2. Graphique de l’évolution mensuelle du résultat . . . . . . . . . . . . . . . . . . . . . . . . . 161

3. Graphique de l’évolution mensuelle de la trésorerie . . . . . . . . . . . . . . . . . . . . . . 162
D. Interactivité du modèle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
E. Interprétation du modèle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
 Modélisation financière avec Excel
6 De l’analyse à la prise de décision

Chapitre 2-3
Détermination du seuil de rentabilité
A. Introduction au seuil de rentabilité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
1. Méthodes de calcul. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
2. Exemple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
B. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
C. Analyse du risque d’exploitation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
1. La marge de sécurité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
2. L’indice de sécurité. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
3. Le levier opérationnel (ou le levier d’exploitation) . . . . . . . . . . . . . . . . . . . . . . . 172
D. Représentation graphique du seuil de rentabilité . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
1. Calculs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
2. Création du graphique. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
a. Ajout des séries de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
b. Ajout de l’axe des abscisses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
c. Amélioration du graphique. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177

Chapitre 2-4
Outils statistiques de prévisions
A. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
B. La régression linéaire. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
1. Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
2. Calcul de la droite de régression. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
a. Calcul du coefficient directeur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
b. Calcul de l’ordonnée à l’origine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
3. Concepts associés à une régression linéaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
a. La corrélation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
b. Le coefficient de détermination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
C. La régression linéaire simple : application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . 193
1. Première étape : représentation graphique des données . . . . . . . . . . . . . . . . . . 194
2. Deuxième étape : calcul des éléments de la régression. . . . . . . . . . . . . . . . . . . . 196
a. Première méthode : à partir des formules d’Excel . . . . . . . . . . . . . . . . . . . . . 196
b. Deuxième méthode : la méthode graphique. . . . . . . . . . . . . . . . . . . . . . . . . . 198
c. Troisième méthode : utilisation de l’utilitaire d’analyse. . . . . . . . . . . . . . . . 199
3. Interprétation des résultats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
4. La fonction PREVISION.LINEAIRE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
 Table des matières
7
D. La régression linéaire multiple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
1. Concept . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
2. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
E. La régression linéaire avec variables catégorielles . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
1. Concept . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
2. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
a. Première étape : retraitement des données . . . . . . . . . . . . . . . . . . . . . . . . . . 217
b. Deuxième étape : utilisation de l’utilitaire d’analyse d’Excel . . . . . . . . . . . . 219
F. Pour aller plus loin : les régressions non linéaires . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
G. Les moyennes mobiles. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
1. Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
2. Intérêt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
3. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
a. Première méthode : À partir des formules d’Excel . . . . . . . . . . . . . . . . . . . . 228
b. Deuxième méthode : utilisation de l’utilitaire d’analyse. . . . . . . . . . . . . . . . 229
c. Estimation du modèle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
4. Représentation graphique des moyennes mobiles . . . . . . . . . . . . . . . . . . . . . . . 233
H. Les moyennes mobiles pondérées . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
1. Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
2. Évaluation du modèle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
I. Le lissage exponentiel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
1. Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
a. Première étape : définition du coefficient de lissage. . . . . . . . . . . . . . . . . . . 237
b. Deuxième étape : déterminer l’erreur entre les ventes réelles de la dernière
observation connue et la dernière prévision réalisée en juin. . . . . . . . . . . . . . 237
c. Troisième étape : appliquer le coefficient de lissage à l’erreur observée
pour obtenir l’erreur pondérée de juin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
d. Quatrième étape : additionner l’erreur pondérée à la dernière prévision
connue pour obtenir la nouvelle prévision pour le mois de juillet . . . . . . . 237
2. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
3. Détermination du coefficient de lissage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
a. Première étape : le calcul de l’écart absolu moyen . . . . . . . . . . . . . . . . . . . . . 241
© Editions ENI - All rights reserved

b. Deuxième étape : modification des formules du lissage exponentiel

pour pouvoir utiliser le solveur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
4. Troisème étape : utilisation du solveur pour trouver le coefficient
de lissage qui minimise l’écart absolu moyen. . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
J. Travailler avec des saisonnalités . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
1. Les coefficients saisonniers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
2. Prévision et série chronologique saisonnière . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
 Modélisation financière avec Excel
8 De l’analyse à la prise de décision
K. Les nouvelles fonctions d'analyse des séries chronologiques . . . . . . . . . . . . . . . . . 256
1. La fonction PREVISION.ETS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
a. Visualisation de la série chronologique. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
b. Utilisation de la fonction PREVISION.ETS . . . . . . . . . . . . . . . . . . . . . . . . . . 258
2. La fonction PREVISION.ETS.CARACTERESAISONNIER . . . . . . . . . . . . . . . . . 260
3. La fonction PREVISION.ETS.CONFINT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
a. Le concept d’intervalle de confiance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
b. Exemple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
c. Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
4. L’outil Feuille de prévision . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264

Partie 3
Modélisation financière comme outil d’aide à la décision

Chapitre 3-1
Décision, rentabilité et temporalité
A. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
B. Le principe de capitalisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
1. Valeur capitalisée d’une suite d’annuité constante . . . . . . . . . . . . . . . . . . . . . . . 271
2. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
C. La valeur actuelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
1. Valeur actualisée d’une suite d’annuités constantes. . . . . . . . . . . . . . . . . . . . . . 274
2. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
D. La Valeur actuelle nette (VAN). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
1. Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
2. Quelle est la signification du taux d’actualisation ? . . . . . . . . . . . . . . . . . . . . . . 277
3. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
4. Application de la méthode de la valeur actuelle nette
dans le choix d’un projet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
5. Analyse de la sensibilité de la VAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
6. Exemple et application avec Excel : sensibilité à deux variables . . . . . . . . . . . . 285
E. Le taux interne de rentabilité (T.I.R). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
1. Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
2. Biais d’interprétation et erreur courante liée au TIR . . . . . . . . . . . . . . . . . . . . . 289
3. Cas pratique : étude de la rentabilité d’un projet. . . . . . . . . . . . . . . . . . . . . . . . . 292
F. Le délai de récupération du capital . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294
1. Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294
2. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
 Table des matières
9
3. Inconvénient de la méthode du délai de récupération . . . . . . . . . . . . . . . . . . . . 298

Chapitre 3-2
Décision en avenir certain : l'optimisation
A. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
1. Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
2. Cas pratique n° 1 : optimisation du nombre de produits à fabriquer . . . . . . . . 302
3. Cas pratique n° 2 : optimisation du choix des projets. . . . . . . . . . . . . . . . . . . . . 308

Chapitre 3-3
Décision en avenir incertain : arbre de probabilité,
simulation Monte-Carlo
A. La méthode de l’arbre de probabilité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
1. Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
2. Application avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
3. Analyse du risque attaché au projet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
4. Faut-il lancer le projet ? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
B. La simulation Monte-Carlo. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
1. Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
2. Génération des 30 000 expériences. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
3. Espérance de gain et statistiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
4. Détermination de la quantité à commander optimale . . . . . . . . . . . . . . . . . . . . 336
5. Détermination de la distribution du résultat. . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
6. Insertion du graphique dans l’onglet statistiques . . . . . . . . . . . . . . . . . . . . . . . . 352
7. Automatisation de l’actualisation des données . . . . . . . . . . . . . . . . . . . . . . . . . . 353

Conclusion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
© Editions ENI - All rights reserved
 Editions ENI

Gestion comptable avec Excel

(versions 2016 et 2019)

Collection
Solutions Business

Table des matières

 Table des matières
1
Chapitre 1
Introduction
A. À qui s'adresse ce livre ?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
B. Présentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
C. Avertissement pour la partie comptabilité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
D. Comment lire ce livre . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
E. Les macros : un plus mais pas une nécessité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
F. Précisions sur les projets proposés . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
G. Version utilisée . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
H. Téléchargement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Chapitre 2
Les outils à maîtriser avant de commencer
A. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
B. Les références relatives, absolues et mixtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1. Références relatives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
2. Références absolues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
3. Références mixtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
C. Nommer une cellule. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
1. Créer un nom de manière rapide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2. Gestionnaire de noms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
D. La fonction SI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
1. Une question, deux réponses possibles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
2. Une question, plusieurs réponses possibles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
3. Plusieurs questions dans une fonction SI. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
a. Fonction ET. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
b. Fonction OU . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
4. Astuce pour vérifier des fonctions SI imbriquées . . . . . . . . . . . . . . . . . . . . . . . . . 24
a. SI.CONDITIONS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
b. SI.MULTIPLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
E. Évaluation de formule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
F. Onglet Développeur. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
© Editions ENI - All rights reserved

G. Macros et fichiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
1. Enregistrement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
2. Paramétrage des macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
 Gestion comptable avec Excel (versions 2016 et 2019)
2
Chapitre 3
Note de frais automatisée
A. Descriptif de la note de frais à concevoir . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
B. La note de frais vue du côté comptabilité. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
1. Tenir compte de la TVA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
2. Affecter un compte comptable. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
3. Envoyer le fichier informatisé au comptable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
C. Les outils Excel utiles à notre projet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
1. La validation des données : listes de choix, infobulles, contrôle des saisies . . . 37
a. Listes de choix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
b. Contrôle de validité des données saisies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
c. Création d'infobulles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
2. Les commentaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3. Calcul des indemnités kilométriques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
a. La fonction SI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
b. La fonction RECHERCHEV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
4. Regrouper les dépenses par compte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
a. La fonction SOMME.SI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
b. La fonction SOMME.SI.ENS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
5. Protection partielle de la feuille et/ou protection du classeur. . . . . . . . . . . . . . . 41
D. Note de frais pas à pas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
1. Créer des listes déroulantes pour le type de frais . . . . . . . . . . . . . . . . . . . . . . . . . 42
2. Interdire une saisie. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
3. Commentaires et infobulles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
4. Calcul des indemnités kilométriques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
5. La mise en forme conditionnelle pour faire illusion . . . . . . . . . . . . . . . . . . . . . . . 52
6. Saisir plusieurs lignes dans la même cellule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
7. Préparer l'impression. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
8. Automatiser les écritures comptables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
9. Créer les boutons pour imprimer et envoyer par mail . . . . . . . . . . . . . . . . . . . . . 60
a. Créer la macro et le bouton Imprimer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
b. Créer la macro et le bouton Envoyer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
c. Astuce pour envoyer directement le classeur
sans ouverture de la fenêtre message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
10.Mettre en place des protections : feuille et classeur . . . . . . . . . . . . . . . . . . . . . . . 65
a. Préparer les cellules qui doivent rester accessibles . . . . . . . . . . . . . . . . . . . . . 66
b. Protéger la feuille . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
c. Protéger le classeur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
11.Enregistrer un classeur en tant que modèle. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
 Table des matières
3
Chapitre 4
Suivi des factures clients en devises
A. Descriptif du projet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
B. Importer le cours des devises à partir d'Internet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
1. Où trouver ces informations et sous quelle forme ? . . . . . . . . . . . . . . . . . . . . . . . 71
a. Un fichier à télécharger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
b. Un tableau dans la page du site Internet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
2. Importer les données à partir d'un site web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
3. Actualiser les données importées. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
C. Fonctions utilisées pour les calculs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
D. Recopier des formules de calcul . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
1. Recopie manuelle : astuce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
2. Option Excel : étendre les formules d'une plage de données. . . . . . . . . . . . . . . . 87
a. Activer cette option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
b. Contraintes et limites de cette option. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
3. Recopie automatisée par une macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
E. Extraire les factures non réglées en fin d'exercice . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
1. Les filtres avancés . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
a. Exemples de critères courants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
b. Critères calculés . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
c. Limiter le nombre de colonnes à extraire de la liste d'origine . . . . . . . . . . . . 93
d. Automatiser l'extraction des factures par une macro . . . . . . . . . . . . . . . . . . . 93
F. Construction pas à pas sans les macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
1. Importer le cours des devises à partir du Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
2. Récupérer le cours correspondant à la devise et à la date de facturation. . . . . . 96
3. Déterminer les pertes ou les gains de change . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
4. Les écritures comptables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
5. Extraire les factures non payées en fin d'année. . . . . . . . . . . . . . . . . . . . . . . . . . 103
6. Déterminer les plus-values ou les moins-values en fin d'exercice. . . . . . . . . . . 105
7. Remarques sur le résultat final à télécharger . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
G. Construction des macros pas à pas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
1. Macro de recopie automatique des formules . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
a. Macro enregistrée pour s'aider dans la construction du code . . . . . . . . . . . 106
© Editions ENI - All rights reserved

b. Modifier et ajouter du code à la macro enregistrée . . . . . . . . . . . . . . . . . . . . 108

c. Adaptation à la recopie de plusieurs cellules . . . . . . . . . . . . . . . . . . . . . . . . . 111
d. Réduire le nombre de lignes de code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
e. Tester la macro sur de nombreuses données pour éviter les surprises . . . 112
2. Macro d'extraction des factures non payées en fin d'exercice . . . . . . . . . . . . . . 114
a. Utilisation de l’Enregistreur de macro pour débuter. . . . . . . . . . . . . . . . . . . 114
b. Nettoyage du code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
 Gestion comptable avec Excel (versions 2016 et 2019)
4
c. Recopier automatiquement les formules pour chaque facture extraite . . . 118
d. Modifier la date d'exercice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
e. Nommez une feuille d'après le contenu d'une cellule . . . . . . . . . . . . . . . . . . 121

Chapitre 5
Suivi des recettes journalières
A. Descriptif du tableau des recettes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
B. Le tableau des recettes au niveau comptable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
C. Les outils Excel à utiliser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
1. Saisir à partir d'un formulaire. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
2. La fonction Arrondi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
3. La fonction NO.SEMAINE.ISO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
4. Fonction BDSOMME . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
5. Tableaux croisés dynamiques (TCD) et graphique croisé dynamique (GCD) . 130
a. TCD par semaine, par mois, par trimestre . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
b. TCD en %. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
c. Graphique croisé dynamique . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
d. TCD pour consolider les données de plusieurs classeurs
et créer des éléments calculés. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
6. Macro de génération automatique des écritures comptables . . . . . . . . . . . . . . 131
7. Macro : comment créer une fonction de calcul . . . . . . . . . . . . . . . . . . . . . . . . . . 131
D. Suivi des recettes journalières pas à pas sans macro. . . . . . . . . . . . . . . . . . . . . . . . . 132
1. Saisie journalière des recettes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
2. Écritures comptables d'une période donnée . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
3. Suivi des recettes par période avec le tableau croisé dynamique. . . . . . . . . . . . 138
a. TCD par activité, par mois et par trimestre . . . . . . . . . . . . . . . . . . . . . . . . . . 138
b. TCD par semaine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
c. TCD : part de chaque activité en % des recettes totales . . . . . . . . . . . . . . . . 146
d. Graphique croisé dynamique . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
e. TCD basé sur plusieurs classeurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
E. Les macros pas à pas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
1. Macro de génération des écritures comptables . . . . . . . . . . . . . . . . . . . . . . . . . . 163
a. Analyse du travail de la macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
b. Créer la macro enregistrée . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
c. Optimisation du code de la macro enregistrée . . . . . . . . . . . . . . . . . . . . . . . 164
d. Désactiver l'affichage d'un message d'alerte. . . . . . . . . . . . . . . . . . . . . . . . . . 165
e. Créer une boîte de dialogue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
f. Contrôler la saisie de l'utilisateur. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
2. Créer une fonction personnalisée . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
a. Écriture du code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
b. Utilisation de la fonction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
 Table des matières
5
Chapitre 6
Gestion des immobilisations
A. Descriptif du projet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
1. Gestion des immobilisations sans macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
2. Gestion des immobilisations avec macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
B. Rappel des calculs d'amortissements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
1. L'amortissement linéaire. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
a. Calcul du taux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
b. Montant de l'amortissement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
2. L'amortissement dégressif . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
a. Coefficient de majoration et taux dégressif . . . . . . . . . . . . . . . . . . . . . . . . . . 188
b. Début de l'amortissement et prorata de la première annuité . . . . . . . . . . . 188
c. La base de l'amortissement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
d. Les dernières annuités. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
C. Les fonctions financières et leur utilité pour notre projet . . . . . . . . . . . . . . . . . . . . 189
1. AMORLIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
2. AMORLINC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
3. AMORDEGRC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
D. Les fonctions utiles à notre projet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
1. DATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
2. JOURS360. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
3. LIGNE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
4. SI, ET, OU . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
E. Les commandes utiles à notre projet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
1. La mise en forme conditionnelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
2. Les liens hypertextes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
3. Le tableau croisé dynamique avec étiquettes (TCD) . . . . . . . . . . . . . . . . . . . . . . 193
F. Les macros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
G. Gestion des immobilisations pas à pas sans macro. . . . . . . . . . . . . . . . . . . . . . . . . . 193
1. Création du plan d'amortissement linéaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
2. Mise en forme conditionnelle du plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
3. Création du plan d'amortissement dégressif . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
4. Naviguer d'une feuille à l'autre . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
© Editions ENI - All rights reserved

5. Gérer les sorties d'immobilisations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204

a. Cession et amortissement linéaire. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
b. Cession et amortissement dégressif . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
c. Quand la cession intervient après l'amortissement total du bien. . . . . . . . 208
6. Déterminer le montant de la dotation à comptabiliser . . . . . . . . . . . . . . . . . . . 210
a. Création du tableau croisé dynamique . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
b. Astuce pour afficher les dotations par type d'amortissement . . . . . . . . . . . 218
 Gestion comptable avec Excel (versions 2016 et 2019)
6
c. Mise à jour des données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
7. Conclusion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
H. Gestion des immobilisations avec macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
1. Construction du formulaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
a. Validation des données et mise en forme conditionnelle . . . . . . . . . . . . . . 226
b. Création des boutons. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
2. Macro pour réinitialiser le formulaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
3. Macro pour reporter les données dans la liste des immobilisations. . . . . . . . . 231
4. Macro pour créer la fiche immo et le plan d'amortissement . . . . . . . . . . . . . . . 234
a. Création de la fiche immo et recopie des données . . . . . . . . . . . . . . . . . . . . 235
b. Calcul de la Date début amortissement et calcul du taux. . . . . . . . . . . . . . . 235
c. Calcul de la première ligne du plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
d. Calcul des autres lignes du plan d'amortissement. . . . . . . . . . . . . . . . . . . . . 237
5. Macro générale à affecter au bouton Créer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
6. Macro pour gérer la cession . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
7. Affecter une macro à un bouton et créer un onglet personnalisé . . . . . . . . . . . 246

Chapitre 7
Calcul de remboursement d’emprunt
A. À vous de jouer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
B. Descriptif du projet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
1. Les différentes méthodes de calcul de l'emprunt . . . . . . . . . . . . . . . . . . . . . . . . 253
a. Emprunt à amortissement constant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
b. Emprunt à annuité constante . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
C. Quelques fonctions financières . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
1. CUMUL.INTER. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
2. CUMUL.PRINCPER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
3. INTPER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
4. ISPMT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
5. Autres fonctions financières. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
a. PRINCPER. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
b. VPM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
D. Définition du travail attendu : précisions et contraintes imposées . . . . . . . . . . . . 260
1. Une fiche calcul de l'emprunt automatisée sans macro . . . . . . . . . . . . . . . . . . . 260
2. Une fiche de calcul de l'emprunt automatisée avec macro. . . . . . . . . . . . . . . . . 261

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263