TP1-EXCEL

Term. GSI TP N°1 TRAVAUX DE REVISIONS SUR LE TABLEUR EXCEL

NOTIONS ABORDEES : FONCTIONS SI, RECHERCHV, NOM……

TP N°1 : SERIE D'EXERCICES SUR LE TABLEUR EXCEL

Exercice 1La société ALAMANTE vend 5 produits qui se trouvent sur son catalogue des ventes (Annexe 1). La commande est acceptée si le client achète plus de 2 produits sans toutefois en prendre plus de 5. Dans le cas contraire, un système de blocage devra être étudié.

Une remise est accordée par tranches :

Tranche comprise entre 0 – 30 000 : 3%.Tranche comprise entre 30 000 – 50 000 : 5%.Tranche supérieure à 50 000 : 8%.

Un escompte est accordé si le montant brut de la facture est supérieur à 30 000 € ou si le paiement a lieu au comptant.

En vous aidant des annexes 1 et 2, présenter dans un tableau toutes les cellules calculées ainsi que leur formule de calcul.

Annexe 1

293031323334

A B CCODE DESIGNATION P.U

1 PRODUIT A 10,00 € 2 PRODUIT B 15,00 € 3 PRODUIT C 12,00 € 4 PRODUIT D 11,00 € 5 PRODUIT E 18,00 €

Tp 1 – Révisions EXCEL 1/5



Annexe 2

123456789

101112131415161718192021

A B C D ECODE DESIGNATION QUANTITE P.U TOTAUX

1 PRODUIT A 600 10,00 € 6 000,00 € 2 PRODUIT B 500 15,00 € 7 500,00 € 3 PRODUIT C 900 12,00 € 10 800,00 € 4 PRODUIT D 400 11,00 € 4 400,00 € 5 PRODUIT E 600 18,00 € 10 800,00 €

MONTANT BRUT 39 500,00 €

REMISE 1 375,00 €

NET COMMERCIAL 38 125,00 €

ESCOMPTES 3 812,50 €

NET FINANCIER 34 312,50 €

TVA 19,6% 6 725,25 €

NET A PAYER 41 037,75 €




Exercice 2

L'entreprise DEBEC é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.

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.II est à noter que les frais de port facturés aux clients s'élèvent à 1000 € si le total des marchandises est inférieur à 10000€, 500 € si le total des marchandises est compris entre 10 000 et 50 000 €, et gratuit si le total des marchandises dépasse 50 000 €.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

A B C D E F G HCode

articleDésignation Quantité

Prix

unitaire Montant total

125 PORTE D'ENTREE 2 2325,00 4 650,00 €

- €

- €

- €

- €

- €

TOTAL marchandises 4 650,00 €

Remise 10% - €

TOTAL NET 4 650,00 €

Frais de port 1 000,00 €

TOTAL Hors Taxes 5 650,00 €

TVA à 19.6% 1 107,40 €

TOTAL TTC 6 757,40 €

Une remise de 5 % sera accordée si le montant brut est supérieur à 24000 €.Le corps commercial de la facture devra se présenter à peu près de la façon suivante

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


Code article

Désignation QuantitéPrix

unitaireCode article

Désignation Quantité Prix unitaire

725 PORTE FENETRE 10 1782,00 125 PORTE D'ENTREE 2 2325,00544 FENETRE 80 X 140 50 812,00125 PORTE D'ENTREE 7 2325,00



Exercice 3

La société LOUTOUT a pour objet la location de véhicules sans chauffeur. Elle propose plusieurs types de véhicules regroupés en catégorie de prix. (Voir la tarification dans la définition du modèle).Le véhicule est remis au client avec le plein de carburant, contre le dépôt d'une caution. Si le client n'est pas un abonné, la société lui attribue un numéro supérieur à 10000.

EXTRAIT GENERAL DES CONDITIONS DE LOCATION.

* CONDUCTEUR.- Age minimum 23 ans.

