Solver für die Budgetierung von Investitionen verwenden

Wie kann ein Unternehmen mit Solver bestimmen, welche Projekte es durchführen soll?

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

Wie kann ein Unternehmen mit Solver bestimmen, welche Projekte es durchführen soll?

Die meisten Unternehmen wollen Projekte durchführen, die den größten Kapitalwert (NPV) beitragen, vorbehaltlich begrenzter Ressourcen (in der Regel Kapital und Arbeit). Nehmen wir an, ein Softwareentwicklungsunternehmen versucht zu bestimmen, welches von 20 Softwareprojekten es durchführen soll. Der von jedem Projekt beigesteuerte NBW (in Millionen Dollar) sowie das Kapital (in Millionen Dollar) und die Anzahl der in den nächsten drei Jahren benötigten Programmierer sind auf dem Arbeitsblatt Basismodell in der Datei Capbudget.xlsx angegeben, das in Abbildung 30-1 auf der nächsten Seite dargestellt ist. Projekt 2 wirft beispielsweise 908 Millionen US-Dollar ein. Es erfordert 151 Millionen US-Dollar im Jahr 1, 269 Millionen US-Dollar im Jahr 2 und 248 Millionen US-Dollar im Jahr 3. Project 2 erfordert 139 Programmierer im Jahr 1, 86 Programmierer im Jahr 2 und 83 Programmierer im Jahr 3. Die Zellen E4:G4 zeigen das in jedem der drei Jahre verfügbare Kapital (in Millionen Dollar), und die Zellen H4:J4 geben an, wie viele Programmierer zur Verfügung stehen. Im ersten Jahr stehen beispielsweise bis zu 2,5 Milliarden US-Dollar Kapital und 900 Programmierer zur Verfügung.

Das Unternehmen muss entscheiden, ob es jedes Projekt durchführen soll. Nehmen wir an, dass wir nicht einen Bruchteil eines Softwareprojekts durchführen können; Wenn wir zum Beispiel 0,5 der benötigten Ressourcen zuweisen, hätten wir ein nicht funktionierendes Programm, das uns 0 $ Umsatz bringen würde!

Der Trick bei der Modellierung von Situationen, in denen Sie entweder etwas tun oder nicht tun, besteht darin, binär wechselnde Zellen zu verwenden. Eine binär veränderbare Zelle ist immer gleich 0 oder 1. Wenn eine sich ständig ändernde Zelle, die einem Projekt entspricht, binär gleich 1 ist, führen wir das Projekt durch. Wenn eine binäre, sich ändernde Zelle, die einem Projekt entspricht, gleich 0 ist, führen wir das Projekt nicht aus. Sie richten Solver ein, um einen Bereich binär veränderlicher Zellen zu verwenden, indem Sie eine Einschränkung hinzufügen: Wählen Sie die geänderten Zellen aus, die Sie verwenden möchten, und wählen Sie dann in der Liste im Dialogfeld Einschränkung hinzufügen die Option Behälter aus.

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

• Zielzelle. Wir maximieren den von ausgewählten Projekten generierten NBW.
• Veränderliche Zellen. Wir suchen für jedes Projekt nach einer 0- oder 1-Binärzelle, die sich ändert. Ich habe diese Zellen im Bereich A6:A25 lokalisiert (und den Bereich doit genannt). Zum Beispiel zeigt eine 1 in Zelle A6 an, dass wir Projekt 1 durchführen; Eine 0 in Zelle C6 gibt an, dass wir Projekt 1 nicht durchführen.
• Einschränkungen. Wir müssen sicherstellen, dass für jedes Jahr t (t = 1, 2, 3) das eingesetzte Kapital im Jahr t kleiner oder gleich dem verfügbaren Kapital im Jahr t und im Jahr t eingesetzte Arbeit kleiner oder gleich dem Jahr t ist.

