Blog ENI : Toute la veille numérique !
-25€ dès 75€ sur les livres en ligne, vidéos... avec le code FUSEE25. J'en profite !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. Tableaux de synthèse et tableaux de bord
  3. Tableaux de bord et indicateurs
Extrait - Tableaux de synthèse et tableaux de bord Traitez et analysez de gros volumes de données avec Excel 2016
Extraits du livre
Tableaux de synthèse et tableaux de bord Traitez et analysez de gros volumes de données avec Excel 2016
1 avis
Revenir à la page d'achat du livre

Tableaux de bord et indicateurs

Objectif

Élément clé de vos prises de décision, un tableau de bord doit vous permettre de mettre en évidence les points clés du fonctionnement de l’entreprise. Celui-ci doit synthétiser les données importantes et les présenter sous forme d’indicateurs chiffrés ou graphiques. 

Si les indicateurs choisis sont pertinents, un tableau de bord va vous aider à piloter votre entreprise. Les éléments affichés doivent :

  • être choisis pour leur degré d’importance dans la gestion de l’entreprise,

  • pouvoir être comparés à des valeurs optimales liées à vote activité,

  • être obtenus en temps réel à partir des données actualisées,

  • être le reflet de la réalité économique, logistique, commerciale, environnementale ou de la qualité de votre structure.

Ces indicateurs peuvent appartenir à de multiples catégories telles que :

  • Gestion financière

  • Suivi commercial

  • Gestion de production

  • Positionnement par rapport à la concurrence

  • Production et logistique

  • Suivi qualité

  • Satisfaction clients…

Les domaines à prendre en compte sont multiples et variés, à vous de ne pas vous disperser et de cibler les informations pertinentes à suivre.

Présentation

À titre d’exemple, nous allons concevoir un ensemble de trois petits tableaux de bord.

  • Le premier nous permettra de visualiser les indicateurs de chiffres d’affaires et de qualité.

  • Le deuxième affichera les chiffres d’affaires mensuels et les chiffres par familles de produits.

  • Enfin, le troisième sera ciblé sur les indicateurs de qualité et de suivi des délais de règlements clients.

La navigation entre ces trois tableaux devra être facilitée par des liens hypertextes.

L’entreprise qui va nous servir d’exemple fabrique des menuiseries alu et pvc. Le classeur Menuiseries.xlsx intègre :

  • Deux feuilles de calcul (Fact2015 et Fact2016) recensant les ventes, chacune pour une année.

  • Une feuille de calcul incluant les tableaux de calcul statistiques nécessaires à nos indicateurs (Stat).

  • Trois feuilles (TB1, TB2 et TB3) chacune relative à un des trois tableaux de bord décrits précédemment.

Les premières lignes de la feuille Fact2015 :

images/Chap8-001.PNG

 Ouvrez le classeur Menuiseries.xlsx.

Les tableaux de bord font chacun référence à des formules placées dans des tableaux de calculs intermédiaires. Afin de ne pas surcharger l’affichage de vos indicateurs, vous devrez généralement concevoir des tableaux qui assureront la liaison entre vos données...

Conception du premier tableau de bord

Ce premier tableau (à réaliser dans la feuille TB1) doit nous renseigner rapidement sur les points clés :

  • degré d’atteinte des objectifs,

  • suivi rapide d’indicateurs de qualité, de livraison et de délais de règlements clients,

  • suivi des chiffres d’affaires cumulés et mesure des écarts par rapport à l’année précédente.

images/Chap8-031.PNG

La feuille TB1 a été préparée et inclut un petit tableau (à partir de la colonne O) qui va nous permettre de paramétrer nos premiers indicateurs.

images/Chap8-005.PNG

Nous allons reprendre le principe d’un feu tricolore, l’affichage est le suivant :

  • Si le taux de refus moyen est supérieur à 1 %, un rond rouge doit apparaître, sinon le rond doit être vert.

  • Pour le délai de livraison moyen et le délai de règlement moyen, si la valeur est supérieure à 30 jours, le cercle doit être rouge, vert dans le cas contraire.

  • Si l’objectif a été atteint ou dépassé, le "feu" correspondant doit être vert.

  • Si le chiffre d’affaires réalisé est compris entre 70 % et 99,99 % de l’objectif, le "feu" doit être orange.

  • Si le chiffre d’affaires réalisé est inférieur à 70 % de l’objectif, le "feu" doit être rouge.

