| MS Solver | | |
| | | | | | | | | |
| | | | | | | | | |
| Quartal | Q1 | Q2 | Q3 | Q4 | Gesamt | | | | |
| Saisonkoeffizient | 0,9 | 1,1 | 0,8 | 1,2 | | | | | |
| | | | | | | | | | |
| Verkaufte Gebinde | 3.591,55 € | 4.389,68 € | 3.192,49 € | 4.788,74 € | 15.962,46 € | | | | |
| Umsatzerlöse | 143.662,10 € | 175.587,02 € | 127.699,65 € | 191.549,47 € | 638.498,24 € | | | | |
| Aufwendungen | 89.788,81 € | 109.741,88 € | 79.812,28 € | 119.718,42 € | 399.061,40 € | | | | |
| Bruttogewinnspanne | 53.873,29 € | 65.845,13 € | 47.887,37 € | 71.831,05 € | 239.436,84 € | | | | |
| | | | | | | | | | |
| Vertriebskosten | 8.000,00 € | 8.000,00 € | 9.000,00 € | 9.000,00 € | 34.000,00 € | | | | |
| Werbekosten | 10.000,00 € | 10.000,00 € | 10.000,00 € | 10.000,00 € | 40.000,00 € | | | | |
| Gemeinkosten | 21.549,32 € | 26.338,05 € | 19.154,95 € | 28.732,42 € | 95.774,74 € | | | | |
| Gesamtkosten | 39.549,32 € | 44.338,05 € | 38.154,95 € | 47.732,42 € | 169.774,74 € | | | | |
| | | | | | | | | | |
| Gewinn | 14.323,97 € | 21.507,08 € | 9.732,42 € | 24.098,63 € | 69.662,10 € | | | | |
| Gewinnspanne | 10% | 12% | 8% | 13% | 11% | | | | |
| | | | | | | | | |
| Preis pro Gebinde | 40 | | | | | | | | |
| Kosten pro Gebinde | 25 | | | | | | | | |
| | | | | | | | | |
| Das folgende Beispiel zeigt, wie Sie das oben angezeigte Modell für einen oder mehrere Werte lösen können, wie Sie andere Werte | |
| maximieren oder minimieren können, Nebenbedingungen eingeben oder ändern können und ein Problemmodell speichern können. | |
| | | | | | | | | | |
| Zeile | Enthält | | Erklärung | | | | | | |
| 3 | Feste Werte | | Saison-abhängiger Faktor: Die Verkaufszahlen sind höher im 2. Und | |
| | | | 4. Quartal, niedriger im 1. Und 3. Quartal. | |
| | | | | | | | | | |
| 5 | =35*B3*(B11+3000)^0.5 | Vorhersage für die Anzahl verkaufter Einheiten: die 3. Zeile enthält den | |
| | | | Saisonfaktor, die 11. Zeile enthält die Werbungskosten. | |
| | | | | | | | | | |
| 6 | =B5*$B$18 | | Verkaufseinnahmen: Vorhersage der verkauften Einheiten (Zeile 5) | |
| | | | multipliziert mit dem Preis (Zelle B18). | |
| | | | | | | | | | |
| 7 | =B5*$B$19 | | Kosten der Verkäufe: Vorhersage der verkauften Einheiten (Zeile 5) | |
| | | | multipliziert mit den Produktkosten (Zelle B19). | |
| | | | | | | | | | |
| 8 | =B6-B7 | | Bruttogewinn: Verkaufseinnahmen (Zeile 6) abzüglich der Produktkosten | |
| | | | (Zeile 7). | |
| | | | | | | | | | |
| 10 | Feste Werte | | Ausgaben für das Verkaufspersonal | |
| | | | | | | | | | |
| 11 | Feste Werte | | Werbebudget (ca. 6,3% der Verkäufe) | |
| | | | | | | | | | |
| 12 | =0.15*B6 | | Laufende Geschäftskosten: Verkaufseinnahmen (Zeile 6 ) | |
| | | | multipliziert mit 15%. | |
| | | | | | | | | | |
| 13 | =SUMME(B10:B12) | Gesamtkosten: Ausgaben für das Verkaufspersonal (Zeile 10) plus | |
| | | | Werbungskosten (Zeile 11) plus laufende Geschäftskosten (Zeile 12). | |
| | | | | | | | | | |
| 15 | =B8-B13 | | Produktgewinn: Bruttogewinn (Zeile 8) minus Gesamtkosten | |
| | | | (Zeile 13). | |
| | | | | | | | | | |
| 16 | =B15/B6 | | Gewinnspanne: Produktgewinn (Zeile 15) geteilt durch Verkaufseinnahmen | |
| | | | (Zeile 6). | |
| | | | | | | | | | |
| 18 | Feste Werte | | Produktpreis | |
| | | | | | | | | | |
| 19 | Feste Werte | | Produktkosten | |
| | | | | | | | | | |
| Hierbei handelt es sich um ein einfaches Marketingmodell, das steigende Verkaufszahlen, angefangen von einer Basiszahl, durch | |
| steigende Werbungskosten darstellt, jedoch mit verringerten Steigerungen. So führen, zum Beispiel, die ersten € 5.000 an | |
| Werbungskosten im ersten Quartal zu einer Verkaufssteigerung von 1.092 Einheiten, weitere € 5.000 führen jedoch nur noch | |
| zu 775 mehr verkauften Einheiten. | |
| | |
| Sie können Solver verwenden, um die Höhe des Werbebudgets festzulegen und zu überprüfen, ob das Werbebudget besser über | |
| das Jahr verteilt werden kann, um saisonbedingte Schwankungen besser auszunützen. | |
| | |
| Lösung für einen Wert um einen anderen Wert zu maximieren | |
| Ein Anwendungsfall für Solver ist die Ermittlung des Maximalwertes einer Zelle durch die Änderung einer anderen Zelle. Beide Zellen | |
| müssen über Formeln in der Tabelle von einander abhängig sein. Wenn Sie nicht von einander abhängig sind, wird die Änderung | |
| einer Zelle nicht den Wert der anderen Zelle ändern. | |
| | |
| In dem Tabellenbeispiel wollen Sie die Ausgaben für Werbung ermitteln, bei denen ein maximaler Gewinn im ersten Quartal erzielt | |
| wird. Sie sind an der Gewinnmaximierung durch Änderung der Werbungskosten interessiert. | |
| | | | | | | | | | |
| - | Klicken Sie auf 'Solver' im Menü 'Extras'. Geben Sie in dem Feld 'Zielzelle' b15 ein, oder markieren Sie | |
| | Zelle B15 (Gewinne 1.Qrt) in der Tabelle. Aktivieren Sie die Option 'Max'. | |
| | Geben Sie in dem Feld 'Veränderbare Zellen' b11 ein, oder markieren Sie die Zelle B11 | |
| | (Werbungskosten 1.Qrt) in der Tabelle. Klicken Sie auf 'Lösen'. | |
| | |
| In der Statusleiste werden Meldungen angezeigt, daß das Problem aufgestellt wird und Solver mit der Analyse beginnt. Nach einem | |
| Moment wird die Meldung angezeigt werden, das Solver eine Lösung gefunden hat. Solver erkennt, daß Werbungskosten von | |
| € 17.093 im ersten Quartal einen maximalen Gewinn von € 15.093 ergeben. | |
| | |
| - | Um die Ausgangswerte wiederherzustellen, nachdem Sie das Ergebnis analysiert haben, aktivieren Sie | |
| | Ausgangswerte wiederherstellen und klicken Sie auf 'OK'. | |
| | |
| Solveroptionen zurücksetzen | |
| | |
| Wenn Sie die Optionen in dem Dialog 'Solver-Parameter' auf die ursprünglichen Werte zurücksetzen wollen, damit Sie mit einem | |
| neuen Problem beginnen können, können Sie auf 'Zurücksetzen' klicken. | |
| | |
| Lösung für einen Wert durch Änderung mehrerer Werte | |
| | |
| Sie können Solver auch verwenden, um einen Wert zu maximieren oder minimieren, indem Sie mehrere Werte auf einmal ändern. | |
| Z. B. können Sie Werte für das Werbebudget jedes Quartals ermitteln, die den höchsten Gewinn über das gesamte Jahr ergeben. | |
| Da der Saisongkoeffizient in Zeile 3 in die Berechnung der verkauften Einheiten in Zeile 5 als Multiplikator eingerechnet wird, | |
| erscheint es logisch, daß Sie mehr Werbungskosten im 4. Qrtl ausgeben, wo die Verkaufsreaktion am höchsten ist, und weniger im | |
| 3. Qrtl. ausgeben, wo die Verkaufsreaktion am niedrigsten ist. Verwenden Sie Solver, um die beste Quartalsverteilung zu ermitteln. | |
| | | | | | | | | | |
| - | Klicken Sie auf 'Solver' im Menü 'Extras'. Geben Sie in dem Feld 'Zielzelle' f15 ein, oder markieren Sie | |
| | die Zelle F15 (Gesamtgewinne) in der Tabelle. Stellen Sie sicher, daß die Option 'Max' aktiviert ist. Geben | |
| | Sie in das Feld 'Veränderbare Zellen' b11:e11 ein, oder markieren Sie die Zellen B11:E11 (Werbekosten | |
| | für jedes Quartal) in der Tabelle. Klicken Sie auf 'Lösen'. | |
| | | | | | | | | | |
| - | Aktivieren Sie 'Ausgangswerte wiederherstellen' und klicken Sie auf 'OK', nachdem Sie das Ergebnis | |
| | analysiert haben, um die resultierenden Werte zu verwerfen und die Ausgangswerte wieder herzustellen. | |
| | |
| Sie haben Solver verwendet um ein komplexes, nichtlineares Optimierungsproblem zu lösen, in diesem Fall um Werte für die vier | |
| Unbekannten in den Zellen B11 bis E11 zu ermitteln, die die Gewinne maximieren. Es handelt sich hier um ein nichtlineares Problem, | |
| da eine Potenzierung in den Formel in Zeile 5 verwendet wird. Das Ergebnis dieser Optimierung ohne Nebenbedingungen zeigt, | |
| dass Sie die Gewinne auf € 79.706 steigern können, wenn Sie € 89.706 Werbungskosten im Laufe des Jahres investieren. | |
| | |
| In der Praxis gibt es jedoch in den meisten Modellen Beschränkungen, die auf bestimmte Werte zutreffen. Diese Nebenbedingungen | |
| können auf die Zielzelle, die veränderbaren Zellen oder jeden beliebigen anderen Wert, der von den Formeln dieser Zellen verwendet | |
| wird, angewendet werden. | |
| | |
| Nebenbedingungen hinzufügen | |
| | |
| Bis jetzt berücksichtigt das Budget die Werbungskosten und generiert zusätzliche Gewinne, Sie erreichen jedoch einen Punkt, an | |
| dem die Gewinnsteigerungen zurückgehen. Da Sie nie sicher sein können, daß die Verkaufszahlen Ihres Modells im nächsten Jahr | |
| genauso auf die Werbung reagieren werden (insbesondere bei viel größeren Ausgaben), erscheint es vernünftig, die Werbekosten | |
| zu limitieren. | |
| | |
| Angenommen Sie wollen Ihr ursprüngliches Werbebudget von € 40.000 beibehalten. Fügen Sie die Nebenbedingung zu dem | |
| Problem hinzu, die die Summe der Werbekosten für die vier Quartale auf € 40.000 begrenzt. | |
| | |
| - | Klicken Sie auf 'Solver' im Menü 'Extras' und dann auf 'Hinzufügen'. Das Dialogfeld 'Nebenbedingung | |
| | hinzufügen' erscheint. Geben Sie f11 in dem Feld 'Zellbezug' ein, oder markieren Sie die Zelle F11 | |
| | (Gesamtwerbekosten) in der Tabelle. Die Zelle F11 muß kleiner oder gleich € 40.000 sein. | |
| | Die Bedingung zwischen Zellbezug und Nebenbedingung ist standardmäßig <= (kleiner oder gleich), Sie | |
| | müssen sie also nicht ändern. Geben Sie in dem Feld 'Nebenbedingung' 40000 ein. Klicken Sie auf 'OK' | |
| | und dann auf 'Lösen'. | |
| | | |
| - | Um die Ausgangswerte wiederherzustellen, nachdem Sie das Ergebnis analysiert haben, aktivieren Sie | |
| | Ausgangswerte wiederherstellen und klicken Sie auf 'OK'. | |
| | |
| Die Lösung, die von Solver gefunden wurde, verteilt Summen von € 5.117 im 3. Qrtl. bis € 15.263 im 4.Qrtl. Der Gesamtgewinn | |
| stieg von € 69.662 im ursprünglichen Budget auf € 71.447 ohne einen Zuwachs im Werbebudget. | |
| | |
| Nebenbedingungen ändern | |
| | |
| Wenn Sie Microsoft Excel Solver verwenden, können Sie mit unterschiedlichen Variablen experimentieren, um zu entscheiden, was | |
| die beste Lösung für ein Problem ist. Sie können zum Beispiel eine Nebenbedingung ändern, um zu sehen, ob das Ergebnis besser | |
| oder schlechter ist als zuvor. Ändern Sie in dem Tabellenbeispiel die Nebenbedingung für das Werbebudget auf € 50.000, um zu | |
| sehen, wie sich diese Änderung auf den Gesamtgewinn auswirkt. | |
| | |
| - | Klicken Sie auf 'Solver' im Menü 'Extras'. Die Nebenbedingung $F$11 <= 400000 sollte bereits in dem | |
| | Feld 'Nebenbedingungen' ausgewählt sein. Klicken Sie auf 'Ändern'. Ändern Sie in dem Feld | |
| | 'Nebenbedingung' den Wert 40000 auf 50000. Klicken Sie auf 'OK' und dann auf 'Lösen'. | |
| | Aktivieren Sie 'Lösung verwenden' und klicken Sie auf 'OK' um die Werte, die in der Tabelle angezeigt | |
| | werden zu behalten. | |
| | |
| Solver ermittelt eine optimale Lösung, die einen Gesamtgewinn von € 74.817 ergibt. Das ist eine Verbesserung von € 3.370 | |
| gegenüber dem letzten Ergebnis von € 71.447. In den meisten Firmen ist es nicht zu schwierig, eine zusätzliche Investition von | |
| € 10.000 zu rechtfertigen, wenn sie einen zusätzlichen Gewinn von € 3.370, bzw. eine 33,7% Erstattung der Investition verspricht. | |
| Diese Lösung ergibt zwar einen € 4,889 niedrigeren Gewinn als das Modell ohne Nebenbedingung, Sie investieren aber auch | |
| € 39.706 weniger um zu diesem Ergebnis zu gelangen. | |
| | |
| Problemmodelle speichern | |
| | |
| Wenn Sie im Menü 'Datei' auf 'Speichern' klicken, werden die letzten Einstellungen, die Sie in dem Dialogfeld 'Solver-Parameter' | |
| vorgenommen haben, mit der Arbeitsmappe gespeichert. Sie können jedoch auch mehr als ein Problem pro Tabelle definieren, | |
| indem Sie sie unabhängig voneinander über 'Modell speichern' im Dialogfeld 'Optionen' in der Arbeitsmappe speichern. | |
| Jedes Problem beinhaltet die Zellen und Nebenbedingungen, die Sie in dem Dialogfeld 'Solver-Parameter' eingegeben haben. | |
| | |
| Wenn Sie auf 'Modell speichern' klicken erscheint das Dialogfeld 'Modell speichern' mit einer Standardauswahl als Bereich des | |
| zu speichernden Modells, die auf der aktiven Zelle in der Tabelle basiert. Der vorgeschlagene Bereich enthält eine Zelle für jede | |
| Nebenbedingung plus drei zusätzlicher Zellen. Stellen Sie sicher, das dieser Zellbereich in der Tabelle leer ist und keine Daten | |
| enthält. | |
| - | Klicken Sie auf 'Solver' im Menü 'Extras' und dann auf 'Optionen'. Klicken Sie auf 'Modell speichern'. | |
| | Geben Sie in dem Feld 'Modellbereich auswählen' h15:h18 ein, oder markieren Sie die Zellen H15:H18 | |
| | in der Tabelle. Klicken Sie auf 'OK'. | |
| | |
| Anmerkung: Sie können auch einen Bezug auf eine einzelne Zelle in dem Feld 'Modellbereich auswählen' eingeben. Solver | |
| wird diesen Bezug dann als obere, linke Ecke des Bereichs verwenden, in dem er die Problemangaben kopieren wird. | |
| | | | | | | | | | |
| | | | | | | | | | |
| Klicken Sie in dem Dialogfeld 'Optionen' auf 'Modell laden', um diese Problemangaben zu einem späteren Zeitpunkt wieder zu | |
| laden. Geben Sie h15:h18 in dem Feld 'Modellbereich auswählen' ein, oder markieren Sie die Zellen H15:H18 in dem | |
| Tabellenbeispiel und klicken Sie auf 'OK'. Solver wird ein Dialogfeld anzeigen, in dem Sie gefragt werden, ob Sie die aktuellen | |
| Solver-Optionen mit den Einstellungen des gespeicherten Modells überschreiben wollen. Klicken Sie auf 'OK', um fortzufahren. | |
| | | | | | | | | | |
| | | | | | | | | |
| Legende | | | | | | | | | |
| | | | | | | | | | |
| | | Zielzelle | | | | | | |
| | | | | | | | | | |
| | | Veränderbare Zellen | | | | | | |
| | | | | | | | | | |
| | | Nebenbedingungen | | | | | | |
| | | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
zurück