ANALYSE ET SIMULATION Excel 2010

ANALYSES ET SIMULATIONS SUR TABLEUR l. MODÈLE ITÉRATIF 1 – NOTION D’ITÉRATION Dans certains modèles, il arrive qu’une formule renvoie indirectement à ellemême. La formule utilise son propre résultat dans son calcul. En résumé, elle boucle : on parle alors de référence circulaire. Pour résoudre ce type de problème, Excel procède par itérations, ce qui consiste à répéter le calcul en tenant compte à chaque fois du résultat du calcul précédent.

Une référence circulaire peut être soit divergente (elle n’amène aucun résultat significatif), soit conv en org Exemple 1 : Ouvrez le classeur Ex Objectif : Calculer la 5,25 % du Sni* to View rs une valeur). e Iteration. xls rcial qui représente Chiffre des ventes net. Le chiffre des ventes net dépend du montant de la participation : les formules en B3 et 84 contiennent une référence circulaire car chacune fait référence à l’autre. Solution : Participation = 74. 82 KE Exemple 2 Résoudre l’équation à deux inconnues suivante : et Y=X/S Solution : 3. 9 et Ye2. 78 (arrondi à 2 décimales) 2 – AUTORISER LE CALCUL ITERATIF Par défaut, Excel n’effectue pas d’itération. Quand une référence aisissez le nombre maximum de boucles autorisées dans la formule • <Écart maximal> : saisissez la valeur d’écart entre deux calculs ? partir de laquelle l’itération doit s’arrêter • Cliquez sur «OK» A. Rilassi 2. TABLE D’HYPOTHÈSES Cette fonction vous permet de tester plusieurs hypothèses pour une formule. Il peut y avoir une ou deux variables et les résultats sont présentés dans un tableau. – TABLE A SIMPLE ENTREE (UNE VARIABLE) L’exemple suivant est un modèle qui permet de calculer le montant du remboursement mensuel pour un prêt. Il utilise pour cela la fonction : VPM (valeur des paiements). L’objectif est de voir quelle est Pinfluence d’une variation du taux d’intérêt sur le montant du remboursement mensuel. Ouvrez le classeur Excel 2 – Simulation Analyse Emprunt 1 variable. xls: • Saisissez les données utillsées par la formule, de 83 à 85 • Créez la formule en C7 . nées*12;Montant_de_l emprunt) • Nous voulons tester l’influence d’une variation du taux d’intérêt.

Saisissez de *AGF 9 rif q affiche les montants des remboursements en fonction des taux saisis : 2 – TABLE A DOUBLE ENTREE (DEUX VARIABLES) fonction VPM (valeur -2- es paiements). L’objectif est de voir quelle est l’influence d’une variation du taux d’intérêt et d’une variation de la durée du prêt sur le remboursement mensuel. Ily a donc deux variables : le taux et la durée. Ouvrez le classeur Excel 3 – Simulation Analyse Emprunt a double entrée xls • Créez la formule en 37 . • Saisissez de C 7 à F7 les valeurs à tester pour la première variable (la durée). ?? Saisissez de B8 à BI 1 les valeurs à tester pour la seconde variable (le taux) • Sélectionnez la plage de cellules contenant la formule et les valeurs de test, de B7àF11. • Sous l’onglet Données, cliquez sur Analyse de scénarios, puis sur Table de données. • Saisissez la référence de I PAGF3C,Fq quelle doit être la valeur de l’une des données utilisées par le calcul pour que ce résultat soit atteint. Exemple : le modèle suivant calcule le montant du remboursement mensuel pour un emprunt, en fonction du montant de l’emprunt, sa durée et le taux d’intérêt.

Question : sachant que la capacité de remboursement du client est de 500 € pa mois, combien peut-il emprunter ? Ouvrez le classeur Excel 4 – Simulation Analyse Valeur Cible. ls Nommez les cellules et saisissez les formules. • La formule en B7 : = VP 1/1 2; ursm nées* -3- • Sélectionnez la cellule contenant la formule 87. • Données, cliquez sur Analyse de scénarios, puis Valeur cible • ndiquez la valeur à atteindre pour la formule (ici, 500) • ndiquez la cellule à modifier (ici, le montant de l’emprunt : 23) Excel affiche alors le résultat de sa recherche : 4.

