Analyse hypothétique avec des tableaux de données

Avec un tableau de données dans Excel, vous pouvez facilement faire varier une ou deux entrées et effectuer une analyse hypothétique. Un tableau de données est une plage de cellules dans laquelle vous pouvez modifier les valeurs de certaines cellules et proposer différentes réponses à un problème.

Il existe deux types de tableaux de données -

• Tableaux de données à une variable
• Tableaux de données à deux variables

Si vous avez plus de deux variables dans votre problème d'analyse, vous devez utiliser l'outil Gestionnaire de scénarios d'Excel. Pour plus de détails, reportez-vous au chapitre - Analyse des hypothèses avec Scenario Manager de ce didacticiel.

Tableaux de données à une variable

Une table de données à une variable peut être utilisée si vous souhaitez voir comment différentes valeurs d'une variable dans une ou plusieurs formules modifieront les résultats de ces formules. En d'autres termes, avec une table de données à une variable, vous pouvez déterminer comment la modification d'une entrée modifie un nombre quelconque de sorties. Vous comprendrez cela à l'aide d'un exemple.

Example

Il y a un prêt de 5 000 000 pour une durée de 30 ans. Vous souhaitez connaître les mensualités (EMI) pour des taux d'intérêt variés. Vous voudrez peut-être également connaître le montant des intérêts et le principal payés la deuxième année.

Analyse avec table de données à une variable

L'analyse avec un tableau de données à une variable doit être effectuée en trois étapes -

Step 1 - Définissez l'arrière-plan souhaité.

Step 2 - Créez la table de données.

Step 3 - Effectuer l'analyse.

Comprenons ces étapes en détail -

Étape 1: définir l'arrière-plan requis

• Supposons que le taux d'intérêt soit de 12%.

• Répertoriez toutes les valeurs requises.

• Nommez les cellules contenant les valeurs afin que les formules aient des noms au lieu de références de cellules.

• Définissez les calculs pour EMI, Cumulative Interest et Cumulative Principal avec les fonctions Excel - PMT, CUMIPMT et CUMPRINC respectivement.

Votre feuille de calcul doit ressembler à ceci -

Vous pouvez voir que les cellules de la colonne C sont nommées comme indiqué dans les cellules correspondantes de la colonne D.

Étape 2: créer la table de données

• Tapez la liste des valeurs, c'est-à-dire les taux d'intérêt que vous souhaitez remplacer dans la cellule d'entrée en bas de la colonne E comme suit -

Comme vous le voyez, il y a une ligne vide au-dessus des valeurs de taux d'intérêt. Cette ligne concerne les formules que vous souhaitez utiliser.

• Tapez la première fonction (PMT) dans la cellule une ligne au-dessus et une cellule à droite de la colonne de valeurs. Tapez les autres fonctions (CUMIPMT and CUMPRINC) dans les cellules à droite de la première fonction.

  Maintenant, les deux lignes au-dessus des valeurs de taux d'intérêt se présentent comme suit:

Le tableau de données ressemble à celui ci-dessous -

Étape 3: Faites l'analyse avec l'outil de tableau de données d'analyse hypothétique

• Sélectionnez la plage de cellules contenant les formules et les valeurs que vous souhaitez remplacer, c'est-à-dire sélectionnez la plage - E2: H13.

• Cliquez sur l'onglet DONNÉES sur le ruban.

• Cliquez sur What-if Analysis dans le groupe Data Tools.

• Sélectionnez Table de données dans la liste déroulante.

Data Table la boîte de dialogue apparaît.

• Cliquez sur l'icône dans la zone de cellule d'entrée de colonne.
• Cliquez sur la cellule Interest_Rate, qui est C2.

Vous pouvez voir que la cellule d'entrée Colonne est considérée comme $ C $ 2. Cliquez sur OK.

Le tableau de données est rempli avec les résultats calculés pour chacune des valeurs d'entrée comme indiqué ci-dessous -

Si vous pouvez payer un EMI de 54 000, vous pouvez constater que le taux d'intérêt de 12,6% vous convient.

Tableaux de données à deux variables

Une table de données à deux variables peut être utilisée si vous souhaitez voir comment différentes valeurs de deux variables dans une formule modifieront les résultats de cette formule. En d'autres termes, avec une table de données à deux variables, vous pouvez déterminer comment la modification de deux entrées modifie une sortie unique. Vous comprendrez cela à l'aide d'un exemple.

