Répartition CA par année / Formules comparaison sur dates
Bonjour à tous,
Je dois créer un fichier qui réparti un chiffres d'affaires d'une affaire sur plusieurs années en fonction de la date de démarrage de cette affaire (cf.fichier joint)
- Colonne A = Chiffres d'affaires totaux des affaires
- Colonne B = Durée de l'affaire en mois
- Colonne C = Formule de calcul pour 1 année pleine
- Colonne D = Date de démarrage de l'affaire
- Colonne E, F, G, H, I, J = Répartition du CA par année
- Colonne E (CA 2015) =500.000 € (car on commence en Juin 2015 (cf.colonneB2)
- Colonne E (CA 2016) = 1.000.000€
- Colonne E (CA 2017) =1.000.000€
- Colonne E (CA 2018) =1.000.000€
- Colonne E (CA 2019) =1.000.000€
- Colonne E (CA 2020) =500.000€ (car on finit en Juin 2020 (cf.colonneB2)
Bien cordialement;
Will_Zero
Bonjour
Formule en E2 à incrémenter sur tout le tableau
=(($C2*($B2/12))/SOMMEPROD(N(LIGNE(INDIRECT($D2&":"&MOIS.DECALER($D2;$B2)))>1)))*SOMMEPROD((LIGNE(INDIRECT($D2&":"&MOIS.DECALER($D2;$B2)))>=DATE(E$1;1;1))*(LIGNE(INDIRECT($D2&":"&MOIS.DECALER($D2;$B2)))<=DATE(E$1;12;31)))Cordialement
Bonjour Amadeus,
Merci pour ton retour mais ta formule est erronée !
Sur la ligne 2 la CA global sur 60 mois est de 5.000.000 € il faut diviser ce montant par 60 et en fonction de la date de démarrage, le répartir par année.
Donc comme 60 mois correspond à 5 ans, une répartition sur sur 2015, 2016, 2017, 2018, 2019, 2020 (stop en juin 2020)
Donc les CA sur année complète doit être de 1.000.000€ et les 1/2 année i.e 2015 et 2020 de 500.000€ pour obtenir le CA global (contrôle de 5.000.000€)
Bien cordialement,
Will_Zero
Bonjour
Merci pour ton retour mais ta formule est erronée !
Pas erronée. Le calcul est affiné au nombre de jours sur la période au lieu d'être sur une moyenne mensuelle uniforme sur la période.
Cordialement
Bonjour à tous,
Je reviens finalement vers vous concernant la formule de répartition des montants par année.
Elle fonctionne bien si la durée est supérieure à 12 mois; en revanche pour les durées inférieures à 12 mois, la formule ne fait pas la répartition correctement, après plusieurs tentatives pour corriger ce problème je n'y arrive toujours pas.
Pourriez-vous me donner un petit coup de pouce.
Je remets le fichier pour info.
Merci de votre aide
A bientot,
will_zero
Bonjour
J'ai ouvert le fichier à nouveau et je ne vois pas d'anomalie.
Peux-tu préciser quelle cellule est fausse?
Cordialement
Bonjour à tous,
Amadeus, je te joins le fichier modifié de nouveau; tu peux voir que le CA Total est de 5.000.000€ et avec un démarrage en 01/06/15 et quand j'indique une durée de 3 mois, je devrais avoir l'intégralité du CA en 2015, hors là je n'ai que 1.250.000€ en 2015 et plus rien après. Il n'est pas forcément nécessaire que j'ai le CA précis; un montant uniforme tous les ans me va bien aussi.
Dans le nouveau fichier que je joins, j'ai une autre difficulté, j'ai dans un onglet la liste des affaires avec les montants date de début et fin, ...et l'agence responsable de l'affaire;en plus de cela j'ai un statut "perdu", "remporté" ou "En cours". Je dois ensuite faire la répartition par agence dans les onglet "Répartition Agence 1","Répartition Agence 2" et "Répartition Agence 3"; le but ici est de voir l'impact de chaque agence en terme de chiffres d'affaires sur plusieurs années en fonction des affaires "remporté".
J'ai essayé avec la formule RECHERCHEV() mais je n'y arrive pas, il faudrait que je puisse avoir une fonction RECHERCHEV() imbriquée mais je bloque.
Je suis OnLine si besoin d'autres info,
Merci de votre aide,
Will_Zero
Bonjour
quand j'indique une durée de 3 mois, je devrais avoir l'intégralité du CA en 2015, hors là je n'ai que 1.250.000€ en 2015 et plus rien après
Ton premier fichier fait état du CA pour une année et donc, en toute logique si l'année représente 5000000€, 3 mois représentent 1250000€.
Ton second fichier fait état du CA total. Juste une nuance d'interprétation.
Je regarde dès que...
Sur ton premier fichier, sauf erreur, cela devrait donner..
Cordialement