Jean-Marc Lagoda

LES FICHES OUTILS f cus

TabLEaUx dE bOrd
et bUdgETS avec ExCEL
61 fiches opérationnelles
61 conseils personnalisés
61 cas pratiques
100 illustrations

Compléments à télécharger
Matrices Excel complètes
LES FICHES OUTILS f cus

TabLEaUx dE bOrd
et bUdgETS avec ExCEL

Pratique et basé sur l’expérience, ce guide opérationnel complet propose 61 fiches, les outils et
les pratiques essentiels d’Excel répondant aux problématiques métier des contrôleurs de gestion, des
managers ou des gestionnaires.

Rechercher et modéliser des données clés de gestion

Calculer un budget de ventes et des frais commerciaux
Calculer et évaluer la rentabilité d’un budget d’investissement
Réaliser un arrêté mensuel des comptes
Mettre en forme et publier un reporting
Créer un tableau de bord opérationnel

les fiches outils: des guides opérationnels coMplets

Les ouvrages de cette collection permettent de découvrir ou d’approfondir un sujet ou une fonction ; la série FOCUS propose
d’explorer un sujet plus précis. Regroupées en modules, leurs fiches pratiques et largement outillées (conseils, cas pratiques,
schémas, tableaux, illustrations…) vous seront rapidement indispensables !

Jean-Marc lagoda est diplômé de Reims Management School, en marketing, RH et finance. Il a exercé pendant près de vingt ans dans le
contrôle de gestion. Il est aujourd’hui consultant et formateur sur le sujet à la Cegos.

Configuration requise :




 

Module 1 découvrir et organiser son espace
de travail
  
  
   
  
  

Module 2 rechercher les données clefs
de gestion et Modéliser
   
  
  

   
   

Module 3 calculer le budget des ventes
et des frais coMMerciaux
   

   
   
 

   

  


Module 4 calculer le budget de production,
d’achat et les frais industriels
   

   
 

   
   
   

Module 5 calculer le budget d’investisseMent
et évaluer la rentabilité
   
   
   
   
   
Module 6 calculer le budget des autres frais
et centres de coût
   
   

   
     

Module 7 calculer le budget de trésorerie
   

   

  

   
   
Module 8 calculer les coûts
   


  
   
   

   

Module 9 réaliser un arrêté Mensuel
des coMptes
   
   

  


   
Module 10 Mettre en forMe et publier
un reporting
   

   
   
   

    

Module 11 créer et Mettre en forMe un tableau
de bord opérationnel
   
   

   

  


   
Module 12 réaliser des études éconoMiques
ad hoc
    

   
   
Module 13 diffuser les inforMations
   
   
   

   
   

LES FICHES OUTILS F CUS
TABLEAUX DE BORD
ET BUDGET AVEC EXCEL
 Groupe Eyrolles
61, bd Saint-Germain
75240 Paris Cedex 05

[Link]

J’exprime mes remerciements à tous ceux qui m’ont aidé à la rédaction de ce livre :
Caroline, Alain, Philippe, Frédéric, Florent et Michel.
À mes parents,
À ma sœur, mes frères,
À mon épouse et mes enfants, pour leur amour, source d’énergie et d’inspiration.

Accompagnement éditorial : Caroline Selmer

En application de la loi du 11 mars 1957, il est interdit de reproduire intégralement ou partiellement

le présent ouvrage, sur quelque support que ce soit, sans autorisation de l’éditeur ou du Centre
français d’exploitation du droit de copie, 20, rue des Grands-Augustins, 75006 Paris.

© Groupe Eyrolles, 2015

ISBN : 978-2-212-56063-3
Jean-Marc Lagoda

LES FICHES OUTILS F CUS

TABLEAUX DE BORD
ET BUDGETS AVEC EXCEL
 Sommaire

Introduction 9

Module 1 Découvrir et organiser son espace de travail 13

Fiche 1 Nommer et classer ses dossiers et fichiers 15

Fiche 2 Auditer et nettoyer les fichiers 18

Fiche 1
3 Changer rapidement de période et de liaisons 21

Fiche 4 Définir une barre d’outils Accès rapide 24

Fiche 1
5 Gérer les incidents : récupérer et sauvegarder des fichiers 26

Module 2 Rechercher les données clefs de gestion et modéliser 29

Fiche 6
1 Importer et mettre en forme des fichiers texte 31

Fiche 7 Trier et filtrer les données 33

Fiche 8
1 Représenter graphiquement une tendance et un Pareto 36

Fiche 9 Identifier des règles de variabilité des coûts 39

Fiche 10
1 Inter-relier des feuilles de calcul d’un même classeur 41

Module 3 Calculer le budget des ventes et des frais commerciaux 43

Fiche 11
1 Rechercher graphiquement une saisonnalité des ventes 45

Fiche 12 Calculer les coefficients de saisonnalité 48

© Groupe Eyrolles

Fiche 13
1 Consolider les ventes dans différentes devises :
utilisation de tables 50

Fiche 14 Calculer les contributions directes par famille de produits 52

5
SOMMAIRE

Fiche 15
1 Mesurer la sensibilité des variables
et calculer le levier opérationnel 55

Module 4 Calculer le budget de production, d’achat et les frais industriels 59

Fiche 16
1 Calculer les besoins de production
avec la fonction Produitmat 61

Fiche 17 Calculer le taux d’utilisation des équipements

et identifier les conséquences 63

Fiche 1
18 Cadencer les achats et valoriser le flux 65

Fiche 19 Calculer les heures productives 67

Fiche 1
20 Calculer la masse salariale des effectifs directs
de production 69

Module 5 Calculer le budget d’investissement et évaluer la rentabilité 73

Fiche 1
21 Présenter un tableau des cashs-flows 75

Fiche 22 Calculer la période de récupération 78

Fiche 1
23 Calculer la VAN et le TRI 81

Fiche 24 Effectuer des mesures de sensibilité 84

Fiche 1
25 Identifier les priorités 86

Module 6 Calculer le budget des autres frais et centres de coût 89

Fiche 26
1 Calculer la masse salariale totale 91

Fiche 27 Calculer les amortissements : règle du prorata temporis 93

Fiche 28
1 Consolider les autres frais 95

Fiche 29 Analyser les causes de variations d’un budget à un autre 97

Module 7 Calculer le budget de trésorerie 101

© Groupe Eyrolles

Fiche 1
30 Calculer les flux en TTC et appliquer le décalage lié
aux conditions de paiement 103

6
 SOMMAIRE

Fiche 31 Consolider les encaissements

et décaissements de chaque mois 105

Fiche 1
32 Identifier les variables d’ajustement majeures 108

Fiche 33 Simuler les options possibles 111

Fiche 1
34 Calculer un escompte bancaire 114

Module 8 Calculer les coûts 118

Fiche 35
1 Répartir les charges indirectes par centres d’analyse 119

Fiche 36 Calculer les coûts unitaires d’unité d’œuvre 122

Fiche 37
1 Calculer le coût des activités et des processus 124

Fiche 38 Affecter les coûts directs et indirects aux produits 127

Fiche 39
1 Simuler les actions d’amélioration sur les coûts
en utilisant les boutons curseurs 129

Module 9 Réaliser un arrêté mensuel des comptes 131

Fiche
1 40 Planifier et suivre l’avancement des travaux 133

Fiche 41 Calculer les provisions de dépréciation

des stocks et des créances clients 135

Fiche
e1 42 Tester la dépréciation (« impairment ») des immobilisations 137

Fiche 43 Procéder à la revue analytique des comptes 139

Module 10 Mettre en forme et publier un reporting 141

Fiche 44
1 Rechercher les principaux écarts dans les tableaux 143

Fiche 45 Définir les formats de cellules 146

Fiche 46
1 Définir les formats de présentation 148

Fiche 47 Utiliser la mise en forme conditionnelle des cellules 150

Fiche 48
1 Calculer les écarts budgétaires par niveau hiérarchique
© Groupe Eyrolles

et en global 152

7
SOMMAIRE

Module 11 Créer et mettre en forme un tableau de bord opérationnel 155

Fiche 49
1 Structurer et sélectionner les indicateurs 157

Fiche 50 Concevoir la page de garde du tableau de bord 159

Fiche 51
1 Concevoir les feuilles de détail du tableau de bord 161

Fiche 52 Insérer des graphiques sparkline,

des liens et des formes automatiques 163

Fiche 53
1 Réaliser les graphiques de simulation 165

Module 12 Réaliser des études économiques ad hoc 167

Fiche 1
54 Calculer un seuil de rentabilité
et mesurer le risque d’exploitation 169

Fiche 55 Créer un scénario 173

Fiche 56
1 Étudier l’opportunité de différentes alternatives 176

Module 13 Diffuser les informations 179

Fiche 1
57 Partager des classeurs Excel 181

Fiche 58 Utiliser les espaces partagés 183

Fiche 1
59 Créer et utiliser des connexions vers d’autres sources 186

Fiche 60 Protéger des données 188

Fiche 1
61 Lier des fichiers Excel avec PowerPoint
pour une présentation 190

Principales fonction Excel 193

Index 197
© Groupe Eyrolles

8
 Introduction

L’utilisation d’un tableur comme Excel fait loppe une stratégie de différenciation par le haut
maintenant partie de la vie courante, pour des (qualité et service) en offrant assistance, forma-
besoins aussi bien personnels que profession- tion et garanties de dépannage.
nels. C’est un outil qui allie, en effet, souplesse Elle est organisée en trois grandes directions
et rapidité d’exécution pour les calculs. Mais ses opérationnelles et une direction fonctionnelle
nombreuses fonctionnalités ne sont que peu ou qui correspondent à des centres de responsabi-
pas utilisées. C’est la raison pour laquelle nous lité financiers. Les directions opérationnelles
avons pensé écrire cet ouvrage répondant au sont la R&D, Supply Chain (industrie et logis-
souci d’efficacité des professionnels. tique) et la direction commerciale. La direction
La structure du livre répond aux problématiques fonctionnelle comprend la DRH, les finances et
métier du contrôleur de gestion et plus large- l’administration.
ment des gestionnaires, à travers l’utilisation du La R&D assure la conception des nouveaux
logiciel tableur Excel de Microsoft. L’organisation modèles et le cahier des charges donné aux
des chapitres s’articule autour des trois temps fournisseurs pour les caméras qu’ils fabriquent.
de la gestion : mesurer le passé, réagir au pré-
La direction industrie et logistique personnalise,
sent, prévoir le futur.
sur un site unique basé en France, les caméras
Nous les illustrons à travers l’histoire d’une achetées aux fournisseurs en déposant des logos
PME : FILM+. à la marque de l’entreprise et en ajoutant aux
emballages client la documentation produit. Elle
LE CONTEXTE DE LA PME
gère les approvisionnements et tous les stocks.
L’entreprise FILM+ commercialise des produits Elle cède sa production aux unités commer-
de grande consommation – des caméras – en ciales à un prix de cession défini.
quatre gammes produit, à la fois pour des parti-
La direction commerciale a en charge les rela-
culiers et des professionnels, via des distribu-
tions avec les distributeurs, la tarification et
teurs nationaux de tailles bien différentes ou des
© Groupe Eyrolles

l’organisation de salons professionnels. Elle gère

filiales de distribution spécialisées à l’étranger.
les actions de formation, de dépannage et de
Elle est implantée sur trois territoires : les terri- garantie vis-à-vis des clients sur chaque terri-
toires français, allemand et brésilien. Elle déve- toire national.

9
INTRODUCTION

d’Excel qu’un gestionnaire est régulièrement

Ladirection support gère l’ensemble des aspects
amené à utiliser. La construction de
administratifs et sociaux de l’entreprise.
macros étant plutôt réservée à des spécialistes,
ORGANISATION DE L’OUVRAGE nous ne la traitons pas ici. Certaines
fonctions appa-raissent dans plusieurs
L’ouvrage est organisé en modules thématiques,
chapitres de manière à vous montrer leur
eux-mêmes subdivisés en fiches pratiques. Les
utilisation dans des finalités variées.
soixante et une fiches sont numérotées de ma-
nière incrémentale. Dans chaque fiche, vous Des raccourcis clavier permettant d’accéder di-
trouverez des copies d’écran de fichiers rectement à certaines fonctions sont indiqués
Excel. Ces derniers figurent les fichiers en téléchar- pour Windows, mais les utilisateurs de
ment et sont référencés par modules. Mac pourront utiliser l’ouvrage sans problème.
Les fiches sont constituées de cinq parties : Vous trouverez à la fin du livre le classement
Q les objectifs visés en gestion par la fonction croisé des principales fonctions Excel utilisées
Excel ; dans chacune des fiches et un index reprenant
les concepts clefs de l’ouvrage.
Q une illustration pratique (liée à notre PME,
FILM+) qui figure dans le fichier Excel réfé- FICHIERS DU ZIP EN TELECHARGEMENT
rencé ;
Tous les chapitres font référence à des fichiers
Q le mode opératoire de la fonctionnalité ;
Excel dans lesquels les données de FILM+ sont
Q des conseils et astuces complémentaires ; utilisées. Vous trouverez l’ensemble de ces
Q un résumé reprenant les points clefs de la fichiers le dossier zip en téléchargement qui
fiche de manière synthétique. accompagne cet ouvrage. N’hésitez pas à les
associer à votre lecture. Les cas pratiques
FONCTIONS EXCEL ABORDÉES
débutent ainsi souvent par la phrase : « À
Nous avons volontairement mêlé dans ce livre partir du zip, ouvrez tel fichier, puis tel
des fonctions de base et des fonctions avancées
onglet » : ces documents illustrent

© Groupe Eyrolles

SCHÉMA DES LIENS ENTRE FICHIERS

10
 INTRODUCTION

en effet pour vous les principales fonctions questions pour affiner sa réponse. Vous pourrez
Excel utiles à un manager et détaillent comment même indiquer si vous êtes satisfait des réponses
les mettre en œuvre. fournies ! Tous les utilisateurs en ligne bénéfi-
Ils sont reliés entre eux selon le schéma ci- cient ainsi de réponses complètes et ciblées.
contre. Par exemple, si vous ne savez plus comment
Il est conseillé d’ouvrir ensemble tous les fichiers figer une cellule et que vous faites appel à l’aide
liés pour une mise à jour fiable. Utilisez-les pour en ligne, Microsoft Excel dresse une liste de
mettre en pratique immédiatement les fonctions réponses possibles : « Figer des lignes et des
abordées dans le texte. colonnes », « Figer des volets »… Vous n’avez
plus qu’à choisir ce qui vous convient.
POINT SUR L’AIDE EN LIGNE Vous pouvez également trouver l’assistance
Il peut arriver que l’on ne se souvienne plus du Microsoft sur le Web à l’adresse suivante : http://
chemin de commandes pour réaliser une opéra- [Link]/ph/918/fr-fr. Trois mo-
tion particulière. Excel vous facilite la vie avec dalités sont proposées (f illustration ci-
une assistance complète. Vous obtiendrez l’aide dessous).
en ligne depuis Excel avec la touche F1 (il vous
Pensez également à consulter les nombreux
faudra être connecté à Internet).
forums sur le Web ou les démos sur YouTube.
Dans la boîte Recherche du site Microsoft Office
sur lequel vous serez dirigé, indiquez la fonction
recherchée ou décrivez brièvement votre pro- Bonne lecture !
blème. Microsoft vous posera ensuite d’autres Jean-Marc LAGODA
© Groupe Eyrolles

L’ASSISTANCE MICROSOFT

11
 Module 1

DÉCOUVRIR ET ORGANISER
SON ESPACE DE TRAVAIL

L
’efficacité au travail repose sur la capacité à organiser les informations. Nous
évoquerons dans ce chapitre cinq techniques fondamentales.
© Groupe Eyrolles

13
Fiche 1 Nommer et classer ses dossiers et fichiers 15

Fiche 2 Auditer et nettoyer les fichiers 18

Fiche 3
1 Changer rapidement de période et de liaisons 21

Fiche 4 Définir une barre d’outils Accès rapide 24

Fiche 5
1 Gérer les incidents : récupérer et sauvegarder des fichiers 26
© Groupe Eyrolles
 NOMMER ET CLASSER SES DOSSIERS ET FICHIERS
1
Tout manager ou contrôleur de gestion a besoin de données quantitatives pour prendre ou pour
préparer des décisions. Ces informations sont souvent contenues dans des fichiers Excel. Pour être
utiles, elles doivent cependant être classées selon une logique qui facilitera vos recherches.

LES OBJECTIFS VISÉS sélectionnez le répertoire avant la saisie du nom

du fichier et le choix du type de fichier : le
Retrouver rapidement l’information voulue
format Excel .xlsx est affiché par défaut. Pour
suppose d’avoir réfléchi auparavant à l’organi-
une diffusion large à titre d’information, vous
sation du classement. Chacun peut avoir son
pourrez choisir un format PDF lisible avec
propre modèle mais les organisations étant de
Acrobat Reader.
plus en plus collaboratives, les données de-
viennent un bien commun à partager. Nous Il est possible également d’ajouter un mot clef.
proposons ici un classement fondé sur l’expé- Utilisez le chemin suivant : Fichier, Enregistrer
rience et qui facilitera les recherches. sous, puis cliquez sur la commande inscrite en
bleu Ajouter un mot clef, située en bas à droite
COMMENT UTILISER EXCEL ? de la boîte de dialogue. Vous pouvez, d’ailleurs,
Vous utiliserez Excel pour nommer les fichiers. en ajouter plusieurs ! Pensez à les séparer du
Ces fichiers sont également appelés classeurs. signe point-virgule. Cette option facilitera par la
Lors de la sauvegarde, vous pourrez définir les suite la recherche du document si vous le clas-
noms des classeurs en cohérence avec le thème sez mal par mégarde.
du répertoire de classement : par exemple,
sauvegarde d’un classeur Excel dans le réper- CONSEIL
toire COÛT/Gamme Produit A/Annee 2014/ Vous pourrez toujours renommer un fichier via
fichier Camera 1A [Link] ou l’explorateur Windows si son nom ne convient
pas à l’usage. Il faudra cependant être attentif
FORECAST/Annee 2014/F1/F1-2014 version
aux liens éventuels avec d’autres fichiers. Dans
du [Link].
ce cas, passez par le menu Édition, Liaisons,
Dans le menu Fichier, choisissez Enregistrer choisissez Modifier la source et sélectionnez le
sous, puis dans la boîte de dialogue qui s’ouvre, nouveau fichier.
© Groupe Eyrolles

Découvrir et organiser son espace de travail 15

FICHE 1
NOMMER ET CLASSER
SES DOSSIERS ET FICHIERS

Cas pratique
On a structuré le répertoire des fichiers utilisés par la PME FILM+ avec l’explorateur Windows.
D’abord par grands thèmes :

Puis par périodes : par années, voire par mois, car le tri chronologique couplé à un thème donné facilite la
recherche et la comparaison de données.

© Groupe Eyrolles

16 Découvrir et organiser son espace de travail

 FICHE 1
NOMMER ET CLASSER
SES DOSSIERS ET FICHIERS

POUR RÉSUMER
• Avec l’explorateur Windows, structurez le répertoire des fichiers par thème.
• Pensez à nommer vos fichiers de manière standardisée.
• Ajoutez un mot clef à chaque fichier au moment de l’enregistrement.

f fiche 5
Gérer les incidents : récupérer et sauvegarder des fichiers
© Groupe Eyrolles

Découvrir et organiser son espace de travail 17

2 AUDITER ET NETTOYER LES FICHIERS

La performance d’une entreprise dépendant de la qualité de ses décisions, les informations

fournies pour cette prise de décision sont importantes. Pour bâtir des relations de confiance, le
contrôleur de gestion sera donc attentif à mettre en place un audit de ses données.

LES OBJECTIFS VISÉS rions restreindre la saisie des effectifs à un

nombre inférieur à 30. Sélectionnez les cel-
Pour cette opération d’audit des données, trois
lules concernées, puis activez le bouton Vali-
niveaux de contrôle sont à considérer.
dation des données. La boîte de dialogue qui
1. Valider la qualité des données d’entrée. Pour s’ouvre vous proposera trois onglets que vous
les données saisies, on pourra activer une utiliserez selon vos besoins.
saisie automatique, et les données importées
2. Données importées à retraiter.
devront souvent être nettoyées. Excel possède
de nombreuses fonctionnalités : vérificateur Q En plus de la vérification orthographique, si
d’orthographe ou suppression des lignes certaines données ne conviennent pas, vous
dupliquées. utiliserez la commande Rechercher et rempla-
cer du menu Accueil. Par exemple pour
2. Contrôler la fiabilité des traitements. L’audit
remplacer « centre SC » par « centre Industrie
des formules peut consister à vérifier la ba-
et Logistique ».
lance carrée (dans un tableau, la somme des
lignes doit être égale à la somme des colonnes). Q Pour supprimer des doublons, sélectionnez la
Les formules peuvent également faire l’objet zone d’analyse, puis Données et cliquez sur
d’une revue générale par visualisation des Supprimer les doublons (la duplication du
calculs opérés. centre SC sera éliminée).

3. Valider les résultats obtenus. Contrôler la fiabilité des traitements

Q Contrôle de vraisemblance : quel sens donne- Vous utiliserez, dans Excel, différentes fonction-
rait-on à des effectifs ou des stocks négatifs ? nalités d’audit situées dans le menu Formules,
Q Contrôle de cohérence : une forte variation Vérification des formules.
peut cacher une incohérence liée à une erreur
Repérer les antécédents
de saisie ou de formule.
Cette fonctionnalité précise quelles sont les
COMMENT UTILISER EXCEL ? cellules à la source du résultat. Sélectionnez la
cellule avec une formule à auditer, en cliquant
Valider les données d’entrée
sur Repérer les antécédents. Une flèche bleue
1. Données saisies. apparaît et relie toutes les cellules utilisées. Pour
Q Vérification de l’orthographe : appuyez sur la la faire disparaître, cliquez sur Supprimer les
touche F7. Excel va repérer chaque mot sus- flèches.
© Groupe Eyrolles

pect et vous proposer de le corriger.

Repérer les dépendants
Q La fenêtre Validation des données du menu
Excel permet de déterminer la cause d’un pro-
Données permet de restreindre une saisie de
blème dans un modèle. Il suffit de sélectionner
valeurs numériques. Par exemple, nous pour-

18 Découvrir et organiser son espace de travail

 FICHE 2
AUDITER ET NETTOYER
LES FICHIERS

une plage de cellules à auditer et de cliquer sur Valider les résultats et traquer
Repérer les dépendants. Une flèche bleue pré- les erreurs
sentera tous les liens créés. Vous pourrez prévoir des formules d’audit. Avec
la formule =SI, vous vérifierez, par exemple, que
Afficher les formules
la somme des lignes égale la somme des co-
Pour vérifier la conformité des règles de calcul,
lonnes. Dans notre exemple, nous obtenons
cliquez sur Afficher les formules ; un autre clic
=SI(G10<>G11;G10-G11;« OK »), qui signifie :
les fera disparaître.

Cas pratique
À partir du zip, ouvrez le fi chier CH1-1, onglet F2 Données base
De nombreuses erreurs se sont glissées dans ce tableau comptabilisant les effectifs de FILM+. Excel va nous
aider à les découvrir !

À partir du zip, ouvrez le fi chier CH1-1, onglet F2 Données corrigées

L’illustration ci-dessous présente la situation corrigée avec l’aide d’Excel. Les zones qui ont été corrigées sont
indiquées en vert. Nous reparlerons de la fenêtre Espion visible sur l’illustration en fi n de fiche.
© Groupe Eyrolles

Découvrir et organiser son espace de travail 19

FICHE 2
AUDITER ET NETTOYER
LES FICHIERS

« Si les cellules contenant ces deux sommes

sont différentes, alors afficher la différence, si- POUR RÉSUMER
non indiquer OK ». • Les données texte dans un fichier Excel
peuvent être vérifiées avec la touche F7. Les
données numériques sont contrôlées avec la
CONSEIL commande Validation des données du menu
Lorsque des cellules ne sont pas visibles dans Données.
une feuille de calcul, vous pouvez les visualiser • Les fonctionnalités d’audit d’Excel vous per-
avec leurs formules : Menu Formules puis mettent également de vérifier vos formules dans
Fenêtre Espion. La fenêtre Espion permet d’ins- le menu Formules avec les commandes Repérer
pecter, de contrôler ou de confirmer les calculs les antécédents et Repérer les dépendants.
de formules et leurs résultats dans des feuilles
• La formule =SI vous permet de tester la
de calcul volumineuses.
concordance de vos résultats.

f fiche 22 f fiche 42
Calculer la période de récupération Tester la dépréciation (« impairment ») des immobilisa-
tions
f fiche 34
Calculer un escompte bancaire

© Groupe Eyrolles

20 Découvrir et organiser son espace de travail

 CHANGER RAPIDEMENT DE PÉRIODE ET DE LIAISONS
3
L’analyse des résultats intervient chaque mois. Dans ce cas, il faut sur un même modèle renommer
par mois tous les fichiers de travail et adapter les liaisons qui ont pu être créées. C’est assez fastidieux
et sans grande valeur ajoutée. Heureusement Excel a une solution pour chacune de ces situations.

LES OBJECTIFS VISÉS (VBA). Pour créer une macro simple, Excel peut
vous « filmer », c’est-à-dire enregistrer en code
Comme nous l’avons précisé dans l’introduction
VBA toutes vos manipulations et les stocker
de ce livre, nous ne développons pas de chapitre
pour qu’elles puissent être répétées.
spécifique sur les macros Excel qui nous
semblent réservées à des utilisateurs avertis. COMMENT UTILISER EXCEL ?
Cependant, des macros simples vous seront
indiquées dans cette fiche, car elles répondent à Enregistrer une macro
une problématique classique en gestion. Vous utiliserez l’enregistreur de macros d’Excel
du Menu Affichage, Macro, Enregistrer une
Qu’est-ce qu’une macro Excel ? Une macro est
macro. Dans la boîte de dialogue, saisissez le
un ensemble d’instructions qui s’enchaînent
nom de la macro, sa touche de raccourci et un
pour réaliser une tâche déterminée. Elle utilise
commentaire descriptif, puis effectuez les mani-
un langage de programmation qui lui est
pulations prévues avant de cliquer sur le bouton
propre : le Visual Basic pour Applications

Cas pratique
À partir du zip, ouvrez le fi chier [Link], onglet F3 Modèle
On veut préparer le reporting mensuel du chiffre d’affaires généré par les produits FILM+ dans les différents
pays où la PME possède des filiales. On a créé une première macro réalisant le format d’un tableau de trois
lignes sur trois colonnes avec le raccourci clavier Ctrl+Maj+A, puis une seconde macro créant en début de
classeur, par mois, cette feuille modèle.
© Groupe Eyrolles

Découvrir et organiser son espace de travail 21

FICHE 3
CHANGER RAPIDEMENT
DE PÉRIODE ET DE LIAISONS

Arrêter l’enregistrement de la barre d’état : Pour exécuter la macro, choisissez Affichage,

toutes les opérations réalisées entre le moment Macro, Exécuter. Vous trouverez ainsi douze
où vous enregistrez la macro et celui où vous feuilles numérotées par mois au début du clas-
arrêtez l’enregistrement viennent s’ajouter à la seur, qui reprennent exactement le format de la
macro. Il ne reste plus qu’à exécuter cette der- feuille appelée Modèle.
nière avec la touche de raccourci sélectionnée.
Notez qu’il faudra enregistrer le classeur au
Modifier la source d’une liaison
format .xlsm pour conserver les macros. Dans notre cas, des liens existent entre le fichier
du budget d’investissement (fichier [Link])
Insérer automatiquement une feuille et celui du budget de trésorerie (fichier CH7-1.
de calcul par mois avec une macro xlsx). CH7-1 est lié à CH5-1 pour le décaisse-
simple ment des investissements. Ouvrez ces fichiers
Pour un suivi de résultats mensuel, il peut être sans mise à jour des liens, puis enregistrez-les
utile de créer, à partir d’un modèle, une feuille sous leur nouveau nom. Automatiquement,
pour chaque mois. Voici la macro qui automa- Excel changera la référence du fichier source
tise cette tâche. Des codes seront à saisir après dans chaque liaison.
avoir cliqué, dans Développeur, sur Visual Basic
ou appuyé sur les touches Alt+F11. Nous devons CONSEIL
maintenant insérer un module pour la feuille
Nous avons ajouté une fonction pour addition-
qui va garder les codes de la macro : faites un ner une sélection de cellules en fonction d’une
clic droit sur la zone module (f illustration ci- couleur spécifique (indiquée en cellule A1).
dessous). Nous vous invitons à la tester ; c’est la fonction
SOMME_COULEUR (voir feuille CH1-1 Couleur).
Dans le module créé (Module 2), les lignes de
code ont été insérées pour générer la macro
appelée Feuilles_Mois.

© Groupe Eyrolles

ÉCRAN DE MICROSOFT VISUAL BASIC POUR DÉFINIR UNE MACRO

22 Découvrir et organiser son espace de travail

 FICHE 3
CHANGER RAPIDEMENT
DE PÉRIODE ET DE LIAISONS

POUR RÉSUMER
• Vous utiliserez l’enregistreur de macros d’Excel pour automatiser des actions répétitives en allant dans
le Menu Affichage, Macro, Enregistrer une macro.
• Le classeur devra être enregistré au format .xlsm pour conserver les macros.
• Vous pouvez créer des liaisons entre fichiers en toute sécurité : Excel gère tout changement éventuel.

f fiche 5
Gérer les incidents : récupérer et sauvegarder des fichiers
© Groupe Eyrolles

Découvrir et organiser son espace de travail 23

4 DÉFINIR UNE BARRE D’OUTILS ACCÈS RAPIDE

Pour activer sans perdre de temps vos commandes habituelles, pensez à paramétrer la barre
d’outils Accès rapide.

LES OBJECTIFS VISÉS d’outils Accès rapide à l’aide de la commande

Options.
Excel comprend plus d’une cinquantaine de
boutons de commandes courantes qui sont Pour cela, cliquez sur l’onglet Fichier puis sur
classés dans la barre de menu selon leur fonc- Options ; cliquez ensuite sur Barre d’outils
tion : Insertion, Mise en page, Affichage… Mais Accès rapide. Vous pouvez alors apporter à cette
il est parfois difficile de savoir où se trouve un barre les modifications souhaitées.
bouton spécifique. Ainsi, il sera pratique de
Modifier l’ordre des commandes dans
pouvoir accéder directement aux commandes la barre d’outils Accès rapide
usuelles. Excel vous facilitera la tâche avec la
Dans le menu Barre d’outils Accès rapide, allez
barre d’outils Accès rapide.
sur Personnaliser la barre d’outils Accès rapide
COMMENT UTILISER EXCEL ? dans le menu contextuel. Sous Personnaliser la
barre d’outils Accès rapide, cliquez sur la com-
Personnaliser la barre d’outils Accès mande à déplacer, puis sur la flèche Monter ou
rapide à l’aide de la commande Descendre (f illustration ci-contre).
Options
Vous pouvez ajouter ou supprimer des com-
mandes et en modifier l’ordre dans la barre

Cas pratique
Le contrôleur de gestion de FILM+ va personnaliser la barre d’outils Accès rapide selon ses habitudes, et
elle reprendra les commandes usuelles dans sa fonction. Elle doit aussi être limitée en nombre de boutons.
Nous proposons la barre d’outils suivante :
Commandes de base : Enregistrer sous, Annuler, Rétablir, Copier, Collage spécial
Mise en forme : Bordures, Police, Taille, Remplissage
Traitements et calculs : Somme, Tri croissant, décroissant, Tableau croisé dynamique
Diffusion : Aperçu avant impression
Elle apparaît sous le menu standard d’Excel.
© Groupe Eyrolles

24 Découvrir et organiser son espace de travail

 FICHE 4
DÉFINIR UNE BARRE
D’OUTILS ACCÈS RAPIDE

PERSONNALISER LA BARRE D’OUTILS ACCÈS RAPIDE DANS VOS OPTIONS