Example

Il y a un prêt de 50 000 000. Vous voulez savoir comment différentes combinaisons de taux d'intérêt et de durée de prêt affecteront le paiement mensuel (EMI).

Analyse avec tableau de données à deux variables

L'analyse avec un tableau de données à deux variables doit être effectuée en trois étapes -

Step 1 - Définissez l'arrière-plan souhaité.

Step 2 - Créez la table de données.

Step 3 - Effectuer l'analyse.

Étape 1: définir l'arrière-plan requis

• Supposons que le taux d'intérêt soit de 12%.

• Répertoriez toutes les valeurs requises.

• Nommez les cellules contenant les valeurs, de sorte que la formule ait des noms au lieu de références de cellules.

• Définissez le calcul pour EMI avec la fonction Excel - PMT.

Votre feuille de calcul doit ressembler à ceci -

Vous pouvez voir que les cellules de la colonne C sont nommées comme indiqué dans les cellules correspondantes de la colonne D.

Étape 2: créer la table de données

• Type =EMI dans la cellule F2.

• Tapez la première liste de valeurs d'entrée, c'est-à-dire les taux d'intérêt dans la colonne F, en commençant par la cellule sous la formule, c'est-à-dire F3.

• Tapez la deuxième liste de valeurs d'entrée, c'est-à-dire le nombre de paiements sur la ligne 2, en commençant par la cellule à droite de la formule, c'est-à-dire G2.

  Le tableau de données se présente comme suit -

Effectuer l'analyse avec le tableau de données de l'outil d'analyse des hypothèses

• Sélectionnez la plage de cellules contenant la formule et les deux ensembles de valeurs que vous souhaitez remplacer, c'est-à-dire sélectionnez la plage - F2: L13.

• Cliquez sur l'onglet DONNÉES sur le ruban.

• Cliquez sur What-if Analysis dans le groupe Data Tools.

• Sélectionnez Table de données dans la liste déroulante.

La boîte de dialogue Table de données s'affiche.

• Cliquez sur l'icône dans la zone Cellule d'entrée de ligne.
• Cliquez sur la cellule NPER, qui est C3.
• Encore une fois, cliquez sur l'icône dans la zone Cellule d'entrée de ligne.
• Ensuite, cliquez sur l'icône dans la zone de cellule d'entrée de colonne.
• Cliquez sur la cellule Interest_Rate, qui est C2.
• Encore une fois, cliquez sur l'icône dans la zone de cellule d'entrée de colonne.

Vous verrez que la cellule d'entrée de ligne est prise comme $ C $ 3 et la cellule d'entrée de colonne est prise comme $ C $ 2. Cliquez sur OK.

La table de données est remplie avec les résultats calculés pour chaque combinaison des deux valeurs d'entrée -

Si vous pouvez payer un EMI de 54 000, le taux d'intérêt de 12,2% et 288 EMI vous conviennent. Cela signifie que la durée du prêt serait de 24 ans.

Calculs de table de données

Les tables de données sont recalculées chaque fois que la feuille de calcul les contenant est recalculée, même si elles n'ont pas changé. Pour accélérer les calculs dans une feuille de calcul contenant une table de données, vous devez modifier les options de calcul enAutomatically Recalculate la feuille de calcul mais pas les tableaux de données, comme indiqué dans la section suivante.

Accélérer les calculs dans une feuille de calcul

Vous pouvez accélérer les calculs dans une feuille de calcul contenant des tableaux de données de deux manières:

• À partir des options Excel.
• Du ruban.

À partir des options Excel

• Cliquez sur l'onglet FICHIER sur le ruban.
• Sélectionnez Options dans la liste du volet gauche.

La boîte de dialogue Options Excel s'affiche.

• Dans le volet gauche, sélectionnez Formulas.

• Sélectionnez l'option Automatic except for data tables sous Workbook Calculationdans la section Options de calcul. Cliquez sur OK.

Du ruban

• Cliquez sur l'onglet FORMULES sur le ruban.

• Clique le Calculation Options dans le groupe Calculs.

• Sélectionner Automatic Except for Data Tables dans la liste déroulante.