Wie Sie sehen können, muss unser Arbeitsblatt für jede Auswahl von Projekten den NBW, das jährlich verwendete Kapital und die jedes Jahr verwendeten Programmierer berechnen. In Zelle B2 verwende ich die Formel SUMMENPRODUKT(doit;NBW), um den gesamten NBW zu berechnen, der von ausgewählten Projekten generiert wird. (Der Bereichsname NBW bezieht sich auf den Bereich C6:C25.) Für jedes Projekt mit einer 1 in Spalte A erfasst diese Formel den NBW des Projekts und für jedes Projekt mit einer 0 in Spalte A nicht den NBW des Projekts. Daher sind wir in der Lage, den NBW aller Projekte zu berechnen, und unsere Zielzelle ist linear, da sie durch Summieren von Termen berechnet wird, die der Form (sich verändernde Zelle)*(Konstante) entsprechen. In ähnlicher Weise berechne ich das jährlich verbrauchte Kapital und die jährlich eingesetzte Arbeit, indem ich die Formel SUMMENPRODUKT(doit,E6:E25) von E2 nach F2:J2 kopiere.

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

Unser Ziel ist es, den NBW ausgewählter Projekte zu maximieren (Zelle B2). Unsere sich ändernden Zellen (der Bereich mit dem Namen doit) sind die binären sich ändernden Zellen für jedes Projekt. Die Einschränkung E2:J2<=E4:J4 stellt sicher, dass in jedem Jahr das eingesetzte Kapital und die eingesetzte Arbeit kleiner oder gleich dem verfügbaren Kapital und der verfügbaren Arbeit sind. Um die Einschränkung hinzuzufügen, die die sich ändernden Zellen binär macht, klicke ich im Dialogfeld Solver-Parameter auf Hinzufügen und wähle dann Bin aus der Liste in der Mitte des Dialogfelds aus. Das Dialogfeld Einschränkung hinzufügen sollte wie in Abbildung 30-3 dargestellt angezeigt werden.

Unser Modell ist linear, weil die Zielzelle als Summe von Termen berechnet wird, die die Form (sich ändernde Zelle)*(Konstante) haben, und weil die Einschränkungen der Ressourcennutzung berechnet werden, indem die Summe von (sich ändernden Zellen)*(Konstanten) mit einer Konstante verglichen wird.

Wenn das Dialogfeld "Solver-Parameter" ausgefüllt ist, klicken Sie auf "Lösen", und die Ergebnisse sind weiter oben in Abbildung 30-1 dargestellt. Das Unternehmen kann einen maximalen NBW von 9.293 Millionen US-Dollar (9,293 Milliarden US-Dollar) erreichen, indem es die Projekte 2, 3, 6-10, 14-16, 19 und 20 auswählt.

Umgang mit anderen Einschränkungen

Manchmal haben Projektauswahlmodelle andere Einschränkungen. Nehmen Sie zum Beispiel an, wenn wir Projekt 3 auswählen, müssen wir auch Projekt 4 auswählen. Da unsere aktuelle optimale Lösung Project 3, nicht aber 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 sich ändernde Zelle für Project 3 kleiner oder gleich der binären sich ändernden Zelle für Project 4 ist.

Sie finden dieses Beispiel auf dem Arbeitsblatt If 3 then 4 in der Datei Capbudget.xlsx, die in Abbildung 30-4 dargestellt ist. Zelle L9 bezieht sich auf den Binärwert von Projekt 3 und Zelle L12 von dem Binärwert von Projekt 4. Wenn wir die Einschränkung L9<=L12 hinzufügen und Project 3 auswählen, ist L9 gleich 1, und unsere Einschränkung zwingt L12 (die Project 4-Binärdatei), gleich 1 zu sein. Unsere Einschränkung muss auch den Binärwert in der sich ändernden Zelle von Projekt 4 uneingeschränkt lassen, wenn wir Projekt 3 nicht auswählen. Wenn wir Project 3 nicht auswählen, ist L9 gleich 0, und unsere Einschränkung erlaubt, dass die Binärdatei von Project 4 gleich 0 oder 1 ist, was wir wollen. Die neue optimale Lösung ist in Abbildung 30-4 dargestellt.

