■■ Comment une entreprise peut-elle utiliser Solver pour déterminer les projets à entreprendre?

Comment une entreprise peut-elle utiliser Solver pour déterminer les projets à entreprendre ?

La plupart des sociétés souhaitent entreprendre des projets qui apportent la plus grande valeur actuelle nette (VAN), sous réserve de ressources limitées (généralement du capital et de la main-d’œuvre). Imaginons qu’une entreprise de développement de logiciels essaie de déterminer lequel des 20 projets logiciels elle devrait entreprendre. La VAN (en millions de dollars) apportée par chaque projet ainsi que le capital (en millions de dollars) et le nombre de programmeurs nécessaires au cours de chacune des trois prochaines années sont indiqués sur la feuille de calcul Modèle de base dans  , illustré à la figure 1. Par exemple, le projet 2 rapporte 908 millions de dollars. Il nécessite 151 millions de dollars pendant l’année 1, 269 millions de dollars pendant l’année 2 et 248 millions de dollars pendant l’année 3. Le projet 2 nécessite 139 programmeurs au cours de l’année 1, 86 programmeurs au cours de l’année 2 et 83 programmeurs au cours de l’année 3. Les cellules E4: G4 indiquent le capital (en millions de dollars) disponible au cours de chacune des trois années et les cellules H4: J4 indiquent le nombre de programmeurs. disponible. Par exemple, pendant la première année, jusqu’à 2,5 milliards de dollars en capital et 900 programmeurs sont disponibles.

Figure 1: Il s’agit de données que vous utiliserez avec Solver pour déterminer les projets à entreprendre.

L’entreprise doit décider si elle doit entreprendre chaque projet. Supposons que la com-

panie ne peut pas entreprendre une fraction d’un projet logiciel; si 0,5 des ressources nécessaires sont allouées, par exemple, l’entreprise aurait un programme non fonctionnel qui rapporterait 0 $ de revenus!

L’astuce pour modéliser des situations dans lesquelles vous faites ou ne faites pas quelque chose est d’utiliser des cellules à changement binaire. Une cellule de changement binaire est toujours égale à 0 ou 1. Lorsqu’une cellule de changement binaire qui correspond à un projet est égale à 1, vous effectuez le projet. Si une cellule de changement binaire qui correspond à un projet est égale à 0, vous ne faites pas le projet. Vous configurez Solver pour utiliser une plage de cellules changeantes binaires en ajoutant une contrainte; sélectionnez les cellules changeantes que vous souhaitez utiliser, puis choisissez Bin dans la liste de la boîte de dialogue Ajouter une contrainte.

Dans ce contexte, vous êtes prêt à résoudre le problème de sélection de projet logiciel. Comme avec tout Modèle de solveur, vous devez commencer par identifier la cellule cible, les cellules changeantes et les contraintes:

■■ Cellule cible Maximisez la VAN générée par les projets sélectionnés.

■■ Cellules changeantes Recherchez une cellule changeante binaire 0 ou 1 pour chaque projet. Recherchez ces cellules dans la plage A6: A25 (appelée plage doit). Par exemple, un 1 dans la cellule A6 indique que vous entreprenez le projet 1; un 0 dans la cellule A6 indique que vous n’entreprenez pas le projet 1.

■■ Contraintes Vous devez vous assurer que pour chaque année t (t = 1, 2, 3), le capital de l’année t utilisé est inférieur ou égal au capital de l’année t disponible et la main-d’œuvre de l’année t utilisée est inférieure ou égale à l’année t main-d’œuvre disponible.

Comme vous pouvez le voir, la feuille de calcul doit calculer la VAN, le capital utilisé annuellement et les programmeurs utilisés chaque année pour toute sélection de projets. Dans la cellule B2, utilisez la formule SOMMEPROD (doit, VAN) pour calculer la VAN totale générée par les projets sélectionnés. (Le nom de la gamme VAN fait référence au C6: C25 ). Pour chaque projet avec un 1 dans la colonne A, cette formule récupère la VAN du projet et pour chaque projet avec un 0 dans la colonne A, cette formule ne récupère pas la VAN du projet. Par conséquent, vous pouvez calculer la VAN de tous les projets et la cellule cible est linéaire car elle est calculée en sommant les termes qui suivent la forme (cellule changeante) * (constante). De la même manière, calculez le capital utilisé chaque année et le travail utilisé chaque année en copiant la formule SOMMAIRE (doit, E6: E25) de E2 à F2: J2.

Remplissez maintenant la boîte de dialogue Paramètres du solveur, comme illustré à la figure.

FIGURE 2: La boîte de dialogue Paramètres du solveur est configurée pour le modèle de sélection de projet.

L’objectif est de maximiser la VAN des projets sélectionnés (cellule B2). Les cellules changeantes (la plage nommée doit) sont les cellules binaires changeantes pour chaque projet. La contrainte E2: J2 <= E4: J4 garantit que pendant Chaque année, le capital et le travail utilisés sont inférieurs ou égaux au capital et au travail disponibles. Pour ajouter la contrainte qui rend les cellules changeantes binaires, cliquez sur Ajouter dans la boîte de dialogue Paramètres du solveur, puis sélectionnez Bin dans la liste au milieu de la boîte de dialogue Ajouter une contrainte. La boîte de dialogue Ajouter une contrainte doit apparaître comme illustré à la figure ci-dessous.

