Introduction
Vous avez lu toute la documentation qu'offre ce site et vous vous avez complété les exercices. Il
est maintenant temps de mettre en pratique ce que vous avez lu et essayé. Cette page est
un exercice pour créer un modèle. Vous devrez, à partir des données
initiales, concevoir un modèle qui va vous aider à évaluer les
tendances pour un trimestre. Pour faciliter l'apprentissage, il y a seulement
une variable: le taux de croissance mensuel qui est initialement évalué à
25 %.
Entrez
le texte et les chiffres suivants dans les cellules appropriées.
Pour l'instant, ne vous préoccupez pas de la présentation
du classeur. Ne placez pas des signes de dollars ( $ ), ou mettre en gras
ou toute autre option de présentation.
Cela viendra lorsque le modèle sera réalisé.
Insérer une ligne
On a oubliez les salaires ! Même avec la meilleure planification, il est
toujours possible d'oublier quelque chose. Il y aura certainement des révisions
à apporter ainsi que des ajouts. La prochaine partie montre comment insérer des lignes
ou des colonnes pour ajouter de l'information supplémentaire.
Placez
le pointeur sur la case grise ayant le chiffre 11.
Toute la ligne sera sélectionnée. Il est possible d'accomplir des opérations sur plusieurs lignes ou colonnes en même temps. Il suffit de choisir les cases avec les chiffres ou les lettres requis.
En
gardant le pointeur par-dessus la case ayant le numéro 11, appuyez
sur le bouton droit de la souris.
De
la liste des options disponibles du menu contextuel, sélectionnez l'option
Insérer.
OU
Cliquez
sur la case grise ayant le numéro 11 sur la bordure de gauche.
Du
menu Insertion, sélectionnez l'option Lignes.
Placez
le pointeur sur la cellule A11 et écrivez: Salaires .
Placez
le pointeur sur la cellule B11 et écrivez: 15000 .
La même chose est possible avec les colonnes. Il est aussi possible d'ajouter
plusieurs lignes ou colonne en même temps. Il suffit de sélectionner
autant de lignes ou de colonnes dont vous avez besoin, à l'endroit où vous voulez les insérer, et de
choisir l'option Insertion.
La somme automatique
Depuis longtemps, Excel facilite l'addition de chiffres dans des colonnes et ou dans des lignes grâce à l'option de somme automatique. Auparavant, il fallait écrire une formule commençant par =somme( suivi de l'étendu désiré (B3:B5) suivi de ).
Placez
le pointeur sur la cellule B7.
Appuyez
sur le bouton 
.
Attention !
Assurez-vous d'avoir la bonne étendue ( =somme(B3:B6)
). L'option de somme automatique à ses limites. Elle va s'arrêter lorsque la prochaine cellule est vide. L'option ne vous donnera pas la bonne étendue si vous avez oublié d'inscrire un chiffre. Assurez-vous de toujours avoir l'étendue que vous voulez.
Pour cet exercice, si vous voulez la somme des cellules de B3 à B6 mais vous avez oublié d'inscrire un chiffre dans la cellule B4. Dans ce cas, l'option de somme automatique va seulement vous donner l'étendu =somme(B5:B6). Cela ne vous donnera pas l'étendu désiré. C'est pour cette raison qu'il faut toujours vérifier si la formule est correcte.
Appuyez
sur la touche Entrée.
Même chose pour la somme des charges.
Placez
le pointeur sur la cellule B15.
Appuyez
sur le bouton .
Excel devrait afficher dans la barre des formules la formule suivante: =somme(B10:B14) .
Appuyez
sur la touche Entrée.
Les formules simples
La prochaine partie de l'exercice consiste à créer les formules
qui vont servir à calculer automatiquement certaines valeurs dont l'utilisateur a
besoin. À chaque occasion que vous avez, utilisez des formules pour automatiser les calculs du modèle. Cela vous facilite la tâche et réduit grandement la possibilité d'erreurs. Les valeurs à calculer sont le profit brut, l'imposition fiscale, le profit
net et le profit cumulatif. Avant de continuer, vous pouvez essayer par vous-même
de créer les formules nécessaires. Vous pourrez ensuite les
vérifier avec le texte qui suit.
Pour calculer le profit brut, il faut premièrement savoir le
total des charges et des revenus. Ces montants sont déjà
calculés et se retrouvent respectivement dans les cellules B7
et B15. Il suffit de créer une formule qui fait la soustraction
entre les totaux des revenus et des charges.
Placez
le pointeur sur la cellule B16.
Écrivez
la formule suivante: =B7-B15 .
Il y a aussi une autre manière d'écrire cette formule en utilisant les curseurs du clavier.
Placez
le pointeur dans la cellule B16.
Écrivez
=.
En
utilisant les curseurs ou la souris, déplacez le pointeur sur la cellule
B7.
Écrivez
-.
En
utilisant les curseurs ou la souris, déplacez le pointeur sur la cellule
B15.
Appuyez
sur la touche Entrée.
Même si on voudrait s'en passer, il faut payer des impôts. Pour
l'exemple, le taux d'imposition de l'entreprise est de 30% du profit brut. Il faut calculer
combien on doit au gouvernement.
Placez
le pointeur sur la cellule B17.
Écrivez
la formule suivante: = B16 * 0,30 .
OU
Écrivez
=B16 * 30% .
Il y a deux manières d'écrire cette formule. Les deux donnent
le même résultat. C'est à vous de choisir celle qui vous
intéresse le plus. Il reste encore à calculer le profit net d'impôt.
Placez
le pointeur sur la cellule B18.
Écrivez
la formule suivante: =B16-B17 .
La poignée de recopie
Vous connaissez probablement les options pour couper, copier et coller avec
Windows. Excel vous offre une autre façon de copier des cellules ou de
générer une liste de chiffres et du texte. Il s'agit de la poignée
de recopie.
Placez
le pointeur sur la cellule B1.
Vous allez remarquer qu'il y a un petit carré au coin inférieur de la cellule active. C'est le poignée de recopie.
Placez
le pointeur sur la poignée de recopie au coin de la cellule B1.
Le pointeur va changer de forme. Il va devenir un "+" mince et noir.
En
gardant un doigt sur le bouton gauche de la souris, déplacez la poignée
de recopie jusqu'à la cellule D1.
Vous allez remarquer que le texte "février" et "mars" s'est automatiquement copié dans les cellules C1 et D1 respectivement. Les listes personnalisées permettent d'accélérer l'écriture des listes préparées. Excel détient déjà dans sa liste personnalisée. Il y a déjà la liste des mois et des jours. Pour pouvoir ajouter vos propres listes personnalisées:
Du
menu Outils, sélectionnez l'option Options.
Sélectionnez
l'onglet Liste pers.
Vous pouvez à partir de cette fenêtre ajouter une liste, importer à partir de cellules de votre classeur ou supprimer ceux dont vous n'avez plus besoin.
Pour
le moment, appuyez sur le bouton Annuler.
Position relative et absolue
L'un des aspects les plus importants lors de la création d'un modèle
est la possibilité de réutiliser des formules en les recopiant.
Cependant, il y a certaines formules dont il faut "fixer" ou "geler" une position,
une colonne, une rangée ou les deux pour la position d'une cellule pour
pouvoir efficacement réutiliser une formule. Si vous ne l'avez pas déjà
fait, allez voir la page sur les positions relatives
et absolues et complétez l'exercice au bas de cette page.
Placez
le pointeur sur la cellule B21.
Cette cellule va contenir le taux de croissance qui sera utilisé pour déterminer la croissance, ou diminution, des produits et des charges pour les mois de février et de mars. En fait, le modèle va calculer l'augmentation des profits de l'entreprise pour février et mars selon ce taux de croissance.
Entrez
dans la cellule B21: 25% .
Placez
le pointeur sur la cellule C3.
La formule qui sera écrite dans cette cellule est très importante parce qu'elle sera ensuite recopiée pour tous les produits et toutes les charges des mois de février et de mars. Il s'agit de prendre la valeur du mois précédent et de l'augmenter avec le taux de croissance de la cellule B21. Mais attention ! Lorsqu'on recopie une formule avec une variable ou une constante, la valeur celle-ci ne doit jamais être dans la formule. Il est préférable de mettre cette valeur dans une autre cellule et d'y faire référence dans la formule. Ceci est beaucoup plus facile à gérer si vous devez changer cette valeur.
Mais pourquoi ? Comme vous le remarquerez-vous même à la fin de l'exercice,
il est beaucoup plus facile et efficient de modifier le contenu d'une seule
cellule que de plusieurs. Aussi, votre modèle n'est plus valide si jamais
vous oubliez de changer une seule formule.
Écrivez
la formule suivante dans la cellule C3: =B3+B3*$B$21 .
OU
Écrivez
la formule suivante dans la cellule C3: =B3*(1+$B$21) .
Ces deux formules vont toujours donner le même résultat. Il suffit de choisir celle que vous préférez.
Mais pourquoi faut-il mettre $B$21 ? La formule précédente consiste à prendre la valeur du mois précédent, B3 dans ce cas, et de l'augmenter du taux de croissance qui se retrouve dans la cellule B21. Il faut comprendre que vous allez recopier cette formule horizontalement et verticalement pour qu'elle soit identique pour tous les mois et pour tous les produits et les charges.
Copier une formule
Il faut maintenant recopier la formule de la cellule C3 aux cellules de tous les produits et les charges des mois de février et de mars. N'hésitez pas à réessayer si vous avez des difficultés. Tout le monde à des problèmes à copier et coller les premières fois. L'opération et toujours la même:
Choisir
ce que vous voulez copier.
Sélectionnez
l'option copier.
Choisir
la zone sur laquelle vous voulez recoller.
Sélectionnez
l'option Coller.
Placez
le pointeur sur la cellule C3.
Du
menu Edition, sélectionnez l'option Copier.
OU
Appuyez
sur le bouton 
.
OU
Appuyez
sur le bouton droit de la souris
Du
menu contextuel, sélectionnez l'option Copier.
Vous allez remarquer que la cellule C3 est encadrée par des lignes qui bougent. C'est normal. C'est la manière d'Excel de vous avertir que c'est
la cellule qui sera recopiée. Cet encadrement va disparaître lorsque
vous ferez une opération autre que de coller la cellule sélectionnée.
Vous pouvez coller aussi souvent que vous en avez besoin. Pour cette partie
de l'exercice, vous aurez besoin de coller au moins deux fois; pour tous les revenus
et toutes les charges de l'entreprise.
Faites
un bloc avec les cellules C3 à D5.
Du
menu Edition, sélectionnez l'option Coller.
OU
Appuyez
sur le bouton 
.
OU
Appuyez
sur le bouton droit de la souris.
Du
menu contextuel, sélectionnez l'option Coller.
Si l'option Coller n'est pas disponible, placez le pointeur sur la cellule
C3 et reprenez l'option copier. Vous pourrez ensuite coller la formule
sur les cellules C3 à D5 et plusieurs autres.
Ces dernières opérations ont recopié la formule aux cellules
pour les produits. Il faut encore recopier la même formule pour toutes
les charges. Afin de ne pas alourdir le texte, une seule des trois options de
copier et de coller sera inscrite. Vous pouvez utiliser la technique de votre
choix, à partir du menu, en utilisant un bouton de la barre d'outils
ou du menu contextuel, en tout temps selon votre préférence.
Faites
un bloc avec les cellules C10 à D13.
Du
menu Edition, sélectionnez l'option Coller.
Il faut aussi recopier la formule pour les totaux des revenus aux cellules C7 et D7.
Placez
le pointeur sur la cellule B7.
Du
menu Edition, sélectionnez l'option Copier.
La formule est copiée. Il ne reste qu'à la coller.
Faites
un bloc avec les cellules C7 à D7.
Du
menu Edition, sélectionnez l'option Coller.
Copier plusieurs formules
À date, vous avez copié le contenu d'une seule cellule dans plusieurs. Maintenant, vous allez copier le contenu de plusieurs cellules dans les rangées C et D pour les mois de févier et mars.
Faites
un bloc avec les cellules B15 à B19.
Du
menu Edition, sélectionnez l'option Copier.
Faites
un bloc avec les cellules C15 à D19.
Du
menu Edition, sélectionnez l'option Coller.
Si la fenêtre suivante apparaît, il ne faut pas s'en inquiéter. Par accident, vous avez sélectionné une colonne ou une ligne en trop. Il suffit d'appuyer sur le bouton OK et de rechoisir
l'étendue que vous avez besoin avant de le coller.
Il reste seulement deux formules à ajouter: le profit cumulatif pour le premier mois et une seconde pour les mois suivants.
Placez
le pointeur sur la cellule B19.
Écrivez
dans celle-ci la formule suivante: =B18 .
Appuyez
sur la touche Entrée.
Cette formule doit démontrer les profits jusqu'à présent pour l'année. Puisqu'il s'agit du premier mois, le montant sera le même que le profit net. Et maintenant, pour la seconde formule !
Placez
le pointeur sur la cellule C19.
Écrivez
la formule suivante: =B19 + C18 .
Appuyez
sur la touche Entrée.
Cette dernière formule à l'avantage d"être facilement recopiable pour les prochains mois. Elle prend le profit cumulatif des mois précédents et ajoute les profits nets du mois actuel. Il est donc facile de recopier cette formule pour les prochains mois et de toujours avoir le bon résultat.
Le modèle devrait ressembler à ceci:
Du
menu Outils, sélectionnez l'option Options.
Sélectionnez
l'onglet Affichage.
Activez
l'option Formules.
Appuyez
sur le bouton OK.
L'affichage de la feuille de calcul va maintenant vous afficher les formules au lieu des résultats
de celles-ci.
Vérifiez
les formules de l'image précédente avec celles de votre modèle.
Apportez
les corrections si nécessaires.
Pour retourner à un affichage normal:
Du
menu Outils, sélectionnez l'option Options.
Sélectionnez
l'onglet Affichage.
Désactivez
l'option Formule.
Appuyez
sur le bouton OK.
Et maintenant ... ... la raison d'être d'un chiffrier.
Après avoir terminé le modèle, votre patron bondit dans
votre bureau et vous informe de la bonne nouvelle. Il vous dit: "J'ai réussi
à dénicher un gros contrat de notre plus gros client". Le
taux de croissance va passer de 25% à 50%. Voyons le résultat.
Placez
le pointeur sur la cellule B21.
Changer
la valeur à 50% ou 0,5.
Normalement, tout le modèle devrait s'être ajusté aux valeurs
suivantes:
Tout s'est ajusté en changeant une seule valeur ! Imaginez le potentiel
avec des formules ayant plusieurs variables. Par exemple, il pourrait avoir
une variable pour contrôler le taux de croissance de chacun des produits
de l'entreprise ou chacun des mois, ou ...
C'est aussi pour cette raison qu'il ne faut jamais mettre une constante ou
une variable dans une formule. Celle-ci peut changer et vous devriez changer
toutes les formules. C'est aussi pour cette raison que vous devez absolument
maîtriser les notions de position relative
et absolue.
La présentation
Bien que le modèle fonctionne correctement, il reste encore beaucoup de travail point de vue présentation. La prochaine partie consiste à ajouter le nom de la compagnie et d'améliorer la présentation du modèle.
Insérer une ligne
Il est aussi possible d'insérer des lignes ou des colonnes pour ajouter de l'information ou pour améliorer la présentation du chiffrier. Le prochain exercice consiste à ajouter une ligne, ensuite une seconde, pour vous démontrer le fonctionnement de cette option.
Placez
le pointeur sur la case grise à la gauche ayant le chiffre 1.
Du
menu Insérer, sélectionnez l'option Ligne.
Une nouvelle ligne va s'insérer. Toutes les lignes en dessous vont se déplacer vers le bas et les formules vont s'ajuster automatiquement.
Ajoutez
une seconde ligne en suivant les mêmes étapes.
Il est possible d'ajouter plusieurs lignes ou colonnes que vous ayez besoin à l'endroit qu'elles vous seront utiles. Il suffit de choisir les lignes ou les colonnes dont vous avez besoin, à l'endroit requis et de choisir l'option Insérer.
Les lignes que vous venez d'insérer vont servir à ajouter le nom de la compagnie et de donner un peu d'espace entre le titre et les chiffres.
Placez
le pointeur dans la cellule A1.
Écrivez
le texte suivant: Compagnie XYZ Inc.
Appuyez
sur la touche Entrée.
Il est possible de centrer le texte sur plusieurs lignes. Avec Excel, il s'agit de fusionner des cellules et de centrer. Il y a un bouton pour cela.
Faites
un bloc avec les cellules de A1 à D1.
Appuyez
sur le bouton 
.
Le nom de la compagnie est maintenant parfaitement centré sur plusieurs
colonnes. Aussi possible de fusionner des cellules sur plusieurs lignes ou colonnes
ou les deux en même temps. Vous pouvez utiliser le bouton sur
la barre d'outils Format. Vous pouvez aussi utiliser les options Format,
Cellule, Onglet Alignement et Fusionner. Vous pouvez aussi
faire l'inverse pour annuler la fusion de cellules.
La prochaine partie consiste à changer la taille de la police de caractères.
Placez
le pointeur sur la nouvelle cellule A1.
De
la liste de taille des polices de caractères, sélectionnez 20.
OU
Du
menu Format, sélectionnez l'option Cellule.
Sélectionnez
l'onglet Police.
Changez
la taille de la police à 20.
OU
Appuyez
sur le bouton droit de la souris.
Du
menu contextuel, sélectionnez l'option Format de cellule.
Sélectionnez
l'onglet Police.
Changez
la taille de la police à 20.
Il faut maintenant mettre le titre en gras.
Placez
le pointeur sur la cellule A1.
Appuyez
sur le bouton 
.
Vous pouvez aussi utiliser le menu Format ou le menu contextuel pour changer la présentation.
Pour terminer avec le titre, il faut changer la couleur du texte au bleu.
Placez
le pointeur sur la cellule A1.
De
la barre d'outils, sélectionnez l'option pour changer la couleur du texte.
OU
Du
menu Format, sélectionnez l'option Cellule.
Sélectionnez
l'onglet Police.
Changez
la couleur au bleu.
Encore une fois, il y a plusieurs manières d'accomplir la tâche pour atteindre le même résultat. Le menu contextuel est une troisième manière. Excel vous permet aussi de changer la couleur d'un caractère à la fois. Il faut:
D'éditer
le contenu de la cellule en utilisant la touche F2, la barre formule
ou un double-clic sur la cellule à modifier.
Faites
un bloc à la ou les lettres à changer de couleur.
Utilisez
l'une des trois méthodes mentionnées ci-dessus pour changer la
couleur du texte sélectionné.
Il est aussi possible de changer la couleur de texte, ou le format de présentation de plusieurs cellules à la fois. La prochaine partie consiste à changer la couleur des mois en bleu.
Faites
un bloc avec les cellules B3 à D3.
Utilisez
l'une des trois méthodes mentionnées ci-dessus pour changer la
couleur des cellules sélectionnées.
Utilisez
la même technique pour mettre les totaux en gras.
Format automatique
Vous venez d'accomplir quelques exercices pour améliorer la présentation
du modèle. Mais on pourrait en faire plus. Excel offre aussi des formats
de présentation prédéterminés qui sont appelés
des formats automatiques. La prochaine partie consiste à mettre l'un
de ces formats sur tout le modèle.
Sélectionnez
les cellules A3 à D9.
Du
menu Format, sélectionnez l'option Mise en forme automatique.
Excel vous offre une liste de formats prédéterminés. Après avoir appuyé sur le bouton Options, il est possible d'appliquer un style de présentation avec ou sans les options de votre choix.
Pour
les besoins de l'exercice, sélectionnez le format Classique 3.
Appuyez
sur le bouton OK.
Il reste à appliquer le même format aux autres cellules de la feuille de calcul.
Sélectionnez
les cellules A11 à D21.
Du
menu Format, sélectionnez l'option Mise en forme automatique.
Sélectionnez
le format Classique 3.
Appuyez
sur le bouton OK.
Ce genre de changement apporte quelque fois des désagréments.
Dans ce cas, la largeur des colonnes est trop petite. C'est pour cette raison
que certaines cellules affichent seulement le caractère " # ".
C'est de cette façon que tous les chiffriers vous avertissent que la
cellule est trop mince pour afficher correctement le chiffre qu'elle contient.
La largeur est calculée selon le contenu du bloc que vous avez sélectionné et non tout le contenu de la feuille de calcul. Il faut donc ajuster la largeur des cellules avant de continuer.
Maintenant que vous avez un modèle opérationnel. Voyons comment il peut être utilisé pour comparer des opportunités qui vous sont offertes. Pourriez-vous me dire parmi les deux options ci-dessous celle qui va rapporter le plus de profit cumulatif à l'entreprise pour le premier trimestre (janvier à mars) ?
Garder
les sommes initiales pour les revenus mais augmenter le taux de croissance à
50%.
OU
Augmenter
les revenus du produit 300 de 35 000 $ à 50 000 $ à un taux de
croissance de 10 %.
Pour plus d'informations sur le gestionnaire
de scénarios, cliquez sur ce lien.
C'est le temps d'utiliser le gestionnaire de scénarios pour comparer ces deux options. Avant, regardons quelles seront les cellules qui seront nécessaires pour réaliser les scénarios.
B7: Valeur initiale du produit 300.
B23: Taux de croissance mensuel.
D21: Cumulatif des profits de l'entreprise jusqu'au mois de mars.
Il y aurait normalement plus de variables si le modèle était plus complet. Mais ceci n'est qu'un exercice. Il faut maintenant réaliser les deux scénarios.
Avant
de commencer, assurez-vous que la valeur de la cellule B23 soit de 0,25
(25%).
Du
menu Outils, sélectionnez l'option Gestionnaire de scénarios.
Sélectionnez
le bouton Ajouter.
Écrivez
dans la case Nom du scénario: Taux de croissance de 50 %.
Écrivez
dans la case des variables B23.
Il y a d'autres manières de sélectionner une ou plusieurs cellules
variables. Ne vous inquiétez pas des "$" qui apparaissent ($B$23). Ils
n'affectent pas le scénario.
Attention !
Vous devez seulement prendre des cellules dont le contenu est un chiffre et non une formule. Sinon, le gestionnaire de scénarios va remplacer votre formule par le chiffre et votre modèle ne sera plus valide.
Appuyez
sur le bouton OK.
Il faut entrer les valeurs que les cellules vont prendre dans ce scénario. Elles ne vont pas changer celle du modèle actuel.
Entrez
la valeur 0,5 pour la cellule B23.
Ce scénario est terminé. Il ne reste qu'à appuyer sur le bouton OK ou Ajouter. Puisqu'il reste un autre scénario à entrer ...
Appuyez
sur le bouton Ajouter.
Écrivez
dans la case Nom du scénario: Augmentation du produit 300.
Écrivez
dans la case des variables B7;B23.
Dans ce scénario, il y a deux variables qui changent par rapport aux valeurs du modèle actuel. Il faudra entrer les nouvelles valeurs dans la prochaine fenêtre.
Entrez
la valeur 50000 dans la case B7.
Entrez
la valeur 0,1 dans la case B23.
Appuyez
sur le bouton OK.
Le gestionnaire de scénarios vous présente ensuite la fenêtre initiale avec le nom des deux scénarios. Vous avez en dessous des noms de scénarios la liste des cellules variables selon le scénario choisi.
Il reste qu'à voir le résultat.
Appuyez
sur le bouton Synthèse.
Le gestionnaire de scénario vous offre deux manières de voir
le résultat: avec une synthèse ou avec un tableau
croisé dynamique.
Pour
les besoins de l'exercice, sélectionnez l'option Synthèse de
scénario.
Entrez
dans la case Cellule résultante: D21.
Appuyez
sur le bouton OK.
Et voilà ! La seconde option rapporte en fin mars plus de profits. Cependant, si les tendances auraient continué jusqu'à la fin de mai, c'est la première option qui serait devenue la meilleure. Cela dépend de notre point de vue ...
