Taux d'imposition et salaires nets avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Calcul des charges sur salaires

Cet exercice Excel consiste à résoudre un cas multicritère. Une société réalise le suivi des performances de ses commerciaux. Des commissions conditionnelles sont attribuées selon les chiffres d'affaires réalisés. Additionnées aux fixes, elles permettent de déduire le salaire total brut. Sur ce dernier, il faut considérer des tranches d'impôts à calculer. Une fois déduits, ils fournissent le salaire net pour chacun.

Source et présentation de la problématique
La classeur source existe et nous devons commencer par le réceptionner.

• Télécharger le classeur calcul-salaires-nets.xlsx en cliquant sur son lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Le tableau de suivi des commerciaux est proposé sur l'unique feuille de ce classeur. Ils sont énumérés en colonne C. Les salaires fixes respectifs sont inscrits dans la colonne D voisine. Ces vendeurs interviennent sur deux secteurs. C'est la raison pour laquelle leurs chiffres d'affaires sont tronçonnés dans les deux colonnes respectives E et H.



Dans les deux cas, il convient alors de calculer un taux de commission et la commission elle-même dans les colonnes suivantes. Comme vous le savez, dans Excel, tous les calculs doivent être dynamiques. C'est pourquoi un petit tableau des conditions est posé plus bas dans la feuille, entre les colonnes B et F. Pour un chiffre d'affaires inférieur à 3000 Euros, aucune commission n'est allouée. En revanche, pour un CA compris entre 3000 et 7000, un taux de 5% se déclenche et de 7% au-delà.

Les taux d'imposition à déduire sur les salaires répondent eux aussi à de multiples critères. Lorsque le salaire total (Fixe + Commission) ne dépasse pas les 1500 Euros, aucun impôt n'est dû. Et comme vous le remarquez, il augmente ensuite par tranches, selon le niveau de rétribution du salarié.

Calculs des taux et commissions
L'analyse multicritère consiste à comparer le chiffre d'affaires de chacun avec les valeurs des contraintes imposées dans le tableau de bord. Comme elles se cumulent, elles doivent être évaluées les unes après les autres, pour n'en oublier aucune. L'une des méthodes suggère d'imbriquer les fonctions Excel Si :

=Si(Condition; Action_alors ; Action_sinon)

Le critère à analyser doit être passé en premier argument de la fonction. S'il est vérifié, l'action correspondante doit être enclenchée dans le deuxième paramètre. S'il n'est pas satisfait en revanche, une autre action doit être prévue en troisième paramètre. Ici, les actions sont les calculs de taux.

Cependant, selon cette construction, seule une condition pouvant être vérifiée, seules deux actions peuvent être enclenchées. Or, comme l'indique le tableau de bord pour le calcul des taux, trois cas sont à envisager. Il faut donc analyser un critère supplémentaire en imbriquant une seconde fonction Si dans la première.