SOLVEUR Le solveur permet de chercher les valeurs que doivent avoir certaines variables pour que le résultat d’un calcul soit o timisé (maximal, minimal ou égal à une bornes dans lesquelles doivent rester certaines valeurs. Il s’agit d’une macro complémentaire qui doit être installée si cela n’a pas encore été fait. 1 – INSTALLER LE SOLVEUR • Cliquez sur l’onglet Fichier, sur Options, puis sur la catégorie Compléments. • Dans la zone Gérer, cliquez sur Compléments Excel, puis Atteindre… ?? Dans la zone Macro Complémentaires disponibles, activez la case à cocher Complément Solveur, puis cliquez sur OK. RECHERCHER UNE VALEUR QUI EN MAXIMISE UNE AUTRE Objectif : ce modèle calcule le bénéfice généré par la vente d’un produit. La colonne C affiche, pour information, les formules de la colonne B. La particularité de ce modèle est d’émettre l’hypothèse que le nombre d’unités vendues 86 dépend du prix de vente et suit la règle suivante : Formule de la cellule En termes clairs, plus le produit est cher, plus la marge unitaire est élevée mais moins on fait de ventes, et inversement.

Vaut-il mieux vendre IO 000 raduits à 50 € ou seulement 5 000 mais à 75 € ? • Indiquez si sa valeur doit être maximale, minimale ou égale à une valeur précise • Saisissez la référence de la cellule variable, elle que le solveur est autorisé à modifier (ici, le prix : 84) Cliquez sur «Résoudre» Si le solveur trouve une solution, il affiche un dialogue de ce type • Cochez Garder la solution du solveur pour que la valeur trouvée remplace l’ancienne Solution : le bénéfice maximal est de 78125 € pour un prix de vente de 70 €. 5- 3 – AJOUTER DES CONTRAINTES Pour que le modèle soit réaliste, il est souvent nécessaire de limiter la marge de fluctuation de certaines valeurs. Dans cet exemple, nous pouvons ajouter les contraintes sulvantes : — La marge unitaire doit être supérieure à 20 euros. ?? Le stock étant de 3 250 unités, le nombre d’unités vendues ne doit pas dépasser 3 250. • Données/SoIveur est un ensemble nommé de valeurs d’entrée que vous pouvez réappliquer à volonté à un modèle. Lors de la création d’un scénario, Il vous faudra préciser les cellules variables et les valeurs à utiliser dans ces cellules. – CRÉER UN SCÉNARIO Exemple : Nous allons créer deux scénarios pour ce budget : une hypothèse haute et une hypothèse basse. • Ouvrez le classeur Excel 6 – Simulation Analyse Scenarios. xls • Nommez les cellules et saisissez les formules en 85, 89 et BIO. 6- • Données/Analyse scénarios/Gestionnaire de scénarios • Cliquez sur «AJouter» • Saisissez le nom du scénario • Spécifiez les adresses des cellules variables en les séparant par des points-virgules Modifiez la valeur des variables . ?? Marge Bénéficiaire : 25% Charges externes : 75000 • Charges de personnel : 180000 • Autres Charges : 65000 • Cliquez sur «Ajouter» Passez à la création du scé tapez en les séparant avec des points-virgules les noms des cellules calculées dont vous voulez visualiser les résultats dans les diverses hypothèses (BI O). Excel crée une feuille Synthèse de scénarios et y place la synthèse de vos divers scenarios 7 – CONSOLIDATION Cette fonction permet de consolider plusieurs tableaux ayant des libellés identiques, dans une nouvelle feuille de calcul.

Deux possibilités – Les tableaux à consolider ont une structure identique, ils contiennent les mêmes libellés et dans le même ordre : on les consolidera à Faide de leur référence. – Les tableaux à consolider ont la même structure, mais leur taille est différente ou leurs libellés ne sont pas dans le même ordre : on les consolidera par catégories (libellés). – CONSO IDER PAR RÉFÉRENCES Exemple : vous disposez de ces deux feuilles de calcul, chacune présentant les résultats d’un trimestre. Vous désirez les consolider pour obtenir les résultats du 1er semestre et du 2e trimestre aur chaque commercial. onsolidation à effectuer (généralement une somme) • Référence : • Sélectionnez la première feuille à consolider en cliquant sur son onglet • Sélectionnez dans la feuille la plage de données à consolider (iCi : 34 à 310) • Recommencez pour tous les autres tableaux à consolider : les noms s’accumulent dans la zone Références sources ?? Cliquez sur «OK» pour terminer Remarque : dans le dialogue de consolidation, vous pouvez cocher Xll_ier aux données source pour créer une liaison permanente entre le tableau consolidé et les tableaux sources.

Le tableau généré sera alors en mode Plan et mis à jour en permanence. 2 – CONSOLIDER PAR LIBELLES Ouvrir le classeur Excel 8 – Simulation Analyse Consolidation par Libelles. xls • Nommez les cellules et saisissez la formule de la cellule ag -9- Créez une nouvelle feuille et placez le curseur là où le tableau à consolider doit débuter • Données/Consolider