BTS MCO 1ére année

Transversale : Tableaux de bord

Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 1 : Chiffre d’affaires

Consignes de travail

Les cases vides sont destinées à recevoir des calculs. À vous de créer les formules !

Le formatage de la cellule doit être monétaire

CHIFFRE D'AFFAIRES DES REPRESENTANTS

Noms Code Janvier Février Mars Total

1216 42 685 18 751 116 242
AZAR

CHANLOT 3452 48 806 59 455 70 279

FRANCOLONNE 1246 97 491 103 894 44 057

MASOF 4023 76 148 91469 85 905

NOGLOTTE 3453 61970 114 336 109 229

SENATUS 3454 94 899 78 053 84151

VALMOND 4081 86 286 129 581 72 565

Total

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 1 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 2 :Notes élèves

Consignes de travail

Mettre en page le tableau et saisir les notes des élèves

Trier alphabétiquement les élèves à l'aide de la fonction TRI

Faire effectuer les calculs par Excel en insérant les formules adéquates

Noms Français Maths Anglais Gestion Droit Économie Moyenne

BIELSA 10,50 12,75 11,00 15,00 13,25 12,00

LARTIQUE 8,00 13,25 15,00 10,00 12,00 11,50

MESTRESAT 7,00 10,00 12,00 8,00 10,00 12,25

NOGUEIRA 11,50 12,25 14,00 10,00 9,30 8,40

12,30 16,40 12,35 19,50 18,40 16,15 -

DUMAIN ·-

VARENNES 11,00 8,50 9,75 11,00 10,50 8,40 -

DESVIGNES 16,00 18,50 17,75 15,50 13,25 14,75

MAURY 9,40 8,75 7,50 6,25 5,75 10,00

--·
·-
FULOUX 16,00 18,75 15,50 13,25 12,75 14,00 i"-;

DHUGUES 18,75 13,25 15,75 16,25 12,75 11,00

GAGNAIRE 13,00 15,00 16,75 17,25 13,40 13,00

Moyenne par
matière - -

Meilleure note

Note la plus basse -.

Moyenne Générale

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 2 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 3 :

Consignes de travail

Vous appliquerez une mise en forme conditionnelle, dont vous déterminerez vous-même les règles,
pour que les valeurs inférieures à la moyenne s'affichent automatiquement en rouge.

Vous utiliserez en image de fond une bouteille de vin en filigrane

Classement des enseignes selon les ventes des rayons boissons

indice 100 = moyenne des prix des GMS

*BRSA: Boissons rafraîchissantes sans alcool

Indice général BRSA* et eaux Vins

Enseignes Semestre 1 Semestre 2 Semestre 1 Semestre 2 Semestre 1 Semestre 2
Leclerc 97,10 96,40 96,40 99,90 95,20 96,40
.
lntermarché 97,20 96,70 97,00 95,30 95,80 96,10
Carrefour 97,70 99,00 97,20 96,30 97,60 99,00
Auchan 99,00 98,30 98,50 98,10 98,90 98,30
Système U 100,40 98,20 100,50 98,40 99,00 97,50
Atac 100,70 102,70 100,60 98,00 100,30 101,70
Cora 100,70 101,70 101,00 100,40 100,80 98,10
Champion 100,90 99,70 101,10 103,10 101,30 102,70
Géant 101,10 100,30 101,10 104,3 103,00 100,30
0
Match 101,50 102,80 101,40 102,7 103,50 103,00
0
Casino 103,70 104,40 105,10 104,0 105,00 106,00
0

APPLICATION 4 : Coûts

Consignes de travail

Réfléchissez avant d'agir.

Quel pourcentage veut-on calculer, par rapport à quoi veut-on faire une comparaison ?
complétez les cases vides avec les formules Excel adéquates.
une mise en page paysage est de rigueur

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 3 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 5: Ventilation C.A

Consignes de travail

Construire un tableau selon le modèle ci-dessous permettant de calculer, à partir du chiffre d'affaires
mensuel hors taxes la T. V. A. mensuelle au taux en vigueur, le chiffre d'affaires mensuel T. T. C..

N’oubliez pas d'utiliser la poignée de recopie pour les noms des mois

Le titre du tableau : ventilation et analyse du chiffre d’affaires

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 4 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

T.V.A.
Mois C.A. HT C.A.TTC C.A.
19,60% en%
Janvier 4 185,80 €
Février 2 859,20 €
3 406,02 €
4377,60 €
4 995,00 €
5 869,30€
3 913,38 €
1881,22 €
4046,60 €
5 452,34€
.5 011,00 €
4 335,35 €
Total

APPLICATION 6: Catalogue

Consignes de travail

Sur ce tableau, les cellules vides devront afficher les prix diminués de la remise annoncée en haut de
colonne.

Catalogue d’accessoires informatiques

Tarif-revendeur hors taxes par quantité

Prix public de 1 à 4 de 5 à 9 de 10 à 19 20 et+
Réf. Désignation
en €
-10% -15 % - 20% - 25 %

S 5236 Clé USB capacité 512 Mo 10

S 5496 Clé USB capacité 1 Go 25

S 5232 Clé USB capacité 2 Go 30

S 5237 Écran LCD 178

S 6290 Souris optique sans fil 15

S 1911 Clavier sans fil 21

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 5 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 7: BUDGET DE TRÉSORERIE

Consignes de travail

Le plan de trésorerie que nous vous proposons comporte trois parties :

-Encaissements prévisionnels, décaissements prévisionnels,

-Écarts entre encaissements et décaissements.

La situation de trésorerie à la fin de chaque mois est calculée de la façon suivante :

Situation initiale + total encaissements - total décaissement

