Retrouvez cet article dans : Linux Pratique 41

Et si nous calculions combien la boutique Cusoon doit vendre de t-shirts pour faire des bénéfices ? Cela nous permettrait d'investir dans d'autres goodies OpenOffice.org. Mais, à combien chaque goodie doit-il revenir pour que la boutique puisse à nouveau réinvestir ? Autant d'inconnues dans l'équation que Calc va nous aider à résoudre. À travers les différents outils que sont la recherche de valeur cible, les scénarios ou les opérations multiples, nous allons voir comment optimiser les investissements en imaginant, à chaque fois, différents cas de figure.

La recherche de valeur cible

La recherche de valeur cible permet de jouer avec le résultat que l'on souhaite obtenir en faisant varier un des éléments de l'opération. Reprenons l'exemple concret évoqué en ouverture de ce tutoriel. Nous avons une boutique en ligne et nous vendons des t-shirts. Nous souhaitons arriver à un bénéfice net de 7 000 € par an et voulons donc savoir à quel prix nous devons mettre en vente nos t-shirts en tenant compte des différents autres coûts annexes que sont la fabrication, le stockage, la gestion du stock.
Voici donc l'opération d’où nous partons. Nous avons calculé le bénéfice sur la vente de t-shirts à 15 € pièce en tenant compte des coûts annexes fixes. Le bénéfice annuel s'élève à 5 497,50 €. Or, nous
recherchons un bénéfice de 7 000 €.

Fig. 1 :  Calcul du bénéfice

Fig. 2 :  Définition des paramètres de la valeur cible

Nous allons donc rechercher quel sera le prix de vente des t-shirts à l'unité pour réaliser un tel chiffre d'affaires.

• Positionnez le curseur sur la cellule contenant le calcul du bénéfice B6.
• Dans le menu Outils, sélectionnez Recherche de valeur cible.
• Dans la fenêtre de dialogue qui s'ouvre, dans la ligne Cellule de formule, la cellule contenant la formule de calcul du bénéfice est sélectionnée.
• Dans la ligne Valeur cible, nous indiquons le bénéfice escompté.
• Enfin, dans la ligne Cellule variable, nous indiquons la cellule sur laquelle nous allons jouer pour obtenir le bénéfice recherché.

Une fois tous les paramètres indiqués, cliquez sur OK pour refermer la boîte de dialogue. Un message indique que la recherche a réussi et vous demande si vous souhaitez intégrer le résultat obtenu au tableau sur lequel vous êtes en train de travailler.

Fig. 3 : recherche effectuée

Nous cliquons sur Oui et le tableau affiche maintenant un nouveau résultat en fonction de la modification de la variable.

Fig. 4 : Résultat inséré dans le tableau

Nous avons opté pour un prix de vente variable, mais nous aurions bien sûr pu prendre la quantité pour ne pas varier que sur le prix.

Les scénarios

Les scénarios sont proches des valeurs cibles. Il s'agit en fait encore de jouer avec des cellules contenant des variables, mais ils vont permettre de mémoriser un jeu sur une variable en listant les différentes modifications apportées lors de l'opération. Restons sur notre tableau et, cette fois encore, jouons avec le prix des t-shirts à l'unité.

• Sélectionnez l'ensemble de la colonne contenant les différentes variables et l'opération.
• Dans le menu Outils, sélectionnez Scénario.
• Dans la fenêtre qui s'ouvre, ajoutez un commentaire pour préciser le scénario dont il s'agit et laissez les différents paramètres tels qu'ils sont réglés. Modifiez juste la couleur en orange.
• Lorsque terminé, appuyez sur OK pour refermer la fenêtre de dialogue.

Fig. 5 : Paramétrage du scénario

Le scénario s'affiche dans la feuille sous forme d'un cadre entourant la plage des données qui ont servi au calcul. Le titre du scénario est affiché en haut de ce cadre.

Fig. 6 : Insertion du scénario dans la feuille

Continuons de créer des scénarios en changeant la variable Prix de vente à 20 euros. Comme précédemment, nous sélectionnons la colonne contenant les calculs, puis Outils et Scénario. Cette fois-ci, nous sélectionnons la couleur violette et nommons ce scénario TV20. Puis, nous créons encore un nouveau scénario avec un tarif de vente à 25 €.
Lorsque vous retournez sur la feuille, vous voyez maintenant la liste déroulante en en-tête de la colonne qui vous permet de sélectionner le scénario désiré ! Vous pouvez ainsi visualiser les résultats suivant la variable entrée.

