Modélisation financière avec Excel

De l'analyse à la prise de décision

La finance d'entreprise est un sujet qui nécessite beaucoup de notions théoriques comptables,
mathématiques et statistiques. Pourtant, la finance est aussi une discipline très pratique, ancrée
dans la réalité quotidienne : il est indispensable d'utiliser des données chiffrées pour éclairer
l'activité présente ou future de l'entreprise, sa rentabilité et aider à la prise de décision.

Le but de cet ouvrage est d'acquérir les compétences nécessaires pour pouvoir appliquer les
principes essentiels de la finance d'entreprise avec un des logiciels les plus utilisés dans le
monde professionnel : Excel. Il a été rédigé avec la version 2016 d'Excel mais convient
également si vous disposez de la version 2019.

Il s'adresse aussi bien aux étudiants en comptabilité–gestion qu'aux professionnels de la

comptabilité, de la gestion et de la finance, ainsi qu'aux managers ayant une activité en lien
avec la finance d'entreprise ou la gestion en général.

La première partie de l'ouvrage permet au lecteur néophyte de se familiariser avec Excel tout
en abordant les bases de la comptabilité et de la finance : l'analyse du compte de résultat et du
bilan.

Par la suite, la modélisation financière proposée s'appuie sur des notions plus avancées : plan
de remboursement, compte de résultat prévisionnel, plan de trésorerie, seuil de rentabilité,
prévisions statistiques (régression linéaire, moyennes mobiles, lissage exponentiel…), analyse
du risque, simulations… L'utilisation d'outils plus complexes d'Excel est alors abordée : outils
statistiques, solveur, utilitaire d'analyse, langage VBA….

Les nombreux exercices réalisés dans Excel n'ont pas seulement pour but d'obtenir des
réponses numériques à des problèmes financiers, mais aussi d'approfondir la compréhension
des concepts étudiés. La difficulté des exercices proposés est progressive, permettant ainsi
aux débutants de se familiariser à la fois avec les notions financières, et avec Excel cependant
au cours de l'ouvrage, des concepts relativement avancés en finance/gestion sont abordés :
autant de compétences concrètes et précieuses dans le monde professionnel d'aujourd'hui.
Avant-propos
Les métiers de la comptabilité, de la gestion et de la finance sont amenés à
évoluer. La blockchain, le machine learning et l’intelligence artificielle ont
un effet disruptif sur ces métiers.
À vrai dire, ce n’est pas un phénomène nouveau, durant ma carrière, j’ai
déjà connu l’évolution des progiciels de gestion intégrée, la
dématérialisation des factures et d’autres évolutions technologiques qui ont
changé le métier du tout au tout.
Cependant, et peu importe le lieu : entreprise, administration, cabinet
comptable, un seul élément est resté absolument récurrent dans mon
quotidien : Microsoft Excel.
Ce livre est né d’une frustration.
Du haut de mes 22 ans, diplôme en poche, je me suis très vite rendu
compte que sur le terrain ma formation manquait cruellement d’expérience
pratique sur les outils informatiques, et particulièrement Excel.
Avec le recul, c’est probablement l’acquisition de ces compétences qui m’a
permis d’avancer dans ma carrière.
D’après un article de Lauren Weber paru le 5 mars 2015 dans le Wall
Street Journal, la clé d’un emploi bien rémunéré serait Microsoft Excel.
Honnêtement, je ne sais pas si la lecture de cet ouvrage vous permettra de
négocier une promotion. Par contre, je suis persuadé que la maîtrise
d’Excel et à long terme l’intérêt pour la science des données que ce type de
logiciel peut éveiller seront un élément fondamental de votre montée en
compétence.

À qui s’adresse ce livre ?

Cet ouvrage s’adresse en priorité aux comptables, chefs comptables ou
directeurs administratifs et financiers qui manipulent des données
comptables et financières dans le but d’en faire une analyse.
Il s’adresse également aux managers, assistants, étudiants en comptabilité -
gestion ou toute personne ayant à la fois un intérêt pour le logiciel Excel et
pour la gestion et l’analyse financière d’entreprise.
Toutes les étapes de l’analyse et des manipulations avec le logiciel Excel
2016 seront décrites pas à pas dans un langage simple.
Le but de ce livre est de pouvoir mettre en œuvre une analyse ou une
modélisation financière cohérente tout en apprenant des fonctionnalités de
base et avancées d’Excel. Il a été rédigé avec la version 2016 d’Excel mais
convient également si vous disposez de la version 2019. Les fichiers
d’exemples utilisés dans ce livre peuvent être téléchargés à partir de la
page Informations générales.

L’analyse financière
La première partie de ce livre est consacrée à l’analyse financière avec
Excel.
L’analyse financière peut être définie comme l’ensemble des méthodes et
outils permettant de porter une appréciation sur la réalité financière d’une
entreprise.
Elle propose d’établir un diagnostic financier à partir des documents
comptables.
Il n’existe pas de méthode d’analyse financière prédéfinie. Toute analyse
dépend à la fois de l’activité de l’entreprise, mais aussi des personnes
auxquelles elle est destinée.
Les différentes parties prenantes de l’entreprise, les dirigeants, les
actionnaires, les banquiers, l’État, les fournisseurs… n’ont pas les mêmes
objectifs ni les mêmes exigences d’informations.
Cependant, il existe des questions cruciales auxquelles toute analyse
financière doit tenter de répondre :
 Est-ce que cette entreprise est solvable ?
 Est-il risqué d’y investir ?
 Quelle est sa rentabilité ?
C’est en se basant sur ces interrogations que nous allons mener une analyse
financière cohérente à partir des documents comptables de synthèse que
sont le compte de résultat et le bilan.
Dans la deuxième partie de cet ouvrage, nous allons aborder le thème de la
modélisation financière avec Excel.
Un modèle est une représentation schématique d’un contexte et des
hypothèses sur lesquels un décideur s’appuie pour évaluer les
conséquences d’une décision.

La modélisation financière est un ensemble de méthodes et d’outils

permettant à partir d’un contexte réel d’établir des prévisions possibles sur
les finances et l’activité de l’entreprise.
Typiquement, une modélisation financière comprend les éléments
suivants :
 des données réelles ou connues
 des scénarii ou hypothèses de prévisions possibles
  des relations entre les différentes variables (par exemple : changer le
montant des ventes va aussi affecter d’autres variables telles que le
bénéfice, les impôts à payer, etc.)
Le but de la modélisation financière est toujours d’apporter une aide à la
prise de décision.
Par exemple, un dirigeant d’entreprise tente de déterminer quel serait son
retour sur investissement lors de l’achat d’une nouvelle machine ; un
entrepreneur essaye de déterminer la viabilité de son projet et son résultat
probable à court terme...

Excel, un outil pour penser avec les

chiffres
À la fin des années 70, l’invention du « Personal Computer » a révolutionné le monde de
l’entreprise.
L’ordinateur, qui était jusque-là réservé à des chercheurs ou à des scientifiques, se
démocratise et fait son entrée aussi bien en entreprise que chez les particuliers.
Petit à petit, les registres comptables papier ont été remplacés par des tableurs électroniques.

Visicalc l’ancêtre d’Excel (1979)

Avec l’augmentation de la puissance des ordinateurs et des programmes, les tableurs avec
leurs calculs automatiques et leurs formules ne sont plus de simples versions électroniques des
registres papier, mais de véritables outils de modélisation, permettant d’établir différents
scénarii, de faire des hypothèses...
En bref, un outil pour penser avec les chiffres.
Aujourd’hui, Excel est devenu un logiciel puissant et flexible. Ce « super tableur » est capable
de synthétiser une quantité importante de données, de faire des graphiques et des calculs
statistiques poussés.
Abordable d’utilisation et peu coûteux, c’est l’un des logiciels les plus utilisés en entreprise et
la grande majorité des modélisations financières sont faites avec ce logiciel.
Cependant, la grande flexibilité qu’il permet peut aussi devenir un piège, surtout lorsque le
modèle devient complexe.
Dans le cadre de cet ouvrage, et avant d’entrer dans le vif du sujet, nous allons étudier
quelques règles clefs pour permettre de construire une analyse claire et éviter certaines
erreurs.

Règles pour faciliter l’utilisation d’un

modèle
Utiliser les zones nommées
Il est possible de rendre les formules nettement plus compréhensibles et
plus gérables en y intégrant des noms au lieu des références de cellules.
Voici un exemple :
La formule en D3 utilise les références des cellules = B3*C3.

À présent, la même formule utilisant cette fois la zone

nommée montant en B3 et la zone nommée taux en C3.
=montant*taux

Vous pouvez définir un nom pour une cellule, une plage de cellules, ou un
tableau.
Cependant, un nom ne doit jamais contenir d’espaces ou de caractères de
ponctuation.
Première méthode
Sélectionnez la cellule ou la plage à nommer.
Saisissez le nom souhaité dans la zone nom située à gauche sous le ruban.
Appuyez sur la touche Entrée .

Ici, nous avons nommé la cellule E2 taux.

Deuxième méthode
Dans l’onglet Formules, groupe Noms définis, cliquez sur Définir un
nom.
La boîte de dialogue Nouveau nom apparaît.

Saisissez le nom souhaité et sélectionnez la cellule ou plage de cellules

correspondante :

Au besoin, vous pouvez sélectionner votre cellule ou plage de cellules en

cliquant sur le bouton Fait référence à. La boîte de dialogue Nouveau
Nom - Fait référence à : apparaît.
Elle permet de sélectionner une cellule ou une plage de cellules.
Sélectionnez les cellules concernées par un cliqué-glissé, puis cliquez de
nouveau sur le bouton Fait référence à pour réafficher toute la boîte de
dialogue.
Cliquez sur OK.
Il est aussi possible de créer, modifier ou supprimer les zones nommées
grâce au gestionnaire de noms.
Dans l’onglet Formules, groupe Noms définis, cliquez sur Gestionnaire
de noms.
Vous visualisez la zone nommée taux, et au besoin vous pouvez la
modifier ou la supprimer.

De même, il est possible de créer une zone nommée à partir du

gestionnaire des noms.
Dans la fenêtre Gestionnaire des noms, cliquez sur Nouveau.
La boîte de dialogue Nouveau nom apparaît :

Saisir les variables une seule (et unique) fois

Lors de la mise en place d’une modélisation, il est courant d’oublier qu’une
valeur a été saisie dans une cellule et de la saisir manuellement à nouveau
dans une autre cellule.
Le problème est que si cette valeur fluctue par la suite, il faudra la modifier
à chaque endroit où elle a été saisie manuellement sous peine d’erreur.
La solution est de faire référence à cette valeur grâce à une formule.
Exemple :
Nous avons saisi 20 % dans la cellule C6, cependant cette cellule devrait
faire référence au taux d’amortissement qui se trouve en E2 (zone
nommée taux).
Si ce taux est amené à être modifié, il faudra aussi modifier manuellement
la saisie en C6.
Il est préférable de faire référence à la cellule E2 (zone nommée taux)
grâce à une formule en C6 : =taux.

Pour utiliser des zones nommées dans une formule, il suffit de saisir les
premières lettres du nom de la zone nommée directement dans la formule,
un menu contextuel apparaît pour vous permettre de la retrouver et de la
sélectionner :

Une deuxième méthode consiste à appuyer sur la touche F3 pendant la

saisie d’une formule.
La boîte de dialogue Coller un nom comportant la liste des zones
nommées apparaît.

Cliquez sur le nom, puis sur OK.

Dans le même ordre d’idée, il n’est pas conseillé d’entrer une valeur
statique dans une formule.
Exemple :
Nous avons saisi en D6 la formule =B6*0,2.
Avec cette formule, si le taux d’amortissement en E2 venait à être modifié,
il faudrait aussi modifier cette formule manuellement, ce qu’il est
préférable d’éviter.

Comme précédemment, la solution est de faire référence à notre zone

nommée taux en E2.
En D6, insérez la formule : = B6*taux

Mettre en place un système d’aide à la

vérification
Il est malheureusement très facile de réaliser des erreurs dans un modèle ou
dans une analyse financière.
Nous allons tenter de les limiter ou du moins de pouvoir les déceler.
Exemple :
Voici un bilan simplifié en format Excel. Ce document est erroné, l’égalité
Actif/Passif n’est pas respectée.

Vous trouverez ce classeur dans les fichiers proposés en téléchargement

avec ce livre (cf. Avant-propos).

Dans ce document, plusieurs cellules ont été nommées.