• Sélectionner le premier taux à trouver, soit la cellule F6,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit Si(,
• Sélectionner le premier chiffre d'affaires à comparer, soit la cellule E6,
• Taper le symbole inférieur pour l'inégalité du critère à vérifier,
• Sélectionner la première contrainte à atteindre, soit la cellule C17 du tableau de bord,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul, ce qui donne : $C$17,

En effet et c'est très important, cette formule est destinée à être répliquée sur les lignes du dessous mais aussi sur l'autre colonne des taux. Et malgré ces déplacements, chaque chiffre d'affaires doit être strictement comparé à ce premier seuil, immuablement placé en cellule C17.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Puis, sélectionner la cellule correspondante du tableau de bord, soit B17,
• Pour les mêmes raisons que précédemment, enfoncer la touche F4 du clavier, soit : $B$17,

Si la condition n'est pas satisfaite, cela indique que le chiffre d'affaires du commercial n'atteint pas le premier palier des 3 000 Euros. Dans ce cas, comme l'indique le panneau des contraintes, aucune commission n'est concédée.

• Taper un nouveau point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir de nouveau la fonction conditionnelle suivie d'une parenthèse, soit Si(,

En effet, deux cas supplémentaires sont à étudier. Il est donc nécessaire de poser un nouveau critère débouchant sur deux issues différentes.

• Sélectionner de nouveau le chiffre d'affaires à comparer, soit la cellule E6,

La condition à remplir est différente mais elle concerne bien sûr le même chiffre d'affaires. Il est possible de ne pas atteindre sa cellule à la souris, à cause de la formule qui déborde. Dans ces conditions, il suffit simplement de saisir ses coordonnées (E6) au clavier.

• Taper le symbole inférieur (<) pour l'inégalité de la condition à remplir,
• Sélectionner le deuxième seuil du panneau de contrôle, soit la cellule C18,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul, ce qui donne : $C$18,
• Taper un point-virgule (;) pour passer dans la branche Alors de cette seconde fonction Si,
• Désigner le taux associé, soit la cellule B18,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $B$18,
• Taper un point-virgule (;) pour passer dans la branche Sinon de cette seconde fonction Si,
• Sélectionner le dernier taux, soit la cellule B19,
• Et la figer avec la touche F4, soit : $B$19,

En effet, si les deux premières conditions ne sont pas satisfaites, nous en déduisons naturellement que le chiffre d'affaires du commercial dépasse les 7 000 Euros. C'est l'ordre et l'enchaînement des critères qui permettent les déductions. La deuxième condition si elle est vérifiée, indique que le chiffre d'affaires est compris entre 3 000 et 7 000, puisque le premier critère n'a pas été validé.

• Fermer deux parenthèses pour les deux fonctions Si imbriquées,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette combinaison de touches permet de valider le calcul tout en conservant active la cellule du résultat. L'objectif est effectivement de répliquer sa logique sur les cellules du dessous dans un premier temps.

Quoiqu'il en soit, le premier résultat tombe et il est parfaitement logique. Comme l'indique le panneau des contraintes, c'est bien un taux de 5% qui est accordé pour un chiffre d'affaires compris entre 3 000 et 7 000 Euros.

• Cliquer et glisser la poignée du résultat sur les cellules du dessous jusqu'en ligne 12,

Tous les taux sont naturellement calculés et ils sont en parfaite adéquation avec les critères que nous avons posés. Un commercial n'ayant pas atteint le premier seuil ne perçoit aucune commission. En revanche, lorsqu'il dépasse le plafond le plus élevé, il touche la commission la plus importante en pourcentage.

La formule que nous avons bâtie est la suivante :

=SI(E6<$C$17;$B$17;SI(E6<$C$18;$B$18;$B$19))

Si vous sélectionnez le dernier taux calculé en F12, vous pouvez consulter sa formule adaptée, dans sa barre de formule, au-dessus de la feuille :

=SI(E12<$C$17;$B$17;SI(E12<$C$18;$B$18;$B$19))

Les raisonnements et calculs sont bien établis par rapport au chiffre d'affaires du commercial en ligne 12. Les contraintes et taux sont immuablement exploités dans leurs cellules de référence, grâce aux dollars ayant permis de figer ces cellules. Et grâce à eux précisément, nous allons pouvoir naturellement répliquer ce raisonnement sur la deuxième colonne des taux, soit la colonne I.

• Sélectionner de nouveau le premier résultat, soit la cellule F6,
• La copier grâce au raccourci clavier CTRL + C,
• Sélectionner la première cellule de réception, soit I6,
• Coller la formule grâce au raccourci clavier CTRL + V,
• Puis, tirer sa poignée jusqu'en ligne 12,

Grâce aux références absolues, les critères et calculs se réalisent toujours par rapport aux données du panneau de contrôle. Et précisément, comme nous n'avons pas figé la cellule du chiffre d'affaires, celui-ci se déplace avec la formule. Désormais, ce sont bien les CA en colonne H qui sont utilisés pour déterminer le taux du secteur 2.



Plutôt que de copier un seul des résultats, nous aurions pu copier l'ensemble des calculs de la colonne F. Cette technique nous aurait permis de ne pas devoir tirer la poignée. Enfin et comme vous le savez pertinemment, cette formule est dynamique. Si vous modifiez des seuils ou des taux dans le panneau de contrôle, tous les résultats s'adaptent automatiquement. C'est ainsi que l'on façonne des modèles d'entreprise déclinables.

Le calcul à suivre de la commission est trivial. Il consiste en une simple multiplication du taux par le chiffre d'affaires. De plus, comme chaque valeur dans sa ligne correspond à un commercial respectif, aucune référence absolue n'est nécessaire.

• Sélectionner la première commission à trouver, soit la cellule G6,
• Taper le symbole égal (=) pour initier le calcul,
• Sélectionner le chiffre d'affaires correspondant, soit la cellule E6,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Sélectionner alors le taux correspondant, soit la cellule F6,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Tirer la poignée du résultat vers le bas, jusqu'en ligne 12,
• Copier la sélection toujours active par le raccourci clavier CTRL + C,
• Sélectionner la première commission à trouver pour le secteur 2, soit la cellule J6,
• Puis, coller les résultats (CTRL + V) afin de répliquer la logique de calcul,

Ce sont bien les taux et chiffres d'affaires des colonnes H et I qui sont cette fois exploités pour le calcul de la commission sur le secteur 2. Cette méthode du copier-coller est très intéressante pour optimiser les actions.

Calculs de synthèse
Les colonnes K, L et M sur la droite du tableau, proposent de synthétiser les informations. Il s'agit dans un premier temps de calculer la somme des chiffres d'affaires ainsi que la somme des commissions, pour chaque commercial. Ensuite, en additionnant le salaire fixe aux commissions, nous en déduirons le salaire brut pour chacun.

Ces sommes ne concernent que deux cellules à chaque occasion. L'emploi de la fonction Excel Somme automatique n'est donc pas nécessaire. Un calcul posé manuellement est aussi simple et rapide.

Il n'est pas nécessaire de nous attarder sur la mise en oeuvre des formules.

En K6 pour le premier total des ventes, la syntaxe est la suivante : =E6+H6. Ensuite, il convient de répliquer la poignée du résultat jusqu'en ligne 12.

En L6 pour le premier total des commissions, la syntaxe est la suivante : =G6+J6. Là aussi, il convient de reproduire la logique sur les lignes du dessous jusqu'en cellule L12.

En M6 pour le calcul du salaire brut, la syntaxe est la suivante : =D6+L6. Cette formule doit être reproduite, au même titre que les autres, jusqu'en ligne 12.



Ces résultats ne sont pas parfaitement alignés dans la hauteur de la cellule. Nous pallierons ce défaut à l'issue, une fois que tous les calculs seront tombés.

Impôts et salaires Nets
L'impôt à déduire sur chaque salaire brut est variable. Il dépend des contraintes inscrites dans le panneau de contrôle entre les colonnes E et F. Elles sont plus nombreuses que précédemment. Nous pourrions poser le même raisonnement. Mais cette fois, nous devrions imbriquer trois fonctions Si pour envisager les quatre possibilités. La formation Excel sur les taux de remise variables nous avait appris à envisager une autre solution, plus simple et productive.

Le dernier argument de la fonction RechercheV, s'il est réglé à Vrai, permet de réaliser une recherche approchante. En l'occurrence, il s'agit de trouver le montant duquel le chiffre d'affaires se rapproche le plus pour en déduire le taux d'imposition. Cependant la RechercheV ne fonctionne que si l'élément à trouver se situe en premier colonne du tableau. Or dans ce tableau de recherche, les seuils des chiffres d'affaires sont placés en seconde colonne. C'est pourquoi, nous devons exploiter les fonctions Index et Equiv imbriquées, selon le même raisonnement, comme nous l'avait appris la formation Excel sur les extractions approximatives.

=Index(Tableau_de_recherche ;indice_de_ligne ; indice_de_colonne)
=Equiv(Valeur_cherchée ; Colonne_de_recherche ; Mode_de_recherche)

Mais comme la recherche approximative consiste à caler l'extraction à partir de la valeur approchante directement inférieure, nous devons réajuster les contraintes du panneau de contrôle. Elles sont en effet prévues pour solutionner le problème sur la base des fonctions Si imbriquées. Si la démarche que nous proposons ne vous convient pas, il suffit de réaliser l'imbrication des fonctions conditionnelles comme suit :

=SI(M6<$F$17;$E$17;SI(M6<$F$18;$E$18;SI(M6<$F$19;$E$19;$E$20)))

Mais sachez que les extractions approximatives sont plus efficaces et satisfaisantes.

• Sélectionner la plage de cellules E18:E20,

Il s'agit des taux d'imposition, excepté le premier. Celui-ci se déduira naturellement.

• Couper ces valeurs à l'aide du raccourci clavier CTRL + X,
• Puis, sélectionner la cellule du dessus, soit E17,
• Coller ces valeurs avec le raccourci clavier CTRL + V,

Avec ce décalage opéré, nous sommes désormais en cohérence pour produire les extractions approximatives. Par exemple, la valeur inférieure la plus proche d'un salaire à 1600 Euros est le seuil de 1500 Euros auquel correspond bien un taux d'imposition de 10%.

• Dès lors, sélectionner le premier impôt à calculer, soit la cellule N6,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit Index(,
• Sélectionner le tableau de recherche, soit la plage E17:F20,
• Enfoncer la touche F4 du clavier pour la figer,
• Taper un point-virgule (;) pour passer dans l'argument de la ligne de la valeur à retourner,

Cette ligne n'est pas connue. Elle dépend de la correspondance du chiffre d'affaires avec le seuil du panneau de contrôle. Et c'est la fonction Equiv qui permet de retourner le numéro de ligne d'une valeur cherchée approximativement. Cette fonction doit donc être imbriquée.

• Taper la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner la valeur cherchée, soit la cellule M6 du chiffre d'affaires,

Une fois encore, si la formule déborde, il vous suffit de taper ses coordonnées au clavier.

• Taper un point-virgule (;) pour passer dans la colonne de recherche,
• Désigner les cellules des seuils, soit la plage F17:F20,
• Enfoncer la touche F4 du clavier pour la figer,
• Taper un point-virgule (;) pour passer dans le dernier argument,

Comme vous le remarquez, une info-bulle indique quelles sont les valeurs possibles pour cet argument. Avec le chiffre 1, nous réalisons une recherche approximative qui consiste à trouver la correspondance inférieure la plus proche.

• Taper le chiffre 1 et fermer la parenthèse de la fonction Equiv, soit : 1),
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,
• Saisir le chiffre 1 pour désigner les taux d'imposition situés dans la première rangée,
• Fermer la parenthèse de la fonction Index,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Le premier résultat tombe et il ne semble pas cohérent. Une imposition nulle ne devrait pas être associée à ce salaire. Mais comme l'indique le symbole de l'Euro qui l'accompagne, cette cellule porte le format monétaire.

• Dans la section Nombre du ruban Accueil, cliquer sur le bouton Style de pourcentage,

Le résultat est cette fois tout autre. Il affiche désormais 15%. En effet et ne l'oublions pas, 15% équivaut à 0,15. Cette valeur était bien inscrite dans la cellule, mais pour des raisons de formatage, les décimales n'étaient tout simplement pas affichées.

• Tirer la poignée du résultat sur les cellules du dessous, jusqu'en ligne 12,

Tous les résultats semblent cohérents. Par exemple, dès qu'un chiffre d'affaires dépasse 3 000 Euros, il est automatiquement imposé sur la tranche supérieure, définie à 15%. Mais une erreur se glisse au beau milieu de la liste. Ce message #N/A est une information communiquée par la fonction index en retour. Il signifie Not Availiable, soit Non disponible. En d'autres termes, aucune correspondance n'a été trouvée avec la valeur cherchée. Et c'est bien le cas. En dessous de 1500 Euros, aucun seuil n'est déclaré dans le panneau de contrôle.

L'astuce consiste à imbriquer cette formule de recherche dans une fonction de gestion d'erreur. Elle se nomme SiErreur. En premier argument, elle tente le calcul. En second, elle réalise une action en cas d'erreur.

• Sélectionner de nouveau le premier résultat, soit la cellule N6,
• Dans la barre de formule, imbriquer le calcul dans la fonction SiErreur, comme suit :

=SiErreur(INDEX($E$17:$F$20;EQUIV(M6;$F$17:$F$20;1);1);0)

Dans le cas d'une anomalie détectée, nous demandons à la fonction d'inscrire le chiffre 0 en lieu et place, soit le taux d'imposition nul pour les petits salaires.

• Valider la modification par le raccourci clavier CTRL + Entrée,
• Puis, répliquer la formule jusqu'en ligne 12 à l'aide de la poignée,

L'erreur disparaît. L'anomalie est corrigée.

Désormais, le calcul du salaire net se déduit en toute simplicité. Par exemple, sur un salaire brut imposé à 15%, le salaire net est de 85% du brut, soit Brut*(1-15%).

• Sélectionner le premier salaire net à trouver, soit la cellule O6,
• Taper le symbole égal (=) pour initier le calcul,
• Sélectionner le salaire brut correspondant, soit la cellule M6,
• Taper le symbole de l'étoile (*) du pavé numérique pour déclencher la multiplication,
• Ouvrir la parenthèse pour la factorisation du calcul,
• Taper le chiffre 1 suivi du symbole moins, soit 1-,
• Désigner la cellule correspondante du taux d'imposition, soit N6,

Encore une fois, il est conseillé de saisir ses coordonnées.

• Fermer la parenthèse et valider la formule par le raccourci clavier CTRL + Entrée,
• Reproduire la formule jusqu'en ligne 12 avec la poignée du résultat,

Comme vous le constatez, ces cellules étaient préformatées en couleur et en Euro. Les résultats sont tous cohérents. Les salaires nets sont inférieurs aux bruts, en adéquation avec les taux d'imposition respectifs appliqués.

Tous les totaux de la ligne 13 peuvent être obtenus facilement par la somme automatique, même si certains d'entre eux ne sont pas très pertinents à dévoiler.

• Sélectionner toutes les valeurs à sommer ainsi que les résultats à trouver, soit la plage D6:013,
• Puis, à droite du ruban Accueil, cliquer sur le bouton Somme automatique,

Cette méthode particulièrement efficace livre tous les résultats en un clic. Il convient de formater le total de la colonne Impôt en pourcentage. Et comme nous le disions, entre les colonnes K et O, les valeurs doivent être remontées dans la hauteur de leur cellule. Pour cela, vous devez les sélectionner et cliquer sur le bouton Aligner au centre dans la section Alignement du ruban Accueil.