Nous avons vu précédemment dans Une difficile conversion de données avec Excel – Un exemple de Modeloff 2014 (1 de 3) comment normaliser une chaîne de caractères afin de faciliter l’extraction des données. Par la suite, dans Une difficile conversion de données avec Excel – Un exemple de Modeloff 2014 (2 de 3), les données ont été extraites.
D’emblée, je veux rappeler que cet exercice est un exemple réel de la première ronde de tests de Modeloff 2014, un concours international en modélisation financière. Et le concours suggérait de ne consacrer que 35 minutes à cette section, soit 35 minutes pour lire et comprendre la question, pour faire les conversions, les extractions et répondre aux 7 questions (la partie analyse que nous couvrons aujourd’hui). Donc, dans une situation de tests, il faut avoir de très bons réflexes de modélisation, rapidement.
Il est également bon de mentionner que de telles conversions, extractions et analyses sont monnaie courante dans la vie du modélisateur financier. En effet, il arrive régulièrement que des données nous soient fournies dans des formats des plus étranges…
Les questions
L’analyse requise dans cette section dépend évidemment des questions auxquelles nous devons répondre, Dans le cadre des présents billets, nous avons un document PDF qui nous donne ces 7 questions de Modeloff. Toutefois, dans le déroulement réel du test, nous ne voyons qu’une question à la fois. Il faut donc défiler toutes les questions et prendre des notes afin de s’assurer de bien structurer nos données et se préparer adéquatement.
Les questions 1 à 3 touchent des consommations moyennes sur des périodes d’une heure. Nous sommes chanceux car toutes les données ne touchent que des périodes d’une heure, donc nul besoin de champs calculés!
La question 4 nous demande la consommation la plus grande sur une période de 4 heures consécutives. Par inspection, on voit bien que les données fournies sont dans un ordre quelconque. Il va donc falloir faire un tri afin de répondre à cette question.
Finalement, les questions 5 à 7 touchent au coût de la consommation énergétique. Il va donc falloir lier les données extraites au tarifs fournis.
La préparation des données
Dans un premier temps, j’ai décidé de reporter mes 3 données de base: le temps de 0 à 23 heures, les dates dans un format approprié et les consommations dans un tableau que j’ai formaté sous forme de table. J’ai tout mis en valeur et j’ai trié par ordre croissant de date et, ensuite, par ordre croissant de l’heure. Le tout afin d’être prêt pour la question 4. Mon tableau de données, à cette étape-ci, ressemble donc à:
La réponse aux questions
Des tableaux croisés dynamiques (TCD) sont créés afin de répondre aux 7 questions. Dans la vraie vie, un seul TCD a été fait et ajusté au fur et à mesure afin de répondre aux questions. On va également constater que notre table de données va prendre de l’expansion puisque nous allons ajouter des colonnes afin de répondre à certaines questions. On voit le bénéfice d’utiliser une table au lieu d’une plage de données, car le TCD va s’adapter à notre expansion de la table, alors que cela est plus difficile si le TCD est lié à une simple plage de données.
Mon fichier final est disponible en téléchargement ci-bas.
Question 1: Consommation moyenne sur toute la période
Cette première question est facile à répondre avec un TCD où seulement la consommation est montré dans les mesures et où la moyenne est choisie.
Question 2: Consommation moyenne en février
Cette question demande de placer les dates en paramètre de lignes et la consommation comme mesure. On utilise encore la moyenne. Afin d’avoir les dates en mois, on utilise la fonctionnalité de grouper des dates et de les présenter en mois. On n’a plus ensuite qu’à regarder le résultat pour le mois de février. Cette fonctionnalité a déjà été présenté dans le billet Excel: Maîtriser les tableaux croisés dynamiques (1 de 5).
Question 3: Le jour de la semaine où la consommation moyenne est la plus élevée
Afin de répondre à cette question, on va jouter une nouvelle colonne à nos résultats afin de récupérer le jour de la semaine. La fonction WEEKDAY (JOURSEM en français) nous fournit le jour de la semaine de 1 à 7, avec 1 = dimanche, 2=lundi et ainsi de suite. On utilise donc le jour de la semaine comme paramètre de lignes et la consommation comme mesure, en utilisant la moyenne. Et on va trouver que dimanche est la journée de plus grande consommation moyenne.
Question 4: La consommation la plus élevée sur une période consécutive de 4 heures
Encore une fois, afin de bien répondre à cette question, j’ai choisi d’ajouter une colonne à mes données. Cette nouvelle colonne est en fait un SUM ( OFFSET()) ou SOMME ( DECALER ()) en français. On choisit en référence du OFFSET la consommation sur la ligne où nous sommes et nous faisons la somme sur une plage de 4 lignes x 1 colonne. Par la suite, on n’a plus qu’à reporter cette colonne comme mesure dans le TCD et de demander le MAX.
Questions 5 à 7: Le coût de la consommation selon les différents tarifs
Pour les questions 5 à 7, il faut multiplier la consommation par le bon tarif. J’ai donc ajouté 3 colonnes dans mes résultats afin de calculer le coût de la consommation en fonction de chacun des tarifs. Pour le tarif annuel, on multiple simplement la consommation par le tarif unique. Pour le tarif mensuel, je fais un VLOOKUP (RECHERCHEV en français) dans la table fournie. J’ai ajouté le numéro du mois à cette table, ce qui me permet de passer le numéro du mois en paramètre du VLOOKUP à l’aide de la fonction MONTH (MOIS en français) appliquée sur la date. Finalement, en ajoutant la plage horaire de 0 à 23 heures à la table des tarifs quotidiens, il devient facile de faire un VLOOKUP du tarif quotidien à partir des résultats.
Ces 3 nouveaux champs sont passés comme mesures dans le TCD et la somme est demandée. Le résultat est alors le coût annuel de la consommation énergétique pour les 3 tarifs. On conclut que le tarif quotidien est le plus avantageux.
Pour votre gouverne, je vous invite à consulter mon fichier final Modeloff Template Energie Final.
Conclusion
En 3 billets de blogue, nous avons couvert 35 minutes de la vie d’un test de Modeloff… Cela devrait vous laisser pensif (ou admiratif, c’est selon!). Évidemment, dans la vraie vie, le sentiment d’urgence n’est pas toujours présent, ce qui permet de trouver des solutions plus permanentes à ce genre de situations où la conversion et la transformation de données sont nécessaires. Et nous sommes des experts en cette matière.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble