Analyse de sensibilité avec tableau de données dans Microsoft Excel

■ Je pense ouvrir un magasin dans le centre commercial local pour vendre de la limonade gastronomique. Avant d’ouvrir le magasin, je suis curieux de savoir comment mes bénéfices, revenus et coûts variables dépendront du prix que je facture et du coût unitaire.

■ J’ai l’intention de construire une nouvelle maison. Le montant d’argent que j’ai besoin d’emprunter (avec une période de remboursement de 15 ans) dépend du prix pour lequel je vends ma maison actuelle. Je ne suis pas sûr non plus du taux d’intérêt annuel que je recevrai à la clôture. Puis-je déterminer comment mes versements mensuels dépendront du montant emprunté et du taux d’intérêt annuel?

■ Une grande société Internet envisage d’acheter un autre détaillant en ligne. Les revenus annuels actuels du détaillant sont de 100 millions de dollars, avec des dépenses de 150 millions de dollars. Les projections actuelles indiquent que les revenus du détaillant augmentent de 25% par an et que ses dépenses augmentent de 5% par an. Cependant, vous savez que les projections peuvent être erronées et vous aimeriez savoir, pour diverses hypothèses sur la croissance annuelle des revenus et des dépenses, le nombre d’années avant que le détaillant affiche un bénéfice.

La plupart des modèles de feuille de calcul contiennent des hypothèses sur certains paramètres ou entrées du modèle. Dans l’exemple de la limonade, les entrées incluent:

■ Le prix pour lequel un verre de limonade est vendu.

■ Le coût unitaire de production d’un verre de limonade.

■ La sensibilité de la demande de limonade au prix pratiqué.

■ Le coût fixe annuel de fonctionnement d’un stand de limonade.

Sur la base d’hypothèses d’entrée, vous pouvez calculer les sorties d’intérêt. Pour l’exemple de la limonade, les sorties d’intérêt peuvent inclure:

■ Bénéfice annuel.

■ Revenus annuels.

■ Coût variable annuel.

Malgré les meilleures intentions, les hypothèses sur les valeurs d’entrée peuvent être erronées. Par exemple, votre meilleure estimation du coût variable de la production d’un verre de limonade pourrait être de 0,45 $, mais il est possible que votre hypothèse soit erronée. L’analyse de sensibilité détermine la façon dont les sorties d’une feuille de calcul varient en réponse aux modifications de ses entrées. Par exemple, vous voudrez peut-être voir comment une modification du prix du produit affecte le bénéfice annuel, les revenus et les coûts variables. Avec un tableau de données dans Microsoft Excel 2013, vous pouvez facilement varier une ou deux entrées et effectuer une analyse de sensibilité. Avec un tableau de données unidirectionnel, vous pouvez déterminer comment la modification d’une entrée modifie un nombre quelconque de sorties. Avec un tableau de données bidirectionnel, vous pouvez déterminer comment la modification de deux entrées modifie une seule sortie. Les trois exemples de ce chapitre montrent à quel point il est facile d’utiliser un tableau de données et d’obtenir des résultats de sensibilité significatifs.

Réponses aux questions de cet article

Cette section fournit les réponses aux questions répertoriées au début de cet article.

Je pense ouvrir un magasin dans le centre commercial local pour vendre de la limonade gastronomique. Avant d’ouvrir le magasin, je suis curieux de savoir comment mes bénéfices, revenus et coûts variables dépendront du prix que je facture et du coût unitaire.

Le travail requis pour cette analyse se trouve dans le fichier . (Voir , Figure .) Les hypothèses d’entrée sont données dans la plage D1: D4. Supposons que la demande annuelle de limonade (voir la formule dans la cellule D2) est égale à 65000-9000 * prix. (Le chapitre 83, «Estimation d’une courbe de demande», contient une discussion sur la façon d’estimer une courbe de demande.) Les noms créés dans C1: C7 correspondent aux cellules D1: D7.

Le revenu annuel a été calculé dans la cellule D5 avec la formule de prix de la demande *. Dans la cellule D6, le coût variable annuel a été calculé avec la formule de demande unit_cout *. Enfin, dans la cellule D7, le bénéfice a été confondu en utilisant la formule revenue – _cout fixes – cout variable.

