PERT – Date au plus tard

Dans les deux derniers articles, nous avons vu comment calculer la date au plus tôt de chaque tâche dans un projet. Nous verrons aujourd’hui comment calculer la date au plus tard, c’est-à-dire la dernière date à laquelle une tâche peut démarrer sans retarder l’ensemble du projet.

Pour la calculer, nous avons mis dans les colonnes L à O les noms des successeurs immédiats de chaque tâche. Là, je dois avouer que j’ai triché un peu…

En effet, la formule de L2 est déjà assez gratinée :

=si(estna(equiv(A2;D2:$D$12;0));si(estna(equiv (A2;F2:$F$12;0));si(estna(equiv
(A2;G2:$G$12;0));"";equiv(A2;G2:$G$12;0)+ligne()-2);equiv(A2;F2:$F$12;0)+ligne()-2);
equiv(A2;D2:$D$12;0)+ligne()-2)

J’ai donc triché pour les colonnes M à O en entrant les valeurs à la main :(

La formule de P3, reproduite jusque P11, est :

=min(index(P:P;L3+1);index(P:P;M3+1);index(P:P;N3+1);index(P:P;O3+1))-C3

Si vous trouvez des formules supportables pour le bloc L:O, dites-le moi…

Les tâches dont la date au plus tôt et la date au plus tard sont égales forment le chemin critique : si l’une quelconque des tâches du chemin critique prend du retard, c’est l’ensemble du projet qui prend du retard !

Nous avons défini un format conditionnel pour identifier le chemin critique avec en A2 et B2 la formule =$J2=$P2, cette formule ayant été ensuite recopiée jusqu’en bas.

La méthode PERT – Graphique

Reprenons le modèle développé il y a quatre jours pour en faire un graphique. Nous sélectionnons à cet effet le bloc B2:B11 puis – en maintenant la touche [Ctrl] enfoncée, le bloc J2:K11. Nous insérons alors un graphe en barres indépendantes et nous obtenons le premier graphe ci-dessous, après avoir détruit la légende.

Faisons à présent un clic droit sur une barre de la série des dates au plus tôt et activons la commande « Mettre en forme une série de données » puis, dans le premier onglet – Options des séries – mettons à 50% le taux de superposition et réglons à 69% la largeur de l’intervalle. Nous voyons en premier plan la série la plus courte et, en arrière-plan, la série la plus longue.

Sélectionnez la série la plus courte, celle des dates au plus tôt et donnez-lui une couleur blanche unie. Si cette série est « devant » l’autre série, tout va bien ; sinon, modifiez l’ordre des séries pour qu’elle passe devant.

Il ne vous reste plus qu’à mettre à 50% le taux de superposition et à mettre la série qui dépasse en bleu pour obtenir le résultat du graphe ci-dessus…

… à une petite différence près : vous constaterez que la série en blanc masque le quadrillage vertical. Problème que nous avons résolu en insérant des lignes verticales pour combler les trous ! Un autre choix esthétique serait de donner, au lieu d’un fond blanc, une transparence de 10% à la série des dates au plus tôt, ce qui éviterait l’insertion de lignes de quadrillage…

La méthode PERT – Initiation

La méthode PERT – pour Program Evaluation and Review Technique – est réputée pour la modélisation d’un projet contenant plusieurs étapes. Dans l’exemple ci-dessous, il y a 11 étapes depuis l’organisation initiale jusqu’à la fin d’un projet.

On connaît la durée prévue de chaque étape. Il y a en outre (cf. colonnes D, F et H) des contraintes d’ordre : ainsi, l’assemblage (tâche g) ne peut démarrer que lorsque les tâches b et d sont terminées.

Le but d’un tel modèle est de calculer (en colonne J) la date au plus tôt à laquelle chaque tâche peut démarrer et la date de fin prévue (en colonne K) pour chaque tâche. De tout cela, on peut déduire – en K12 – la date prévue pour la fin du projet.

Voici les principales formules originales utilisées dans ce modèle…

E2 (recopiée vers le bas, puis en colonne G et I) : =si(D2="";"";equiv(D2;List;0))

J2 : =max(si(E2="";0;index(P_tôt;E2)+ index (Durée;E2)); si (G2="";0; index (P_tôt;G2)+ index (Durée;G2)); si (I2="";0; index (P_tôt;I2)+ index (Durée;I2)))

K2 : =J2+C2

…où List est le nom attribué au bloc A2:A12, et où Durée et P_tôt correspondent respectivement aux blocs C2:C12 et J2:J12.

Nous verrons dans le prochain article comment illustrer ce planning par un graphique.

Un graphe en waterfall

Les graphes en waterfall – aussi appelés bridges – sont régulièrement utilisés en finance. Ils servent à mettre en relief l’impact d’un certain nombre de recettes et de dépenses sur une situation donnée, les dépenses étant représentées en rouge et les recettes en vert.

Dans l’exemple ci-dessous, nous avons l’EBITDA 2009 et les divers éléments qui permettent d’en tirer l’EBITDA 2010. Les valeurs d’origine sont en colonne B.

Pour construire le graphe, il faut créer les colonnes C à F qui respectivement correspondront aux barres bleue, invisible, verte et rouge.

Voici les formules originales…

Formule de D3 : =min(somme($B$2:B2);somme($B$2:B3))

Formule de E3 : =max(B3;0)

Formule de F3 : =-min(B3;0)

Tout cela prend du temps à construire, même quand vous savez exactement ce que vous devez faire. C’est pourquoi nous avons créé – dans le cadre de la société Finance 3.1 – un add-in (complément) Excel qui permet, entres autres fonctionnalités, de créer un waterfall en un seul clic.

Pour en savoir plus sur cet add-in, lisez les articles « UpSlide pour Excel » des 6 et 10 mai 2011,

Des contrôles de valeurs en série

Nous disposons en colonne A d’une série de valeurs et, en colonne B, d’une série de seuils auxquels nous voulons comparer ces valeurs. L’objectif est d’afficher en colonne C le résultat de ces tests. Nous constatons ainsi que « 12 > 90 » est bien FAUX (en C2) alors que 82 > 61 est VRAI (en C3)…

La formule de C2, reproduite ensuite vers le bas, est assez simple :
=si(somme.si(A2;B2)>0;"Oui";"Non")

Remarque 1 – Que le test en B2 affiche « > 90 » ou « >90 » revient au même, l’espace n’ayant aucune importance. On, pourrait même en mettre plusieurs sans que cela ne pose le moindre problème.

Remarque 2 – Une formule un peu plus légère peut aussi être utilisée : =si(somme.si(A2;B2);"Oui";"Non"). Notez que je trouve souvent, quand j’audite et améliore des modèles chez mes clients, des cas de ce genre ou des tests peuvent être simplifiés…

Si vous voulez en savoir plus sur la fonction somme.si() qui, bien que peu connue, est assez intéressante, je vous conseille de lire mes articles « Les fonctions Somme.Si() et Nb.Si() » du 25 février 2006 et « La fonction Somme.Si() : des trucs en plus » du 2 mars 2006.

Nombre de valeurs différentes

Dans l’exemple ci-dessous, nous souhaitons savoir combien il y a de valeurs différentes dans le bloc A1:A15.

Nous vous proposons deux solutions différentes, à l’aide des formules matricielles suivantes, donc validées avec [Ctrl]-[Maj]-[Entrée] :

Formule de D2 : =somme(1/nb.si(A1:A15;A1:A15))

Formule de D3 : =somme(1*(ligne(A1:A15)=equiv(A1:A15;A:A;0)))

Certes, la seconde formule est plus longue, mais je vous la montre aussi pour illustrer le fait – une fois de plus ! – qu’il y a souvent plusieurs solutions possibles à un problème délicat.

Dans le cas de D2, la formule nb.si(A1:A15;A1:A15) engendre le vecteur suivant : {1;3;2;2;1;1;3;2;3;1;2;1;2;2;1}. A chaque position, on a le nombre de fois où la valeur est présente dans le vecteur. Ainsi, quand la valeur est présente trois fois, on va additionner trois fois 1/3, pour un résultat de 1. Le total est donc le nombre de valeurs différentes.

Dans le cas de D3, la formule ligne(A1:A15)=equiv(A1:A15;A:A;0) engendre le vecteur {VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;FAUX;VRAI;FAUX;VRAI;FAUX;VRAI;FAUX; FAUX;VRAI}, où l’on obtient VRAI quand la valeur concernée est la première occurrence de cette valeur dans la colonne. Quand on multiplie par 1, on obtient donc le nombre de valeurs « originales ».

En conclusion, voilà pour vos devoirs de vacances un excellent petit exercice d’application des formules matricielles !

Mesurez-vous à l’Everest !

Pour progresser en Excel, on ne manque pas de livres, de tutoriaux de toutes sortes, de vidéos d’apprentissage, et de blogs, celui-ci n’en étant qu’un parmi de nombreux autres en toutes langues…

Sean Duffy a créé à cet égard un produit innovant – Excel Everest –, un fichier Excel qui poursuit un objectif multiple : vous aider à progresser dans votre utilisation d’Excel, quel que soit votre niveau de départ, vous permettre de vous évaluer vous-même à tout moment selon la façon dont vous avez réussi les exercices, et vous distraire tout en vous apprenant plein de choses.

Tout cela se fait à l’aide d’un classeur Excel unique faisant un peu plus de 5 Mo, comportant 45 onglets, équipé de 339 boutons de navigation, et incorporant 87 vidéos.

Nous voyons ci-dessus une copie d’une partie de l’écran d’accueil, avec une liste de thèmes de difficultés variées, avec pour chacun un bouton carré qui vous mène directement à l’onglet voulu.

L’ensemble du cours devrait occuper un utilisateur débutant durant 20 à 30 heures, mais il peut bien entendu progresser au rythme qui lui convient.

Ce produit est vendu au prix tout à fait abordable de $ 34,95.

Il ne vous reste plus qu’à aller voir : http://www.exceleverest.com/