Lilapuce
 

exercices (mercredi 2 juin 2010)

Valeur cible et scénarios (Excel)

Nous voici en présence d’une mise en situation sur Excel. Vous recherchez « l’équivalent OpenOffice Calc » ? Alors c’est ici qu’il faudra vous rendre.

Ouvrez le fichier budget 2007.xls qui se trouve, comme d’habitude, en bas de cette page.
Enregistrez une copie de ce document dans votre dossier personnel ("Enregistrer sous").

Le but de la manœuvre consiste à évaluer les différentes hypothèses de ventes pour atteindre un bénéfice mensuel de 10 000 €.
Facile : pour atteindre cet objectif, les ventes doivent progresser en sorte que le bénéfice augmente de 2 358 € par rapport à sa valeur d’origine.

Sur quels postes doit porter cette augmentation ?

Dans un premier temps, imaginons que nous ne voulons rien changer à notre modèle ; c’est à dire, ne rien changer à la façon dont le budget est décomposé actuellement. Observons la feuille de calcul.

Il faut donc modifier le chiffre des ventes, bien entendu, mais ce n’est pas tout : les commissions (B17) sont indexées de façon proportionnelle à ce dernier. En cliquant sur cette cellule, on constate qu’elle comporte non pas une valeur mais une formule : =0,5*B6 soit une valeur égale à 50% des ventes. Oh !! Vraiment ?

Pour être précis il va donc falloir demander à Excel d’arriver immédiatement au résultat souhaité sans modifier le schéma général (en particulier, le rapport entre le chiffre d’affaire et le montant des commissions qui est donc proportionnel à ce dernier).

La recherche de la valeur cible permet de prendre en compte ce paramètre, car on pourra demander de recalculer le nouveau chiffre des ventes - et le montant proportionnel des commissions calculées sur ce dernier - jusqu’à obtenir le résultat souhaité (un bénéfice de 10000€).

Utilisation de la valeur cible

Cliquez sur la cellule B26 (bénéfice).

Cliquez sur le menu Outils, puis sur " Valeur cible ".

La boîte de dialogue indique trois zones distinctes :

-  1) Cellule à définir  : indique les coordonnées de la cellule qui comporte la valeur de l’objectif à atteindre. Dans notre exemple, il s’agit de la cellule B26, celle qui affiche le bénéfice.

-  2) Valeur à atteindre  : indique la valeur à atteindre. Il faut donc taper 10000.

-  3) Cellule à modifier , indique la cellule qu’il faut modifier pour atteindre cet objectif. Cliquez sur la cellule B6, laquelle concerne les ventes.

En validant ces paramètres (OK), une boite de dialogue confirme le résultat. On se rend compte que les modifications ont été portées à l’ensemble du tableau, en particulier le poste " Commissions " (B17) correspondant aux sommes versées aux réseaux de vente. Un simple clic sur le bouton " Annulez " permet un retour à l’état précédent du document, avant d’effectuer une recherche sur la valeur cible.

Après modification de la recherche de la valeur cible " 10 000€ " de bénéfice.

La modification par valeur cible du montant du bénéfice a provoqué le recalcule du chiffre des ventes, mais également celui des commissions versées aux commerciaux..

Annulez la dernière opération, permet de revenir à l’état antérieur.

Affiche le tableau tel qu’il était avant d’effectuer la recherche de la valeur cible. Cela permet de vérifier quelles sont les valeurs du tableau qui ont été modifiées.

Utilisation des scénarios

La valeur cible permet donc de modifier un paramètre (chiffre de vente) pour atteindre un objectif (montant du bénéfice) en sorte que les modifications se reportent également sur des montants proportionnels au chiffre de vente (commissions).

Comme je l’ai déjà évoqué, nous restons avec cette fonctionnalité, toujours dans le " même schéma " : les sommes sont automatiquement modifiées en fonction de la valeur cible demandée, mais la répartition entre ces différentes valeurs reste identique.

Peut-être est-il nécessaire d’envisager des solutions alternatives reposant, par exemple, sur l’intégration d’autres montants, poste par poste. Ainsi il sera possible de créer, de façon beaucoup plus souple, différentes hypothèses en combinant scénarios et valeur cible. Pour cela nous faisons donc appel aux " scénarios " d’Excel.

