Excel est un outil de gestion de données mais il peut aussi faire le travail à notre place dans beaucoup de situations. C'est le cas avec les outils d'analyse de simulation. Ces outils permettent de connaître les situations possibles grâce au modèle déjà en place et donc en émettant des hypothèses et connaître les résultats de ces hypothèses. Il existe plusieurs outils sous Excel, mais les deux plus importants que nous allons étudier sont la valeur cible et le solveur.
En effet, vous avez, dans votre classeur, un grand nombre de formule dépendante des unes et des autres. Vous souhaitez faire varier le résultat en changeant les valeurs de certaines cellules. Sans ces outils, vous allez essayer en modifiant les unes après les autres les valeurs sans vous souvenir du résultat que les différentes combinaisons donnent et donc passer un temps fou à connaître la meilleur combinaison possible. Nous allons donc apprendre à créer des scénarios qui vont nous donner les combinaisons les meilleures possibles.
La valeur cible
Commençons doucement avec le premier outil : la valeur cible. Cet outil permet de déterminer le résultat voulu et Excel va changer lui même les valeurs affectant ce résultat pour trouver une combinaison possible et réelle. Vous aurez plus qu'à regarder si les valeurs vous conviennent. Un exemple est plus efficace qu'un long discours.
Prenons cet exemple :
Je souhaite fixer mon prix de vente en fonction du prix d'achat et du taux de marque. Mon prix de vente est calculée par rapport à ces deux valeurs de la façon suivante :
C5=C3/(1-C4) Je souhaite maintenant baisser mon prix de vente. Pour cela, je dispose de deux possibilités : soit je baisse le taux de marque soit je baisse mon prix d'achat. Mon taux de marque, c'est moi qui le fixe, c'est assez facile de le baisser. Le prix d'achat, il va falloir négocier avec le fournisseur…
Sans la valeur cible, comment faire pour avoir 90€ pile dans la cellule C5 ?
Facile ! Je modifie les autres cellules jusqu'à ce que ça tombe jute !
Allez-y, appelez moi quand vous avez trouvé… Bon là, l'exemple est simple et c'est possible assez facilement. Mais lorsque les calculs sont plus complexes, la fonction valeur cible est là !
Hypothèse 1 : on négocie le prix d'achat que l'on va faire baisser. Hypothèse 2 : on accepte de faire baisser notre taux de marque.
Allez c'est parti, testons. Pour ouvrir la fonction valeur cible allez dans l'onglet Données, dans le groupe Outils de données cliquez sur Analyse de scénarios puis sur Valeur cible… une fenêtre s'ouvre.
Il suffit alors de remplir les trois champs :
- La cellule à définir : on ne peut pas la modifier car elle contient la formule. C'est à cette cellule qu'on attribue la valeur fixe.
- La valeur à atteindre : c'est la valeur fixe dont je parle juste avant, c'est la valeur à laquelle doit être égale la cellule que l'on souhaite définir.
- La cellule à modifier : c'est la cellule dont la valeur va être modifiée après avoir cliqué sur OK
Dans notre cas, la cellule à définir est la cellule C5, la valeur à atteindre est de 90 et dans l'hypothèse 1 c'est le prix d'achat qui change donc la cellule C3. Essayez, et voyez le résultat ! Le résultat s'affiche dans une fenêtre comme ceci :
Cliquez sur OK si vous êtes d'accord avec le résultat ou sur annuler pour l'annuler. Si vous cliquez sur Ok, la valeur de la cellule C3 change et passe à 72€.
Maintenant, il n'est plus possible de changer le prix d'achat, il va falloir jouer sur le taux de marque. C'est l'hypothèse 2. Si besoin, remettez la valeur de la cellule C3 à 80 et recommencez la procédure. Dans la fenêtre qui s'ouvre la cellule à définir ne change pas et reste la cellule C5, sa valeur à atteindre ne varie pas non plus et est de 90. Par contre, la valeur à modifier n'est plus C3 mais C4. Cliquez sur Ok et voyez le résultat ! Excel calcul qu'il faut descendre le taux de marque à 11%.
Un outil très simple et efficace pour trouver une valeur. Seulement maintenant, je souhaiterais modifier les deux valeurs pour avoir le même résultat… Je veux bien négocier mais je veux bien aussi baisser mon taux de marque. Pour cela, Excel propose un autre outil : le solveur.
Le solveur
Avant de commencer à l'utiliser, il faudrait qu'il soit disponible. On va donc commencer par l'installer. Pour cela, cliquez sur le gros bouton Office en haut à gauche du logiciel puis sur Options Excel, dans la catégorie Compléments du volet de gauche. En bas de la fenêtre, dans la liste Gérer vérifiez que Compléments Excel soit sélectionné. Cliquez sur le bouton Atteindre, dans la fenêtre qui s'ouvre cochez Complément Solver et cliquez sur Ok. Patientez le temps de l'installation.
Si tout a bien fonctionné, dans l'onglet Données est apparu un groupe tout à droite Analyse avec dans ce groupe Solver.
Voilà, nous sommes parés pour utiliser le solveur.
Les outils d'analyses de simulation sont donc très pratiques et puissants à partir du moment où on les utilise à bon escient. Il faut savoir qu'ils existent pour éviter d'avoir à tâtonner avec les valeurs influençant le résultat.