Fig. 7 : Liste déroulante permettant la sélection du scénario

OpenOffice.org dispose d'un autre outil très efficace pour manipuler ces scénarios : le Navigateur. Affichez-le si ce n'est déjà fait en sélectionnant Édition, puis Navigateur, ou appuyez sur la touche [F5] ou encore cliquez sur l'icône représentant une étoile dans la barre d'outils.
Lorsque celui-ci est affiché, dans la barre d'outils du Navigateur, sélectionnez l'icône Scénarios. L'ensemble des scénarios et leurs commentaires s'affichent dans la fenêtre du Navigateur.

 

Fig. 8 : Scénarios accessibles depuis le Navigateur

 

Si vous faites un clic droit sur le nom du scénario depuis la fenêtre du Navigateur, il vous est alors possible soit d'éditer les propriétés du scénario, soit de le supprimer. C'est ainsi que vous pourrez supprimer les scénarios dont vous n'aurez plus l'utilité. Vous voyez qu'il est donc important de bien renseigner le commentaire ajouté au scénario et de lui donner un nom explicite.
Nous n'avons pas bien nommé le premier scénario, nous allons donc éditer ses propriétés pour lui donner un nom significatif. Après avoir sélectionné le nom du scénario, dans le menu contextuel de celui-ci,  choisissez Propriétés. La même fenêtre de dialogue de paramétrage s'ouvre et il vous est ainsi possible de modifier l'ensemble des choix.
Tout à l'heure, nous ne nous étions pas occupé de ce que signifiaient les différents paramétrages. Voyons un peu maintenant les choix qui s'offrent à nous pour ces scénarios et les modifications que nous pouvons introduire et leur signification.

Recopier :

Cette option permet de recopier les modifications apportées aux cellules variables dans le scénario actif. Si cette case n'est pas cochée, à chaque fois que vous ferez une modification, le scénario restera tel que vous l'avez créé.

Empêcher les modifications :

Si la feuille est protégée et que cette option est cochée, il ne vous sera pas possible d'éditer les propriétés du scénario pour les modifier, c'est une protection complète du scénario.
Si la feuille n'est pas protégée, cette option permet de conserver les données initiales du scénario, même si  vous modifiez la variable, le nouveau calcul s'affichera, mais ne sera pas mémorisé. Exemple :

• modifiez le scénario TV15 pour passer le prix de vente à 17 € ;
• sélectionnez le scénario TV20 ;
• revenez sur le scénario TV15, la valeur initiale de la variable qui était de 15 € n'a pas été modifiée.

Copier la feuille entière :

Recopie l'intégralité de la feuille dans une feuille de scénario.
Cette dernière option est intéressante : cela signifie en fait que, contrairement à la recherche de valeur cible où vous ne jouez qu'avec une cellule contenant la variable, les scénarios permettent de créer des listes de valeurs qui vont constituer une ressource de variables pour les différents scénarios à mettre en œuvre.

Les opérations multiples

Voyons maintenant ce que donneraient nos calculs d'optimisation des ventes dans le temps : doit-on investir dans beaucoup de t-shirts ou au contraire varier avec des sweat-shirts et des chemises ? Cruel dilemme que Calc nous aide là encore à résoudre !
Reprenons le même exemple de calcul du bénéfice sur la vente des t-shirts en calculant, cette fois-ci, le bénéfice réalisé par t-shirt. Divisons donc la quantité par le chiffre net dans la cellule C5.
Dans la colonne D, saisissez les variables sur lesquelles nous allons baser l'opération multiple, à savoir les différentes quantités sur lesquelles nous extrapolons. Les deux colonnes suivantes recevront le résultat des calculs en fonction de la variable.

Fig. 9 : Préparation de la fonction Opérations multiples

Application de l'opération à une variable

En suivant la figure ci-dessus :