La cellule D15 fait référence à l’actif net a été nommée total_actif_net.
La cellule G15 fait référence au total du passif a été nommée total_passif.
Dans la cellule B17, nous avons un vérificateur d’erreur qui nous alerte s’il
y a une différence entre le total de l’actif net (cellule D15) et le total du
passif net (cellule G15).
La formule en B17 est :
=SI(total_actif_net-total_passif=0;"ok";total_actif_net-total_passif)

Il s’agit d’une fonction conditionnelle (« SI »). Elle va tester une condition

et renvoyer une valeur si la condition est vraie ou une autre valeur si la
condition est fausse.

Cette formule est structurée de la façon suivante :

=si(test_logique ;valeur_si_vrai ;valeur_si_faux)
Dans notre cas :
Si la différence entre total_actif_net et total_passif est égale à 0,
alors :
La formule renvoie la chaîne de caractères ok (entre guillemets doubles
pour signifier à Excel qu’il s’agit de texte),
sinon :
La formule renvoie le résultat du calcul total_actif_net - total_passif.
 Lorsque vous saisissez une formule, vous pouvez utiliser la boîte de
dialogue Insérer une fonction. Sélectionnez la cellule B17 et cliquez sur
le bouton Insérer une fonction situé dans la barre de formule.
La boîte de dialogue d’aide aux fonctions apparaît. Elle peut être utile pour
mettre en place une formule ou pour vérifier la saisie.
Dans la zone Sélectionnez une fonction, choisissez la fonction SI pour cet
exemple

La cellule B17 contient également une mise en forme conditionnelle. Elle

permet d’afficher le résultat de la formule en couleur rouge si la valeur
contenue en B17 est différente de la chaîne de caractères ok.
Sélectionnez la cellule B17.
Dans l’onglet Accueil, ouvrez la liste du bouton Mise en forme
conditionnelle du groupe Styles.
Cliquez sur l’option Nouvelle règle. La boîte de dialogue Nouvelle règle
de mise en forme apparaît. Sélectionnez la règle Utiliser une formule
pour déterminer pour quelles cellules le format sera appliqué.

Dans la zone Appliquer une mise en forme aux valeurs pour lesquelles
cette formule est vraie, la formule relative à notre mise en forme
conditionnelle est =B17<> "ok".

Le symbole <> dans une formule Excel signifie « différent de ».

Nous devons tester ici si le contenu de notre cellule B17 est différent de la
chaîne de caractères ok.

Si la valeur contenue en B17 est différente de la chaîne de caractères ok,

notre formule de mise en forme conditionnelle va renvoyer la
valeur VRAIE et une mise en forme particulière que nous allons définir va
s’appliquer.
Dans la boîte de dialogue Nouvelle règle de mise en forme, cliquez sur le
bouton Format.
Dans la boîte de dialogue Format de cellule, sélectionnez l’onglet Police,
et dans le champ Couleur, sélectionnez la couleur rouge.

Cliquez sur OK deux fois pour fermer les boîtes de dialogue.

À présent, si notre bilan est équilibré, le contenu de la cellule B17 sera la
chaîne de caractères ok et aucune mise en forme particulière ne sera
appliquée.

À l’inverse, dans le cas d’un bilan déséquilibré, le contenu de la

cellule B17 sera la différence entre le total de l’actif net et le total du
passif. Ce résultat apparaîtra de couleur rouge.

Utiliser des renvois grâce aux liens hypertextes

Dans le but de réaliser une présentation synthétique, nous allons placer le
détail des calculs complexes de la feuille principale de notre modèle dans
une autre feuille.
Cependant pour faciliter la navigation et le contrôle des calculs, il est plus
pratique de mettre en place des liens hypertextes qui vont nous permettre
de créer des renvois.
Exemple :
La cellule D11 de la feuille SIG concerne la consommation de l’exercice
en provenance des tiers.

Feuille SIG
Dans le but de garder notre modèle lisible, nous n’avons pas détaillé ce
calcul sur cette feuille, mais dans une autre feuille que nous avons
nommée calculs_SIG.

Feuille Calculs_SIG
Dans la feuille calculs SIG, en C11, se trouve le détail de la somme de
notre consommation de l’exercice en provenance des tiers.

Notez que la cellule C11 a été nommée au préalable « cons_tiers ».

Nous allons créer un lien hypertexte sur la cellule D11 de notre

feuille SIG vers la zone nommée cons_tiers de notre feuille calculs.
Sélectionnez la cellule D11 de la feuille SIG, puis dans l’onglet Insérer,
cliquez sur Lien.
La boîte de dialogue Insérer un lien hypertexte apparaît :

Dans la zone Lier à, cliquez sur Emplacement dans ce document puis

sélectionnez con_tiers dans la zone Ou sélectionnez un emplacement
dans ce document.

Notre consommation de l’exercice en provenance des tiers apparaît en

texte souligné et bleuté, il suffit de cliquer dessus pour être renvoyé à la
zone nommée cons_tiers de notre feuille calcul.

Une méthode plus rapide est de se placer sur la cellule D11 de la

feuille SIG, de faire un clic droit, puis de sélectionner Lien. Nous arrivons
alors comme précédemment dans la boîte de dialogue Insérer un lien
hypertexte.

Documenter le modèle
Il est souhaitable de documenter autant que possible son modèle dans un
onglet prévu à cet effet. D’expérience, ce qui peut paraître clair aujourd’hui
le sera beaucoup moins dans six mois.
Il est très utile de créer un diagramme pour représenter le processus suivi
pour notre modèle.
Il est possible de créer un diagramme directement avec Excel en utilisant
des objets ou des SmartArts. (Le diagramme présenté ici sera étudié plus
tard dans la partie dédiée à l’étude du bilan fonctionnel).
Exemple de diagramme créé avec des formes dans Excel

Si vous souhaitez approfondir le sujet, je vous conseille d’utiliser un

logiciel dédié à cette utilisation telle que Bizagi BPM Modeler ou bien
encore Microsoft Visio.

Règles pour faciliter l’utilisation d’un

modèle
Utiliser les zones nommées
Il est possible de rendre les formules nettement plus compréhensibles et
plus gérables en y intégrant des noms au lieu des références de cellules.
Voici un exemple :
La formule en D3 utilise les références des cellules = B3*C3.

À présent, la même formule utilisant cette fois la zone

nommée montant en B3 et la zone nommée taux en C3.
=montant*taux

Vous pouvez définir un nom pour une cellule, une plage de cellules, ou un
tableau.
Cependant, un nom ne doit jamais contenir d’espaces ou de caractères de
ponctuation.
Première méthode
Sélectionnez la cellule ou la plage à nommer.
Saisissez le nom souhaité dans la zone nom située à gauche sous le ruban.
Appuyez sur la touche Entrée .
Ici, nous avons nommé la cellule E2 taux.
Deuxième méthode
Dans l’onglet Formules, groupe Noms définis, cliquez sur Définir un
nom.
La boîte de dialogue Nouveau nom apparaît.

Saisissez le nom souhaité et sélectionnez la cellule ou plage de cellules

correspondante :

Au besoin, vous pouvez sélectionner votre cellule ou plage de cellules en

cliquant sur le bouton Fait référence à. La boîte de dialogue Nouveau
Nom - Fait référence à : apparaît.
Elle permet de sélectionner une cellule ou une plage de cellules.

Sélectionnez les cellules concernées par un cliqué-glissé, puis cliquez de

nouveau sur le bouton Fait référence à pour réafficher toute la boîte de
dialogue.
Cliquez sur OK.
Il est aussi possible de créer, modifier ou supprimer les zones nommées
grâce au gestionnaire de noms.
Dans l’onglet Formules, groupe Noms définis, cliquez sur Gestionnaire
de noms.
Vous visualisez la zone nommée taux, et au besoin vous pouvez la
modifier ou la supprimer.

De même, il est possible de créer une zone nommée à partir du

gestionnaire des noms.
Dans la fenêtre Gestionnaire des noms, cliquez sur Nouveau.
La boîte de dialogue Nouveau nom apparaît :
Saisir les variables une seule (et unique) fois
Lors de la mise en place d’une modélisation, il est courant d’oublier qu’une
valeur a été saisie dans une cellule et de la saisir manuellement à nouveau
dans une autre cellule.
Le problème est que si cette valeur fluctue par la suite, il faudra la modifier
à chaque endroit où elle a été saisie manuellement sous peine d’erreur.
La solution est de faire référence à cette valeur grâce à une formule.
Exemple :
Nous avons saisi 20 % dans la cellule C6, cependant cette cellule devrait
faire référence au taux d’amortissement qui se trouve en E2 (zone
nommée taux).
Si ce taux est amené à être modifié, il faudra aussi modifier manuellement
la saisie en C6.

Il est préférable de faire référence à la cellule E2 (zone nommée taux)

grâce à une formule en C6 : =taux.

Pour utiliser des zones nommées dans une formule, il suffit de saisir les
premières lettres du nom de la zone nommée directement dans la formule,
un menu contextuel apparaît pour vous permettre de la retrouver et de la
sélectionner :

Une deuxième méthode consiste à appuyer sur la touche F3 pendant la

saisie d’une formule.
La boîte de dialogue Coller un nom comportant la liste des zones
nommées apparaît.

Cliquez sur le nom, puis sur OK.

Dans le même ordre d’idée, il n’est pas conseillé d’entrer une valeur
statique dans une formule.
Exemple :
Nous avons saisi en D6 la formule =B6*0,2.
Avec cette formule, si le taux d’amortissement en E2 venait à être modifié,
il faudrait aussi modifier cette formule manuellement, ce qu’il est
préférable d’éviter.

Comme précédemment, la solution est de faire référence à notre zone

nommée taux en E2.
En D6, insérez la formule : = B6*taux

Mettre en place un système d’aide à la

vérification
Il est malheureusement très facile de réaliser des erreurs dans un modèle ou
dans une analyse financière.
Nous allons tenter de les limiter ou du moins de pouvoir les déceler.
Exemple :
Voici un bilan simplifié en format Excel. Ce document est erroné, l’égalité
Actif/Passif n’est pas respectée.

Vous trouverez ce classeur dans les fichiers proposés en téléchargement

avec ce livre (cf. Avant-propos).

Dans ce document, plusieurs cellules ont été nommées.

La cellule D15 fait référence à l’actif net a été nommée total_actif_net.
La cellule G15 fait référence au total du passif a été nommée total_passif.
Dans la cellule B17, nous avons un vérificateur d’erreur qui nous alerte s’il
y a une différence entre le total de l’actif net (cellule D15) et le total du
passif net (cellule G15).
La formule en B17 est :
=SI(total_actif_net-total_passif=0;"ok";total_actif_net-total_passif)
Il s’agit d’une fonction conditionnelle (« SI »). Elle va tester une condition
et renvoyer une valeur si la condition est vraie ou une autre valeur si la
condition est fausse.

Cette formule est structurée de la façon suivante :

=si(test_logique ;valeur_si_vrai ;valeur_si_faux)
Dans notre cas :
Si la différence entre total_actif_net et total_passif est égale à 0,
alors :
La formule renvoie la chaîne de caractères ok (entre guillemets doubles
pour signifier à Excel qu’il s’agit de texte),
sinon :
La formule renvoie le résultat du calcul total_actif_net - total_passif.
Lorsque vous saisissez une formule, vous pouvez utiliser la boîte de
dialogue Insérer une fonction. Sélectionnez la cellule B17 et cliquez sur
le bouton Insérer une fonction situé dans la barre de formule.
La boîte de dialogue d’aide aux fonctions apparaît. Elle peut être utile pour
mettre en place une formule ou pour vérifier la saisie.
Dans la zone Sélectionnez une fonction, choisissez la fonction SI pour cet
exemple

La cellule B17 contient également une mise en forme conditionnelle. Elle

permet d’afficher le résultat de la formule en couleur rouge si la valeur
contenue en B17 est différente de la chaîne de caractères ok.
Sélectionnez la cellule B17.
Dans l’onglet Accueil, ouvrez la liste du bouton Mise en forme
conditionnelle du groupe Styles.
Cliquez sur l’option Nouvelle règle. La boîte de dialogue Nouvelle règle
de mise en forme apparaît. Sélectionnez la règle Utiliser une formule
pour déterminer pour quelles cellules le format sera appliqué.
Dans la zone Appliquer une mise en forme aux valeurs pour lesquelles
cette formule est vraie, la formule relative à notre mise en forme
conditionnelle est =B17<> "ok".