- Titulaire du permis de conduire.

* CARBURANT. - Non compris dans la location, le véhicule est remis avec le "plein" de carburant.

Au retour, le "plein" est refait de nouveau et facturé au client.

* TARIF. - C'est un forfait journalier prévu pour 120 Km. Pour les kilomètres supplémentaires parcourus,

une indemnité kilométrique est facturée. Le forfait est un tarif dégressif, c'est-à-dire qu'il diminue en fonction du nombre de jours. Il est diminué de X%.

X représentant le nombre de jours de location. Toute journée commencée est due.La T.V.A sera calculée sur le forfait majoré de l’indemnité kilométrique.

* CAUTION.

- Une caution de 4 500 € est demandée au départ. Celle-ci sera encaissée.

* OFFRE SPECIALE A SES ABONNES.

- Pour les abonnés (n°< 5000) titulaires de la carte "Route Libre", aucune caution n'est demandée à la prise en charge du véhicule sauf si le véhicule loué est un véhicule de catégories 5,6,7. La caution normalement exigée est réduite de 50%.

- Les abonnés (numéros compris entre 5000 et 10000) titulaires de la carte "grande Route" bénéficient en plus de la dispense de caution, d'une remise de 20% sur les forfaits journaliers.

La société LOUTOUT vous demande de réaliser la facturation de la location.Elle souhaite que la remise et la caution apparaissent avec le signe "-".

En vous inspirant de l’exemple, faites l’analyse des résultats.Vérifier vos formules avec Excel.

Toutes les cellules encadrées sont calculées.




123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051

A B C D E F G H I J K L M

NOMS CELLULES

SOCIETE LOUTOUT CARBU =LOUTOUT!$J$22Rue de la gare Nom du client Melle Honoré de Beaulieu CAT =LOUTOUT!$J$859 000 Lille Rue 150, Rue de Paris CAUT =LOUTOUT!$J$25

Ville 06 000 Nice DATESYS =LOUTOUT!$J$7 DD =LOUTOUT!$B$12Numero de client : 4200 le : 13-avr-04 DR =LOUTOUT!$D$12Véhicule : MEGANE Catégorie : 4 DU =LOUTOUT!$J$24

FORFAIT =LOUTOUT!$B$45:$H$51Date Départ Date retour Nombre de jours Forfait H.T. Montant FORHT =LOUTOUT!$H$12

IKHT =LOUTOUT!$H$1615/01/2001 13-avr-04 1185 -9 656,50 € -11 442 952,50 € KMD =LOUTOUT!$B$16

KMPAY =LOUTOUT!$F$16Km départ Km retour Km à payer Indemnité Km H.T. KMR =LOUTOUT!$D$16

MONFOR =LOUTOUT!$J$1212500 14400 0 0,00 € 0,00 € MONKM =LOUTOUT!$J$16

NBRJ =LOUTOUT!$F$12(1) Remise AGR 0% 0,00 € NET =LOUTOUT!$J$27

NUMCLI =LOUTOUT!$D$7Total Hors Taxes -11 442 952,50 € REM =LOUTOUT!$J$18T.V.A 19,60% -2 242 818,69 € REMB =LOUTOUT!$J$28Carburant 150,00 € TOTALHT =LOUTOUT!$J$20

TVA =LOUTOUT!$J$21Montant dû -13 685 621,19 € TXREM =LOUTOUT!$I$18Caution 0,00 €

Net à Payer 0,00 €A rembourser -13 685 621,19 €

AGR : Abonné grande route

CATEGORIE FORFAIT Kilométre TypeJOURNALIER supplémentaire Véhicule

1 410 1,7 TWINGO2 590 2,45 PUNTO3 680 2,85 PUNTO CABRIOLET4 890 3,7 MEGANE5 950 3,85 PEUGEOT 4066 1310 5,45 SAFRANE7 1760 7,45 B.M.W 3.25


Documents

TP1-EXCEL