FIGURE 3 : Utilisez Bin dans la boîte de dialogue Ajouter une contrainte pour configurer le binaire changer les cellules – cellules qui affichent soit un 0 ou 1.

Ce modèle est linéaire car la cellule cible est calculée comme la somme des termes qui ont la forme (cellule changeante) * (constante) et parce que les contraintes d’utilisation des ressources sont calculées en comparant la somme de (cellules changeantes) * (constantes) à une constante. Par conséquent, sélectionnez le moteur Simplex LP.

Une fois la boîte de dialogue Paramètres du solveur remplie, cliquez sur Résoudre et obtenez les résultats indiqués précédemment, dans la figure 32-1. L’entreprise peut obtenir une VAN maximale de 9 293 milliards de dollars (9,293 milliards de dollars) en choisissant les projets 2, 3, 6-10, 14-16, 19 et 20.

Gérer d’autres contraintes

Parfois, les modèles de sélection de projets ont d’autres contraintes. Par exemple, supposons que si vous sélectionnez Projet 3, vous devez également sélectionner le projet 4. Étant donné que la solution optimale actuelle sélectionne le projet 3 mais pas le projet 4, cela vous indique que la solution actuelle ne peut pas rester optimale. Pour résoudre ce problème, ajoutez la contrainte que la cellule de changement binaire pour Project 3 est inférieure ou égale à la cellule de changement binaire pour Project 4.

Vous pouvez trouver cet exemple sur la feuille de calcul If 3 puis 4 dans le fichier Capbudget.xlsx, qui est illustré à la figure 4. La cellule L9 fait référence à la valeur binaire liée au projet 3 et la cellule L12 à la valeur binaire liée au projet 4. En ajoutant la contrainte L9 <= L12, si vous choisissez le projet 3, L9 est égal à 1, et cette contrainte force L12 (le Projet 4 binaire) égal à 1. Cette contrainte doit également laisser la valeur binaire dans la cellule changeante du projet 4 sans restriction si vous ne sélectionnez pas le projet 3. Si vous ne sélectionnez pas le projet 3, L9 est égal à 0 et la contrainte permet au binaire Project 4 d’être égal à 0 ou 1, ce que vous voulez. La nouvelle solution optimale est illustrée à la figure 4.

Supposons maintenant que vous ne pouvez effectuer que quatre projets parmi les projets 1 à 10. (Voir la feuille de calcul Au plus 4 de P1 à P10, illustrée à la figure 32-5.) Dans la cellule L8, vous calculez la somme des valeurs binaires associées à Projets 1 à 10 avec la formule SOMME (A6: A15). Ajoutez ensuite la contrainte L8 <= L10, ce qui garantit qu’au plus 4 des 10 premiers projets sont sélectionnés. La nouvelle solution optimale est illustrée à la figure 32-5. La VAN a chuté à 9,014 milliards de dollars.

FIGURE 4: Il s’agit de la nouvelle solution optimale pour Si la sélection du projet 3 nécessite que nous fassions le projet 4.

FIGURE 5: Solution optimale lorsque seulement 4 des projets 1 à 10 peuvent être sélectionnés.

Résolution des problèmes de programmation binaires et entiers

Les modèles de solveurs linéaires dans lesquels certaines ou toutes les cellules changeantes doivent être binaires ou un entier sont généralement plus difficile à résoudre que les modèles linéaires dans lesquels toutes les cellules changeantes peuvent être des fractions. Pour cette raison, les analystes sont souvent satisfaits d’une solution presque optimale à un problème de programmation binaire ou entier. Si votre modèle de solveur s’exécute pendant une longue période, vous souhaiterez peut-être envisager de régler le paramètre Optimality entier (anciennement appelé Tolérance) dans la boîte de dialogue Options du solveur. (Voir figure 6.) Par exemple, un paramètre de tolérance de 0,5 signifie que le solveur s’arrêtera la première fois qu’il trouvera solution qui se trouve à 0,5 pour cent de la valeur théorique optimale de la cellule cible. (La valeur théorique optimale de la cellule cible est la valeur cible optimale trouvée lorsque les contraintes binaires et entières sont omis.) Souvent, vous aurez le choix entre trouver une réponse dans les 10% d’optimal en 10 minutes ou trouver une solution optimale en deux semaines de temps informatique! La valeur par défaut de l’optimalité entière est de 5%, ce qui signifie que le solveur s’arrête lorsqu’il trouve une valeur de cellule cible à moins de 5% de la valeur théorique optimale de cellule cible. Lorsque l’exemple de développement logiciel a été résolu pour la première fois, l’optimalité entière a été définie sur 5% et a trouvé une valeur de cellule cible optimale de 9269. Lorsque la valeur d’optimalité entière a été modifiée à 0,50%, une meilleure valeur de cellule cible (9293) a été obtenue.

FIGURE 6 : Cette figure montre le réglage de l’option d’optimisation entière.