Valeur cible Fermé

Question

Bonjour

je calcule les salaires de mon entreprise qui se compose comme suite 

Salaire de base - Retenu sécurité sociale 9% +Prime de panier -Impot sur le revenue globlal (c'est une formule sur la base du salaire de base - la retenu) se qui va me détermine le salaire net de l'employe.

losque nous avons des augmentations de salaire en net , j'utilise la fonction analyse de scenarios valeur cible pour augmenter le salaire de base avec la cible augmenter le montant net 

lorsque je traite un ou deux salaire c'est bon , mais actuellement j'ai un nombre important d'augmentation a applique plus de 250.

y aurait t'il le moyen de mettre en place une formule qui fonctionne comme valeur cible et que je puisse selectionne une valeur dans une cellule.

je vous remercie par avance pour votre aide.

Raymond PENTIER · Answer

Bonjour.

Non, je ne connais pas de formule qui fasse ça.
Je ne suis même pas certain qu'on puisse le réaliser avec une macro.
Ce dont je suis certain, c'est que les augmentations de salaire ne se font jamais sur le net à payer, en France !
Alors utilise ton analyse valeur cible sur le plus petit et le plus gros salaires, pour avoir une indication de départ, et fais comme toutes les entreprises : pourcentage d'augmentation ou somme forfaitaire, sur le salaire de base. 
 
C'est bien, la retraite ! Surtout aux Antilles ... :-) 
Raymond (INSA, AFPA, CF/R)

JvDo · Answer

Bonjour à tous,

Mets un exemple de ton fichier à disposition (sur cjoint.com par exemple).
Anonymise le bien entendu.
Il faut connaître ta fonction "calcul impôts" (qui, théoriquement, est affine par morceaux) pour identifier les éventuels sauts de tranche.
À partir de là, il devrait être facile de calculer l'augmentation du salaire de base qui donnera celle attendue en net.

Cordialement

malek1982-2015 · Answer

Bonjour JvDo

Merci pour ton assistance.

Voici le lien pour le fichier:

https://www.cjoint.com/c/GHDkI6YmFwm

Salutations 
Malek

JvDo · Answer

Bonjour,

5 fois que j'essaie de poster un message et ça ne passe pas .......

6ème essai

dans la tranche [28750 - 30000[ de IRG, il devrait y avvoir +2250 au lieu de + 22500.

Cordialement

malek1982-2015 · Answer

Bonjour

tu as raison j'ai corriger ma formule,  je te remercie.

malek1982-2015 · Answer

bonjour JvDo

tu as du nouveau pour mon problem

JvDo · Answer

Bonjour,

tu es bien impatient, malek1982-2015.
ça ne se fait pas tout seul et en plus tu calcules IRG sur des arrondi inf à la dizaine.
ça génère une complexité supplémentaire si on veut un résultat précis.

J'ai ajouté des arrondis à la deuxième décimale à droite à gauche.
J'ai simplifié le calcul de l'IRG : =RECHERCHEV(J2;$S$2:$U$7;2;VRAI)*(ARRONDI.INF((J2/O2);-1)-RECHERCHEV(J2;$S$2:$U$7;1;VRAI))+RECHERCHEV(J2;$S$2:$U$7;3;VRAI)
Tu noteras le paramètre -1 de arrondi.inf() qui remplace tes /10 et *10.

Pour simplifier l'IRG, j'ai créé une table des impôts en S2:U7


Pour le calcul du salaire de base à partir du salaire net augmenté (qui se trouve en colonne N) voilà la formule en P2 :
=ARRONDI((N2+RECHERCHEV(ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2);$S$2:$U$7;2;VRAI)*(ARRONDI.INF((ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2)/O2);-1)-RECHERCHEV(ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2);$S$2:$U$7;1;VRAI))+RECHERCHEV(ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2);$S$2:$U$7;3;VRAI)-G2-F2)/0,91;2)
Elle est matricielle. Il faut donc la valider par CTRL+MAJ+ENTER

Tous les EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1) cherchent l'indice de ligne dans la table impôts.
En les nommant on simplifie visuellement la formule.

Cordialement

malek1982-2015 · Answer

Bonjour JvDo

Désole pour mon impatience, mais je me voyais traiter employé par employé.

Tu es trop fort , je te remercie.

Peux-tu stp me vérifier le calcul de l'irg car je ne comprends pas comment tu l’as mis en place car le barème de calcule est comme suit :

