Office
Se connecter

À l’aide 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 société peut utiliser le solveur pour déterminer les projets il doit s’engager ?

Chaque année, une société comme Eli Lilly a besoin pour déterminer qui médicaments pour développer ; une société comme Microsoft, quel logiciel programmes à développer ; une société comme Proctor & risque, les nouveaux produits grand public de développer. La fonctionnalité du solveur dans Excel peut aider une entreprise ces décisions.

La plupart des sociétés souhaitez entreprendre des projets qui contribuent la valeur actuelle nette (VAN), plus grande soumis à des ressources limitées (généralement capitaux et main). Supposons qu’une entreprise de développement logiciel tente de déterminer qui de 20 projets logiciels il doit s’engager. La fonction VAN (en millions de dollars) de contribution de chaque projet ainsi que le capital (en millions de dollars) et le nombre de développeurs nécessaire au cours de chacune des trois années suivantes est donné sur la feuille de calcul du Modèle de base dans le fichier Capbudget.xlsx, qui est indiqué dans la Figure 30-1 dans la page suivante. Par exemple, Project 2 donne 908 dollars. Il nécessite 151 millions de $ pendant 1 année, 269 millions de $ pendant 2 année et 248 millions de $ pendant 3 ans. Projet 2 requiert 139 développeurs pendant 1 année, 86 développeurs pendant 2 année et 83 développeurs pendant 3 ans. Cellules E4:G4 afficher le capital (en millions de dollars) disponible au cours de chacune des trois années, et cellules H4:J4 indiquer combien développeurs sont disponibles. Par exemple, au cours de l’année 1 jusqu'à 2,5 milliards de $ en majuscule et 900 développeurs sont disponibles.

L’entreprise doit déterminer si elle doit entreprendre chaque projet. Supposons que nous ne pouvons pas procéder à une fraction d’un projet de logiciel ; Si nous allouer 0,5 les ressources nécessaires, nous avons par exemple, un programme périodes chômé qui serait permettent de faire nous chiffre d’affaires $0 !

Obtenir ce résultat dans les situations dans lesquelles vous ou ne pas effectuer une opération de modélisation consiste à utiliser des cellules variables binaires. Un fichier binaire modification cellule toujours est égal à 0 ou 1. Lorsqu’un fichier binaire modification de cellule qui correspond à un projet est égale à 1, nous faire le projet. Si un fichier binaire modification de cellule qui correspond à un projet est égal à 0, nous ne faites le projet. Vous configurer le solveur pour utiliser une plage de cellules variables en ajoutant une contrainte de fichier binaire, sélectionnez les cellules variables que vous voulez utiliser, puis choisissez emplacement dans la liste dans la boîte de dialogue Ajouter une contrainte.

Image représentant un livre
Nous utiliserons avec le solveur pour déterminer les projets à procéder à des données figure-30-1

Dans ce contexte, nous sommes prêts à résoudre le problème de sélection de projet logiciel. Comme toujours avec un modèle du solveur, commençons par identifier les contraintes de notre cellule cible et les cellules à modifier.

  • Cellule cible. Nous agrandir la VAN générée par les projets sélectionnés.

  • Cellules variables. Nous recherchez un 0 ou 1 cellule variable binaire pour chaque projet. J’ai trouve ces cellules dans la plage A6:A25 (et nommé la plage doit). Par exemple, 1 dans la cellule A6 indique que nous entreprendre Project 1 ; la valeur 0 dans la cellule C6 indique que nous ne s’engage Project 1.

  • Contraintes. Nous avons besoin de vous assurer que pour chaque année t (t = 1, 2, 3) majuscule année t utilisée est inférieure ou égale à majuscule année t disponible et travaillées année t utilisée sont inférieure ou égale à travail année t disponible.