• Sélectionnez la plage contenant les variables et les colonnes d'autant de lignes que de variables. Dans notre exemple, nous sélectionnons la plage D2:F6.
• Dans le menu Données, sélectionnez Opérations multiples.
• Dans la ligne Formule, sélectionnez les formules qui ont servi au calcul du chiffre, soit B5 et C5.
• Les variables sont en colonne. Dans la ligne Colonne, vous allez donc saisir ce qu'elles représentent dans la formule : la quantité, vous sélectionnez donc la cellule B4.
• Cliquez sur OK, les colonnes E et F se sont remplies des calculs en fonction des variables exprimées dans la colonne D.

Fig. 10

Fig. 11 : Résultat de l'opération multiple

 

Si nous regardons dans la formule les cellules qui ont été prises en compte, ce sont bien celles que nous avons indiquées et la fonction se charge d'utiliser la bonne variable au bon moment !
Dans ce calcul, nous avons utilisé deux opérations pour une variable. Voyons comment utiliser deux variables. Nous allons faire varier non seulement la quantité, mais aussi le prix de vente unitaire.
Cette fois-ci, nous plaçons les variables en ligne dans les colonnes E, F et G.
Sélectionnez la plage correspondant aux variables comme ci-dessous :

 

Fig. 12 : Préparation de l'opération multiple avec deux variables

•  Comme précédemment, sélectionnez Données > Opérations multiples.
• Sélectionnez la formule qui a servi au calcul dans la ligne Formules (cellule B12).
• Dans Ligne, sélectionnez le prix de vente (cellule B8), c'est la première variable.
• Puis, dans Colonne,sélectionnez la quantité (cellule B11), c'est la seconde variable.
• Cliquez sur OK pour refermer la boîte de dialogue.

À nouveau les cellules se sont remplies des données correspondant aux calculs avec les différentes variables de prix et de quantité.
Voilà, vous pouvez donc combiner formules et variables pour avoir une représentation des données en fonction de ce que vous mettez dans les lignes ou les colonnes !

 

Fig. 13 : Opérations multiples avec deux variables

Auditer les données

Qu'est-ce que c'est ? Lorsque nous faisons des calculs et notamment des opérations multiples ou des recherches de valeurs cibles, il est intéressant de savoir à quelles cellules se réfère le calcul ou encore si une cellule donnée est déjà utilisée dans une autre formule. Cela s'appelle faire un audit des données et nous allons donc repérer les antécédents de la cellule ou les dépendants. Cette fonctionnalité est très utile lorsque vous travaillez sur de grandes plages de cellules.

Repérer les antécédents

Cette fonction permet donc de repérer quelles cellules sont impactées par la fonction utilisée dans une cellule donnée.

• Dans notre dernier exemple, sélectionnez la cellule F12.
• Dans le menu Outils, sélectionnez Audit puis Repérer les antécédents ou utilisez la combinaison de touches [Maj]+[F7].
• Dans le tableau, toutes les cellules concernées par le calcul sont marquées par des flèches indiquant le sens du calcul et un point pour chaque cellule, comme le montre la figure suivante.

Fig. 14 : Repérage des antécédents de la cellule

 

 Si vous placez le curseur sur l'une des lignes bleues, un curseur avec une petite loupe vous indique la direction de la flèche et, si vous cliquez sur la ligne, l'affichage à l'écran se déplacera à l'origine de la
cellule concernée par le repérage.
Pour supprimer un tel repère, dans le menu Outils, sélectionnez Audit puis Supprimer le repérage des antécédents.
Repérage des dépendants
De la même façon vous pouvez marquer par quels calculs est concernée une cellule. Cette fois-ci, cela s'appelle repérer les dépendants.

• Sélectionnez la cellule sur laquelle vous cherchez les dépendants, la cellule B8 pour notre exemple.
• Dans le menu Outils, sélectionnez Audit puis Repérer les dépendants.
• Toutes les cellules impactées par celle sélectionnée sont marquées de flèches bleues.

Fig. 15 : Repérage des dépendants

 

Bien sûr, sur notre copie d'écran, c'est un peu tassé et pas très efficace, mais imaginez, si vous travaillez sur de grandes feuilles, cette fonctionnalité devient alors très pratique et pratiquement incontournable.
Vous supprimerez le marquage des dépendants en vous rendant dans le menu Outils > Audit puis Supprimer le repérage des dépendants ou Supprimer tous les repères. Mais attention, cette dernière option supprimera tous les repères, y compris les repères des antécédents.

Retrouvez cet article dans : Linux Pratique 41