Office: Verwenden von Solver für die Kapital Budgetierung

Verwenden von Solver für die Kapital Budgetierung
Excel für Microsoft 365 Excel für Microsoft 365 für Mac Excel 2019 Excel 2016 Excel 2019 für Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 für Mac Excel für Mac 2011 Mehr... Weniger Wie kann ein Unternehmen Solver verwenden, um zu ermitteln, welche Projekte es durchführen soll?

Jedes Jahr muss ein Unternehmen wie Eli Lilly ermitteln, welche Arzneimittel entwickelt werden sollen. ein Unternehmen wie Microsoft, das Softwareprogramme entwickelt; ein Unternehmen wie Proctor & Gamble, das neue Konsumgüter entwickelt. Das Solver-Feature in Excel kann einem Unternehmen dabei helfen, diese Entscheidungen zu treffen.

Wie kann ein Unternehmen Solver verwenden, um zu ermitteln, welche Projekte es durchführen soll?
Die meisten Unternehmen möchten Projekte durchführen, die den größten Nettobarwert (Barwert) beisteuern, wobei nur begrenzte Ressourcen (in der Regel Kapital und Arbeitskräfte) gelten. Angenommen, ein Softwareentwicklungsunternehmen versucht, zu ermitteln, welche von 20 Softwareprojekten es durchführen soll. Der Barwert (in Millionen von Dollar), der von jedem Projekt beigetragen wurde, sowie das Kapital (in Millionen von Dollar) und die Anzahl der Programmierer, die in den nächsten drei Jahren benötigt werden, werden auf dem Arbeitsblatt des Grundmodells in der Datei Capbudget.xlsx angegeben, das in Abbildung 30-1 auf der nächsten Seite angezeigt wird. Beispiel: Project 2 ergibt $908 Millionen. Sie erfordert $151 Millionen im Jahr 1, $269 Millionen während des Jahres 2 und $248 Millionen im Jahr 3. In Project 2 sind 139-Programmierer während des Jahres 1, 86-Programmierer während des Jahres 2 und 83-Programmierer während des Jahres 3 erforderlich. Zelle E4: G4 zeigt das Kapital (in Millionen von Dollar), das in jedem der drei Jahre zur Verfügung steht, und die Zellen H4: G3 geben an, wie viele Programmierer verfügbar sind. Beispielsweise stehen im Jahr 1 bis zu $2,5 Milliarden in Capital und 900-Programmierer zur Verfügung.

Das Unternehmen muss entscheiden, ob es jedes Projekt übernehmen soll. Gehen wir davon aus, dass wir keinen Bruchteil eines Software Projekts übernehmen können. Wenn wir beispielsweise 0,5 der benötigten Ressourcen zuteilen, hätten wir ein arbeitsfreier Programm, das uns $0 Umsatz bringen würde!

Der Trick in der Modellierung von Situationen, in denen Sie entweder etwas tun oder nicht tun, ist die Verwendung von binären Zellen, die sich ändern. Eine binäre Zelle, die sich ändert, entspricht immer 0 oder 1. Wenn eine binäre Zelle, die einem Projekt entspricht, 1 ist, wird das Projekt durchführen. Wenn eine binäre Zelle, die einem Projekt entspricht, gleich 0 ist, wird das Projekt nicht durchführen. Sie können Solver so einrichten, dass Sie einen Bereich von binären Zellen ändern, indem Sie eine Einschränkung hinzufügen: Wählen Sie die Zellen aus, die Sie verwenden möchten, und wählen Sie dann in der Liste im Dialogfeld Abhängigkeit hinzufügen die Option bin aus.



Mit diesem Hintergrund sind wir bereit, das Problem der Software Projektauswahl zu lösen. Wie immer bei einem Solver-Modell erkennen wir zunächst unsere Zielzelle, die sich ändernden Zellen und die Einschränkungen.

• Zielzelle Wir maximieren den von ausgewählten Projekten generierten.
  
  
• Ändern von Zellen Für jedes Projekt suchen wir nach einer 0 oder 1 binären Zelle, die sich ändert. Ich habe diese Zellen im Bereich A6: A25 (und nannte den Bereich doit) lokalisiert. Eine 1 in Zelle A6 zeigt beispielsweise an, dass wir Project 1 durchführen. eine 0 in Zelle C6 gibt an, dass wir Project 1 nicht übernehmen.
  
  
• Einschränkungen. Wir müssen dafür sorgen, dass für jedes Jahr t (t = 1, 2, 3), das verwendete Jahr t -Kapital kleiner oder gleich dem verfügbaren Jahr -t-Kapital ist , und das Jahr t Arbeit, das verwendet wird, weniger als oder gleich dem Jahr t Arbeit zur Verfügung steht.
  
  

