Comment une entreprise peut-elle utiliser Solver pour déterminer les projets qu’elle doit entreprendre ?
Chaque année, une entreprise comme Eli Lilly doit déterminer quels médicaments développer ; une entreprise comme Microsoft, qui développe des logiciels ; une entreprise comme Proctor & Gamble, qui développe de nouveaux produits de consommation. La fonction Solver d’Excel peut aider une entreprise à prendre ces décisions.
Comment une entreprise peut-elle utiliser Solver pour déterminer les projets qu’elle doit entreprendre ?
La plupart des entreprises veulent entreprendre des projets qui contribuent à 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). Supposons qu’une société de développement de logiciels essaie de déterminer lequel des 20 projets logiciels elle doit 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 dans la feuille de calcul du modèle de base dans le Capbudget.xlsx du fichier, qui est illustré à la figure 30-1 à la page suivante. Par exemple, le projet 2 rapporte 908 millions de dollars. Il nécessite 151 millions de dollars au cours de l’année 1, 269 millions de dollars au cours de l’année 2 et 248 millions de dollars au cours de l’année 3. Le projet 2 nécessite 139 programmeurs au cours de la première année, 86 programmeurs au cours de la deuxième année et 83 programmeurs au cours de la troisième année. 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 combien de programmeurs sont disponibles. Par exemple, au cours de l’année 1, jusqu’à 2,5 milliards de dollars de capital et 900 programmeurs sont disponibles.
L’entreprise doit décider si elle doit entreprendre chaque projet. Supposons que nous ne puissions pas entreprendre une fraction d’un projet logiciel ; Si nous allouons 0,5 des ressources nécessaires, par exemple, nous aurions un programme non fonctionnel qui nous rapporterait 0 $ de revenus !
L’astuce dans la modélisation des situations dans lesquelles vous faites ou ne faites pas quelque chose est d’utiliser des cellules variables binaires. Une cellule binaire changeante est toujours égale à 0 ou 1. Lorsqu’une cellule changeante binaire qui correspond à un projet est égale à 1, nous effectuons le projet. Si une cellule binaire changeante qui correspond à un projet est égale à 0, nous ne faisons pas le projet. Vous configurez le Solveur pour qu’il utilise une plage de cellules variables 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.
Avec ce contexte, nous sommes prêts à résoudre le problème de sélection du projet logiciel. Comme toujours avec un modèle de solveur, nous commençons par identifier notre cellule cible, les cellules changeantes et les contraintes.
- Cellule cible. Nous maximisons la VAN générée par les projets sélectionnés.
- Changement de cellule. Nous recherchons une cellule binaire 0 ou 1 pour chaque projet. J’ai localisé ces cellules dans la plage A6 :A25 (et nommé la plage doit). Par exemple, un 1 dans la cellule A6 indique que nous entreprenons le projet 1 ; Un 0 dans la cellule C6 indique que nous n’entreprenons pas le Projet 1.
- contraintes. Nous devons nous 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 disponible de l’année t , et que la main-d’œuvre de l’année t utilisée est inférieure ou égale à la main-d’œuvre de l’année t disponible.
Comme vous pouvez le voir, notre feuille de calcul doit calculer pour toute sélection de projets la VAN, le capital utilisé annuellement et les programmeurs utilisés chaque année. Dans la cellule B2, j’utilise la formule SOMMEPROD(doit,VAN) pour calculer la VAN totale générée par les projets sélectionnés. (Le nom de plage NPV fait référence à la plage C6 :C25.) Pour chaque projet avec un 1 dans la colonne A, cette formule prend la VAN du projet, et pour chaque projet avec un 0 dans la colonne A, cette formule ne prend pas la VAN du projet. Par conséquent, nous sommes en mesure de calculer la VAN de tous les projets, et notre cellule cible est linéaire car elle est calculée en additionnant les termes qui suivent la forme (cellule changeante)*(constante). De la même manière, je calcule le capital utilisé chaque année et la main-d’œuvre utilisée chaque année en copiant de E2 à F2 :J2 la formule SOMMEPROD(doit,E6 :E25).
Je remplis maintenant la boîte de dialogue Paramètres du solveur, comme illustré à la figure 30-2.
Notre objectif est de maximiser la VAN des projets sélectionnés (cellule B2). Nos cellules changeantes (la plage nommée doit) sont les cellules changeantes binaires pour chaque projet. La contrainte E2 :J2<=E4 :J4 garantit que, pendant chaque année, le capital et la main-d’œuvre utilisés sont inférieurs ou égaux au capital et à la main-d’œuvre disponibles. Pour ajouter la contrainte qui rend les cellules changeantes binaires, je clique sur Ajouter dans la boîte de dialogue Paramètres du Solveur, puis je sélectionne Bin dans la liste au milieu de la boîte de dialogue. La boîte de dialogue Ajouter une contrainte devrait apparaître comme illustré à la Figure 30-3.
Notre modèle est linéaire parce que 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.
Une fois la boîte de dialogue Paramètres du solveur remplie, cliquez sur Résoudre et nous obtenons les résultats présentés précédemment dans la Figure 30-1. La société peut obtenir une VAN maximale de 9 293 millions de dollars (9,293 milliards de dollars) en choisissant les projets 2, 3, 6 à 10, 14 à 16, 19 et 20.
Gestion d’autres contraintes
Parfois, les modèles de sélection de projet ont d’autres contraintes. Par exemple, supposons que si nous sélectionnons le projet 3, nous devons également sélectionner le projet 4. Étant donné que notre solution optimale actuelle sélectionne Project 3 mais pas Project 4, nous savons que notre solution actuelle ne peut pas rester optimale. Pour résoudre ce problème, il suffit d’ajouter la contrainte selon laquelle 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 dans la feuille de calcul Si 3 alors 4 dans la Capbudget.xlsx fichier, illustrée à la figure 30-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 nous choisissons le projet 3, L9 est égal à 1 et notre contrainte force L12 (le binaire du projet 4) à être égal à 1. Notre contrainte doit également laisser la valeur binaire dans la cellule changeante du projet 4 sans restriction si nous ne sélectionnons pas le projet 3. Si nous ne sélectionnons pas Project 3, L9 est égal à 0 et notre contrainte permet au binaire Project 4 d’être égal à 0 ou 1, ce que nous voulons. La nouvelle solution optimale est illustrée à la figure 30-4.
Une nouvelle solution optimale est calculée si la sélection de Project 3 signifie que nous devons également sélectionner Project 4. Supposons maintenant que nous ne puissions faire que quatre projets parmi les projets 1 à 10. (Voir la feuille de calcul Au maximum 4 sur P1-P10, illustrée à la figure 30-5.) Dans la cellule L8, nous calculons la somme des valeurs binaires associées aux projets 1 à 10 avec la formule SOMME(A6 :A15). Ensuite, nous ajoutons la contrainte L8<=L10, ce qui garantit qu’au maximum, 4 des 10 premiers projets sont sélectionnés. La nouvelle solution optimale est illustrée à la figure 30-5. La VAN est tombée à 9,014 milliards de dollars.
Résolution des problèmes de programmation binaire et entière
Les modèles de solveur linéaire dans lesquels certaines ou toutes les cellules changeantes doivent être binaires ou entières sont généralement plus difficiles à résoudre que les modèles linéaires dans lesquels toutes les cellules changeantes sont autorisées à être des fractions. Pour cette raison, nous sommes souvent satisfaits d’une solution quasi optimale à un problème de programmation binaire ou entier. Si votre modèle de Solveur est exécuté pendant une longue période, vous pouvez envisager d’ajuster le paramètre Tolérance (Tolerance) dans la boîte de dialogue Options du solveur. (Voir la figure 30-6.) Par exemple, un paramètre de tolérance de 0,5 % signifie que le Solveur s’arrête la première fois qu’il trouve une solution réalisable à moins de 0,5 % de la valeur optimale théorique de la cellule cible (la valeur optimale théorique de la cellule cible est la valeur cible optimale trouvée lorsque les contraintes binaires et entières sont omises). Souvent, nous sommes confrontés à un choix entre trouver une réponse à moins de 10 % de l’optimal en 10 minutes ou trouver une solution optimale en deux semaines de temps informatique ! La valeur de tolérance par défaut est de 0,05 %, ce qui signifie que le Solveur s’arrête lorsqu’il trouve une valeur de cellule cible à moins de 0,05 % de la valeur optimale théorique de la cellule cible.
Problèmes
- Une entreprise a neuf projets à l’étude. La VAN ajoutée par chaque projet et le capital requis par chaque projet au cours des deux prochaines années sont indiqués dans le tableau suivant. (Tous les chiffres sont en millions.) Par exemple, le projet 1 ajoutera 14 millions de dollars en VAN et nécessitera des dépenses de 12 millions de dollars au cours de l’année 1 et de 3 millions de dollars au cours de l’année 2. Au cours de l’année 1, 50 millions de dollars en capital sont disponibles pour les projets, et 20 millions de dollars sont disponibles au cours de l’année 2.
| VAN* | Dépenses de l’année 1 | Dépenses de l’année 2 | |
|---|---|---|---|
| Projet 1 | 14 | 12 | 3 |
| Projet 2 | 17 | 54 | 7 |
| Projet 3 | 17 | 6 | 6 |
| Projet 4 | 15 | 6 | 2 |
| Projet 5 | 40 | 30 | 35 |
| Projet 6 | 12 | 6 | 6 |
| Projet 7 | 14 | 48 | 4 |
| Projet 8 | 10 | 36 | 3 |
| Projet 9 | 12 | 18 | 3 |
- Si nous ne pouvons pas entreprendre une fraction d’un projet mais devons entreprendre tout ou rien d’un projet, comment pouvons-nous maximiser la VAN ?
- Supposons que si le projet 4 est entrepris, le projet 5 doit être entrepris. Comment pouvons-nous maximiser la VAN ?
Une maison d’édition tente de déterminer lequel des 36 livres elle devrait publier cette année. Le Pressdata.xlsx de fichiers contient les informations suivantes sur chaque livre :
- Revenus et coûts de développement prévus (en milliers de dollars)
- Pages de chaque livre
- Si le livre s’adresse à un public de développeurs de logiciels (indiqué par un 1 dans la colonne E)
Une maison d’édition peut publier des livres totalisant jusqu’à 8500 pages cette année et doit publier au moins quatre livres destinés aux développeurs de logiciels. Comment l’entreprise peut-elle maximiser ses profits ?
À propos de l’article
Cet article a été adapté de Microsoft Office Excel 2007 Data Analysis and Business Modeling par Wayne L. Winston.
Ce livre de style classe a été développé à partir d’une série de présentations de Wayne Winston, un statisticien et professeur de commerce bien connu qui se spécialise dans les applications créatives et pratiques d’Excel.