Le symbole <> dans une formule Excel signifie « différent de ».

Nous devons tester ici si le contenu de notre cellule B17 est différent de la
chaîne de caractères ok.

Si la valeur contenue en B17 est différente de la chaîne de caractères ok,

notre formule de mise en forme conditionnelle va renvoyer la
valeur VRAIE et une mise en forme particulière que nous allons définir va
s’appliquer.
Dans la boîte de dialogue Nouvelle règle de mise en forme, cliquez sur le
bouton Format.
Dans la boîte de dialogue Format de cellule, sélectionnez l’onglet Police,
et dans le champ Couleur, sélectionnez la couleur rouge.

Cliquez sur OK deux fois pour fermer les boîtes de dialogue.

À présent, si notre bilan est équilibré, le contenu de la cellule B17 sera la
chaîne de caractères ok et aucune mise en forme particulière ne sera
appliquée.

À l’inverse, dans le cas d’un bilan déséquilibré, le contenu de la

cellule B17 sera la différence entre le total de l’actif net et le total du
passif. Ce résultat apparaîtra de couleur rouge.

Utiliser des renvois grâce aux liens hypertextes

Dans le but de réaliser une présentation synthétique, nous allons placer le
détail des calculs complexes de la feuille principale de notre modèle dans
une autre feuille.
Cependant pour faciliter la navigation et le contrôle des calculs, il est plus
pratique de mettre en place des liens hypertextes qui vont nous permettre
de créer des renvois.
Exemple :
La cellule D11 de la feuille SIG concerne la consommation de l’exercice
en provenance des tiers.

Feuille SIG
Dans le but de garder notre modèle lisible, nous n’avons pas détaillé ce
calcul sur cette feuille, mais dans une autre feuille que nous avons
nommée calculs_SIG.

Feuille Calculs_SIG
Dans la feuille calculs SIG, en C11, se trouve le détail de la somme de
notre consommation de l’exercice en provenance des tiers.

Notez que la cellule C11 a été nommée au préalable « cons_tiers ».

Nous allons créer un lien hypertexte sur la cellule D11 de notre

feuille SIG vers la zone nommée cons_tiers de notre feuille calculs.
Sélectionnez la cellule D11 de la feuille SIG, puis dans l’onglet Insérer,
cliquez sur Lien.
La boîte de dialogue Insérer un lien hypertexte apparaît :

Dans la zone Lier à, cliquez sur Emplacement dans ce document puis

sélectionnez con_tiers dans la zone Ou sélectionnez un emplacement
dans ce document.

Notre consommation de l’exercice en provenance des tiers apparaît en

texte souligné et bleuté, il suffit de cliquer dessus pour être renvoyé à la
zone nommée cons_tiers de notre feuille calcul.
Une méthode plus rapide est de se placer sur la cellule D11 de la
feuille SIG, de faire un clic droit, puis de sélectionner Lien. Nous arrivons
alors comme précédemment dans la boîte de dialogue Insérer un lien
hypertexte.

Documenter le modèle
Il est souhaitable de documenter autant que possible son modèle dans un
onglet prévu à cet effet. D’expérience, ce qui peut paraître clair aujourd’hui
le sera beaucoup moins dans six mois.
Il est très utile de créer un diagramme pour représenter le processus suivi
pour notre modèle.
Il est possible de créer un diagramme directement avec Excel en utilisant
des objets ou des SmartArts. (Le diagramme présenté ici sera étudié plus
tard dans la partie dédiée à l’étude du bilan fonctionnel).

Exemple de diagramme créé avec des formes dans Excel

Si vous souhaitez approfondir le sujet, je vous conseille d’utiliser un

logiciel dédié à cette utilisation telle que Bizagi BPM Modeler ou bien
encore Microsoft Visio.

Introduction
La comptabilité traduit les actions qui ont lieu dans le monde des affaires
en un ensemble de chiffres utilisables pour prendre une décision éclairée.
La comptabilité recueille des informations sur le fonctionnement d’une
entreprise, les nombreux obstacles qu’elle doit surmonter et ses
perspectives d’avenir.
Ce chapitre n’est pas un traité sur la comptabilité et la finance, mais il
décrit des outils concrets utilisables pour effectuer une analyse financière
d’entreprise.
Résolument pratique, nous allons simplement effectuer ensemble pas à pas
une analyse financière avec Excel en partant des principaux documents
comptables : le compte de résultat et le bilan.
Démarche d’analyse financière
proposée
Il n’existe pas de démarche d’analyse financière absolue. L’analyse
effectuée dépend à la fois du type d’entreprise, de son contexte, mais aussi
des personnes à qui elle s’adresse.
D’une manière générale, pour créer de la richesse, l’entreprise doit investir
et cet investissement requiert des financements.
Ce processus met en relations diverses parties prenantes.

Chaque partie prenante de l’entreprise aura un objectif différent dans son

analyse.
 Partie prenante Objectif

Co-contractants (Clients, Quelle est la solvabilité de l’entreprise ? Quel est le poids

fournisseurs...) relatif de chacun dans la relation commerciale ?

Direction de l’entreprise Organiser, contrôler, aider à la décision managériale

Établissements bancaires Apprécier le risque de non-remboursement

Établir des bases d’imposition, apprécier la santé du tissu

État
économique

Investisseurs Apprécier le risque et la rentabilité

Quelle est la politique sociale de l’entreprise ? Quelle est

Salariés
la part de la richesse créée reversée au personnel ?

Dans le cadre de cet ouvrage, nous allons suivre une démarche logique et
structurée à partir des principaux documents comptables dans le but
d’établir un diagnostic financier général sur la solvabilité et la rentabilité
de l’entreprise.
L’analyse se fera en quatre temps :
 La détermination des soldes intermédiaires de gestion (S.I.G) et de la
capacité d’autofinancement (C.A.F) à partir du compte de résultat.
 La détermination du bilan fonctionnel à partir du bilan comptable.
 Une analyse par la méthode des ratios.
 Un rapport d’analyse synthétique.
Le contexte de l’entreprise étudiée
Avant de se plonger dans les documents comptables, il est important de
comprendre le contexte et l’environnement de l’entreprise étudiée.
Pour illustrer notre analyse, nous allons étudier une société qui pourrait très
bien être réelle, la société Elena.
La société Elena est une entreprise spécialisée dans le tricot vegan. Elle
existe depuis 5 ans. Pour la majeure partie de son activité, elle réalise elle-
même des vêtements (pulls, bonnets, gants, etc.) qu’elle revend à des
détaillants. Elle possède aussi un magasin où elle vend ses propres produits
ainsi que d’autres produits vegans qu’elle achète en l’état à d’autres
sociétés.
Il s’agit donc d’une entreprise à la fois industrielle et commerciale.
Depuis deux ans, la société Elena a connu une forte croissance et une
expansion à l’international.
Pour faire face à la demande, la société a dû réaliser de nombreux
investissements.
Cependant, malgré cette croissance et un chiffre d’affaires en
augmentation, le résultat de l’entreprise n’a quasiment pas augmenté.
La directrice et le responsable financier s’interrogent, y a-t-il quelque
chose à améliorer dans la gestion financière de la société Elena ?

Introduction
Dans un premier temps, nous allons partir du compte de résultat de
l’entreprise Elena pour créer un autre document, les soldes intermédiaires
de gestion (SIG), puis calculer un indicateur fondamental pour notre
analyse, la capacité d’autofinancement (CAF).
Le schéma d’analyse est le suivant :
La notion de compte de résultat
Le compte de résultat retrace l’activité de l’entreprise. Il contient la
somme des charges et des produits enregistrée au cours de la période
concernée.
La différence entre les charges et les produits détermine le résultat de
l’entreprise.
Le compte de résultat nous renseigne aussi sur la formation de ce résultat.

Même s’il existe des différences minimes entre ces documents, le compte
de résultat est appelé income statement aux États-Unis et profit and loss
income (P&L) en Angleterre.

Deux cas de figure peuvent apparaître :

Si les charges sont inférieures aux produits, l’entreprise dégage un
bénéfice.
À l’inverse, si les charges sont supérieures aux produits, l’entreprise
effectue une perte.

Les éléments du compte de résultat

Le compte de résultat est structuré en fonction de la nature des opérations.
Il distingue les éléments d’exploitations des éléments financiers et des
éléments exceptionnels.

Les opérations d’exploitations

Les opérations d’exploitations contiennent l’ensemble des opérations qui
concourent directement à la réalisation de l’objet de l’entreprise. On y
retrouve les opérations d’achats, de productions, de rémunérations du
personnel, de ventes...
En bref, celles qui correspondent au métier de l’entreprise.
On parle de « cycle » d’exploitation, car ces opérations se déroulent
suivant un ordre précis qui se renouvelle dans le temps.

Le résultat financier
Le résultat financier retrace les choix effectués en matière de financement
(activité d’endettement et de placement) et l’impact du mode de
financement sur le résultat de l’entreprise.
Les charges financières regroupent par exemple les charges d’intérêt sur
emprunt, les intérêts débiteurs (sur découvert bancaire), les pertes de
change, etc.
Les produits financiers regroupent les intérêts de placement, les intérêts de
prêt, les gains de change, etc.
Les opérations exceptionnelles
Les opérations exceptionnelles regroupent les opérations non courantes :
cession d’immobilisation, les dédits et pénalités perçues sur achats et sur
ventes (lorsqu’un créancier dépasse son échéance de paiement et que
l’entreprise facture des pénalités de retard), les amendes fiscales, les dons,
etc.
Le résultat comptable n’est pas équivalent au
flux de trésorerie
C’est une erreur courante de croire que le résultat comptable est équivalent
aux ressources monétaires, voire au solde du compte bancaire.
En réalité, le compte de résultat n’est pas représentatif des seules
opérations monétaires qui ont eu lieu dans l’entreprise.
De fait, il est possible de considérer le compte de résultat comme étant
constitué de deux types d’opérations.
D’une part, les opérations « économiques », c’est-à-dire les opérations qui
donnent lieu à un encaissement ou à un décaissement monétaire réel
(achats, ventes...).
D’autre part, les opérations correspondant à des conventions comptables
dont le but est de retranscrire une image fidèle de la situation de
l’entreprise.
Dans ces dernières, nous retrouvons principalement :
Les amortissements
L’amortissement est une écriture comptable constatant l’usure
économique, régulière et irréversible des immobilisations. L’écriture
comptable d’amortissement ne fait pas intervenir de flux monétaires.
Ainsi, une entreprise qui enregistre un montant élevé de dotations aux
amortissements verra son résultat diminué, mais son compte bancaire
restera inchangé.
Les provisions
Tout comme les amortissements, les provisions retranscrivent aussi une
perte de valeur, et elles n’ont pas de contrepartie monétaire.
Cependant, à l’inverse des amortissements qui ont un caractère régulier et
irréversible, les provisions sont ponctuelles et réversibles.
Ainsi, l’amortissement va constater chaque année la perte régulière et
irréversible d’une machine, alors que la provision va par exemple constater
la baisse d’un titre : rien n’empêche que la valeur de ce titre remonte par la
suite.
Les variations de stocks
Lorsqu’une entreprise achète une marchandise dans le but de la stocker
pour la consommer plus tard, elle ne constituera comptablement une charge
que lors de sa consommation. Elle sera donc intégrée au compte de résultat
à ce moment.
Jusque-là, elle sera comptabilisée en stock, à l’actif du bilan, car jusqu’à sa
consommation elle augmente le patrimoine de l’entreprise.
Exemple : une société achète 100 pulls à 20 €, elle en vend 50 à 30 €.
À partir de là, une erreur possible serait de calculer le résultat de la manière
suivante :
Ventes : 50 x 30 = 1 500 €
Achat : 100 x 20 = 2 000 €
Résultat = - 500 €
En réalité, ce calcul ne correspond pas au résultat comptable de
l’entreprise, mais au flux de trésorerie.
Le calcul du résultat comptable serait le suivant :
Ventes : 50 x 30 = 1 500 €
Achats vendus : 50 x 20 = 1 000 €
Résultat = 500 €
Les 50 pulls restants seront comptabilisés en stocks de marchandises à
l’actif du bilan et non en charges dans le compte de résultat.