Wie Sie sehen können, muss unser Arbeitsblatt für jede Auswahl von Projekten den, das jährlich verwendete Kapital und die pro Jahr verwendeten Programmierer berechnen. In Zelle B2 verwende ich die Formel SumProduct (doit, Barwert), um den gesamten von ausgewählten Projekten generierten Barwert zu berechnen. (Der Bereichsname barname bezieht sich auf den Bereich C6: C25.) Bei jedem Projekt mit einer 1 in Spalte a nimmt diese Formel den Barwert des Projekts auf, und bei jedem Projekt mit einer 0 in Spalte a übernimmt diese Formel nicht den Barwert des Projekts. Daher sind wir in der Lage, den Barwert aller Projekte zu berechnen, und unsere Zielzelle ist linear, weil Sie durch Summieren von Termen berechnet wird, die dem Formular (Zelle ändern) * (Konstante)folgen. Auf ähnliche Weise berechne ich das jährlich verwendete Kapital und die jährlich verwendete Arbeit, indem ich von E2 in F2: J2 die Formel SumProduct (doit, E6: E25)kopiere.

Ich Fülle nun das Dialogfeld Solver-Parameter aus, wie in Abbildung 30-2 dargestellt.



Unser Ziel ist es, den ausgewählter Projekte (Zelle B2) zu maximieren. Unsere sich ändernden Zellen (der Bereich mit dem Namen " doit") sind die binären Zellen, die für jedes Projekt geändert werden. Die Einschränkung E2: J2<= E4: Sie stellt sicher, dass in jedem Jahr die verwendeten Kapital-und Arbeitsmittel kleiner oder gleich dem verfügbaren Kapital und Arbeitsaufwand sind. Wenn Sie die Einschränkung hinzufügen möchten, die die binäre Zellen Änderung annimmt, klicken Sie im Dialogfeld Solver-Parameter auf Hinzufügen, und wählen Sie dann in der Liste in der Mitte des Dialogfelds den Eintrag bin aus. Das Dialogfeld Einschränkung hinzufügen sollte wie in Abbildung 30-3 angezeigt werden.



Unser Modell ist linear, weil die Zielzelle als Summe von Termen berechnet wird, die das Formular (Zelle ändern) * (konstant) aufweisen, und weil die Einschränkungen für die Ressourcennutzung berechnet werden, indem die Summe von (veränderbaren Zellen) * (Konstanten) mit einer Konstante verglichen wird.

Wenn das Dialogfeld Solver-Parameter ausgefüllt ist, klicken Sie auf lösen, und die Ergebnisse werden in Abbildung 30-1 weiter oben angezeigt. Das Unternehmen kann einen maximalen Barwert von $9,293 Milliarden ($9,293 Milliarden) erreichen, indem es die Projekte 2, 3, 6 – 10, 14 – 16, 19 und 20 auswählt.

Behandeln anderer Einschränkungen
Manchmal haben Projektauswahl Modelle andere Einschränkungen. Nehmen wir beispielsweise an, dass bei Auswahl von Projekt 3 auch Project 4 ausgewählt werden muss. Da unsere aktuelle optimale Lösung Project 3, aber nicht Project 4 auswählt, wissen wir, dass unsere aktuelle Lösung nicht optimal bleiben kann. Um dieses Problem zu lösen, fügen Sie einfach die Einschränkung hinzu, dass die binäre Zelle, die sich in Project 3 befindet, kleiner oder gleich der binären Zelle ist, die sich in Project 4 ändert.

Sie finden dieses Beispiel auf dem Arbeitsblatt if 3 then 4 in der Datei Capbudget.xlsx, das in Abbildung 30-4 zu sehen ist. Zelle L9 bezieht sich auf den Binärwert in Bezug auf Project 3 und die Zelle L12 auf den Binärwert, der sich auf Project 4 bezieht. Durch Hinzufügen der Einschränkung L9<= L12, wenn wir Project 3 auswählen, ist L9 gleich 1, und unsere Einschränkungs Kräfte L12 (die Project 4-Binärdatei) auf gleich 1. Unsere Einschränkung muss auch den Binärwert in der sich ändernden Zelle von Project 4 Unrestricted belassen, wenn wir Project 3 nicht auswählen. Wenn wir Project 3 nicht auswählen, entspricht L9 0, und unsere Einschränkung ermöglicht es, dass die Project 4-Binärdatei 0 oder 1 entspricht, was wir wünschen. Die neue optimale Lösung ist in Abbildung 30-4 zu sehen.



