Valeur cible, scénario & solveur (Calc)

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

Ouvrez le fichier « Travail_budget 2007.ods » qui se trouve, comme d’habitude, en bas de cette page (vous y trouverez également le document finalisé).

Budget prévisionnel par « Valeur cible »

Considérons le budget suivant :

Hmm... ?

Action : vous devez passer le bénéfice à 10 000 €. Interdiction de toucher aux charges fixes.

Bah, il suffit alors d’augmenter le chiffre de ventes de 2 358 €.

Compte tenu des contraintes, nous sommes d’accord quant à la méthode, mais c’est le montant qui n’est pas bon.

En particulier, il existe, dans ce budget, un poste « dépense » qui est calculé en proportion du chiffre de ventes. Il s’agit des commissions.

En cliquant sur cette cellule « B17 » (ci-dessus), on constate que ce poste, contrairement aux autres dépenses, ne contient pas une valeur fixe.

La barre de formule indique : =0,5*B6.

Ce qui signifie que le montant des commissions est égal à… à... à… à 50% des ventes.

Oh ! Vraiment ?

Dites donc... ces commissions-là... seraient pas versées à une filiale, par hasard ?

(Pour information, le tableau présenté dans cet exo est la reprise conforme d’un exemple trouvé sur un vieux bouquin consacré à Excel 97, édité par Microsoft. S’agit-il d’un précieux témoignage du modèle économique ayant cours dans la maison mère ?)

Ce poste « Commissions » – qui est donc indexé de façon proportionnelle au chiffre des ventes – est pris en compte dans le calcul du bénéfice. Autrement dit notre nouveau budget fera intervenir une variation sur trois postes :

– bénéfice (objectif à atteindre)

– chiffre des ventes (variable)

– commissions (variable)

Bref, il va falloir demander au tableur de recalculer le budget dans sa globalité en fonction d’un objectif ou, plus exactement – selon la terminologie en usage sur les tableurs – d’une « valeur cible » : 10K€ de bénèf.

Nous utiliserons donc la fonctionnalité dite de « Valeur cible », via le menu « Outils » :

Sur la boîte de dialogue, je clique d’abord dans le premier champ (« Cellule de formule ») puis je sélectionne la cellule « B26 » (le bénéfice). Je viens d’indiquer à quel niveau du tableau doit s’opérer l’action.

Maintenant j’indique dans le champ « Valeur cible » le montant à atteindre, soit 10 000 €.

Puis, après avoir cliqué dans le troisième champ « Cellule variable », je sélectionne la cellule « B6 », correspondant donc au chiffre de ventes.

Après avoir cliqué sur « Ok », le tableur me retourne une boîte de dialogue. Si je clique sur « Oui », je verrais le résultat.

Alors, allons-y donc.

Voici mon nouveau budget avec les trois postes modifiés :

J’annule ensuite la procédure pour revenir au budget initial :

Budget prévisionnel par scénario et solveur

Je souhaite à présent appliquer une autre variante de la simulation précédente : la valeur cible du bénéfice doit être toujours définie à 10 000 €, mais, cette fois-ci, plutôt que de jouer sur le chiffre des ventes, je vais demander au tableur d’intervenir sur le taux de pourcentage des commissions.

Pour cela, il me faut procéder à quelques ajustements car nous avions observé que, dans la cellule « B17 », on trouve une formule permettant de calculer le montant des commission à partir du chiffre des ventes :

Le taux de pourcentage est inscrit « en dur » dans la formule. Or, comme je vais demander au tableur de modifier cette valeur, il est préférable de stocker cette dernière dans une cellule séparée.

Je me place en « C17 » puis je tape « 50% ». Je supprime les décimales et je valide.

Je supprime le contenu de « B17 » :

Puis j’inscris, à la place, la formule qui fait simplement référence aux variables « ventes » et « taux des commissions » :

Je valide et je vérifie le résultat :

Avant de lancer ma nouvelle simulation, il est préférable de mémoriser la situation de départ, ce qui me permettra de comparer les différentes hypothèses.

Voilà précisément à quoi correspond la notion de « scénario ».

Je sélectionne le groupe de cellules « A6 :C26 » :

Je clique ensuite sur le menu « Outils/scénarios... » :

Sur la boîte de dialogue, je tape « Original » puis je valide par « OK » :

Cet affichage caractéristique, avec un sélecteur portant un intitulé, permet d’attester que le tableau, dans son état d’origine, est bien conservé en mémoire. Pour le retrouver, je n’aurai pas besoin, comme précédemment, d’annuler mes différentes simulations.

Remarquez que ce sélecteur a supprimé le contenu de la ligne placée au-dessus de la plage de cellules sélectionnées. Dans notre cas, cela ne porte pas vraiment à conséquence, puisqu’il n’ y avait, à cet emplacement, qu’un titre (« Chiffre d’affaires ») ; ce dernier n’étant relié, par une formule, à aucune autre cellule du tableau. Mais pensez-y : au besoin, insérez une ligne vide au-dessus de votre tableau, avant d’activer vos scénarios.

Je conserve le même groupe de cellules sélectionnées.

