L’article suivant fait suite aux articles : Défi Excel #1:Qui fournira la meilleure réponse? , Défi Excel #2: Qui fournira la meilleure réponse? et Défi Excel #3: Qui fournira la meilleure réponse? Il s’agit de questions d’anciens examens de ModelOff. Je vous mets maintenant au défi de fournir la meilleure réponse possible. Pour ma part, je vous propose une solution avec la fonction Excel ARRONDI.SUP.
Problématique à résoudre dans Excel
Dans l’exemple ci-dessous, vous devez partir de la zone de données suivante :
Défi à résoudre dans Excel
Vous avez donc 5 scénarios, dans la section en jaune. Le “first flag” correspond aux dates de fins de périodes alors que la “duration” correspond au nombre d’années avant la prochaine fin de période. Dans une période, un “flag” est levé. Parallèlement, dans le tableau à droite de la section en jaune, vous avez également des dates de fins de périodes. Le défi est de rédiger une formule qui permettra de révéler le nombre de “flags” levés durant cette période ajustée.
Le défi original, dans l’examen de ModelOff, était de trouver une formule qui à la fois fonctionne (évidemment!) et à la fois est la plus courte possible (plus petit nombre de caractères, sans utiliser de champs nommés). De plus, le temps alloué pour cette question était de 10 minutes. Cela dit, la réponse, en termes de résultat, est déjà connue et il est donc possible de valider que la formule fonctionne.
Solution possible avec la fonction Excel ARRONDI.SUP
À regarder seulement après avoir fourni votre propre réponse 😉
Comme vous le savez, dans Excel, il existe une panoplie de formules pour arriver à un résultat identique. Ci-bas, je vous ai fourni une réponse, sachant qu’il ne s’agit pas de la réponse comportant le moins de caractères. À vous maintenant de proposer de meilleures solutions!
=ARRONDI.SUP(MAX(E$5-$B7+1,0)/365/$C7,0)
Note: La fonction Roundup se traduit par Arrondi.sup en français.
Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière. |
|---|
Vos solutions
Svp, utilisez la zone de commentaires ci-dessous pour proposer vos propres solutions à cette problématique et laissez-nous savoir combien de temps vous avez pris pour résoudre le tout.
À vous de jouer!!!
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour une introduction aux principes de base et aux meilleures pratiques d’affaires en modélisation financière dans Excel et vous familiariser avec les fonctions et les fonctionnalités qui permettent de faire des prévisions financières, des analyses de sensibilité et des analyses de scénarios, suivez notre formation Excel – Modélisation financière niveau 1.
Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Modélisation financière niveau 1 :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Merci !
J’étais bloqué avec ma première piste, mais en voyant la réponse, je me rends compte que j’étais proche
Mon autre solution, moins efficiente, était :
=ARRONDI.INF(E$5/$B7+NB.SI($D7:D7;”>0″)/(4*$C7);0)
oups j’étais pas près d’y arriver
Dans ce cas, j’ai fourni une longue réponse:
=IF(E$5=EOMONTH($B7,$C7*12),1+OFFSET(E7,,-$C7*4),1))
La formule suivante serait convenable:
=IF(E$5-$B7<0,0,IF(E$5-$B7=0,IF(E$5-$B7<=365.25*$C7,1,),)
Encore une fois nous avons un autre biais pour les longues périodes comportants deux années bissextiles et plus:
Exemple: Début=31-déc-2011, Durée=5.00, Fin=31-déc-2016
En reprenant l'énoncé, on déduit que la durée est exprimée en quart: 0.5 année veut dire 6 mois. Si la date de départ est le 30 juin, 6 mois plus tard on veut avoir le 31 décembre pas le 30 décembre!!!
Pour cette raison, il serait plus souhaitable d'utiliser les fonctions de date. Aussi, on pourrait utiliser les valeurs TRUE/FALSE qui serait plus utile.
En conclusion, la formule suivante tient compte des années bissextiles et tient aussi compte des jours de fin de mois.
=AND($B7<=E$5,E$5<=DATE(YEAR($B7),MONTH($B7)+$C7*12+1,0))
Cette formule vérifie si la date de référence (E5) est comprise entre la date de début et la date de fin de la période!
Pour répondre rapidement à la question initiale, la première formule serait la meilleure. La dernière formule est plus fiable et précise!
François
J’ai toujours du mal avec le RoundUp alors
=SI(E$5-$B7<0;0;ENT((E$5-$B7)/365/$C7)+1)