Eine neue optimale Lösung wird berechnet, wenn Sie in Projekt 3 auswählen, dass wir auch Project 4 auswählen müssen. Nehmen wir nun an, dass wir nur vier Projekte aus den Projekten 1 bis 10 ausführen können. (Siehe höchstens 4 von P1 – P10- Arbeitsblatt, Abbildung 30-5.) In Zelle L8 berechnen wir die Summe der binären Werte, die Projekten 1 bis 10 zugeordnet sind, mit der Formel Summe (A6: A15). Anschließend fügen wir die Einschränkung L8<= L10hinzu, die sicherstellt, dass höchstens 4 der ersten 10 Projekte ausgewählt sind. Die neue optimale Lösung ist in Abbildung 30-5 zu sehen. Der ist auf $9,014 Milliarden gesunken.



Lösen von Problemen mit der Binär-und ganz Zahl Programmierung
Lineare Solver-Modelle, bei denen einige oder alle veränderbaren Zellen als binäre oder ganze Zahlen erforderlich sind, sind in der Regel schwerer zu lösen als lineare Modelle, bei denen alle veränderbaren Zellen zu Bruchteilen zugelassen werden. Aus diesem Grund sind wir oft mit einer nahezu optimalen Lösung für ein Problem mit binären oder ganzzahligen Programmierproblemen zufrieden. Wenn Ihr Solver-Modell für längere Zeit ausgeführt wird, sollten Sie möglicherweise die Einstellung Toleranz im Dialogfeld Solver-Optionen anpassen. (Siehe Abbildung 30-6.) Eine Toleranzeinstellung von 0,5% bedeutet beispielsweise, dass Solver beim ersten Auffinden einer machbaren Lösung, die sich innerhalb von 0,5 Prozent des theoretisch optimalen Zielzelle-Werts befindet, beendet wird (der theoretisch optimale zielzellewert ist der optimale Zielwert, der gefunden wird, wenn die binären und Integer-Einschränkungen ausgelassen werden). Oft haben wir die Wahl zwischen der Suche nach einer Antwort innerhalb von 10 Prozent des optimalen in 10 Minuten oder der Suche nach einer optimalen Lösung in zwei Wochen Computer Zeit! Der Standardtoleranzwert ist 0,05%, was bedeutet, dass Solver angehalten wird, wenn ein zielzellewert innerhalb von 0,05 Prozent des theoretisch optimalen Zielzelle-Werts gefunden wird.



Probleme

1. 1. in einem Unternehmen sind neun Projekte berücksichtigt. In der folgenden Tabelle ist der von jedem Projekt hinzugefügte Barcode und das für die einzelnen Projekte in den nächsten zwei Jahren erforderliche Kapital angegeben. (Alle Zahlen sind in Millionen.) In Project 1 wird beispielsweise $14 Millionen in Barcode hinzugefügt, und es werden Ausgaben von $12 Millionen im Jahr 1 und $3 Millionen im Jahr 2 benötigt. Im Jahr 1 steht $50 Millionen in Capital für Projekte zur Verfügung, und $20 Millionen ist im zweiten Jahr verfügbar.
   
   

	NPV	Jahr 1 Ausgaben	Jahr 2 Ausgaben
Projekt 1	14	12	3
Projekt 2	17	54	7
Project 3	17	6	6
Projekt 4	15	6	2
Project 5	40	30	35
Project 6	12	6	6
Projekt 7	14	48	4
Projekt 8	10	36	3
Projekt 9	12	18	3

• Wie können wir den Baranteil maximieren, wenn wir keinen Bruchteil eines Projekts übernehmen, sondern entweder alle oder kein Projekt durchführen müssen?
  
  
• Angenommen, wenn Project 4 durchgestellt wird, muss Project 5 durchgestellt werden. Wie können wir den maximieren?
  
  

• Ein Publishing-Unternehmen versucht, festzustellen, welche von 36-Büchern dieses Jahr veröffentlicht werden sollen. Die Datei Pressdata.xlsx gibt die folgenden Informationen zu jedem Buch an:
  
  • Voraussichtliche Umsatz-und Entwicklungskosten (in Tsd. US-Dollar)
    
    
  • Seiten in jedem Buch
    
    
  • Ob das Buch auf ein Publikum von Softwareentwicklern ausgerichtet ist (in Spalte E mit einem 1 gekennzeichnet)
    
    Ein Verlagsunternehmen kann in diesem Jahrbücher mit einer Gesamtzahl von bis zu 8500 Seiten veröffentlichen und mindestens vier Bücher veröffentlichen, die sich an Softwareentwickler orientieren. Wie kann das Unternehmen seinen Profit maximieren?
    
    

Informationen zum Artikel
Dieser Artikel wurde von Microsoft Office Excel 2007 Data Analysis and Business Modeling von Wayne L. Winston angepasst.

Dieses Buch im Klassenzimmer wurde aus einer Reihe von Präsentationen von Wayne Winston entwickelt, einem bekannten Statistiker und Business-Professor, der sich auf kreative, praktische Anwendungen von Excel spezialisiert hat.

:)