Mise en pratique
Voilà le compte de résultat de la société Elena (présentation en liste).
Vous retrouvez le compte de résultat de la société Elena dans
l’onglet cpte_resultat du fichier chapt_1_analyse_vierge ainsi que dans
l’onglet cpte_resultat du fichier chapt_1_analyse.
Le haut du compte de résultat fait effectivement apparaître une
augmentation des ventes de marchandises et de la production entre
l’exercice N et N-1. Parallèlement, le résultat d’exploitation a lui aussi
augmenté au cours de la période.

Les charges financières sont élevées et ont augmenté au cours de l’exercice

N, pesant lourdement sur le résultat courant avant impôts.
 *

Comme annoncé par la direction de la société Elena, malgré

l’augmentation des ventes, le résultat de l’exercice n’a pratiquement pas
bougé entre les deux exercices.

Structure et objectif des soldes intermédiaires de gestion

Pour enrichir notre analyse financière, nous allons modifier le compte de
résultat pour créer un nouveau document de synthèse : les soldes
intermédiaires de gestion (S.I.G).
Les soldes intermédiaires de gestion nous permettront de mesurer
l’activité, la création de valeur et la rentabilité de l’entreprise.
Ils vont aussi permettre de calculer des ratios et éventuellement de
comparer ces ratios entre des entreprises d’un même secteur.
Pour mettre en place ce document, il nous faudra reclasser les éléments du
compte de résultat pour passer des trois catégories (exploitation, financier,
exceptionnelle) à l’élaboration de soldes qui vont nous permettre
d’expliquer étape par étape d’où vient le résultat net comptable de
l’entreprise. En fait, chacun des soldes représente une étape dans la
formation du résultat.
Le calcul des différents soldes se fait en cascade :

Exemple de SIG vierge

Explication et mise en place des différents soldes
Dans l’onglet SIG du fichier chapt_1_SIG_vierge, vous retrouvez un modèle vierge de
tableau des soldes intermédiaires de gestion.
La résolution de cet exercice se trouve dans l’onglet SIG du fichier chapt_1_SIG.
La marge commerciale

La marge commerciale est un solde intermédiaire de gestion utilisé dans les

entreprises de négoce. Il représente la marge réalisée sur les ventes de
marchandises, c’est-à-dire les biens achetés et revendus en l’état.
Ventes de marchandises
- Coût d’achat des marchandises vendues
= Marge commerciale
Mise en place :
Du côté des produits
Nous allons reporter grâce à une formule le montant des ventes de
marchandises de l’année N du compte de résultat.
Ouvrez le fichier chapt_1_SIG_vierge.xlsx.
Dans l’onglet SIG, sélectionnez la cellule B7.
Entrez la formule =cpte_resultat!C5.
Vous pouvez aussi saisir le signe = puis cliquer sur cpte_resultats, puis
sur la cellule C5 et valider par Entrée .
Ensuite, nous allons copier cette formule dans la cellule C7 pour reporter le
montant des ventes de marchandises de l’année N-1 du compte de résultat.
Sélectionnez la cellule B7, faites un clic droit, copiez. Sélectionnez la
cellule C7, faites un clic droit et dans Options de collage, cliquez
sur Formules.
Pour reporter le total des ventes de marchandises de l’exercice N en B9,
saisissez la formule =B7.
Puis comme précédemment, recopiez la formule de la cellule B9 dans la
cellule C9.
Vous pouvez aussi utiliser la poignée de recopie.
Du côté des charges
En D7, le coût d’achat des marchandises vendues est égal au total des
achats de marchandises et des variations de stocks de marchandises.
Pour améliorer la lisibilité de notre modèle, nous allons détailler cet
élément dans l’onglet calcul_SIG.

Dans l’onglet calcul_SIG, séléctionnez la cellule C4 pour reporter le

montant des achats de marchandises N du compte de résultat, saisissez la
formule =cpte_resultat!C14.
Copiez cette formule dans les cellules C5, D4 et D5 pour reporter à la fois
le montant des achats de marchandises N-1 et la variation des stocks N et
N-1.

Nous allons utiliser la fonction somme pour calculer le coût d’achats des
marchandises vendues.
En C6, saisissez =SOMME(C4:C5).
Copiez cette formule dans la cellule D6 pour afficher le coût d’achats des
marchandises vendues N-1.
Dans le but de pouvoir faciliter les contrôles, nous allons créer un lien
hypertexte sur la cellule D7 de l’onglet SIG vers l’onglet calcul_SIG.
Retournez dans l’onglet SIG, sélectionnez la cellule D7, faites un clic
droit, puis choisissez Lien.
Du compte de résultat aux soldes
intermédiaires de gestion
Structure et objectif des soldes intermédiaires
de gestion
Pour enrichir notre analyse financière, nous allons modifier le compte de
résultat pour créer un nouveau document de synthèse : les soldes
intermédiaires de gestion (S.I.G).
Les soldes intermédiaires de gestion nous permettront de mesurer
l’activité, la création de valeur et la rentabilité de l’entreprise.
Ils vont aussi permettre de calculer des ratios et éventuellement de
comparer ces ratios entre des entreprises d’un même secteur.
Pour mettre en place ce document, il nous faudra reclasser les éléments du
compte de résultat pour passer des trois catégories (exploitation, financier,
exceptionnelle) à l’élaboration de soldes qui vont nous permettre
d’expliquer étape par étape d’où vient le résultat net comptable de
l’entreprise. En fait, chacun des soldes représente une étape dans la
formation du résultat.
Le calcul des différents soldes se fait en cascade :

Exemple de SIG vierge

Explication et mise en place des différents

soldes
Dans l’onglet SIG du fichier chapt_1_SIG_vierge, vous retrouvez un
modèle vierge de tableau des soldes intermédiaires de gestion.
La résolution de cet exercice se trouve dans l’onglet SIG du
fichier chapt_1_SIG.
La marge commerciale
La marge commerciale est un solde intermédiaire de gestion utilisé dans les
entreprises de négoce. Il représente la marge réalisée sur les ventes de
marchandises, c’est-à-dire les biens achetés et revendus en l’état.
Ventes de marchandises
- Coût d’achat des marchandises vendues
= Marge commerciale
Mise en place :
Du côté des produits
Nous allons reporter grâce à une formule le montant des ventes de
marchandises de l’année N du compte de résultat.
Ouvrez le fichier chapt_1_SIG_vierge.xlsx.
Dans l’onglet SIG, sélectionnez la cellule B7.
Entrez la formule =cpte_resultat!C5.
Vous pouvez aussi saisir le signe = puis cliquer sur cpte_resultats, puis
sur la cellule C5 et valider par Entrée .
Ensuite, nous allons copier cette formule dans la cellule C7 pour reporter le
montant des ventes de marchandises de l’année N-1 du compte de résultat.
Sélectionnez la cellule B7, faites un clic droit, copiez. Sélectionnez la
cellule C7, faites un clic droit et dans Options de collage, cliquez
sur Formules.
Pour reporter le total des ventes de marchandises de l’exercice N en B9,
saisissez la formule =B7.
Puis comme précédemment, recopiez la formule de la cellule B9 dans la
cellule C9.
Vous pouvez aussi utiliser la poignée de recopie.
Du côté des charges
En D7, le coût d’achat des marchandises vendues est égal au total des
achats de marchandises et des variations de stocks de marchandises.
Pour améliorer la lisibilité de notre modèle, nous allons détailler cet
élément dans l’onglet calcul_SIG.

Dans l’onglet calcul_SIG, séléctionnez la cellule C4 pour reporter le

montant des achats de marchandises N du compte de résultat, saisissez la
formule =cpte_resultat!C14.
Copiez cette formule dans les cellules C5, D4 et D5 pour reporter à la fois
le montant des achats de marchandises N-1 et la variation des stocks N et
N-1.

Nous allons utiliser la fonction somme pour calculer le coût d’achats des
marchandises vendues.
En C6, saisissez =SOMME(C4:C5).
Copiez cette formule dans la cellule D6 pour afficher le coût d’achats des
marchandises vendues N-1.
Dans le but de pouvoir faciliter les contrôles, nous allons créer un lien
hypertexte sur la cellule D7 de l’onglet SIG vers l’onglet calcul_SIG.
Retournez dans l’onglet SIG, sélectionnez la cellule D7, faites un clic
droit, puis choisissez Lien.

La boîte de dialogue Insérer un lien hypertexte s’affiche.

Dans Lier à, sélectionnez Emplacement dans ce document, puis dans la
zone Ou sélectionnez un emplacement dans ce document, sélectionnez
l’onglet calcul_SIG, puis OK.

Dans la cellule E7 de l’onglet SIG, nous allons reporter le calcul du coût d’achat des
marchandises vendues N se trouvant dans l’onglet calcul_SIG. La formule est =calcul_SIG!
C6.
Copiez cette formule dans la cellule F7 pour afficher le calcul du coût d’achat des
marchandises vendues N-1.
Pour reporter le total du coût d’achat des marchandises vendues de l’exercice N
en E9 saisissez =E7.
Copiez cette formule dans la cellule F9 pour reporter le total du coût d’achat des
marchandises vendues de l’exercice N-1.
Calcul de la marge commerciale
En H7, pour calculer la marge commerciale N, saisissez la formule =B9-E9.
Le résultat est 30 000.
Copiez la formule de la cellule H7 dans la cellule I7 pour calculer la marge commerciale de
l’exercice N -1.
Le résultat est 20 000.
Pour finir, nommez la cellule H7 relative à la marge commerciale N Marge_Com_N et la
cellule I7 relative à la marge commerciale N-1 Marge_Com_N_1.
Comme nous l’avons vu dans le chapitre Introduction - section Règles pour faciliter
l’utilisation d’un modèle, pour créer une zone nommée, sélectionnez la cellule ou la plage à
nommer, saisissez le nom souhaité dans la zone nom située à gauche sous le ruban et appuyez
sur la touche Entrée .

La production de l’exercice
Ce solde regroupe l’intégralité de la production de l’entreprise pour la période.
Il distingue la production vendue de la production stockée et de la production immobilisée.
Cette dernière correspond à des biens ayant été produits par l’entreprise pour son propre
usage.
Exemple : une entreprise construit par ses propres moyens une armoire, celle-ci sera
comptabilisée en production immobilisée, c’est-à-dire une production qui devient une
immobilisation.
Production vendue
+ Production immobilisée
+ Production stockée
- Déstockage de production
= Production de l’exercice
Mise en place :
Du côté des produits
Dans la cellule B10 de la feuille SIG, pour calculer la production vendue (de biens et de
services) de l’exercice N, entrez la formule =cpte_resultat!C6+cpte_resultat!C7.
Le résultat est 379 900.
Copiez la formule de la cellule B10 dans la cellule C10 pour calculer la production vendue de
l’exercice N-1.
En B11, pour reporter la production immobilisée N, entrez la formule =cpte_resultat!C9.
Copiez la formule de la cellule B11 dans la cellule C11 pour reporter la production
immobilisée N-1.
Concernant la production stockée ou le déstockage de production, nous allons utiliser une
formule conditionnelle.
Pour l’exercice N, si le montant de la production stockée du compte de résultat est positif
(cellule C8 de l’onglet cpte_resultat), il doit être retranscrit dans la cellule B12 de
l’onglet SIG, c’est-à-dire la cellule relative à la production stockée.
Sinon, la valeur en B12 doit être égale à 0.
Dans la cellule B12, entrez la formule =SI(cpte_resultat!C8>0;cpte_resultat!C8;0).
Le résultat est 8 000.
Recopiez la formule de la cellule B12 dans la cellule C12 pour retranscrire la production
stockée de l’exercice N-1.
Additionnez le total de la production N du côté des produits dans la cellule B13 avec la
fonction somme : =SOMME(B10:B12).
Copiez la formule de la cellule B13 en C13.
Du côté des charges
Si le montant de la production stockée du compte de résultat est négatif, il s’agit d’un
déstockage de production et il doit être reporté du côté des charges de notre tableau des SIG,
dans la cellule E10.
En E10, entrez la formule =SI(cpte_resultat!C8<0;-cpte_resultat!C8;0).
SI(cpte_resultat!C8<0
Si la valeur de la production stockée N du compte de résultat (cpte_resultat!C8) est
inférieure à 0, il s’agit d’un déstockage de production.
;-cpte_resultat!C8
Alors, ce montant doit venir en augmentation des charges du tableau des SIG, nous
devons donc effectuer un changement de signe afin de retourner un montant positif.
;0)
Sinon, la formule renverra la valeur 0.
Recopiez la formule contenue dans la cellule E10 dans la cellule F10 pour reporter la
production stockée de l’exercice N-1.
En E13, reportez le montant du déstockage de production N : =E10.
Enfin, copiez la formule de la cellule E13 dans la cellule F13 pour reporter le déstockage de
production N-1.
Calcul de la production de l’exercice
En H10, saisissez la formule de calcul de la production de l’exercice N : =B13-E13.
Le résultat est 387 900.
Recopiez la formule de la cellule H10 dans la cellule I10 pour retranscrire la production de
l’exercice N-1.
Le résultat est 323 900.
Nommez la cellule H10 Prod_N et la cellule I10 Prod_N_1.
La valeur ajoutée