Ce solde est repris au début du mois suivant.

Lorsque vous voudrez saisir un tiret en début de cellule, Excel va croire qu'il s'agit d'un calcul,
puisque c'est un signe arithmétique. Pour lui indiquer que c'est un tiret alphabétique, faites-le
précéder d'une apostrophe {qui ne se verra pas).

Pour reprendre début février la situation de fin janvier, placez-vous sous le nom février et tapez
= puis allez chercher avec la souris la cellule qui indique la situation de fin janvier. Recopie2
ensuite ces cellules horizontalement.

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 6 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 8: Évolution des ventes

Consignes de travail

Créer sur la première feuille d'un classeur le tableau Année N(actualiser N= année en cours).

Sur la, deuxième feuille du classeur, créer le tableau, évolution des ventes et le compléter avec les
données obtenues par calcul dans le tableau année N

D'après le tableau Année N créer un histogramme visant à comparer les ventes par catégorie et par
ville.

D'après les données du même tableau, créer un graphique à barres empilées à 100 % faisant apparaître
trois séries : le total de l'année N, la ville ayant réalisé le plus petit chiffre d'affaires et celle ayant
réalisé le plus important (en fin de ligne, ne sélectionnez pas les totaux).

D'après le tableau d'évolution des ventes, créer un graphique adapté pour représenter l'évolution du
chiffre d'affaires par catégorie au cours des 5 dernières années.

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 7 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 9 : VALREAS

Consignes de travail

L’Entreprise VALREAS souhaite étudier sur les 4 dernières années pour ses 4 sites du sud de la France
les coûts de communication Internet; cette étude permettra d'envisager un autre type de connexion.

-Créer un graphique montrant l'évolution des coûts de communication par site sur les 4 années

-Créer un graphique montrant la répartition des coûts pour les 4 sites pour l'année

n-1 seule.

Vous disposez du tableau suivant :

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 8 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 10 : RENOVA

Consignes de travail

L'Entreprise RENOVA commercialise des téléphones cellulaires.

Voici le tableau des quantités vendues mois par mois durant les trois dernières années.
Représenter ces données par un graphique Radar (les trois années devront ressortir différemment les
unes des autres).

APPLICATION 11 : CHABAUD

Consignes de travail

L'entreprise CHABAUD offre en fin de trimestre une prime de 25 € par enfant à toutes les familles de
2 enfants et plus.

• Avec une formule conditionnelle, faites afficher par Excel prime ou pas de prime

Dans La colonne chèques, refaites une condition basée sur la précédente et

Calculez le montant des chèques à faire. '" Dégagez le coût global pour l'entreprise.

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 9 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 12 : GRIFFON

Consignes de travail

La société GRIFFON rémunère ses représentants par une commission dont le taux est fonction du
chiffre d'affaires réalisé par ceux-ci.

• Pour un chiffre d'affaires inférieur à 76 225 €, le taux est de 10 %

• Pour un chiffre d'affaires à partir de 76 225 €, le taux est de 15 %

• Construire le tableau ci-dessous avec les formules conditionnelles adéquates dans la colonne taux.

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 10 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

• Introduisez ensuite les autres formules nécessaires.

Représentants Chiffre d'affaires Taux commission Montant commission

15 245 €
RIVOIRE
76 450 €
CORNET
85 372 €
JOLIVET
135 680 €
HARDY
76 074 €
DURAND
.
LEROUX
77 749 €

1
Totaux 1
1
1

APPLICATION 13: SEMATIC

Consignes de travail

Dans l'entreprise SEMATIC, les vendeurs sont rémunérés de la façon suivante :

• un salaire fixe de 1 220 €

• une commission de 2 % sur leur chiffre d'affaires

• un bonus personnel, lorsque leur objectif personnel a été dépassé ; ce bonus correspond à 8 %
du montant du dépassement

• un bonus d'équipe, lorsque l'objectif global de l'équipe a été dépassé ; toutefois ce bonus n'est
versé qu'à ceux qui ont également atteint ou dépassé leur objectif personnel ; il correspond à 3 % du
dépassement global.

• Calculer la rémunération totale des vendeurs en fonction des éléments ci-dessus.

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 11 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 14: MERCATOR

Consignes de travail

Dans l’entreprise MERCATOR, les directeurs commerciaux sont rémunérés par une somme fixe de 1
670 € plus une commission calculée au prorata du chiffre d'affaires réalisé par son équipe.

Calcul de la commission

Pour un chiffre d'affaires inférieur à 15 250 € Pas de commission

Pour un chiffre d'affaires de 15 250 € à moins de 30 500 € Commission = 6 %

Pour un chiffre d'affaires de 30 500 € à moins de 76 250 € Commission= 7,5 %

Pour un chiffre d'affaires supérieur à 76 250 € Commission= 10 %

Etablissez le tableau de calcul des salaires des 4 directeurs commerciaux, sachant que les chtffres
d'affaires des régions ont été les suivants :

NORD: 57 626 €
SUD: 100 312 €
EST: 37 655 €
OUEST: 122 112 €

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 12 sur 13

 BTS MCO 1ére année
Transversale : Tableaux de bord
Activité 1 : prise en main d’Excel

TD prise en main d’EXCEL

APPLICATION 15 : Augmentation de tarifs

Consignes de travail

L'agence de voyages INCENTIVE va augmenter ses tarifs. Elle a également décidé d'inclure la taxe
d'aéroport dans le prix public.

Voici le tarif initial :

• A l'aide du calcul matriciel, calculez directement dans un troisième tableau les tarifs
augmentés et contenant .la taxe d'aéroport.

Introduction à la prise en main d’EXCEL Mme NIÇOISE Page 13 sur 13