FIGURE  1: Ce sont les données qui modifient la rentabilité d’un magasin de limonade.

Supposons que vous souhaitiez savoir comment les changements de prix (par exemple, de 1,00 $ à 4,00 $ par incréments de 0,25 $) affectent le bénéfice annuel, les revenus et les coûts variables. Comme vous ne modifiez qu’une seule entrée, une table de données unidirectionnelle résoudra le problème. Le tableau de données est illustré à la figure ci-dessous.

Pour configurer une table de données unidirectionnelle, commencez par répertorier les valeurs d’entrée dans une colonne. Les prix d’intérêt (allant de 1,00 $ à 4,00 $ par incréments de 0,25 $) sont répertoriés dans la fourchette C11: C23. Ensuite, déplacez-vous sur une colonne et sur une ligne de la liste des valeurs d’entrée, et là, les formules sont répertoriées que vous souhaitez que la table de données calcule. La formule de profit est entrée dans la cellule D10, la formule de revenu dans la cellule E10 et la formule de coût variable dans la cellule F10.

FIGURE 2 :  Il s’agit d’un tableau de données à sens unique avec des prix variables.

Sélectionnez maintenant la plage de tables (C10: F23). La plage du tableau commence une ligne au-dessus de la première entrée; sa dernière ligne est la ligne contenant la dernière valeur d’entrée. La première colonne de la plage de tableaux est la colonne contenant les entrées; sa dernière colonne est la dernière colonne contenant une sortie. Après avoir sélectionné la plage de tableaux, cliquez sur l’onglet Données sur le ruban. Dans le groupe Data Tools, choisissez Analyse du classeur , puis choisissez Data Table. Remplissez maintenant la boîte de dialogue Tableau de données, comme illustré à la figure ci-dessous.

FIGURE 3:Créez un tableau de données.

Pour la cellule d’entrée de colonne, vous utilisez la cellule dans laquelle vous souhaitez affecter les entrées répertoriées, c’est-à-dire les valeurs répertoriées dans la première colonne de la plage du tableau de données. Étant donné que les entrées répertoriées sont des prix, vous pouvez choisir D1 comme cellule d’entrée de colonne. Après avoir cliqué sur OK, Excel crée le tableau de données unidirectionnel illustré à la figure .

FIGURE 4: Il s’agit d’un tableau de données à sens unique avec des prix variables.

Dans la fourchette D11: F11, le profit, le revenu et le coût variable sont calculés pour un prix de 1,00 $. Dans les cellules D12: F12, le profit, le revenu et le coût variable sont calculés pour un prix de 1,25 $ et ainsi de suite à travers la fourchette de prix. Le prix maximisant le profit parmi tous les prix indiqués est de 3,75 $. Un prix de 3,75 $ serait générer un bénéfice annuel de 58 125,00 $, un revenu annuel de 117 187,50 $ et un coût variable annuel de 14 062,50 $.

Supposons que vous vouliez déterminer comment le bénéfice annuel varie, car le prix varie de 1,50 $ à 5,00 $ (en incréments de 0,25 $) et le coût unitaire varie de 0,30 $ à 0,60 $ (en incréments de 0,05 $). Comme vous modifiez deux entrées ici, vous avez besoin d’une table de données bidirectionnelle. (Voir figure 16-5.) Énumérez les valeurs pour une entrée dans la première colonne de la plage du tableau (utilisez la plage H11: H25 pour les valeurs de prix) et les valeurs pour l’autre entrée dans la première ligne de la plage du tableau. (Dans cet exemple, la plage I10: O10 contient la liste des valeurs de coût unitaire.) Un tableau de données bidirectionnel ne peut avoir qu’une seule cellule de sortie et la formule de la sortie doit être placée dans le coin supérieur gauche du tableau gamme. Par conséquent, placez la formule de profit dans la cellule H10.

FIGURE 5 : Un tableau de données bidirectionnel montre le bénéfice en fonction du prix et du coût variable unitaire.

