Faire des scénarios chiffrés avec Excel

La technique décrite permet de construire une pyramide des âges, et de comprendre comment manipuler ses paramètres d'affichage.

Pour l'exemple, nous allons imaginer un besoin :

  • simuler la marge réalisée sur un contrat, ainsi que son taux,
  • suivant l'évolution d'une ristourne commerciale (2% ou 5%),
  • avec ou sans variation de la charge salariale (0% ou 2%),
  • mais sans modification du prix de vente.

Ces scénarii ne vous rappellent rien ?

Définir une matrice de donnéess

L'ensemble des libellés, répartis en 3 groupes pour une meilleure lisibilité

Colonne B :

  • groupe 1 : B4:B7 :
    • CA du contrat,
    • Charge salariale de réf.,
    • Marge,
    • Tx de marge

  • groupe 2 : B9:B12 :
    • Tx d'augmentation salariale,
    • Charge salariale ajustée,
    • Tx de ristourne commerciale,
    • Charge salariale ajustée

  • groupe 3 : B14:B15 :
    • Marge ajustée,
    • Tx marge ajusté

Colonne C : les données de référence

  • groupe 1 :
    • C4 : 2000

      Le CA généré par le contrat (référence).

    • C5 : 16000

      Le coût salarial (référence).

    • C6 : la formule =C4-C5

      La marge dégagée (référence).

    • C7 : la formule =C4/C5-1

      La marge dégagée (référence).


  • groupe 2 :
    • C9 : 0

      Le taux de variation de la masse salariale. Pour le moment de 0 (= pas de variation).

    • C10 : la formule =C5*(1+C9)

      La nouvelle masse salariale.

    • C11 : 0

      Le taux de variation de la ristourne commerciale. Pour le moment de 0 (= pas de variation).

    • C12 : la formule =C4*(1-C11)

      Le nouveau CA généré.

  • groupe 3 :
    • C14 : la formule =C12-C10

      La nouvelle marge dégagée.

    • C15 : la formule =C12/C10-1

      Le nouveau taux de marge.


Un peu de cosmétique pour mieux percevoir les valeurs :

  • mise en forme financière pour les cellules C4, C5, C6, C10, C12, C14
  • mise en forme % pour les cellules C7, C9, C11, C15

Voir la capture d'écran

Nommer les cellules

Cette manipulation, bien qu'optionnelle, apporte ensuite une plus grande clarté dans la maîtrise des opérations.

  • Sélectionner le tableau de données (B4:C15)
  • Utiliser le Gestionnaire de noms

  Astuce : pour un nommage rapide
  • sélectionner le tableau de données
  • ruban > Formules
  • Depuis selection (à côté de Gestionnaire de noms)
  • cocher l'option Colonne de gauche, puis valider
  • Pour le plaisir, ouvrir le Gestionnaire de noms pour constater que tous les noms sont générés !

Voir la capture d'écran

Création des scénarios

Sélectionner le tableau de données (B4:C15).

Ouvrir la fenêtre du Gestionnaire de noms :

  • ruban > Données
  • menu Analyse de scénarios
  • Gestionnaire de scénarios

Voir la capture d'écran


Ajouter un 1er scénario dans la fenêtre du Gestionnaire de scénarios :

  • Ajouter
  • Nom du scénario : Ristourne 5%
  • Cellules variables : $C$9; $C$11
  • Commentaire : texte libre pour commenter le scénario

Voir la capture d'écran


Valider pour afficher les hypothèses à prendre en compte (c'est quand même plus simple avec des cellules nommées...)

  • Tx_d_augmentation salariale : 0 (= pas d'augmentation salariale)
  • Tx_de_ristourne_commerciale : 0,05 (= 5%)

Voir la capture d'écran


Valider le tout.


 

Ajouter un 2e scénario en reproduisant la séquence précédente avec les informations suivantes :

  • Nom du scénario : Ristourne 5% + Salaires 2%
  • Cellules variables : $C$9;$C$11
  • Commentaire : texte libre pour commenter le scénario
  • Tx_d_augmentation salariale : 0,02 (= 2%)
  • Tx_de_ristourne_commerciale : 0,05 (= 5%)

Ajouter un 3e scénario :

  • Nom du scénario : Ristourne 10%
  • Cellules variables : $C$9;$C$11
  • Commentaire : texte libre pour commenter le scénario
  • Tx_d_augmentation salariale : 0
  • Tx_de_ristourne_commerciale : 0,10 (= 10%)

Ajouter un 4e scénario :

  • Nom du scénario : Ristourne 10% + Salaires 2%
  • Cellules variables : $C$9;$C$11
  • Commentaire : texte libre pour commenter le scénario
  • Tx_d_augmentation salariale : 0,02 (= 2%)
  • Tx_de_ristourne_commerciale : 0,10 (= 10%)

De retour dans la fenêtre du Gestionnaire de scénarios, vous pouvez modifier vos saisies précédentes, les supprimer ou en ajouter de nouvelles.

Génération de la synthèse

Cliquer sur Synthèse...

Voir la capture d'écran

Choisissez Synthèse de scénario (feuille dédiée) ou Scénario... de tableau croisé dynamique (pour des manipulations ultérieures).

Les cellules de résultats sont automatiquement proposées : C10; C12; C14; C15 (ce sont les cellules contenant des formules).

Valider pour faire apparaitre une nouvelle feuille (Synthèse de scénarios) avec tous les scénarios calculés.

Voir la capture d'écran