Exercices Excel sur les différents calculs

Exercice Excel calcul d’itérations intérêts composés

Notions : progression géométrique : références relatives, recopie.

Le club investissement d'une grande école vient de prendre son bénéfice. Et il est coquet ! Le club envisage de prêter cette somme à l'association des élèves pour financer un voyage promo sur la station spatiale. L’association rémunérera annuellement cette somme à un taux d’intérêts composés T.

Dans un emprunt  les intérêts se calculent à l'échéance de chaque période, ici annuelle. Si le prêt est à t%, avec un capital c, les intérêts au bout d'un an se montent à c x T ; le montant dû par l’emprunteur est          c + c x T

somme qui devient le nouveau capital prêté pour l'année suivante.     C_année = C_année-1 ^ (1+T)

On demande d'établir le tableau de l'accroissement du montant dû) pour 12 ans.

Exercice Excel calcul de la somme automatique

L'entreprise SOMMES emploie 4 représentants. Chacun d'entre eux a effectué un chiffre d'affaires qu'il vous communique. Présentez ces chiffres sous la forme d'un tableau qui fera apparaître les totaux annuels par représentant et le total du chiffre d'affaires de chaque trimestre pour l'ensemble des 4 représentants.

M. MAXIME

87 200 ? au 1er trimestre, 88 200 ? au second trimestre, 75 700 ? au troisième trimestre et 94 200 ? au quatrième trimestre.

M. LEGENDRE

1er trimestre : 66 200 ?
2ème trimestre : 72 200 ?
3ème trimestre : 70 200 ?
4ème trimestre : 83 200 ?

M. MARTINALE

Trimestre 1 : 104 200 ?, trimestre 2 : 100 800 ?, trimestre 3 : 103 100 ?, trimestre 4 : 109 000 ?

M. SERTIF

J'ai réalisé  89 200 ? de chiffre d'affaires au premier trimestre, 80 200 ? au second, 81 500 ? au troisième et 86 900 ? au

Solution : 

Formule en F2 : =somme(B2:E2) - Cette formule est à recopier sur les lignes suivantes.

Formule en B6 : =somme(B2:B5) - Cette formule est à recopier sur les colonnes de droite

Exercice Excel consolidation de tableaux contenant des données différentes

La société PRETA commercialise des articles de prêt à porter.

On vous demande de concevoir un tableau, établi à partir des chiffres ci-dessous, faisant apparaître :

1- le total des achats par mois pour chaque article
2- le total des achats du trimestre par article
3- le total des achats par mois.

Vous utliserez, pour cela, les fonctions de consolidation.

Nombres d'articles achetés au cours du trimestre :

Achats au fournisseurs LESTETE

Exercice Excel calculs financiers pour compte bancaire

Dans ce premier exercice simple et utile vous serez guidé pas à pas pour construire la feuille de calcul. Excel est parfaitement adapté à ce type de calcul de gestion.
Notions : fonctions SOMME et SI, mise en page, recopie de formules avec références relatives.

Afin de tenir à jour un compte bancaire, on se propose d'utiliser un tableur. Les différentes opérations sont mises dans un tableau, à raison d'une opération par ligne, avec leur date et leur montant. 

1/ entrée des opérations bancaires.

Dans un premier temps nous allons entrer les différentes opérations, leur date dans la colonne A et leur libellé dans la colonne B. Le montant de l'opération est placé en colonne C s'il s'agit d'une opération débitrice, en colonne D s'il s'agit d'une opération créditrice.

Les totaux seront calculés par une formule de sommation, comme nous le verrons dans l'étape suivante.

2/ calculs de débit et de crédit.

Plaçons nous dans la cellule correspondant au total du débit, en C16.

Une formule commence toujours par un signe =. Excel possède une fonction prédéfinie SOMME. Elle s'utilise comme toutes les autres fonctions, sous forme =SOMME(ref:ref). Pour entrer la formule nous pouvons frapper directement le texte "=SOMME(" ou bien cliquer sur l'icône de l'outil de sommation, puis sélectionner la plage de cellules à sommer C5:C15.

Pour le calcul du total du crédit, nous allons recopier cette formule (en C15), qui est en références relatives), à droite (en D15).

3/ calculs du solde final.