Sélectionnez la plage de tableaux (cellules H10: O25) et cliquez sur l’onglet Données. Dans le groupe Data Tools, choisissez Rapport analyse du classeur, puis sélectionnez Data Table. La cellule D1 (prix) est la cellule d’entrée de colonne et la cellule D3 (coût variable unitaire) est la cellule d’entrée de ligne. Ces paramètres garantissent que les valeurs de la première colonne de la plage de tableaux sont utilisées comme prix et les valeurs de la première ligne de la plage de tableaux sont utilisées comme coûts variables unitaires. Après avoir cliqué sur OK, vous voyez le tableau de données bidirectionnel illustré à la figure ci-dessus. À titre d’exemple    dans la cellule K19, lorsque vous facturez 3,50 $ et que le coût variable unitaire est de 0,40 $, le bénéfice annuel est égal à 58 850,00 $. Pour chaque coût unitaire, le prix maximisant le profit a été mis en évidence. Notez que lorsque le coût unitaire augmente, le prix maximisant le profit augmente car une partie de l’augmentation des coûts est répercutée sur les clients. Bien sûr, vous ne pouvez garantir que le prix de maximisation du profit dans le tableau de données est à moins de 0,25 $ du prix de maximisation du profit réel. Lorsque vous découvrez Excel Solver  à l’article 83, vous apprenez à déterminer (au centime près) le prix exact de maximisation du profit.

Voici quelques autres notes sur ce problème:

■ Lorsque vous modifiez des valeurs d’entrée dans une feuille de calcul, les valeurs calculées par une table de données changent également. Par exemple, si vous augmentiez le coût fixe de 10 000 $, tous les chiffres de profit du tableau de données seraient réduits de 10 000 $.

■ Vous ne pouvez pas supprimer ou modifier une partie d’une table de données. Si vous souhaitez enregistrer les valeurs dans un tableau de données, sélectionnez la plage du tableau, copiez les valeurs, puis cliquez avec le bouton droit et sélectionnez Collage spécial. Choisissez Valeurs dans le menu Collage spécial. Cependant, si vous suivez cette étape, les modifications apportées aux entrées de votre feuille de calcul n’entraînent plus la mise à jour des calculs du tableau de données.

■ Lors de la configuration d’un tableau de données bidirectionnel, veillez à ne pas mélanger vos cellules d’entrée de ligne et de colonne. Une confusion entraînera des résultats absurdes.

■ La plupart des utilisateurs définissent leur mode de calcul de feuille de calcul sur Automatique. Avec ce paramètre, toute modification de votre feuille de calcul entraînera le recalcul de toutes vos tables de données. Habituellement, vous le souhaitez, mais si vos tableaux de données sont volumineux, le recalcul automatique peut être incroyablement lent. Si le recalcul constant des tableaux de données ralentit votre travail, cliquez sur l’onglet Fichier du ruban, choisissez Options, puis cliquez sur l’onglet Formules. Sélectionnez Automatique sauf pour les tableaux de données. Lorsque l’option Automatique sauf les tables de données est sélectionnée, toutes vos tables de données ne recalculent que lorsque vous appuyez sur la touche F9 (recalcul). Vous pouvez également cliquer sur le bouton Options de calcul (dans le groupe Calcul sous l’onglet Formules), puis choisir Automatique sauf pour les tableaux de données.

J’ai l’intention de construire une nouvelle maison. Le montant d’argent que j’ai besoin d’emprunter (avec une période de remboursement de 15 ans) dépend du prix pour lequel je vends ma maison actuelle. Je ne suis pas sûr non plus du taux d’intérêt annuel que je recevrai à la clôture. Puis-je déterminer comment mes versements mensuels dépendront du montant emprunté et du taux d’intérêt annuel?

La puissance réelle des tableaux de données devient évidente lorsque vous combinez un tableau de données avec l’une des fonctions Excel. Dans cet exemple, vous utilisez un tableau de données bidirectionnel pour faire varier deux entrées (le montant emprunté et le taux d’intérêt annuel) pour la fonction Excel PMT et déterminer comment le paiement mensuel varie au fur et à mesure que ces entrées changent. (La fonction PMT est décrite en détail dans l’un des  chapitres précédents , «Autres fonctions financières Excel».) Le travail de cet exemple se trouve dans le fichier Mortgagedt.xlsx, illustré à la figure ci-dessous.

