Macros-commandes VBA/Exercices/Calcul d'une commission

Calcul d'une commission

L’entreprise TAL souhaite automatiser son système de rémunération de ses vendeurs à l'aide d'un tableur Excel. Pour ce faire, chaque commercial perçoit un salaire brut mensuel composé:

• d'un salaire fixe de 1 000 €
• d'une commission (COMMISSION) qui sera calculée en fonction d'un taux (TAUXCA) sur Chiffres d'Affaires (CA), et majorée selon un taux lié à son ancienneté (TAUXAN = 1% par année de présence) dans l'entreprise
  • COMMISSION = TAUXCA * CA
  • COMMISSION = COMMISSION + COMMISSION * TAUXAN

Vous disposez des informations suivantes :

Représentant	Vente	Ancienneté	Commission	Fixe	Salaire Brut
CHEL	28 000 €	4		1 000 €
NAIT	22 000 €	8		1 000 €
ROBERT	8 000 €	0		1 000 €
MONDON	10 000 €	5		1 000 €
JULES	15 000 €	6		1 000 €
DUPONT	22 000 €	12		1 000 €
LACROS	18 000 €	8		1 000 €
MARTIN	20 000 €	9		1 000 €
DACOSTE	18 000 €	10		1 000 €
NATACHA	28 000 €	8		1 000 €
FALGO	22 000 €	15		1 000 €

Chiffre d'affaires mensuel	Taux de commission
0 à 10 000 €	6%
10 000 € à 20 000 €	8%
20 000 € à 30 000 €	10%
> 30 000 €	12%

Questions :

1) Concevez une fonction personnalisée VBA permettant de calculer la commission des vendeurs en fonction du chiffre d'affaires mensuel réalisé et de l’ancienneté.

2) Calculez en EXCEL la rémunération brute de chaque commercial

Solution

• Nous allons créer une fonction qui permet de calculer les commissions des commerciaux en fonction des ventes réalisées et de leur ancienneté dans l'entreprise.
• Pour cela, il faut ouvrir le fichier "commission.xlsm" et dans l'onglet Développeur Visual Basic recopié le code VBA ci-dessous :

'La fonction "commission" utilise deux variables: Ventes et Ancienneté. La valeur retournée est un nombre à décimal fixe
Function Commission(Ventes As Currency, Anciennete As Integer) As Currency
    'Déclaration de la constante + attribution de sa valeur
    Const TauxCom1 As Double = 0.06
    Const TauxCom2 As Double = 0.08
    Const TauxCom3 As Double = 0.1
    Const TauxCom4 As Double = 0.12
    'Début de la sélection
    Select Case Ventes 'indique la variable à tester (ici Ventes)
       Case 0 To 10000 'Si la variable "Ventes" est compris entre 0 et 10000 alors effectuer le calcul suivant
       Commission = Ventes * TauxCom1
       Case 10000 To 20000 'Si la variable "Ventes" est compris 10000 0 et 10000 alors effectuer le calcul suivant
       Commission = Ventes * TauxCom2
       Case 20000 To 30000 'Si la variable "Ventes" est compris entre 20000 et 30000 alors effectuer le calcul suivant
       Commission = Ventes * TauxCom3
       Case Is >= 30000 'Si la variable "Ventes" est supérieur à 30000 alors effectuer le calcul suivant
       Commission = Ventes * TauxCom4
    'Fin sélection
     End Select
    'Formule pour calculer une commission
    Commission = Commission + (Commission * Anciennete / 100)
End Function

• L'exécution de cette fonction personnalisée est identique à une fonction Excel classique.