Les cercles vont être obtenus par la lettre L minuscule. Une mise en forme conditionnelle va être appliquée à chaque cellule.

 Saisissez un L minuscule dans les cellules E3, E4, E5, E9, E10 et E11.

Le remplissage des cellules étant noir ainsi que la couleur de la police, vous ne pourrez pas visualiser vos saisies. Celles-ci seront visibles dès que la mise en forme conditionnelle aura été appliquée.

 Sélectionnez les trois cellules E3, E4 et E5 puis sélectionnez la police Wingdings et la taille 26 pt.

 Procédez de même pour E9, E10 et E11.

Pour l’instant, vous ne devez rien discerner dans ces cellules.

images/Chap8-006.PNG

Indicateur du taux de refus moyen

 Positionnez-vous en E3.

 Onglet Accueil - groupe Styles, cliquez sur le bouton Mise en forme conditionnelle.

 Cliquez sur Nouvelle règle.

 Dans la boîte de dialogue Nouvelle règle...

Tableau de bord relatif aux chiffres d’affaires

Ce deuxième tableau (à réaliser dans la feuille TB2) doit permettre d’effectuer une comparaison rapide entre les chiffres des prestations de l’année en cours et ceux de l’année précédente. 

  • Comparaison des chiffres d’affaires par famille année n, année n-1.

  • Comparaison des chiffres d’affaires mensuels année n, année n-1.

images/Chap8-030.PNG

Le graphique va être conçu à partir du tableau présent dans la feuille Stat :

images/Chap8-019.PNG

 Activez la feuille Stat.

 Sélectionnez la plage A42 à D45.

 Onglet Insérer - groupe Graphiques - cliquez sur le bouton Insérer un histogramme puis dans la catégorie Histogramme 2D, choisissez Histogramme groupé.

images/Chap8-020.PNG

 Onglet Outils de graphique - Création - groupe Styles du graphique - cliquez sur le Style 8.

Pour l’instant, la courbe des taux de variation n’est pas visible car ses valeurs sont disproportionnées par rapport aux chiffres d’affaires. Nous allons donc paramétrer un axe secondaire pour cette série.

 Onglet Outils de graphique - Format - groupe Sélection active, déroulez le menu Éléments de graphique puis sélectionnez la série "TX VAR".

 Onglet Outils de graphique - Format - groupe Sélection active, cliquez sur Mise en forme de la sélection.

 Dans la fenêtre Mise en forme des séries...

Tableau de bord relatif aux règlements clients et aux taux de refus des produits

Ce dernier tableau (à réaliser dans la feuille TB3) doit permettre de visualiser quelques informations sur la qualité de nos prestations et sur la gestion des règlements clients.

images/Chap8-029.PNG

Les formules de ce tableau ne comportent pas de difficultés particulières.

images/Chap8-028.PNG

 Les formules :

C10 : =MAX(Stat!H10:H21)

C11 : =MAX(Stat!H26:H37)

D10 : =MOYENNE(Stat!H10:H21)

D11 : =MOYENNE(Stat!H26:H37)

C15 : =MOYENNE(Fact2015!$J$2:$J$2000)

C16 : =MOYENNE(Fact2016!$J$2:$J$2000)

D15 : =MAX(Fact2015!$J$2:$J$2000)

D16 : =MAX(Fact2016!$J$2:$J$2000)

F15 : =MOYENNE(Fact2015!$K$2:$K$2000)

F16 : =MOYENNE(Fact2016!$K$2:$K$2000)

G15 : =MAX(Fact2015!$K$2:$K$2000)

G16 : =MAX(Fact2016!$K$2:$K$2000)

Pour finaliser ce tableau nous allons appliquer aux formules des mises en forme conditionnelles.

  • Si les taux de refus sont supérieurs à 2 %, faire apparaître les valeurs en rouge.

  • Si les délais sont supérieurs à 30 jours, faire apparaître les valeurs en rouge.

Taux de refus

 Sélectionnez la plage C10 à D11.

 Onglet Accueil - groupe Styles, cliquez sur le bouton Mise en forme conditionnelle.

 Cliquez sur Nouvelle règle.

 Dans la boîte de dialogue Nouvelle règle de mise en forme, sélectionnez l’option Appliquer une mise en forme uniquement aux cellules...