CONSEIL
Si vous souhaitez ajouter à la barre d’outils
Accès rapide d’autres commandes moins POUR RÉSUMER
usuelles, il vous faudra aller, dans les Options • Pour gagner du temps, vous pouvez
d’Excel Barre d’outils Accès rapide, chercher créer une barre d’outils Accès rapide.
Toutes les commandes du menu déroulant et • Cette barre peut être personnalisée avec la
cliquer sur Ajouter après avoir sélectionné la commande Options d’Excel.
commande voulue.
• Identifiez bien les icônes Excel que vous
© Groupe Eyrolles

Pour indiquer la position souhaitée de la barre utilisez le plus souvent pour personnaliser votre
d’outils Accès rapide, au-dessus ou en dessous barre de manière efficace.
du ruban standard, pensez à cocher ou à déco-
cher l’option figurant sous la zone listant les
commandes.

Découvrir et organiser son espace de travail 25

5 GÉRER LES INCIDENTS :
RÉCUPÉRER ET SAUVEGARDER DES FICHIERS

Dans tous les domaines, la gestion des incidents (panne électrique, bug informatique, mauvaise
manipulation…) est souvent très délicate. Excel a prévu ce type de problème, mais ne pourra pas
tout résoudre non plus. Il revient à chacun d’être prudent et d’anticiper.

LES OBJECTIFS VISÉS Récupérer des fichiers

Nous vous présentons, dans cette fiche, deux Première option
démarches importantes à connaître. Si vous avez oublié d’enregistrer votre fichier
1. La sauvegarde automatique des fichiers Excel. Excel, il existe une possibilité de retrouver le
2. La récupération de fichiers. travail fait sous un classeur nommé en l’ouvrant
dans sa version non modifiée. Cliquez sur
COMMENT UTILISER EXCEL ? Fichier, puis sur le bouton Versions au milieu
de l’écran et là… miracle ! Vous retrouvez votre
Sauvegarder des fichiers
travail non enregistré.
Vous utiliserez le chemin suivant : Fichier,
Le volet Office Récupération de document
Options, Enregistrement.
affiche jusqu’à trois versions du fichier. En règle
Cochez la case Enregistrer les informations de
générale, les versions d’un fichier sont affichées
récupération automatique toutes les x minutes,
par ordre d’ancienneté, la plus récente se trou-
puis indiquez la fréquence (en minutes) à la-
vant en haut de la liste (f illustration ci-contre).
quelle vous souhaitez que le programme enre-
Double-cliquez sur tous les fichiers récupé-
gistre vos données et l’état du programme
rables, puis enregistrez-les immédiatement
(f illustration ci-dessous).

© Groupe Eyrolles

PARAMÉTRAGE DE L’ENREGISTREMENT D’UN FICHIER

26 Découvrir et organiser son espace de travail

 FICHE 5
GÉRER LES INCIDENTS :
RÉCUPÉRER ET SAUVEGARDER DES FICHIERS

Cas pratique
La PME FILM+ prend très au sérieux la sauvegarde de ses fichiers, et l’a inscrite dans ses process. Tout salarié
qui crée un fichier est ainsi tenu de le nommer avant de commencer un travail de contenu et de l’enregistrer
sur le serveur de l’entreprise dans le répertoire de partage. En cas d’incident, la procédure mise en place dans
la PME répond aux critères habituels de prudence, à savoir une recherche des versions précédentes enregistrées.

avant de poursuivre votre travail. Vous pouvez Démarrer, choisissez Recherche, option Tous
également enregistrer une copie de sauvegarde les fichiers et dossiers, et entrez « *.xlsx »
du fichier sous un nom différent. dans la zone. Vous pourrez ensuite les trier
par nom ou par date.
Deuxième option
Il est possible aussi de rechercher des fichiers
via l’explorateur de Windows : dans le menu
© Groupe Eyrolles

ÉCRAN D’IDENTIFICATION DES VERSIONS DE FICHIERS ENREGISTRÉES

Découvrir et organiser son espace de travail 27

FICHE 5
GÉRER LES INCIDENTS :
RÉCUPÉRER ET SAUVEGARDER DES FICHIERS

Troisième option
Une dernière possibilité consiste à utiliser un POUR RÉSUMER
outil de récupération de fichier. Téléchargez-le, • Sauvegardez régulièrement vos travaux,
puis ouvrez-le et examinez la liste des fichiers manuellement ou de manière automatique,
« restaurables ». Mettez en surbrillance le fichier en précisant dans les options d’Excel vos
préférences : fréquence, emplacement, etc.
de feuille de calcul que vous voulez récupérer
et cliquez sur le bouton Récupérer. Ouvrez le • Vous pourrez récupérer un fichier Excel dans
le volet Office Récupération de document.
fichier pour vous assurer qu’il est complet, puis
• Il est possible de rechercher des fichiers
enregistrez-le sur votre disque dur.
Excel via l’explorateur de Windows en para-
métrant la commande Recherche, option Tous
CONSEIL les fichiers et dossiers, avec la terminaison de
Il vaut mieux prévenir que guérir : aussi, dès fichier « *.xlsx ».
que vous avez créé un nouveau classeur Excel,
enregistrez-le sous le nom voulu. Selon la fré-
quence de sauvegarde automatique définie,
pensez à faire des sauvegardes manuelles dès
que des travaux importants ou des paramé-
trages délicats ont été opérés.

f fiche 1 f fiche 60
Nommer et classer ses dossiers et fichiers Protéger des données

© Groupe Eyrolles

28 Découvrir et organiser son espace de travail

 Module 2

RECHERCHER LES DONNÉES CLEFS

DE GESTION ET MODÉLISER

P
our prendre de bonnes décisions, un gestionnaire se doit d’anticiper les pro-
blèmes, rechercher les causes possibles et cibler ses actions. Pour cela, il doit
« faire parler » les chiffres en les modélisant et en mettant en évidence des
faits marquants. Excel va vous aider avec quelques commandes à bien maîtriser.
© Groupe Eyrolles

29
Fiche 6
1 Importer et mettre en forme des fichiers texte 31

Fiche 7 Trier et filtrer les données 33

Fiche 8
1 Représenter graphiquement une tendance et un Pareto 36

Fiche 9 Identifier des règles de variabilité des coûts 39

Fiche 10
1 Inter-relier des feuilles de calcul d’un même classeur 41
© Groupe Eyrolles
 IMPORTER ET METTRE EN FORME DES FICHIERS TEXTE
6
Les données utiles aux gestionnaires se trouvent au sein même de l’entreprise mais également en
dehors. Excel permet de gérer toutes ces données.

LES OBJECTIFS VISÉS fichier texte et affiche les données dans un

nouveau classeur.
Les données peuvent provenir de fichiers texte
d’organismes divers : Insee, offices de tourisme, Les valeurs décimales sont présentées dans le
cours de Bourse… Obtenir des données quanti- fichier texte avec un point ; vous le remplacerez
tatives sur son environnement devient, en effet, par une virgule de manière à obtenir un format
indispensable à une bonne gestion. Excel facilite numérique reconnu par Excel. Pour cela, dans
la vie du contrôleur de gestion en acceptant la le menu Accueil, choisissez Rechercher-
plupart des formats d’importation. Remplacer, renseignez la recherche et appliquez
votre correction sur la zone voulue (f illustra-
COMMENT UTILISER EXCEL ? tion page suivante).
Importer un fichier texte dans Excel Importer d’autres formats de fichier
Les fichiers texte délimités (qui portent l’exten-
Vous pouvez aussi ouvrir des feuilles de calcul
sion .txt) sont des fichiers dans lesquels les
au format .ods dans Excel 2010 : il s’agit des
champs de texte sont séparés par des tabulations.
classeurs OpenDocument. Mais sachez que la
Vous pouvez modifier le caractère séparateur
mise en forme peut être perdue lors de l’enregis-
utilisé dans les fichiers délimités et les fichiers
trement et l’ouverture de ces fichiers.
texte .csv. Vous pourrez ainsi importer ou expor-
ter jusqu’à 1 048 576 lignes et 16 384 colonnes !
CONSEIL
Ouvrez dans Excel le fichier source (Fichier,
Il est possible d’importer des données d’un fi-
Ouvrir), puis sélectionnez le type de fichier .txt. chier texte en s’y connectant. Le chemin à suivre
Excel démarre l’Assistant Importation de texte. est le suivant : Données, Données externes,
Suivez ensuite les instructions de l’Assistant et Connexion. Cette option présente l’avantage de
sauvegardez le fichier sous Excel. S’il s’agit d’un ne pas dupliquer de données accessibles et
fichier .csv, Excel ouvre automatiquement le donc ne pas encombrer les espaces disque.

Cas pratique
À partir du zip, ouvrez le fi chier CH2-1, onglet F6 PX1
Les actionnaires de la société FILM+ compareront, de temps en temps, l’évolution de la valeur économique de
© Groupe Eyrolles

leur entreprise avec la valeur des actions des entreprises françaises pour vérifier si leur risque est suffisamment
rémunéré. Aussi le contrôleur de gestion calculera-t-il le taux de retour moyen du marché français. À partir du
fichier texte [Link] comprenant les cours du CAC 40 sur une année, nous constituons le fichier Excel CH2-1.
Des en-têtes ont été ajoutés. Les cours de Bourse apparaissent en format texte avec un point. Pour pouvoir traiter
la série statistiquement – calcul de la moyenne, écart type, etc. –, il faut la reformater au format numérique.

Rechercher les données clefs de gestion et modéliser 31

FICHE 6
IMPORTER ET METTRE EN FORME
DES FICHIERS TEXTE

BOÎTE DE DIALOGUE PERMETTANT DE FAIRE DES CORRECTIONS EN SÉRIE

POUR RÉSUMER
• L’importation dans Excel de fi chiers texte pose souvent problème, car les champs de texte sont séparés
par des points ; pour remplacer ces points par des virgules (format numérique reconnu par Excel), utilisez
la chaîne de commandes suivante : menu Accueil, Rechercher, Remplacer.
• Excel permet également l’ouverture des classeurs OpenDocument avec leurs données numériques, mais
les mises en forme peuvent être perdues.
• L’importation de données dans Excel peut n’être que temporaire ; on utilisera alors la fonctionnalité de
connexion : Données, Données externes, Connexion.

f fiche 13
Consolider les ventes dans différentes devises : utilisation de tables

© Groupe Eyrolles

32 Rechercher les données clefs de gestion et modéliser

 TRIER ET FILTRER LES DONNÉES
7
Excel facilite la vie du contrôleur de gestion avec ses nouvelles versions depuis 2007, car il est
maintenant possible de trier selon 64 critères.

LES OBJECTIFS VISÉS COMMENT UTILISER EXCEL ?

Le contrôle de gestion a pour mission de struc- Trier les données
turer les données pour leur donner du sens, faci-
Pour un tri rapide, vous utiliserez dans Excel le
litant ainsi la prise de décision. Cela implique
bouton Trier du menu Données en choisissant
de classer les volumétries importantes de don- Trier du plus petit au plus grand (ou inverse-
nées par groupes homogènes. Une modélisation
ment) après avoir sélectionné la plage de don-
sera alors possible. Ainsi, on peut orienter des
nées à étudier. Excel vous demandera sur quel
actions d’amélioration sur les articles en stock champ opérer le tri avec une liste déroulante.
les plus coûteux ou vouloir connaître unique-
Si vous souhaitez opérer un tri multicritères, il
ment le chiffre d’affaires d’une gamme produit
faudra cliquer sur Ajouter un niveau. L’ordre de
pour une période particulière.

Cas pratique
À partir du zip, ouvrez le fi chier CH2-1, onglet F7 Trier-filtrer
Les dirigeants de FILM+ pourront avoir à expliquer aux actionnaires de la société le niveau de volatilité de
l’action par rapport à celles du marché. La volatilité prendra en compte la valeur moyenne et les dispersions
basses et hautes autour de cette moyenne. On pourra ainsi souhaiter trier les cours d’ouverture du plus petit
au plus grand. On pourrait également filtrer les cours par mois et par année en décomposant la date en trois
parties : le jour, le mois et l’année.
© Groupe Eyrolles

Rechercher les données clefs de gestion et modéliser 33

FICHE 7
TRIER ET FILTRER
LES DONNÉES

tri pourra être défini en déplaçant les critères pourrez également rechercher une donnée spé-
vers le haut (tri prioritaire) ou vers le bas (tri cifique dans la zone Recherche.
secondaire). Dans la liste déroulante, toutes les valeurs sont
Notez qu’il faut bien préciser un titre (étiquette) cochées par défaut : par exemple, pour la co-
pour chaque colonne et ne pas laisser de ligne lonne année, on trouvera deux valeurs : 2013 et
vide entre la première ligne de données et celle 2014. Pour filtrer sur 2014, seule cette valeur
contenant les étiquettes. devra demeurer cochée. Un petit symbole en
Les tris peuvent être réalisés sur des valeurs forme d’entonnoir apparaît dans la colonne
numériques, sur du texte, des dates ou même servant de base au filtre. C’est un repère. Si vous
des couleurs. Dans ce cas particulier, ouvrez la passez le pointeur de la souris au-dessus de ce
boîte de dialogue Tri et choisissez l’option Trier repère, une info-bulle vous indiquera les critères
sur Couleur de cellule (f illustration ci- qui ont été définis.
dessous).
CONSEIL
Filtrer les données Pour faciliter les comparaisons de résultats entre
Pour activer le filtre automatique, rien de plus plusieurs classeurs Excel, allez dans le menu
simple : cliquez dans l’onglet Données sur le Affichage, puis cliquez sur Afficher côte à côte.
bouton Filtrer. Le bouton Rétablir la position de la fenêtre
permet de diviser l’écran en parties égales.
L’en-tête de chaque colonne se transforme alors
Lorsque vous naviguez dans un tableau avec de
en une liste déroulante avec un bouton repré-
nombreuses lignes, pour garder les en-têtes des
sentant une flèche orientée vers le bas. Selon les colonnes toujours visibles, utilisez la commande
données présentes dans la colonne, la liste de Figer les volets du menu Affichage.
choix proposera différents types de filtres. Vous

© Groupe Eyrolles

DÉFINITION DES OPTIONS DE TRI

34 Rechercher les données clefs de gestion et modéliser

 FICHE 7
TRIER ET FILTRER
LES DONNÉES

POUR RÉSUMER
• Les tableaux de données peuvent être triés selon plusieurs critères hiérarchisés : sélectionnez Données,
Trier, puis ajoutez un niveau si nécessaire.
• Ces tableaux peuvent ne faire apparaître que les données souhaitées : il suffit d’appliquer un filtre sur
certaines colonnes via la commande Données, Filtrer.
• En analysant un tableau de données, vous serez donc attentif à la présence de symboles en forme
d’entonnoir indiquant qu’une partie seulement des données est présentée.

f fiche 25 f fiche 44
Identifier les priorités Rechercher les principaux écarts dans les tableaux

f fiche 37
Calculer le coût des activités et des processus
© Groupe Eyrolles

Rechercher les données clefs de gestion et modéliser 35

8 REPRÉSENTER GRAPHIQUEMENT UNE TENDANCE ET UN PARETO

Selon les lois de la statistique, bon nombre de réponses pour demain se trouvent dans nos archives.
Les tendances marquées ne s’inversent pas si facilement. En matière de gestion, on pourra chercher
à éclairer l’avenir avec la mise en évidence de tendances fortes.

LES OBJECTIFS VISÉS Analyse). En recopiant ses valeurs dans un autre

tableau, vous pouvez faire un tri par ordre dé-
À partir d’un tableau historique de données,
croissant des valeurs et un cumul.
Excel peut représenter graphiquement une ten-
dance et faciliter une extrapolation. Opérations programmées
On pourra chercher à identifier l’importance Les données de base doivent se situer sur deux
relative d’événements dans une liste en compa- colonnes : la colonne A contiendra, par exemple,
rant leur fréquence d’apparition. Un diagramme les types de défauts ; la colonne B contiendra le
de Pareto mettra en évidence que 20 % des caté- nombre de défauts constatés.
gories produisent 80 % des effets. Agir sur ces Vous programmerez le classement des catégories
20 % aidera à solutionner un problème avec un et leur montant avec les formules suivantes :
maximum d’efficacité.
=INDEX(ColA;EQUIV(E3;ColB;0))
COMMENT UTILISER EXCEL ? La formule =Index donne la valeur d’une cellule
d’un tableau et la formule =Equiv situe le rang
Les courbes de tendance
d’un élément d’une matrice.
À partir d’une série de données, vous pouvez
=SI(NB(ColB)<LIGNES(E$3:E3);"";GRANDE.
insérer un graphique sous forme de courbe.
VALEUR(ColB;LIGNES(E$3:E3)))
Faites un clic droit sur la courbe : une boîte de
dialogue vous propose l’insertion d’une courbe La formule =[Link] permet de sélec-
de tendance. Vous aurez également l’option de tionner la k-ième plus grande valeur d’une série.
faire figurer l’équation de régression et son type Dès que les pourcentages des catégories seront
(linéaire, exponentiel, etc.). calculés, vous pourrez insérer le graphique à
deux axes présentant le pourcentage cumulé et
Les Pareto le montant en euros de chaque catégorie avec
La réalisation d’un graphique Pareto sous Excel la commande Insérer, Graphique, Graphiques
requiert plusieurs étapes pour préparer les recommandés.
données de manière à classer les effets princi-
paux par ordre d’importance. Elles peuvent être CONSEIL
suivies manuellement ou de manière program-
Des fonctionnalités complémentaires de la ver-
mée. sion de base du logiciel, ou Add’in, sont déve-
loppées et vendues par de nombreux éditeurs
Opérations manuelles
© Groupe Eyrolles

sur Internet. Elles automatisent toute une série de

