Editions ENI Livres | Vidéos | e-Formations
Analyse des données et simulation
Introduction
Ce chapitre vous propose de découvrir plusieurs outils d’analyse et de simulation d’Excel. Ils permettent non seulement de calculer, mais aussi de comparer différents scénarios, d’anticiper une évolution à partir de données connues, ou encore de rechercher la valeur à atteindre pour satisfaire une condition donnée.
Vous verrez notamment comment utiliser des tables de données, établir des prévisions à partir de données antérieures, construire un diagramme de Pareto, recourir à la valeur cible et mettre en œuvre le solveur pour résoudre des problèmes sous contraintes.
Utilisation des tables de données
Une table de données est une plage de cellules organisée pour afficher automatiquement les résultats produits par une formule lorsqu’on fait varier une ou plusieurs valeurs d’entrée. Excel s’appuie sur une formule de référence et recalcule, dans le tableau, le résultat correspondant à chaque hypothèse testée.
Ce dispositif permet de comparer rapidement plusieurs scénarios sans avoir à réécrire ou recopier les formules à chaque fois. Il est particulièrement utile pour mesurer l’effet d’un taux, d’une durée, d’un montant ou de toute autre donnée d’entrée sur un calcul.
Nous verrons d’abord les tables de données à une variable, puis à deux.
Créer une table de données à une variable
Dans une table de données à une variable, une seule donnée d’entrée est modifiée, tandis que les autres paramètres du calcul restent inchangés. Cette méthode permet d’observer rapidement l’effet de cette variation sur un ou plusieurs résultats. Dans l’exemple qui suit, vous allez étudier l’impact de différents taux d’emprunt sur le montant des mensualités et sur le coût total du crédit. La table de données permettra ainsi de comparer plusieurs hypothèses à partir d’une même formule de départ, afin d’identifier la solution la plus compatible avec votre budget.
Ouvrez le classeur ExemplesAnalyse.xlsx. Activez la feuille TD1V.
Cette feuille permet de calculer les mensualités et le total des intérêts pour rembourser un capital emprunté en utilisant la fonction VPM().
Sa syntaxe est la suivante :
=VPM(taux d'intérêt mensuel;nombre de mensualités;capital
emprunté;valeur future de l'achat;type de paiement) La fonction VPM a pour paramètres obligatoires le taux d’intérêt mensuel, le nombre de mensualités envisagé, le capital emprunté et deux options facultatives la valeur future de l’achat (0 si omis) et le type de paiement (en début ou fin de mois).
Le résultat de ce calcul, correspondant...
Obtenir des prévisions à partir de données antérieures
Principe
Lorsque vous disposez d’informations sur une période révolue, vous pouvez prolonger la tendance afin d’estimer l’évolution future. Ce type de prévision ne constitue pas une certitude, mais une projection fondée sur les informations déjà connues. Il est surtout pertinent lorsque les données présentent une évolution suffisamment régulière pour servir de base à une estimation.
Cette démarche peut s’appuyer sur un graphique représentant les données passées, ou sur une fonction permettant de modéliser la tendance observée. Il est ainsi possible d’estimer une valeur future à partir de l’évolution enregistrée jusque-là.
Activez la feuille Prévisions du fichier ExemplesAnalyse.xlsx.
Depuis que vous avez mis en ligne votre nouveau site web, vous avez relevé son indice de fréquentation sur 52 semaines, et avez tracé la courbe représentant le nombre de connexions par semaine.
Les données saisies et le graphique correspondant
Vous souhaitez connaître quelle sera sa fréquentation dans X semaines, l’évolution garde le même rythme.
Calculer l’équation de la droite de régression linéaire avec une formule...
Conception d’un diagramme de Pareto à partir de vos données
Principe du diagramme de Pareto
Un diagramme de Pareto est un graphique qui présente des catégories classées par ordre décroissant selon la valeur qui leur est associée, par exemple un nombre d’occurrences, une durée, un coût ou un chiffre d’affaires. Il permet de visualiser immédiatement quelles catégories pèsent le plus dans l’ensemble observé et lesquelles ont un impact plus limité. Dans de nombreuses situations, tous les problèmes ou toutes les tâches n’ont pas le même poids. Certaines difficultés reviennent beaucoup plus souvent que d’autres, certains types d’erreurs concentrent l’essentiel des incidents et certaines activités apportent une valeur bien plus forte que le reste. Le diagramme de Pareto permet précisément de faire apparaître cette hiérarchie visuelle, afin d’aider à décider par où commencer..
Cette logique est souvent rapprochée de la règle des 80/20 : une part relativement réduite des causes peut représenter la majeure partie des effets. Par exemple, 80 % des erreurs peuvent se concentrer dans 20 % des types d’erreurs, ou une faible part de l’activité peut mobiliser l’essentiel du temps ou produire la plus forte valeur ajoutée. Ce type d’analyse aide à répondre à des questions concrètes : quelles sont les difficultés les plus fréquentes ? Quelles tâches prennent le plus de temps ? Quels éléments contribuent le plus fortement au résultat ou à la valeur créée ?
Présentation du cas d’étude
Votre société commercialise des sorbets aux fruits. La liste des ventes de l’année est enregistrée ans la feuille Sorbets du fichier ExemplesAnalyse.xlsx.
Vous voulez connaître les montants et pourcentages des ventes pour chaque parfum en les affichant dans un tableau présentant les ventes par ordre décroissant de valeurs.
Comme vous souhaitez automatiser au maximum les calculs et la présentation vous avez décidé de simplifier les formules chaque fois que cela pourra se faire....
Effectuer des simulations
Les outils de simulation permettent d’étudier l’effet de différentes hypothèses sur un résultat, ou, à l’inverse, de rechercher les valeurs à atteindre pour satisfaire un objectif donné.
Dans cette partie, vous allez découvrir deux démarches complémentaires : la valeur cible, qui agit sur une seule variable, puis le solveur, qui permet de traiter des situations plus complexes en tenant compte de plusieurs variables et de différentes contraintes.
À l’aide de la valeur cible
La valeur cible permet de déterminer quelle valeur il faut entrer dans une fonction pour obtenir un résultat souhaité. Cet outil est utile lorsqu’un calcul dépend d’une variable unique que vous souhaitez ajuster pour satisfaire un objectif précis.
Reprenez le fichier ExemplesAnalyses.xlsx et activez la feuille VC1.
Calcul d’un taux de marge
Cette feuille calcule le prix de vente d’un produit, à partir de son coût de production, la marge souhaitée et le taux de TVA. Le dernier calcul du prix de vente est de 3457,38 €, et votre unique concurrent vend son produit 3200,00 €. Vous décidez de passer votre prix de vente à 3150,00 €.
Votre coût de production est très justement calculé et ne peut être diminué. La TVA est non modifiable. Vous ne pouvez "jouer" que sur la marge.
Quelle sera votre marge dans cette configuration ?
Pour déterminer la marge, nous allons utiliser la nouvelle méthode de la valeur cible.
Sélectionnez la cellule B6. Elle contient la formule =B4*(1+B5) permettant de calculer le prix de vente.
Dans l’onglet Données et le groupe Prévision, cliquez sur le bouton Analyse scénarios puis sélectionnez Valeur cible.
La boîte de dialogue Valeur cible s’ouvre.
La boîte de dialogue Valeur cible
La cellule qui contient le prix actuel que nous souhaitons ajuster à 3150 est la cellule B6.
Dans le champ Cellule à définir, saisissez B6.
Nous souhaitons que le nouveau prix soit de 3150 €.
Dans le champ Valeur à atteindre, saisissez 3150.
La seule valeur que vous pouvez modifier pour obtenir le nouveau prix est la marge située en cellule...