La valeur ajoutée représente la richesse qui a été créée par l’entreprise. Il

s’agit donc d’un indicateur important dans un diagnostic financier.
Cette valeur ajoutée sera par la suite partagée par les différents acteurs
économiques : le personnel, l’État, les prêteurs et l’entreprise elle-même.

La valeur ajoutée est calculée de la manière suivante :

Marge commerciale
+ Production de l’exercice
- Consommation de l’exercice en provenance des tiers
= Valeur ajoutée
Mise en place :
Du côté des produits
En B14, reportez la marge commerciale N calculée précédemment.
Saisissez =H7.
En B15, reportez la production de l’exercice N, saisissez =H10.
Recopiez ces deux formules en C14 et C15.
Dans la cellule B17, entrez la formule =SOMME(B14:B15), recopiez
cette formule en C17.
Du côté des charges
Concernant le calcul de la consommation de l’exercice en provenance des
tiers, nous allons retracer le détail de ce calcul dans l’onglet calcul_SIG de
notre modèle.

Dans l’onglet calcul_SIG, en C8, pour reporter la somme des achats de matières premières et
autres approvisionnements du compte de résultat N, entrez la formule =cpte_resultat!C16.
À l’aide de la poignée de recopie, recopiez la formule vers le bas jusqu’en C10 puis vers la
droite sur les cellules D8, D9 et D10.
Dans la cellule C11, calculez la consommation de l’exercice en provenance des tiers N avec
la formule =SOMME(C8:C10).
Copiez la formule de la cellule C11 en D11 pour calculer la consommation de l’exercice en
provenance des tiers N-1.
Retournez dans l’onglet SIG et pour reporter le calcul de la consommation de l’exercice en
provenance des tiers N, saisissez en E14 : =calcul_SIG!C11.
Copiez la formule contenue dans la cellule E14 en F14 pour reporter la consommation de
l’exercice en provenance des tiers N-1.
Dans la cellule E17, reportez la consommation de l’exercice N dans la ligne de total : =E14.
Copiez la formule contenue dans la cellule E17 dans la cellule F17 pour reporter la
consommation de l’exercice N-1 dans la ligne de total.
Créer un lien hypertexte pour la consommation de l’exercice en provenance des tiers
Sélectionnez la cellule D14, faites un clic droit puis sélectionnez l’option Lien. La boîte de
dialogue Insérer un lien hypertexte s’affiche à l’écran.
Dans Lier à, sélectionnez Emplacement dans ce document, puis dans la zone Ou
sélectionnez un emplacement dans ce document, sélectionnez l’onglet calcul_SIG,
puis OK.
Calcul de la valeur ajoutée
Dans la cellule H14 pour calculer la valeur ajoutée N, saisissez =B17-E17.
Le résultat est 202 100, nommez la cellule H14 VA_N.
Copiez la formule de la cellule H14 dans la cellule I14 pour calculer la valeur ajoutée N-1.
Le résultat est 159 900. Nommez la cellule I14 VA_N_1.
L’excédent brut d’exploitation (E.B.E)

L’excédent brut d’exploitation traduit la performance économique de

l’entreprise indépendamment de son mode de financement.
Par rapport à la valeur ajoutée, l’EBE prend en compte les charges de
personnel, les impôts et taxes et les subventions d’exploitation.
Valeur ajoutée
+ Subvention d’exploitation
Impôts, taxes et versements assimilés
- Charges de personnel
= Excédent brut d’exploitation
Mise en place :
Du côté des produits
En B18, pour reporter le montant de la valeur ajoutée N, saisissez =H14.
Copiez la formule de la cellule B18 dans la cellule C18 pour reporter la
valeur ajoutée N-1.
Pour les subventions d’exploitation N, en B19, entrez la
formule =cpte_resultat!C10.
Copiez la formule de la cellule B19 dans la cellule C19 pour reporter les
subventions d’exploitation N-1.
En B20, additionnez les éléments précédents pour l’exercice
N =SOMME(B18:B19). Pour l’exercice N-1, recopiez la formule de la
cellule B20 en C20.
Du côté des charges
Dans la cellule E18, reportez le montant des impôts, taxes et versements
assimilés N du compte de résultat : =cpte_resultat!C19.
Copiez la formule de la cellule E18 dans la cellule F18.
Concernant les charges de personnel, nous allons à nouveau retracer le
détail du calcul dans l’onglet calcul_SIG de modèle.

Dans l’onglet calcul_SIG, en C13, pour reporter les salaires et traitements N, entrez la
formule =cpte_resultat!C20.
Copiez la formule de la cellule C13 dans les cellules C14, D13 et D14 pour reporter les
salaires et traitements N-1 et les charges sociales N et N-1.
 Dans la cellule C15, calculez le total des charges de personnel N avec la
formule =SOMME(C13:C14).
Copiez la formule de la cellule C15 en D15 pour calculer le total des charges de personnel N-
1.
Retournez dans l’onglet SIG et pour reporter le calcul des charges de personnel N, entrez la
formule en E19 : =calcul_SIG!C15.
Copiez la formule de la cellule E19 dans la cellule F19 pour reporter le montant des charges
de personnel N-1.
En E20, additionnez les deux éléments précédents pour l’exercice N =SOMME(E18:E19) et
copiez la formule de la cellule E20 en F20 pour l’exercice N-1.
Créer un lien hypertexte pour les charges de personnel
Dans l’onglet SIG, sélectionnez la cellule D19, faites un clic droit, choisissez l’option Lien.
La boîte de dialogue Insérer un lien hypertexte s’affiche.
Dans Lier à, sélectionnez Emplacement dans ce document, puis dans la zone Ou
sélectionnez un emplacement dans ce document, sélectionnez l’onglet calcul_SIG,
puis OK.
Calcul de l’excédent brut d’exploitation (E.B.E)
Pour calculer l’excédent brut d’exploitation N, en H18, saisissez =B20-E20.
Le résultat est 109 300. Nommez la cellule H18 EBE_N.
Recopiez cette formule en I18 pour calculer l’excédent brut d’exploitation N-1.
Le résultat est 70 100. Nommez la cellule I18 EBE_N-1.
Le résultat d’exploitation
Le résultat d’exploitation mesure la capacité de l’entreprise à dégager des ressources du fait
de son activité d’exploitation. Il ne prend pas en compte les éléments financiers ni les
éléments exceptionnels.
Excédent Brut d’Exploitation
+ Reprise sur provisions d’exploitation
- Dotation aux amortissements et provisions
- Autres charges d’exploitation
= Résultat d’exploitation
Mise en place :
Du côté des produits
Dans la cellule B21, pour l’excédent brut d’exploitation N, nous allons mettre en place une
formule conditionnelle.
Si le calcul de l’excédent brut d’exploitation N dans la cellule H18 est positif, il doit être
reporté dans la cellule B21, sinon, la valeur en B21 sera 0.
Saisissez en B21 la formule =SI(H18>0;H18;0).
Copiez cette formule dans la cellule C21 pour reporter l’excédent brut d’exploitation N-1,
l’excédent brut d’exploitation sera uniquement reporté si son montant est positif.
Pour les autres produits N, en B22, entrez la formule =cpte_resultat!C12.
Copiez la formule de la cellule B22 en C22 pour reporter les autres produits N-1.
En B23, la formule relative aux reprises sur amortissements et provisions d’exploitation,
transfert de charges N est : =cpte_resultat!C11.
Copiez la formule de la cellule B23 en C23 pour les reprises sur amortissements et provisions
d’exploitation, transfert de charges N-1.
En B25, additionnez les trois éléments précédents pour l’exercice N avec la
formule =SOMME(B21:B23) et copiez la formule de la cellule B25 en C25 pour l’exercice
N-1.
Du côté des charges
Concernant l’insuffisance brute d’exploitation, dans la cellule E21 nous allons à nouveau
utiliser une formule conditionnelle.
Si le calcul de l’excédent brut d’exploitation N dans la cellule H18 est négatif (il s’agit donc
en fait d’une insuffisance brute d’exploitation), il doit être reporté dans la cellule E21, sinon,
la valeur en E21 sera 0.
 Saisissez en E21 la formule =SI(H18<0;-H18;0).
 Copiez la formule de la cellule E21 en F21 pour reporter l’insuffisance brute
d’exploitation N-1.
 Pour les autres charges N, en E22, entrez la formule : =cpte_resultat!C23.
 Concernant les dotations aux amortissements, aux dépréciations et aux provisions N
dans la cellule E23, entrez la formule =cpte_resultat!C22.
 Copiez les formules des cellules E22 et E23 en F22 et F23 pour obtenir les valeurs N-
1.
 En E25, additionnez les éléments précédents pour l’exercice N avec la
formule =SOMME(E21:E23), copiez cette formule en F25 pour l’exercice N-1.
Calcul du résultat d’exploitation
En H21, pour calculer le résultat d’exploitation N, saisissez =B25-E25.
Le résultat est 70 100. Nommez la cellule H21 R_expl_N.
Copiez la formule de la cellule H21 en I21 pour calculer le résultat brut d’exploitation N-1.
Le résultat est 49 700. Nommez la cellule I21 R_expl_N_1.
Le résultat courant avant impôt
Le résultat courant avant impôt correspond au résultat brut d’exploitation en tenant compte du
résultat financier, mettant ainsi en évidence l’impact de la politique de financement (poids des
charges d’intérêts sur le résultat).
Résultat brut d’exploitation
+ Produits financiers
- Charges financières
=Résultat courant avant impôt
Mise en place :
Nous allons commencer par reporter le montant du résultat brut d’exploitation si ce montant
est positif. Pour cela, nous allons à nouveau utiliser une formule conditionnelle.
Du côté des produits
En B26, saisissez : =SI(H21>0;H21;0).
En B27, concernant les produits financiers N, entrez la formule : =cpte_resultat!C34.
Copiez la formule des cellules B26 et B27 en C26 et C27 afin de reporter les montants pour
N-1.
En B28, additionnez les éléments précédents pour l’exercice N avec la
formule =SOMME(B26:B27), recopiez cette formule en C28 pour l’exercice N-1 à l’aide de
la poignée de recopie.
Du côté des charges
En E26, nous allons reporter le montant du résultat brut d’exploitation N contenu dans la
cellule H21 si son montant est négatif. La formule est : =SI(H21<0;-H21;0).
Notez le changement de signe dans la formule (-H21), en effet, comme ce résultat est placé
dans la section charges des SIG, nous souhaitons un montant positif.
Pour les charges financières N en E27, entrez la formule =cpte_resultat!C39.
Copiez la formule des cellules E26 et E27 en F26 et F27 afin de reporter les montants pour
N-1.
En E28, additionnez les éléments précédents pour l’exercice N avec la
formule =SOMME(E26:E27), copiez la formule de la cellule E28 en F28 pour l’exercice N-
1.
Calcul du résultat courant avant impôt
Dans la cellule H26, saisissez =B28-E28.
Le résultat est 41 300. Nommez la cellule H26 R_courant_N.
Copiez la formule contenue dans la cellule H26 dans la cellule I28 pour calculer le résultat
courant avant impôt N-1.
Le résultat est 37 700. Nommez la cellule I26 R_courant_N_1.
Le résultat exceptionnel
Le résultat exceptionnel reprend l’ensemble des opérations ayant un caractère exceptionnel et
non récurrent, c’est-à-dire non inhérentes à l’activité courante de l’entreprise.
Produits exceptionnels
- Charges exceptionnelles.
= Résultat exceptionnel
Mise en place :
Du côté des produits
En B30, la formule pour reporter le montant des produits exceptionnels N, entrez la
formule: =cpte_resultat!C47.
Copiez la formule contenue dans la cellule B30 dans la cellule C30 pour reporter les produits
exceptionnels N-1.
Reportez le calcul en B31 sur la ligne de total avec la formule =B30 pour l’exercice N.
Copiez la formule contenue dans la cellule B31 en C31 pour reporter les produits
exceptionnels N-1.
Du côté des charges
Pour les charges exceptionnelles N, en E30, entrez la formule =cpte_resultat!C51.
À nouveau, copiez la formule contenue dans la cellule E30 dans la cellule F30 pour afficher
le montant des charges exceptionnelles N-1.
Reportez la formule de la cellule E30 dans la cellule E31 avec la formule =E30.
Copiez la formule de la cellule E31 dans la cellule F31.
Calcul du résultat exceptionnel
En H29, pour calculer le résultat exceptionnel N, saisissez =B31-E31.
Le résultat est -400. Nommez cette cellule R_exp_N.
Copiez la formule contenue dans la cellule H29 dans la cellule I29 pour calculer le
résultat exceptionnel N-1. Le résultat est 400. Nommez cette cellule R_exp_N_1.