Astuce : il est possible de nommer ce groupe de cellules (par exemple en tant que « BUDGET ») à l’aide de la commande CTRL+F3, ce qui permet de le retrouver, par la suite, d’un clic de souris dans la « Zone de nom ».

Puis je retourne par le menu « Outils/Scénarios... » :

Je nomme ce deuxième scénario « Rogne les coms », puisqu’il s’agit, ici, de simuler une situation où l’on pioche dans les « Commissions » pour obtenir un bénéfice à 10K€, tout en conservant un chiffre des ventes égal à celui de notre tableau original.

Voyons, justement, comment le solveur nous aidera à mettre en œuvre cette petite affaire :

Je déplace l’assistant « Solveur », afin d’avoir la meilleure visibilité sur le tableau.

Je clique dans le premier champ « Cellule cible » puis je sélectionne la cellule « B26 ». Rien de neuf par rapport à tout à l’heure : le but de tout cela reste encore « d’optimiser le bénéfice ».

Je clique ensuite sur le bouton radio (rond) « Valeur de » :

Et je tape la valeur cible de « 10000 », différente de celle du départ.

Champ suivant, « Par modification de cellule », j’indique à quel niveau de la feuille de calcul se situe la « variable d’ajustement ».

On a dit que c’était les commissions.

Certes, mais c’est la cellule qui indique le taux de pourcentage, qu’il va falloir sélectionner. Je clique donc sur « C17 » :

Voilà, tout est en place. Il ne reste plus qu’à lancer le solveur par « Résoudre » :

Une boîte de dialogue s’affiche alors : on m’indique, le cas échéant si ma requête est incohérente (impossibilité d’effectuer le calcul), ou, cas présent, s’il faut valider le résultat ; remarquez que ce dernier s’affiche également en arrière-plan dans le tableau :

Je valide par « Conserver le résultat » (ci-dessus).

L’assistant solveur disparaît de l’écran pour laisser place au scénario « Rogne les coms » :

Je peux comparer les deux scénarios en passant de l’un...

… à l’autre :

Avant de poursuivre l’exo, il est important de bien vérifier que le scénario « Original » est affiché à l’écran : par souci de clarté, mieux vaut construire toutes les scénarios à partir des mêmes conditions de départ.

Nouvelle simulation : le but du jeu consiste à conserver le chiffre des ventes, tout en optimisant (toujours) le bénéfice, mais, cette fois-ci, je n’indiquerai aucune valeur cible précise. Par contre de nouvelles valeurs (au rabais) seront définies pour deux postes de coûts : marchandises et fret (et non « frêt », désolé pour la faute).

J’appelle à nouveau « Scénarios... » :

Celui-ci s’intitulera donc « Rogne les coûts » :

Je valide et je lance le solveur :

« Cellule cible » : toujours et encore le bénéfice.

Pour « Optimiser le résultat », je clique à nouveau sur « Valeur de ».

Par contre, cette fois-ci, je n’indique pas une valeur absolue. Je clique sur la cellule cible elle-même (« B26 »).

Il a bien été question « d’optimiser », non ? Donc cela signifie que le montant qui s’affichera dans la cellule cible, après moulinette du solveur, sera, au pire, égal à la valeur de départ.

C’est bien beau d’essayer de garder le même chiffre des ventes, mais si on veut, en même temps augmenter le bénéfice, il va bien falloir, quand même, se servir quelque part.

Petit rappel : nous avions indiqué qu’il s’agirait des coûts.

Ah oui, baisser les coûts, le grand classique (remarquez que, dans notre schéma, il n’y a plus aucune trace visible de dépense de type « main d’œuvre. Le nettoyage avait-il été « solvé » lors d’un précédent ajustement ?).

Bref, allons-y pour le refrain « baisse des coûts » :

Je clique d’abord dans le champ « Par modification des cellules » et je sélectionne ensuite les cellules « B10 :B11 » :

Ce n’est pas fini : dans la rubrique « Conditions de limitation », je clique dans le premier champ et je sélectionne la cellule « B10 ».

Ensuite, je vérifie bien que le champ suivant, placé à droite, indique l’opérateur « inférieur ou égal » (<=).

Je place le point d’insertion sur le troisième champ de la ligne et j’indique une valeur inférieure à celle du budget de départ. Je tape « 15000 » :

Voilà pour les coûts « Marchandises », au rabais.

Il ne reste plus qu’à indiquer le budget « Fret ».

Je clique sur le premier champ de la deuxième ligne, puis je sélectionne la cellule « B11 ».

Je vérifie si l’opérateur indique « inférieur ou égal » (<=) et je tape « 200 », au lieu de 397 (valeur initiale)

Je lance la moulinette par « Résoudre »...

et je valide par « Conserver le résultat » :

Le scénario « Rogne les coûts » indique :

– chiffre d’affaire constant

– réduction des coûts

– dépenses constantes

– augmentation du bénéfice

Et cela, même sans indiquer de valeur « bénéfice » supérieure à celle du départ. Vous êtes bon pour le CAC 40.

Et je compare les différents scénarios...

...par le sélecteur :