Nous avons maintenant les totaux des débits et crédits, il ne nous reste plus qu'à calculer le nouveau solde du compte. La formule de calcul du nouveau solde en ligne 17 fait la différence entre les totaux des opérations de débit et de crédit. Évidemment si le solde est positif il figure en colonne Crédit, et en colonne Débit s'il est négatif : dans ces cellules la valeur à afficher est conditionnée par son signe, nous utiliserons la fonction SI.

Ainsi en C17 nous avons    =SI(C16>D16;C16-D16;"")    et en D17 la formule complémentaire.

4/ libellé final.

En B17 nous plaçons le libellé "solde débiteur" ou "solde créditeur", suivant le cas. Quelle est donc la formule =SI(… à employer ?

5/ présentation.

Pour une meilleure lisibilité du tableau, nous pouvons préciser le format d'affichage des valeurs dans les colonnes débit et  crédit, aussi mettre en gras le solde final et en italique le solde initial.

Enfin nous gagnons aussi en lisibilité à encadrer les plages de cellules pour mettre en valeur la structure du tableau.

6/ sauvegarde et impression.

Maintenant que nos calculs sont effectifs, nous désirons les conserver.

• Au préalable nous aurons mis en page la feuille de calcul : menu Fichier, ligne Mise en page.
• Sur disque nous sauvegardons la feuille de calcul, menu Fichier, ligne Enregistrer sous, en positionnant l'enregistrement sur la disquette de sauvegarde des documents.
• Sur papier nous imprimons le tableau : menu Fichier, ligne Imprimer.

7/ modifications.

Modifions une valeur et observons les totaux et solde. Ceux-ci sont automatiquement remis à jour. De même supprimons ou ajoutons une ligne d'opération bancaire (pour insérer : menu Fichier, ligne Insérer). Tout est alors recalculé.

---

Exercice Excel fonction si calcul d'une facture simple

L?entreprise FACTOR établit actuellement sa facturation à la main. Cette entreprise souhaite à présent établir ses factures sur Excel. On vous demande de reproduire le modèle de facture ci-dessous sur Excel et d'automatiser le plus possible les calculs.

• Construisez le modèle de la facture, avec toutes les formules de calcul demandées, sur la feuil1
• Renommez ensuite cet onglet "Modèle"
• Dupliquez cet onglet à chaque fois que vous aurez une facture à faire, de façon à conserver l'original vierge.

Le numéro de facture, les colonnes "code article", "désignation", "quantité" et "prix unitaire" seront saisies, les autres données seront le résultat d'un calcul effectué par Excel.

Il est à noter que les frais de port facturés aux clients s'élèvent à 100 Euros si le total des marchandises est inférieur à 10 000 Euros, 50 Euros si le total des marchandises est compris entre 10000 et 50 000 Euros, et gratuit si le total des marchandises dépasse 50 000 Euros (utilisez la fonction =SI() pour déterminer les frais de port). La facture devra se présenter à peu près de la façon suivante : 

Testez votre facture avec les bons de commande suivants (les TTC à trouver vous sont donnés pour vérification) :

TTC A TROUVER : 19145.45

TTC A TROUVER : 5 124.86

TTC A TROUVER : 128 355.32

---

Formule en F9, F10, F11, etc... : =D9*E9
Formule en F16 : =somme(F10:F15)
Formule en F17 : =F16*10%
Formule en F18 : =F16-F17
Formule en F19 : =SI(F16<10000;100;SI(F16<50000;50;0))
Formule en F20 : =somme(F18:F19)
Formule en F21 : =F20*19.6%
Formule en F22 : =somme(F20:F21)

---

Exercice Excel calcul du pourcentages et proportions

L'entreprise LAPORTE rémunère ses représentants en leur attribuant un salaire fixe et une commission basée sur le chiffre d'affaires. En outre, chaque mois, elle partage entre eux une prime. Cette prime est partagée proportionnellement au chiffre d'affaires mensuel réalisé par chacun d'eux. Les salaires fixes sont les suivants :

LAPEYRE : 1 000   -     MARTIN : 860     -     GUINOT : 950

Chiffres d'affaire :

Juillet : Octobre :

LAPEYRE              25 225                        LAPEYRE                 33 000

MARTIN                  38 720                        MARTIN                     62 020

GUINOT                  58 275                        GUINOT                     44 825

Août : Novembre :

LAPEYRE              13 405                        LAPEYRE                 38 600

MARTIN                  35 440                        MARTIN                     51 125

GUINOT                  49 445                        GUINOT                     59 335

Septembre : Décembre :

LAPEYRE              16 570                        LAPEYRE                 34 650

MARTIN                  32 240                        MARTIN                     44 105

GUINOT                  51 880                        GUINOT                     57 340

La commission sur le chiffre d'affaires s'élève à 2%, la prime à partager chaque mois est de 2000 ?

Concevoir un tableau par représentant permettant de connaître, pour chaque mois, le détail du salaire brut de chacun. (fixe + commission + prime)

---

Exercice Excel fonction RECHERCHE() facturation a partir de données

1. Reproduisez sur la feuil1 la liste des clients (liste en ANNEXE 1)
2. Renommez l'onglet de la feuil1 "Clients"
3. Reproduisez sur la feuil2 le barème des frais de port (barème en ANNEXE 1)
4. Renommez l'onglet de la feuil2 "Frais de port"
5. Reproduisez sur la feuil3 la liste des articles (liste en ANNEXE 1)
6. Renommez l'onglet de la feuil3 "Catalogue"
7. Nommer le tableau contenant la liste des clients "clients", le tableau des frais de port "port", le tableau des articles "catalogue"
8. Insérez une nouvelle feuille de calcul (menu "Insertion/ feuille")
9. Reproduisez sur la feuil4 (la nouvelle feuille) la facture (modèle en ANNEXE 2)

10. Renommez l'onglet de la feuil4 "Modèle facture"

11. concevez les formules de la facture de la façon suivante :

• A la place de "NOM" : saisir le nom d'un client
• A la place de "ADRESSE" : créez une fonction de recherche sur le NOM à partir de la liste des clients
• idem pour le code postal et la ville
• Colonne "désignation" : créez une fonction de recherche sur le code article à partir du catalogue produits
• Colonne "quantité" : saisie directe
• Colonne "prix unitaire" : créez une fonction de recherche sur le code article à partir du catalogue produits
• Colonne "montants" : produit des prix par les quantités
• Ligne "remise" : créez une fonction recherche sur le total des marchandises à partir du barème de remises

Pour élaborer les factures relatives aux bons de commandes ci-dessous, dupliquez la feuille "Modèle facture" autant de fois que nécessaire de façon à préserver votre modèle.

12. Tester la facture à l'aide des cas proposés en ANNEXE 3

ANNEXE 1

Liste des principaux clients

Barème des frais de port

Le montant des frais de port diminue en fonction du total net des marchandises achetées (par exemple 50 ? de frais de port jusqu'à 100 ? de marchandises achetées, puis 25 ? de frais de port entre 1000 ? à 2000 ? de marchandises achetées, etc.)

Catalogue produits 

ANNEXE 3

Bon de commande du client EMILIE

Bon de commande du client JEROME

Bon de commande du client FERNAND

Bon de commande du client KILIGE

---

 Exercice Excel fonction si plus complexe

Saisissez les données ci-dessous ou bien téléchargez le fichier de l'exercice.

Vous disposez des informations tarifaires dans les tableaux ci-dessous enANNEXE 1 à saisir dans une feuille de calcul d'un nouveau classeur. 
Vous renommerez l'onglet de cette feuille : "Tarifs".
Vous construirez ensuite, sur une deuxième feuille du classeur, les deux fiches en ANNEXE2.
Vous renommerez l'onglet de cette feuille : "Devis".
Dans la fiche de renseignements, vous saisirez les données fournies par le client (exemple en ANNEXE3).
A l'aide de formules de calculs, vous exploiterez les informations contenues dans la fiche de renseignements et dans la feuille "Tarifs" afin d'automatiser la fiche "Devis séjour". Toutes les données de la fiche "devis séjour" seront le résultat de formules de calculs. 
Une fois le document terminé, aucune information ne devra être saisie dans le devis.

ANNEXE 1 - Tarifs

Tarif des locations

Assurance Annulation

Remises

Demi-pension (prix de la semaine)

ANNEXE 2 - Fiche de renseignements et Devis

ANNEXE 3 - Exemples de renseignements à saisir pour vérifier vos formules

---

 Télécharger la solution

Plutôt que d'écrire les montants dans la formule, il est préférable faire référence aux cellules contenant ces montants, qui se trouvent dans la feuille de calcul où vous avez construit les tableaux de tarif. De cette façon, en cas de changement tarifaire, il ne sera pas nécessaire de modifier les formules de calcul.