Utilisation du solveur pour la budgétisation de capital

Remarque : Nous faisons de notre mieux pour vous fournir le contenu d’aide le plus récent aussi rapidement que possible dans votre langue. Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez-vous nous indiquer en bas de page si ces informations vous ont aidé ? Voici l’article en anglais à des fins de référence aisée.

Comment une entreprise peut-elle utiliser le solveur pour identifier les projets qu'elle doit entreprendre?

Chaque année, une entreprise telle que Eli Lilly doit déterminer les médicaments à développer; une entreprise telle que Microsoft, qui est un logiciel à développer; une entreprise telle que Proctor &, qui est le développement de nouveaux produits grand public. La fonctionnalité solveur d'Excel peut aider une entreprise à prendre les décisions suivantes.

La plupart des entreprises souhaitent engager des projets qui apportent la plus grande valeur actuelle nette (Van), moyennant des ressources limitées (généralement capital et main-d'œuvre). Imaginons qu'une entreprise de développement de logiciels tente de déterminer lequel de 20 projets logiciels doit s'engager. La van (en millions de dollars) fournie par chaque projet, ainsi que le nombre de développeurs (en millions de dollars) et le nombre de programmes de programmation requis au cours des trois années à venir, sont fournis dans la feuille de calcul du modèle de base du fichier Capbudget. xlsx, qui est illustré sur la page suivante de la figure 30-1. Par exemple, Project 2 génère $908 millions. Il nécessite $151 millions au cours de l'année 1, $269 millions lors de l'année 2 et $248 millions au cours de l'année 3. Project 2 nécessite des programmeurs 139 pendant l'année 1, 86 programmeurs en année 2 et 83 programmeurs lors de l'année 3. Les cellules E4: G4 présentent le capital (en millions de dollars) disponible au cours des trois années, et les cellules H4: J4 indiquent le nombre de programmeurs disponibles. Par exemple, au cours de l'année 1, les programmeurs en capital et 900 sont disponibles.

La société doit décider s'il doit engager chaque projet. Supposons que nous ne pouvons pas engager une fraction de projet logiciel; Si nous allouons 0,5 aux ressources nécessaires (par exemple, nous aurions un programme chômée qui nous enverrons des revenus $0?

L'astuce dans les situations de modélisation dans lesquelles vous effectuez ou n'effectuez aucune action est d'utiliser des cellules variables en binaire. Une cellule à changement binaire est toujours égale à 0 ou 1. Lorsqu'une cellule qui change en binaire qui correspond à un projet est égale à 1, nous faisons le projet. Si une cellule à changement binaire qui correspond à un projet est égale à 0, le projet n'est pas utilisé. Vous configurez le solveur pour utiliser une plage de cellules variables binaires en ajoutant une contrainte, sélectionnez les cellules à utiliser, puis sélectionnez bin dans la liste de la boîte de dialogue Ajouter une contrainte.

Image représentant un livre

Avec cet arrière-plan, 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 la cellule cible, les cellules variables et les contraintes.

  • Cellule cible. Nous maximisons la van générée par des projets sélectionnés.

  • Modification de cellules.Nous cherchons une cellule en mutation binaire 0 ou 1 pour chaque projet. J'ai trouvé ces cellules dans la plage A6: A25 (et nommée la plage doit). Par exemple, la cellule 1 de la cellule A6 indique que nous engageons Project 1; le 0 dans la cellule C6 indique que nous n'engageons pas Project 1.

  • Aux.Nous devons veiller à ce que pour chaque année t (t = 1, 2, 3), le capital t utilisé est inférieur ou égal à l'année en capital t disponible, et l'année t utilisée est inférieure ou égale à la main-d'œuvre disponible pour l'année.

Comme vous pouvez le constater, notre feuille de calcul doit calculer pour tout type de projet de la van, le capital utilisé chaque année et les programmeurs utilisés chaque année. Dans la cellule B2, j'utilise la formule SOMMEPROD (doit, Van) pour calculer le nombre total de Van générés par les projets sélectionnés. (Le nom de plage van fait référence à la plage C6: C25.) Pour chaque projet avec la valeur 1 dans la colonne A, cette formule récupère la van du projet, et pour chaque projet avec 0 dans la colonne A, cette formule ne décroche 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 par des termes de synthèse qui suivent le formulaire (modification de cellule) * (constante). De la même façon, je calcule le capital utilisé chaque année et la main-d'œuvre utilisée chaque année en copiant du E2 vers F2: J2, la formule SOMMEPROD (doit et E6: E25).

Je remplit désormais la boîte de dialogue Paramètres du solveur, comme illustré dans la figure 30-2.

Image représentant un livre

Notre objectif est d'optimiser la van de projets sélectionnés (cellule B2). Nos cellules variables (la plage nommée doit) sont les cellules variables binaires pour chaque projet. La contrainte E2: J2< = E4: J4 vérifie qu'au cours de 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 apporte le format binaire aux cellules variables, je clique sur Ajouter dans la boîte de dialogue Paramètres du solveur, puis sélectionne bin dans la liste au milieu de la boîte de dialogue. La boîte de dialogue Ajouter une contrainte doit apparaître comme illustré dans la figure 30-3.

Image représentant un livre

Notre modèle est linéaire, car la cellule cible est calculée en tant que somme des termes qui présentent le formulaire (variable) * (constante) et dans la mesure où les contraintes d'utilisation des ressources sont calculées en comparant la somme (cellules variables) * (constantes) à une constante.

Dans la boîte de dialogue Paramètres du solveur, cliquez sur résoudre, et les résultats sont présentés plus haut dans la figure 30-1. La société peut obtenir une van maximum de $9,293 milliards ($9,293 milliards) en sélectionnant les projets 2, 3, 6-10, 14-16, 19 et 20.

Parfois, les modèles de sélection de projet ont d'autres contraintes. Par exemple, supposons que si nous sélectionne Project 3, vous devez également sélectionner Project 4. Comme 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, ajoutez simplement la contrainte que la cellule de modification binaire pour le projet 3 est inférieure ou égale à la cellule de modification binaire pour Project 4.

Cet exemple est affiché dans la feuille de calcul If 3 et 4 dans le fichier Capbudget. xlsx, présenté dans la figure 30-4. La cellule L9 fait référence à la valeur binaire associée à Project 3 et à la cellule L12 la valeur binaire liée à Project 4. En ajoutant la contrainte L9< = L12, si nous choisissons Project 3, L9 est égal à 1, et notre contrainte L12 (le fichier binaire Project 4) est égale à 1. Notre contrainte doit également laisser la valeur binaire dans la cellule changeante de Project 4 sans restriction si vous ne sélectionnez pas Project 3. Si vous ne sélectionnez pas Project 3, L9 est égal à 0 et notre contrainte permet au fichier binaire Project 4 de correspondre à 0 ou 1, c'est ce que nous voulons. La nouvelle solution optimale est illustrée dans la figure 30-4.

Image représentant un livre

Une nouvelle solution optimale est calculée si vous sélectionnez Project 3, vous devez également sélectionner Project 4. Imaginons que nous ne puissiez faire que quatre projets entre les projets 1 à 10. (Voir la feuille de calcul 4 de P1-P10 , présentée dans la figure 30-5.) Dans la cellule N8, nous calculons la somme des valeurs binaires associées aux projets 1 à 10 avec la somme de formule (A6: A15). Ensuite, nous ajoutons la contrainte L8< = L10, qui garantit que, au plus, 4 des 10 premiers projets sont sélectionnés. La nouvelle solution optimale est illustrée dans la figure 30-5. La van a déposé sur $9,014 milliards.

Image représentant un livre

Les modèles de solveur linéaire dans lesquels certaines ou toutes les cellules variables doivent être de type binaire ou entier sont généralement plus difficiles à résoudre que les modèles linéaires dans lesquels toutes les cellules variables sont autorisées à être des fractions. Pour cette raison, nous sommes souvent satisfaites du problème de programmation de type binaire ou entier pour une solution plus proche. Si votre modèle du solveur s'exécute depuis longtemps, vous pouvez envisager de régler le paramètre tolérance dans la boîte de dialogue Options du solveur. (Voir figure 30-6.) Par exemple, un paramètre Tolerance de 0,5% signifie que le solveur arrêtera la première fois qu'il trouve une solution faisable qui est comprise dans 0,5 pour cent de la valeur de la cellule cible optimale théorique (c'est la valeur cible optimale trouvée lors du les contraintes binaires et entiers sont omises). Souvent, nous sommes confrontés à un choix entre la recherche d'une réponse dans un délai de 10% de la valeur optimale dans un délai de 10 minutes ou la recherche d'une solution optimale en deux semaines d'utilisation de l'ordinateur. La valeur de tolérance par défaut est 0,05%, ce qui signifie que le Solveur s'arrête lorsqu'il trouve une valeur de cellule cible dans 0,05 pour cent de la valeur de la cellule cible optimale théorique.

Image représentant un livre

  1. 1. une société a neuf projets en considération. La van ajoutée par chaque projet et le capital requis par chaque projet au cours des deux années suivantes sont indiqués dans le tableau suivant. (Tous les numéros sont en millions.) Par exemple, Project 1 ajoute $14 millions dans la van et nécessite des dépenses d' $12 millions en année 1 et $3 millions lors de l'année 2. Pendant l'année 1, $50 millions en capital est disponible pour les projets et $20 millions est disponible en 2 ans.

Van

Année 1 Dépenses

Année 2-dépenses

Projet 1

14

12

3

Projet 2

Play

54

7

Projet 3

Play

6

6

Projet 4

15

6

2

Projet 5

40

trente

35

Projet 6

12

6

6

Project 7

14

48

4

Project 8

10

36

3

Project 9

12

18

3

  • Si nous ne sommes pas en mesure d'entreprendre une fraction de projet, vous devez s'engager sur tout ou partie d'un projet, comment puis-je optimiser la van?

  • Supposez que si le projet 4 est engagé, le projet 5 doit être engagé. Comment puis-je optimiser la van?

  • Une entreprise d'édition tente de déterminer la documentation 36 qui doit être publiée cette année. Le fichier pressData. xlsx donne accès aux informations suivantes sur chaque livre:

    • Coûts de produits et de développement prévisionnels (en milliers de dollars)

    • Pages de chaque livre

    • Si le livre est orienté vers un public de développeurs de logiciels (indiqué par un 1 dans la colonne E)

      Une société de publication 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 la société peut-elle optimiser son profit?

Cet article a été adapté de l'analyse des données et de la modélisation de l' entreprise Microsoft Office Excel 2007 par Wayne L. Winston.

Ce livre de style Classroom a été développé à partir d'une série de présentations rédigées par Wayne Winston, un statisticien et un professeur bien connus qui s'est spécialisé dans les applications créatives et pratiques d'Excel.

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×