Google Sheets ne dispose pas d'un outil intégré d'analyse de simulation comme Excel. Cela ne veut pas dire pour autant que vous ne pouvez pas réaliser ce type d'analyse dans Google Sheets. Avec un module complémentaire gratuit comme Goal Seek, vous pouvez réaliser des analyses de simulation pour explorer et anticiper les conséquences de changements futurs possibles. Si vous cherchez plutôt la fonction IF (SI), consultez ce guide sur les fonctions IF dans Google Sheets.
Dans cet article, vous allez découvrir la différence entre l'analyse de simulation et l'analyse de sensibilité, et comment utiliser le module complémentaire Goal Seek de Google Sheets pour réaliser des analyses de simulation sur plusieurs scénarios. Pour en savoir plus sur ce module, consultez notre article sur comment utiliser Goal Seek dans Google Sheets.
Quelle est la différence entre l'analyse de simulation et l'analyse de sensibilité ?
Dans une analyse de simulation, on envisage différentes situations, ou "scénarios", en modifiant certaines variables. Parmi d'autres, deux types d'analyse de simulation sont très répandus : l'analyse de scénarios et l'analyse de sensibilité.
Dans l'analyse de sensibilité, l'accent est mis sur les effets des changements d'une variable précise ; ce type d'analyse explique comment des variables (dépendantes) sont affectées par les changements apportés à une autre variable (indépendante). Dans l'analyse de scénarios, l'accent est mis sur les effets des changements de plusieurs variables ; ici, les différents scénarios correspondent à ces variables qui changent. Par exemple, le scénario le plus pessimiste pourrait combiner des coûts supérieurs à la moyenne et des ventes minimales.
Comme vous pouvez le voir, ces deux types d'analyse de simulation peuvent être très utiles : ils vous permettent de vous préparer au changement ou de prendre des risques calculés. Dans la section suivante, vous allez apprendre à réaliser des analyses de simulation dans Google Sheets.
Analyse de simulation avec Goal Seek dans Google Sheets
En configurant différents scénarios dans votre feuille de calcul, vous pouvez utiliser Goal Seek pour réaliser une analyse de simulation. Qu'il s'agisse d'une analyse de sensibilité ou de scénarios dépendra du nombre de variables que vous modifiez et de la mesure dans laquelle vous testez chacune d'elles.
Suivez les étapes ci-dessous pour installer le module complémentaire Goal Seek pour Google Sheets, configurer plusieurs scénarios et utiliser Goal Seek pour modifier plusieurs variables.
Installer le module complémentaire Goal Seek dans Google Sheets
Comme pour la plupart des produits et fonctionnalités Google, il vous suffit d'un compte Google et de quelques minutes. Suivez les étapes ci-dessous pour trouver et installer Goal Seek dans Google Sheets.
1. Ouvrez Google Sheets et allez dans Extensions > Modules complémentaires > Télécharger des modules complémentaires.
2. Dans Google Workspace Marketplace, recherchez Goal Seek, cliquez sur le module complémentaire, puis sur "Continuer".
3. Une fenêtre pop-up vous demande d'autoriser Goal Seek à accéder à votre compte. Cliquez sur "Autoriser" pour terminer l'installation du module complémentaire.
4. Une fois le module complémentaire installé, un message de confirmation semblable à celui ci-dessous s'affiche.
Configurer des scénarios de simulation
Dans la feuille de calcul d'exemple ci-dessous, vous pouvez voir trois copies du jeu de données initial. Ici, l'objectif est d'atteindre un bénéfice de 20000 $ et d'analyser différents scénarios.
Dans le premier, vous verrez les effets d'une modification du prix de vente ; dans le deuxième, les conséquences d'une modification du nombre d'unités vendues ; et dans le troisième, les effets d'une modification du coût unitaire.
Analyse de simulation dans Google Sheets avec Goal Seek
Vous pouvez utiliser Goal Seek pour réaliser une analyse de simulation sur plusieurs scénarios en l'exécutant sur plusieurs copies de vos données, dans lesquelles vous définissez votre objectif et indiquez les variables à modifier.
Paramètres obligatoires de Goal Seek
Goal Seek comporte trois paramètres obligatoires :
- Cellule cible : Il s'agit de la cellule contenant la formule qui relie les autres cellules entre elles. Goal Seek veillera à ce que le résultat de cette formule corresponde à la valeur que vous avez définie comme cible.
- Valeur cible : Il s'agit de la valeur que nous voulons obtenir dans la cellule cible.
- Cellule variable : Il s'agit de la cellule que Goal Seek peut modifier pour atteindre la valeur cible dans la cellule cible.
Paramètres facultatifs de Goal Seek
Vous pouvez aussi configurer certains paramètres facultatifs dans Goal Seek :
- Nombre maximal d'itérations : la valeur par défaut est 200. Vous pouvez augmenter ce nombre pour que Goal Seek fasse plus de tentatives pour trouver une solution, mais cela dépendra aussi de la limite de temps que vous fixez.
- Tolérance : la valeur par défaut est 0,0001. Vous pouvez augmenter ce nombre si votre solution n'a pas besoin d'être aussi précise.
- Limite de temps maximale : la valeur par défaut est de 120 secondes. Vous pouvez augmenter ce nombre pour des calculs plus complexes. Les calculs s'arrêtent dès que cette limite est atteinte, quel que soit le réglage "Max Iterations".
1. Allez dans Extensions > Goal Seek > Open.
2. Le panneau latéral Goal Seek s'affiche à droite de votre feuille de calcul, avec les sections "Settings", "Solve Status" et "History".
3. Cliquez sur la cellule contenant la formule du bénéfice, puis sur l'icône de grille à côté de "Set Cell" dans le panneau latéral. Si vous placez le curseur sur l'icône de grille, le texte "Capture selected cell" apparaît, comme sur la capture d'écran ci-dessous.
4. Saisissez ensuite la valeur cible ("Target value") pour la "Set Cell". Ici, "20000".
5. Cliquez sur la cellule contenant la valeur que Goal Seek doit modifier, puis sur l'icône de grille à côté de "By Changing Cell" dans le panneau latéral. Ici, le prix de vente du scénario 1, dans la cellule B14.
6. Pour modifier le nombre d'itérations, la tolérance et/ou la limite de temps, cliquez sur "Options". Ici, j'utilise les valeurs par défaut.
7. Une fois que tout est prêt, cliquez sur "Solve". Avec les options par défaut utilisées ci-dessus, le calcul de la solution prendra au maximum deux minutes. Si Goal Seek ne trouve pas de solution, essayez d'augmenter la tolérance, ou la limite de temps et le nombre d'itérations.
8. Lorsque Goal Seek trouve une solution, une fenêtre pop-up semblable à celle ci-dessous s'affiche. Vous pouvez voir la solution directement dans la cellule ou dans "Solve Status", en bas du panneau latéral.
9. Configurons maintenant les réglages du scénario 2. Sélectionnez d'abord la "Set Cell" et la "Target Value". Ici, la "Set Cell" contient la formule du bénéfice dans la cellule E20, et la valeur cible apparaît dans "To Value", soit 20000.
10. Une fois les deux valeurs définies, cliquez sur la cellule contenant la valeur variable. Cliquez sur "Solve" lorsque les réglages vous conviennent.
11. La fenêtre pop-up "Goal Seek Complete" s'affiche à nouveau.
12. Enfin, configurez les réglages du scénario 3 en suivant les mêmes étapes que précédemment, puis cliquez sur "Solve" pour terminer.
13. Et voilà. La solution du scénario 3 est disponible, comme illustré ci-dessous.
14. Vous devriez voir les résultats obtenus avec Goal Seek pour les trois scénarios ; les cellules variables ont un fond vert et les cellules cibles, un fond rouge.
Conclusion
Vous pouvez réaliser des analyses de simulation directement dans Google Sheets avec Goal Seek. Vous pouvez configurer différents scénarios avec des objectifs différents et choisir n'importe quelle variable comme cellule variable. Comme Goal Seek conserve un historique des tentatives, vous pouvez toujours revenir à des solutions précédentes.
Après avoir lu cet article, vous savez comment installer le module complémentaire Goal Seek, configurer des scénarios dans Sheets et les analyser avec Goal Seek. Pour en savoir plus sur Goal Seek, découvrez comment utiliser Goal Seek dans Google Sheets.
Pour des outils statistiques comme la régression, essayez le module complémentaire XLMiner Analysis ToolPak. Pour confronter vos scénarios aux résultats réels, découvrez comment identifier et analyser les écarts entre budget et réalisé, ou partez d'un modèle de compte de résultat.
