Verwenden von Solver für die Kapitalbudgetierung
Wie kann ein Unternehmen Solver verwenden, um zu bestimmen, welche Projekte es durchführen soll?
Jedes Jahr muss ein Unternehmen wie Eli Lilly bestimmen, welche Medikamente entwickelt werden sollen; ein Unternehmen wie Microsoft, das Softwareprogramme entwickelt; ein Unternehmen wie Proctor & Gamble, das neue Verbraucherprodukte entwickelt. Die Solver-Funktion in Excel kann einem Unternehmen dabei helfen, diese Entscheidungen zu treffen.
Die meisten Unternehmen möchten Projekte durchführen, die den größten Nettogegenwartswert (NPV) beitragen, vorbehaltlich begrenzter Ressourcen (normalerweise Kapital und Arbeit). Nehmen wir an, ein Softwareentwicklungsunternehmen versucht zu bestimmen, welches von 20 Softwareprojekten es durchführen soll. Der Kapitalwert (in Millionen Dollar), der von jedem Projekt beigesteuert wird, sowie das Kapital (in Millionen Dollar) und die Anzahl der Programmierer, die in jedem der nächsten drei Jahre benötigt werden, sind auf dem Basismodell- Arbeitsblatt in der Datei Capbudget.xlsx angegeben. was in Abbildung 30-1 auf der nächsten Seite gezeigt wird. Beispielsweise bringt Projekt 2 908 Millionen US-Dollar ein. Es erfordert 151 Millionen Dollar in Jahr 1, 269 Millionen Dollar in Jahr 2 und 248 Millionen Dollar in Jahr 3. Projekt 2 erfordert 139 Programmierer in Jahr 1, 86 Programmierer in Jahr 2 und 83 Programmierer in Jahr 3. Die Zellen E4:G4 zeigen das Kapital (in Millionen Dollar), die während jedes der drei Jahre verfügbar sind, und die Zellen H4:J4 geben an, wie viele Programmierer verfügbar sind. Beispielsweise stehen im ersten Jahr bis zu 2,5 Milliarden US-Dollar an Kapital und 900 Programmierern zur Verfügung.
Das Unternehmen muss entscheiden, ob es jedes Projekt durchführen soll. Nehmen wir an, wir können keinen Bruchteil eines Softwareprojekts übernehmen; Wenn wir zum Beispiel 0,5 der benötigten Ressourcen zuweisen, hätten wir ein nicht funktionierendes Programm, das uns einen Umsatz von 0 $ bringen würde!
Der Trick beim Modellieren von Situationen, in denen Sie etwas entweder tun oder nicht tun, besteht darin, binär veränderliche Zellen zu verwenden. Eine binäre Wechselzelle ist immer gleich 0 oder 1. Wenn eine binäre Wechselzelle, die einem Projekt entspricht, gleich 1 ist, machen wir das Projekt. Wenn eine binäre sich ändernde Zelle, die einem Projekt entspricht, gleich 0 ist, führen wir das Projekt nicht durch. Sie richten Solver so ein, dass er einen Bereich binär veränderlicher Zellen verwendet, indem Sie eine Einschränkung hinzufügen – wählen Sie die veränderlichen Zellen aus, die Sie verwenden möchten, und wählen Sie dann Bin aus der Liste im Dialogfeld Einschränkung hinzufügen aus.
Mit diesem Hintergrund sind wir bereit, das Problem der Softwareprojektauswahl zu lösen. Wie immer bei einem Solver-Modell beginnen wir damit, unsere Zielzelle, die sich ändernden Zellen und die Einschränkungen zu identifizieren.
Zielzelle. Wir maximieren den NPV, der durch ausgewählte Projekte generiert wird.
Zellen wechseln. Wir suchen für jedes Projekt nach einer 0 oder 1 binär wechselnden Zelle. Ich habe diese Zellen im Bereich A6:A25 lokalisiert (und den Bereich doit genannt). Beispielsweise 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 verwendete Kapital des Jahres t kleiner oder gleich dem verfügbaren Kapital des Jahres t ist und die verwendete Arbeit des Jahres t kleiner oder gleich der verfügbaren Arbeit des Jahres t ist.
Wie Sie sehen können, muss unser Arbeitsblatt für jede Auswahl von Projekten den Barwert, das jährlich verwendete Kapital und die jedes Jahr verwendeten Programmierer berechnen. In Zelle B2 verwende ich die Formel SUMPRODUCT(doit,NPV) , um den gesamten NPV zu berechnen, der von ausgewählten Projekten generiert wird. (Der Bereichsname NPV bezieht sich auf den Bereich C6:C25.) Für jedes Projekt mit einer 1 in Spalte A übernimmt diese Formel den NPV des Projekts, und für jedes Projekt mit einer 0 in Spalte A wird diese Formel nicht ausgewählt den Kapitalwert des Projekts erhöhen. Daher sind wir in der Lage, den Barwert aller Projekte zu berechnen, und unsere Zielzelle ist linear, da sie durch Summieren von Termen berechnet wird, die der Form (veränderliche Zelle)*(Konstante) folgen. Auf ähnliche Weise berechne ich das jedes Jahr verwendete Kapital und die jedes Jahr verwendete Arbeit, indem ich von E2 nach F2:J2 die Formel SUMPRODUCT(doit,E6:E25) kopiere.
Ich fülle jetzt das Dialogfeld Solver-Parameter aus, wie in Abbildung 30-2 gezeigt.
Unser Ziel ist es, den Barwert ausgewählter Projekte zu maximieren (Zelle B2). Unsere Wechselzellen (der Bereich namens doit ) sind die binären Wechselzellen für jedes Projekt. Die Bedingung 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 Bedingung hinzufügen sollte wie in Abbildung 30-3 angezeigt werden.
Unser Modell ist linear, da die Zielzelle als Summe von Termen berechnet wird, die die Form (veränderliche Zelle)*(Konstante) haben, und weil die Einschränkungen der Ressourcennutzung berechnet werden, indem die Summe von (veränderliche Zellen)*(Konstante) mit a verglichen wird Konstante.
Wenn das Dialogfeld „Solver-Parameter" ausgefüllt ist, klicken Sie auf „Lösen", und wir haben die Ergebnisse, die zuvor in Abbildung 30-1 gezeigt wurden. Das Unternehmen kann einen maximalen NPV von 9.293 Millionen US-Dollar (9,293 Milliarden US-Dollar) erzielen, indem es die Projekte 2, 3, 6–10, 14–16, 19 und 20 auswählt.
Manchmal haben Projektauswahlmodelle andere Einschränkungen. Nehmen wir beispielsweise an, dass wir bei Auswahl von Projekt 3 auch Projekt 4 auswählen müssen. Da unsere aktuelle optimale Lösung Projekt 3, aber nicht Projekt 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 Wechselzelle für Projekt 3 kleiner oder gleich der binären Wechselzelle für Projekt 4 ist.
Sie finden dieses Beispiel auf dem Arbeitsblatt Wenn 3 dann 4 in der Datei Capbudget.xlsx, die in Abbildung 30-4 gezeigt wird. Zelle L9 bezieht sich auf den Binärwert in Bezug auf Projekt 3 und Zelle L12 auf den Binärwert in Bezug auf Projekt 4. Durch Hinzufügen der Einschränkung L9<=L12 , wenn wir Projekt 3 auswählen, ist L9 gleich 1 und unsere Einschränkung erzwingt L12 (das Projekt 4 binär) gleich 1. Unsere Einschränkung muss auch den Binärwert in der sich ändernden Zelle von Projekt 4 unbeschränkt lassen, wenn wir Projekt 3 nicht auswählen. Wenn wir Projekt 3 nicht auswählen, ist L9 gleich 0 und unsere Einschränkung lässt die Projekt 4-Binärzahl zu gleich 0 oder 1 sein, 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 das Arbeitsblatt „Höchstens 4 von P1–P10" in Abbildung 30 -5.) In Zelle L8 berechnen wir die Summe der binären Werte, die den Projekten 1 bis 10 zugeordnet sind, mit der Formel SUM(A6:A15) . Dann fügen wir die Einschränkung L8<=L10 hinzu, die dafür sorgt, dass höchstens 4 der ersten 10 Projekte ausgewählt werden. Die neue optimale Lösung ist in Abbildung 30-5 dargestellt. Der NPV ist auf 9,014 Milliarden Dollar gefallen.
Lineare Solver-Modelle, bei denen einige oder alle sich ändernden Zellen binär oder ganzzahlig sein müssen, sind normalerweise schwieriger zu lösen als lineare Modelle, bei denen alle sich ändernden Zellen Brüche sein dürfen. Aus diesem Grund sind wir oft mit einer nahezu optimalen Lösung eines binären oder ganzzahligen Programmierproblems zufrieden. Wenn Ihr Solver-Modell längere Zeit ausgeführt wird, sollten Sie die Einstellung Toleranz im Dialogfeld Solver-Optionen anpassen. (Siehe Abbildung 30-6.) Beispielsweise bedeutet eine Toleranzeinstellung von 0,5 %, dass Solver das erste Mal anhält, wenn es eine machbare Lösung findet, die innerhalb von 0,5 Prozent des theoretisch optimalen Zielzellenwerts liegt (der theoretisch optimale Zielzellenwert ist der optimale Zielwert, der gefunden wird, wenn die Binär- und Ganzzahlbeschränkungen weggelassen werden). Oft stehen wir vor der Wahl, in 10 Minuten eine Antwort innerhalb von 10 Prozent des Optimums zu finden oder in zwei Wochen Computerzeit eine optimale Lösung zu finden! Der Standardwert für die Toleranz beträgt 0,05 %, was bedeutet, dass Solver stoppt, wenn ein Zielzellenwert innerhalb von 0,05 Prozent des theoretisch optimalen Zielzellenwerts gefunden wird.
Ein Unternehmen hat neun Projekte in Betracht gezogen. Der durch jedes Projekt hinzugefügte Kapitalwert und das für jedes Projekt während der nächsten zwei Jahre erforderliche Kapital sind in der folgenden Tabelle aufgeführt. (Alle Zahlen sind in Millionen.) Zum Beispiel wird Projekt 1 einen Barwert von 14 Millionen US-Dollar hinzufügen und Ausgaben von 12 Millionen US-Dollar in Jahr 1 und 3 Millionen US-Dollar in Jahr 2 erfordern. In Jahr 1 stehen 50 Millionen US-Dollar an Kapital für Projekte und 20 US-Dollar zur Verfügung Millionen stehen im zweiten Jahr zur Verfügung.
Barwert | Ausgaben für 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 |
Projekt 7 | 14 | 48 | 4 |
Projekt 8 | 10 | 36 | 3 |
Projekt 9 | 12 | 18 | 3 |
Wenn wir keinen Bruchteil eines Projekts übernehmen können, sondern entweder das gesamte Projekt oder nichts übernehmen müssen, wie können wir dann den NPV maximieren?
Angenommen, wenn Projekt 4 durchgeführt wird, muss Projekt 5 durchgeführt werden. Wie können wir den NPV maximieren?
Ein Verlag versucht festzulegen, welches von 36 Büchern er in diesem Jahr veröffentlichen soll. Die Datei Pressdata.xlsx enthält die folgenden Informationen zu jedem Buch:
Voraussichtliche Einnahmen und Entwicklungskosten (in Tausend Dollar)
Seiten in jedem Buch
Ob das Buch auf ein Publikum von Softwareentwicklern ausgerichtet ist (angezeigt durch eine 1 in Spalte E)
Ein Verlag darf in diesem Jahr Bücher mit einem Gesamtumfang von 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?
Dieser Artikel wurde von Wayne L. Winston aus Microsoft Office Excel 2007 Data Analysis and Business Modeling ü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.
No comments:
Post a Comment