Le résultat de l’exercice
Le résultat de l’exercice est le résultat net, après impact des sources de financement (charges
d’intérêts sur endettement), de la participation des salariés et de l’impôt sur les bénéfices.
Résultat courant avant impôt
+/- Résultat exceptionnel
- Impôt sur les bénéfices
- Participation des salariés.
= Résultat de l’exercice
Mise en place :
Du côté des produits
Nous allons à nouveau utiliser une formule conditionnelle pour reporter le résultat courant
avant impôt N si ce dernier est supérieur à 0.
En B32, saisissez =SI(H26>0;H26;0).
En B33, même opération pour le résultat exceptionnel N, saisissez : =SI(H29>0;H29;0).
Copiez les formules contenues dans les cellules B32 et B33 en C32 et C33 pour reporter les
valeurs N-1.
En B36, additionnez les éléments précédents pour l’exercice N avec la
formule =SOMME(B32:B33), recopiez cette formule en C36 pour l’exercice N-1 à l’aide de
la poignée de recopie.
Du côté des charges
En E32, pour afficher le résultat courant avant impôts N si ce dernier est négatif,
saisissez =SI(H26<0;-H26;0).
En E33, même opération pour le résultat exceptionnel N, saisissez =SI(H29<0;-H29;0).
Pour reporter la participation des salariés N du compte de résultat, dans la cellule E34 entrez
la formule =cpte_resultat!C53.
Copiez la formule contenue dans la cellule E34 dans la cellule E35 pour afficher l’impôt sur
les bénéfices N.
Copiez les formules contenues dans les cellules E32 à E35 dans les cellules F32 à F35 pour
afficher les valeurs N-1.
En E36, additionnez les éléments précédents pour l’exercice N avec la
formule =SOMME(E32:E35), copiez cette formule en F36 pour l’exercice N-1.
Calcul du résultat de l’exercice
Pour calculer le résultat de l’exercice N en H32, saisissez =B36-E36.
Le résultat est 36 700. Nommez cette cellule resultat_N.
Copiez la formule de la cellule H32 en I32 pour calculer le résultat de l’exercice N-1. Le
résultat est 34 100. Nommez cette cellule resultat_N_1.
Plus-values et moins-values sur cessions d’éléments d’actif

Pour l’exercice N, la société Elena a vendu un meuble 1 000 €. Sa valeur

comptable, c’est-à-dire son coût d’achat diminué des amortissements est de
500 €.
Elle a donc réalisé une plus-value de 1 000 - 500 = 500 €.
Ces opérations sont détaillées dans l’onglet cpte_resultat dans les
cellules C60 et C61. Elles sont comprises dans les opérations de gestion en
produits exceptionnels et charges exceptionnelles du compte de résultat.
Nous allons les reporter dans nos soldes intermédiaires de gestion ; elles
seront aussi utiles plus tard lorsque nous allons calculer la capacité
d’autofinancement de la société Elena.
Mise en place :
Du côté des produits
Dans l’onglet SIG, sélectionnez la cellule B37, pour reporter les produits
des cessions d’éléments d’actifs N, entrez la formule =cpte_resultat !C60.
Copiez la formule de la cellule B37 en C37 pour les cessions d’éléments
d’actifs N-1.
Du côté des charges
Sélectionnez la cellule E37 et pour reporter la valeur comptable des
éléments d’actifs N, entrez la formule =cpte_resultat!C61.
Copiez la formule de la cellule E37 en F37 pour les cessions d’éléments
d’actifs N-1.
Calcul des plus-values et moins-values sur cession d’éléments d’actifs
En H37, saisissez =B37-E37. Le résultat devrait être 500.
Copiez la formule de la cellule H37 en I37 pour plus-values et moins-
values N-1. Le résultat obtenu est nul.
Calcul de la variation entre les deux exercices
En J7, nous allons calculer la variation de la marge commerciale entre N
et N-1, saisissez =H7-I7.
Exprimée en pourcentage, la formule mathématique d’un calcul de
variation est :
(valeur d’arrivée - valeur de départ)/valeur de départ
Appliqué à notre marge commerciale, en K7, la formule
serait : =(H7-I7)/I7.
Cependant, si le dénominateur de notre formule de variation est égal à 0,
Excel va renvoyer une erreur, en effet, il est impossible de diviser un
nombre par zéro.
Exemple :

Pour éviter cela, nous allons ajouter à notre formule la fonction

SIERREUR. Cette fonction permet de renvoyer une valeur paramétrée si
notre formule renvoie une erreur.
Saisissez en K7 la formule =SIERREUR((H7-I7)/I7;"").
À présent, si notre dénominateur est égal à 0, notre formule ne renverra
rien.
Pour afficher le résultat de ce calcul sous forme de pourcentage
sélectionnez la cellule K7, allez dans l’onglet Accueil, Nombre et appuyez
sur le bouton Style de pourcentage .
Éventuellement, toujours dans Nombre réduisez les décimales en
appuyant sur le bouton

Amélioration de la lisibilité des soldes intermédiaires de

gestion
Pour rendre l’affichage de nos soldes plus lisible et synthétique, nous
allons grouper des colonnes, c’est-à-dire créer un plan.
Sélectionnez les colonnes A à F, puis dans le menu Données,
groupe Plan, cliquez sur Grouper.
Vous obtenez l’affichage suivant au-dessus des colonnes :

En appuyant sur le bouton , les colonnes A à F seront masquées,

rendant ainsi notre tableau des SIG plus synthétique.
En appuyant sur le bouton l’intégralité des colonnes du tableau des SIG
sera affichée.

Mise en place d’un système d’aide à la vérification

Nous allons mettre en place un système qui nous permettra de vérifier les
égalités entre l’onglet des soldes intermédiaires de gestion et le compte de
résultat.
Test d’égalité au niveau des produits
En B40, nous allons calculer le total des produits N des soldes
intermédiaires de gestion.
Entrez la formule =B7+B10+B11+B12+B19+B22+B23+B27+B30-E10.
Son résultat est 470 300.
Notez que dans ce calcul, il faut déduire la cellule E10 correspondante au
déstockage de production. Dans les soldes intermédiaires de gestion, cet
élément est placé dans la colonne des charges, mais en réalité, il s’agit d’un
« produit négatif ».
Dans la cellule B41, nous allons reporter le total des produits du compte de
résultat : =cpte_resultat!C55.
Dans la cellule B42, nous allons tester s’il existe une différence entre les
cellules B40 et B41. En cas d’égalité, il sera renvoyé la chaîne de
caractères ok, sinon la différence entre le total des produits du tableau des
soldes intermédiaires de gestion et le total des produits du compte de
résultat sera affichée.
Dans la cellule B42, saisissez =SI(B40-B41=0;"ok";B40-B41).
Copiez les formules contenues des cellules B40 à B42 dans les
cellules C40 à C42.
Test d’égalité au niveau des charges
En E40, nous allons calculer le total des charges N des soldes
intermédiaires de gestion. La formule
sera =E7+E14+E18+E19+E22+E23+E27+E30+E34+E35. Son
résultat est 433 600.
Dans la cellule E41, reportez le total des produits du compte de
résultat : =cpte_resultat!C56.
Dans la cellule E42, testez l’égalité entre ces deux montants : =SI(E40-
E41=0;"ok";E40-E41).
Copiez les formules contenues des cellules E40 à E42 dans les
cellules F40 à F42.

Retraitements facultatifs
Dans le but de focaliser l’analyse du compte de résultat sur un point de vue
économique, il est éventuellement possible d’opérer certains retraitements
au tableau des soldes intermédiaires de gestion.

Le retraitement du crédit-bail
Les biens immobilisés acquis en crédit-bail peuvent être considérés comme
des immobilisations acquis par de l’endettement. Les redevances de
crédits-bails sont retirées des consommations en provenance des tiers et
ventilées entre deux éléments :
 L’amortissement qui aurait été pratiqué si l’entreprise avait été
propriétaire du bien. Il est affecté aux dotations aux
amortissements.
 Le solde est considéré comme étant des charges d’intérêts et doit
donc être reclassé en charge financière.
Le retraitement du personnel intérimaire
Le personnel extérieur à l’entreprise comptabilisé au compte 621 «
personnel extérieur » et se trouvant dans les consommations de l’exercice
en provenance des tiers peut être reclassé dans les charges de personnel.

La capacité d’autofinancement
La capacité d’autofinancement (C.A.F) est la capacité dont dispose
l’entreprise à dégager par elle-même un excédent de trésorerie.
En d’autres termes, la C.A.F est la capacité dont dispose l’entreprise à
générer par elle-même des ressources financières pour faire face à ses
engagements ; notamment pour réaliser des investissements et/ou pour se
désendetter.
Il s’agit donc d’un élément important lors d’une analyse et probablement le
premier élément qui sera observé par les banquiers et apporteurs de
capitaux.
Pour calculer la C.A.F, il faut exclure les charges et les produits calculés,
c’est-à-dire les charges et produits qui ne donnent pas lieu à un
encaissement ou un à un décaissement réel monétaire tel que les
amortissements ou les provisions.
Seules les opérations ayant une incidence sur la trésorerie immédiate ou
différée doivent être prises en compte.
Il existe deux méthodes pour calculer la C.A.F :
La méthode additive, à partir de l’excédent brut d’exploitation :

La méthode soustractive à partir du résultat net :

Application avec Excel

Pour calculer la capacité d’autofinancement de la société Elena, nous
allons utiliser le compte de résultat ainsi que nos soldes intermédiaires de
gestion calculés précédemment.
Dans l’onglet CAF du fichier chapt_1_SIG_vierge, vous retrouvez un
tableau vierge décrivant le calcul de chacune des deux méthodes. La
résolution de cet exercice se trouve dans l’onglet CAF du
fichier chapt_1_SIG.
La méthode additive, à partir de l’excédent brut d’exploitation
À partir du calcul de l’EBE, nous allons ajouter toutes les charges et les
produits non pris en compte à l’exception des éléments qui ne donnent pas
lieu à des décaissements ou à des encaissements monétaires réels.
Pour les charges :
Dans la cellule C4 de l’onglet CAF, nous allons reporter l’EBE de
l’exercice N en utilisant la zone nommée à cet effet. Vous pouvez
éventuellement rechercher vos zones nommées en appuyant sur la touche
F3 .

Sélectionnez la cellule C4 et entrez la formule =EBE_N.