Montant n’excédant pas 10 000.00  le taux est de 0%
Montant de 10 000.01 à 30 000.00 le taux est de 20%
Montant de 30 000.01 à 120 000.00 le taux est de 30%
Montant supérieur a 120 000.00 le taux est de 35%

Exemple pour une base imposable de 38 000.00 le calcule sera de 
Fraction                              Différence        Taux                 IRG
De 0 à 10 000.00                   10 000.00            0%                  0 Da
De 10 000.01  à 30 000.00     30 000.00          20%          4 000.00 Da
De 30 000.01  à 38 000.00       8 000.00          30%          2 400.00 Da
                                                   Total cumule                   6 400.00 Da
                                                    Abattement                     1 500.00 Da
				      Retenu IRG                   4 900.00 Da
L’abattement se calcule comme suite SI(Base imposable*40%<1000;1000;SI(Base imposable *40%>1500;1500; Base imposable *40%))


Je vais un peu abuser mais dans ta formule je voudrais incorporer le ratio de présence pour calculer l'IRG il est en colonne P

Demain je vais être beaucoup plus détendu au boulot grâce a toi merci

patrick · Answer

Bonjour Malek,

Fichier Excel 2007 : https://mon-partage.fr/f/N75esyug/

Il y a 2 feuilles : "(38)" et "(39)" ; la seule différence entre elles
est que sur "(38)", les colonnes K à S sont masquées ; sur la
feuille "(39)" où les colonnes K à S sont affichées, regarde bien
toutes les formules de la ligne 2 (qui ont été recopiées vers
le bas jusqu'en ligne 7) ; exemple pour la ligne 2 :

En E2 : "Salaire de Base" ; si tu n'as rien saisi, tu pourras voir
que la plupart des autres cellules à droite sont vides.

En H2 : "Salaire du Poste" = reprise du "Salaire de Base" ;
tu avais mis =+E2 alors que =E2 suffit !

------------------------------------

En I2 : "Retenue S. Sle" = 9% du "Salaire de Poste", arrondi
à 2 chiffres après la virgule : =ARRONDI(H2*9%;2)

En J2 : "Salaire Imposable" : comme H2 est juste la reprise de E2,
c'est bien plus simple de faire : =SOMME(E2:G2)-I2 ; et complété
ainsi : =SI(E2="";0;SOMME(E2:G2)-I2)

------------------------------------

En X2 : % IRG (que tu saisis)

En K2 : "Base Imposable" : si X2 = 0, pour éviter une erreur de
division par 0, on fait comme si tu avais saisi 1 => on utilise J2
car J2 / 1 = J2 ; si X2 est autre que 0 (et je ne pense pas que
tu vas saisir un nombre négatif), on utilise J2 / X2 ; ce qui est
utilisé (J2, ou J2 / X2) est arrondi à la dizaine inférieure.

------------------------------------

L2 à T2 : calcul de l'IRG retenu (abattement compris).

Il n'y a pas de colonne "B.0" (Base 0 %) car le taux étant nul,
c'est inutile : l'impôt de la 1ère tranche de 0 à 10 000 inclus
est toujours 0 Da.

L2 : Base 20% ; M2 : impôt à 20% correspondant
N2 : Base 30% ; O2 : impôt à 30% correspondant
P2 : Base 35% ; Q2 : impôt à 35% correspondant

En R2 : "Total cumulé" : =M2+O2+Q2

En S2 : "Abattement" : calculé selon tes indications

En T2 : "Retenu IRG" : =R2-S2

------------------------------------

En U2 : "Salaire Net" =
"Salaire Imposable" - "Retenu IRG" : =J2-T2

En V2 : "Augmentation" (que tu saisis)

En W2 : "Nouveau Salaire" : =SI(E2="";0;U2+V2)

------------------------------------

Tu auras noté que les valeurs 0 ne s'affichent pas ; c'est grâce
à ce format personnalisé : # ##0,00;-# ##0,00;

Utilise la feuille que tu préfères et supprime l'autre.
Si besoin, tu peux me demander une adaptation.

Merci de me dire si ça te convient.

Cordialement

JvDo · Answer

Bonjour à tous, Pour l'intégration de l'abattement dans la formule que j'avais écrite ....... j'ai préféré abandonner. J'ai donc écrit une petite fonction qui calcule par dichotomie le salaire de base à partir du salaire net souhaité. C'est beaucoup moins compliqué qu'une formule. Il aurait peut-être été aussi simple de faire dès le départ une macro à base de valeur cible. Si quelqu'un en a envie..... Voilà un fichier ( https://www.cjoint.com/c/GHEeuYgybD6 )avec une zone de contrôle des résultats de la function(). Elle a 4 arguments et il faut l'appeler comme ça :=Sal_base(P2;F2;G2;Q2) La fonction en elle-même : Option Explicit Const TxSS = 0.09 'taux sécurité sociale Const Tranche1 = 10000 'borne 1ère tranche Const Tranche2 = 30000 'borne 2ème tranche Const Tranche3 = 120000 'borne 3ème tranche Const Tx1 = 0.2 'taux 1ère tranche Const Tx2 = 0.3 'taux 2ème tranche Const Tx3 = 0.35 'taux 3ème tranche Const AbtMin = 1000 'Abattement minimum Const AbtMax = 1500 'Abattement maximum Const AbtTx = 0.4 'taux de calcul de l'abattement Function Sal_base(sal_net_cible As Currency, Panier As Currency, Transport As Currency, Pirg As Double) 'calcule le salaire de base à partir d'un salaire net cible, de primes de panier et de transport 'Assiette et Pirg sont là pour calculer IRG à la dizaine inférieure 'méthode dichotomique limitée à 50 boucles pour trouver une solution 'la borne inférieure est initialisée à sal_net_cible - Panier - Transport 'la borne supérieure est initialisée à (1 + Tx3) * sal_net_cible Dim i As Integer, bInf As Currency, bSup As Currency, Base_imposable As Currency Dim Sal_imposable As Currency, IRG As Currency, Abattement As Currency, Sal_dicho As Currency Dim sal_net As Currency, k As Integer If sal_net_cible > 0 And Panier > 0 And Transport > 0 And Pirg > 0 Then sal_net_cible = Round(sal_net_cible, 2) bInf = sal_net_cible - Panier - Transport bSup = (1 + Tx3) * sal_net_cible: Sal_dicho = -1 k = 0 While (Abs(sal_net_cible - sal_net) >= 0.01 And k < 50) k = k + 1 Sal_dicho = Round((bSup + bInf) / 2, 2) Sal_imposable = Round(Sal_dicho * (1 - TxSS) + Panier + Transport, 2) Base_imposable = WorksheetFunction.RoundDown(Sal_imposable / Pirg, -1) IRG = Round(-(Base_imposable > Tranche1) * Tx1 * (Base_imposable - Tranche1) _ - (Base_imposable > Tranche2) * (Tx2 - Tx1) * (Base_imposable - Tranche2) _ - (Base_imposable > Tranche3) * (Tx3 - Tx2) * (Base_imposable - Tranche3), 2) Abattement = WorksheetFunction.Max(AbtMin, WorksheetFunction.Min(AbtMax, Round(Base_imposable * AbtTx, 2))) sal_net = Sal_imposable - IRG + Abattement If sal_net < sal_net_cible Then bInf = Round((bSup + bInf) / 2, 2) Else bSup = Round((bSup + bInf) / 2, 2) Wend If k < 50 Then Sal_base = Round((sal_net_cible + IRG - Abattement - Panier - Transport) / (1 - TxSS), 2) Else Sal_base = [na()] End If Else Sal_base = [na()] End If End Function Cordialement

malek1982-2015 · Answer

Bonjour JvDo

C'est top merci beaucoup, je vais l'utiliser.

Je voudrais bien revenir vers toi pour développer le fichier de traitement des salaire avec plus de variable , car tu fait des miracles 

Je te remercie

eriiic · Answer

Bonjour,

Il aurait peut-être été aussi simple de faire dès le départ une macro à base de valeur cible. Si quelqu'un en a envie.....  
Un peu plus simple oui ;-)
Sub nouveauSalaire()
    Dim c As Range
    For Each c In [E2].Resize(Cells(Rows.Count, 5).End(xlUp).Row - 1)
        If c.Offset(, 9) > 0 Then c.Offset(, 7).GoalSeek Goal:=c.Offset(, 9), ChangingCell:=c
    Next c
End Sub
https://www.cjoint.com/c/GHEkTYEnLuw
eric

Valeur cible

12 réponses

Discussions similaires