Commencez par déterminer dans un tableau mises en forme de tableaux. Parmi celles de la
croisé dynamique les occurrences et les effets liste, nous citerons QLIKVIEW, qui est assez
sur chaque catégorie (fichier CH2-1, feuille pratique.

36 Rechercher les données clefs de gestion et modéliser

 FICHE 8
REPRÉSENTER GRAPHIQUEMENT
UNE TENDANCE ET UN PARETO

Cas pratique
À partir du zip, ouvrez le fi chier CH2-1, onglet F8 Tendance
FILM+ réalise son chiffre d’affaires principalement sur la vente de caméras. Les chiffres des onze dernières
années donnent une tendance pour estimer la douzième année. Les ventes de l’année 2015 sont estimées avec
l’équation indiquée dans le graphique et pour un certain niveau de précision R2 (plus l’indice sera élevé, plus
l’extrapolation sera fiable).

À partir du zip, ouvrez le fi chier CH2-1, onglet F8 Graph de Pareto

Les caméras vendues par FILM+ sont des produits de haute technologie qui marient plusieurs types de compo-
sants : mécaniques, électroniques, logiciels… Malgré tous les efforts de FILM+ pour garantir une haute qualité à
ses clients, certains composants sont défaillants. Voici un Pareto de défauts :

A : Aspect
E : Électrique
EL : Électronique
M : Mécanique
L : Logiciel
© Groupe Eyrolles

Rechercher les données clefs de gestion et modéliser 37

FICHE 8
REPRÉSENTER GRAPHIQUEMENT
UNE TENDANCE ET UN PARETO

POUR RÉSUMER
• À partir d’une série de données quantitatives, Excel peut calculer une courbe de tendance selon
différents modèles proposés et afficher l’équation de la courbe : Insérer, Graphique, Courbe ou nuage de
points, puis Ajouter une courbe de tendance.
• L’élaboration d’un Pareto est possible avec les formules =INDEX et =[Link].

f fiche 9 f fiche 11
Identifier des règles de variabilité des coûts Rechercher graphiquement une saisonnalité des ventes

© Groupe Eyrolles

38 Rechercher les données clefs de gestion et modéliser

 IDENTIFIER DES RÈGLES DE VARIABILITÉ DES COÛTS
9
La modélisation est très utilisée en contrôle de gestion. Elle sert la mise en œuvre de la stratégie
en éclairant les facteurs de consommation des ressources, les contributions des processus d’une
organisation et en guidant les comportements des acteurs économiques.

LES OBJECTIFS VISÉS Excel va aider à identifier les liens entre variables
avec ses fonctionnalités statistiques et à en
Avant l’établissement de budgets, le contrôleur
donner une représentation visuelle pour faciliter
de gestion pourra travailler les données de son
la compréhension.
organisation pour identifier les principales va-
riables et leurs liens. Le modèle le plus classique COMMENT UTILISER EXCEL ?
utilisé par les contrôleurs de gestion est sans
Vous utiliserez, dans Excel, le graphique en
conteste la variabilité des charges. Partant de
nuage de points. Il permet de mettre en évidence
différentes hypothèses d’activité, on estimera
le degré de corrélation entre deux variables liées.
comment le niveau des charges réagit.

Cas pratique
À partir du zip, ouvrez le fi chier CH2-1, onglet F9 Modèle variabilité
FILM+ présente, avec le tableau suivant, le lien entre les charges et un niveau d’activité exprimé en nombre de
caméras HD (son produit majeur). Un modèle précis en ressort et sert à prédire le niveau de charges qui sera
requis pour un volume d’activité donné : ici, 1 580 unités.
© Groupe Eyrolles

Rechercher les données clefs de gestion et modéliser 39

FICHE 9
IDENTIFIER DES RÈGLES
DE VARIABILITÉ DES COÛTS

Les deux variables sont, dans notre exemple, le

volume et le montant des charges. Le nuage de POUR RÉSUMER
points (en bleu) peut être modélisé par une • Vous pouvez calculer dans Excel la
droite de régression (en pointillés) permettant corrélation entre deux variables quantitatives
en sélectionnant les données des deux séries x
d’estimer le montant des charges pour un
et y (variable explicative, variable expliquée)
volume donné. et en appliquant la formule =coefficient.
Sélectionnez les données du tableau à représen- correlation(matrice des x ; matrice des y).
ter graphiquement, puis choisissez Insérer gra- • Sa représentation graphique se matérialise
phique, Nuage de points. avec l’insertion d’un graphique type « nuage
de points » et d’une courbe de tendance et
En cliquant droit sur un des points, vous pour-
en affichant la droite de régression et son
rez ajouter au graphique une courbe de tendance coefficient de détermination.
et son équation ainsi que son coefficient de • Une prévision peut être réalisée en utilisant
détermination. Plus celui-ci sera élevé, plus la la formule =prevision(valeur recherchée ;
tendance identifiée aura de sens. matrice des y ;matrice des x) ou en reprenant
les termes de l’équation affichée.
CONSEIL
Excel propose une autre fonctionnalité qui pourra
vous être utile dans le menu Données, Analyse,
Utilitaire d’analyse. Une boîte de dialogue
dévoile une série d’options : moyenne mobile,
lissage exponentiel, analyse de position…
Une formule directe applique l’extrapolation
linéaire selon la méthode des moindres carrés :
=Tendance (série). Vous n’avez pas besoin de
passer par une représentation graphique. La
valeur calculée s’ajoute à la dernière valeur de
la série.

f fiche 8 f fiche 49
Représenter graphiquement une tendance et un Pareto Structurer et sélectionner les indicateurs

f fiche 11
Rechercher graphiquement une saisonnalité des ventes
© Groupe Eyrolles

40 Rechercher les données clefs de gestion et modéliser

 INTER-RELIER DES FEUILLES DE CALCUL D’UN MÊME CLASSEUR
10
La plupart des calculs d’Excel se réalisent sur une seule et même feuille. Mais un fichier Excel peut
comporter de nombreuses feuilles de calcul. Malgré tout, vous enchaînerez aisément les calculs si
vous inter-reliez les feuilles.

LES OBJECTIFS VISÉS pour des calculs liés ? Comment calculer une
couverture de stocks en fonction des ventes si
L’analyse d’une situation nécessite souvent une
les données de stock figurent dans un onglet et
volumétrie importante de données. De plus, ces
les données des ventes dans un autre ? Excel
données peuvent être de natures diverses
permet tous les liens : aussi bien entre cellules
(ventes, coûts, stocks). Pour une meilleure lisi-
d’une même feuille qu’entre cellules de feuilles
bilité, elles sont souvent créées dans des feuilles
différentes d’un même classeur, ou encore entre
de calcul différentes. On peut aussi structurer
cellules de feuilles appartenant à différents
les calculs avec une feuille Excel récapitulant
classeurs. Excel garde la trace de tous les liens
toutes les variables d’entrée et opérer les calculs
établis.
détaillés sur des feuilles distinctes. Mais si ces
données ont été séparées, peut-on les utiliser

Cas pratique
À partir du zip, ouvrez le fi chier CH2-1, onglets F10 Liens stocks et F10 Ventes
Le niveau de stock influence le niveau de financement requis. C’est la raison pour laquelle les gestionnaires
de FILM+ définiront un niveau souhaitable à respecter et contrôleront son application.
FILM+ dispose de stocks de caméras HD1A d’une valeur totale de 70 000 € à une date donnée. La couverture
de stock sera calculée en utilisant la valeur de stock figurant dans sa feuille de calcul et en allant chercher les
données dans la feuille ventes : nombre de jours de la période et valeur du coût des ventes sur cette même période.
© Groupe Eyrolles

Rechercher les données clefs de gestion et modéliser 41

FICHE 10
INTER-RELIER DES FEUILLES DE CALCUL
D’UN MÊME CLASSEUR

COMMENT UTILISER EXCEL ?

POUR RÉSUMER
Vous utiliserez, dans Excel, le signe égal (=) dans
• Excel est l’outil idéal pour réaliser des
la cellule où afficher la donnée. Avec le pointeur calculs. Les données peuvent se trouver à
de la souris, dans l’autre feuille de calculs, cli- différents endroits d’une feuille ou même sur
quez sur la cellule à référencer, puis sur la des feuilles différentes. Excel va gérer la loca-
touche Entrée. Excel fait apparaître automati- lisation de ces données pour vous permettre de
faire tous les liens possibles.
quement la référence de la feuille utilisée : par
exemple, si la seconde feuille de calculs est • Vous pouvez ainsi utiliser des données de
différentes feuilles pour réaliser un calcul.
nommée « ventes », Excel ajoutera « ventes! »
• Pensez juste à confirmer par la touche Entrée
avant la référence de la cellule utilisée. Pour la
dès que vous avez sélectionné la cellule voulue
couverture des ventes, vous aurez donc la for- ou terminé la formule de calculs utilisant les
mule suivante : =D5*ventes!H5/ventes!H8 données.
(c’est-à-dire : valeur des stocks multipliée par le
nombre de jours figurant dans la feuille ventes
et divisée par le coût des ventes indiqué dans
cette feuille ventes).

CONSEIL
Pensez à confirmer en appuyant sur la touche
Entrée dès que vous avez sélectionné la cellule
voulue ou la formule de calcul utilisant les
données dans une autre feuille.
Donnez à chaque feuille de travail un nom expli-
cite : double-cliquez sur le nom de l’onglet ; le
numéro de feuille apparaît en surbrillance.
Modifiez le nom et appuyez sur Entrée. Le
changement apparaîtra dans tous les liens créés.

f fiche 36 f fiche 46
Calculer les coûts unitaires d’unité d’œuvre Définir les formats de présentation © Groupe Eyrolles

42 Rechercher les données clefs de gestion et modéliser

 Module 3

CALCULER LE BUDGET
DES VENTES ET DES FRAIS
COMMERCIAUX

L
e budget des ventes constitue la base de tout budget d’entreprise. Il comporte
des éléments spécifiques que nous abordons dans les fiches suivantes.
© Groupe Eyrolles

43
Fiche 1
11 Rechercher graphiquement une saisonnalité des ventes 45

Fiche 12 Calculer les coefficients de saisonnalité 48

Fiche 1
13 Consolider les ventes dans différentes devises :
utilisation de tables 50

Fiche 14 Calculer les contributions directes par famille de produits 52

Fiche 15
1 Mesurer la sensibilité des variables
et calculer le levier opérationnel 55
© Groupe Eyrolles
 RECHERCHER GRAPHIQUEMENT
UNE SAISONNALITÉ DES VENTES 11
Un budget fixe des points de repère en vue de délivrer, dans le court terme, la stratégie voulue
sur le long terme. Il doit donc être précis. Le budget des ventes, notamment, doit répondre à ce
besoin. Vous devrez rechercher une saisonnalité dans les ventes.

LES OBJECTIFS VISÉS Vous ne pourrez donc pas considérer une répar-
tition linéaire et homogène, en divisant l’objectif
Les objectifs de vente annuels doivent être ca-
annuel en douzièmes par exemple. Il faudra
dencés sur chaque mois et détaillés par produit.
savoir si vous devez considérer une saisonnalité
Or l’activité peut subir des variations saison-
particulière.
nières. Certaines variations sont évidentes
(vente de glaces en été, vente de jouets pour Cette saisonnalité se caractérise si deux condi-
Noël), mais d’autres peuvent être cachées der- tions sont réunies :
rière des données chiffrées. Excel va vous aider Q une irrégularité ;
à visualiser et à mettre en évidence ces varia- Q une répétition de cette irrégularité à la même
tions en vous permettant de générer de nom- période chaque année.
breux graphiques.

Cas pratique
À partir du zip, ouvrez le
fichier CH3-1, onglet F11
Saisonnalité
On cherche à déterminer chez
FILM+ une saisonnalité pour
les ventes de caméras HD. Les
ventes des différents modèles
de caméras sont présentées par
mois. On obtient le graphique
ci-dessous.
On commencera par représen-
ter graphiquement chacune des
années au niveau de la famille
de produits et sur la périodicité
mensuelle et, au besoin, on
détaillera par produit.
On obser ve que les diffé-
rents segments de droite se
superposent ou demeurent
© Groupe Eyrolles

bien parallèles chaque mois :

il y a donc une saisonnalité
mensuelle.

Calculer le budget des ventes et des frais commerciaux 45

FICHE 11
RECHERCHER GRAPHIQUEMENT
UNE SAISONNALITÉ DES VENTES

Pour identifier une saisonnalité, il faut disposer fier si une saisonnalité existe sur cette maille
d’un historique sur plusieurs années avec un détail temporelle et ainsi de suite.
des ventes réalisées selon différentes fréquences :
mois, trimestre… Plus l’historique sera riche en
COMMENT UTILISER EXCEL ?
données, plus le niveau de certitude sera élevé. Dans le tableau des ventes, sélectionnez les
On détaillera dans un premier temps les ventes données souhaitées en cliquant sur la zone
selon la périodicité la plus élevée : le mois. Si, voulue. Puis, dans la barre de menu Excel,
en superposant les courbes mensuelles des dif- choisissez Insertion puis Graphique.
férentes années, on note que ces courbes pré- Une boîte de dialogue s’ouvre et vous propose
sentent des segments parallèles, c’est qu’il y a différents types de graphiques : sélectionnez
bien une saisonnalité. Si ce n’est pas le cas, on Courbes. Vous pouvez choisir une des courbes
passera à la périodicité trimestrielle pour véri- proposées (f illustration ci-dessous).

© Groupe Eyrolles

DIFFÉRENTS TYPES DE COURBES

46 Calculer le budget des ventes et des frais commerciaux

 FICHE 11
RECHERCHER GRAPHIQUEMENT
UNE SAISONNALITÉ DES VENTES

CONSEIL
Pour bien visualiser les différentes courbes,
POUR RÉSUMER
adaptez l’échelle des ordonnées du graphique • Une saisonnalité des ventes est mise en
(vous chercherez la valeur la plus faible et la évidence quand on assiste à une répétition
valeur la plus élevée). Pour cela, faites un clic d’une irrégularité à la même période chaque
droit sur l’axe des ordonnées : Mise en forme année.
de l’axe. Dans la boîte de dialogue s’ouvrant, • La représentation visuelle de cette saison-
vous indiquerez le minimum et le maximum nalité peut être démontrée avec un graphique
après avoir changé l’option d’automatique à Excel sous forme de courbe.
fixe (cliquez sur la puce correspondante) et • L’axe des ordonnées pourra être mis en
indiqué les nouvelles valeurs. Votre démonstration forme avec Excel pour mieux faire ressortir le
n’en sera que plus visuelle. parallélisme des différents segments de droite.

f fiche 49
Structurer et sélectionner les indicateurs
© Groupe Eyrolles

Calculer le budget des ventes et des frais commerciaux 47

12 CALCULER LES COEFFICIENTS DE SAISONNALITÉ

Nous avons vu que les ventes pouvaient être saisonnières. Un objectif annuel doit donc pouvoir
se décliner de manière précise par saison. Pour cela, on appliquera à une prévision moyenne de
chaque saison (mois, trimestre…) des coefficients de saisonnalité.

LES OBJECTIFS VISÉS Excel dispose d’une large gamme de formules

de calcul qui vont vous faire gagner un temps
Les coefficients de saisonnalité sont calculés
précieux.
selon la méthode de la double moyenne.
Celle-ci est calculée à partir : COMMENT UTILISER EXCEL ?
Q d’une part, de la moyenne des périodes sai- Dans la première cellule de la ligne ou colonne
sonnières selon les années ; moyenne, saisissez la formule =moyenne(.
Q d’autre part, de la moyenne annuelle des pé- Excel précise alors qu’il faut sélectionner la série
riodes saisonnières. de nombres entrant dans le calcul de la moyenne.

Cas pratique
À partir du zip, ouvrez le fi chier CH3-1, onglet F12 Coeff saison
Chez FILM+, la moyenne des ventes mensuelles pour les caméras HD est calculée pour chaque année (colonnes
B à M de l’onglet F12 Coeff saison). Puis, après avoir calculé, par mois, la moyenne des années, on obtient en
cellule O8 rouge la double moyenne. Les coefficients saisonniers résultent du rapport de la moyenne de chaque
mois à cette double moyenne.
On observe que la prévision annuelle de 1 750 correspond en moyenne à 145,8 unités par mois et c’est sur
celle-ci que nous appliquons les coefficients saisonniers. Ainsi pour le mois de janvier de la nouvelle année 2014,
nous obtenons une prévision de 171 en opérant un arrondi sur 145.8*1.17, soit 171.
Nous pouvons alors répartir une prévision annuelle sur les différentes périodes en respectant la saisonnalité
observée : la prévision annuelle est divisée par le nombre de saisons. Le résultat final est obtenu en multipliant
le coefficient de saisonnalité par cette prévision moyenne.

© Groupe Eyrolles

48 Calculer le budget des ventes et des frais commerciaux

 FICHE 12
CALCULER LES COEFFICIENTS
DE SAISONNALITÉ

Sélectionnez donc les cellules concernées :

d’abord la cellule de départ, puis appuyez si- POUR RÉSUMER
multanément sur les touches Maj et « point » et • Le calcul des coefficients saisonniers
déplacez le curseur jusqu’à la dernière cellule à peut être réalisé avec un tableau présentant
des données par année, par saison et en
sélectionner avant de fermer la parenthèse : ).
procédant au calcul de la double moyenne.
La formule =ARRONDI soit INF ou SUP permet • La formule =moyenne permet d’obtenir le
d’obtenir des arrondis avec le nombre de déci- résultat escompté en colonne et en ligne.
males souhaité. Par exemple, arrondi. • Vous pouvez recopier cette formule auto-
inf(2,1455 ;2) arrondira le chiffre sélectionné à matiquement en double-cliquant sur la croix
la deuxième décimale inférieure, soit 2,14. de recopie.

CONSEIL
Pour éviter de répéter dans chaque cellule la
saisie de la formule moyenne, vous pouvez, sur
la cellule contenant la formule, capturer le coin
en bas à droite : une croix apparaît.
Cliquez dessus et, sans lâcher le bouton de la
souris, déplacez le curseur sur les cellules sou-
haitées. La formule moyenne a été recopiée
automatiquement. Si vous double-cliquez sur la
croix de recopie, Excel recopiera automatique-
ment les formules sur les cellules du bas. Cela
permet d’économiser un temps précieux.

f fiche 36
Calculer les coûts unitaires d’unité d’œuvre
© Groupe Eyrolles

Calculer le budget des ventes et des frais commerciaux 49

13 CONSOLIDER LES VENTES DANS DIFFÉRENTES DEVISES :
UTILISATION DE TABLES

Les ventes d’une entreprise internationale peuvent être réalisées dans différentes devises. Le
budget des ventes doit donc tenir compte de ce paramètre supplémentaire.

LES OBJECTIFS VISÉS Nous ajoutons une colonne intitulée « ventes en

euro » dans chaque feuille de calcul reçue des
Les ventes soumises par les différentes filiales
filiales. Dans cette colonne, les ventes en devise
sont des ventes exprimées en devise locale :
locale sont multipliées par le taux de change. Il
quantités prévues multipliées par prix de vente
faut aller le chercher manuellement dans la
unitaire en devise locale. Ces ventes vont devoir
table. Dans la colonne de destination, saisissez
être consolidées dans une devise unique : la
la formule de calcul :
devise de la maison mère.
=cellule de la vente en devise locale * cellule de
Pour la période budgétaire, une table fixera les
la table de devise
taux retenus pour chaque devise vis-à-vis de la
devise de consolidation. Cette table sera donc Ensuite, activez la fonction F4 pour figer cette
utilisée pour convertir toutes les ventes expri- cellule et terminez en appuyant sur la touche
mées dans une devise locale étrangère. Entrée.

Les fonctions Excel présentées automatisent les L’activation de la fonction F4 fait apparaître
calculs et évitent des erreurs préjudiciables. dans la formule le symbole « $ » avant et après
la lettre de la colonne. Vous pourrez recopier
COMMENT UTILISER EXCEL ? rapidement cette formule vers le bas en double-
cliquant sur le coin inférieur droit de cette
Premier cas
première cellule.
À partir du zip, ouvrez le fichier CH3-1,
onglet F13 Conso

Cas pratique
La société a deux filiales étrangères soumettant leur budget en devise euro (Allemagne) et en real brésilien
(Brésil). La devise de consolidation est l’euro.
Deux cas peuvent se présenter.
Premier cas : chaque filiale envoie un fichier des ventes en monnaie locale ; la consolidation en euro est faite
manuellement pour chaque fichier en sélectionnant le taux de conversion approprié.
Second cas : chaque filiale envoie un fichier des ventes en monnaie locale ; la consolidation est faite dans un
fichier unique. Dans ce fichier, on ajoute une colonne ventes en euro. La conversion est automatisée par une
recherche programmée du taux de conversion dans la table des devises.
© Groupe Eyrolles

50 Calculer le budget des ventes et des frais commerciaux

 FICHE 13
CONSOLIDER LES VENTES DANS
DIFFÉRENTES DEVISES : UTILISATION DE TABLES

Second cas figer ligne et colonne : $D$3 ; pour ne figer que

À partir du zip, ouvrez le fichier CH3-1, la colonne : $D3 ; pour ne figer que la ligne : D$3.
onglet F13 Table des devises
Dans le fichier général des ventes en devise lo- CONSEIL
cale, une colonne identifie la devise. Avec la En utilisant la fonction RechercheV, vous préfé-
rerez peut-être nommer la zone de la table
fonction RechercheV, importez automatique-
plutôt que de sélectionner toutes les cellules la
ment, dans une colonne dédiée, le taux de
composant. Sélectionnez cette zone et, dans la
change qui se trouve dans la table des devises. case en haut à gauche du fichier, attribuez-lui
RechercheV signifie « recherche verticale » un nom.
(fonction utilisée le plus souvent) ; on peut ce-
pendant utiliser une recherche horizontale si la
table a été ainsi conçue ; dans ce cas, ce sera la
POUR RÉSUMER
fonction RechercheH.
• La conversion de données monétaires
RECHERCHEV(valeur_cherchée, table_matrice, d’une devise en une autre peut s’obtenir en
no_index_col, [valeur_proche]) multipliant chaque ligne exprimée en devise
La fonction RechercheV demande la valeur re- par son coefficient de conversion stocké dans
une cellule particulière. Le calcul avec Excel
cherchée : ici, sélectionnez la cellule indiquant
utilisera la fonction F4 pour figer la cellule
la devise, puis l’ensemble des cellules de la table contenant le coefficient de conversion.
des devises, et indiquez dans quel numéro de • Une alternative est de stocker les coefficients
colonne se trouve le taux à importer : comptez dans une table en les liant à un code devise.
le numéro de colonne en commençant par la Dans ce cas, utilisez la fonction RechercheV
gauche ; terminez en indiquant « faux » et fer- pour rapatrier automatiquement les coeffi-
mez la parenthèse. Chaque étape est séparée cients relatifs aux codes figurant sur chaque
ligne de données exprimées en devise.
d’un point-virgule.
• Cette fonction est très pratique quand vous
La fonction F4 est utile pour figer une ligne et/ voulez codifier des données pour opérer des
ou une colonne. Il suffit d’indiquer dans la for- calculs statistiques.
mule le symbole du dollar $. Par exemple, pour

f fiche 41
Calculer les provisions de dépréciation des stocks et des créances client
© Groupe Eyrolles

Calculer le budget des ventes et des frais commerciaux 51

14 CALCULER LES CONTRIBUTIONS PAR FAMILLE DE PRODUITS

Les ventes génèrent des revenus qui sont nécessaires à la couverture des frais engagés. La comp-
tabilité de gestion précisera les types de coût sur lesquels porter son attention si l’on veut optimi-
ser le résultat. On calculera pour cela les contributions des différentes familles de produits.

LES OBJECTIFS VISÉS En distinguant ces différents coûts, on pourra

calculer deux marges importantes :
On identifiera notamment les coûts qui varient
en fonction de l’activité : les frais variables (di- Q la marge sur coût variable ;
rects ou indirects) d’une part, et les frais directs Q la marge sur coût direct.
(variables ou fixes) d’autre part, qui sont spéci- La marge sur coût variable se définit ainsi : prix
fiques à l’objet que l’on veut chiffrer. de vente – coût variable.

Cas pratique
À partir du zip, ouvrez le fi chier CH3-1, onglet F12 Saisonnalité
Dans la société FILM+, on présente sous forme de tableau les différentes marges exprimées en valeur absolue ou
en pourcentage des ventes de caméras HD (ratio). Pour identifier les types de charges en détail, vous pouvez revoir la
fiche no 9. Le tableau présente différents niveaux hiérarchiques de synthèse : la famille de produits, le produit, le mois.

© Groupe Eyrolles

52 Calculer le budget des ventes et des frais commerciaux

 FICHE 14
CALCULER LES CONTRIBUTIONS
PAR FAMILLE DE PRODUITS

Cette marge est utile pour mesurer la sensibilité

du volume sur le résultat. De plus, elle permet
de décider d’un arrêt de commercialisation. En
effet, si la marge sur coût variable est négative,
cela signifie qu’elle n’apporte aucune contribu-
tion à la couverture des frais fixes et que plus le
volume est élevé, plus fortes seront les pertes !
Elle permet également d’arbitrer le développe-
ment de plusieurs produits : le produit ayant la
marge la plus élevée sera celui à privilégier car
le plus contributeur.
La marge sur coût direct se définit ainsi : prix
de vente – coût direct.
Cette marge donne la contribution spécifique
d’une famille de produits, c’est-à-dire la marge
n’intégrant que des coûts qui sont propres à la
famille de produits et dont on est absolument
BOÎTE DE DIALOGUE POUR PARAMÉTRER DES SOUS-TOTAUX
certain (ce qui n’est pas le cas des coûts indi-
rects, qui subissent une répartition plus ou
En cliquant sur la barre de réduction,
moins arbitraire).
Nous allons pouvoir présenter ces différentes
marges dans un tableau Excel et mesurer leur
sensibilité.

COMMENT UTILISER EXCEL ? vous pourrez ne faire apparaître que les sous-
totaux sans le détail.
Nous utilisons la fonction Données, Plan, Sous-
Vous pourriez aussi mettre en évidence les
total sur l’ensemble de la zone de données, y
marges les plus importantes en utilisant la mise
compris les titres des colonnes.
en forme conditionnelle des cellules du menu
La boîte de dialogue demande alors sur quelle
Accueil (voir fiche 43).
hiérarchie opérer les sous-totaux : dans notre
exemple, sélectionnez « famille » et précisez
CONSEIL
que les « quantités » sont à additionner (f illus-
Pour mettre en forme le document, vous avez la
tration ci-après).
possibilité de créer un thème ou d’en choisir un
Après confirmation, Excel ajoutera les sous- dans la liste proposée par Excel. Dans le menu,
© Groupe Eyrolles

totaux : titre et somme. Sur la gauche appa- optez pour Mise en page, puis cliquez sur le
raissent les barres de hiérarchie : détail, bouton Thèmes : une palette de couleurs, polices
sous-total, total général. et effets est disponible. Si elle ne vous convient
pas, vous pouvez la personnaliser.

Calculer le budget des ventes et des frais commerciaux 53

FICHE 14
CALCULER LES CONTRIBUTIONS
PAR FAMILLE DE PRODUITS

POUR RÉSUMER
• Afin de calculer les contributions par familles de produits, vous calculerez la marge sur coût variable
et la marge sur coût direct et les présenterez dans des tableaux Excel.
• Les tableaux de données quantitatives sont parfois imposants. Excel peut vous aider à les rendre plus
lisibles en calculant des sous-totaux à chaque fois qu’une variable change.
• Excel vous permet également de montrer le niveau de détail ou seulement les sous-totaux avec l’utilisation
de barres de hiérarchie.

f fiche 9 f fiche 43
Identifier des règles de variabilité des coûts Procéder à la revue analytique des comptes

© Groupe Eyrolles

54 Calculer le budget des ventes et des frais commerciaux

 MESURER LA SENSIBILITÉ DES VARIABLES ET
CALCULER LE LEVIER OPÉRATIONNEL 15
Les variables principales dans une organisation sont le volume, le prix de vente unitaire, le coût
variable unitaire et le niveau des frais fixes. Il est intéressant de mesurer l’effet d’une modification
de ces variables sur le résultat financier, de changer leur niveau actuel dans la même proportion
pour repérer la hiérarchie des variables.

LES OBJECTIFS VISÉS bilité d’une variation de l’activité sur le résultat

opérationnel de l’organisation.
Faut-il privilégier une baisse de 10 % des
charges variables ou une hausse de 10 % du On calcule le levier opérationnel en divisant la
volume ou du prix de vente ? Excel enchaînant marge sur coût variable par le résultat opération-
les calculs programmés sur différentes cellules, nel. Si le levier est de 4, alors une variation de
nous allons simuler la modification de para- 10 % de l’activité entraînera une variation du
mètres pour examiner leur effet sur des indica- résultat de 4 × 10 %, soit 40 %.
teurs de synthèse.
COMMENT UTILISER EXCEL ?
Le levier opérationnel est un indicateur finan-
Excel facilite l’investigation du champ des pos-
cier couramment utilisé pour indiquer la sensi-
sibles grâce à la fonction Boutons curseurs. Cette

Cas pratique
À partir du zip, ouvrez le fichier CH3-1, onglet F15 Synthèse
FILM+ a besoin de réaliser une simulation concernant l’évolution de son résultat d’exploitation selon son volume de
vente, ses prix de vente ou ses coûts. On modifiera les principales variables en leur appliquant un taux de variation entre
0 % et 100 %. On vérifiera l’effet sur le levier opérationnel provoqué par ces changements. Une présentation soignée
des résultats facilitera la compréhension et, ainsi, le choix d’arbitrage entre ces variables. Voici la feuille obtenue :
© Groupe Eyrolles

Lorsque l’on déplace les curseurs, les valeurs des variables changent automatiquement. Il en est de même pour
le résultat et le levier opérationnels.
On pourra insérer un graphique pour présenter la situation des variables (point rouge du graphique) au regard
de toutes les possibilités pouvant être considérées.

Calculer le budget des ventes et des frais commerciaux 55

FICHE 15
MESURER LA SENSIBILITÉ DES VARIABLES ET
CALCULER LE LEVIER OPÉRATIONNEL

MENU DE CRÉATION DES BOUTONS CURSEURS

fonction nécessite l’installation du menu Il ne reste plus qu’à paramétrer les calculs : le
Développeur dans la barre d’Excel (Fichier, résultat, par exemple, sera obtenu en multipliant
Personnaliser le ruban). le volume par la marge sur coût variable (prix
Pour insérer un bouton curseur, utilisez le bou- de vente – coût variable) de laquelle vous reti-
ton situé dans la partie haute de la fenêtre rerez les coûts fixes.
Contrôles de formulaire. Après avoir cliqué sur
ce bouton, dessinez dans la feuille Excel la taille
CONSEIL
du bouton curseur souhaité (f illustration ci-
Pour créer les boutons, il sera parfois nécessaire
dessus).
de travailler sur des pourcentages de variation.
À ce bouton curseur va être associée une cellule En effet, la valeur est plafonnée à 30 000 et le
de la feuille de calcul. La valeur de cette cellule nombre de pas est limité…
variera selon le déplacement du curseur. Nous pourrions garder dans une partie du fichier
Faites un clic droit sur le bouton curseur ; une le volume de référence et lui appliquer un
coefficient de changement allant par exemple
boîte de dialogue apparaît, dans laquelle l’option
de 10 % à 300 %. Un volume de référence de
Format de contrôle est choisie ; vous devez alors 50 000 unités pourra varier sur l’intervalle de :
préciser la valeur de départ, la valeur active, les 50 000 × 10 % = 5 000 unités à 50 000
valeurs mini et maxi… et, surtout, la référence × 300 % = 150 000 unités.
de la cellule qui sera modifiée par le déplace-
ment du curseur. © Groupe Eyrolles

56 Calculer le budget des ventes et des frais commerciaux

 FICHE 15
MESURER LA SENSIBILITÉ DES VARIABLES ET
CALCULER LE LEVIER OPÉRATIONNEL

POUR RÉSUMER
• Assurez-vous d’avoir installé dans la barre d’outils d’Excel la commande Développeur.
• Vous pouvez maintenant utiliser des boutons curseurs pour réaliser des simulations sur vos variables clefs.
• Pour créer le bouton curseur, allez dans le menu Développeur, puis choisissez Insérer Contrôles de formu-
laire et associez à ce bouton curseur une cellule de la feuille de calcul correspondant à la variable à simuler.

f fiche 39 f fiche 53
Simuler les actions d’amélioration sur les coûts en utilisant les Réaliser les graphiques de simulation
boutons curseurs
© Groupe Eyrolles

Calculer le budget des ventes et des frais commerciaux 57

 Module 4

CALCULER LE BUDGET
DE PRODUCTION, D’ACHAT
ET LES FRAIS INDUSTRIELS

A
près avoir défini le budget des ventes, le gestionnaire doit calculer les res-
sources courantes qui vont être nécessaires à la réalisation de ses objectifs.
© Groupe Eyrolles

59
Fiche 16
1 Calculer les besoins de production
avec la fonction Produitmat 61

Fiche 17 Calculer le taux d’utilisation des équipements

et identifier les conséquences 63

Fiche 18
1 Cadencer les achats et valoriser le flux 65

Fiche 19 Calculer les heures productives 67

Fiche 20
1 Calculer la masse salariale des effectifs directs
de production 69
© Groupe Eyrolles
 CALCULER LES BESOINS DE PRODUCTION
AVEC LA FONCTION PRODUITMAT 16
Le calcul des besoins de production peut se révéler fastidieux compte tenu du nombre de références
produites. Excel fournit une fonction bien utile pour accélérer ces calculs : la fonction Produitmat.

LES OBJECTIFS VISÉS produit intermédiaire prévu en nomenclature,

donnera le besoin brut en fabrication du produit
La production de biens de consommation cou-
intermédiaire. Ce besoin brut corrigé de la varia-
rante repose le plus souvent sur des prévisions
tion de stock donnera alors le besoin net en
de vente à court terme établies par la force de
produits intermédiaires. On recommencera cette
vente. Le plan directeur de production définit
opération autant de fois qu’il y a de niveaux de
les quantités à fabriquer pour chaque référence
nomenclature.
produite en tenant compte des politiques de
stock de l’entreprise (besoins nets). Il s’opère en Ces calculs peuvent être fastidieux si le nombre
partant des références de produits finis. Puis, sur de références et de niveaux de nomenclature est
chaque niveau de nomenclature, le calcul des élevé. Pour limiter les paramétrages de cellules,
besoins nets est cascadé. Ainsi, le besoin net Excel dispose d’une fonction bien utile : la
d’un produit intermédiaire s’obtient en deux fonction Produitmat. En effet, le calcul de be-
étapes : tout d’abord, en reprenant le besoin net soins bruts à chaque niveau de nomenclature
du produit fini qui, multiplié par la quantité de s’apparente à un calcul de produit matriciel : les

Cas pratique
À partir du zip, ouvrez le fichier CH4-1, onglet F15 CBN
On calcule le besoin brut des composants pour la société FILM+ : caméras, cartons, documentation, etc., au
regard des caméras à personnaliser.
La matrice 2 est multipliée par la matrice 1 :
© Groupe Eyrolles

Le besoin brut de LAB, soit 6 448, est obtenu par la fonction Produitmat qui multiplie le lien de nomenclature de
chaque produit fini, soit 4, par la quantité à fabriquer ; on a donc :
(4*1081)+(4*446)+(4*45)+(4*40)=6 448

Calculer le budget de production, d’achat et les frais industriels 61

FICHE 16
CALCULER LES BESOINS DE PRODUCTION
AVEC LA FONCTION PRODUITMAT

quantités des liens de nomenclature formant la CONSEIL

première matrice et les quantités de besoins nets Attention, la multiplication des matrices
des produits du niveau supérieur formant la n’est pas symétrique ! Il est donc néces-
deuxième matrice. saire de bien poser le problème à résoudre
avant de paramétrer la formule pour respecter
COMMENT UTILISER EXCEL ? l’ordre de priorité des matrices. Vérifiez égale-
ment que le nombre de colonnes de la matrice 2
Commencez dans Excel par sélectionner la zone (dans notre exemple) est bien égal au nombre
de réponse (colonne jaune des besoins bruts), de lignes de l’autre matrice.
puis appuyez simultanément sur les touches
Ctrl et Maj, puis Entrée avant de saisir sur la
première ligne de la zone de réponse la formule : POUR RÉSUMER
=produitmat(cellules de la matrice 2;cellules de • Le calcul des besoins quantitatifs pour
la matrice 1) chaque référence d’une nomenclature de
production est fastidieux. Excel fournit une
Et terminez par la même combinaison de formule bien utile : =produitmat(cellules de la
touches : Ctrl+Maj+Entrée. Les résultats s’affi- matrice 2;cellules de la matrice 1).
cheront alors sur les différentes lignes de la zone • Les données doivent être structurées sous
de réponse. forme de matrices : matrice des quantités et
Vous pouvez utiliser la formule =SOMMEPROD matrice des liens de nomenclature entre les
références composant/composé.
(serie1;série2) quand vous souhaitez obtenir le
• Il faut respecter l’ordre de priorité des
produit de deux séries. Dans notre cas, la ma-
matrices et vérifier que le nombre de colonnes
trice 1 et une ligne de la matrice 2 que vous de la matrice 2 égale le nombre de lignes de
disposerez en colonne près de la matrice 1, par l’autre matrice.
exemple.

f fiche 35
Répartir les charges indirectes par centres d’analyse

© Groupe Eyrolles

62 Calculer le budget de production, d’achat et les frais industriels

 CALCULER LE TAUX D’UTILISATION DES ÉQUIPEMENTS
ET IDENTIFIER LES CONSÉQUENCES 17
Le calcul budgétaire ne se conçoit que par itérations successives compte tenu de l’évolution des
nombreuses variables. Le budget de production intègre des problématiques de capacité machine
et de gestion des ressources humaines.

LES OBJECTIFS VISÉS est utilisé à la production, multiplié par le

nombre d’heures d’ouverture durant la journée.
Après avoir calculé les quantités à fabriquer
Le nombre de jours tient compte des fermetures
pour chaque mois, il est important de vérifier
annuelles et des jours de maintenance néces-
que l’organisation industrielle est en capacité de
saires. Le nombre d’heures d’ouverture intègre
répondre à la demande commerciale. Pour cela,
les divers temps d’arrêt (panne, changement de
on définira, pour chaque équipement, les para-
format, bourrage…) et s’aligne sur les accords en
mètres clefs sur lesquels on pourra agir.
matière de gestion du temps de travail.
Tout d’abord : le temps effectif d’ouverture. Ce
On précisera ensuite le temps de l’équipement
temps est un nombre de jours où l’équipement
nécessaire pour la fabrication d’une unité

Cas pratique
À partir du zip, ouvrez le fichier CH4-1, onglet F15 CBN
On obtient pour FILM+ le tableau suivant, qui permet de calculer les taux d’utilisation des équipements dans
la société :

On a défini, dans un premier temps, le nombre de jours ouvrés dans le mois et un temps d’ouverture journalier
uniforme de 10 heures.
En fonction des résultats obtenus et d’une limite fixée à 90 % d’utilisation, on modifie ces variables d’action : variation
des jours de travail par mois (samedis) et de la durée d’ouverture par jour pour rendre réalisable le plan commercial.
© Groupe Eyrolles

Calculer le budget de production, d’achat et les frais industriels 63

FICHE 17
CALCULER LE TAUX D’UTILISATION DES ÉQUIPEMENTS
ET IDENTIFIER LES CONSÉQUENCES

standard. Ce temps gamme pourra être actualisé opterez donc pour un graphique avec le taux
aussi souvent que nécessaire. d’utilisation calculé en histogramme et le taux
On peut ainsi calculer un nombre maximal limite en courbe.
d’unités à produire chaque mois, en divisant le Après avoir sélectionné les données, insérez un
temps effectif d’ouverture par le temps gamme graphique de type histogramme puis, sur la série
unitaire. En divisant les quantités demandées de données Taux limite, modifiez le type de
par le marché avec les quantités maximales, on graphique pour une courbe. Il ne reste plus qu’à
obtient le taux d’utilisation de l’équipement. choisir les couleurs si celles proposées par Excel
Les équipements représentant une masse de frais ne conviennent pas : faites un clic droit sur la
fixes, on cherchera souvent à maximiser le taux série de données à modifier, puis choisissez
d’utilisation pour mieux les amortir. Cependant, le Mise en forme, option Couleur.
taux constaté peut être supérieur au taux maximal.
Si c’est de manière quasi permanente, un investis- CONSEIL
sement dans un nouvel équipement s’imposera. Dans un tableau, les données à sélectionner ne
sont pas toujours juxtaposées. Dans ce cas,
Si le taux excède la limite de manière ponc-
maintenez la touche Ctrl enfoncée et cliquez sur
tuelle, on cherchera à agir sur la durée d’ouver-
la première partie de données à sélectionner,
ture journalière (horaires étendus) et sur le puis sur la deuxième et ainsi de suite. Relâchez
nombre de jours travaillés dans le mois. la touche Ctrl quand la sélection est terminée.
Excel facilite ces calculs que l’on peut présenter
sous forme de tableaux ou de graphiques pour
les rendre plus visuels. POUR RÉSUMER
• Calculer le taux d’utilisation des équi-
COMMENT UTILISER EXCEL ?
pements permet de déterminer précisément les
Vous utiliserez Excel pour structurer le tableau ressources nécessaires à la production d’une
de calculs : capacité et besoin. unité standard.

Vous obtiendrez le taux d’utilisation par simple • Les cellules d’un tableau Excel peuvent être
formatées. Il est ainsi possible de présenter
division entre le besoin et la capacité, et la mise
les données numériques en nombres entiers,
en forme du taux sous forme de pourcentage en
avec des décimales, en unités monétaires ou
cliquant droit sur la cellule et en choisissant dans en pourcentages et de les faire apparaître en
la boîte de dialogue Format de cellule Pourcentage caractères gras, en italique, en couleur...
avec autant de décimales que souhaité. Puis, re- • Utilisez la commande Format de cellule par
copiez cette cellule formatée sur les autres mois. un clic droit ou dans le menu Accueil, Style
de cellules. Le format adopté pour une cellule
Pour les graphiques, il est intéressant de montrer
pourra être recopié sur les autres.
où se situent les limites d’acceptabilité. Vous
© Groupe Eyrolles

f fiche 49 f fiche 50
Structurer et sélectionner les indicateurs Concevoir la page de garde du tableau de bord

64 Calculer le budget de production, d’achat et les frais industriels

 CADENCER LES ACHATS ET VALORISER LE FLUX
18
L’approvisionnement des références achetées peut s’opérer de multiples façons. Nous avons opté
pour un cadencement respectant un niveau de stock fin de mois couvrant les ventes du mois
suivant pour les caméras et un stock de sécurité constant sur les autres références.

LES OBJECTIFS VISÉS pondent aux montants TTC achetés sur lesquels
on applique les termes de crédit négociés.
Le calcul des valeurs achetées servira à établir
le budget de TVA et de trésorerie. En effet, le fait Pour les besoins de l’exercice, nous avons utilisé
générateur de la TVA étant la livraison, on cal- Excel pour le calcul des besoins. En pratique, les
culera pour chaque période le montant de TVA données quantitatives sont issues du système de
déductible (taux de TVA utilisé : 20 %). Pour le gestion de production. Seules les données
budget de trésorerie, les décaissements corres- agrégées sont reprises dans Excel pour des valo-
risations budgétaires.

Cas pratique
À partir du zip, ouvrez le fichier CH8-1, onglets F18 Vol Achats et F18 Valo Achats
Afi n de valoriser les achats et les stocks de caméras HD chez FILM+, on réalise le tableau suivant :
En quantités :

En valeur :
© Groupe Eyrolles

Calculer le budget de production, d’achat et les frais industriels 65

FICHE 18
CADENCER LES ACHATS
ET VALORISER LE FLUX

COMMENT UTILISER EXCEL ?

POUR RÉSUMER
Excel permet de présenter sous forme de tableau
• Pour cadencer les achats, Excel permet
une synthèse mensuelle des flux d’achats valo- de présenter sous forme de tableau une
risés. Dans la mesure où un même tarif d’achat synthèse mensuelle des flux d’achats valorisés.
et un même taux de TVA sont retenus, vous • L’utilisation de la valeur d’une cellule donnée
pourrez dupliquer facilement les formules de dans une chaîne de calculs est fréquente. Pour
calcul en utilisant des cellules figées sur ces éviter des paramétrages répétitifs, Excel a
deux constantes. prévu plusieurs options simplificatrices.
• La touche F4 permet de figer la cellule
Utilisez la touche F4 à chaque fois que vous
sélectionnée ; une autre possibilité consiste à
souhaiterez garder un paramètre constant. nommer la cellule contenant la constante dans
Ainsi, la formule de calcul définie pour un mois, la case en haut à gauche de la feuille de calcul.
par exemple, pourra s’appliquer à tous les autres
mois.

CONSEIL
Une alternative à la touche de fonction F4 est
le paramétrage d’un nom pour une cellule. La
cellule qui contient la constante peut recevoir un
nom spécifique, qui doit être indiqué dans la
case en haut à gauche de la feuille de calcul,
juste en dessous du menu.
Les calculs s’opéreront alors directement dès
que vous saisirez le nom choisi.

f fiche 38
Affecter les coûts directs et indirects aux produits

© Groupe Eyrolles

66 Calculer le budget de production, d’achat et les frais industriels

 CALCULER LES HEURES PRODUCTIVES
19
Le calcul de la masse salariale de la main-d’œuvre directe repose sur la décomposition précise
entre les heures payées et les heures travaillées effectives.

LES OBJECTIFS VISÉS sûr des besoins, mais également de paramètres

spécifiques : dates de prise de congés, temps
Les heures de production de la main-d’œuvre
de formation, heures de maintenance et heures
sont calculées pour mesurer les effectifs néces-
de nettoyage…
saires à mettre en place. Ce calcul dépend bien

Cas pratique
À partir du zip, ouvrez le fichier CH4-1, onglet F19 H productives
Les heures payées par mois sont soigneusement décomposées chez FILM+ afin de déterminer les effectifs
nécessaires, mois par mois. On obtient le tableau suivant :
© Groupe Eyrolles

À partir du besoin en heures de travail, on calcule la main-d’œuvre nécessaire en équivalent temps plein ; puis
on compare le résultat obtenu à l’effectif actuel pour déterminer si des ajustements sont nécessaires : recrutements,
décalage de congés, positionnement des formations…
Le contrôleur de gestion sera particulièrement attentif à ne pas avoir trop de ressources payées non utilisées.

Calculer le budget de production, d’achat et les frais industriels 67

FICHE 19
CALCULER LES HEURES
PRODUCTIVES

Toutes ces données varient selon les périodes. CONSEIL

Cela suppose que le contrôleur de gestion col- Faites attention à ne pas confondre la donnée
lecte, en amont, un historique ou des objectifs affichée avec une donnée qui fait l’objet d’un
précis d’amélioration sur ces différents para- arrondi. En effet, la première garde toutes ses
mètres. décimales éventuelles pour les calculs, tandis que
la seconde se voit corrigée comme souhaité.
Il restera à rechercher comment obtenir le meil-
12,3341*2=24,6682 pourra s’afficher
leur équilibre entre les temps disponibles et les 12,33*2=24,67, mais gardera toutes ses déci-
besoins en ressources. C’est un processus itératif males.
qu’Excel sait bien gérer. En revanche, si vous avez procédé à un arrondi
supérieur à la deuxième décimale, vous obtien-
COMMENT UTILISER EXCEL ? drez 12,34*2=24,68.
Vous utiliserez, dans Excel, la formule =SI pour
définir l’effectif total qui sera à valoriser. En effet,
POUR RÉSUMER
si le besoin en effectif supplémentaire est néga-
• Le calcul des heures productives est
tif, il faudra retenir l’effectif actuel en totalité. En nécessaire pour déterminer les besoins en
revanche, si le besoin est positif, vous l’ajouterez ressources de l’entreprise.
à l’effectif actuel. • Certains calculs sont conditionnés ou doivent
De plus, les données figurant dans le tableau être vérifiés : par exemple la somme des lignes
doit être égale à la somme des colonnes.
peuvent être affichées selon différents formats :
nombre entier, avec décimales, en pourcentage. • Vous pourrez utiliser dans Excel la formule
=SI(condition respectée, réaliser l’opération
Dans Excel, vous pouvez choisir le format de
voulue ; sinon, faire autre chose).
chaque cellule : faites un clic droit, puis sélec-
• Vous pourrez imbriquer dans une chaîne de
tionnez le format approprié. Il vous sera parfois
calculs plusieurs conditions avec cette formule
utile, en effet, d’afficher un résultat négatif dans =SI.
une couleur différente.

f fiche 22 f fiche 32
Calculer la période de récupération Identifier les variables d’ajustement majeures

© Groupe Eyrolles

68 Calculer le budget de production, d’achat et les frais industriels

 CALCULER LA MASSE SALARIALE
DES EFFECTIFS DIRECTS DE PRODUCTION 20
La masse salariale est le premier poste des frais budgétés en termes d’importance. Son calcul peut
être réalisé avec plus ou moins de précision selon les enjeux.

LES OBJECTIFS VISÉS Q des statuts (CDI, CDD, autres) : adapté en cas
d’avantages sociaux très différents ;
La valorisation de la masse salariale des effectifs
directs de production peut s’opérer avec plus ou Q du salaire de chaque salarié : adapté en cas
moins de précision selon les enjeux budgétaires. d’évolution forte de la structure des emplois,
On pourrait calculer la masse salariale à partir : des statuts…

Q d’un coût moyen global unitaire : adapté si la Les éléments de valorisation à prendre en
population est homogène ; compte peuvent être assez nombreux. Il faut
considérer le salaire brut nominal de base com-
Q des types d’emplois occupés : adapté pour
plété de primes : prime de fin d’année (treizième
des niveaux de qualification différents ;

Cas pratique
À partir du zip, ouvrez le fichier CH4-1, onglet F19 H productives
Pour FILM+, on fait l’hypothèse d’un coût moyen unique par salarié (main-d’œuvre directe) avec une augmen-
tation générale de 2 % en avril.
On ajoute des majorations salariales pour horaires étendus ou travail en équipe. Le taux de cotisation salarial est
prévu à 20 % et le taux patronal à 45 %. On obtient, pour le calcul de la masse salariale mensuelle concernant
la main-d’œuvre directe (MOD), le tableau suivant :
© Groupe Eyrolles

La construction de tableaux de calculs successifs (tableau avec les données de base, tableau de calculs intermé-
diaires, tableau de synthèse) peut être facilitée avec la fonction Copier/coller d’Excel.

Calculer le budget de production, d’achat et les frais industriels 69

FICHE 20
CALCULER LA MASSE SALARIALE
DES EFFECTIFS DIRECTS DE PRODUCTION

OPTIONS DE LA FONCTION COPIER/COLLER

mois), prime pour travail de nuit en équipe, COMMENT UTILISER EXCEL ?

prime au mérite, etc. Ces éléments peuvent être
La fonction Copier/coller figurant dans le menu
corrigés, en cours d’année, d’une augmentation
Accueil d’Excel comprend de multiples options.
générale.
Vous pourrez choisir de ne reproduire que des
On viendra ensuite ajouter les charges patro-
formules, des valeurs, des formats… ou l’en-
nales à l’ensemble de ces rémunérations.
semble.
Éventuellement, les frais d’un intéressement aux
résultats (légal ou conventionnel) compléteront La répétition de tableaux aux contenus diffé-
le dispositif. rents se gérera avec la recopie de formats. La
répétition de tableaux au contenu identique
Certains éléments de rémunération, comme les
(valorisation de la masse salariale de différents
primes de fin d’année, sont à abonner tout au
centres de coûts, par exemple) se gérera avec
long de l’année. De plus, les jours travaillés
l’option Formules et formats.
ouvrant un droit à congé payé, une provision
(charge à payer) sera à comptabiliser régulière-
CONSEIL
ment et à ajuster selon le nombre de jours
Utilisez les raccourcis clavier de la fonction
consommés.
© Groupe Eyrolles

Copier/coller : Ctrl+C pour copier et Ctrl+V

Excel décomposera tous ces calculs dans diffé- pour coller. Pour couper, le raccourci est Ctrl+X.
rents tableaux. Le Presse-papiers garde en mémoire les vingt-
quatre dernières copies. Son contenu est acces-
sible via le menu Accueil.

70 Calculer le budget de production, d’achat et les frais industriels

 Module 5

CALCULER LE BUDGET
D’INVESTISSEMENT
ET ÉVALUER LA RENTABILITÉ

L
a création de valeur provient de la sélection minutieuse des projets d’investis-
sement. Cela suppose de bien évaluer leur rentabilité au regard des risques qui
leur sont associés.
© Groupe Eyrolles

73
Fiche 21
1 Présenter un tableau des cash-flows 75

Fiche 22 Calculer la période de récupération 78

Fiche 23
1 Calculer la VAN et le TRI 81

Fiche 24 Effectuer des mesures de sensibilité 84

Fiche 25
1 Identifier les priorités 86
© Groupe Eyrolles
 PRÉSENTER UN TABLEAU DES CASH-FLOWS
21
Un projet d’investissement modifie une situation donnée. Aussi, l’évaluation économique d’un
projet passe par l’établissement des flux de trésorerie comparés entre une situation de départ et
une situation future envisagée.

LES OBJECTIFS VISÉS des cash-flows ». Elle consiste à présenter, dans

un tableau récapitulatif pour chacune des an-
L’évaluation économique des projets d’investis-
nées, les différents flux.
sement passe par l’identification des flux de
Q Les encaissements : économies générées ou
trésorerie associés aux projets. Cette méthode est
gains de trésorerie résultant de ventes supplé-
appelée « méthode des flux nets de trésorerie ou
mentaires.

Cas pratique
À partir du zip, ouvrez le fichier [Link], onglet F21 Recap
On présente pour la société un projet complet d’investissement détaillé en cinq onglets (Synthèse, Analyse
des risques, Flux de trésorerie, Compte de résultat et Détail du montant investi). À chaque onglet correspond un
niveau de détail du projet d’investissement.
© Groupe Eyrolles

– L’onglet Synthèse récapitule les données descriptives et chiffrées du projet ;

– l’onglet Analyse des risques précise la sensibilité de trois variables ;
– l’onglet Flux de trésorerie synthétise tous les flux d’encaissements et de décaissements ;
– l’onglet Compte de résultat renseigne sur la nature et l’origine des gains et des charges liés au projet ;
– enfin, l’onglet Invest détaille, par durée d’amortissement et par année, les montants qui seront investis.

Calculer le budget d’investissement et évaluer la rentabilité 75

FICHE 21
PRÉSENTER UN TABLEAU
DES CASH-FLOWS

Q Les décaissements : à commencer par l’inves- plusieurs autres avec le détail des flux de tréso-
tissement proprement dit, les frais associés au rerie du projet étudié.
projet comme des frais d’entretien, sans ou- Excel permet de constituer un dossier complet
blier les impacts fiscaux relatifs à l’impôt sur avec différents onglets qui échangent des infor-
les sociétés. mations sans qu’il soit nécessaire de les ressaisir.
Le tableau récapitulatif présentera donc tous ces
flux par année et en cumul.
COMMENT UTILISER EXCEL ?
À partir de ce tableau de flux de trésorerie, trois Il est important de lier toutes les données se
indicateurs économiques seront calculés (voir trouvant dans les tableaux de flux de manière à
fiches 22 et 23) : la période de récupération, la pouvoir les recalculer automatiquement en cas
valeur actuelle nette et le taux de rendement de modification d’une variable d’entrée.
interne. Vous procéderez ainsi : saisissez dans la cellule
Il est recommandé de constituer le fichier d’ana- de destination le signe « = », puis positionnez le
lyse de la rentabilité d’un projet en au moins curseur sur la cellule source et confirmez en
deux feuilles de calcul inter-reliées : une page appuyant sur la touche Entrée. La valeur de cette
avec les indicateurs de synthèse, les commen- cellule sera alors automatiquement rapatriée. La
taires et les analyses de sensibilité et une ou cellule source pourra se trouver sur la même

© Groupe Eyrolles

BOÎTE DE DIALOGUE POUR LA CRÉATION D’UN MENU DÉROULANT

76 Calculer le budget d’investissement et évaluer la rentabilité

 FICHE 21
PRÉSENTER UN TABLEAU
DES CASH-FLOWS

feuille ou sur une autre feuille que la cellule de CONSEIL

destination. Pour le menu déroulant, il faut localiser les
Dans notre exemple, le tableau détaillant le valeurs dans la même feuille. Utilisez cette
fonctionnalité, sans en abuser pour autant, dès
montant de l’investissement est en lien avec le
que vous voulez limiter les risques de mauvaise
tableau des flux pour afficher le montant total
interprétation de l’information souhaitée.
de l’investissement sur chaque année.
Nous pouvons caractériser certaines informations,
comme le type de projet ou l’organisation concer-
née, à travers la mise en place d’un menu dérou-
lant ; cela évite une saisie fastidieuse et des erreurs. POUR RÉSUMER
1. Précisez dans une zone séparée (au bas de la • Excel permet de constituer un dossier
feuille) les valeurs souhaitées, par exemple : complet pour présenter les cash-flows (encais-
sements et décaissements) associés à un projet
expansion, économies, remplacement…
d’investissement.
2. Pour chaque cellule où une de ces valeurs doit
• Dans une zone séparée de la feuille de calcul
être indiquée, utilisez la commande Validation Excel, vous préciserez les valeurs souhaitées
de données du menu Données. que doit prendre votre variable.
3. Dans la zone d’options de la boîte de dialogue, • Pour chaque cellule où une des valeurs de
choisissez le critère Liste. cette variable doit être indiquée, utilisez la
commande Validation de données du menu
4. Il ne vous restera plus qu’à choisir la source Données puis, dans la zone d’options de la
en sélectionnant la zone des valeurs (f illus- boîte de dialogue, le critère Liste.
tration ci-contre).

f fiche 22 f fiche 41
Calculer la période de récupération Calculer les provisions de dépréciation des stocks et des
créances client
f fiche 23
Calculer la VAN et le TRI
© Groupe Eyrolles

Calculer le budget d’investissement et évaluer la rentabilité 77

22 CALCULER LA PÉRIODE DE RÉCUPÉRATION

Lors de l’évaluation économique des projets d’investissement, trois indicateurs économiques sont
calculés, dont l’indicateur « période de récupération ».

LES OBJECTIFS VISÉS On peut calculer plusieurs variantes de cet indica-

teur selon que l’on considère ou pas l’actualisation
L’indicateur « période de récupération » mesure
des flux de trésorerie futurs et les aspects fiscaux.
la vitesse de récupération des capitaux initiale-
ment investis. C’est un indicateur facile à com- Nous calculerons la période de récupération basée
prendre et assez usité par les praticiens. sur les flux nets de trésorerie en valeur actuelle et
Cependant, il ne donne qu’une vision à court après impôt sur les sociétés. C’est cette dernière
terme du projet étudié. Excel nous permet de variante qu’il est recommandé de présenter dans
compter, automatiquement et précisément, le un dossier d’investissement. Les capacités de pa-
nombre d’années ou fractions d’années en ramétrage d’Excel vont nous permettre d’obtenir,
fonction de la valeur des flux annuels. de manière automatique, cette information clef.

Cas pratique
À partir du zip, ouvrez le fichier [Link], onglet F22 Flux
On veut calculer la période de récupération pour un projet d’investissement chez FILM+ à partir de ses flux
de trésorerie. Le calcul se fait sur l’onglet F22 Flux aux lignes 44 et 45 et on affiche le résultat en cellule D47.
On obtient le tableau suivant :

© Groupe Eyrolles

On calcule, tout d’abord, le nombre d’années où le flux cumulé de trésorerie demeure négatif, puis la portion
d’année où le flux de trésorerie devient positif. On obtient la période de récupération en additionnant les deux
valeurs obtenues.

78 Calculer le budget d’investissement et évaluer la rentabilité

 FICHE 22
CALCULER LA PÉRIODE
DE RÉCUPÉRATION

COMMENT UTILISER EXCEL ? nant le flux cumulé de trésorerie au point zéro ;

sinon afficher la valeur 0 ».
Pour le calcul de la période de récupération avec
Excel, nous utiliserons deux formules de calcul : La formule =MAX
la formule =SI et la formule =MAX. La formule =MAX recherche la valeur maximale
parmi une série de cellules. Ici, nous recher-
La formule =SI
chons le nombre d’années le plus élevé tel qu’il
La formule =SI est utile pour identifier l’année
a été calculé précédemment en ligne 44. Nous
où les flux de trésorerie deviennent positifs. Sur
affichons donc en cellule D47 la valeur maxi-
la ligne 44 nous reportons le nombre d’années
male d’années où les flux sont négatifs plus la
avec la formule suivante :
portion de l’année suivante.
=SI(condition à respecter ; calcul à opérer si
À partir du zip, ouvrez le fichier CH5-1, onglet
condition vraie ; sinon calcul ou valeur à afficher).
F22 Flux (f illustration ci-dessous)
Dans notre exemple, cela donne :
Cette capture d’écran affiche les formules utili-
=SI(F37<0 ;F7 ;0)
sées pour le calcul des années.
C’est-à-dire : « Si le flux cumulé de trésorerie de
l’année (F37) est négatif, alors afficher le nombre CONSEIL
d’années figurant en F7 ; sinon afficher la valeur En imbriquant la condition SI dans la formule =SI,
0 ». On utilise encore cette formule pour calcu- nous pourrions calculer directement, pour chaque
ler la portion d’année où le flux de trésorerie année, sa quotité : =SI(G37<0;1;SI(F37>0;0;-F37/
devient positif : G36)), soit : « Si le flux cumulé est négatif,
compter 1 année, sinon vérifier si le flux cumulé
=SI(F44<E44;-E37/F36;0)
de l’année précédente est positif ; dans ce cas,
C’est-à-dire : « Si le nombre calculé précédem- afficher la valeur 0, sinon calculer la portion
ment est inférieur au nombre calculé l’année d’année en divisant la valeur absolue du flux cu-
précédente, alors calculer la part d’année ame- mulé de l’année précédente par le flux annuel de
l’année en cours ». Il ne resterait alors qu’à addi-
tionner les montants obtenus.
© Groupe Eyrolles

FORMULES DE CALCUL DES FLUX DE TRÉSORERIE ET DES INDICATEURS ÉCONOMIQUES

Calculer le budget d’investissement et évaluer la rentabilité 79

 Module 6

CALCULER LE BUDGET
DES AUTRES FRAIS
ET CENTRES DE COÛT

A
près avoir établi le budget des achats, le gestionnaire construira le budget
des autres charges à partir de sources d’information variées et le consolidera
dans le budget général. La phase d’analyse pourra alors commencer.
© Groupe Eyrolles

89
Fiche 26
1 Calculer la masse salariale totale 91

Fiche 27 Calculer les amortissements : règle du prorata temporis 93

Fiche 28
1 Consolider les autres frais 95

Fiche 29 Analyser les causes de variations d’un budget à un autre 97

© Groupe Eyrolles
 CALCULER LA MASSE SALARIALE TOTALE
26
Les ressources humaines représentent souvent les ressources principales d’une organisation. Cet
actif immatériel riche de compétences variées caractérise les entreprises dans leur savoir-faire. À
la création de valeur on viendra comparer le coût d’utilisation de ces ressources, c’est-à-dire la
masse salariale.

LES OBJECTIFS VISÉS nales. Ce montant total ainsi obtenu donne la

masse salariale chargée représentant le coût
La masse salariale sera calculée pour chaque
d’utilisation des différentes ressources hu-
centre de coût avec un niveau de détail selon la
maines.
précision souhaitée : à la personne, selon la
qualification, le statut, des critères combinés… Tout comme nous l’avons vu dans la fiche 20
relative à la masse salariale de la main-d’œuvre
On intégrera toutes les charges de salaires,
directe, Excel permet de garder la souplesse
primes et majorations pour obtenir une base
souhaitée en permettant un calcul détaillé dans
brute de rémunération à laquelle on appli-
une feuille et l’importation du résultat dans une
quera un taux global de charges sociales patro-
autre feuille.

Cas pratique
À partir du zip, ouvrez le fi chier CH6-1, onglet F26 MS
On détaille, dans un tableau, le budget mensuel de masse salariale chez FILM+ pour obtenir le résultat suivant :
© Groupe Eyrolles

On pourra procéder de la même manière pour les autres centres de coût de la société : R&D, Commercial et
Support.
Le résultat « Masse salariale totale » du centre concerné sera exporté via un lien Excel avec la feuille récapitulant
les différentes natures de charges de ce centre de coût dans le fichier CH8-1.

Calculer le budget des autres frais et centres de coût 91

FICHE 26
CALCULER LA MASSE
SALARIALE TOTALE

COMMENT UTILISER EXCEL ?

POUR RÉSUMER
Excel permet facilement l’exportation de don-
• En vue d’effectuer les calculs pour
nées vers d’autres feuilles par une simple for- déterminer la masse salariale totale, vous
mule : =nom de la feuille numéro de cellule. pouvez exporter des données vers d’autres
Vérifiez bien que le numéro de cellule n’est pas feuilles en saisissant la formule =nom de la
figé (absence du signe $). feuille numéro de cellule.

Pour l’ouverture des fichiers liés, ouvrez tous les • Vérifiez que le numéro de cellule n’est pas
figé (absence du signe $).
fichiers de manière à garantir leur bonne mise à
• Il est recommandé d’ouvrir, en même
jour. Excel vous le proposera.
temps, tous les fichiers pour que les liaisons
et les mises à jour soient faites de manière
CONSEIL synchrone.
Soyez attentif à ne pas supprimer une donnée
qui est liée à un autre fichier, car alors appa-
raîtrait dans chaque cellule de destination
l’erreur « # REF ! ».
Vous pouvez également constituer dans une
feuille Excel un récapitulatif des liens entre tous
les fichiers et leurs feuilles de calcul.

f fiche 20
Calculer la masse salariale des effectifs directs de production

© Groupe Eyrolles

92 Calculer le budget des autres frais et centres de coût

 CALCULER LES AMORTISSEMENTS :
RÈGLE DU PRORATA TEMPORIS 27
Les ressources en capital représentent souvent le deuxième poste des charges d’exploitation d’une
société industrielle et commerciale. Elles constituent des actifs immobilisés. Leur utilisation constitue donc
une consommation traduite par une charge appelée « amortissement » dans le compte de résultat.

LES OBJECTIFS VISÉS tion prévues (une durée de cinq ans donne par
exemple : 1/5, soit 20 %). On fera débuter la
La budgétisation mensuelle des amortissements
charge d’amortissement à compter de la date de
nécessite la connaissance du flux des actifs im-
mobilisés : actifs en parc, investissements et mise en service, selon la règle du prorata tem-
désinvestissements. poris.

Les amortissements peuvent être calculés selon La méthode de l’amortissement dégressif consi-
deux méthodes : l’amortissement linéaire et dère comme base, pour chaque année, la valeur
l’amortissement dégressif. nette comptable (valeur brute moins les amortis-
La méthode de l’amortissement linéaire consi- sements accumulés). Le taux d’amortissement
dère comme base, pour chaque année, la valeur dégressif est égal au taux linéaire auquel on
historique d’achat (valeur brute). À cette base est applique un coefficient multiplicateur fiscal. La
appliqué un taux d’amortissement qui se calcule règle du prorata temporis s’applique également
en divisant 1 par le nombre d’années d’utilisa- mais à compter de la date d’acquisition.

Cas pratique
À partir du zip, ouvrez le fi chier CH6-1, onglet F27 Amort
On réalise un tableau des projets d’investissement de la société FILM+ qui va permettre de déterminer la
charge d’amortissement par projet, par mois et par centre de coût pour compléter ces différents budgets. On
obtient le tableau suivant :

On retient la méthode des amortissements linéaires. On calcule la charge d’amortissement annuelle pour chaque
projet en respectant la règle du prorata temporis, puis la charge mensuelle de l’année 2014, au regard des
flux sur les actifs immobilisés. Dans un souci de simplification, on considère que chaque mois compte le même
nombre de jours.
© Groupe Eyrolles

Calculer le budget des autres frais et centres de coût 93

FICHE 27
CALCULER LES AMORTISSEMENTS :
RÈGLE DU PRORATA TEMPORIS

À partir des paramètres de durée, de date CONSEIL

d’achat ou de mise en service, et des montants Les fonctions MIN et MAX vous seront égale-
investis, Excel peut être programmé de manière ment utiles. Dans le cas de calculs d’amortisse-
à appliquer strictement les règles comptables ment sur plusieurs années, il faut s’assurer que
citées. l’on ne calcule pas d’amortissement dès que la
période d’utilisation est terminée. Ces fonctions
COMMENT UTILISER EXCEL ? limiteront donc le nombre de mois nécessaires.
La formule =MIN(série de cellules) renvoie le
Compte tenu des dates de mise en service des plus petit nombre de la série de valeurs sélec-
projets d’investissement, il nous faut identifier tionnée. La formule =MAX(série de cellules)
le nombre de mois d’amortissement pour appli- renvoie au plus grand nombre de la série.
quer la règle du prorata temporis. Nous utilise-
rons les fonctions =Mois et =Année pour
transformer une date en un nombre au format
numérique. En effet, en appliquant la formule POUR RÉSUMER
=Mois à la date de mise en service du projet • Le calcul d’une charge d’amortissement
« Achat d’un ERP » qui est 01/12/2014, on ob- doit respecter des règles de durée et nécessite
tiendra le nombre 12. Nous savons donc qu’il l’utilisation de dates.
faudra appliquer 12–12 (nombre de mois cal- • Vous convertirez dans Excel les dates en
culé)+1=1 mois d’amortissement au projet format numérique pour pouvoir réaliser ces
concerné. calculs de durée.
• Les formules =Jour, =Mois et =Année, par
Il en est de même pour le projet d’achat d’un
exemple, convertiront la date du 7 juillet 2014
robot d’emballage : la date de mise en service
en données numériques (7 ;7 ;2014) stockées
étant 01/07/2014, le nombre de mois d’amortis- dans trois cellules différentes.
sement sera calculé ainsi : 12–7+1=6 mois.

f fiche 22
Calculer la période de récupération

© Groupe Eyrolles

94 Calculer le budget des autres frais et centres de coût

 CONSOLIDER LES AUTRES FRAIS
28
Les budgets de frais des différents responsables budgétaires ont pu être préparés sur différentes
feuilles de calcul Excel qu’il va falloir maintenant consolider pour donner une image complète.

LES OBJECTIFS VISÉS COMMENT UTILISER EXCEL ?

Le fonctionnement des organisations repose sur Deux options de consolidation sont possibles.
la délégation. Pour mettre en œuvre la stratégie 1. La première option est adaptée pour la
définie, des pouvoirs d’action s’accompagnent consolidation d’un faible nombre de feuilles :
de moyens financiers. Cette délégation se traduit vous répéterez, dans Excel, la fonction sou-
par des budgets de ressources affectés à chaque haitée : + pour additionner par exemple des
centre de responsabilité. Il s’agit, pour le contrô- charges.
leur de gestion, de s’assurer que ces budgets
2. La seconde option est très utile pour consoli-
n’excèdent pas les limites cadrant le plan à
der de nombreuses feuilles de calcul : vous
moyen terme. Il lui est donc nécessaire d’avoir
utiliserez la fonction Somme de la manière
rapidement une vision consolidée de l’ensemble
suivante : Somme(‘première feuille’:’dernière
des centres de responsabilité. Même si les bud-
feuille’!cellule à additionner).
gets sont détaillés par feuille, Excel permet une
Notez que le signe « ! » n’est utilisé que pour
consolidation qui facilite la tâche du contrôleur
caractériser la dernière cellule à additionner.
de gestion.

Cas pratique
À partir du zip, ouvrez le fi chier CH8-1, onglet F28 Charges FR
Total France, l’organisation principale constituant la société FILM+, regroupe les directions Industrie et Logis-
tique, R&D, Commercial France et la direction Supports. On peut établir le budget des charges par nature pour
cette organisation. On obtient le tableau suivant :
© Groupe Eyrolles

Calculer le budget des autres frais et centres de coût 95

FICHE 28
CONSOLIDER
LES AUTRES FRAIS

CONSEIL
La seconde option ne pourra fonctionner que si
POUR RÉSUMER
les cellules de chaque feuille à additionner • Vous pouvez consolider de nom-
correspondent exactement. Il est donc conseillé breuses feuilles de calcul en utilisant la
de finaliser une feuille « maître » puis d’en faire formule =Somme(première feuille:dernière
des copies : positionnez le curseur sur le nom feuille!cellule à additionner).
de l’onglet à copier, puis faites un clic droit et • Les feuilles doivent avoir le même format,
choisissez Déplacer/copier. Dans la boîte de c’est-à-dire que chaque cellule doit être
dialogue qui s’ouvre, cochez Créer une copie positionnée au même endroit.
et définissez la destination : aussi bien le fichier • Pour en être sûr, copiez l’onglet de réfé-
que la position de la feuille copiée dans le rence : faites un clic droit sur le nom de la
fichier. feuille et choisissez Déplacer/copier.

f fiche 3
Changer rapidement de période et de liaisons

© Groupe Eyrolles

96 Calculer le budget des autres frais et centres de coût

 ANALYSER LES CAUSES DE VARIATIONS
D’UN BUDGET À UN AUTRE 29
Le contrôle de gestion a parmi ses missions la maîtrise des performances par l’analyse des causes.
Cette analyse doit ensuite inciter à passer à l’action, c’est-à-dire corriger les déviations ou déve-
lopper les opportunités.

LES OBJECTIFS VISÉS peuvent être utilisés pour faire apparaître les
différentes causes de variations budgétaires sous
La recherche des causes est souvent empirique
forme de « pont », d’où le nom de « bridge ».
et suppose une excellente connaissance des
métiers et de l’environnement de l’entreprise. COMMENT UTILISER EXCEL ?
Elle nécessite la participation des principaux
Vous disposerez, dans une feuille Excel, les diffé-
managers opérationnels. Il est donc important
rentes causes par nature à saisir. Précisez le niveau
de présenter de façon visuelle les causes de va-
d’impact de chacune d’elles dans une colonne
riations budgétaires pour faciliter la compréhen-
appelée « Variations ». Selon le sens de la varia-
sion des acteurs et obtenir leur adhésion.
tion, reportez automatiquement la valeur absolue
Excel facilite cette tâche avec ses capacités gra- des montants dans la colonne + pour une aug-
phiques, notamment les histogrammes qui mentation et dans la colonne – pour une baisse.

Cas pratique
À partir du zip, ouvrez le fi chier CH6-1, onglet F29 Bridge
Le budget de la société FILM+ a connu une variation entre l’année N et l’année N+1. On décide de présenter
graphiquement l’analyse des causes de cette variation :
© Groupe Eyrolles

Calculer le budget des autres frais et centres de coût 97

FICHE 29
ANALYSER LES CAUSES DE VARIATIONS
D’UN BUDGET À UN AUTRE

FORMAT DES DONNÉES POUR RÉALISER UN GRAPHIQUE BRIDGE

Pour cela, utilisez la formule =SI(H8>=0;H8;"") Vous obtiendrez le graphique sous forme de
dans la colonne +. bridge dans une autre feuille en utilisant les
La formule de mise en valeur absolue, soit =ABS histogrammes empilés (Insérer, Graphique,
(cellule), sera utilisée pour la colonne –, comme Histogramme). Sélectionnez la colonne des
dans notre exemple : causes et les colonnes « Base » et « Variations ».
Une fois le graphique généré, il ne restera qu’à
=+SI(E13="";ABS(H13);"") (f illustration ci-
rendre transparente la base de l’histogramme
dessous)
empilé : faites un clic droit sur la série Base et
Il ne reste plus qu’à calculer, dans la colonne
optez pour un remplissage en blanc.
Base, le montant cumulé. Saisissez dans la co-
lonne « Section » le montant de départ, comme
CONSEIL
le montre l’exemple ci-dessus. Ce montant sera
Les variations pouvant être de tailles différentes
repris automatiquement sur la première ligne
et minimes par rapport au chiffre de base, il est
des causes. Puis vous obtenez le cumul de conseillé d’adapter l’échelle de l’axe des ordon-
chaque ligne de cause en additionnant le mon- nées. Positionnez le curseur sur l’axe, puis faites
tant précédent avec le montant figurant dans la un clic droit et sélectionnez la commande Mise
colonne « Variations » : sur la ligne Prix vous en forme de l’axe avec les options limites mini
obtenez 108, c’est-à-dire : 100 de base + 8 de et maxi souhaitées, ainsi que la valeur de l’axe
© Groupe Eyrolles

quand il coupe l’axe horizontal.

variation positive.

98 Calculer le budget des autres frais et centres de coût

 FICHE 29
ANALYSER LES CAUSES DE VARIATIONS
D’UN BUDGET À UN AUTRE

POUR RÉSUMER
• La prise de décision d’un manager repose sur des modèles bâtis sur l’identification des causes.
• Excel facilite la visualisation des causes de variation budgétaire avec ses possibilités graphiques : un
« bridge » sera obtenu en utilisant les histogrammes empilés Insérer-Graphique-Histogramme.
• Une fois que vous aurez créé l’histogramme empilé, rendez sa base transparente.

f fiche 53
Réaliser les graphiques de simulation
© Groupe Eyrolles

Calculer le budget des autres frais et centres de coût 99

 Module 7

CALCULER LE BUDGET
DE TRÉSORERIE

L
e budget de trésorerie va synthétiser les flux d’encaissements et de décaisse-
ments résultant des budgets amont : ventes, achats, investissements et frais.
© Groupe Eyrolles

101
Fiche 1
30 Calculer les flux en TTC et appliquer le décalage lié
aux conditions de paiement 103

Fiche 31 Consolider les encaissements

et décaissements de chaque mois 105

Fiche 32
1 Identifier les variables d’ajustement majeures 108

Fiche 33 Simuler les options possibles 111

Fiche 34
1 Calculer un escompte bancaire 114
© Groupe Eyrolles
 CALCULER LES FLUX EN TTC ET
APPLIQUER LE DÉCALAGE LIÉ AUX CONDITIONS DE PAIEMENT 30
Les flux de trésorerie doivent être exprimés toutes taxes comprises et intégrer le décalage lié aux
termes de paiement contractualisés.

LES OBJECTIFS VISÉS de détailler l’origine que chaque flux : par

exemple, l’encaissement des ventes du mois de
Les budgets de base – ventes, achats, charges,
janvier sera reporté sur une ligne spécifique.
investissements – vont donner lieu à une conso-
Puis nous ferons figurer, en colonnes, les mois
lidation dans le budget de trésorerie en identi-
effectifs d’encaissement, compte tenu des pour-
fiant séparément les encaissements des
centages de répartition sur chaque mois.
décaissements.
Considérons par exemple des ventes pour jan-
Il est donc opportun, une fois le budget de base
vier de 1 000 € HT avec un taux de TVA à 20 %.
défini, de procéder au calcul du montant en-
Si les termes de paiement sont pour 50 % des
caissé ou décaissé, selon le cas. Ces flux de
ventes au comptant, 30 % à 30 jours et le solde
trésorerie doivent être exprimés toutes taxes
à 60 jours, nous appliquerons la règle énoncée
comprises. Nous appliquerons ensuite à ces
ci-dessus.
montants le décalage des termes de paiement
convenus contractuellement : paiement comp- Sinon, nous pourrons grouper sur une même
tant, à 30 jours date de facture… ligne les montants TTC successivement encais-
sés. Excel présente différentes possibilités pour
Si 100 % des ventes d’un mois ne sont pas en-
répondre à ces cas de figure.
caissés sur une seule échéance, il est conseillé

Cas pratique
À partir du zip, ouvrez le fi chier [Link], onglet F30 Exemple encaissement
On présente les deux possibilités pour les encaissements : le cas du paiement en termes homogènes et le cas
d’une multitude d’échéances.
© Groupe Eyrolles

Calculer le budget de trésorerie 103

FICHE 30
CALCULER LES FLUX EN TTC ET APPLIQUER
LE DÉCALAGE LIÉ AUX CONDITIONS DE PAIEMENT

COMMENT UTILISER EXCEL ? sement du mois de janvier, vous aurez la for-

mule : =$G$12*$C$15.
Premier cas
Dans le cas où les termes de paiement sont ho- CONSEIL
mogènes sur toute la période budgétaire, vous Dans le premier cas, si des ventes font l’objet
utiliserez, dans Excel, la formule =Décaler qui d’avances, prévoyez des colonnes vides avant
permet de paramétrer le déplacement les valeurs le premier mois, sinon ce sont les textes de la
de cellules contenant les montants TTC. Dans la première colonne qui apparaîtront ! Ne mélan-
gez pas les types de décalage (mois même,
zone des encaissements (en jaune dans
avance, report) afin d’éviter des erreurs (oubli
l’exemple), saisissez : ou une double prise en compte).
=DECALER(cellule à déplacer;0;cellule conte- Dans le second cas, présentez les différents
nant le nombre de colonnes pour le déplace- termes de paiement en ligne pour automatiser
ment). plus facilement les calculs en recopiant juste la
formule (onglet BU encaissements TTC France).
Voici la signification des valeurs :
Q 0 : aucun déplacement ;
Q 1 : on avance d’une colonne ;
–1 : on recule d’une colonne.
Q
POUR RÉSUMER
Second cas • Le calcul d’un budget des encaissements
ou décaissements suppose d’appliquer aux
Dans le cas où les échéances sont multiples,
ventes ou aux achats un décalage correspon-
vous pourrez faire figurer les différents termes dant aux termes de paiement négociés.
dans une zone séparée (dans notre exemple, sur • Excel fournit une aide précieuse avec la
la gauche du tableau). Dans le tableau des en- formule =DECALER.
caissements, utilisez la formule de calcul multi- • La cellule à décaler sera précisée, puis
pliant le montant TTC du mois par le pourcentage le nombre de changements de lignes ou le
correspondant au mois approprié en figeant les nombre de colonnes.
cellules avec la touche F4. Ainsi, pour l’encais-

f fiche 13 f fiche 18
Consolider les ventes dans différentes devises : utilisation de Cadencer les achats et valoriser le flux
tables
© Groupe Eyrolles

104 Calculer le budget de trésorerie

 CONSOLIDER LES ENCAISSEMENTS ET
DÉCAISSEMENTS DE CHAQUE MOIS 31
Les différents budgets de base, comme le budget des ventes et le budget des achats, entraînent
des flux de trésorerie que l’on peut synthétiser dans un tableau récapitulatif des encaissements et
des décaissements.

LES OBJECTIFS VISÉS le recours à de l’escompte bancaire (voir

fiches 32 à 34).
Les différents flux de trésorerie se déclinent en
trois catégories : Excel permet ainsi d’identifier rapidement et
clairement, dans une seule feuille de calcul, les
Q les flux d’exploitation ;
principales options sur lesquelles le décideur
Q les flux d’investissement ; devra se prononcer.
Q les flux de financement.
COMMENT UTILISER EXCEL ?
En effet, nous pourrons ainsi vérifier si l’exploi-
tation permet de générer suffisamment de cash Vous utiliserez toute la palette de mise en forme
pour financer les investissements nécessaires à d’un tableau dans Excel. Les calculs à paramé-
la préparation du futur. Le montant résiduel est trer se résument à des sommes dans le tableau
appelé « free cash-flow ». S’il est négatif, il fau- de trésorerie. Vous reprendrez automatique-
dra alors recourir à des financements externes ment, dans la trésorerie de début de période, le
par appel aux actionnaires ou par l’endettement montant calculé pour la période précédente.
(flux de financement). Dans le tableau du budget de TVA, il sera néces-
Les flux d’exploitation étant exprimés en valeur saire de conditionner le montant de la TVA à
TTC, on aura calculé, préalablement, un budget payer selon la présence ou l’absence d’un crédit
spécifique pour la TVA en comparant chaque de TVA avec la formule =SI (condition ; action 1 ;
mois le montant de TVA collectée et le montant sinon action 2).
de TVA déductible.
Ces différents flux sont à établir par devises pour CONSEIL
savoir si des couvertures de change à terme Il est préférable de paramétrer les sous-totaux
seront nécessaires. mensuels en additionnant les différents flux. Il faut
donc définir une convention sur le signe des
Le tableau final obtenu va permettre au déci- différents flux : signe positif pour les encaisse-
deur d’optimiser sa trésorerie en décalant des ments et signe négatif pour les décaissements.
achats ou des investissements, en mesurant Cela évite des erreurs involontaires de paramé-
l’impact des termes de paiement, et en simulant trage qui ne sont pas toujours faciles à identifier.
© Groupe Eyrolles

Calculer le budget de trésorerie 105

FICHE 31
CONSOLIDER LES ENCAISSEMENTS ET
DÉCAISSEMENTS DE CHAQUE MOIS

Cas pratique
À partir du zip, ouvrez le fi chier CH7-1, onglet BU trésorerie
On présente pour la société FILM+ le tableau mensuel des flux de trésorerie par nature en partant de la
trésorerie d’ouverture et en calculant le solde mensuel cumulé.
Sans entrer dans les détails de la fiscalité française, on considérera un alignement sur le régime des débits (fait
générateur de la TVA = la livraison) et un taux unique à 20 % du montant HT.

Le budget de TVA a été calculé préalablement dans l’onglet BU TVA.

© Groupe Eyrolles

On prévoit le cas d’un crédit de TVA, c’est-à-dire quand la TVA déductible est supérieure à la TVA déclarée. En
effet, dans ce cas, aucune TVA n’est à payer mais l’entreprise bénéficie d’un report du crédit sur le mois suivant.

106 Calculer le budget de trésorerie

 FICHE 31
CONSOLIDER LES ENCAISSEMENTS ET
DÉCAISSEMENTS DE CHAQUE MOIS

POUR RÉSUMER
• La conception d’un tableau de flux de trésorerie doit être rigoureuse et respecter des règles de report
de données et de consolidation.
• Il vous est recommandé de faire figurer les encaissements en valeurs monétaires positives et les décaisse-
ments en valeurs monétaires négatives.
• Puis il ne restera qu’à additionner les différents flux avec la formule =Somme(série voulue).

f fiche 32 f fiche 34
Identifier les variables d’ajustement majeures d’un budget de Calculer un escompte bancaire
trésorerie

f fiche 33
Simuler les options possibles
© Groupe Eyrolles

Calculer le budget de trésorerie 107

32 IDENTIFIER LES VARIABLES D’AJUSTEMENT MAJEURES

Le processus budgétaire vise notamment à anticiper sur les événements futurs et à rechercher à
coordonner la mise en œuvre de solutions optimales. Le budget de trésorerie est d’une grande
utilité, car il synthétise l’ensemble des budgets fondateurs tels que les budgets des ventes, des
achats, des investissements…

LES OBJECTIFS VISÉS relative de chacun d’entre eux et mesurer leur

sensibilité à l’équilibre général.
Il est critique de repérer les principales variables
Excel facilite cette identification pour le contrô-
influant sur les flux de trésorerie.
leur de gestion. Nous pouvons mettre en évi-
Nous soulignons ici l’importance de bien détail- dence les périodes où la trésorerie de fin de
ler par nature l’ensemble des flux qui doivent période excède une norme définie : quand le
apparaître dans le tableau récapitulatif de la montant est négatif ou quand il dépasse un
trésorerie. On peut ainsi mesurer l’importance certain seuil, par exemple.

Cas pratique
À partir du zip, ouvrez le fi chier CH7-1, onglet BU trésorerie
On cherche à déterminer les variables d’ajustement majeures du budget de trésorerie des directions managé-
riales de FILM+ basées en France. On obtient le tableau suivant :

© Groupe Eyrolles

108 Calculer le budget de trésorerie

 FICHE 32
IDENTIFIER LES VARIABLES
D’AJUSTEMENT MAJEURES

COMMENT UTILISER EXCEL ? tants hors norme. Pour cela, allez dans le menu
Accueil et cliquez sur la fonction recherchée.
Vous utiliserez la mise en forme des cellules.
1. Sélectionnez le champ des valeurs à analyser
Pour bien identifier un solde négatif, Excel
puis, dans la boîte de dialogue, Règles des
propose un paramétrage des cellules. Vous
valeurs plus ou moins élevées. La ou les va-
pouvez faire afficher en rouge tout montant né-
leurs répondant au critère sélectionné s’affi-
gatif. Pour cela, positionnez votre curseur sur
cheront avec le format que vous pourrez
une cellule et cliquez droit : une boîte de dia-
personnaliser. Des barres, des icones ou des
logue apparaît. Choisissez la catégorie Nombre
nuances de couleur permettent de rendre
ou Monétaire et la forme souhaitée en rouge.
encore plus visuelles les données critiques.
La mise en forme conditionnelle est aussi pos-
2. Pour définir une norme spécifique, il faudra
sible mais nécessite un paramétrage plus éla-
toujours, dans la boîte de dialogue, cliquer sur
boré. Elle permet de visualiser automatiquement
Nouvelle règle de mise en forme. Une nou-
les dix valeurs les plus élevées ou de choisir une
velle boîte de dialogue propose alors six
couleur particulière pour identifier des mon-
options (f illustration ci-dessous).
© Groupe Eyrolles

LISTE DES OPTIONS DE MISE EN FORME CONDITIONNELLE

Calculer le budget de trésorerie 109

FICHE 32
IDENTIFIER LES VARIABLES
D’AJUSTEMENT MAJEURES

N’abusez pas des formats de mise en forme, car

votre tableau deviendrait vite illisible… ce qui POUR RÉSUMER
est justement l’inverse de l’effet escompté. • Les variables d’ajustement majeures
L’idéal est de mettre en évidence 20 % des va- doivent être identifiées pour anticiper sur les
événements futurs.
leurs en nombre présentant 80 % de l’impact
total en valeur. • Avec la fonction Mise en forme condi-
tionnelle des cellules, Excel, dans son menu
Accueil, vous permet de repérer visuellement
CONSEIL et automatiquement les dix valeurs les plus
Les tableaux de trésorerie comprenant de nom- élevées ou de choisir une couleur particulière
breuses périodes sont de grande taille, ce qui pour identifier des montants hors norme.
peut poser problème lors de l’impression. Pensez • Excel vous propose une large liste de règles
à afficher l’aperçu avant impression : choisissez à définir dans la boîte de dialogue Nouvelle
Fichier, Imprimer et précisez les paramètres de règle de mise en forme.
mise en page, notamment l’orientation (l’orienta-
tion paysage est recommandée pour bénéficier
de toute la largeur) et la mise à l’échelle (qui
consiste à ajuster la feuille à une page).

f fiche 43
Procéder à la revue analytique des comptes

© Groupe Eyrolles

110 Calculer le budget de trésorerie

 SIMULER LES OPTIONS POSSIBLES
33
Les principales variables étant identifiées, l’étape suivante consiste à mesurer quel plan d’action
peut modifier sensiblement le niveau prévu de trésorerie. Ces changements sont, bien sûr, assortis
de risques ; il s’agit donc de tester assez largement toutes les possibilités avant de prendre la
décision finale.

LES OBJECTIFS VISÉS COMMENT UTILISER EXCEL ?

On pourra commencer par hiérarchiser les types Les boutons curseurs
d’action en commençant par ceux touchant à
Vous utiliserez les boutons curseurs (voir
l’exploitation, puis au plan d’investissement et,
fiche 15). À partir du menu Développeur, insé-
enfin, aux options de financement. Il est, bien
rez pour chaque variable un bouton curseur.
sûr, possible de cumuler les actions pour avoir
Ensuite, faites un clic droit sur le bouton : dans
un effet amplifié.
la boîte de dialogue qui apparaît, saisissez les
Le contrôleur de gestion aura une vision globale
valeurs demandées dans l’onglet Contrôle.
en utilisant les boutons curseurs d’Excel qui
vont lui permettre de corriger les variables de Pour pouvoir simuler les impacts des change-
0 % à 100 % et en mesurant leur effet sur un ments souhaités, il est recommandé d’ajouter au
graphique de synthèse. fichier une copie des tableaux de trésorerie

Cas pratique
À partir du zip, ouvrez le fi chier CH7-1, onglet Simu
Une simulation est réalisée au sein de FILM+ pour déterminer comment équilibrer la trésorerie. On obtient le
tableau suivant :

On considère que les principales variables touchent aux délais de règlement (client et fournisseur) et aux niveaux
d’achats, de charges, d’investissements et de dividendes versés.
© Groupe Eyrolles

En effet, avant de rechercher des sources de financement complémentaires, il convient de s’interroger sur le
fonctionnement actuel et de mesurer les possibilités d’optimisation. Peut-on renégocier les conditions de paiement
avec certains clients ou fournisseurs ? Peut-on décaler ou réduire les achats, certaines charges, les investissements ?
Les boutons curseurs vont traduire les changements escomptés et le graphique montrera leur effet en comparant
la situation de la trésorerie initiale (courbe rouge) avec la nouvelle situation (histogramme bleu).

Calculer le budget de trésorerie 111

FICHE 33
SIMULER LES OPTIONS
POSSIBLES

(onglets Simu encaissements, Simu décaisse- au montant mensuel de trésorerie résultant des
ments, Simu trésorerie) de manière à bien garder simulations. Pour rendre cette comparaison vi-
la trace des calculs originaux. suelle, nous la présenterons sous forme de gra-
Il ne reste plus qu’à appliquer les nouveaux phique.
paramètres des variables à ces tableaux de simu- Sélectionnez donc les différents mois et les
lation. Par exemple, les décaissements des montants de trésorerie initiale, puis insérez un
nouveaux montants d’investissement seront graphique sous forme de courbe ; pour cela,
égaux à la valeur du bouton curseur divisée par dans le menu Insertion, choisissez le type de
100 et multipliée par les montants initiaux. Vous graphique souhaité. Une fois le graphique inséré,
obtenez ainsi un nouveau tableau de trésorerie en cliquant droit sur celui-ci, vous allez ajouter
qui synthétise tous les changements que vous une deuxième série de données – les données
voulez simuler. de l’onglet Simu Budget de trésorerie – en
confirmant Sélectionner les données, puis en
Le graphique de synthèse appuyant sur le bouton Ajouter pour préciser les
Vous avez obtenu un montant mensuel de tréso- données de la nouvelle série (f illustration ci-
rerie initiale que vous allez pouvoir comparer dessous).

© Groupe Eyrolles

BOÎTE DE DIALOGUE POUR INSÉRER UNE NOUVELLE SÉRIE DE DONNÉES

112 Calculer le budget de trésorerie

 FICHE 33
SIMULER LES OPTIONS
POSSIBLES

CONSEIL
Pour modifier les valeurs des variables sélection- POUR RÉSUMER
nées, appliquez soit directement un pourcen- • Pour simuler les options possibles dans
tage qui donnera le résultat souhaité (et qui un budget de trésorerie, un graphique vaut
variera de 0 à 100), soit un pourcentage de mieux qu’un long discours.
variation sur les valeurs initiales. • Excel permet de faire figurer sur un même
Si vous souhaitez ne modifier que quelques graphique plusieurs séries de données et de
valeurs, alors les boutons curseurs sont inutiles personnaliser leur représentation : courbe,
et il convient, dans ce cas, de modifier directe- histogramme, aire…
ment la valeur souhaitée dans le tableau de • L’enchaînement à suivre est le suivant :
simulation. insérez un graphique puis, en cliquant droit
sur celui-ci, ajoutez-en un deuxième avec sa
série de données.

f fiche 15
Mesurer la sensibilité des variables et calculer le levier opérationnel
© Groupe Eyrolles

Calculer le budget de trésorerie 113

34 CALCULER UN ESCOMPTE BANCAIRE

Les soldes d’une trésorerie erratique, excédentaire puis déficitaire avant de redevenir positive
peuvent être traités par un emprunt à court terme annuel. Mais pourquoi payer des frais financiers
pendant un an alors que le besoin n’est que de quelques mois ? Une réponse à ce genre de
situation : l’escompte bancaire.

LES OBJECTIFS VISÉS conséquent, le montant perçu par l’entreprise

sera net des frais. Elle devra donc escompter un
L’escompte bancaire consiste pour l’entreprise à
montant suffisant de créances de manière à
mobiliser une partie de ses créances commer-
couvrir ces frais.
ciales en les transférant à sa banque qui procé-
dera à leur paiement immédiat, déduction faite Le montant à escompter se calculera ainsi :
des intérêts et commissions. Besoin mensuel en trésorerie
x= .
À la différence de l’emprunt, les frais d’escompte (1 – taux d’escompte mensuel)
sont prélevés sur le nominal de la créance. Par

Cas pratique
À partir du zip, ouvrez le fi chier CH7-1, onglet Simu BU trés.
On veut calculer l’escompte bancaire nécessaire pour que la société FILM+ évite de présenter un découvert à
la fin de certains mois. On obtient le tableau suivant :

© Groupe Eyrolles

114 Calculer le budget de trésorerie

 FICHE 34
CALCULER UN ESCOMPTE
BANCAIRE

Naturellement, le montant des créances com- CONSEIL

merciales escomptées majoré des frais sera Dans le cas où l’escompte nécessiterait la mobi-
soustrait des créances de la période suivante. lisation de plusieurs mois de créances commer-
Avec Excel, la trésorerie pourra ainsi s’équilibrer ciales, il faudra calculer la durée de l’escompte
automatiquement et les frais d’escompte seront et déterminer à chaque mois l’escompte restant
à apurer. Le coût sera alors calculé :
connus.
Montant du besoin de trésorerie / (1 – taux)m ,
COMMENT UTILISER EXCEL ? m étant le nombre de mois. Cela pourra être
effectué pas à pas pour éviter des paramétrages
Vous utiliserez la formule de calcul du montant trop complexes.
à escompter après avoir déterminé le solde de
trésorerie de chaque mois. À chaque fois que le
montant mensuel sera déficitaire (utilisation de
la condition =SI), vous appliquerez la formule
de calcul ; sinon, aucun montant d’escompte
POUR RÉSUMER
n’aura à figurer. Vous pouvez vérifier que le
• L’équilibrage d’un budget mensuel
solde de trésorerie après escompte est bien égal
de trésorerie peut passer par le recours à
à zéro. l’escompte bancaire.
Notez également que le montant escompté est • Une formule de calcul du coût de cet
reporté en flux négatif le mois suivant sur la ligne escompte et du montant à escompter doit être
des flux d’exploitation Créances escomptées. mise en œuvre dès lors que le solde mensuel
est négatif.
En ce qui concerne les montants d’escompte
• Avec Excel, nous pouvons utiliser la formule
importants, il sera nécessaire de vérifier si le
de condition =SI et mettre en évidence les
montant des créances commerciales du mois mois problématiques avec une mise en forme
suivant est suffisant pour couvrir le déficit de conditionnelle des cellules.
trésorerie.

f fiche 56
Étudier l’opportunité de différentes alternatives
© Groupe Eyrolles

Calculer le budget de trésorerie 115

 Module 8

CALCULER LES COÛTS

L
e calcul des coûts fait partie des techniques fondamentales de la gestion en
fournissant les données permettant d’apprécier la consommation de ressources
et le niveau de rentabilité.
© Groupe Eyrolles

117
36 CALCULER LES COÛTS UNITAIRES D’UNITÉ D’ŒUVRE

Le calcul du coût unitaire d’unité d’œuvre va permettre d’affecter, à chaque objet de coût, sa part
de charges indirectes.

LES OBJECTIFS VISÉS Connaissant les charges indirectes par centre

d’analyse principal, et en calculant le nombre
Le système de calcul des coûts complets dis-
total d’unités d’œuvre généré par ce centre, nous
tingue les charges directes des charges indi-
pouvons déterminer le coût indirect d’une unité
rectes ; celles-ci n’ayant naturellement aucun
d’œuvre. Nous disposons ainsi du moyen d’af-
lien évident et quantifié avec les objets de coût,
fecter les charges indirectes à tout produit.
elles doivent être réparties, dans un premier
temps, sur des centres d’analyse (répartitions Chaque produit va nécessiter un nombre donné
primaire et secondaire vues en fiche 35). d’unités d’œuvre dans les centres par lesquels il
va passer pour être fabriqué et commercialisé.
C’est grâce aux centres d’analyse que l’on va
Nous lui affecterons donc la valeur de chaque
pouvoir ensuite affecter une quote-part de
unité d’œuvre consommée.
charge indirecte à chaque produit (objet de
coût). En effet, certains de ces centres ont une Tous ces calculs se présentent sous forme de
activité qui peut se mesurer selon une caracté- tableaux de données. Excel est donc tout à fait
ristique du produit (kilos de matière, heure approprié pour ce type de travaux. Il présente
machine, etc.) appelée « unité d’œuvre ». Il en outre l’avantage de permettre une visualisa-
s’agit des centres dits principaux. tion d’ensemble pour le contrôleur de gestion.

Cas pratique
À partir du zip, ouvrez le fi chier CH8-1, onglet F35 Calcul coût C Analyse
On va calculer le coût unitaire pour une caméra HD produite par FILM+. On obtient le tableau suivant :

© Groupe Eyrolles

122 Calculer les coûts

 FICHE 36
CALCULER LES COÛTS UNITAIRES
D’UNITÉ D’ŒUVRE

COMMENT UTILISER EXCEL ? indiquera la liste des cellules générant le pro-

blème. Cependant, les références circulaires
Excel calcule le coût unitaire d’unité d’œuvre.
peuvent être utilisées pour réaliser des calculs
Ce coût peut présenter de nombreuses déci-
itératifs (charge financière dépendant du solde
males, comme dans l’exemple suivant :
de trésorerie, par exemple : charge de 0,1 % du
23,4432789.
solde négatif). Pour procéder à des calculs itéra-
On distinguera la notion d’affichage : ne faire tifs, activez ce mode de calcul dans la rubrique
apparaître que les deux premières décimales, Formules des Options d’Excel.
soit 23,44. Mais tous les calculs se feront avec la
totalité des décimales.
CONSEIL
Si vous souhaitez ne travailler que sur des Insérez dans les tableaux des formules de véri-
chiffres entiers ou arrondis avec une certaine fication. Par exemple, la somme des dépenses
précision, comme une seule décimale au niveau indirectes reste la même après chaque étape de
inférieur, Excel propose les formules =ARRONDI répartition. Vous pourrez donc ajouter une co-
ou =[Link] et =[Link]. Il suffit lonne Total ou utiliser la formule conditionnelle
=SI pour afficher le niveau d’erreur.
de préciser la cellule qui doit être arrondie et à
quelle précision.
Ainsi, si vous souhaitez arrondir au niveau infé-
rieur à deux décimales la valeur 23,4432789, POUR RÉSUMER
vous saisirez ARRONDI. INF (cellule contenant • Les centres d’analyse permettent d’impu-
la donnée;2) pour obtenir 23,44. ter une quote-part des charges indirectes à
chaque produit.
Si vous souhaitez arrondir au niveau supérieur
• Les calculs d’Excel sur des données quantita-
à une décimale la valeur 23,4432789, vous sai-
tives peuvent produire des résultats numériques
sirez =[Link] (cellule contenant la
avec de nombreuses décimales. Le format des
donnée;1) pour obtenir 23,5. cellules d’Excel permet de n’en afficher que
Vous rencontrerez peut-être le cas d’une réfé- quelques-unes mais toutes les décimales seront
rence circulaire si une de vos formules fait réfé- utilisées pour de futurs calculs.
rence à son propre résultat. Excel affichera un • Si vous ne voulez en retenir qu’une partie,
utilisez les formules =[Link] ou
message d’avertissement. Dans le menu sous
=[Link] en précisant le nombre de
l’onglet Formules, Vérification des erreurs, cli-
décimales à conserver.
quez sur Références circulaires et Excel vous

f fiche 35
Répartir les charges indirectes par centre d’analyse
© Groupe Eyrolles

Calculer les coûts 123

37 CALCULER LE COÛT DES ACTIVITÉS ET DES PROCESSUS

La méthode de calcul des coûts complets par les centres d’analyse est particulièrement adaptée
aux produits banalisés et à grands volumes. La recherche d’une meilleure compréhension des
facteurs de complexité au sein des entreprises conduit à l’utilisation d’une autre méthode fondée
sur l’identification des activités principales.

LES OBJECTIFS VISÉS COMMENT UTILISER EXCEL ?

Pour chaque activité, un facteur explicatif de Pour procéder au regroupement des inducteurs
consommation des ressources est recherché ; il et calculer leur coût unitaire, vous utiliserez les
s’agit des inducteurs. Par exemple, le nombre de tableaux croisés dynamiques.
fournisseurs : plus l’organisation Achat gérera Après avoir sélectionné la zone des données
de fournisseurs, plus elle consommera de res- dans le menu d’Excel, choisissez Insérer, Tableau
sources. croisé dynamique. Vous pouvez insérer le tableau
Avec la méthode ABC (Activity Based Costing), dans la feuille de calcul en cours ou dans une
on peut regrouper les activités en processus dès nouvelle feuille. Il faut ensuite choisir les
lors qu’elles sont sensibles au même inducteur. champs à faire figurer en lignes ou en colonnes.
Si des activités telles que négocier avec les Cochez la case correspondante et déplacez le
fournisseurs, auditer la qualité des fournisseurs champ.
et payer les fournisseurs ont comme inducteur Les données quantitatives sont à transférer en
le nombre de fournisseurs, alors on définira le zone Valeurs ; par défaut, il vous sera proposé
processus « gestion des fournisseurs » compre- d’afficher la somme des valeurs sélectionnées
nant ces trois activités. (f illustration page 126).
Le calcul du coût d’un produit s’inscrit donc
dans une logique de consommation : les res- CONSEIL
sources sont consommées par les activités re- On peut également prévoir dans le tableau
groupées en processus et ces processus sont croisé dynamique d’inclure un filtre pour ne
consommés par les produits pour répondre aux sélectionner qu’une partie des données.
attentes des clients. Attention : si, pour procéder à d’autres calculs,
vous voulez utiliser les résultats figurant dans un
Excel présente l’avantage de la souplesse. On
tableau croisé dynamique, il vous faudra reco-
pourra plus facilement adapter la configuration
pier les valeurs de ce dernier, car Excel fige
des processus, notamment en phase de décou- chaque cellule, ce qui vous obligerait à saisir
verte de cette méthode. pour chaque ligne une formule de calcul.
© Groupe Eyrolles

124 Calculer les coûts

 FICHE 37
CALCULER LE COÛT DES ACTIVITÉS
ET DES PROCESSUS

Cas pratique
À partir du zip, ouvrez le fi chier CH8-1, onglet F37 Coût ABC
On part du budget de chaque centre de coût de la société FILM+ pour identifier ses activités principales. Selon
le type de ressource consommée (temps passé, matériel…), on affecte celle-ci sur les activités (dans la colonne
« Coût des activités »), puis on vient préciser l’inducteur d’activité. Cela permet de regrouper toutes les activités
ayant le même inducteur et de calculer le coût unitaire de chaque inducteur.
On obtient le tableau suivant :

À partir de la cartographie des activités et de leurs inducteurs, on définit des processus par regroupement des
activités portant le même inducteur avec un tableau croisé dynamique comme ci-dessous :
© Groupe Eyrolles

Calculer les coûts 125

FICHE 37
CALCULER LE COÛT DES ACTIVITÉS
ET DES PROCESSUS

BOÎTE DE DIALOGUE EXCEL POUR LA RÉALISATION D’UN TABLEAU CROISÉ DYNAMIQUE

POUR RÉSUMER
• Pour croiser des données entre elles, Excel vous donne la possibilité d’insérer des tableaux croisés
dynamiques (TCD) dans une feuille de calcul.
• L’ensemble des données nécessaires au TCD doit avoir été sélectionné auparavant.
• Vous devez ensuite préciser quel champ faire figurer dans le TCD, son emplacement (en ligne ou colonne)
et quelle opération numérique opérer (somme ; nombre, etc.).

f fiche 44
Rechercher les principaux écarts dans des tableaux
© Groupe Eyrolles

126 Calculer les coûts

 AFFECTER LES COÛTS DIRECTS ET INDIRECTS AUX PRODUITS
38
La dernière étape de calcul consiste à affecter l’ensemble des charges (directes et indirectes) aux
produits.

LES OBJECTIFS VISÉS coût par unité d’œuvre dans chaque centre
d’analyse (méthode traditionnelle), soit le coût
La méthode de calcul des coûts complets affecte
de chaque inducteur (méthode ABC).
l’ensemble des charges récurrentes aux diffé-
rents produits : les charges directes ainsi que les Le calcul du coût indirect de chaque produit
charges indirectes. s’opérera donc en multipliant le nombre d’uni-
tés d’œuvre ou d’inducteurs nécessaires par le
Les charges directes telles que la matière pre-
coût unitaire des unités d’œuvre/inducteurs.
mière sont connues de manière parfaite pour
chacun des produits que l’on cherche à valori- Ces valorisations sont faites dans le système de
ser. En revanche, pour les charges indirectes, il gestion de production ou dans le progiciel de
est nécessaire de calculer préalablement soit le gestion intégré de l’entreprise quand le nombre

Cas pratique
À partir du zip, ouvrez le fi chier CH8-1, onglet F38 CC camera (ABC)
On souhaite calculer les coûts directs et indirects des caméras HD produites par la société FILM+. Avec deux
onglets – l’un avec la méthode des centres d’analyse et l’autre avec la méthode ABC –, on obtient le tableau
suivant :
© Groupe Eyrolles

Calculer les coûts 127

FICHE 38
AFFECTER LES COÛTS DIRECTS ET
INDIRECTS AUX PRODUITS

de références de produits finis est important. Si CONSEIL

cette quantité n’est que de quelques unités ou Le contrôleur de gestion peut souhaiter n’affi-
dizaines d’unités, Excel peut être suffisant pour cher que les résultats et masquer les feuilles de
réaliser les calculs de coût complet au niveau de calculs intermédiaires. Pour cela, sélectionnez
chacune des références. la feuille à masquer puis, dans le menu Accueil,
cliquez sur Format du groupe Cellules.
Le contrôleur de gestion disposera alors de Choisissez Masquer et Afficher dans le thème
fichiers contenant l’ensemble des données Visibilité, puis Masquer la feuille. La feuille
financières sensibles pour la prise de décision. existe toujours, mais n’est plus visible.

COMMENT UTILISER EXCEL ?

Pour simplifier les calculs et éviter de passer
trop de temps dans la recherche de cellules POUR RÉSUMER
fréquemment utilisées, nous avons la possibilité • Le calcul des coûts directs et indirects
des produits nécessite d’utiliser les résultats
avec Excel de nommer des cellules et d’utiliser
des calculs intermédiaires stockés dans des
ainsi directement leur nom. Par exemple, le cellules de la feuille Excel. Plusieurs solutions
montant total de la matière peut être nommé s’offrent à nous pour paramétrer des calculs
TMAT et être repris ensuite sous ce nom pour entre ces cellules.
le calcul des coûts indirects d’approvisionne- • Nommer des cellules peut faciliter l’établisse-
ment. Il suffit, pour cela, de positionner le cur- ment de chaînes de calcul plutôt que de figer
seur sur la cellule à nommer et de saisir le nom les cellules contenant les constantes.
souhaité dans sa case de définition, qui se trouve • Choisissez un nom avec peu de caractères
en haut à gauche du menu Excel (juste au-dessus et facile à retenir.
des lettres des colonnes). • Positionnez le curseur sur la cellule à
nommer et saisissez le nom souhaité dans sa
Pour définir le nom d’une cellule, choisissez de case de définition (en haut à gauche du menu
préférence un mot avec un nombre limité de Excel).
caractères, quatre ou cinq, pour éviter une saisie
trop longue, ce qui irait à l’encontre du but visé.
Par convention, il est également indiqué de
n’utiliser que des majuscules.

f fiche 13
Consolider les ventes dans différentes devises : utilisation de tables
© Groupe Eyrolles

128 Calculer les coûts

 SIMULER LES ACTIONS D’AMÉLIORATION SUR LES COÛTS
EN UTILISANT LES BOUTONS CURSEURS 39
Le système de calcul des coûts modélise la consommation de ressources au regard de paramètres
clefs sélectionnés. Ainsi, avec la méthode Activity Based Costing, vous pouvez relier les ressources
aux activités et les activités aux produits (objets de coût).

LES OBJECTIFS VISÉS Excel répond à ces deux impératifs en fournis-

sant aussi bien les formules de calcul que les
La traçabilité des charges vers les produits peut
éléments d’une présentation visuelle marquant
servir aussi bien pour faire un bilan que pour
établir des données prévisionnelles. Émile de les esprits.
Girardin disait : « Gouverner c’est prévoir. » Le
COMMENT UTILISER EXCEL ?
contrôleur de gestion se doit d’anticiper sur des
changements à venir pour faciliter le pilotage La fonctionnalité Boutons curseurs répond par-
des opérations par le management. Il lui faut faitement au besoin de simulation. Dans le
alors des outils simples à comprendre et à utili- menu Excel, choisissez la fonction Développeur
ser pour mobiliser l’attention des organisations. (voir fiche 15).

Cas pratique
À partir du zip, ouvrez le fi chier CH8-1, onglet F39 Simu
On cherche à mesurer, pour la société FILM+, l’effet financier d’un changement quantitatif sur les principaux
inducteurs. On crée pour cela une feuille de calcul spécifique et on présente un graphique de synthèse.
On obtient les éléments suivants :
© Groupe Eyrolles

Calculer les coûts 129

FICHE 39
SIMULER LES ACTIONS D’AMÉLIORATION SUR LES COÛTS
EN UTILISANT LES BOUTONS CURSEURS

Nous avons retenu trois paramètres majeurs in- standard, qui forment la valeur de référence, en
fluençant le coût unitaire du produit : le volume, courbe. Cela facilite la comparaison et renforce
le nombre de commandes client et le nombre de le visuel.
clients. Pour chacun d’eux, nous ajoutons un
bouton de simulation. CONSEIL
Après avoir inséré les boutons curseurs dans la Créez, dans un premier temps, un graphique
feuille Simu, reliez la valeur de chaque bouton avec les deux types de coûts en histogramme.
à une cellule de calcul de coût. Par exemple, le Puis, pour faire apparaître les coûts standard en
courbe, positionnez le curseur sur l’histogramme
nombre de commandes client (cellule N8) qui
et cliquez droit. Dans la boîte de dialogue qui
peut être changé avec le bouton curseur ira apparaît, sélectionnez Modifier le type de gra-
modifier le coût indirect de production (cellule phique et choisissez les courbes.
E20).
Vous pouvez maintenant constater que si vous
déplacez les différents boutons, le coût unitaire
produit s’en trouve modifié. POUR RÉSUMER
Pour donner un aspect visuel à cette simulation, • Vous pouvez relier les tables de données
calculées à des boutons curseurs pour procé-
vous opterez pour une représentation graphique
der à des simulations.
sous forme d’histogramme. Le déplacement
• Comme des graphiques peuvent être générés
d’un curseur changera le niveau de coûts indi- à partir de tables de données, vous obtiendrez
rects simulés : l’effet sur le coût total est affiché des graphiques qui changeront selon le
sur l’histogramme de gauche et l’effet détaillé déplacement des boutons curseurs.
par inducteur est précisé sur l’histogramme de • Vous pouvez également faire figurer dans un
droite. même graphique plusieurs séries de données
en choisissant leur type de représentation
Pour éviter de surcharger le graphique de droite
visuelle avec la commande Modifier le type
en ayant six histogrammes : deux histogrammes de graphique.
(coût standard et coût simulé) pour chacun des
trois inducteurs, vous pouvez mettre les coûts

f fiche 15
Mesurer la sensibilité des variables et calculer le levier opérationnel © Groupe Eyrolles

130 Calculer les coûts

 Module 9

RÉALISER UN ARRÊTÉ MENSUEL

DES COMPTES

C
e module propose au manager financier quelques outils pour l’aider à la
réalisation d’un arrêté comptable.
© Groupe Eyrolles

131
Fiche
1 40 Planifier et suivre l’avancement des travaux 133

Fiche 41 Calculer les provisions de dépréciation

des stocks et des créances clients 135

Fiche
e1 42 Tester la dépréciation (« impairment ») des immobilisations 137

Fiche 43 Procéder à la revue analytique des comptes 139

© Groupe Eyrolles
 PLANIFIER ET SUIVRE L’AVANCEMENT DES TRAVAUX
40
Un arrêté comptable suppose de synchroniser de nombreuses tâches selon un calendrier très
précis. Excel peut faciliter le suivi de l’avancement avec des fonctionnalités simples d’utilisation.

LES OBJECTIFS VISÉS Excel peut être utilisé à cette fin, car il permet
le partage de fichiers et, avec la colorisation
Les travaux du contrôleur de gestion sont très
possible des noms d’onglets (rouge ou vert, par
cycliques. Chaque mois, un bilan des perfor-
exemple), le contrôleur connaît l’avancement
mances financières obtenues doit être réalisé et
des différents travaux et sait s’il peut utiliser des
mis en perspective vis-à-vis du budget. Ces résultats intermédiaires.
opérations comptables nécessitent une bonne
coordination avec les différents contrôleurs COMMENT UTILISER EXCEL ?
d’unités et les services comptables ou opéra- Dans Excel, vous coloriserez le nom des feuilles.
tionnels. En positionnant le curseur sur le nom d’une

Cas pratique
À partir du zip, ouvrez le fi chier CH9-1, onglet F40 Summary
L’arrêté mensuel comptable chez FILM+ révèle que les actions sont affectées à des services différents et doivent
être coordonnées pour garantir le respect du délai imparti.
© Groupe Eyrolles

Les différents travaux sont listés avec un code statut : « Open » signale un retard, « Closed » précise que l’action
est terminée et « Monitor » désigne les tâches en cours.
Dans chaque onglet, Feuille 1 et Feuille 2, on détaille les travaux et on colorise le nom de la feuille pour indiquer
si les résultats peuvent être utilisés (couleur verte) ou si les travaux ne sont pas terminés (couleur rouge).

Réaliser un arrêté mensuel des comptes 133

FICHE 40
PLANIFIER ET SUIVRE
L’AVANCEMENT DES TRAVAUX

feuille, cliquez droit et sélectionnez Couleur

d’onglet dans la boîte de dialogue qui s’ouvre. Il POUR RÉSUMER
ne vous reste plus qu’à choisir la couleur voulue. • La colorisation des noms de feuilles
Excel peut être utile pour suivre l’évolution d’un
planning de tâches à réaliser par plusieurs
CONSEIL personnes ou services.
Pour assurer le suivi des actions, il est possible • En cliquant droit sur le nom de la feuille et en
d’insérer un filtre sur chaque colonne afin de ne sélectionnant Couleur d’onglet dans la boîte de
sélectionner qu’une partie des lignes. Par dialogue, vous choisirez facilement la couleur
exemple, vous pouvez faire apparaître unique- qui convient.
ment les actions dont le statut est « Open ».
• Dans une liste de travaux à réaliser, vous
Positionnez votre curseur dans une cellule de la pourrez également sélectionner celles qui
ligne des titres de colonnes, puis choisissez vous intéressent plus particulièrement avec la
Données et Filtrer. Excel insère alors un bouton commande Filtrer du menu Données.
dans chaque colonne au niveau des titres, sur
lequel il est possible de cliquer pour choisir la
valeur du filtre souhaité.

f fiche 7
Trier et filtrer les données

© Groupe Eyrolles

134 Réaliser un arrêté mensuel des comptes

 CALCULER LES PROVISIONS
DE DÉPRÉCIATION DES STOCKS ET DES CRÉANCES CLIENTS 41
L’appréciation des risques de dépréciation d’actifs incombe le plus souvent au contrôleur de
gestion. À partir des données de gestion, il doit déterminer le caractère probable du risque et
préciser les paramètres de valorisation.

LES OBJECTIFS VISÉS Ce risque est revu régulièrement et ajusté.

Nous évoquerons ici deux risques fréquemment Le risque de dépréciation

reportés : les risques de dépréciation de stock et de créance client
de créance client. L’ancienneté d’une créance client est un signe

Le risque de dépréciation de stock de non-recouvrement probable. Des échanges

avec le client doivent permettre d’identifier la
Ce risque se manifeste en cas de mévente ou
lors d’un changement de modèle : le stock rési- cause de non-paiement : insatisfaction sur la
duel n’est pas couvert par des ventes suffisantes. qualité, difficulté financière, etc. Des solutions
Le contrôleur de gestion ou le gestionnaire peuvent alors être mises en œuvre, mais si l’on
produit alertent les responsables commerciaux n’obtient pas de réponse malgré des relances,
en cas de couverture trop importante. En fonc- une procédure de contentieux est engagée et
tion du niveau, un taux de provision sera appli- amène le contrôleur de gestion à constituer une
qué (par exemple, au-delà de 90 jours : 25 %, provision. De la même manière que pour le
au-delà de 120 jours : 50 %, au-delà de stock, selon l’ancienneté de la créance et l’avis
150 jours : 75 % et au-delà de 180 jours : pro- du service contentieux, un taux de provision
vision à 100 %). peut être défini.

Cas pratique
À partir du zip, ouvrez le fi chier CH9-1, onglet F41 Provisions
La société FILM+ est amenée à calculer les provisions de dépréciation de ses stocks de caméras et ses créances
client. Le tableau obtenu est le suivant :
© Groupe Eyrolles

Réaliser un arrêté mensuel des comptes 135

FICHE 41
CALCULER LES PROVISIONS DE DÉPRÉCIATION DES STOCKS
ET DES CRÉANCES CLIENTS

Excel facilite la vie du contrôleur de gestion en pourcentage de provision à importer, faux). Un