Commençons par attribuer des noms à nos postes. Nous pouvons faire cela automatiquement :

-  sélectionnez le groupe de cellule A6 à B26

-  Cliquez sur le menu " Insertion ", puis " Nom ", puis " Créer ". Vérifiez que la case " Colonne de gauche " est cochée (cliquez si tel n’est pas le cas).

-  Cliquez sur OK

Comment mettre en correspondance des valeurs avec des intitulés de postes. Il sera ainsi beaucoup plus simple de dialoguer avec Excel dans la définition des scénarios.

Scénario de variation de coûts

Avant de partir sur différentes hypothèses, il faut créer un scénario à partir du " schéma " tel qu’il est énoncé actuellement, ne serait-ce que pour nous y référer, de façon comparative, ultérieurement.

-  Cliquez sur le menu " Outils ", puis sur " Gestionnaire de scénarios ".

-  Une boîte de dialogue apparaît. Il s’agit du " Gestionnaire de scénario ". Cet assistant indique " Aucun scénario défini " ; pas de panique, c’est normal, vous n’avez encore rien fait. Cliquez sur " Ajouter ".

-  Dans la zone " Nom du scénario " saisissez " Budget en cours ".

-  Placez le point d’insertion dans le champ " Cellule variables " ; déplacez si nécessaire la boîte de dialogue car vous devez maintenant pouvoir cliquer sur le tableau.

-  Cliquez sur la cellule B6.

-  Maintenez la touche du clavier " CTRL " enfoncée (permet de conserver la sélection précédente) tout en cliquant sur les cellules B7, B10, B11 et B16.

Toutes ces cellules comportent donc les variables sur lesquelles vous allez intervenir pour calculer les différents scénarios.

-  Cliquez sur OK.

Une nouvelle boîte de dialogue " Valeur de scénarios " présente les intitulés de postes avec, en regard, les montants correspondants.

-  Cliquez encore sur OK ; ce qui fait apparaître à nouveau la boîte " Gestionnaire de scénario ", mais cette fois-ci, avec votre scénario " Budget en cours ". Cliquez sur " Fermer ".

A présent, il va falloir créer différents scénarios.

Un scénario " faible coûts ", un autre " Port élevé ", etc.

Recommencez la procédure pour créer un deuxième scénario que vous intitulez " Faibles coûts ".

A l’étape suivante, dans la boîte de dialogue " Valeurs de scénarios ", indiquez les valeurs suivantes :

-  Marchandises : 15000

-  Frêt : 200

Cliquez sur OK, après avoir modifié ces valeurs. La boîte de dialogue apparaît à nouveau avec votre deuxième scénario " Faibles coûts " dans la liste.

Créez, maintenant un troisième scénario, " Port élevé " avec les valeurs suivantes :

-  Port : 80000

Puis un quatrième " Publicité faible ", pour un montant … Pub de 400.

Le gestionnaire de scénario présente désormais 4 hypothèses de travail.

Il suffit de cliquer sur l’une des options puis de cliquer sur le bouton " Afficher " pour visualiser le résultat. Vous aurez peut-être besoin de fermer la boîte de dialogue pour reprendre la main sur le tableau afin de faire défiler la feuille de calcul vers le bas.

Synthèse des scénarios.

Il serait intéressant de pouvoir comparer les différents scénarios en les présentant de façon synthétique côte à côte :

- Appelez à nouveau le gestionnaire de scénarios (menu Outil) puis, depuis la boîte de dialogue, cliquez sur " Synthèse de scénarios ".

- Une boîte de dialogue demande de confirmer la " cellule résultante ". Oui !!! Il s’agit bien du bénéfice, à savoir B26.

Confirmation de la cellule résultante.

- Et hop, la synthèse jaillit sous la forme d’un magnifique tableau.

Subtile approche comparative, rigueur scientifique et haut niveau de savoir-faire méthodologique. Quelle fierté.

 

Budget_2007.xls

 

1 Message

 

Autres exercices
de l'atelier Tableur

 

L'atelier «Tableur»