Comme vous pouvez le voir, notre feuille de calcul calculez pour toute sélection de projets la VAN, le capital utilisé par an et les développeurs chaque années. Dans la cellule B2, j’utilise la formule SUMPRODUCT(doit,NPV) pour calculer le total VAN générée par les projets sélectionnés. (Le nom de la plage VAN fait référence à la plage C6:C25.) Pour chaque projet avec un 1 dans la colonne A, cette formule capte la VAN du projet, et pour chaque projet avec un 0 dans la colonne A, cette formule ne sélectionne la VAN du projet. Par conséquent, nous sommes en mesure de calculer la VAN de tous les projets, et notre cellule cible linéaire, car il est calculé par la somme des termes qui suivent le formulaire (modification cellule. De la même manière, j’ai calculer que le capital utilisé chaque année et le travail utilisé chaque année en copiant E2 vers F2:J2 la formule SUMPRODUCT(doit,E6:E25).

J’ai maintenant remplir dans la boîte de dialogue Paramètres du solveur comme indiqué dans la Figure 30-2.

Image représentant un livre
Boîte de dialogue Paramètres du solveur figure-30-2 configurée pour le modèle de sélection de projet

Notre objectif est d’optimiser VAN des projets sélectionnés (cellule B2). Les cellules variables (la plage nommée doit) sont le fichier binaire cellules variables pour chaque projet. La contrainte E2:J2 < = E4:J4 garantit que chaque année la majuscule et main de œuvre sont inférieure ou égale à la majuscule et le travail disponible. Pour ajouter la contrainte qui rend les cellules variables binaire, j’ai cliquez sur Ajouter dans la boîte de dialogue Paramètres du solveur et puis sélectionnez emplacement dans la liste au milieu de la boîte de dialogue. La boîte de dialogue Ajouter une contrainte doit apparaître comme indiqué dans la Figure 30-3.

Image représentant un livre
Utilisez figure-30-3 l’emplacement option dans la boîte de dialogue Ajouter une contrainte pour configurer binaire cellules variables — cellules affichant un 0 ou 1.

Notre modèle est linéaire, car la cellule cible est calculée comme la somme des termes qui se présentent comme suit (modification cellule et parce que les contraintes de l’utilisation des ressources sont calculées en comparant la somme des (modification cells)*(constants) à une constante.

Avec la boîte de dialogue Paramètres du solveur remplie dans, cliquez sur résoudre et nous avons les résultats affichés précédemment Figure 30-1. La société pouvez obtenir un VAN maximale de 9,293 millions de dollars ($9.293 milliards) en choisissant projets 2, 3, 6 à 10, 14 – 16, 19 et 20.

Modèles de sélection de projet possèdent parfois autres contraintes. Par exemple, supposons que si nous sélectionnons Project 3, nous devons également sélectionner Project 4. Étant donné que notre solution optimale actuelle sélectionne Project 3, mais pas Project 4, nous savoir que notre solution actuelle ne peut pas rester optimale. Pour résoudre ce problème, ajoutez simplement la contrainte que la cellule évolution binaire pour Project 3 est inférieure ou égale à la cellule de modification binaire de 4 de projet.

Vous trouverez cet exemple sur la feuille de calcul Si 3 puis 4 dans le fichier Capbudget.xlsx, qui est affichée dans la Figure 30-4. Cellule L9 fait référence à la valeur binaire liée au projet 3 et cellule L12 la valeur binaire liés au projet 4. En ajoutant la contrainte L9 < = L12, si nous sélectionnez Project 3, L9 égal à 1 et notre contrainte force L12 (binaire Project 4) égal à 1. Notre contrainte doit également conservez la valeur binaire dans la cellule changement du projet 4 illimité si nous ne sélectionnez pas Project 3. Si nous ne sélectionnez pas Project 3, L9 est égal à 0 et notre contrainte permet du projet 4 binaire égal à 0 ou 1, qui est ce que nous voulons. La nouvelle solution optimale apparaît dans la Figure 30-4.

Image représentant un livre
Figure-30-4 nouveau choix idéal pour si ce n’est pas Project 3 puis projet 4

Une nouvelle solution optimale est calculée si en sélectionnant le projet 3, que nous devons également sélectionner Project 4. Supposons à présent que nous pouvons faire uniquement quatre projets parmi les projets 1 à 10. (Voir la feuille de calcul Au plus 4 de P1 – P10 montre la Figure 30-5). Dans la cellule N8, nous calcule la somme des valeurs binaires associés aux projets 1 à 10 avec la formule SUM(A6:A15). Puis nous ajouter la contrainte N8 < = L10, qui permet de garantir que, au maximum de 4 des 10 premiers projets sont sélectionnées. La nouvelle solution optimale s’affiche dans la Figure 30-5. La fonction VAN a supprimé à 9.014 milliards.

Image représentant un livre
Solution figure-30-5 optimale quand nous pouvons sélectionner uniquement les 4 sur 10 projets

Modèles du solveur linéaires dans les cellules variables certaines ou toutes les sont requises pour être binaire ou entier sont généralement plus difficile à résoudre que de modèles linéaires dans laquelle toutes les cellules variables sont autorisés à être fractions. Pour cette raison, nous sont souvent satisfaits à une solution presque optimale pour un problème de programmation binaire ou entier. Si votre modèle du Solveur s’exécute pendant une longue période, vous souhaiterez peut-être essayer 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 tolérance de 0,5 % signifie que le Solveur s’arrête à la première fois qu’il trouve une solution réalisable dans 0,5 pour cent de la valeur de la cellule cible optimal théorique (la valeur de la cellule cible optimal théorique est la valeur cible optimal trouvée lorsque le contraintes binaire et entier sont omises). Nous sommes souvent confrontés à un choix entre rechercher une réponse à 10 pour cent optimale dans 10 minutes ou trouver une solution optimale dans deux semaines d’heure 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 détecte une valeur de la cellule cible au sein de 0,05 % de la valeur de la cellule cible optimal théorique.

Image représentant un livre
Figure-30-6 réglage de l’option tolérance

  1. 1. une société a neuf projets considérée. La fonction VAN ajouté par chaque projet ainsi que le capital requis par chaque projet pendant les deux prochaines années est présentée dans le tableau suivant. (Tous les nombres sont en millions). Par exemple, Project 1 sera complément $14 millions VAN et exiger des dépenses de 12 millions au cours de l’année 1 et 3 millions au cours de l’année 2. Au cours de l’année 1, 50 millions de $ en majuscule est disponibles pour les projets, et 20 millions de dollars est disponibles pendant 2 de l’année.

VAN

Dépenses année 1

Dépenses 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 peut pas procéder à une fraction d’un projet, mais doit s’engager la totalité ou aucune d’un projet, comment optimiser VAN ?

  • Supposons que si Project 4 entreprendre, 5 projet doivent être pratiqués. Comment optimiser VAN ?

  • Une société publication tente de déterminer qui de 36 livres elle doit publier cette année. Le fichier Pressdata.xlsx vous propose les informations suivantes relatives à chaque livre :

    • Coûts prévus de chiffre d’affaires et le développement (en milliers de dollars)

    • Pages de chaque livre

    • Si le carnet d’est destiné à une audience de développeurs de logiciels (indiqué par 1 dans la colonne E)

      Une société publication peut publier des annuaires totalisation jusqu'à 8500 pages cette année et devez publier au moins quatre livres destinés aux développeurs de logiciels. Comment la société peut maximiser ses profits ?

Cet article a été adapté à partir d’entreprise de modélisation et analyse des données Microsoft Office Excel 2007 en Winston.

Ce manuel de style de classe a été développé à partir d’une série de présentations par Wayne Winston, un statisticien et professeur spécialisé dans des applications creative 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.

×