Même opération en D4 pour l’EBE N-1, saisissez =EBE_N_1.
Pour les autres charges d’exploitation (hors dotations) de l’exercice N,
dans la cellule C5, entrez la formule =cpte_resultat!C23.
Concernant les charges financières N (hors dotation), dans la cellule C6,
entrez la formule =-(cpte_resultat!C36+cpte_resultat!
C37+cpte_resultat!C38).
En C7, pour les charges exceptionnelles N (hors dotations et coûts des
immobilisations cédées), entrez la formule =-(cpte_resultat!
C48+cpte_resultat!C49-cpte_resultat!C61).
Dans la cellule C8, concernant la participation des salariés N, la formule
est =-cpte_resultat!C53.
Enfin, reportez l’impôt sur les bénéfices N, en C9 avec la formule =-
cpte_resultat!C54.
Pour les produits :
Dans la cellule C10, concernant les autres produits d’exploitation N (hors
reprises sur amortissements et provisions), entrez la
formule =cpte_resultat!C12.
Dans la cellule C11, pour les produits financiers N (hors reprises), entrez
la formule =cpte_resultat!C28+cpte_resultat!C29+cpte_resultat!
C30+cpte_resultat!C32+cpte_resultat!C33.
Dans la cellule C12, concernant les produits exceptionnels N (hors
reprises et cessions d’immobilisation), entrez la formule =cpte_resultat!
C44+cpte_resultat!C45-cpte_resultat!C46-cpte_resultat!C60.
Dans la cellule C13, reportez le montant des transferts de charges N,
entrez la formule =cpte_resultat!C59.
Calcul du total de la CAF par la méthode additive
En C14, pour calculer la CAF, il suffit d’additionner les éléments
précédents =SOMME(C4:C13). Le résultat est 75 400.
Sélectionnez les cellules C5 à C14 et copiez les formules contenues dans
ces cellules en D5 à D14. Le résultat est 54 500.
La méthode soustractive, à partir du résultat de l’exercice
Avec la méthode soustractive (aussi appelée « méthode fainéante » !), nous
allons partir du résultat de l’exercice, soustraire les produits non encaissés
et ajouter les charges non décaissées. Cette méthode est plus populaire, car
elle nécessite moins de calculs.
Dans la cellule C18 de l’onglet CAF, nous allons reporter le résultat N.
saisissez =resultat_N.
Dans la cellule D18, nous allons reporter le résultat N-1.
Saisissez =resultat_N_1.
Soustraction des produits non encaissés
En C19, nous allons soustraire les reprises sur amortissements et
provisions N du résultat net. Entrez la formule =-(cpte_resultat!
C11+cpte_resultat!C31+cpte_resultat!C46).
En C20, nous allons retirer du résultat net les produits de cession des actifs
cédés N. Entrez la formule =-cpte_resultat!C60.
Addition des charges non décaissées
En C21, pour ajouter les dotations aux amortissements et provisions N,
entrez la formule =cpte_resultat!C22+cpte_resultat!C35+cpte_resultat!
C50.
En C22, nous allons ajouter la valeur comptable des actifs cédés, entrez la
formule =cpte_resultat!C61.
Notez que nous avons retiré les produits de cessions des actifs cédés (1
000) et ajouté la valeur comptable des actifs cédés (500).
De ce fait, dans le calcul de la CAF, il ne reste que la plus-value réalisée
lors de la cession (1 000 - 500 = 500), c’est-à-dire un produit réellement
encaissé.
Calcul du total de la CAF par méthode soustractive
En C23, pour calculer la CAF, il suffit d’additionner les éléments
précédents avec la formule =SOMME(C18:C22). Le résultat est 75 400.
Nommez la cellule C23 CAF_N.
Sélectionnez les cellules C19 à C23 et copiez les formules contenues dans
ces cellules en D19 à C23. En C23, le résultat de la CAF N-1 est 54 500.
Nommez cette cellule CAF_N_1.
Mise en place d’un système d’aide à la vérification
Nous allons mettre en place un système qui nous permettra de vérifier
l’égalité entre les deux méthodes de calcul de la CAF.
Pour cela, nous allons utiliser une formule conditionnelle. Si la valeur de la
CAF calculée par la méthode additive est égale à la valeur de la CAF
calculée par la méthode soustractive, alors il sera affiché ok, sinon pour
nous aider à déceler l’erreur, nous afficherons la différence entre les deux
méthodes de calcul.
Dans la cellule C25, saisissez =SI(CAF_N=C14;"ok";CAF_N-C14).
Dans la cellule D25, saisissez =SI(CAF_N_1=D14;"ok";CAF_N_1-
D14).
Calcul de la variation de la CAF entre N et N-1
En E4, pour la variation de l’EBE saisissez =C4-D4.
Pour le calcul de la variation en pourcentage, en F4,
saisissez =SIERREUR((C4-D4)/D4;"").
Éventuellement, formatez la cellule F4 en style de pourcentage en allant
dans Accueil, groupe Nombre, bouton Style de pourcentage.
Sélectionnez les cellules E4 et F4, faites un clic droit, choisissez Copier,
sélectionnez les cellules E14 et F14, appuyez et maintenez la touche Ctrl ,
sélectionnez les cellules E18, F18, E23, F23, faites un clic droit,
dans Options de collage sélectionnez Formules.
Documentation et ergonomie du
document
L’onglet Doc du fichier chapt_1_SIG reprend le schéma d’analyse ainsi
que la description de chacun des onglets.
Nous allons ajouter des liens hypertextes permettant d’améliorer la
navigation dans le document.
Dans le schéma d’analyse, sélectionnez la forme correspondant au compte
de résultat, faites un clic droit puis cliquez sur l’option Lien hypertexte.
La boîte de dialogue Insérer un lien hypertexte apparaît à l’écran.
Dans Lier à, sélectionnez Emplacement dans ce document.
Dans la zone Ou sélectionnez un emplacement dans ce document,
choisissez l’onglet cpte_resultat, puis cliquez sur OK.
Répétez cette manipulation avec la forme relative au SIG pour créer un
lien hypertexte vers l’onglet SIG et avec la forme relative à la CAF pour
créer un lien hypertexte vers l’onglet CAF.
Des formes représentant des « flèches de retour » avec l’indication
« sommaire » ont été disposées dans le document pour permettre à
l’utilisateur de revenir vers l’onglet de documentation.

Elles se trouvent en haut à droite des onglets cpte_resultat, SIG, calcul

SIG et CAF.
Placez des liens hypertextes permettant un retour vers l’onglet Doc pour
chacune des flèches.
La notion de bilan comptable
Le bilan est un document de synthèse dans lequel sont regroupés à une date
donnée l’ensemble des ressources dont a disposé l’entreprise, ainsi que
l’ensemble des emplois qu’elle en a fait. Il propose donc de retracer le
patrimoine de l’entreprise.
Le bilan comptable est composé de deux éléments :
À droite, le passif représente les ressources à disposition de l’entreprise.
À gauche, l’actif représente la manière dont ces ressources ont été utilisées.

Un bilan comptable peut se décomposer schématiquement de la manière

suivante :
Les cycles et équilibre financier de
l’entreprise
Le cycle d’investissement
Avant de pouvoir vendre des biens ou des services, une entreprise doit
réaliser des investissements pour acquérir des machines, du mobilier et de
l’immobilier, c’est-à-dire l’ensemble des immobilisations nécessaire à son
activité.
Ces actifs immobilisés, aussi appelés emplois stables doivent être
financés par des ressources stables c’est-à-dire des capitaux apportés par
les actionnaires ou les associés et des emprunts à long terme auprès des
établissements bancaires.
Une entreprise n’investit pas toutes les semaines. La décision
d’investissement est une décision réfléchie à long terme tant en termes de
mode de financement (type d’apport, taux, modalité de remboursement,
durée de remboursement) qu’en termes de retour sur investissement
possible.
À ce titre, il serait hasardeux de financer l’achat d’une grosse machine de
montage avec une facilité bancaire octroyée par le banquier pour un
découvert ponctuel.
Le fonds de roulement est le solde des capitaux permanent restant après
financement de l’actif immobilisé.
Fonds de roulement = Ressources stables - Emplois stables
Le fonds de roulement est donc l’excédent de ressources stables, par
rapport aux emplois stables, qui va pouvoir être utilisé pour financer une
partie du cycle d’exploitation.

Le cycle d’exploitation
Avant de pouvoir livrer et facturer ses clients, l’entreprise doit aussi
acheter des marchandises, des matières premières, des services, c’est-à-dire
réaliser son cycle d’exploitation pour produire.
L’entreprise doit donc engager des dépenses pour réaliser son cycle
d’exploitation et cela avant même de pouvoir être payée par ses clients.
Exemple : Une société engage 250 000 € pour acheter des marchandises et
constituer des stocks ainsi que 50 000 € pour pouvoir livrer ses clients. Elle
paie ses fournisseurs et facture ses clients.
À ce moment, l’entreprise a déboursé 300 000 €, mais les clients n’ont pas
encore réglé leurs factures.
Cette « avance » obligatoire, née du décalage temporel du cycle
d’exploitation, est appelée le besoin en fonds de roulement (B.F.R). Le
besoin en fonds de roulement est l’argent « gelé » par l’entreprise pour
financer son cycle d’exploitation. Ce concept est très important, car de fait,
une société peut très bien faire faillite tout en étant par ailleurs rentable
« sur le papier » : il suffit que les clients paient de plus en plus tard, créant
ainsi une crise de trésorerie entraînant une cessation de paiement.
Le terme anglo-saxon pour désigner le besoin en fonds de roulement est
assez explicite : Working Capital Requirement.
Besoin en fonds de roulement = actif circulant - passif circulant
À l’inverse du fonds de roulement, le besoin en fonds de roulement évolue
de manière rapide. Il est soumis au cycle d’exploitation.
Exemple : un fabricant de glace va avoir un pic d’activité en été. De ce fait,
il va devoir acheter et stocker plus de matière première. Son besoin en
fonds de roulement sera donc plus élevé qu’en hiver.

La trésorerie nette
Si le fonds de roulement est supérieur au besoin en fonds de roulement,
l’entreprise dégage une trésorerie nette positive et pourra faire face à ces
échéances à court terme.
C’est un signe de risque faible d’illiquidité.
Trésorerie nette = fonds de roulement - besoin en fonds de roulement

À l’inverse, si le fonds de roulement est inférieur au besoin en fonds de

roulement, l’entreprise ne pourra pas faire face à ces échéances à court
terme. Un besoin en trésorerie apparaît et il devra être financé par un
découvert bancaire et/ou un concours bancaire courant.
La trésorerie nette peut aussi être calculée en soustrayant la trésorerie
passive (ce que l’entreprise doit) de la trésorerie active (ce dont l’entreprise
dispose).

Quelle est la trésorerie idéale ?

Une trésorerie négative récurrente et importante générera des charges
d’intérêt importantes, voire à terme une cessation de paiement.
À l’inverse, une trésorerie trop importante est synonyme d’argent non
utilisé de manière efficiente.
Il n’y a pas de niveau de trésorerie idéale clé en main.
Elle fluctue en fonction de l’activité, de la gestion des stocks et des
négociations de délais de paiement fournisseurs et clients.
Si la trésorerie est négative ou trop faible, et si cela est possible, il est
opportun de tenter de réduire le besoin en fonds de roulement : augmenter
le délai de paiement aux fournisseurs, optimiser la gestion des stocks.
Une autre solution peut être de réduire le délai de paiements accordé aux
clients.
Le bilan fonctionnel
Si le bilan comptable présente l’actif et le passif de l’entreprise afin de
déterminer le patrimoine de celle-ci, pour mettre en lumière la réalité
économique de l’entreprise, l’analyste financier peut être amené à reclasser
les données du bilan comptable pour créer un nouveau document : le bilan
fonctionnel.
Ce document recense les ressources et les emplois en fonction de leur
participation aux cycles de l’entreprise.
On y retrouve les éléments du cycle d’investissement :
 Les emplois stables, liés au cycle d’investissement : ce sont les actifs
immobilisés.
 Les ressources stables : ce sont les capitaux propres et les emprunts.
Et les éléments du cycle d’exploitation :
 L’actif circulant : les stocks, les créances...
 Le passif circulant : dettes fournisseurs, dettes fiscales et sociales...
Ce document est particulièrement intéressant, car il permet de déterminer
le fonds de roulement, le besoin en fonds de roulement, ainsi que la
trésorerie nette.

À partir de là, nous pourrons calculer le fonds de roulement, le besoin en

fonds de roulement ainsi que la trésorerie nette puisque :
Fonds de roulement = Ressources stables - Emplois stables
Besoin en fonds de roulement = Actif circulant - Passif circulant
Trésorerie nette = fonds de roulement - besoin en fonds de roulement
Ou
Trésorerie nette = trésorerie active - trésorerie passive

Les reclassements à effectuer

À l’actif :

Au passif :

Points particuliers : les écarts de conversion actif et passif

Dans le bilan fonctionnel, les écarts de conversion doivent être neutralisés pour restituer le
coût historique des dettes et des créances du bilan.
Ainsi, les écarts de conversion actif et passif sont à annuler pour permettre au poste concerné
de retrouver sa valeur de départ. Ils vont conduire soit à l’augmentation des créances, soit à la
diminution des dettes fournisseurs, selon la justification des montants.

Exemple : cas d’une dette fournisseur