Eine neue optimale Lösung wird berechnet, wenn die Auswahl von Projekt 3 bedeutet, dass wir auch Projekt 4 auswählen müssen. Nehmen wir nun an, dass wir nur vier Projekte aus den Projekten 1 bis 10 durchführen können. (Siehe höchstens 4 von P1–P10 Arbeitsblatt in Abbildung 30-5.) In Zelle L8 berechnen wir die Summe der Binärwerte, die den Projekten 1 bis 10 zugeordnet sind, mit der Formel SUMME(A6:A15). Dann fügen wir die Einschränkung L8<=L10 hinzu, die sicherstellt, dass höchstens 4 der ersten 10 Projekte ausgewählt werden. Die neue optimale Lösung ist in Abbildung 30-5 dargestellt. Der NBW ist auf 9,014 Mrd. $ gesunken.

Lösen von Problemen der binären und ganzzahligen Programmierung

Lineare Solver-Modelle, bei denen einige oder alle sich ändernden Zellen binär oder ganzzahlig sein müssen, sind in der Regel schwieriger zu lösen als lineare Modelle, in denen alle sich ändernden Zellen Brüche sein dürfen. Aus diesem Grund geben wir uns oft mit einer nahezu optimalen Lösung für ein binäres oder ganzzahliges Programmierproblem zufrieden. Wenn Ihr Solver-Modell lange läuft, sollten Sie die Toleranzeinstellung im Dialogfeld Solver-Optionen anpassen. (Siehe Abbildung 30-6.) Eine Toleranzeinstellung von 0,5 % bedeutet beispielsweise, dass der Löser beim ersten Mal stoppt, wenn er eine praktikable Lösung findet, die innerhalb von 0,5 Prozent des theoretischen optimalen Zielzellwerts liegt (der theoretisch optimale Zielzellwert ist der optimale Zielwert, der gefunden wird, wenn die Binär- und Ganzzahlbedingungen weggelassen werden). Oft stehen wir vor der Wahl, eine Antwort innerhalb von 10 Prozent des Optimalen in 10 Minuten oder eine optimale Lösung in zwei Wochen Computerzeit zu finden! Der Standardtoleranzwert beträgt 0,05 %. Dies bedeutet, dass der Löser anhält, wenn er einen Zielzellwert innerhalb von 0,05 Prozent des theoretisch optimalen Zielzellwerts findet.

Probleme

1. Ein Unternehmen hat neun Projekte in Betracht gezogen. Der von jedem Projekt hinzugefügte NBW und der Kapitalbedarf jedes Projekts in den nächsten zwei Jahren sind in der folgenden Tabelle aufgeführt. (Alle Zahlen sind in Millionen.) Beispielsweise wird Projekt 1 den NBW um 14 Millionen US-Dollar erhöhen und Ausgaben in Höhe von 12 Millionen US-Dollar im ersten Jahr und 3 Millionen US-Dollar im zweiten Jahr erfordern. Im ersten Jahr stehen 50 Millionen $ Kapital für Projekte zur Verfügung, und 20 Millionen $ stehen im zweiten Jahr zur Verfügung.

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

• Wenn wir nicht einen Teil eines Projekts durchführen können, sondern entweder das gesamte oder kein Projekt durchführen müssen, wie können wir dann den NBW maximieren?
• Angenommen, wenn Projekt 4 durchgeführt wird, muss Projekt 5 durchgeführt werden. Wie können wir den NBW maximieren?

• Ein Verlag versucht herauszufinden, welches von 36 Büchern er in diesem Jahr veröffentlichen soll. Die Datei enthält Pressdata.xlsx die folgenden Informationen zu jedem Buch:

  • Voraussichtliche Umsatz- und Entwicklungskosten (in Tausend Dollar)
  • Seiten in jedem Buch
  • Ob das Buch an ein Publikum von Softwareentwicklern gerichtet ist (gekennzeichnet durch eine 1 in Spalte E)
    Ein Verlag kann in diesem Jahr Bücher mit insgesamt bis zu 8500 Seiten veröffentlichen und muss mindestens vier Bücher veröffentlichen, die sich an Softwareentwickler richten. Wie kann das Unternehmen seinen Gewinn maximieren?

Über den Artikel

Dieser Artikel wurde aus Microsoft Office Excel 2007 Data Analysis and Business Modeling von Wayne L. Winston übernommen.

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