Supposons que vous empruntez de l’argent sur une hypothèque de 15 ans, pour laquelle des paiements mensuels sont effectués à la fin de chaque mois. Saisissez le montant emprunté dans la cellule D2, le nombre de mois dans l’hypothèque (180) en D3 et le taux d’intérêt annuel en D4. Associez les noms de plage dans les cellules C2: C4 aux cellules D2: D4. Sur la base de ces entrées, calculez le paiement mensuel en D5 avec la formule -=-VPM(Annual_int_rate/12;Number_of_Months;Amt_Borrowed).

FIGURE 6: Vous pouvez utiliser un tableau de données pour déterminer comment les versements hypothécaires varient en fonction du montant emprunté et du taux d’intérêt.

Vous pensez que le montant emprunté variera (selon le prix pour lequel vous vendez votre maison actuelle) entre 300 000 $ et 650 000 $ et que votre taux d’intérêt annuel variera entre 5% et 8%. Pour préparer la création d’un tableau de données, entrez les montants empruntés dans la fourchette C8: C15 et les valeurs de taux d’intérêt possibles dans la fourchette D7: J7. La cellule C7 contient la sortie que vous souhaitez recalculer pour diverses combinaisons d’entrée. Par conséquent, définissez la cellule C7 égale à la cellule D5. Sélectionnez la plage de tableaux (C7: J15), choisissez Rapport d’analyse des données  dans l’onglet Données, puis sélectionnez Data Table. Parce que les nombres dans la première colonne de la plage du tableau sont   montants empruntés, la cellule d’entrée de la colonne est D2. Les nombres dans la première ligne du tableau sont des taux d’intérêt annuels, donc votre cellule d’entrée de ligne est D4. Après avoir cliqué sur OK, vous voyez le tableau de données illustré à la figure 16-6. Ce tableau montre, par exemple, que si vous empruntez 400 000 $ à un taux annuel de 6%, vos paiements mensuels dépasseraient un peu plus de 3 375 $. Le tableau de données montre également qu’à un taux d’intérêt bas (par exemple, 5%), une augmentation de 50 000 $ du montant emprunté augmente le paiement mensuel d’environ 395 $, tandis qu’à un taux d’intérêt élevé (comme 8%), une augmentation de 50 000 $ du montant emprunté augmente le paiement mensuel d’environ 478 $.

Une grande société Internet envisage d’acheter un autre détaillant en ligne. Les revenus annuels actuels du détaillant sont de 100 millions de dollars, avec des dépenses de 150 millions de dollars. Les projections actuelles indiquent que les revenus du détaillant augmentent de 25% par an et que ses dépenses augmentent de 5% par an. Cependant, vous savez que les projections peuvent être erronées et vous aimeriez savoir, pour diverses hypothèses sur la croissance annuelle des revenus et des dépenses, le nombre d’années avant que le détaillant affiche un bénéfice.

Vous voulez déterminer le nombre d’années nécessaires pour atteindre l’équilibre, en utilisant des taux de croissance annuels en revenus de 10% à 50% et des taux de croissance des dépenses annuelles de 2% à 20%. Supposons également que si l’entreprise ne peut pas atteindre le seuil de rentabilité en 13 ans, vous direz qu’elle ne peut pas atteindre le seuil de rentabilité. Votre travail se trouve dans le fichier , illustré dans les figures précédentes et .

Masquez les colonnes A et B et les lignes 16-18. Pour masquer les colonnes A et B, sélectionnez d’abord les cellules des colonnes A et B (ou sélectionnez les en-têtes de colonne), puis cliquez sur l’onglet Accueil. Dans le groupe Cellules, cliquez sur Format, pointez sur Masquer et afficher et sélectionnez Masquer les colonnes. Pour masquer les lignes 16-18, sélectionnez toutes les cellules de ces lignes (ou sélectionnez les en-têtes de ligne) et répétez la procédure précédente en sélectionnant Masquer les lignes. Les options de visibilité du format incluent également Afficher les lignes et Afficher les colonnes. Si vous recevez une feuille de calcul avec de nombreuses lignes et colonnes masquées et que vous souhaitez les afficher toutes rapidement, vous pouvez sélectionner la feuille de calcul entière en cliquant sur le bouton Sélectionner tout à l’intersection des en-têtes de colonne et de ligne. Sélectionnez maintenant Afficher les lignes ou Afficher les colonnes pour afficher toutes les lignes ou colonnes masquées dans la feuille de calcul.