offrant plusieurs possibilités de paramétrage, message d’erreur (#N/A) apparaîtra si Excel ne
soit par des menus déroulants encadrant les peut trouver une valeur proche (« valeur
valeurs à retenir, soit en retenant une valeur proche » = faux).
précise stockée dans une table. De manière automatique, le pourcentage corres-
pondant au nombre de jours sera affiché.
COMMENT UTILISER EXCEL ?
Excel propose deux options. CONSEIL
Première option : le menu déroulant Vous pouvez imbriquer des formules dans une
pour un choix guidé règle de condition comme dans notre exemple
pour déterminer le pourcentage de provision de
Vous préciserez dans une zone séparée de la
dépréciation de stock.
feuille les valeurs souhaitées (ici, les pourcen-
=SI(G4>$L$93;$M$93;SI(G4<$L$3;0;RECHE
tages de provision). RCHEV(G4;$L$3:$M$93;2;FAUX)))
Vous appliquerez la commande Validation de Attention, cependant, à ne pas en abuser : la
données du menu Données à chaque cellule où maintenance se révélerait difficile quelques an-
une de ces valeurs doit être indiquée. nées plus tard !

Dans la zone d’options de la boîte de dialogue

qui s’affiche, choisissez le critère Liste puis la
source des données (les pourcentages de provi-
sion) en sélectionnant la zone des valeurs. POUR RÉSUMER
Pour établir les provisions de dépréciation
Seconde option : calcul automatique ou sur risques encourus, vous pouvez procéder
du pourcentage de provision de deux façons.
par la fonction RechercheV • Option 1 : avec un menu déroulant (com-
Créez la table des valeurs : le taux de provision mande Validation de données du menu
selon le nombre de jours de couverture, par Données). Cette option présente l’avantage
d’encadrer strictement les valeurs possibles.
exemple.
• Option 2 : avec la fonction RechercheV.
Dans la zone de calcul, saisissez la formule Cette option est à utiliser si les taux de
=RECHERCHEV(valeur à trouver, la table où se provision sont très variés.
trouvent les données, le numéro de colonne du

f fiche 13 f fiche 21
Consolider les ventes dans différentes devises : utilisation de Présenter un tableau des cash-flows
tables
© Groupe Eyrolles

136 Réaliser un arrêté mensuel des comptes

 TESTER LA DÉPRÉCIATION (IMPAIRMENT)
DES IMMOBILISATIONS 42
Au moins une fois par an, la valeur des immobilisations doit faire l’objet d’un contrôle au regard
des avantages économiques procurés : c’est le test d’« impairment ». Excel fournit les formules
financières pour accélérer les calculs.

LES OBJECTIFS VISÉS bien la valeur nette comptable de l’actif en

question. Ces flux futurs correspondent souvent
Le suivi de la valeur des actifs des entreprises
à la marge sur coût variable.
est encadré par la norme IAS 36, qui comprend
toutes les dispositions relatives aux déprécia- COMMENT UTILISER EXCEL ?
tions d’actifs non couverts par une norme spé-
Deux fonctions d’Excel, maintenant bien
cifique. Les entreprises doivent, au moins une
connues, vont opérer le calcul de l’impairment.
fois par an, à la clôture de leur exercice et/ou
dès lors qu’un indice de perte de valeur est Avec la formule =SI dans notre exemple, véri-
constaté, procéder à la comparaison de la valeur fiez si la valeur recouvrable est supérieure à la
recouvrable de leurs actifs à leur valeur nette VNC ; si oui, alors pas d’impairment : 0 ; sinon,
comptable (VNC). Si la valeur recouvrable se la provision correspondra à la différence entre
révèle inférieure à la valeur comptable, l’entre- le total des flux actualisés et la VNC. Appliquée
prise doit alors passer une provision. à notre exemple, la formule est
La valeur recouvrable correspond aux flux de =SI(I7>B6;0;I7-B6).
trésorerie actualisés que l’actif permet de géné- Pour l’actualisation, utilisez la formule
rer. Si un événement particulier survient, telle =VAN(taux d’actualisation;cellules à actualiser).
une perte de brevet par exemple, il faut estimer Dans notre exemple, le flux actualisé de la pre-
les flux futurs et ainsi vérifier qu’ils couvrent mière année est =VAN($D$9;E6).

Cas pratique
À partir du zip, ouvrez le fi chier CH9-1, onglet F42 Impairment
FILM+ veut tester la dépréciation de ses immobilisations. L’entreprise réalise un tableau de données pour le test
d’impairment. Le contrôleur de gestion pourra combiner les différents calculs en utilisant deux formules d’Excel :
la condition SI pour comparer valeur recouvrable et VNC, et la formule =VAN(taux d’actualisation;flux) pour
déterminer le fl ux actualisé.
Il obtiendra le tableau suivant :
© Groupe Eyrolles

Réaliser un arrêté mensuel des comptes 137

FICHE 42
TESTER LA DÉPRÉCIATION (IMPAIRMENT )
DES IMMOBILISATIONS

CONSEIL
Pour déterminer la VAN des flux au-delà de la
POUR RÉSUMER
première année, vous pourrez déterminer un • Pour tester la dépréciation éventuelle
coefficient d’actualisation à appliquer au flux d’immobilisations, vous devrez utiliser plu-
annuel. Ce coefficient se calcule en divisant 1 sieurs fonctions d’Excel.
par (1+taux d’actualisation) à la puissance du • La formule =VAN(taux d’actualisation ;cel-
nombre d’années. Si vous souhaitez connaître lules à actualiser) vous permettra de définir
directement la valeur actuelle, saisissez toutes les avantages procurés par un actif immobilisé
les périodes : ainsi, pour l’année 3, vous saisi- donné.
rez : =VAN(taux d’actualisation ; 0 ;0 ;flux an- • La reconnaissance d’une dépréciation sera
née 3). faite avec la formule =SI pour vérifier si la
valeur actuelle nette de ces avantages est
supérieure ou non à la VNC.

f fiche 23
Calculer la VAN et le TRI

© Groupe Eyrolles

138 Réaliser un arrêté mensuel des comptes

 PROCÉDER À LA REVUE ANALYTIQUE DES COMPTES
43
La revue analytique des comptes est utile au contrôleur de gestion et à l’auditeur, car tous deux
cherchent à éliminer des risques non maîtrisés et à comprendre une situation qui paraît anormale.

LES OBJECTIFS VISÉS en mettant en évidence uniquement les données

qui présentent les écarts les plus élevés.
Une revue analytique des comptes consiste à
mettre en évidence des écarts importants, des COMMENT UTILISER EXCEL ?
ruptures ou des inversions de tendances. Ces
Vous utiliserez la fonction Mise en forme condi-
écarts peuvent être établis au regard d’une réfé-
tionnelle des cellules du menu Accueil, après
rence budgétaire, historique ou d’un bench-
avoir sélectionné la zone de valeurs à analyser.
mark externe. On peut les exprimer en valeur
Parmi les options proposées, vous vous intéres-
absolue ou en pourcentage (valeur relative),
serez à Règles des valeurs plus ou moins élevées,
ces deux valeurs pouvant être complémen-
puis à 10 valeurs les plus élevées ou à 10 % des
taires.
valeurs les plus élevées. Ensuite, une boîte de
Gérer par exception consiste à rechercher l’effi- dialogue permet de limiter encore plus le
cacité en ne sollicitant le management que nombre de valeurs ou le pourcentage et de
lorsque les enjeux sont élevés. Excel peut aider choisir (f illustration ci-dessous).

Cas pratique
À partir du zip, ouvrez le fi chier CH9-1, onglet F43 Revue Ana
Les ventes de FILM+ font l’objet d’une revue analytique. On obtient le tableau suivant :
© Groupe Eyrolles

BOÎTE DE DIALOGUE POUR LES OPTIONS DE MISE EN FORME CONDITIONNELLE

Réaliser un arrêté mensuel des comptes 139

FICHE 43
PROCÉDER À LA REVUE ANALYTIQUE
DES COMPTES

CONSEIL
Pour être sélectif, il est recommandé de sélec-
POUR RÉSUMER
tionner au maximum 20 % des valeurs extrêmes, • Un tableau de données doit toujours
ou une dizaine en nombre, car un rapport trop attirer l’attention du manager pour l’orienter
coloré est illisible. Vous pouvez également insé- vers des décisions à prendre. Il est donc
rer un objet ou une forme (rectangle, cercle) sur important de mettre les éléments marquants
la zone où l’attention doit être attirée. Il est en évidence. Excel propose de nombreuses
possible d’organiser ces objets ou formes en solutions, parmi lesquelles :
« couches » : premier plan ou arrière-plan. Pour • le formatage des données, caractères gras
cela, sélectionnez l’onglet Mise en page, puis ou italiques, peut être une première solution
cliquez sur les boutons Avancer ou Reculer du réalisée manuellement au cas par cas ;
groupe Organiser. • la mise en forme conditionnelle de cellules
présente l’avantage d’une automatisation
selon des critères prédéfinis ;
• vous pourriez également utiliser la com-
mande Insérer un objet ou une forme (rec-
tangle, cercle) sur un chiffre particulièrement
important.

f fiche 14 f fiche 32
Calculer les contributions directes par famille produit Identifier les variables d’ajustement majeures

© Groupe Eyrolles

140 Réaliser un arrêté mensuel des comptes

 Module 10

METTRE EN FORME
ET PUBLIER UN REPORTING

C
haque manager a besoin de piloter sa performance. Excel lui fournit des
outils pour lui permettre de contrôler son budget et de communiquer sur
les faits marquants avec des formats adaptés.
© Groupe Eyrolles

141
Fiche 44
1 Rechercher les principaux écarts dans les tableaux 143

Fiche 45 Définir les formats de cellules 146

Fiche 46
1 Définir les formats de présentation 148

Fiche 47 Utiliser la mise en forme conditionnelle des cellules 150

Fiche 48
1 Calculer les écarts budgétaires par niveau hiérarchique
et en global 152
© Groupe Eyrolles
 RECHERCHER LES PRINCIPAUX ÉCARTS DANS LES TABLEAUX
44
Le tableau croisé dynamique est l’outil indispensable pour analyser des données présentant une
volumétrie importante sur plusieurs variables. Il va permettre de réaliser une synthèse en croisant
les données de différentes variables.

LES OBJECTIFS VISÉS personnalisés. Vous pouvez alors définir vos

propres critères.
La gestion par exception est un grand principe
du contrôle de gestion. L’identification de Pour des analyses avec des critères autres que
variables clefs à des fins de modélisation ou des valeurs, les tableaux croisés dynamiques
la recherche de causes d’écarts pour bâtir des sont très pratiques. Sélectionnez le tableau à
plans d’action nécessitent d’aller toujours à analyser, puis cliquez sur la fonction Insérer
l’essentiel. Dans un environnement où l’infor- dans le menu et choisissez Tableau croisé dyna-
mation est plus qu’abondante, la tâche n’est mique. Une boîte de dialogue s’ouvrira et vous
pas toujours aisée. Le contrôleur de gestion demandera de confirmer la zone d’analyse et
pourra utiliser des logiciels de requête spécia- l’insertion du tableau croisé dynamique dans
une nouvelle feuille Excel. Dans ce nouvel on-
lisés, mais avec ses possibilités d’analyses
multidimensionnelles son outil de travail glet, vous n’avez plus qu’à faire vos choix de
disposition du tableau en cochant le champ de
quotidien Excel peut aussi lui rendre bien des
données et en le faisant glisser vers une des
services.
quatre propositions : Filtre, Colonne, Lignes ou
COMMENT UTILISER EXCEL ? Valeurs (f illustration p. 145).
Vous utiliserez, dans Excel, la fonction Filtre
du menu Données. Son activation ajoute à CONSEIL
chaque colonne un petit carré avec une flèche Dans l’analyse de données nombreuses, la séle-
dirigée vers le bas. Sur ce petit carré de la co- ction de la zone peut prendre du temps. Excel
permet une sélection en un seul clic si les titres
lonne que vous voulez trier, cliquez droit et
des colonnes figurent bien sur la première ligne :
sélectionnez Filtres numériques. Différents il vous suffit d’aller cliquer dans la case à gauche
critères standard sont proposés avec, en plus, entre la ligne 1 et la colonne A. Toute la feuille
tout en bas de la boîte de dialogue, Filtres est ainsi sélectionnée.
© Groupe Eyrolles

Mettre en forme et publier un reporting 143

FICHE 44
RECHERCHER LES PRINCIPAUX ÉCARTS
DANS LES TABLEAUX

Cas pratique
À partir du zip, ouvrez le fi chier CH10-1, onglet F44 Filtres sur CONSO
À partir de l’historique des prévisions de vente de caméras chez FILM+, on pourrait souhaiter savoir quelles
références produit génèrent un niveau de chiffre d’affaires entre 60 000 € et 100 000 €, dans quel pays et à
quelle époque de l’année.
On obtient le tableau suivant avec les données brutes des ventes :

Avec un tableau croisé dynamique, on peut choisir à loisir les données souhaitées, comme ci-après, avec les
ventes en quantité et valeur des caméras HD de l’année 2014 et sur les marchés France et Allemagne confondus.

© Groupe Eyrolles

144 Mettre en forme et publier un reporting

 FICHE 44
RECHERCHER LES PRINCIPAUX ÉCARTS
DANS LES TABLEAUX

BOÎTE DE DIALOGUE POUR L’INSERTION D’UN TABLEAU CROISÉ DYNAMIQUE

POUR RÉSUMER
• Pour analyser un tableau de données numériques ou autres, Excel dispose de deux commandes
intéressantes : le filtre et le tableau croisé dynamique.
• Un filtre sur les données peut être entrepris avec la commande Filtre du menu Données. Excel vous propo-
sera une personnalisation de vos filtres pour qu’apparaissent uniquement les données qui vous intéressent.
• Le tableau croisé dynamique obtenu dans le menu Insérer va vous permettre de réaliser un tableau de
synthèse en comptant par exemple les effectifs d’une variable ou en croisant deux variables (l’une en ligne
et l’autre en colonne).

f fiche 7
Trier et filtrer les données
© Groupe Eyrolles

Mettre en forme et publier un reporting 145

45 DÉFINIR LES FORMATS DE CELLULES

La performance d’une organisation se mesure avec des indicateurs quantitatifs s’exprimant dans
différentes unités ou sous forme de ratios. Un reporting doit donc afficher clairement les spécifi-
cités de chacun de ses indicateurs.

LES OBJECTIFS VISÉS un même format de présentation des informa-

tions pour faciliter la consolidation.
La mise en œuvre d’une stratégie amène à délé-
guer des pouvoirs aux principales organisa- Excel réalisera ce formatage car à chaque cellule,
tions : commerciale, recherche et industrielle. un choix est possible : nombre, monétaire,
Ces pouvoirs nécessitent des ressources en capi- pourcentage, comptable…
tal, humaines ou autres. Aussi, la direction géné-
COMMENT UTILISER EXCEL ?
rale sera soucieuse de vérifier que ces moyens
sont bien utilisés et permettent d’atteindre les Pour paramétrer une cellule, ouvrez la boîte de
objectifs stratégiques. Ce contrôle se concrétise dialogue Format de cellule qui propose les dif-
par un reporting régulier (souvent mensuel) des férentes options. Il vous suffit de cliquer droit
unités décentralisées auprès de l’unité centrale, sur une cellule pour y accéder (f illustration ci-
à une date fixée par avance. Ce reporting utilise contre).

Cas pratique
À partir du zip, ouvrez le fi chier CH10-1, onglet F45 Indicateurs Fi
L’organisation logistique et industrielle FR-SC pourrait avoir un reporting auprès de sa direction générale,
comme dans l’exemple ci-dessous, en détaillant sa maîtrise des coûts de production et ses principaux actifs.
On obtiendrait le tableau suivant :

© Groupe Eyrolles

Dans ce tableau, on pourra faire figurer des données monétaires, du texte et des nombres.

146 Mettre en forme et publier un reporting

 FICHE 45
DÉFINIR LES FORMATS
DE CELLULES

BOÎTE DE DIALOGUE POUR CHOISIR LES OPTIONS DU FORMAT DE CELLULE

CONSEIL
POUR RÉSUMER
La duplication d’un format de cellule est auto-
matique quand on la copie en tirant la croix de
• Le clic droit sur une cellule est un réflexe
copie vers d’autres cellules. Vous pouvez éga-
que tout utilisateur d’Excel doit acquérir,
lement copier uniquement le format d’une cel-
notamment pour définir un format approprié.
lule, sans dupliquer les formules ou les valeurs. • Cette commande Format de cellule offre, en
Pour cela, après la copie (Ctrl+C), choisissez effet, la possibilité de préciser le type de for-
Collage spécial en cochant uniquement la case mat pour un nombre, l’alignement et la police
Format. souhaités, la présence d’un type de bordure
dans le tableau, les couleurs de remplissage
et, éventuellement, de protéger la cellule d’une
modification.
• Ces formats pourront être recopiés en totalité
ou partiellement. Dans ce cas, vous devrez utili-
ser les commandes Copier, puis Collage spécial
et sélectionner ce que vous voulez copier.
© Groupe Eyrolles

f fiche 18
Cadencer les achats et valoriser le flux

Mettre en forme et publier un reporting 147

46 DÉFINIR LES FORMATS DE PRÉSENTATION

Un reporting présente des informations en vue de faire réagir le management. Il s’agit donc de
mettre en évidence les points majeurs pour que ce dernier puisse rapidement décider des actions
à engager ; les différentes options de présentation des données vous y aideront.

LES OBJECTIFS VISÉS Q Alignement (centré, gauche, droit…) ;

La présentation de reporting est un outil de Q Remplissage (couleur d’arrière-plan ou de

motif) ;
travail pour les contrôleurs de gestion et un
outil de communication pour le management. Q Bordure (style ou présélection) ;
La forme de présentation a donc son impor- Q Police (caractères, soulignements, style nor-
tance. On pourra mettre en évidence les zones mal, italique ou gras, couleur et taille).
de saisie des données, les zones de calcul auto- Pour accélérer le formatage, vous pouvez aussi
matique, et celles où les données sont importées utiliser les options prédéfinies d’Excel : dans le
d’autres feuilles. menu Accueil, choisissez Style de cellules ou
Mettre sous forme de tableau, ou encore Format
COMMENT UTILISER EXCEL ? puis choix de la taille, visibilité et protection.
Vous utiliserez, dans Excel, le format de cellule L’insertion de commentaires pour une cellule
sur les onglets : peut se révéler très utile quand il y a un doute

Cas pratique
À partir du zip, ouvrez le fi chier CH10-1, onglet F44 Indicateurs Fi
Le reporting de l’organisation logistique et industrielle FR-SC présente en blanc les données à saisir, en jaune
les valeurs importées d’autres feuilles et en gris les cellules calculées.
On obtient le tableau suivant :

© Groupe Eyrolles

Dans ce tableau, les données en gris sont protégées.

148 Mettre en forme et publier un reporting

 FICHE 46
DÉFINIR LES FORMATS
DE PRÉSENTATION

sur la donnée ou quand une précision est néces-

saire. Faites un clic droit sur la cellule et choi- POUR RÉSUMER
sissez Insérer un commentaire. Saisissez ensuite • Un tableau de données avec un format
le commentaire dans la zone de texte qui clair est la garantie de bien faire comprendre
ce que vous souhaitez.
s’ouvre, puis validez. Pour indiquer qu’un
• Pour vous faire gagner du temps, Excel
commentaire est inséré, la cellule présente, dans
propose des formats standard de tableaux.
son coin haut à droite, un petit bandeau rouge.
• Dans le menu Accueil, choisissez Style de
cellules.
CONSEIL • Autre option : dans le menu Accueil, choisis-
Il est conseillé de protéger les cellules où un sez Mettre sous forme de tableau (différentes
calcul est réalisé de manière automatique, afin couleurs et agencements de tableaux).
que l’on ne puisse pas les modifier. Pour cela,
dans le format de cellule Protection, verrouillez
la cellule. Pour activer la protection, affectez un
mot de passe à la feuille de calcul dans le menu
Révision (dans notre exemple, le mot de passe
est « eyrolles »).

f fiche 32 f fiche 43
Identifier les variables d’ajustement majeures Procéder à la revue analytique des comptes
© Groupe Eyrolles

Mettre en forme et publier un reporting 149

47 UTILISER LA MISE EN FORME CONDITIONNELLE DES CELLULES

Dans ses fonctionnalités de base, Excel propose un paramétrage qui vous permettra d’automatiser
la mise en forme des cellules d’un tableau selon leurs valeurs.

LES OBJECTIFS VISÉS riques si celle-ci respecte la condition définie

(valeur supérieure à … % en dessous de…).
Nous avons souligné l’importance d’une pré-
sentation structurée pour un reporting. En tant COMMENT UTILISER EXCEL ?
qu’outil de contrôle de la direction, il doit
Dans le menu Accueil, sélectionnez Mise en
cibler les points sur lesquels le management
forme conditionnelle puis Nouvelle règle de
aura à prendre des décisions. Ce ciblage peut
mise en forme. Parmi les options proposées,
être laissé à l’appréciation du lecteur qui
choisissez la première : Mettre en forme toutes
sélectionnera les informations utiles parmi
les cellules d’après leur valeur. Puis choisissez le
toutes les feuilles du rapport. On comprend
style de mise en forme dans le menu déroulant
bien que cette sélection prendra du temps
(dans l’exemple, jeux d’icônes) ainsi que le style
sans être un gage de qualité. Ce temps pré-
d’icône (dans le tableau d’exemple, nous les
cieux sera mieux employé s’il est engagé dans
avons fait varier : rond ou croix pour montrer la
l’action.
diversité de présentation). Il reste maintenant à
Excel peut réaliser ce ciblage de manière auto- définir la règle pour déterminer quelle icône doit
matique en fonction de règles prédéfinies par le s’afficher en fonction de la valeur de la cellule.
concepteur du reporting. Parmi les mises en Le type de valeur est le plus souvent un nombre,
forme, nous évoquons ici la mise en forme mais on peut utiliser un pourcentage ou une
conditionnelle, c’est-à-dire la définition d’une formule. Les critères de valeur sont à renseigner
forme particulière (couleur, taille de police, etc.) dans la colonne Valeur, comme le montre la boîte
d’une cellule contenant des données numé- de dialogue ci-après (f illustration ci-contre).

Cas pratique
À partir du zip, ouvrez le fi chier CH10-1, onglet F47 Reporting dépenses
Chaque direction de FILM+ doit rendre compte, à la direction générale, sur l’utilisation des ressources qui lui
sont allouées. Elle reportera ainsi ses réalisations en les comparant au budget. Le tableau obtenu sera le suivant :

© Groupe Eyrolles

Dans ce tableau, nous avons fait figurer, pour chaque colonne des écarts budgétaires, une mise en forme parti-
culière : insertion d’icônes ou remplissage de la cellule avec un code couleur type feux tricolores ou vert = tout
va bien, jaune = attention et rouge = alerte danger.

150 Mettre en forme et publier un reporting

 FICHE 47
UTILISER LA MISE EN FORME
CONDITIONNELLE DES CELLULES

BOÎTE DE DIALOGUE PRÉSENTANT LES DIFFÉRENTES OPTIONS DE MISE EN FORME DES CELLULES

Dans le tableau de notre exemple, la cellule d’écart

mensuel sur les revenus apparaît avec une icône POUR RÉSUMER
rouge, car sa valeur est négative. La cellule d’écart • La mise en forme conditionnelle de
cumulé sur les revenus apparaît avec une icône cellules dans Excel est un bon moyen pour
mettre en évidence un fait marquant, mais il
jaune, car sa valeur est comprise entre 0 et 500.
ne faut pas en abuser.
• Excel propose dans le menu Accueil cette
CONSEIL fonctionnalité qui vous permet d’insérer
La mise en forme conditionnelle des cellules est des icônes, de remplir les cellules avec un
en fait un format particulier. Vous pouvez donc code couleur type feux tricolores quand une
le recopier sur d’autres cellules sans avoir à condition est réalisée.
refaire le paramétrage. Il est conseillé de vérifier • Vous pouvez recopier la règle définie sur
que les mêmes règles s’appliquent bien, voire d’autres cellules du moment que la condition
d’ajuster les valeurs au besoin, ce qui pourrait reste la même.
être le cas entre des valeurs d’écart mensuel ou
des valeurs d’écart cumulé.
© Groupe Eyrolles

f fiche 32 f fiche 43
Identifier les variables d’ajustement majeures Procéder à la revue analytique des comptes

Mettre en forme et publier un reporting 151

48 CALCULER LES ÉCARTS BUDGÉTAIRES PAR NIVEAU
HIÉRARCHIQUE ET EN GLOBAL

Une présentation hiérarchisée de données peut offrir aussi bien une vision très synthétique qu’une
vision détaillée. Cette option est bien utile pour les rapports d’écarts budgétaires.

LES OBJECTIFS VISÉS Excel peuvent répondre à ce double objectif, ce

qui évitera au contrôleur de gestion de devoir
Le management doit disposer à la fois de don-
nées de synthèse pour avoir une vision d’en- préparer de nombreux rapports sous des formes
semble sur son centre de responsabilité et différentes. Ce temps sans grande valeur ajoutée
d’informations détaillées pour pouvoir analyser pourra être investi pour accompagner le mana-
toute dérive éventuelle. Les rapports établis avec gement dans la construction de plans d’action.

Cas pratique
À partir du zip, ouvrez le fi chier CH10-1, onglet F48 Écart bu (fonction)
Pour la société FILM+, un reporting des effectifs présenté par pays, par fonction et par direction avec des
données budgétaires, réalisations et écarts budgétaires pourra se présenter avec des niveaux de synthèse
automatiques comme suit :

À partir du zip, ouvrez le fi chier CH10-1, onglet F48 Écart bu (pays)

© Groupe Eyrolles

Les boutons + et – permettent par un simple clic d’afficher le détail ou la synthèse (sous-total). On peut donc
montrer le détail de FR ou, en cliquant sur son bouton –, seulement le sous-total figurant ligne 4. Ce qui est
possible pour des lignes l’est également pour des colonnes ; ainsi seul l’écart peut être montré en cliquant sur le
bouton (figurant au-dessus de la colonne F).

152 Mettre en forme et publier un reporting

 FICHE 48
CALCULER LES ÉCARTS BUDGÉTAIRES
PAR NIVEAU HIÉRARCHIQUE ET EN GLOBAL

COMMENT UTILISER EXCEL ? Pour afficher les sous-totaux en dessous des

lignes Détail, pensez à cocher Synthèse sous les
Après avoir trié les données par colonne, sélec-
données dans la boîte de dialogue. Vous pourrez
tionnez les cellules du tableau de référence.
également effectuer ces opérations avec Données
Dans le menu Données, Sous-total, une boîte de
et créer un plan automatique. Pour supprimer
dialogue va programmer les choix de synthèse :
des regroupements, toujours dans le menu
quels sous-totaux et pour quelles valeurs ?
Données, optez pour Dissocier sur la sélection
souhaitée du tableau.

CONSEIL
Si vous souhaitez, dans un même tableau, créer
des sous-totaux à plusieurs niveaux (voir fichier
CH10-1, onglet Écarts multiniveaux), créez le
premier niveau comme précédemment puis
réutilisez la commande Sous-totaux en prenant
garde de désélectionner la case Remplacer les
sous-totaux existants avant de valider.

POUR RÉSUMER
• Excel permet d’établir des rapports à
partir des données de synthèse pour mettre en
évidence les écarts budgétaires.
• En classant vos données avec la commande
Données, Trier, vous pouvez demander
BOÎTE DE DIALOGUE POUR RÉALISER
à Excel de réaliser automatiquement des
DES SOUS-TOTAUX EN MODE PLAN sous-totaux : sélectionnez les colonnes
sur lesquelles des sous-totaux doivent être
Notez que vous pouvez grouper des colonnes obtenus, puis utilisez la commande Sous-total
pour alléger le nombre de données affichées. du menu Données.
Après avoir sélectionné les colonnes à grouper, • Excel va insérer des boutons de hiérarchie
allez dans le menu Données, puis cliquez sur (+ et –) qui vous permettront d’afficher plus ou
moins de détails.
Grouper.

f fiche 37
Calculer le coût des activités et des processus
© Groupe Eyrolles

Mettre en forme et publier un reporting 153

 Module 11

CRÉER ET METTRE EN FORME

UN TABLEAU DE BORD
OPÉRATIONNEL

L
a mise en forme d’un tableau de bord va s’appuyer sur toute la palette d’outils
visuels disponible dans Excel : couleurs, graphiques, images, formes spé-
ciales…
© Groupe Eyrolles

155
Fiche 49
1 Structurer et sélectionner les indicateurs 157

Fiche 50 Concevoir la page de garde du tableau de bord 159

Fiche 51
1 Concevoir les feuilles de détail du tableau de bord 161

Fiche 52 Insérer des graphiques sparkline,

des liens et des formes automatiques 163

Fiche 53
1 Réaliser les graphiques de simulation 165
© Groupe Eyrolles
 STRUCTURER ET SÉLECTIONNER LES INDICATEURS
49
Le tableau de bord est un outil de pilotage et d’aide à la décision regroupant une sélection
d’indicateurs. Ces indicateurs sont organisés en système et suivis par un même responsable. Ils
doivent l’aider à connaître, à comprendre, à prévoir et à agir.

LES OBJECTIFS VISÉS Le tableau de bord s’articule autour de deux

catégories d’indicateurs : les indicateurs de
Un indicateur de tableau de bord peut être défini
de la manière suivante : information qui doit performance d’une part, qui mesurent le résul-
aider un acteur à conduire une action vers tat des actions, et les indicateurs de pilotage
l’accomplissement d’un objectif, ou permettre à d’autre part, qui mesurent l’avancement des
cet acteur d’évaluer le résultat d’une action. actions.

Cas pratique
À partir du zip, ouvrez le fi chier CH11-1, onglet F49 Type graphs
Pour clarifier le tableau de bord de FILM+, on utilisera ici les principaux graphiques de la palette d’Excel
selon leurs caractéristiques :
– un graphique correspond à un besoin particulier ;
– des courbes montrent une évolution et des tendances ;
– un graphique en secteur ou anneau illustre une analyse ;
– les histogrammes empilés sont bien adaptés pour mettre en évidence une évolution analytique ;
– le radar donne une bonne vision d’ensemble pour présenter une synthèse.
© Groupe Eyrolles

Créer et mettre en forme un tableau de bord opérationnel 157

FICHE 49
STRUCTURER ET SÉLECTIONNER
LES INDICATEURS

Cet outil formalise et modélise donc les princi- CONSEIL

pales relations de cause à effet au sein des orga- Pour différencier, dans un histogramme ou une
nisations. barre, les données présentant une inversion de
tendance, modifiez leur couleur d’apparition.
Le tableau de bord est également un outil de
Pour montrer les écarts de chiffre d’affaires par
communication entre le manager et son équipe,
rapport au budget sous forme d’histogramme,
avec sa hiérarchie et avec ses collègues. Il se doit cliquez droit sur la série de données, choisissez
donc d’être visuel pour faciliter la compréhen- Mettre en forme une série de données,
sion et présenter un nombre limité d’indicateurs. Remplissage et Inverser si négatif. Toute donnée
négative sera affichée dans une couleur diffé-
Excel facilite la construction d’un tableau de
rente. Pour choisir les couleurs, cochez
bord car il combine l’importation de données, Remplissage uni.
leur calcul et leur représentation graphique.
L’organisation d’un tableau de bord compte trois
parties : une feuille de données, une page de POUR RÉSUMER
garde et une ou plusieurs feuilles de détail. • Les tableaux de bord utilisent fré-
quemment des graphiques pour montrer des
COMMENT UTILISER EXCEL ? résultats.
Vous ferez appel à la commande lnsérer • Vous choisirez, dans Excel, lnsérer Gra-
Graphiques après avoir sélectionné les données phiques après avoir sélectionné les données à
à représenter visuellement. Pour chaque type de représenter visuellement.
graphique, Excel propose différentes options, • Excel a prévu la plupart des formes gra-
2D, 3D, graphiques combinés, et peut vous phiques simples : histogrammes, secteurs,
courbes, nuage de points, radars… que vous
suggérer un graphique dans l’option Graphiques
pourrez combiner dans un même graphique
recommandés. L’intérêt de cette option est que si vous devez représenter plusieurs séries de
vous pouvez visualiser le graphique avant même données (réel et budget, par exemple).
son insertion.

f fiche 9 f fiche 52
Identifier des règles de variabilité des coûts Insérer des graphiques sparkline, des liens et des formes
automatiques
f fiche 50
Concevoir la page de garde du tableau de bord
© Groupe Eyrolles

158 Créer et mettre en forme un tableau de bord opérationnel

 CONCEVOIR LA PAGE DE GARDE DU TABLEAU DE BORD
50
L’organisation d’un tableau de bord avec Excel compte deux parties : une page de garde et une
ou plusieurs feuilles de détail qui sont générées à partir d’une feuille comprenant, sous forme de
tableau, les données de base. Voyons d’abord comment réaliser la page de garde.

LES OBJECTIFS VISÉS donne une vue d’ensemble sur la réalisation

des missions. Elle doit respecter la charte
La page de garde va donner à son lecteur une
graphique de l’organisation avec un emplace-
vue synthétique sur les résultats obtenus, les
ment spécifique des images ou logos qui
perspectives et les éventuelles actions à engager. doivent figurer sur tout document de l’entre-
Les graphiques qui y figurent seront générés à prise.
partir de la feuille des données de base.
Excel permet d’enrichir la présentation de cette
La première page d’un tableau de bord est sans page avec des graphiques, et l’insertion d’objets
doute la plus importante du document, car elle et de zones de commentaires.

Cas pratique
À partir du zip, ouvrez le fi chier CH11-1, onglet F50 Page garde ou Scorecard
La page de garde du tableau de bord pourrait se présenter ainsi :
© Groupe Eyrolles

Créer et mettre en forme un tableau de bord opérationnel 159

FICHE 50
CONCEVOIR LA PAGE DE GARDE
DU TABLEAU DE BORD

COMMENT UTILISER EXCEL ? CONSEIL

Le tableau de bord sert aussi à donner une
Dans le menu d’Excel, faites appel à la com-
perspective, une tendance pour les périodes
mande Insérer Illustration, puis Image pour
suivantes. Avec des indicateurs présentés en
utiliser des images locales de l’entreprise respec- courbe (ventes, résultat d’exploitation, etc.),
tant une charte graphique définie. Excel offre la possibilité d’ajouter une courbe
Vous pourrez également choisir Image en de tendance avec son équation de régression.
Un clic droit sur la courbe ouvre une boîte de
ligne pour afficher des images de la biblio-
dialogue qui propose l’ajout d’une courbe de
thèque Clipart Office. À partir d’un thème tendance. Cochez le type de courbe choisi
donné, Excel vous proposera différentes (linéaire, logarithmique, polynomiale…) et l’affi-
images. chage de l’équation sur le graphique.
Pour bien isoler les commentaires qualitatifs sur
les résultats présentés, délimitez la zone voulue
avec le bouton raccourci Bordure du menu POUR RÉSUMER
Accueil : bordures extérieures ou bordure • La page de garde du tableau de bord
épaisse en encadré. doit donner une vue d’ensemble sur la réali-
Certains graphiques peuvent paraître peu sation des missions.
visuels, avec un effet « aplati ». C’est le cas • Les tableaux de bord utilisent fréquemment des
lorsque la série de données présente peu de graphiques sous forme de courbes ou d’histo-
grammes pour montrer des résultats historiques.
variations. Vous devrez alors rechercher à am-
• Pensez à donner des commentaires qualita-
plifier tout écart en adaptant l’échelle des ordon-
tifs dans un encadré ou dans une bulle proche
nées : faites un clic droit sur l’axe, puis adaptez
des graphiques. Votre tableau de bord n’en
la mise en forme de l’axe en changeant les limites sera que plus percutant !
supérieure et inférieure.

f fiche 9
Identifier des règles de variabilité des coûts

© Groupe Eyrolles

160 Créer et mettre en forme un tableau de bord opérationnel

 CONCEVOIR LES FEUILLES DE DÉTAIL DU TABLEAU DE BORD
51
Les feuilles de détail reprennent les indicateurs de performance et les couplent avec leurs indica-
teurs de pilotage, de manière à montrer que la situation est sous contrôle ou pas.

LES OBJECTIFS VISÉS actions progressent comme escompté. Ce forma-

lisme des relations de cause à effet nous incite
Le manager consultera les feuilles de détail pour
savoir plus précisément quelle décision prendre à bien structurer la feuille.
s’il découvre un problème au niveau de la Le contrôleur de gestion sera donc attentif à
page de garde. indiquer en toutes lettres l’indicateur de perfor-
Elles ont pour but de préciser de manière ana- mance au regard de la mission du centre de
lytique les résultats obtenus et de montrer si les responsabilité, puis les variables d’action qui

Cas pratique
À partir du zip, ouvrez le fi chier CH11-1, onglet F51 Page détail
On imagine que le centre logistique et industriel a pour mission de sécuriser les conditions de travail de ses
salariés.
© Groupe Eyrolles

On notera que chaque indicateur comprend un résultat qui est comparé à un objectif.

Créer et mettre en forme un tableau de bord opérationnel 161

FICHE 51
CONCEVOIR LES FEUILLES DE DÉTAIL
DU TABLEAU DE BORD

conduisent la définition des indicateurs de Il faut donc créer un autre axe avec pour échelle
pilotage. des pourcentages. Vous allez insérer le gra-
phique avec les deux séries pour un même axe
COMMENT UTILISER EXCEL ? dans un premier temps. En cliquant droit sur la
Si chaque indicateur présente un résultat et un série des pourcentages, choisissez Option des
objectif, le graphique sera d’autant plus lisible séries, puis Axe secondaire. Le graphique com-
et aéré qu’il les distinguera clairement. Utilisez prendra alors deux axes distincts. Vous pourrez
l’option permettant d’obtenir un type de gra- représenter une série en histogramme et l’autre
phique et une couleur particulière pour chaque en courbe (voir fichier CH11-1, onglet F49 Type
série : histogrammes pour les résultats et graphs).
courbes en rouge pour les objectifs.
Dans un premier temps, insérez un histogramme CONSEIL
simple pour les deux séries de données sélec- Pour faciliter la sélection de la série des pour-
tionnées. Puis, en positionnant votre curseur sur centages, nous vous conseillons d’introduire
la série Objectif, faites un clic droit pour accéder temporairement une valeur élevée, que vous
modifierez une fois le nouvel axe créé.
à l’option Modifier le type de graphique de la
série et choisissez Courbe.
Vous pouvez changer les couleurs des histo-
grammes, courbes ou autres grâce à l’option POUR RÉSUMER
Mise en forme, Remplissage de la série. • Il est possible de faire figurer dans un
Pour éviter la multiplication de graphiques et même graphique deux séries de données avec
orienter des actions, vous souhaiterez peut-être des modes de présentation différents.
présenter certains indicateurs dans un même gra- • Dans Excel, vous utiliserez pour une série un
phique. Vous pourriez, par exemple, faire figurer histogramme et pour l’autre série une courbe
avec une couleur particulière : rouge, par
ensemble le chiffre d’affaires cumulé et le résultat
exemple.
d’exploitation en pourcentage du chiffre d’affaires.
• Utilisez pour cela la commande Modifier le
Mais, sur un même axe, les échelles de ces deux type de graphique de la série puis Mise en
séries seraient disproportionnées et ne permet- forme Remplissage de la série.
traient pas de visualiser le pourcentage de profit.

f fiche 11 f fiche 29
Rechercher graphiquement une saisonnalité des ventes Analyser les causes de variations d’un budget à un autre
© Groupe Eyrolles

162 Créer et mettre en forme un tableau de bord opérationnel

 INSÉRER DES GRAPHIQUES SPARKLINE,
DES LIENS ET DES FORMES AUTOMATIQUES 52
Depuis Excel 2010, nous pouvons multiplier des petits graphiques appelés « sparkline » pour
mettre en évidence les tendances d’une série.

LES OBJECTIFS VISÉS crire rapidement une situation. Les graphiques

Nous avons souligné l’importance de donner un sparkline présentent l’avantage de ne pas

aspect très visuel au tableau de bord, notam- prendre beaucoup de place et de donner l’effet
ment avec la présence de graphiques pour dé- visuel escompté.

Cas pratique
À partir du zip, ouvrez le fi chier CH11-1, onglet F52 Sparkline
On présente dans cet exemple des graphiques sparkline sur l’évolution d’un chiffre d’affaires et d’un résultat
d’exploitation.
© Groupe Eyrolles

Au besoin, l’utilisateur pourra naviguer dans le fichier CH3-1 figurant juste en dessous en lien hypertexte, pour
trouver plus de données sur les ventes. Enfin, le plan d’action pourra être résumé sous forme de schéma avec
des formes originales.

Créer et mettre en forme un tableau de bord opérationnel 163

FICHE 52
INSÉRER DES GRAPHIQUES SPARKLINE,
DES LIENS ET DES FORMES AUTOMATIQUES

On pourra également insérer des liens hyper- Sélectionnez celui qui vous convient et confir-
texte pour renvoyer le lecteur à un autre fi- mez en appuyant sur la touche Entrée. Le cro-
chier. Cette fonctionnalité permet de naviguer quis est inséré dans la feuille. Il ne vous reste
entre des documents souvent volumineux et plus qu’à saisir le texte dans les zones indiquées.
donc guide l’utilisateur dans sa recherche
d’informations. CONSEIL
« Un bon croquis vaut mieux qu’un long dis- Le nouvel outil Analyse rapide propose un choix
cours », disait Napoléon Bonaparte. Le plan de mises en forme conditionnelles ou de gra-
d’action pourra s’illustrer avec une des formes phiques sparkline. En bas à droite de la série
sélectionnée, un bouton Analyse rapide donne
SmartArt proposées par Excel.
accès aux options Mise en forme, Graphiques,
Totaux, Tableaux, Graphiques sparkline. Placez
COMMENT UTILISER EXCEL ?
votre curseur sur une de ces options, et vous
À côté de la série de données, vous viendrez verrez immédiatement l’effet qui sera généré. La
insérer une colonne Tendance qui contiendra le poignée de recopie située en bas à droite d’une
graphique sparkline. Sélectionnez la série puis, cellule sert à créer d’autres graphiques sparkline
à partir d’un graphique existant.
dans le menu Excel, lnsérer Graphiques sparkline
et choisissez le type de graphique voulu. Excel
vous demandera d’indiquer l’emplacement
souhaité.
POUR RÉSUMER
• Excel vous permet d’insérer de nom-
Pour créer un lien hypertexte, sélectionnez la breux objets : graphiques, liens hypertexte et
cellule qui doit contenir le lien puis, dans la illustrations.
barre du menu Excel, optez pour Insertion, Lien • Dans le menu Excel, vous utiliserez lnsérer
hypertexte du groupe Liens. Excel vous deman- Graphiques sparkline, Insertion Lien hypertexte
dera ensuite de préciser le type de lien à créer : du groupe Liens, Insertion, puis Illustrations et
fichier, page Web, adresse e-mail… vous choisirez SmartArt.
• Vous pouvez gagner du temps en utilisant le
La réalisation d’un schéma est très simple sous
bouton Analyse rapide en bas à droite de la
Excel : toujours dans la barre du menu Excel, série sélectionnée : toute une série d’options
choisissez Insertion, puis Illustrations et vous sera proposée.
SmartArt. Une palette de croquis apparaît.

f fiche 8
Représenter graphiquement une tendance et un Pareto
© Groupe Eyrolles

164 Créer et mettre en forme un tableau de bord opérationnel

 RÉALISER LES GRAPHIQUES DE SIMULATION
53
Avec des boutons curseurs, Excel permet de faire varier des paramètres influençant un résultat.
Les effets seront visibles sur un graphique en temps réel.

LES OBJECTIFS VISÉS L’onglet avec les données de base devra com-
prendre six colonnes principales :
Le tableau de bord est un outil d’analyse et de
communication mais sert avant tout à prendre Q les causes de variation ;
des décisions. Il doit comprendre des fonction- Q les valeurs de départ et d’arrivée ;
nalités facilitant la mesure de sensibilité des Q la valeur cumulée ;
paramètres sur lesquels le management est ap-
Q la variation positive ;
pelé à se prononcer. La prise de décision est un
Q la valeur absolue de la variation négative ;
processus itératif qui répond aux questions du
type : « Que se passe-t-il si… ? » Excel accélère Q la valeur de la cause de variation.
ce processus dès lors que les paramètres clefs La valeur de départ est à saisir. Les valeurs des
sont identifiés par le contrôle de gestion. causes de variation seront reliées automatique-
ment aux valeurs calculées avec les boutons
COMMENT UTILISER EXCEL ? curseurs. Les autres données seront calculées
Vous créerez trois feuilles : une avec les données par report et cumul.
de base, une autre pour la réalisation du gra- Vous obtenez la feuille de préparation du gra-
phique et une dernière synthétisant la simulation. phique en sélectionnant les cinq premières

Cas pratique
À partir du zip, ouvrez le fi chier CH11-2 F53, onglet Simu
On souhaite estimer le résultat d’exploitation de FILM+ à partir de quelques hypothèses : lancement de nouveaux
produits, nombre d’agences ouvertes… La mesure de sensibilité de ces hypothèses s’opère à partir des boutons
curseurs situés à gauche de l’image : dès qu’un curseur est déplacé, l’impact sur le résultat d’exploitation est
calculé et met à jour automatiquement le graphique présenté sous forme de « bridge », c’est-à-dire sous forme
d’un pont matérialisant le passage entre deux données de référence.
© Groupe Eyrolles

Créer et mettre en forme un tableau de bord opérationnel 165

FICHE 53
RÉALISER LES GRAPHIQUES
DE SIMULATION

colonnes et en insérant un graphique du type CONSEIL

histogramme empilé. Dans un deuxième temps, Attention, Excel limite la plage des valeurs du
affichez la base des histogrammes des causes de curseur entre 0 et 30 000. Il faut donc leur affec-
variation avec une couleur transparente pour ne ter un coefficient de correction pour trouver les
faire apparaître que la variation à la hausse ou à valeurs d’un cas particulier. Par exemple, si
vous souhaitez faire varier le chiffre d’affaires
la baisse.
de 600 000 € au maximum, vous affecterez à
Dans l’onglet de simulation, pour chaque para- chaque valeur du curseur un coefficient de 20.
mètre de variation, insérez un bouton curseur :
dans la barre de menu Excel, allez sur
Développeur, puis Insérer Contrôle de formu- POUR RÉSUMER
laire et choisissez le bouton curseur pour le • La réalisation de graphiques de simula-
dessiner à l’endroit souhaité. Ces boutons tion dans un tableau de bord facilite la prise
pourront être recopiés autant de fois que néces- de décisions.
saire. Pour chaque bouton, faites un clic droit • La réalisation de graphiques de simulation
pour afficher Format de contrôle et déterminez dans Excel avec des boutons curseurs nécessite
d’être attentif à l’échelle de l’axe des ordon-
dans la zone Contrôle les valeurs limite ainsi
nées de manière à pouvoir toujours faire
que la cellule à laquelle le bouton sera relié. Ce apparaître les valeurs les plus basses comme
sont ces cellules qui seront utilisées pour calcu- les plus élevées.
ler l’effet d’une variation du paramètre.

f fiche 15 f fiche 24
Mesurer la sensibilité des variables et calculer le levier Effectuer des mesures de sensibilité
opérationnel

© Groupe Eyrolles

166 Créer et mettre en forme un tableau de bord opérationnel

 Module 12

RÉALISER DES ÉTUDES

ÉCONOMIQUES AD HOC

E
xcel permet de réaliser des opérations de calcul ou de mise en forme répéti-
tives, mais offre également la possibilité de procéder à des analyses ponc-
tuelles de rentabilité et d’opportunité.
© Groupe Eyrolles

167
Fiche 54
1 Calculer un seuil de rentabilité
et mesurer le risque d’exploitation 169

Fiche 55 Créer un scénario 173

Fiche 56
1 Étudier l’opportunité de différentes alternatives 176
© Groupe Eyrolles
 CALCULER UN SEUIL DE RENTABILITÉ ET
MESURER LE RISQUE D’EXPLOITATION 54
La distinction entre charges variables et charges fixes permet de modéliser la rentabilité d’exploi-
tation d’une entreprise et d’apprécier le niveau de risque encouru.

LES OBJECTIFS VISÉS HD et PRO : ce sont nos deux variables qui,

multipliées par leur marge sur coût variable
La première question qu’un entrepreneur se
unitaire, doivent donner la cible de 2 057 000 €.
pose est : « Quel est le seuil de rentabilité de
Cette formule est la règle saisie dans une cellule
mon affaire, de mes gammes produits ? » Selon
séparée juste en dessous.
la structure des coûts entre coûts fixes et coûts
variables, le seuil de rentabilité variera. Plus le Nous pouvons maintenant utiliser, dans Excel,
niveau de charges fixes sera élevé et plus le la fonction Solveur du menu Données, Analyse.
risque d’exploitation grandira. La marge sur Dans la boîte de dialogue, renseignez les cellules
coûts variables est fonction du niveau d’activité. à utiliser : la cellule C32 est l’objectif de
L’équilibre financier sera atteint dès que le ni- 2 057 000 € et les cellules variables sont les
veau d’activité sera suffisant pour obtenir une cellules qui contiendront les quantités de camé-
marge sur coûts variables égale aux coûts fixes. ras. Vous préciserez les contraintes : quantités
positives, l’objectif doit être égal au montant de
Le seuil de rentabilité sera obtenu en divisant
la cellule coûts fixes + objectif profit, la quantité
les charges fixes par le taux de marge pour un
de caméra Pro doit être inférieure ou égale à
seuil en euros de vente ou par la marge unitaire
2 000 (f illustration page suivante).
pour un seuil en unités.
Quand tout est prêt, cliquez sur Résoudre. Excel
Excel pourra calculer l’optimum de répartition
indiquera s’il a trouvé une solution et l’affichera
des ventes entre plusieurs gammes produits
dans les cellules indiquées dans la boîte de
pour atteindre un objectif de profit déterminé.
dialogue.
COMMENT UTILISER EXCEL ?
Dans la feuille de calcul – zone surlignée en
CONSEIL
jaune –, nous précisons les variables et règles de Si la commande Solveur n’apparaît pas dans le
menu Données, Analyse, allez dans les Options
calcul.
d’Excel, choisissez Compléments, et transférez
Ainsi, nous prévoyons les cellules de destina- Complément Solver et Analysis Toolpack dans
tion des résultats pour les quantités de caméras la zone Compléments d’applications actifs.
© Groupe Eyrolles

Réaliser des études économiques ad hoc 169

FICHE 54
CALCULER UN SEUIL DE RENTABILITÉ ET
MESURER LE RISQUE D’EXPLOITATION

LA BOÎTE DE DIALOGUE PARAMÈTRES DU SOLVEUR

© Groupe Eyrolles

170 Réaliser des études économiques ad hoc

 FICHE 54
CALCULER UN SEUIL DE RENTABILITÉ ET
MESURER LE RISQUE D’EXPLOITATION

Cas pratique
À partir du zip, ouvrez le fi chier CH12-1, onglet F54 Seuil
On veut calculer le seuil de rentabilité des produits FILM+. Le tableau suivant détaille entre les deux gammes de
produit la structure des coûts. Le seuil de rentabilité ainsi calculé est de 2 037 245 €. Si on souhaite atteindre
un profit de 1 200 000 €, il sera donc nécessaire de vendre au moins 7 639 caméras. La répartition de cette
quantité entre les deux gammes doit respecter la composition (aussi appelée « mix ») à 73,21 % et 26,79 %.

Mais si on ajoute une contrainte, par exemple : que le volume de caméras Pro ne puisse excéder 2 000 unités,
on pourra recalculer l’optimum avec la fonction Solveur d’Excel, pour obtenir le résultat ci-dessous.
© Groupe Eyrolles

Réaliser des études économiques ad hoc 171

FICHE 54
CALCULER UN SEUIL DE RENTABILITÉ ET
MESURER LE RISQUE D’EXPLOITATION

POUR RÉSUMER
• Pour calculer le seuil de rentabilité, Excel fournit des outils de paramétrage permettant de résoudre
des équations. Parmi ces outils figure la fonction Solveur dans le menu Données, Analyse.
• Vérifiez que cette fonction est bien présente, sinon vous devrez aller dans les Options d’Excel, puis
Compléments. Vous transférerez Complément Solver et Analysis Toolpack dans la zone Compléments
d’applications actifs.
• Vous devrez définir dans la boîte de dialogue Paramètres du solveur les variables et les valeurs des
contraintes avant de valider par Résoudre.

f fiche 35
Répartir les charges indirectes par centres d’analyse

© Groupe Eyrolles

172 Réaliser des études économiques ad hoc

 CRÉER UN SCÉNARIO
55
Excel vous propose de créer et de mettre en forme automatiquement des scénarios intégrant la
modification de plusieurs paramètres de manière simultanée.

LES OBJECTIFS VISÉS lisent de nombreuses variables : conjoncture

économique, niveau de la concurrence, vitesse
La construction budgétaire repose sur des prévi-
sions dans un environnement très incertain, que d’introduction d’un nouveau produit… S’il faut
ce soit dans le domaine économique, réglemen- construire autant de tableaux qu’il existe de
taire ou technologique. Le contrôleur de gestion variables à modifier, le contrôleur de gestion va
est ainsi amené à bâtir un scénario de base et y passer beaucoup de temps et perdre en effica-
d’autres scénarios alternatifs. Ces scénarios uti- cité.

Cas pratique
À partir du zip, ouvrez le fi chier CH12-1, onglet F55 Synthèse de scénarios
FILM+ peut prévoir différents scénarios pour ses différents secteurs d’activité. On prévoit un chiffre d’affaires
fonction de la conjoncture économique (variables taille de marché : TM et taux de croissance du marché :
TxM), du niveau de concurrence (part de marché PM et prix de vente PVuA), mais également de la réactivité de
l’entreprise (quantité de nouveaux produits QnV et prix de vente de ces nouveaux produits PVuNv).
© Groupe Eyrolles

Le chiffre d’affaires résulte donc de la multiplication des facteurs de conjoncture par les facteurs de concurrence,
auxquels on viendra ajouter les facteurs de réactivité. Le tableau présenté ci-dessus est la synthèse générée
automatiquement par Excel après la création des scénarios.

Réaliser des études économiques ad hoc 173

FICHE 55
CRÉER
UN SCÉNARIO

COMMENT UTILISER EXCEL ? scénarios. Vous créerez quatre scénarios :

Difficultés, Prudent, Base et Optimum.
Vous utiliserez, dans Excel, la fonctionnalité de
Dans la boîte de dialogue qui s’ouvre, renseignez
création de scénario. Vous commencerez par
la valeur de chacune des variables et confirmez
définir les variables, leurs noms et leurs valeurs :
par OK ou Ajouter.
onglet Scénario.
Vous pouvez afficher maintenant chaque scéna-
Sélectionnez la cellule de chaque valeur pour
rio de manière individuelle en cliquant sur
lui donner un nom : soit directement en saisis-
Données, Analyse de scénario, Gestionnaire de
sant le nom dans sa case en haut à gauche de la
scénarios, puis Afficher (f illustration ci-
feuille ou en cliquant sur l’onglet Formules, puis dessous).
Définir un nom dans le groupe Gestionnaire de
Les comparaisons pourront être faites avec un
noms (cette façon de faire permet d’ajouter des
tableau de synthèse : allez dans Données,
commentaires de définition).
Analyse de scénario, Gestionnaire de scénarios,
Créez ensuite des scénarios sur ces variables : puis choisissez Synthèse. Dans un nouvel onglet,
cliquez sur l’onglet Données et choisissez Excel génère le tableau présentant les valeurs
Analyse de scénario, puis Gestionnaire des des variables et leur résultante.

© Groupe Eyrolles

EXEMPLE DE CRÉATION DE SCÉNARIO

174 Réaliser des études économiques ad hoc

 FICHE 55
CRÉER
UN SCÉNARIO

CONSEIL
Pour créer un scénario avec les mêmes variables
POUR RÉSUMER
qu’un scénario déjà existant, il suffit de le sélec- • Excel vous aide à construire vos propres
tionner dans la boîte de dialogue Gestionnaire scénarios avec la fonction Données, Analyse
de scénarios et de cliquer sur Ajouter : les cel- de scénario, Gestionnaire de scénarios.
lules variables du scénario sélectionné seront • Il vous faut, auparavant, définir les variables,
automatiquement reprises. leurs noms et leurs valeurs. Un nom pourra être
Si vous souhaitez permettre qu’un utilisateur affecté à chaque variable avec la chaîne de
vienne changer les variables ou les valeurs d’un commandes suivante : Formules, puis Définir
scénario donné, décochez Changements inter- un nom dans le groupe Gestionnaire de noms.
dits dans la boîte de dialogue Gestionnaire de • Vous n’avez plus qu’à créer le scénario
scénarios. voulu : Données, Analyse de scénario, puis
Gestionnaire de scénarios. Ensuite, renseignez la
valeur de chaque variable et confirmez par OK.

f fiche 39 f fiche 53
Simuler les actions d’amélioration sur les coûts en utilisant les Réaliser les graphiques de simulation
boutons curseurs
© Groupe Eyrolles

Réaliser des études économiques ad hoc 175

56 ÉTUDIER L’OPPORTUNITÉ DE DIFFÉRENTES ALTERNATIVES

Prendre la bonne décision, c’est se poser la question des opportunités possibles et mesurer leur
contribution respective.

LES OBJECTIFS VISÉS tester des résultats en fonction de données

d’entrée. Ce sont souvent des préoccupations
Une des missions critiques du contrôleur de
basiques mais régulières auxquelles le manage-
gestion est de développer la capacité d’action du
ment doit faire face ; Excel présente l’avantage
management en lui fournissant les bons outils d’être simple d’utilisation. De plus, le temps
pour prendre des décisions. Cette aide précieuse d’investissement dans l’outil est limité : si le
peut être menée au jour le jour avec des fichiers besoin change ou disparaît, l’outil pourra être
Excel paramétrés dans lesquels le manager peut abandonné sans grande perte.

Cas pratique
À partir du zip, ouvrez le fi chier CH12-1, onglet F56 Alternatives
FILM+ peut envisager plusieurs options de fabrication. On teste l’alternative Produire en juste à temps (JIT) ou
par lots, puis on compare le gain de main-d’œuvre directe avec le coût de possession des stocks et on génère
la conclusion avec une formule conditionnelle.
On obtient le tableau suivant :

© Groupe Eyrolles

176 Réaliser des études économiques ad hoc

 FICHE 56
ÉTUDIER L’OPPORTUNITÉ
DE DIFFÉRENTES ALTERNATIVES

COMMENT UTILISER EXCEL ? La formule sera donc : =SI(I33>0;« Production

par lots »;« JIT »), la cellule I33 contenant le
Vous utiliserez, dans Excel, la mise en forme des
résultat comparatif.
cellules et la formule =SI.
L’outil d’analyse proposé est constitué de trois
CONSEIL
parties : l’estimation du gain en main-d’œuvre,
Pour faciliter l’utilisation du fichier, il est possible
le calcul du coût de possession des stocks et la
d’insérer des commentaires dans les cellules où
comparaison des deux alternatives pour débou- une donnée est attendue : faites un clic droit,
cher sur la conclusion. De manière à bien séparer puis choisissez Insérer commentaire. Un ban-
ces parties, vous appliquerez un jeu de couleurs deau rouge apparaîtra dans le coin droit supé-
pour chacune d’elles. Après avoir sélectionné la rieur de la cellule contenant le commentaire. En
passant le pointeur de la souris sur cette cellule,
zone à coloriser, dans le menu Accueil, cliquez
vous visualiserez directement le commentaire.
sur l’icône Couleur de remplissage qui repré-
sente un pot de peinture et vous propose toute
la palette. Vous pouvez également cliquer droit
dans la zone, choisir Format de cellule, puis
POUR RÉSUMER
Remplissage. Cette opération peut ensuite être
• Excel permet de réaliser des outils d’aide
répétée au niveau d’une cellule pour la distin-
à la décision pour les managers. Chaque
guer des autres (par exemple, une cellule de fichier ou feuille de travail doivent être struc-
donnée d’entrée ou une cellule de résultat). turés avec des zones bien définies.
Pour générer automatiquement la conclusion, • Il conviendra donc de bien séparer ces par-
vous appliquerez la formule =SI. En effet, selon ties en utilisant la palette de couleurs d’Excel :
le résultat comparatif obtenu : si positif, il est cliquez sur l’icône Couleur de remplissage qui
représente un pot de peinture.
préférable de constituer du stock ; si négatif, on
• Si la zone à renseigner nécessite d’être
retiendra le JIT. La formule = SI (condition ; effet ;
expliquée, vous pouvez insérer un commen-
sinon autre effet) gère des chaînes aussi bien
taire dans une des cellules (clic droit, puis
numériques que textuelles pour indiquer les Insérer commentaire).
effets.

f fiche 32
Identifier les variables d’ajustement majeures
© Groupe Eyrolles

Réaliser des études économiques ad hoc 177

 Module 13

DIFFUSER LES INFORMATIONS

N
ous avons illustré les nombreuses possibilités de calcul et de mise en
forme des résultats que permet Excel. Nous évoquons, dans ce module,
l’utilisation des fichiers Excel dans un cadre de travail partagé et les liens
possibles avec le logiciel de présentation PowerPoint.
© Groupe Eyrolles

Diffuser les informations 179

Fiche 57
1 Partager des classeurs Excel 181

Fiche 58 Utiliser les espaces partagés 183

Fiche 59
1 Créer et utiliser des connexions vers d’autres sources 186

Fiche 60 Protéger des données 188

Fiche 61
1 Lier des fichiers Excel avec PowerPoint
pour une présentation 190
© Groupe Eyrolles
 PARTAGER DES CLASSEURS EXCEL
57
Un manager peut souhaiter visualiser dans un même fichier des données provenant de différents
services : Ressources humaines, Finances, Marketing… Excel offre cette possibilité pratique.

LES OBJECTIFS VISÉS Dans la catégorie Modification, activez la case à

cocher Permettre une modification multi-
Le travail d’un contrôleur de gestion est colla-
utilisateurs.
boratif : il est en contact avec les services
comptables pour les données brutes, les diffé-
rents managers et la direction générale. Ces
différents acteurs peuvent être appelés à parta-
ger une information, voire à compléter ces
données : un service d’ingénierie, par exemple,
indiquera la date de mise en service d’un projet
d’investissement dont la valeur brute a été
renseignée par le comptable pour permettre au
contrôleur de gestion de comparer la charge
d’amortissement de la période avec ce qui avait
été budgété.
Excel a bien sûr prévu ce cas : avec l’option
Partage de Classeur, vous pourrez travailler en
groupe et suivre les modifications effectuées.
BOÎTE DE DIALOGUE OPTIONS DE PARTAGE DU FICHIER

Vous pouvez également paramétrer des options

dans la boîte de dialogue, en cliquant sur l’onglet
Avancé.
Après avoir ouvert un classeur partagé, vous
pouvez entrer et modifier les données comme
dans n’importe quel classeur standard.
SCHÉMA D’UN TRAVAIL PARTAGÉ SOUS EXCEL Si vous souhaitez voir les noms des autres per-
sonnes qui ont ouvert le classeur, optez pour
COMMENT UTILISER EXCEL ? Révision, Partager le classeur, puis Modification.
Vous utiliserez, dans le menu Excel, la com- Si nécessaire, vous pouvez déconnecter des
mande Révision, puis Partager le classeur. utilisateurs du classeur partagé.
© Groupe Eyrolles

Diffuser les informations 181

FICHE 57
PARTAGER DES CLASSEURS
EXCEL

CONSEIL
Un conflit peut se produire quand deux utilisa- POUR RÉSUMER
teurs essaient d’enregistrer des modifications • Il peut être utile de partager un même
qui affectent la même cellule dans un classeur fichier Excel entre plusieurs personnes.
partagé. Excel ne peut en conserver qu’une. La • Celle qui initie le fichier doit paramétrer un
boîte de dialogue Résolution des conflits s’af- partage de la manière suivante : commande
fiche alors. Pour conserver votre modification ou Révision, puis Partager le classeur et, dans
celle des autres utilisateurs, choisissez Accepter l’onglet Modification, Permettre une modifi ca-
la mienne ou sur Accepter l’autre. tion multi-utilisateurs.
Une fois un classeur partagé, il n’est plus pos- • Tous les utilisateurs verront alors qui est
sible de définir une protection du contenu ; il connecté au fichier. En cas de conflit sur une
faut donc s’en préoccuper auparavant. modification de données entre des utilisateurs,
Excel les avertira par un message et deman-
dera quelle version accepter.

f fiche 58
Utiliser les espaces partagés

© Groupe Eyrolles

182 Diffuser les informations

 UTILISER LES ESPACES PARTAGÉS
58
Lorsque l’on souhaite rendre des classeurs accessibles pour la collaboration à partir d’un empla-
cement central, on peut les enregistrer sur un serveur de gestion des documents.

LES OBJECTIFS VISÉS Microsoft propose un compte OneDrive (f pre-

mière illustration page suivante).
Travailler à plusieurs sur un fichier Excel à
distance va permettre d’être plus productif et Il faudra préalablement télécharger l’applicatif
réduira les délais de livraison. Chacun peut OneDrive et vous inscrire en suivant les instruc-
avancer ses travaux sur une partie du fichier tions à l’adresse suivante : [Link]
sans avoir à attendre la fin d’une tâche assignée com/about/fr-fr/
à une autre personne. C’en est fini de l’addition Ensuite, vous pourrez enregistrer tous les fichiers
de feuilles Excel travaillées par les contrôles de que vous souhaitez : Enregistrer sous- OneDrive,
gestion des entités décentralisées. Chaque entité Se connecter (f seconde illustration page sui-
peut mettre à jour la partie du fichier qui la vante).
concerne sans interférer avec les autres et per- Vous disposez de 15 Go d’espace de stockage. Si
mettre ainsi une consolidation automatique. vous avez déjà un identifiant Windows live,
vous n’avez pas besoin de vous réinscrire.
COMMENT UTILISER EXCEL ?
D’autres fournisseurs existent pour des fonction-
Si vous souhaitez partager votre classeur sur un
nalités similaires, Dropbox par exemple.
site Windows SharePoint Services 3.0 et autori-
ser d’autres utilisateurs à collaborer facilement,
CONSEIL
tout en conservant une copie locale du classeur
Si vous souhaitez échanger un classeur avec un
synchronisée à partir des modifications, vous
utilisateur qui possède une version antérieure
pouvez créer et utiliser un site Espace de travail d’Excel, enregistrez-le au format Excel 97-2003
de document. (.xls) ou au format binaire (.xlsx ou .xlsb) et
Un site Espace de travail de document est une travaillez en mode de compatibilité (activé auto-
zone hébergée par un serveur Windows matiquement lorsque vous ouvrez un classeur
Excel 97-2003).
SharePoint Services 3.0 sur lequel les utilisa-
Avec Excel 2013, l’enregistrement dans
teurs partagent des documents et des informa-
OneDrive est natif : votre espace de stockage
tions, gèrent des listes de données et se distant peut être considéré comme un emplace-
tiennent informés des différents états des ment d’enregistrement par défaut. Utilisez l’op-
projets. tion Compte du menu Fichier.
© Groupe Eyrolles

Cas pratique
Dans notre exemple, nous pourrions avoir les filiales allemande et brésilienne de la société FILM+ renseignant,
sur un fichier géré par le siège, leurs prévisions de ventes.

Diffuser les informations 183

FICHE 58
UTILISER LES ESPACES
PARTAGÉS

OUVERTURE D’UN COMPTE ONEDRIVE CHEZ MICROSOFT

© Groupe Eyrolles

ENREGISTREMENT DE FICHIERS VIA ONEDRIVE

184 Diffuser les informations

 FICHE 58
UTILISER LES ESPACES
PARTAGÉS

POUR RÉSUMER
• Vous pouvez partager un fichier sur un site Windows SharePoint Services 3.0 en créant votre propre
compte OneDrive à l’adresse suivante : [Link]
• Ensuite, vous enregistrerez tous les fichiers que vous souhaitez selon cette chaîne de commandes :
Enregistrer sous, OneDrive, Se connecter.
• Vous disposez de 15 Go d’espace de stockage. Des extensions sont possibles.

f fiche 57
Partager des classeurs Excel
© Groupe Eyrolles

Diffuser les informations 185

59 CRÉER ET UTILISER DES CONNEXIONS VERS D’AUTRES SOURCES

Il existe tout un monde de données en dehors de votre classeur Microsoft Excel que vous pouvez
intégrer. Mais comment les trouver, vous y connecter, les importer, les mettre à jour, les sécuriser
et les gérer ?

LES OBJECTIFS VISÉS Pour rechercher des fichiers de connexion, uti-

lisez la boîte de dialogue Connexions existantes :
Le contrôleur de gestion pourra avec Excel créer
sous l’onglet Données, dans le groupe Données
et utiliser des connexions de données.
externes, cliquez sur Connexions existantes.
Lorsque l’on se connecte à des données externes,
Notez les trois données externes habituelles
le principal avantage est que l’on peut les ana-
– Access, Web ou Fichier texte – qui figurent à
lyser régulièrement sans avoir à les copier à
l’extrême gauche de la barre de menu.
chaque fois dans le classeur.
Pour importer des données externes dans Excel, Établissement d’une liaison
il faudra disposer d’un accès à celles-ci, c’est-à- à des données Excel à partir d’Access
dire des mots de passe ou autorisations de Il est possible de lier une plage Excel à une base
l’administrateur de la base de données. De de données Access en tant que table. Cette mé-
nombreuses sources de données requièrent thode est utile lorsque vous envisagez de
également un pilote ODBC (Open DataBase continuer à modifier la plage de données dans
Connectivity) ou un fournisseur OLE DB (Object Excel tout en souhaitant conserver la possibilité
Linking and Embedding DataBase) afin de d’y accéder à partir d’Access.
coordonner le flux de données entre Excel, le
fichier de connexion et la source de données.
Importation de données à partir
du Web
COMMENT UTILISER EXCEL ? Les pages Web contiennent souvent des infor-
Sous l’onglet Données, dans le groupe Données mations qui se prêtent parfaitement à l’analyse
externes, cliquez sur Connexions. Vous pouvez dans Excel : taux de change, cours boursiers,
utiliser cette boîte de dialogue pour effectuer les indices Insee… Vous pouvez utiliser une requête
opérations suivantes : sur le Web pour extraire des données et les
analyser à l’aide des outils et fonctionnalités
Q créer, modifier, actualiser et supprimer les
d’Excel. En un clic de souris, il est ainsi très
connexions en cours d’utilisation dans le
facile d’actualiser les données avec les informa-
classeur ;
tions les plus récentes de la page Web.
Q vérifier la source des données externes
(f illustration ci-contre).
© Groupe Eyrolles

Cas pratique
La PME FILM+ pourrait, en effet, interroger les bases de données des ministères du Tourisme en France, en
Allemagne et au Brésil au moment de préparer ses prévisions de vente de caméras.

186 Diffuser les informations

 FICHE 59
CRÉER ET UTILISER DES CONNEXIONS
VERS D’AUTRES SOURCES

ACTIONS POSSIBLES SUR LES DONNÉES EXTERNES

Importation de fichiers texte

L’Assistant Importation de texte examine le fi-
POUR RÉSUMER
• Vous pouvez avoir besoin de vous
chier texte que vous importez et vous aide à
connecter à des bases de données externes.
vérifier que les données sont importées comme
Excel pourra importer ces données pour autant
vous le souhaitez. que vous disposiez des droits d’accès.
Deux formats de fichier texte sont couramment • Sous l’onglet Données, dans le groupe
utilisés. Données externes, cliquez sur Connexions ou
Connexions existantes.
Q Les fichiers texte délimités (fichiers .txt), dans
• Assurez-vous que les données importées ne
lesquels les champs de texte sont souvent
recèlent pas de virus !
séparés par des tabulations (caractère ASCII,
code 009).
Q Les fichiers texte .csv (valeurs séparées par
une virgule), dans lesquels les champs de
texte sont souvent séparés par des virgules.

CONSEIL
Les connexions externes peuvent parfois provo-
quer des incidents amenant Excel à se fermer.
Au redémarrage de l’applicatif, les fichiers au
moment de l’arrêt seront affichés dans le volet
Récupération de document. Si des réparations
ont été effectuées, cliquer sur Afficher les répa-
rations pour les examiner.

f fiche 6
Importer et mettre en forme des fichiers texte
© Groupe Eyrolles

Diffuser les informations 187

60 PROTÉGER DES DONNÉES

La protection des données est un point sensible dans une entreprise et ne doit jamais être prise à
la légère. Excel vous permet de l’assurer très simplement.

LES OBJECTIFS VISÉS

Pour empêcher que des données importantes

soient accidentellement ou intentionnellement
modifiées, déplacées ou supprimées, il est pos-
sible de protéger certains éléments d’une feuille
de calcul ou d’un classeur. Cette protection
pourra être assurée à l’aide ou non d’un mot de
passe. COMMANDE DE PROTECTION PAR MOT DE PASSE

COMMENT UTILISER EXCEL ? Protéger les éléments d’une feuille

Ajouter un mot de passe de calcul
pour contrôler l’accès aux éléments Sélectionnez les cellules de la feuille de calcul
protégés à protéger ; pour déverrouiller les cellules que
Lorsque vous protégez une feuille de calcul ou d’autres utilisateurs pourront modifier, cliquez
un classeur en verrouillant ses éléments, l’ajout droit sur Format de cellule, puis dans l’onglet
d’un mot de passe pour modifier les éléments Protection, et désactivez la case à cocher
déverrouillés est optionnel. Dans ce contexte, le Verrouillé. Cliquez sur OK.
mot de passe vise uniquement à autoriser l’accès
Protéger les éléments d’un classeur
à certains utilisateurs tout en empêchant les
Sous l’onglet Révision, dans le groupe
modifications par les autres utilisateurs. Ce
Modifications, cliquez sur Protéger le classeur.
niveau de protection par mot de passe ne garan-
tit pas la sécurisation de toutes les données Sous Éléments à protéger, pour protéger la
sensibles dans le classeur. Pour une sécurité structure d’un classeur, activez la case à cocher
optimale, vous devez sécuriser le classeur lui- Structure afin que les fenêtres du classeur
même avec un mot de passe afin de le protéger conservent la même taille et la même position à
contre un accès non autorisé. chaque ouverture de celui-ci, et activez la case
à cocher Fenêtres.
Le chemin de commandes à utiliser est le sui-
vant : Fichier, Enregistrer sous, Outils, Options
générales (f illustration ci-après).
© Groupe Eyrolles

Cas pratique
FILM+ pourrait vouloir protéger, par exemple, dans ses fichiers Excel, les données de base sur l’évolution
de ses prix de vente et ses hypothèses de rémunération salariale.

188 Diffuser les informations

 FICHE 60
PROTÉGER DES DONNÉES

CONSEIL
Pour masquer les formules que vous désirez POUR RÉSUMER
rendre invisibles, cliquez droit sur la cellule, puis • Vos fichiers Excel peuvent contenir des
sur Format de cellule. Dans l’onglet Protection, informations confidentielles que vous voulez
activez la case à cocher Masqué, puis cliquez protéger.
sur OK. • Excel vous permet de les protéger avec un
mot de passe que vous saisirez au moment
de l’enregistrement : Fichier, Enregistrer sous,
Outils, Options générales.
• Attention : si vous perdez votre mot de
passe, Excel ne pourra rien pour vous…

f fiche 46
Définir les formats de présentation
© Groupe Eyrolles

Diffuser les informations 189

61 LIER DES FICHIERS EXCEL AVEC POWERPOINT
POUR UNE PRÉSENTATION

Le contrôleur de gestion est l’analyste des données, mais c’est également lui qui doit en présenter
une synthèse. Il peut utiliser Excel pour ses calculs et analyses et réaliser une présentation
synthétique avec PowerPoint sans avoir à tout recopier.

LES OBJECTIFS VISÉS COMMENT UTILISER EXCEL ?

Autant Excel est adapté pour procéder à des Pour lier un fichier Excel (par exemple, un gra-
analyses poussées, autant PowerPoint est le phique Excel) dans PowerPoint, il vous faut,
standard de présentation. La question se pose dans la diapositive PowerPoint, aller dans le
donc de garder séparés ces deux outils ou de menu Insertion, Objet puis sélectionner un objet
créer des ponts, des liens entre les fichiers gérés existant et enfin Parcourir pour rechercher le
par ces deux applications. document Excel que vous souhaitez lier.

Cas pratique
À partir du zip, ouvrez le fi chier [Link] onglet F61
Le contrôleur de gestion de FILM+ doit réaliser une présentation des ventes pour le directeur commercial. Il
utilisera le fichier de référence Excel pour alimenter une diapositive du fichier powerPoint CH13, fiche [Link].
Il obtiendra le résultat ci dessous.

© Groupe Eyrolles

190 Diffuser les informations

 FICHE 61
LIER DES FICHIERS EXCEL AVEC POWERPOINT
POUR UNE PRÉSENTATION

N’oubliez pas de cocher la liaison avant de vali-

der en cliquant sur OK). POUR RÉSUMER
L’intérêt, c’est que la présentation PowerPoint • Les données ou objets d’un fichier Excel
peuvent être utilisés pour réaliser une présen-
sera automatiquement mise à jour lorsque vous
tation dans un autre outil comme PowerPoint.
aurez à modifier le fichier Excel.
• Excel va créer un lien automatique qui
À l’ouverture de PowerPoint, il faudra confirmer synchronisera les deux outils.
la mise à jour des liaisons. • Pour lier un fichier Excel avec un fichier
On peut tout aussi bien insérer directement une PowerPoint, il faut, dans la diapositive Power-
feuille Excel dans une diapositive PowerPoint Point, utiliser le menu Insertion, puis Objet
pour le sélectionner et enfin préciser le nom
en tant que telle ou sous forme d’icône.
du fichier Excel avec la commande Parcourir.

CONSEIL
Vous pourriez utiliser un lien hypertexte, mais si
le fichier Excel est déplacé dans un autre
répertoire, le lien ne fonctionnera plus. Le co-
pier-coller présente l’avantage de la simplicité
et de la rapidité, mais il ne permettra pas des
mises à jour automatiques en cas de modifica-
tion des données source. C’est cependant une
bonne option si la donnée source d’Excel ne
doit être utilisée qu’une fois.

f fiche 52
Insérer des graphiques sparkline, des liens et des formes automatiques
© Groupe Eyrolles

Principales fonctions Excel 191

 Principales fonctions Excel

Module Fiche Fonction principale Fonction secondaire 1 Fonction secondaire 2

1 1 Enregistrer sous Renommer un classeur

1 2 Vérification des formules F7 =SI

1 3 Macros avec Visual Basic Enregistrer une macro simple =somme_couleur

1 4 Barre outils Accès rapide Personnaliser

1 5 Enregister sous Restaurer

2 6 Importer un fichier texte Rechercher

2 7 Données Trier et Filtrer Figer les volets Afficher côte à côte

2 8 Créer un graphique de Pareto Courbe de tendance =index et = [Link]

2 9 Insérer un graphique Courbe de tendance

2 10 Formule =

Insérer un graphique :
3 11 Format de l'axe
les courbes

3 12 Moyenne Recopie Arrondi

3 13 RechercheV F4 cellule figée Nommer une zone

3 14 Plan, sous-totaux Mise en forme conditionnelle

Insérer un formulaire de
3 15 Développeur – Bouton curseur
contrôle

4 16 Produitmat Sommeprod

4 17 Format cellule Graphique combiné Touche Ctrl pour sélection

4 18 F4 NOM
© Groupe Eyrolles

4 19 SI Affichage nombre

4 20 Copier-coller Ctrl+C ; Ctrl+V

5 21 Lien entre onglets Menu déroulant

193
PRINCIPALES FONCTIONS EXCEL

Module Fiche Fonction principale Fonction secondaire 1 Fonction secondaire 2

5 22 SI ET OU MAX

5 23 VAN et TRI

5 24 F4 Bouton curseur

5 25 Trier

6 26 Copier-coller

6 27 =mois =annee max et min

Dupliquer une feuille

6 28 =somme de feuilles
d'un fichier

6 29 =ABS Histogramme empilé Mise en forme de l'axe

7 30 Formule DECALER

7 31 Tableaux

7 32 Mise en forme des cellules Mise en forme conditionnelle

7 33 Bouton curseur Graphiques

7 34 Formule SI

8 35 Produitmat ; Inversemat

8 36 Arrondi Arrondi,inf ; arrondi,sup

8 37 TCD

8 38 Nom cellules

8 39 Boutons curseurs Histogrammes

9 40 Couleur des noms d'onglets

9 41 Menu déroulant RechercheV

9 42 =VAN et =SI

Les dix valeurs ou les 10 % les

9 43 Mise en forme conditionnelle
plus élevés

10 44 Tableaux croisés dynamiques

10 45 Format cellule Copie de format

10 46 Format cellule Insérer un commentaire Protéger une cellule

10 47 Mise en forme conditionnelle

© Groupe Eyrolles

10 48 Plan, grouper, sous-totaux Dissocier

11 49 Insérer des graphiques Mise en forme d'une série Inverser si négatif

Courbe de tendance et
11 50 Insérer un objet Échelle d'un graphique
équation de régression

194
 PRINCIPALES FONCTIONS EXCEL

Module Fiche Fonction principale Fonction secondaire 1 Fonction secondaire 2

Affecter un type de graphique

11 51 Double axe
à une série de données

11 52 Insérer un graphique sparkline Insérer un lien Insérer une forme SmartArt

11 53 Graphique bridge Bouton curseur

12 54 Solveur

12 55 Scénario

12 56 SI Mise en forme cellules Insérer des commentaires

13 57 Partager le classeur

13 58 One Drive

13 59 Données externes/connexions Récupération de document

13 60 Protéger : classeur, cellule Enregistrer avec mot de passe

13 61 Insérer objet avec liaison Copier-coller

© Groupe Eyrolles

195
 Index

Les numéros renvoient aux principales fiches traitant du sujet.

A Graphiques sparkline 52
Analyse de scénario 55
I
Analyse rapide 52
Axe secondaire 51 Insérer commmentaire 56
Insérer Graphiques 49
B
Barre d’outils Accès rapide 4 M
Boutons curseurs 15, 33, 39, 53 Menu déroulant 21, 41
Bouton Trier 7 Menu Formules 2
Mise en forme conditionnelle 32, 47
C
Mise en forme conditionnelle des cellules 43
Coller 20 Mise en forme de l’axe 29
Commentaires 46
Mise en forme des cellules 56
Copier 20
Mot de passe 60
D
N
Données externes 59
Nom pour une cellule 18
F
P
Filtre 40, 44
Fonction F4 24 Protection 46, 60
Fonctions MIN et MAX 27
S
Format de cellule 17
Formule 12 Solveur 54
Formule =MAX 22 Sous-total 48
Formule =SI 22, 56
T
© Groupe Eyrolles

Formule =SOMMEPROD 16
Formules =ARRONDI 36 Tableaux croisés dynamiques 37, 44

G V
Graphique 8, 9, 11, 17 Validations des données 2

197
 Compléments
àtélécharger
Vous avez acheté cet ouvrage et nous vous en remercions. Vous pouvez à titre gracieux bénéficier d’un
dcertain nombre de compléments à télécharger. Riches en contenu, ils ont été pensés comme un
un prolongement pratique et directement opérationnel du livre papier.

Cet avantage est exclusivement réservé aux acquéreurs de cet ouvrage.

Pour bénéficier de ce contenu via le téléchargement, il suffit de vous connecter à l’adresse suivante et
de télécharger les fichiers :

[Link]

Mention légale : le service de téléchargement proposé ci-dessus est délivré à titre gratuit. Les émetteurs
se réservent le droit de retirer ou de modifier cette possibilité sans préavis et en cas d’utilisations qui
s’avéreraient sans commune mesure avec les ventes du livre. Les documents disponibles sur le CD-Rom
ou à télécharger restent la propriété de l’auteur qui les met à la disposition du lecteur pour son usage
personnel ; en retour celui-ci s’engage à ne pas les diffuser ou à en faire un usage commercial sans
l’accord de l’auteur et de l’éditeur.
Dans la même collection