Augmentation de la dette :
L’entreprise a acheté des matières premières aux USA pour 15 000 $. Ce montant est payé à
90 jours.
Le taux de change au moment de l’achat est de 1 € = 1,35 $.
À l’inventaire comptable, le taux de change est de 1 € = 1,3 $.
La dette a augmenté : il s’agit d’un écart de conversion actif, encore appelé perte latente de
change (compte 476).
En euro, la dette initiale était de 15 000 $ / 1,35 = 11 111, 11 €.
Au moment de l’inventaire, d’après le cours du jour, la dette serait de 15 000 $ / 1,3 = 11
538,46 €.
L’écart de conversion (actif) est de 11 538, 46 - 11 111,11 = 427,35 €.
Dans le bilan fonctionnel, au niveau du passif circulant pour retrouver le coût historique de la
dette, il faut déduire cet écart de conversion.
Nous avons donc :
11 538,46 - 427,35 = 11 111,11
Diminution de la dette :
À l’inverse, si à l’inventaire le taux de change était de 1 € = 1,40 $, un écart de conversion
passif aurait été constaté, il s’agit d’une diminution de dettes soit un gain latent de change
(compte 477).
Dette initiale : 15 000 $ / 1,35 = 11 111,11 €
Montant de la dette au moment de l’inventaire : 15 000 $ / 1,40 = 10 714,29 €
Écart de conversion (passif) = 11 111,11 - 10 714,29 = 396,82 €
Dans le bilan fonctionnel, au niveau du passif circulant, pour retrouver le coût historique de la
dette, il faut ajouter cet écart de conversion.
Nous avons donc :
10 714,29 + 396,82 = 11 111,11 €

Exemple : cas d’une créance client

Augmentation de la créance :
L’entreprise a vendu des fournitures pour 15 000 $ à un client américain.
Le taux de change au moment de l’achat est de 1 € = 1,35 $.
À l’inventaire comptable, le taux de change est de 1 € = 1,3 $.
La valeur de la créance a augmenté : il s’agit d’un écart de conversion
passif, encore appelé gain latent de change.
En euro, la créance initiale était de 15 000 $ / 1,35 = 11 111, 11 €.
Au moment de l’inventaire, d’après le cours du jour, la créance serait de
15 000 $ / 1,3 = 11 538,46 €.
L’écart de conversion (passif) est de 11 538, 46 - 11 111,11 = 427,35 €.
Dans le bilan fonctionnel, au niveau de l’actif circulant pour retrouver le
coût historique de la créance, il faut déduire cet écart de conversion.
Nous avons donc :
11 538,46 - 427,35 = 11 111,11
Diminution de la créance :
À l’inverse, si à l’inventaire le taux de change était de 1 € = 1,40 $, un
écart de conversion actif aurait été constaté, il s’agit d’une diminution de
créance soit une perte latente de change (compte 476).
Créance initiale : 15 000 $ / 1,35 = 11 111,11 €
Montant de la créance au moment de l’inventaire : 15 000 $ / 1,40 = 10
714,29 €
Écart de conversion (actif) = 11 111,11 - 10 714,29 = 396,82 €
Dans le bilan fonctionnel, au niveau de l’actif circulant, pour retrouver le
coût historique de la créance, il faut ajouter cet écart de conversion.
Nous avons donc :
10 714,29 + 396,82 = 11 111,11
Pour résumer, dans le bilan fonctionnel, le retraitement des écarts de
conversion se fait de la manière suivante :
Mise en place du bilan fonctionnel
avec Excel
Dans le fichier chapt_1_bilan_fontionnel_vierge, vous retrouvez les bilans
de l’exercice N et N-1, ainsi qu’un modèle vierge de bilan fonctionnel. La
résolution de cet exercice se trouve dans le
fichier chapt_1_bilan_fontionnel.
Pour la mise en place du bilan fonctionnel dans Excel, nous allons utiliser
des listes déroulantes ainsi que la fonction SOMME.SI.
Les listes déroulantes vont permettre d’affecter à chaque élément du bilan
comptable la rubrique correspondante du bilan fonctionnel.

La fonction SOMME.SI permettra ensuite de sommer les éléments des

listes déroulantes pour mettre en place notre bilan fonctionnel.

Mise en place des listes déroulantes

La première étape consiste à créer une liste qui contient les différentes
catégories du bilan fonctionnel :
Nous allons placer cette liste dans l’onglet liste_bilan_fonctionnel.
Sélectionnez l’onglet liste_bilan_fonctionnel. Les éléments de la liste
déroulante sont déjà saisis dans les cellules B1 à B7 :
Nous allons nommer cette liste :
Sélectionnez les cellules B1 à B7, dans le ruban, allez dans
l’onglet Formules et dans le groupe Noms définis, cliquez sur Depuis
sélection.
Dans la boîte de dialogue Créer des noms à partir de la sélection,
cochez la case Ligne du haut puis OK.

La zone nommée liste_bilan_fonctionnel a été créée.

Insertion des colonnes de reclassement

Nous allons placer nos listes déroulantes dans la colonne B des
onglets bilan_N et bilan_N_1.
Dans l’onglet bilan_N_1, sélectionnez la colonne B, clic droit, Insertion.
Saisissez Reclassement en B2.
Sélectionnez les cellules B3 à B31, dans l’onglet Données, groupe Outils
de données, sélectionnez Validation des données.
La boîte de dialogue Validation de données apparaît.

Dans la liste déroulante Autoriser, sélectionnez Liste.

Dans la zone Source, insérez les éléments de la zone
nommée Liste_bilan_fonctionnel :
Placez-vous dans la zone Source, appuyez sur la touche F3 . La boîte de
dialogue Coller un nom apparaît. Sélectionnez Liste_bilan_fonctionnel,
puis appuyez sur OK.

Insérez une colonne avant la colonne G puis appliquez la liste déroulante

sur les cellules G3 à G34.
Renouvelez les manipulations dans l’onglet bilan_N.
À présent, si vous activez une cellule dans une colonne de reclassement,
une liste déroulante répertoriant les différentes rubriques du bilan
fonctionnel apparaît.

Sélection des éléments de reclassement

Actif, haut du bilan
Pour le haut du bilan, les montants bruts de l’actif immobilisé seront
reportés dans les emplois stables du bilan fonctionnel.
Sélectionnez les reclassements dans la colonne B de
l’onglet bilan_N comme dans la capture ci-dessous :

Actif circulant
Pour l’actif circulant, sélectionnez les éléments de reclassement suivants :

Actif, bas du bilan

Reclassez les charges à répartir sur plusieurs exercices en Emplois stables.
Enfin, les écarts de conversion actif seront reclassés ultérieurement.

Passif, capitaux propres

Reclassez l’ensemble des capitaux propres dans les Ressources stables.
Il conviendra aussi d’y ajouter les amortissements et provisions pour
dépréciations. Ce retraitement sera effectué ultérieurement.
Provisions pour risques et charges
Nous partirons du principe que ces provisions sont justifiées.
Reclassez-les dans le Passif circulant.

Dettes
Reclassez les retraitements concertant les dettes de la manière suivante :

Notez qu’il faudra par la suite retrancher les concours bancaires courants
du montant des Emprunt et dettes auprès des établissements de crédits. Ce
retraitement sera effectué ultérieurement.
Passif, bas du bilan
Sélectionnez le reclassement des concours bancaires courants :

Les écarts de conversion actifs seront retraités ultérieurement, les concours

bancaires courants sont reclassés en trésorerie - passif.
Répétez l’ensemble de ces opérations dans l’onglet bilan_N_1.

Création du bilan fonctionnel et

utilisation de la formule Somme.si
Vous trouverez dans l’onglet bilan_fonctionnel un modèle de bilan
fonctionnel vierge.
Nous allons affecter chaque élément du bilan dans la rubrique
correspondante du bilan fonctionnel grâce à la fonction SOMME.SI.
La fonction SOMME.SI permet d’effectuer une somme en tenant compte
d’un critère.
Calcul des emplois stables N
Dans l’onglet bilan_fonctionnel, sélectionnez la cellule B4, puis cliquez
sur le bouton Insérer une fonction situé dans la barre de formule.
 Dans la zone Sélectionnez une fonction, double-cliquez sur SOMME.SI.
Au niveau de la Plage, sélectionnez les données des listes déroulantes de
la colonne B de l’onglet bilan_N soit bilan_N!B3:B33.
Nous allons copier par la suite cette formule vers le bas et la droite, il est
donc nécessaire de figer les lignes et les colonnes en utilisant le signe $.
Ajoutez le signe $ devant les références des lignes et des colonnes
soit bilan_N!$B$3:$B$33.
Vous pouvez automatiquement figer les lignes et les colonnes en appuyant
sur la touche F4 .
Dans la zone Critère, saisissez A4 puisque cette cellule contient le
texte Emplois stables.
Nous allons copier par la suite cette formule vers le bas et la droite, il est
donc nécessaire de figer les colonnes en utilisant le signe $. En revanche, il
ne faut pas figer les lignes, car la formule doit continuer à faire référence
au Critère qui se trouve dans la colonne A mais sur la même ligne que
l’emplacement de la formule.
Ajoutez le signe $ devant la référence à la colonne soit $A4.
Pour la zone Somme_plage, les éléments à sommer se trouvent dans les
cellules C3:C33 de l’onglet bilan_N soit bilan_N!C3:C33.
À nouveau, ajoutez le signe $ devant les références aux lignes et aux
colonnes soit bilan_N!$C$3:$C$33.
Cliquez sur OK.

Le résultat est 271 200.

Calcul de l’actif circulant N
Dans l’onglet bilan_fonctionnel, copiez la formule de la cellule B4 en B5.
Le résultat est 420 760.
Nous reviendrons sur cette formule par la suite pour y intégrer le
retraitement des écarts de conversions.
Calcul de la trésorerie - actif N
Copiez la formule de la cellule B4 en B6.
Le résultat est 2 150.
Calcul des ressources stables N
Suivant la même logique que le calcul des emplois stables, dans la
cellule E4, la première partie de la formule est =SOMME.SI(bilan_N!
$G$4:$G$34;$D4;bilan_N!$H$4:$H$34).
Cependant, nous devons modifier cette formule pour prendre en compte
deux éléments :
 Ajouter le total des amortissements et provisions, cet élément se
trouve dans la cellule D33 de l’onglet bilan_N.
 Soustraire le montant des concours bancaires courants, cet élément se
trouve dans la cellule H34 de l’onglet bilan_N.
Au final, dans la cellule E4, entrez la formule
suivante : =SOMME.SI(bilan_N!$G$4:G$34;$D4;bilan_N!
$H$4:$H$34)+bilan_N!$D33-bilan_N!$H34
Le résultat est 570 000.
Calcul du passif circulants N
Dans la cellule E5, entrez la formule =SOMME.SI(bilan_N!
$G$4:$G$34;$D5;bilan_N!$H$4:$H$34).
Le résultat est 112 010.
Nous reviendrons sur cette formule par la suite pour intégrer le retraitement
des écarts de conversions.
Calcul de la trésorerie - passif N
Dans la cellule E6, recopiez la formule située
en E5=SOMME.SI(bilan_N!$G$4:$G$34;$D6;bilan_N!$H$4:$H$34).
Le résultat est 12 000.
Calcul des éléments du bilan fonctionnel N-1
Nous allons tout d’abord copier les formules relatives au bilan fonctionnel
N, puis remplacer dans chacune des formules copiées la référence à
l’onglet bilan_N par bilan_N_1.
Au niveau de l’actif :
Dans l’onglet bilan_fonctionnel, sélectionnez la cellule B4 puis faites
glisser la poignée de recopie jusqu’à la cellule B6..
Sélectionnez la cellule C4 puis faites glisser la poignée de recopie jusqu’à
la cellule C6.
Au niveau du passif :
Effectuez la même opération au niveau du passif, copiez les formules
contenues dans les cellules E4:E6 dans les cellules F4:F6.
Remplacement des références à l’onglet bilan_N par bilan_N_1 :
Sélectionnez les cellules C4:C6, appuyez et maintenez la touche Ctrl ,
sélectionnez les cellules F4:F6.
Dans le ruban, dans l’onglet Accueil, groupe Édition, cliquez
sur Rechercher et sélectionner, puis sur Remplacer.
Il est aussi possible d’utiliser le raccourci Ctrl H.
La boîte de dialogue Rechercher et remplacer apparaît à l’écran.
Dans Rechercher, saisissez bilan_N.
Dans Remplacer par saisissez bilan_N_1, puis cliquez sur le
bouton Remplacer tout.
Une boîte de dialogue apparaît à l’écran pour vous avertir des
remplacements effectués.

Le résultat est le suivant :