À la ligne 11, projetez les revenus de l’entreprise sur 13 ans (sur la base du taux de croissance annuel des revenus supposé en E7) en copiant la formule E11 * (1 + 7 $ E $) de F11 à G11: R11. À la ligne 12, projetez les dépenses de l’entreprise sur 13 ans (sur la base du taux de croissance annuel des dépenses supposé en E8) en copiant la formule E12 * (1 + 8 $ E) de F12 à G12: R12. (Voir figure 7.)

FIGURE 7: Vous pouvez utiliser un tableau de données pour calculer combien d’années il faudra pour atteindre le seuil de rentabilité.

Vous souhaitez utiliser un tableau de données bidirectionnel pour déterminer comment la variation des taux de croissance des revenus et des dépenses affecte les années nécessaires pour atteindre le seuil de rentabilité. Vous avez besoin d’une cellule dont la valeur vous indique toujours le nombre d’années nécessaires pour atteindre le seuil de rentabilité. Parce que vous pouvez atteindre le seuil de rentabilité au cours des 13 prochaines années, cela peut sembler un défi de taille.

Commencez par utiliser un relevé IF pour chaque année à la ligne 13 pour déterminer si vous atteignez le seuil de rentabilité pendant l’année. L’instruction IF renvoie le numéro de l’année si vous atteignez le seuil de rentabilité au cours de l’année ou 0 sinon. Déterminez l’année où vous atteignez le seuil de rentabilité dans la cellule E15 en ajoutant tous les chiffres de la ligne 13. Enfin, vous pouvez utiliser la cellule E15 comme cellule de sortie pour un tableau de données bidirectionnel.

Copiez la formule SI (ET (E11 <E12, F11> F12), F10,0) de la cellule F13 vers G13: R13. Cette formule montre que vous atteignez le seuil de rentabilité pour la première fois au cours d’une année si, et seulement si, au cours de l’année précédente, les revenus sont inférieurs aux dépenses et, au cours de l’année en cours, les revenus sont supérieurs aux dépenses. Si tel est le cas, le numéro de l’année est inscrit à la ligne 13; sinon, 0 est entré.

À présent, dans la cellule E15, vous pouvez déterminer l’année d’équilibre (le cas échéant) avec la formule SI (SOMME (F13: R13)> 0, SOMME (F13: R13), “No BE”). Si vous n’atteignez pas le seuil de rentabilité au cours des 13 prochaines années, la formule renvoie la chaîne de texte “No BE”.

Entrez maintenant les taux de croissance des revenus annuels (10 à 50%) dans la plage E21: E61. Saisissez les taux de croissance annuels des dépenses (de 2 à 20%) dans la plage F20: X20. Assurez-vous que la formule de l’année de rentabilité est copiée dans la cellule E20 avec la formule = E15. Sélectionnez la plage de tableaux E20: X61, cliquez sur Rapport analyse des données sous l’onglet Données, puis choisissez Tableau de données. Sélectionnez la cellule E7 (taux de croissance des revenus) comme cellule d’entrée de colonne et la cellule E8 (taux de croissance des dépenses) comme cellule d’entrée de ligne. Avec ces paramètres, vous obtenez le tableau de données bidirectionnel illustré à la figure 8.

FIGURE 8: Cette figure montre un tableau de données bidirectionnel.

Notez, par exemple, que si les dépenses augmentent de 4% par an, une croissance annuelle de 10% du chiffre d’affaires entraînera un seuil de rentabilité en huit ans, tandis qu’une croissance annuelle de 50% du chiffre d’affaires entraînera un seuil de rentabilité uniquement deux ans! Notez également que si les dépenses augmentent de 12% par an et que les revenus augmentent de 14% par an, vous n’atteindrez pas le seuil de rentabilité d’ici la fin de 13 ans.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x