Exercice Execl introduction mise en page - formules de calcul et les graphiques

Objectifs : - Mettre en forme et en page une feuille de calcul.

                  - Utiliser les formules de calcul.

                  - Utiliser la notion des références absolues et relatives.

                  - Insérer des graphiques sous Ms-Excel 

INTRODUCTION À EXCEL : EXERCICE CONFITURE

Consignes :

1.    Veuillez indiquer dans la feuille “nom” du fichier confiture.xls votre nom, prénom, orientation et numéro de carte d’étudiant.
2.    Les formules introduites dans Excel dans le cadre de cet exercice ne peuvent pas contenir de nombres. Elles doivent uniquement faire référence à des cellules.

En 1999, Monsieur X a établi, près de Namur, une petite entreprise qui fabrique et commercialise de la confiture. La gamme de produits est limitée à la confiture d'abricots (A) et de bananes (B). Monsieur X vous demande d'effectuer une petite analyse commerciale sur base de l'évolution des prix en € (P) et des quantités vendues (Q) de chaque produit depuis 2001. Ces informations se trouvent dans la feuille “données” du fichier confiture.xls.

NB : La mise en forme du tableau est à réaliser une fois les différents calculs et opérations réalisés (dernière étape).

PREMIÈRE ÉTAPE

Monsieur X pensait ne pas prendre en compte les ventes de 1999 et 2000, en raison du démarrage de l'entreprise. Il estime néanmoins qu'il dispose de peu de données et décide d'inclure ces 2 années. Vous devez donc insérer deux lignes en début de tableau et taper les données suivantes.

 

DEUXIÈME ÉTAPE

Monsieur X aimerait connaître :

• le chiffre d'affaires annuel par produit
• le chiffre d'affaires global de la firme

Le chiffre d'affaires est le montant des ventes (c’est-à-dire le produit des quantités vendues par le prix). Cet exercice est l’occasion d’utiliser les références RELATIVES.

Chiffre d'affaires annuel par produit :

Pour chaque année et chaque produit, vous devez calculer le chiffre d'affaires. Afin de garder une présentation claire, vous introduisez une colonne intitulée chiffre d'affaires (A ou B) à droite de la colonne prix de chaque type de confiture. Si cela est nécessaire, ajustez la taille des colonnes.

Vous introduisez à la ligne correspondant à l’année 1999 la formule permettant de trouver le chiffre d’affaires. Vous copiez ensuite cette formule vers le bas et vous faites de même pour calculer le  chiffre d'affaires de la confiture de bananes.

Chiffre d'affaires global par produit :

Le chiffre d'affaires global par produit est la somme des chiffres d'affaires annuels par produit. En utilisant la fonction SOMME, on vous demande de calculer le chiffre d’affaires global par produit. Pour vérification, le résultat est 26847 € pour les abricots et 42020 € pour les bananes. Le chiffre d’affaire global par produit devra être placé judicieusement dans le tableau.

TROISIÈME ÉTAPE

On vous demande maintenant de calculer le bénéfice avant impôt pour chaque année. Pour cela, vous devez

1. Calculer le chiffre d'affaires annuel.
2. Calculer les charges annuelles.
3. Soustraire les charges annuelles du chiffre d'affaires annuel.

1. Chiffre d'affaires annuel

Afin de calculer le chiffre d'affaires annuel, il suffit d'additionner, pour chaque année, les chiffres d'affaires annuels pour chacun des produits. Vous tapez la formule pour la première année et la recopiez ensuite pour les autres années.

2. Calcul des charges annuelles de l'entreprise

Les charges de l'entreprise sont constituées en grande partie de frais fixes (amortissement des machines, salaires, etc.). Monsieur X estime qu'ils atteignent environ 3750 € par an. Les frais variables sont fonction des quantités produites et diffèrent pour chaque sorte de confiture :

1,60 € par unité de confiture d'abricot produite
2,60 € par unité de confiture de banane produite
Ces informations sont reprises dans la feuille “frais”.

Cet exercice est l'occasion d'utiliser les références MIXTES.

Inscrivez d'abord les frais totaux dans une colonne. En sachant que les frais totaux annuels sont la somme des frais fixes et des frais variables de chaque année, tapez la formule pour 1999, puis copiez sur le reste de la colonne.

3. Bénéfice annuel

Pour calculer le bénéfice annuel, soustrayez les charges annuelles du chiffre d'affaires annuel. Pour vérification, le bénéfice annuel de 1999 est de 2575 €.

QUATRIÈME ÉTAPE

On vous demande de calculer dans la feuille “vente” le nombre total de kilos de confiture vendus par Monsieur X chaque année sachant que le poids net d’un pot de confiture est pour chacun des produits de 0,5 kg. Pour ce faire :

1. Créer deux nouvelles colonnes à droite de la colonne Q B.

2. Multiplier la quantité vendue pour chaque année et pour chaque produit par le poids net d’un pot de confiture.

Cet exercice est l’occasion d’utiliser les références ABSOLUES. Pour vérification, Monsieur X a vendu 775 kg de confiture d’abricots en 1999.

CINQUIÈME ÉTAPE

La cinquième étape consiste en l'analyse graphique du chiffre d'affaires. On vous demande de montrer l'évolution du chiffre d'affaires global de la firme en distinguant ses deux composantes (abricots et bananes).

Faites appel à l'assistant graphique. Choisissez un graphique de type aires, prenez le deuxième graphique proposé. Dans série, vous trouvez l'adresse des cellules relative à chaque série de données. Il vous faut remplir la case 'étiquette des abscisses'. A la troisième étape, Excel vous demande notamment d'inscrire un titre pour le graphique (Evolution du chiffre d'affaires), l'axe des X (années) et des Y (Euros). Lors de la quatrième étape, indiquez à Excel qu'il peut afficher le graphique sur la feuille courante. Si Excel n'affiche pas le graphique à l'endroit désiré, déplacez-le en fonction.

SIXIÈME ÉTAPE

Afin de donner aux résultats une meilleure présentation, il faudrait que les titres de la première ligne soient en gras centrés par rapport aux cases leur correspondant (fusionner les cellules). La cellule « Année » devrait elle aussi être en gras.

Il faudrait également que le tableau soit divisé par des bordures à votre choix mais que les deux premières lignes soient séparées des autres par un double trait, tout comme la première colonne.