Excel: Fortgeschrittene Formeln und Funktionen zur Erstellung effizienter Team-Workflows

1. Einführung: Hallo zusammen. Mein Name ist Al und ich bin Lösungsarchitekt bei Coda, aber ich bin auch seit vielen Jahren ein Excel-Poweruser. Ich habe angefangen, Excel in einem internen Finanzteam bei Google zu verwenden. Ich freue mich sehr, diesen Kurs unterrichten zu können, um Ihnen einige erweiterte Funktionen und Funktionen in Excel vorzustellen, die Ihnen und Ihren Teamkollegen helfen, produktiver zu sein, Daten besser zu analysieren und letztendlich bessere Geschäftsentscheidungen treffen. der heutigen Klasse geht es darum , einige erweiterte Funktionen und Funktionen in Excel zu verwenden . diesen Funktionen gehören Offsets, NPV und die Zielsuchfunktion, mit der Sie die optimale Eingabe für Ihr Modell finden können . Dies ist ein Kurs für Fortgeschrittene, aber ich werde Sie Schritt für Schritt durch all diese Funktionen führen, damit Sie sie auf Ihre Modelle anwenden können. Ich sehe, dass viele Teams Probleme haben und schlechte Geschäfts - oder Investitionsentscheidungen treffen, wenn sie einige dieser Funktionen nicht nutzen. Sie versuchten, ad hoc eine Formel zusammenzustellen. In vielen Excel-Kursen lernen Sie, wie Sie produktiver arbeiten und Aufgaben automatisieren können. In diesem Kurs geht es darum , wie Sie Ihre Daten analytischer und kritischer betrachten können. Die wichtigste Erkenntnis, die Sie mitnehmen sollten ist die Verwendung dieser Funktionen und Formeln, um bessere Anlageentscheidungen zu treffen. ob Sie im Finanzwesen, Marketing oder in einem anderen Bereich arbeiten , in dem Sie mehrere Möglichkeiten bewerten müssen. Es stehen eine Menge Dollar oder viele Ressourcen auf dem Spiel. Sie verfügen über die Fähigkeiten, die Sie benötigen um Ihrem Team zu helfen, kritischer und analytischer zu denken, um bessere Geschäftsentscheidungen zu treffen. Für das Klassenprojekt geben Sie vor, Analyst bei einem großen Online-Händler zu sein. Ihr Ziel ist es, diesem Online-Händler dabei zu helfen, anhand historischer Umsatzdaten herauszufinden , in welche neuen Produktlinien er investieren sollte. Sie werden auch versuchen, zukünftige Umsätze anhand von Wachstumsraten vorherzusagen , zukünftige Umsätze anhand von Wachstumsraten und mithilfe von Goal Seek und der erweiterten Funktion in Excel die optimale Wachstumsrate angesichts der Einschränkungen in der , das Sie irgendwann bauen werden. Ich hoffe wirklich, dass Sie Ihre Fragen im Diskussionsforum der Klasse posten und auch Screenshots und Fragen zu Ihrem Klassenprojekt im Projektbereich der Klasse posten. Lassen Sie uns nun weitermachen und mit Lektion 1 beginnen. 2. Verwende OFFSET W/AVG und SUM: In dieser Lektion geht es darum, wie Sie die Offset-Funktion in Kombination mit den Durchschnitts - und Summenfunktionen verwenden. Dies ist eine sehr nützliche Funktion, insbesondere für Zeitreihendaten, da Sie damit Daten über Monate, Quartale oder Jahre hinweg schnell zusammenfassen Daten über Monate, Quartale oder Jahre hinweg oder mitteln können, um diese Daten schnell mit Ihrem Teamkollegen und Ihre Kollegen. Wir werden in Schritt 1 eine grundlegende Offset-Funktion schreiben, in der wir in dieser Tabelle unten auf den ersten Monat der neuen MRR in Zelle C25 verweisen MRR in Zelle C25 und diese Zelle um zwei Zeilen versetzen und drei Säulen. Es ist eine wirklich, wirklich einfache Offset-Funktion. Bevor wir damit beginnen, werfen wir hier kurz einen Blick auf unser einfaches Wachstumsmodell. Wir haben hier Umsatz- und Kundenwachstum mit einigen Annahmen. Wir haben hier auch einen monatlichen wiederkehrenden Umsatz für neue MRR, verlorene MRR, die Wachstumsrate. Wir werden nicht all diese verschiedenen Metriken verwenden, sondern Ihnen nur eine Vorstellung davon geben, wie das aussieht. Fangen wir an, diese Offset-Funktion zu schreiben. Wir beginnen in Zelle C25, also gleich Offset. Jetzt kann die Offset-Funktion bis zu einer optionalen Anzahl von Argumenten aufnehmen, insgesamt fünf Argumente. In diesem Fall verwenden wir nur die ersten drei Referenzzeilen und -spalten. Erhöhte Breite, die wir später verwenden werden. Aber jetzt gehen wir zum Offset. Wir klicken auf unsere Referenz, nämlich C25. Wir werden es um zwei Zeilen und drei Spalten versetzen. Zwei Zeilen, sehen Sie, wie diese Zeile der zweite Parameter ist. Dann drei Spalten und drücken Sie „Enter“. Schauen wir uns an, was das bewirkt. Zehntausendvierhundertfünfundvierzig ist die Zahl. Denken Sie daran, dass wir dies ab C25 um zwei Zeilen und drei Spalten ausgeglichen haben. Eine grundlegende Offset-Funktion wie diese, alles, was sie tut, ist zwei Zeilen, 1 , 2, und dann drei Spalten nach rechts, 1, 2, 3. So kommen wir in dieser Offset-Funktion zu 10.445. Sie können hier auch negative Zahlen verwenden . Wenn das negativ war zwei. Dies wird von 2.373 nach oben um zwei Zeilen gehen, und negative Spalten gehen rückwärts, wie Sie sich vorstellen können. Dies ist eine grundlegende Methode zur Verwendung von Offsetfunktionen, aber die Offset-Funktion kommt wirklich ins Spiel, wenn Sie einige fortgeschrittenere Modelle erstellen und die Daten oder Durchschnittsdaten zusammenfassen möchten die Daten oder Durchschnittsdaten je nachdem, was Ihre Bedürfnisse sind. Gehen wir zu Nummer zwei. Wir werden unten eine Offset-Funktion schreiben, die Eingaben für den Startmonat und den Endmonat verwendet, diese beiden Zellen hier, um den neuen MRR-Umsatz für einen bestimmten Zeitraum zusammenzufassen . Wir möchten unseren Teamkollegen die Möglichkeit geben, in einer unterschiedlichen Anzahl von Monaten hier teilzunehmen. Dies könnte einer für Januar, zwei für Februar sein, und beachten Sie, wie sich diese Monatszahlen hier oben rechts befinden. Wenn es 3-6 ist, wollen wir alle neuen MRR-Einnahmen von März bis Juni zusammenfassen . Diese Summe sollte hier grundsätzlich 3-6 betragen und die Summe sollte 14.482$ betragen. Lassen Sie uns jetzt anfangen, diese Offset-Funktion zu schreiben . Wir werden beginnen, einen fortgeschritteneren Anwendungsfall der Offset-Funktion zu verwenden . Offset, wir beginnen mit der Referenz, die wieder C25 ist. Wollen wir diese Referenz nun um irgendwelche Zeilen oder Spalten verschieben ? In diesem Fall nein, wir wollen auf dieser Zelle bleiben. Da setzen wir Komma Null, Komma Null. Wir sagen Excel, dass wir diese Referenz nicht um Zeilen oder Spalten verschieben wollen . Jetzt sagen die Höhe und Breite Excel, wie groß eine Auswahl ist. Welchen Bereich möchten Sie in diese Offsetfunktion einfügen? Testen wir zum Beispiel, wenn ich eins als Höhe und fünf als Breite setze , sehen wir uns an, was als Beispiel passiert. Nichts passiert, aber was wir Excel anweisen, ist, alles aus Zelle C25 zu nehmen und eine Breite von fünf Zellen zu nehmen, das sind 1, 2, 3, 4, 5. Was heißt das? Das bedeutet , dass diese Formel derzeit besagt, dass ein Fehler vorliegt, aber das liegt daran, dass sie die Referenz dieser fünf Zellen zurückgibt aber das liegt daran, dass sie die Referenz dieser fünf Zellen zurückgibt . Wenn ich eine Summe um diese Offset-Funktion addiere, erhalte ich die Summe dieser fünf Zellen; 1, 2, 3, 4, 5, 15.279 und entspricht 15.279. Sie können sehen, wie wir diese Offsetfunktion konstruieren können , um sie innerhalb einer Summenfunktion oder einer Durchschnittsfunktion oder was auch immer Sie wollen, zu platzieren oder zu verschachteln einer Summenfunktion oder einer Durchschnittsfunktion oder . Lassen Sie uns diese Offset-Funktion erstellen, um diese Startmonate und Endmonate zu berücksichtigen . Ich werde vorerst die Summenfunktion löschen. Lass uns einfach ein bisschen heranzoomen. Anstatt 0015 hier einzufügen, möchten wir Excel mitteilen ob es einen Startmonat gibt , der größer als eins ist. In diesem Fall beginnt er vielleicht im März, wir wollen tatsächlich die Spalten um einen bestimmten Betrag. Anstelle dieser Null für den Parameter columns wähle ich den Startmonat und setze dann minus eins ein. Der Grund dafür ist, dass, wenn dies drei sind, dies bei C25 beginnt und wir 1, 2, 3 gehen und feststellen, wie dies die Referenz in die Zellenspalte F setzen würde , die Monat 4 ist. Wir werden eigentlich den Startmonat, in dem unsere Teamkollegen und Kollegen in die Zelle eintreten, nehmen wollen den Startmonat, in dem , einen subtrahieren, damit dies die Nummer zwei wird. Dies wird ab C25 auf 1, 2 gehen und ordnungsgemäß in Monat 3 beginnen. Wir sind zu einer Zusammenfassung übergegangen, um in C25 zu beginnen. Wir verschieben Nullen nach unten und wechseln zu drei Spalten, in diesem Fall drei Monate, da wir hier die Zahl drei eingeben, ist die Höhe wie breit oder wie hoch die Höhe von die Reichweite, die wir nutzen wollen. In diesem Fall wollen wir in Reihe 25 bleiben. Hier ist der letzte Parameter, nämlich die Breite. Wie sagen wir Excel grundsätzlich, dass wir es von den Zellen 3-6 wollen? Wir können diese Endmonatsnummer nehmen , die unsere Kollegen eingeben. Alles was wir tun können ist 6 minus 3 und dann plus 1. Der Grund, warum wir das tun wollen, ist , dass die Breite die ersten und letzten Zellen enthalten muss . Wie das aussieht, ist das. Dieser Teil des Parameters entspricht 6 minus 3, was 3 plus 1 ist, was 4 ist. Was Excel tun wird , ist in Zelle E25 zu starten und dann über 1, 2, 3 zu gehen. Dies ist die Breite, die vier, über die wir gerade gesprochen haben, nämlich C11 minus C10 plus 1. Jetzt, da wir unsere Offset-Funktion korrekt geschrieben haben, können wir keine Summe darum setzen. Jetzt haben wir 14.482. Wenn ich drei bis sechs nehme, denken Sie daran Startmonat drei Endmonat ist sechs, 1.442 Ich ändere dies in einen Eins, das ist jetzt eins bis sechs. Das sind jetzt 19.536. Wenn ich dies von sieben auf zwölf ändere, dauert dies von Juli bis Dezember und 36.991. Diese Offset-Funktion ist wirklich interessant da sie Ihnen einen bestimmten Zellbereich zurückgeben kann einen bestimmten Zellbereich , je nachdem, wie Sie die Parameter hier eingeben. Sobald dieser Bereich zurückgegeben wurde, können Sie eine Summenfunktion um diesen oder einen Durchschnitt setzen , und genau das haben wir hier in dieser verschachtelten Summenversatzkombination getan. Lassen Sie uns eine weitere Variante der Summen- und Versatzfunktion erstellen , bei der wir ab Monat 1 einen neuen MRR-Umsatz zusammenfassen werden. Wir werden immer in Monat 1 beginnen, genau hier. Dann legen wir den Endbetrag oder den Endmonat als den Betrag fest, den Sie in Schritt zwei eingegeben haben. Wir werden jeden Monat hier hingehen . Lassen Sie uns das zum Beispiel auf sechs zurücksetzen , und das wäre einer. Im Wesentlichen möchten wir Excel sagen wenn mein Kollege hier eine Sechs eingibt, möchte ich alles von eins bis sechs nehmen. Wenn mein Kollege hier eine Sieben einträgt, möchte ich alles von Monat 1 bis Monat 7 nehmen. Aber wir werden das auf andere Weise aufbauen , indem wir zuerst die Summenfunktion schreiben zuerst die Summenfunktion und den Verweis auf die erste Zelle setzen, die C25 ist, weil wir in Monat 1, Spalte, beginnen wie sagen wir Excel, in welche Zelle es gehen soll, je nachdem, was mein Kollege Endmonat in Zelle C11 eingibt. Normalerweise geben Sie einfach C25 ein, dann etwas Ähnliches und haben eine einfache Referenz. Aber um auf diese Zelle zu verweisen, schreiben wir eine Offset-Funktion. Ich habe Offset nicht richtig geschrieben. Wir beginnen wieder mit C25. Wie viele Zeilen wollen wir dann versetzen? Wir wollen keine Zeilen darunter gehen, also setzen wir dort eine Null. Wie viele Spalten wollen wir dann ausgehend von der neuen MRR ausgleichen? Das wird der Endmonat sein. Wir legen den Endmonat fest. Dies verschiebt die Dinge um 1, 2, 3, 4, 5, 6, 7. Das bringt uns in einen Monat 8, also wollen wir hier eigentlich einen Minus-Eins machen, um diese zusätzliche Bewegung auf den Zellen zu berücksichtigen . Wenn ich das nun in einer abschließenden Klammer setze, ergibt das 24.262. Wenn ich Monat 1 bis Monat 7 hier zusammenfasse, also Monat 1 bis Monat 7, 24.262, 24.262. Dies ist nur eine andere Art, diese Summenfunktion aufzubauen , indem offset als zweite Referenzzelle da wir Excel ab C25 mitteilen , dass sie nach rechts gehen, eine gegebene Anzahl der Spalten, in diesem Fall 7 minus 1. Dieser Teil der Funktion kehrt tatsächlich zur Summenfunktion zurück, diese Zelle genau hier. Wir weisen Excel an, alles von C25 bis H25 zusammenzufassen , indem wir diese Offset-Funktion verwenden , um auf die Zelle zu verweisen. Üben Sie die Verwendung dieser beiden Versionen der Offset-Funktion. Regel verwenden Sie diese Version der Offset-Funktion häufiger mit der Summenfunktion , da Sie normalerweise verschiedene Teile Ihres Modells ausprobieren. Dieser könnte interessant sein, wenn Sie Ihrem Team Inputs geben, wo Daten oder Durchschnittsdaten zusammenfassen möchte. Dies ist jedoch etwas schwierig zu debuggen, wohingegen es sich bei dieser um eine typische verschachtelte Funktion handelt bei der Sie einen Offset innerhalb der Summenfunktion haben. 3. Wähle Szenarien mit OFFSET: Wir werden weiterhin die Offset-Funktion verwenden, diesmal jedoch für eine andere Anwendungsfallszenario-Analyse. Normalerweise verwenden Sie eine Funktion wie die Auswahlfunktion , wenn Sie eine Szenarioanalyse durchführen, aber die Offset-Funktion hat einige Vorteile, da Ihre Teamkollegen und Kollegen neue Bedingungen hinzufügen können oder neue Annahmen zur Szenarioanalyse. Die Offset-Funktion nimmt diese neuen Szenarien automatisch auf, ohne dass Sie die Formeln anpassen müssen. können Sie Ihre Modellierung ein wenig automatisieren diesem Tipp können Sie Ihre Modellierung ein wenig automatisieren. Wir werden dieses SaaS-Wachstumsmodell, das wir in Lektion eins verwendet haben, weiterhin verwenden. Aber beachten Sie, wie die Neukunden stehen, das fehlt jetzt. Wir haben keine Daten außer dem ersten Monat Januar 2020, Lektion eins, wir hatten hier einige fest codierte Zahlen, aber in Lektion zwei werden wir die Offset-Funktion verwenden , um unsere Teamkollegen wählen verschiedene Szenarien mit unterschiedlichen Wachstumsraten aus, um mit einem neuen Kunden vorherzusagen, wie hoch die Beträge sein werden. Dies ist eine sehr gängige Methode, die Sie verwenden, wenn Sie ein Modell erstellen, und um schnell zu verstehen, was wir hier tun werden, geben wir unseren Teamkollegen die Möglichkeit , teilzunehmen hier die Zahlen 1, 2 oder 3. Je nachdem, welche Option sie wählen, entspricht dies unterschiedlichen Annahmen zur Wachstumsrate. Wenn sie einen ausgewählt haben, entspricht dies 10 Prozent. Sie wählen drei aus, das entspricht 20 Prozent. Dann werden diese Zahlen steigen, je nachdem, welche Wachstumsrate hier eingegeben wird. Beginnen wir mit Schritt eins und wir werden eine grundlegende Auswahlfunktion schreiben in Zelle D22 beginnt, die sich genau hier befindet. Wir werden die Wachstumsrate berücksichtigen , die basierend auf dem, was unsere Teamkollegen in das Szenario in Zelle C8 eintreten, ausgewählt wird. Lassen Sie uns hier ganz schnell loslegen und herausfinden, wie die Auswahlfunktion funktioniert. Ich schreibe gleich wählen. Es ist eine sehr grundlegende Funktion. Es nimmt lediglich ein paar verschiedene Parameter auf. Die erste ist, welche Zelle die Nummer enthält , die Sie auswählen werden , oder den Index, den Sie auswählen werden. Das wird unsere Enter-Szenario-Zelle sein, die C8 ist. Sie dann im Wert 1,2,3, Beachten Sie dann im Wert 1,2,3, dass dies alles optionale Argumente sind. Ich wähle D12, dann D13, dann D14. Drücken Sie „Enter“. Ich füge diese Formatierung einfach hier ein. Beachten Sie, dass dies 20 Prozent entspricht, da wir sagen, dass Excel darauf basiert, welche Zahl hier eingegeben wird, die Indexnummer, nehmen Sie diese Zahl aus den Optionen ich in angegeben habe Werte 1,2 und 3. Da dies drei sind, braucht es D14. Wenn ich einen auswähle, gibt es mir die 10 Prozent , weil das der erste Wert ist, den es wählen könnte. Zwei werden natürlich 15 Prozent sein. Jetzt, da wir wissen, wie die Auswahlfunktion funktioniert, können wir diese Auswahlfunktion in der Formel verwenden um neue Kunden in den kommenden Monaten im Jahr 2020 vorherzusagen. Alles was ich tun werde ist gleich dem Vormonat mal Klammern eins plus zu schreiben . Schreiben wir die Funktion noch einmal in der Show. Wir wählen basierend auf den Angaben unserer Teamkollegen und geben dann die verschiedenen Tarife an. Die Wachstumsraten fallen durch Kommas. Dann war's das. Aber wir werden eigentlich wollen, dass ich sichergehen möchte, dass wir es hier tun, Lock-In ist. Das sind alles Verweise, weil wir diese Formel nach rechts ziehen werden. Beachten Sie, dass dies der zweiten Annahme der Wachstumsrate, die 15 Prozent beträgt , um 39 Prozent wächst . Ziehen wir das einfach nach rechts rüber. Sie werden sehen, wie die Zahlen proportional steigen, basierend auf den eingegebenen Wachstumsraten. Ich lege eine hier hin, sie wird weniger schnell wachsen. Ich lege hier eine Drei, um mehr zu wachsen. Auf diese Weise können wir die Auswahlfunktion verwenden, um eine schnelle grundlegende Szenarioanalyse in unserem Modell durchzuführen eine schnelle grundlegende Szenarioanalyse , und unseren Teamkollegen die Möglichkeit geben , die Annahmen hier zu ändern , die sich auf den Rest der Modell für MRR und unser Modell. In Schritt zwei fügen wir den zwei weitere Annahmen zur Wachstumsrate Kundenszenarien zwei weitere Annahmen zur Wachstumsrate hinzu. Machen wir einfach die Funktion, die wir in D22 geschrieben haben, entsprechend. Nehmen wir an, wir möchten einige neue Annahmen hinzufügen , Wachstumsrate, Annahme Nummer vier. Dies wird Option Nummer vier sein. Nehmen wir an, dies ist das Worst-Case-Szenario, in dem wir fünf Prozent setzen werden. Lass uns noch eins machen. kopiere das einfach und ändere die Nummer. Dies wird das optimistischste Szenario sein, das bei 25 Prozent liegt. Lassen Sie mich einfach die Formatierung hier unten auf diese Zahlen kopieren . Wie berücksichtigen wir diese neuen Annahmen, weil unsere Ansicht, erinnern wir uns, unsere ursprüngliche Funktion berücksichtigt nur diese drei. Jedes Mal, wenn Sie neue Annahmen hinzufügen , müssen Sie zu Ihrer Auswahlfunktion zurückkehren und die zusätzlichen Optionen eingeben. Ich habe vier. Ich mache das als Referenz. Dann habe ich diesen fünften . Ist es dieser? Lass mich das nochmal machen. Die fünfte Option ist D16. Sperren Sie das ein und dann, wie ich es zuvor getan habe, muss ich das ganz nach rechts ziehen , um sicherzustellen, dass dies die neuen Raten oder neuen Wachstumsraten berücksichtigt. Wenn ich hier vier setze, sind es fünf, das ist kaputt. Das ist in Ordnung. Sie können jedoch sehen, dass Sie, wenn Sie ein wirklich komplexes Modell haben, die Auswahlfunktion hier ständig anpassen und diese Formeln dann herausziehen müssen, wenn Sie neue Annahmen zur haben, die Auswahlfunktion hier ständig anpassen und diese Formeln dann herausziehen müssen, wenn Sie Wachstumsrate haben. Wenn Sie neue Annahmen zur Wachstumsrate löschen, funktioniert dies ebenfalls nicht mehr. Ich denke, es gibt eigentlich nur Standardwerte auf Null. Beachten Sie, dass dies alle drei Viere sind. Behandelt das Hinzufügen und Löschen von Wachstum oder Annahmen hier in Ihrem Modell nicht wirklich und Löschen von Wachstum oder Annahmen hier in Ihrem Modell , was die Szenarioanalyse sehr schwierig macht , was die Szenarioanalyse sehr schwierig macht, da Sie Ihre Formeln ständig debuggen müssen Ihre Formeln ständig debuggen Berücksichtigung dieses neuen Wachstums oder dieser neuen Annahmen. Für Schritt 3 verwenden wir die Offset-Funktion noch einmal. Anstatt die Auswahlfunktion zu verwenden, verwenden wir die Offset-Funktion und D22 , die das richtige Wachstum oder die richtige Annahme auswählt. Das Beste daran ist, dass es keine Rolle spielt, ob wir Wachstum oder Annahmen zu unserer Liste hinzufügen oder entfernen. Anstatt choose zu verwenden, lösche ich das alles und schreibe Offset. Ich fange in Zelle D11 an. Wenn Sie sich erinnern, wie die Offset-Funktion funktioniert, haben wir hier eine Referenz. Wir werden diese Referenz vorerst einschließen. wie viele Zeilen möchte ich das nun ausgleichen? Nun, wir wissen, dass unsere Teamkollegen diese Szenario-Nummer hier eingeben werden. Ich kann diese Eingabe einfach als Anzahl der Zeilen verwenden , die versetzt werden sollen Ich möchte das sperren damit es sich nicht bewegt, wenn ich es überziehe. Wenn dies fünf ist, geht dies von Rate 1, 2, 3, 4, 5 aus. Wenn es eines ist, geht es nur um eins runter. Die Anzahl der Spalten, die ich darüber versetzen möchte , ist Null, und das sollte einfach funktionieren. Ich glaube, ich habe vielleicht eine schließende Klammer. In diesem Fall muss ich, wenn ich einen hier hinlege , nach rechts ziehen. Schau mal hier, wenn ich hier Nummer eins einfüge, ich setze hier eine Zwei, drei, es macht dasselbe wie die Auswahlfunktion. Aber das Coole hier ist, wenn ich automatisch ein neues Wachstum oder eine neue Annahme hinzufüge, kopiere es einfach und füge es erneut ein. Mach eine Fünf daraus. Das werden 25 Prozent sein. Ich muss diese Offset-Funktion nicht noch einmal anpassen, da sie bereits einen Blick darauf kennt und meine Annahme der Wachstumsrate um so viele Zellen ausgleicht , die in D11 beginnen. Wenn ich fünf hier reinstelle, nimmt sie schon diese neue Zelle auf. Denn was, nur um zusammenzufassen, was das tut, es ist ein Ausgleich von D11 geht fünf Zeilen nach unten, 1, 2, 3, 4, 5, keine Spalten. Es bringt diese Wachstumsrate wieder in meine Formel zurück. Jetzt kann ich schnell Annahmen hinzufügen. Ich kann Annahmen löschen. Nun, das Löschen würde auch nicht wirklich funktionieren, weil ich muss, das wird nur standardmäßig auf Null gesetzt. Aber das Coole ist, dass ich neue Annahmen zur Wachstumsrate hinzufügen kann neue Annahmen zur Wachstumsrate und das Schöne an der Offset-Funktion ist, dass sie diese neuen Wachstumsraten aufnimmt, je nachdem , was meine Teamkollegen oder Kollegen eingehen. Zelle C8. 4. Treffe Entscheidungen W/ IRR und NPV: IRR und NPV stehen für interne Rendite und Kapitalwert. Dies sind Funktionen , die für Sie und Ihre Teamkollegen sehr wichtig sind , da sie Ihnen helfen können verschiedene Investitionen oder Geschäftsmöglichkeiten bei bestimmten Risiken zu bewerten Geschäftsmöglichkeiten bei bestimmten Risiken Rentabilität dieser Möglichkeiten. Auf diese Weise können Sie einen Business Case erstellen , den Sie dem Management oder wem auch immer vorlegen können, welche Gelegenheit Sie verfolgen möchten. Jetzt sind diese Funktionen eigentlich ziemlich einfach zu bedienen, aber es wird wichtig sein, diese Szenarien und die spezifische Umgebung zu kennen , in der sie verwendet werden können. Ich werde in dieser Lektion über all das sprechen. Ziel dieser Lektion ist es, Ihnen einige Formeln und Rahmenbedingungen beizubringen , die Ihnen und Ihren Teamkollegen helfen , herauszufinden, wie Sie verschiedene Anlageentscheidungen bewerten können. Vor allem, wenn Sie in Immobilien oder Maschinen investieren möchten . Hier geht es darum, wie Sie mithilfe von Formeln zusammenarbeiten und herausfinden wie Sie fundierte Geschäftsentscheidungen treffen können. Bevor wir hier auf die Schritte für diese Lektion eingehen, werfen wir hier kurz einen Blick auf einige Annahmen in unserem fiktiven Immobilienmodell. Wir haben hier einige Annahmen. Wir betrachten im Grunde eine Immobilieninvestition und diese spezielle Immobilienimmobilie hat tausend Quadratfuß. Die Miete pro Quadratfuß von 50$. Die Betriebskosten betragen 25 USD pro Quadratfuß. Der Kaufpreis von 10 Millionen US-Dollar. Wir haben hier einige andere Wachstumsraten für die Ertragsaufwendungen und auch die erwartete Obergrenze. Wir haben eine sehr abgekürzte Gewinn- und Verlustrechnung , in der unser jährlicher Bruttoumsatz Beginnend im ersten Jahr und bis zum 11. Jahr. Diese Zahl wächst einfach um die Wachstumsrate, die wir hier bei den Einnahmen haben , die Betriebskosten sind die Quadratfuß multipliziert mit den Betriebskosten pro Quadratfuß. Dies wächst auch jedes Jahr zu einem festen Zinssatz, was uns unser Nettobetriebsergebnis in Zeile 32 einbringt. Dies ist unsere Gewinn - und Verlustrechnung, und hier unten ist unsere Kapitalflussrechnung. Wieder sehr einfach vor dem ersten Jahr. Bevor wir einen Cashflow generieren . Unser erster Bargeldaufwand ist unser Kauf im ersten Jahr, also es negative 10 Millionen $ für diese Investition. Wir treiben diese Immobilie voran und dann ist unser Cashflow aus dem operativen Geschäft einfach der NOI, das Nettobetriebsergebnis jedes Jahr ab Jahr 1, Jahr 2 usw. Dann ist der Nettoliquidität einfach der Nettobetrag. Jetzt werden Sie hier feststellen, dass wir im 10. Jahr unseren NOI haben, aber uns fehlt das Geld, das wir Verkauf der eigentlichen Immobilie erhalten, weil wir möchten es sich lohnt eine Verkäuferimmobilie zu tätigen hoffentlich einen Gewinn erzielen, und diese Zahl fehlt. Unser Ziel ist es, herauszufinden, wie hoch diese Zahl ist, und auch einige andere Formeln und Kennzahlen herauszufinden einige andere Formeln und , um herauszufinden, ob wir in diese Immobilie im Vergleich zu einer anderen Immobilie investieren sollten oder nicht in diese Immobilie im Vergleich zu einer anderen Immobilie investieren . in diesem Immobilienbeispiel. In Schritt 2 fehlt der Cashflow aus dem Verkauf im Casual-Segment aus dem Verkauf im Casual-Segment, und wir gehen davon aus, dass die Immobilie mit einer Immobilie von 10 Millionen US-Dollar im 10. Jahr verkauft wird. Berechnen Sie diesen Wert, indem Sie den NOI selbst durch den Cap-Rate dividieren . Wir haben eine Cap-Rate-Annahme , die bei 10 Prozent liegt. Alles, was wir hier tun müssen, um den Cashflow aus dem Verkauf dieser Immobilie zu berechnen , nachdem 10 Jahre daran gehalten und die Cashflows erhalten haben, ist die Ermittlung des Nettobetriebsergebnisses am Ende des Jahres 10, das ist diese Zahl, 2,392 Millionen. Dann teilen wir das einfach durch den Cap-Satz, um den erwarteten Verkaufspreis zu erhalten. Wir haben einen geschätzten Verkaufspreis von 24 Millionen US-Dollar. Es ist aufgerundet. Dies setzt voraus , dass wir diese erwarteten Cashflows erhalten und sie jedes Jahr wachsen und dass dies bis Ende des 10. Jahres bereits unser erwartetes Nettobetriebsergebnis sein wird, das unser Cashflows für das 11. Jahr. Wenn wir an der Immobilie festhalten würden, verkaufen wir die Immobilie in diesem Fall Ende des 10. Jahres. Unser Bargeld aus dem Eigentum des Verkäufers entspricht dieser von Ihnen berechneten Zahl, die vom Cap-Satz abhängt. Wenn Sie sich unsere Cashflows im Zeitraum der 10 Jahre ansehen. Wir haben negative 10 Millionen für den ersten Kauf der Immobilie. Wir haben dann Cashflows von Jahr 1 bis 10 und am Ende des 10. Jahres verkaufen wir diese Immobilie für 24 Millionen $ oder bar. Am Ende des 10. Jahres sind es 26 Millionen US-Dollar. Es scheint eine ziemlich gute Investition zu sein. Werfen wir einen Blick auf die interne Rendite. Jetzt haben wir eine vollständige Kapitalflussrechnung. Auch dies ist eine sehr einfache Kapitalflussrechnung, aber dies zeigt eher die Leistungsfähigkeit der Verwendung dieser Formeln zur Bewertung verschiedener Investitions- und Geschäftsmöglichkeiten Ihres Unternehmens. könnte konfrontiert sein. Wir berechnen die interne Rendite dieser Investition, indem wir einfach den gleichen IRR oder die interne Rendite ermitteln. Ich kann buchstäblich nur alle Werte aus meinem Nettobetriebsergebnis angeben . Jeder dieser Werte wird in diese IRR-Gleichung eingefügt. Dadurch wird der Abzinsungssatz zurückgespuckt, zu dem mein Nettobarwert meines Cashflows Null ist. Lass uns wieder gleich IRR sein. Mir wurde gerade klar, dass wir die falschen Zahlen verwenden. Wir sollten eigentlich den Nettoabfluss einbeziehen , der 10 Millionen beträgt. Lassen Sie uns all diese Daten einbeziehen. Dort haben wir die interne Rendite von 20,7 Prozent. Ich habe vergessen, dass Sie nicht einfach die jährlichen Cashflows nutzen können. Wir müssen die anfänglichen Ausgaben, die 10 Millionen $ betragen, sowie den endgültigen Verkauf einbeziehen . Ich habe versehentlich das Nettobetriebsergebnis verwendet und sollte das Nettokapital verwenden. Dies ist die interne Rendite dieses Projekts unter der Annahme, und dies wiederum, der IRR, werde ich nicht zu tief interne Rendite eingehen. Dies ist jedoch eine Zahl, die Sie über Ihre verschiedenen Anlagemöglichkeiten hinweg vergleichen können Ihre verschiedenen Anlagemöglichkeiten , um festzustellen, ob dies eine gute Investition ist oder nicht. Wenn ich eine andere Investition mit einem IRR von 25 Prozent finden einem IRR von 25 Prozent würde, würde ich diese Investition natürlich dieser vorziehen, da dies der Abzinsungssatz ist, der erforderlich ist, um den Nettobarwert unserer Cashflows sollen Null sein. Jetzt habe ich hier nur ein wenig über den Nettobarwert gesprochen . Lassen Sie uns ein wenig untersuchen, was der Nettobarwert ist und wie wir ihn mithilfe der NPV-Funktion berechnen können. Wir werden überprüfen, ob dieser Nettobarwert dieser Zahl unter Verwendung dieses IRR Null ist, indem wir die NPV-Funktion verwenden. Ich schreibe einfach gleich NPV und für den ersten Parameter muss ich den Abzinsungssatz [NOISE] eingeben, der die interne Rendite ist, die ich gerade berechnet habe, also 20,7 Prozent, dann muss ich ähnlich wie bei der IRR-Funktion eine Reihe von Werten angeben. Nun, [LÄRM] Ich wähle zunächst einfach alle Cashflows von Jahr 1 bis Jahr 10 aus. Ich zeige dir, wie das aussieht, nachdem wir es gemacht haben. Beachten Sie, dass dies dem Nettobarwert dieser Cashflows entspricht . Annahme eines Abzinsungssatzes von 20,7 Prozent beträgt 10 Millionen US-Dollar. In Wirklichkeit, um zu überprüfen, ob dieser Kapitalwert für diesen Zinssatz Null ist. Sie würden tatsächlich auch in den anfänglichen Bargeldabfluss einfließen, was diesen negativen 10 Millionen entspricht. Wie Sie sich vorstellen können, wird dies Null werden. Dies zeigt im Grunde , dass unser IRR korrekt ist. Es ist jedoch wichtig zu wissen, dass der Nettobarwert all dieser Cashflows Ihren anfänglichen Ausgaben von 10 Millionen US-Dollar entsprechen sollte Ihren anfänglichen Ausgaben . Deshalb werden Sie das als 10 Millionen ansehen, wir berücksichtigen diese negativen 10 Millionen hier nicht in den Kauf im Jahr 0. Aber wenn ich es einschließe setzt es offensichtlich alles auf 0$. Was hier interessant ist, ist wenn Sie diesen Abzinsungssatz ändern, werden Sie feststellen, wie sich auch der Kapitalwert ändern wird. Denken Sie daran, dass der erste Parameter in dieser NPV-Funktion der IRR ist. Nehmen wir an, ich verringere diesen Investitions-IRR auf 15 Prozent. Jetzt ist unser Nettobarwert gestiegen, weil wir einen niedrigeren Abzinsungssatz erwarten um unsere Cashflows auf den Nettobarwert zu diskontieren. Dies ist nicht der richtige IRR für dieses Projekt , da unser Nettobarwert größer als Null ist. Wenn ich dies über den IRR hinaus auf 30 Prozent erhöhe, ist der Nettobarwert der Cashflows natürlich über den IRR hinaus auf 30 Prozent erhöhe, negativ. Seitdem ist der erste Parameter, den wir im Barwert verwenden , in der Tat der IRR. Wenn Sie Anlagemöglichkeiten vergleichen, können Sie in der Regel eine Kombination aus IRR und Nettobarwert verwenden eine Kombination aus IRR und Nettobarwert um den absoluten Dollarbetrag für eine bestimmte Investition zu erhalten. Bringen wir das wieder auf 20,7 Prozent. Wenn ich Anlagen nur anhand ihrer internen Rendite vergleichen würde , werde ich mir nur diese Zahl ansehen. Aber manchmal, wenn Sie verschiedene Opportunities vergleichen, möchten Sie vielleicht auch den absoluten Nettobarwert der Cashflows betrachten absoluten Nettobarwert der , um eine Vorstellung davon zu bekommen, wie viel Geld eingeht. Sie können diese anfänglichen Ausgaben entfernen um zu sehen, wie hoch der gesamte Nettobarwert ist, unter der Annahme verschiedener Abzinsungssätze. 5. Suche W/einfach mit Wildcards: Manchmal wissen Sie nicht genau, was Sie in Ihrem Datensatz zusammenfassen oder nachschlagen oder den Durchschnitt ermitteln möchten. Dann können Sie Platzhalter in Excel verwenden , um eine unscharfe Suche nach Ihren Daten durchzuführen. In dieser Lektion können Sie einen Fuzzy-Suchalgorithmus für Ihre Teamkollegen erstellen einen Fuzzy-Suchalgorithmus für , wenn sie nicht genau wissen, wonach sie suchen, aber ein paar Buchstaben oder etwas im Namen selbst kennen . Die Suche mit Platzhaltern ist eine der am wenigsten genutzten Funktionen in Excel. Der Grund, warum ich es mag, ist, dass Sie und Ihre Teamkollegen wirklich die Möglichkeit haben, nach Daten zu suchen , aber die genaue Schreibweise oder Reihenfolge der Zeichen in der Liste nicht zu kennen die genaue Schreibweise oder . Ich denke, es ist eine viel bessere Art, nach Daten zu suchen , und Sie möchten etwas einrichten , das für Ihre Teamkollegen einfach zu bedienen ist. Nur ein Beispiel: Wir zählen die Anzahl der Adressen darunter, die mit den Buchstaben street, St enden, mithilfe einer COUNTIF-Funktion. Sie sehen diese Adressen unten. Einige von ihnen enden mit Süden, andere enden mit Drive, DR. Wir sehen, dass einige, die St, St St St. haben . Sie könnten eine Variation der Mid- und Find-Funktion machen, was wir getan haben und was ich in früheren Klassen gezeigt habe. Aber Wildcards machen diese Übung wirklich viel einfacher. Sie können für alle verfügbaren Wildcards googeln. Aber ich zeige Ihnen die häufigsten, die ich verwende , wenn ich nach Daten suchen muss , und ich weiß nicht, wie die Daten speziell in der Liste angeordnet sind. Wir werden eine grundlegende COUNTIF-Funktion schreiben, die wir alle schon einmal gemacht haben. Wir gehen hier nur unser Sortiment durch und wählen das aus. Das Kriterium hier ist, dass wir, anstatt bestimmten Wert oder was auch immer gleich zu sein, oder größer oder kleiner als ein Wert, einem bestimmten Wert oder was auch immer gleich zu sein, oder größer oder kleiner als ein Wert, ein doppeltes Anführungszeichen schreiben, dann den Stern St, doppeltes Anführungszeichen. Wir kriegen die Nummer vier. Sie können das schnell beobachten und sehen, dass es vier Adressen mit den Buchstaben Street am Ende gibt. Was hier tatsächlich passiert, ist Excel diese gesamte Liste durchläuft. Dieser Stern sagt im Grunde Excel, mir egal, was vor den Buchstaben St kommt, solange es mit den Buchstaben St. endet Dann möchte ich das in meiner COUNTIF-Funktion zählen. Wenn St irgendwo am Anfang oder in der Mitte steht , zählt es nicht. Dieser Stern teilt Excel im Grunde alle Zeichen, Zahlen, Buchstaben vor St mit, es ist cool. Ich möchte es in meine COUNTIF-Funktion aufnehmen. Sie können hier sehen, wenn ich Rd hier einfüge, es würde nur diese eine bestimmte Adresse mit Buttermilk Road enthalten . Wenn ich Dr. hier reinstelle, dann hat es nur zwei Adressen , die Dr. am Ende haben. Der Star ist einer meiner Favoriten weil es so ist, als würde man eine unscharfe Suche auf der Liste durchführen eine unscharfe Suche auf der Liste wenn man nicht genau weiß, wie die Dinge geschrieben werden. Sie wissen nicht, was vor den Briefen oder nach Briefen kommen wird, aber Sie wissen, dass es ohne Zweifel mit St enden muss, in diesem Fall für die Straße, weil Sie daran interessiert sind in, in Bezug auf Ihren Datensatz. Jetzt können wir mit diesen Wildcards auch andere Funktionen verwenden. Wir werden die Anzahl der Verkäufe für diese Kunden-IDs zusammenfassen . Wir werden uns nur die Kunden ansehen, deren Kunden-ID neun Zeichen enthält mithilfe der SUMIF-Funktion. Sie werden feststellen, dass einige davon 1,2,3,4,5,6,7,8, 9 Zeichen haben, andere jedoch 10 Zeichen, da sie möglicherweise einen zusätzlichen Buchstaben in der Kundennummer enthalten. Wir wollen nur die Verkäufe mit Kunden-IDs mit neun zusammenfassen . Vielleicht sind die Kunden mit neun Kunden-IDs wie die alten Altkunden, ungefähr so. Eine Möglichkeit, das zu tun, ist natürlich, dass wir eine Länge der Zelle machen. Weiß, wie das neun ist und es gibt ein paar von 10. Dann können Sie die Kunden-IDs summieren , die eine bestimmte Nummer haben, in diesem Fall neun. Aber mit den Wildcards möchte ich noch einmal betonen, wie wichtig es ist, in bestimmten Situationen Wildcards zu verwenden . Ist das, dass Sie diese zusätzliche Formel nicht nebenbei machen müssen. Dieser Fragezeichen-Platzhalter teilt mit, dass Excel nur Zellen einschließt, die genau diese Anzahl von Zeichen in den Zellen enthalten. Ich schreibe SUMIF. Der Bereich wird diese Liste von Kunden-IDs sein. das aus Versehen gemacht. Jetzt werden die Kriterien Fragezeichen sein. Auch hier geben Sie die Anzahl der Fragezeichen ein, die Sie Excel mitteilen möchten Hier ist die genaue Anzahl der Zeichen in der Zelle, die ich in meine Kriterien aufnehmen möchte. Wir machen 1,2,3,4,5,6,7,8, 9. Dies sagt, dass Excel nur die Werte zurückzieht, die neun Zeichen haben, nicht 10, nicht acht, nur neun Komma. Der Summenbereich wäre nur der Umsatz und wir haben 14.914. Sie können das noch einmal überprüfen, indem Sie feststellen, wie wir die Zählung hier durchgeführt haben. Wir könnten SUMIF gleich machen, indem wir einfach unsere Arbeit überprüfen. SUMIF, wenn dies neun entspricht, und dann summieren wir das, erhalten wir dieselbe Zahl, 14.914. Mit einer Kombination aus dem Fragezeichen-Platzhalter und dem Stern-Platzhalter können Sie auf diese Weise eine ziemlich leistungsstarke Suche in Ihren Daten durchführen . Ich werde nur googeln, um einige der besten Wildcards, die Sie verwenden können, und die Kombinationen zu sehen . Es gibt einige wirklich coole Kombinationen, die du machen kannst. Aber die Verwendung einer Kombination aus Stern- und Fragezeichen-Platzhaltern ist wahrscheinlich die häufigste, die ich zum Durchsuchen meiner Daten verwende. Um Ihnen zu zeigen, wie dies für Ihr Team funktionieren könnte, haben wir dieselben Daten und jetzt haben wir Adresse plus Kunde plus Umsatz. Wir werden eine unscharfe Suche nach VLOOKUP durchführen. Auf diese Weise können Sie tatsächlich etwas für Ihre Teamkollegen tun, d. h. Ihre Teamkollegen kennen nur den Straßennamen in der Adresse. Sie möchten in der Lage sein, schnell einen Straßennamen einzugeben und zu sehen, wie hoch die Anzahl der Verkäufe für diese angegebenen Straßennamen ist. Sie kennen nur das Wort Buttermilch, oder sie kennen nur das Wort Ross Clark, oder sie kennen nur das Wort Montgomery. Aber sie kennen weder die Adressnummer das Suffix Straße, Laufwerke, Kreis oder was auch immer. Wir werden eine unscharfe Suche schreiben, die es unseren Teamkollegen ermöglicht , so etwas wie sie es bei Google tun könnten, wo sie die vollständige Suchanfrage nicht kennen die vollständige Suchanfrage und etwas Smart zurückerhalten möchten Ergebnisse. Ein Beispiel könnte gleich VLOOKUP sein. Der VLOOKUP-Wert, den wir hier machen werden, ist doppeltes Anführungszeichen, Stern, Buttermilch, Stern, doppeltes Anführungszeichen. Wir werden in diesem Tabellenarray nachschauen, das nur von hier nach hier führt. Wir ziehen die Verkaufszahl zurück, die 3,0 ist. Normalerweise suchen Sie bei einer VLOOKUP immer nach einem bestimmten Wert. Sie möchten ein bestimmtes Spiel durchspielen. Aber in diesem Fall, sage ich Excel, möchte ich alle Adressen zurückziehen , die das Wort Buttermilch enthalten. Es spielt keine Rolle, was davor oder danach kommt. Deshalb habe ich Star vor der Buttermilk und nach der Buttermilk. Das heißt, es ist mir egal, wie die Adressnummer lautet, mir egal, was die Road Street ist. Ich möchte nur wissen , welche Adressen das Wort Buttermilch enthalten , und das kommt hier bei 239 heraus. Auf diese Weise können meine Teamkollegen innerhalb meiner VLOOKUP-Formel erneut eine Fuzzy-Suche durchführen , wenn sie die genaue Adresse 321 Buttermilk Road nicht kennen . Jetzt können Sie dies etwas flexibler gestalten. Ich glaube, man kann das tatsächlich als Referenz machen und ich denke, was man hier tun kann ist eigentlich nur einen Stern zu machen. Nehmen wir an, wir haben hier das Wort Buttermilch. Ich denke, du kannst das tatsächlich löschen und Star machen, diesen Stern. Vielleicht, wenn ich das mit einem Zitat versehen würde. Das funktioniert möglicherweise auch nicht. Ich muss prüfen, wie Sie das machen können. Vielleicht müssen Sie ein Und-Zeichen mit der Zellreferenz machen. So wird es funktionieren. Wenn ich die Suche für meine Teamkollegen flexibler gestalten möchte, könnte ich etwas wie Adresse und Straßenname hier eingeben sagen. Dann können sie Dinge eingeben , wie sie es nur wissen, Montgomery, das ist diese Adresse hier, und sie zieht 1562 zurück, vielleicht kennen sie nur Florenz. Es ist wie eine flexiblere Art, nach Dingen zu suchen , ohne die vollständige Adresse erneut kennen zu müssen. Spielen Sie mit den verschiedenen Wildcards herum. Google, wie Sie sie möglicherweise kombinieren können. Aber ich würde sagen, dass das Fragezeichen, der Stern Platzhalter diejenigen sind , die ich am häufigsten für die Fuzzy-Suche verwende , damit ich meine Tools und Modelle in Excel für meine Teamkollegen flexibler gestalten kann meine Tools und Modelle in Excel für meine Teamkollegen flexibler gestalten . 6. Finde optimale Eingaben W/ Zielsuche: [MUSIK] Wenn Sie ein Modell mit mehreren Eingaben haben, möchten Sie manchmal nur einen Input optimieren , um den Gewinn zu maximieren, Kosten zu minimieren oder vielleicht die Gewinnschwelle in Ihrem Modell oder Ihrem Unternehmen zu finden . Die Funktion Zielsuche eignet sich perfekt dafür, da Sie Excel anweisen können , eine bestimmte Zelle mithilfe einer Eingabe in Ihrem Modell zu optimieren . In dieser Lektion geben wir vor, ein Mitfahrunternehmen wie Uber oder Lyft zu sein , um herauszufinden , was die optimale Anzahl von Fahrten ist, die wir in unserem System haben müssen, um die Gewinnschwelle zu erreichen. Wenn Sie ein Modell entwickeln , möchten Sie manchmal in der Lage sein, den optimalsten Input zu finden , um den Gewinn zu maximieren und die Gewinnschwelle zu finden, ohne eine Menge Versuch und Irrtum durchführen zu müssen . Wir werden uns dieses fiktive Modell ansehen wie ein Mitfahrunternehmen, sagen wir wie Uber oder Lyft, seine Gewinne modellieren könnte. Lassen Sie uns versuchen, schnell zu verstehen, was die D19, diese Gesamtgewinnzahl darstellt. Wir haben hier einige Annahmen, die Anzahl der Fahrten, die dieses Mitfahrunternehmen auf seinem Markt machen muss. Ihre Take-Rate gibt an, wie viel von jeder Fahrt das Mitfahrunternehmen von jedem Fahrpreis nimmt. Die Gesamtmesse pro Fahrt, sagen wir mal, der Durchschnitt liegt bei 15 Dollar. Das bedeutet, dass die Take-Rate für dieses fiktive Unternehmen einfach 25 Prozent mal 15 beträgt, was 3,75 entspricht. Nehmen wir an, sie betreten einen neuen Markt, wie eine neue Stadt, um ihre Mitfahrgelegenheiten auf den Markt zu bringen. Sie könnten feste Marketingkosten von 100.000$ haben, und ihre Kosten pro Fahrt wären einfach ein Gesamtpreis pro Fahrt abzüglich des Take pro Fahrt, was ihrem gesamten Nettogewinn entspricht. Der Gesamtgewinn beträgt hier einfach 100.000, [NOISE] -Investitionen in ihre Marketingbemühungen plus ihre Take-Rate, die ihr Gewinn bei jeder Fahrt ist, multiplizieren Sie ihn mit der Anzahl der durchgeführten Fahrten. Sie können hier sehen, wenn es nur 100 Fahrten gibt, dann haben sie einen Nettoverlust von 99.625. Das steigt, sagen wir mal auf 2.000. Ihr Nettoverlust ist etwas geringer. Was ist der Break-Even-Punkt für dieses Beispiel? Sind es 5.000 Fahrten? Nein, es ist viel zu viel. Bei wenig sind es 50.000? Das ist ein bisschen zu viel. Sie können sehen, wie ich eine Menge Versuch und Irrtum durchführen könnte , um herauszufinden, wie hoch diese Zahl sein sollte, um auf Null zu kommen. Weil ich die Gewinnschwelle der Fahrten ermitteln möchte , die dieser fiktiven Mitfahrgelegenheit eine Gewinnschwelle von 0$ bescheren . Wir passen die Anzahl der Fahrten hier in D10 an und wir passen den Gesamtgewinn an, aber wir wollen in der Lage sein, die genaue Anzahl der Fahrten herauszufinden , um die Gewinnschwelle zu erreichen. Anstatt mit Versuch und Irrtum zu raten, verwenden wir diese Funktion namens Goal Seek, die über eine Funktion verfügt, die Sie für Ihre Modelle verwenden können. Sie können zur Registerkarte „Daten“ gehen, zu „Was-wäre-wenn-Analyse“ gehen und dann zu „Zielsuche“ gehen. Dies funktioniert, wenn Sie nur eine bestimmte Variable maximieren oder optimieren müssen. In diesem Fall wird es der Gesamtgewinn sein. Wir gehen zur Zelle, die die Zelle oder den Wert ist , den wir optimieren möchten, nämlich D19. Wir wollen diesen Wert auf Null setzen , weil das der Punkt sein wird , an dem wir die Gewinnschwelle erreichen. Wie kommen wir zu diesem Null-Break-Even-Punkt? Durch Ändern dieser Eingabe, bei der es sich um die Anzahl der Fahrten handelt. Wir werden weder die Take-Rate noch eine dieser anderen Annahmen ändern , sondern nur die Anzahl der Fahrten, weil unser Modell so aufgebaut ist. Sobald Sie diese Parameter eingestellt haben, klicken Sie auf „Okay“. Sie werden sehen, dass Excel schnell die Lösung entwickelt, die 26.667 Fahrten umfasst , um genau zu sein, um eine Gewinnschwelle von 0$ zu erreichen, wenn wir feste Marketingkosten von 100.000$ haben und Ihr Take 3,75 pro reiten. Wenn Sie hier noch einen hinzufügen, werden Sie sehen, dass dieser Nettogewinn jetzt 5 USD beträgt, also etwas zu hoch ist. Dies ist eine wirklich interessante Methode, um entweder zu versuchen, den Break-Even-Punkt zu finden, oder vielleicht möchten Sie die Gesamtzahl der Fahrten ermitteln, um einen Gewinn von 1 Million US-Dollar zu erzielen. Wir könnten das sehr schnell versuchen indem wir zu „Daten“, „Was-wäre-wenn-Analyse“, „Zielsuche“ gehen. Wir können wieder die Zelle D19 setzen. Anstatt es auf Null zu setzen, setzen wir es auf eine Million indem wir die Zelle D10 ändern , was der Anzahl der Fahrten entspricht. Mal sehen, wie viele Fahrten es braucht, um einen Gewinn von 1 Million US-Dollar zu erzielen, und das sind 293.333 Fahrten. Wenn Sie mit Ihrem Team zusammenarbeiten und versuchen, gemeinsam eine optimale Lösung zu finden, anstelle von Versuch und Irrtum können Sie anstelle von Versuch und Irrtum diese erweiterte Datenfunktion verwenden, nämlich Goal. Suchen Sie, um die optimalen Annahmen oder Inputs zu finden, um Gewinne zu maximieren , Gewinnschwelle und andere Kennzahlen zu finden, die Sie für Ihr Team erreichen möchten. 7. Komplexe Optimierungen W/ Solver: Aufbauend auf der vorherigen Lektion, in der wir Goal Seek verwendet haben, um eine Eingabe zu optimieren und eine Ausgabe zu maximieren, haben Sie manchmal fortgeschrittenere Anwendungsfälle, und Ihre Teamkollegen sagen Ihnen, dass es andere Einschränkungen gibt . in Ihrem Modell , das Sie berücksichtigen müssen. Wenn Sie mehrere Variablen und mehrere Abhängigkeiten haben , die Sie bei der Suche nach den optimalen Eingaben für Ihr Modell berücksichtigen müssen Suche nach den optimalen Eingaben für Ihr Modell berücksichtigen , können Sie die erweiterte Solver-Funktion in Excel verwenden welches ist ein Ad-In. Wir werden so tun, als wären wir immer noch dieses Mitfahrunternehmen, und wir müssen mehrere Einschränkungen und mehrere andere Variablen berücksichtigen, um unseren Gewinn für unsere firma. Für fortgeschrittenere Modelle, bei denen Sie mehrere Einschränkungen haben , unter denen Sie arbeiten müssen, und Sie eine Reihe verschiedener Eingaben maximieren möchten, können Sie die Zielsuchfunktion nicht verwenden, über die wir bereits gesprochen haben. Ungefähr in Lektion fünf müssen Sie eine erweiterte Funktion namens Solver verwenden . Bevor wir hier mit der Verwendung von Solver auf unserem Modell beginnen, nehmen wir uns zunächst hier mit der Verwendung von Solver auf unserem Modell beginnen, in Schritt 1 einen Moment Zeit, um zu verstehen , wie das Modell aussieht. Auch hier haben wir am Beispiel unseres Mitfahrunternehmens einige Annahmen : Die Take-Rate liegt bei 25 Prozent. Jetzt gehen wir davon aus die Gesamtzahl der zuweisbaren Fahrer die Gesamtzahl der Fahrer ist , die Uber oder Lyft oder was auch immer zur Verfügung haben, um in ihrem Unternehmen zu arbeiten bieten Fahrten für Kunden an. Es gibt insgesamt 300.000 Fahrer, die sich für unsere Mitfahrgelegenheit angemeldet haben , und die Gesamtzahl der Fahrten, die jeder Fahrer pro Tag unternehmen und die Gesamtzahl der Fahrten, die kann, beträgt 10. Dies sind nur drei Annahmen , die wir in unserem Modell haben. Wenn wir uns nun diese Liste von Städten ansehen, sind dies potenzielle Städte , in denen unser Mitfahrunternehmen möglicherweise tätig sein möchte. In dieser Spalte werden die pro Tag angeforderten Fahrten Dies ist die maximale Anzahl von Fahrten, die für diesen Tag angefordert werden. Wenn wir von 10 Fahrten pro Fahrer und Tag ausgehen, bedeutet das, dass die in jeder dieser Städte benötigten Fahrer einfach die pro Tag angeforderten Fahrten geteilt durch 10 sind. Dies ist die Gesamtzahl der benötigten Treiber. Die Gesamtzahl beträgt tatsächlich 386.000, was mehr ist als die Anzahl der zuweisbaren Fahrer, die wir für alle unsere Kunden haben. Wir haben hier unser Problem mit Angebot und Nachfrage. Wir haben auch diese Eingaben des Durchschnittspreises pro Stadt. Sie können sehen, dass dies in absteigender Reihenfolge sortiert ist. Nun, es ist in absteigender Reihenfolge sortiert. New York hat den höchsten Durchschnittstarif, wobei Mumbai der niedrigste ist. Wenn wir von dieser 25-prozentigen Take-Rate ausgehen, was dem Gewinn pro Fahrt unseres Mitfahrunternehmens entspricht, nehmen wir einfach 25 Prozent, multiplizieren sie mit dem Durchschnittspreis, und dann können wir den Gewinn pro Fahrt erzielen. Jetzt ist es unser Ziel, die Anzahl der hier zugewiesenen Fahrer einzugeben , um den Gesamtgewinn zu maximieren. Schauen Sie hier, wenn ich hier 100 setze, erhalten wir einen Gewinn von 469, was einfach dem 100-fachen Gewinn pro Fahrt entspricht. Wenn ich hier weitere 100 setze, erhalten wir 336, weil der Fahrpreis niedriger ist, und wir erzielen hier einen Gesamtgewinn, 805. Wenn Sie dies von Hand oder manuell tun würden, würden Sie sich die Einschränkungen in diesem Modell ansehen , das heißt, wir wissen, dass wir hier nur 45.000 Treiber benötigen. Eine Versuchung besteht darin, zu sagen: Nun, wenn wir 300.000 Fahrer zur Verfügung haben, warum bringen wir sie nicht einfach alle in die Stadt mit dem höchsten Tarif und wir können einen Gesamtgewinn von 1,4 Millionen erzielen? Nun, das Problem ist, dass wir in New York keine 300.000 Fahrer brauchen , wir brauchen nur 45.000. Die maximale Anzahl von Fahrern, die wir für New York in diesen Markt bringen können , beträgt einfach 45.000. Dies ist der maximale Gewinn, den wir aus New York erzielen können , der 210.000 beträgt. Jetzt könnten Sie die Linie entlang gehen und sehen, okay, nun, unsere nächsthöhere profitable Stadt sieht tatsächlich so aus, als würde es London sein, 4,39. Wir werden versuchen, die Anzahl der Fahrer, die wir in dieser Stadt haben wollen, zu maximieren die Anzahl der Fahrer, die wir in dieser Stadt haben wollen , das sind 37.400. Dann sieht die nächsthöhere profitable Stadt so aus, als würde Sao Paulo sein, also könnten wir hier 20.000 machen. Wir können die Liste einfach weiter durchgehen, bis wir eine maximale Anzahl von zugewiesenen Fahrern erreicht haben, was hier unsere Einschränkung sein wird, nämlich 300.000. Dies wird offensichtlich sehr langsam sein, insbesondere bei einer langen Liste von Städten. Aber ich möchte das nicht manuell tun, indem ich jede Stadt durchsuche und herausfinde , welche Stadt den größten Gewinn für meine Mitfahrgelegenheit erzielt, und dann versuche, die maximal verfügbare Anzahl einzugeben Fahrer mussten den Gewinn maximieren. Wir wollen herausfinden, wie hoch unser maximaler Gewinn angesichts dieser Einschränkungen im System ist . Nachdem wir dieses Modell sehr schnell verstanden haben, schauen wir uns Schritt Nummer Zwei an. Wir haben das eigentlich nur ein bisschen gemacht. Wir haben Versuch und Irrtum durchgeführt und Treiber zur zugewiesenen Spalte des Fahrers hinzugefügt um zu sehen, wie sich dies auf den Gesamtgewinn auswirkt. Wir stellten schnell fest , dass wir nicht einfach 300.000 Fahrer der profitabelsten Stadt zuweisen können , weil wir in New York keine 300.000 Fahrer brauchen. Wir brauchen maximal 45.000 . Anstatt dies manuell zu tun, eine Stadt nach der anderen, um unseren Gewinn zu maximieren, verwenden wir die Solver-Funktion. Wenn Sie Solver noch nicht in Ihrem Excel haben, wird dies im Menü Daten angezeigt, und Sie sehen Solver hier. Wenn Sie das nicht haben, klicken Sie in Ihrem Excel auf das Menü „Extras“ Es befindet sich hier tatsächlich außerhalb des Fensters, aber Sie sollten eine Registerkarte Tools in Ihrem Excel haben. Gehen Sie dann zu Excel-Add-Ins, und Sie sollten das Solver-Add-In genau hier sehen. Wenn Sie es nicht sehen, ziehen Sie es einfach hier aus und klicken Sie auf „Okay“. Dann wird Solver auf der Registerkarte Daten im Menüband angezeigt . Jetzt klicken wir auf die Registerkarte „Solver“, um die optimale Anzahl von Treibern zu berechnen , die in die Spalte „ Zugewiesene Treiber“ eingefügt werden sollen. Bevor wir nun in das Solver-Menü kommen, möchten wir uns hier einige Dinge ansehen , um herauszufinden, wie das eingerichtet wird. Wir wollen das Ziel auf den Gesamtgewinn setzen , der so sein wird. Nun, aber ändern Sie die Anzahl der zugewiesenen Treiber, das sind diese Zellen genau hier. den Einschränkungen gehören die zugewiesenen Treiber kleiner oder gleich den benötigten Treibern sein müssen, was einfach dieser Zahl entspricht und weniger als 300.000 betragen muss. Die Gesamtzahl der zugewiesenen Fahrer muss ebenfalls kleiner oder gleich den zuweisbaren Fahrern sein. Das bedeutet, dass diese Zahl nicht größer als diese Zahl sein kann, und auch diese Zahlen müssen alle ganze Zahlen sein. Das ist viel Gerede. Schauen wir uns einfach an, was das bedeutet , wenn wir das eigentliche Solver-Menü verwenden. Dies wird vielleicht etwas klein auf dem Bildschirm angezeigt, aber lassen Sie uns das nacheinander durchgehen. Wir haben ein gewisses Ziel. Wir wollen dieses Ziel , Zelle I28, das ist unser Gesamtgewinn aller unserer Städte, nachdem wir Fahrer zugewiesen haben. Wir wollen diese Zahl einfach maximieren. Wir können das auf eine Zahl setzen. In diesem Fall wollen wir nur die optimale maximale Anzahl finden , die wir verwenden können die wir angesichts der Einschränkungen im Modell berechnen können. Jetzt haben wir uns zum Ziel gesetzt, I28 zu sein, was unser Gesamtgewinn ist, und wir müssen Excel mitteilen, welche Labels wir ziehen werden um diesen maximalen Gewinn zu erzielen. Das wird genau dieses Formular sein , das variable Zellen verändert. Es wird von H17 bis H27 sein. Dies sind die Treiber, die wir jeder Stadt zuweisen möchten . Wir werden all diese Zellen ändern oder besser gesagt, Solver wird alle Zellen mithilfe des Algorithmus ändern . Jetzt werden wir die Einschränkungen in unserem Modell einführen. Dies ist der wichtigste Teil von Solver, da wir Excel mitteilen müssen, mit welchen Einschränkungen wir arbeiten, um diesen maximalen Gewinn zu erzielen. Fangen wir an, Einschränkungen hinzuzufügen. Die erste Einschränkung, über die wir gesprochen haben ist die Anzahl der zugewiesenen Fahrer. Dies muss kleiner oder gleich der Gesamtzahl der benötigten Treiber sein. Denken Sie daran, wir können nicht mehr als 45.000 Fahrer in New York einsetzen , mehr als 35.000 in Los Angeles. Das sagen Sie, H17 bis H27 müssen kleiner oder gleich E17 bis E27 sein. Wir werden sie zur Einschränkung hinzufügen. Wir sagen, dem Fahrer wurde hier diese Gesamtzahl zugewiesen, die alle in dieser Spalte zugewiesenen Fahrer zusammenfasst Sie muss kleiner oder gleich der Gesamtzahl der zuweisbaren Treiber sein Treiber, das sind 300.000. Wenn wir uns erinnern, brauchen wir in unserer Gesamtmarktanalyse mehr Treiber als wir tatsächlich haben, also müssen wir herausfinden, wie wir diesen kleinen Pool von 300.000 Treibern am besten optimieren und hinzufügen können. Die letzte Einschränkung ist, dass diese Zahlen, Excel dies durch einen Algorithmus setzen wird. Wir wollen sicherstellen, dass es keine Brüche oder Dezimalzahlen gibt, denn es gibt keinen halben Fahrer, Drittel eines Fahrers. Wir werden sagen, dass diese Zahlen alle ganze Zahlen sein müssen. Ich sage nur, dass dies eine Ganzzahl ist. Wir haben diese drei Einschränkungen in unserem System, klicken Sie auf „Okay“. Jetzt ist unser Solver-Menü so ziemlich fertig eingerichtet. Wir haben unser Ziel, wir haben die Zellen, die wir ändern, nämlich die Anzahl der zugewiesenen Fahrer. Dann haben wir diese drei Einschränkungen. Nun ein paar Dinge hier, die wir uns ansehen wollen. Sie möchten das wahrscheinlich abhaken und nicht eingeschränkte Variablen nicht negativ machen. Das bedeutet einfach , dass diese Variablen , die wir ändern, über Null liegen müssen. Es macht nicht wirklich Sinn, negative 500 Fahrer zugewiesen zu haben , also wollen wir das abhaken. In der Regel sollten Sie die lineare GRG-Lösungsmethode oder den linearen Algorithmus verwenden . Dies behandelt die meisten Situationen, lineare und nichtlineare Situationen. Belassen Sie das als Standard. Sie können hier auf „Optionen“ klicken. Es gibt eine ganze Reihe anderer Optionen, die Sie ausführen können, wie Integer-Einschränkungen ignorieren. Wir wollen das natürlich unter Kontrolle halten, weil wir diese Einschränkungen beibehalten wollen. Ein GRG-Linear, verwenden Sie Multistart, Sie klicken darauf, um genauere Antworten zu finden. Aber normalerweise, wenn Sie in diesem Modell so sprechen, können Sie alle Menüpunkte einfach so lassen, wie sie sind , und klicken Sie auf „Okay“. Wir haben unseren Solver eingerichtet. Versuchen wir herauszufinden, ob Solver die Gesamtzahl der Fahrer ermitteln kann , die für jede Stadt zugewiesen werden müssen. Drücke auf „Lösen“ und wow, das ging ziemlich schnell. Solver hat eine Lösung gefunden. Wir klicken auf „Okay“. Klicken wir einfach auf „Schließen“ und schauen wir uns an, was Solver sich ausgedacht hat. Wie erwartet wurde versucht, die Städte mit dem größten Gewinn wie New York zu maximieren , es wurden 45.000 dort platziert, 35.000 Los Angeles. Das sind alle das Maximum. Aber Sie können sehen, dass Sao nur 33.600 Fahrer zugewiesen wurden, wenn Sie in die Städte mit niedrigerem Gewinn kommen , wenn Sie in die Städte mit niedrigerem Gewinn . Es hieß tatsächlich, dass wir in Hongkong keine Fahrer und in Mumbai Null Fahrer einsetzen in Hongkong keine Fahrer und in Mumbai Null Fahrer werden, weil dies unsere am wenigsten profitablen Städte sind, um diese maximale Anzahl von Fahrern auf nur 300 zu halten . 000, denen wir hier die Finanzierungsübernahme haben. Auf diese Weise können Sie Solver in Ihrem Team einsetzen, um im Grunde ein komplexes Optimierungsproblem zu lösen , bei dem Sie mehrere Einschränkungen haben und hier mehrere Hebel oder mehrere Zellen ändern, die diese Zellen, und du hast eine Nummer, die du treffen willst. Es könnte ein Maximum sein, es könnte ein Minimum sein, es könnte ein bestimmter Wert sein. In diesem Fall wissen wir, dass angesichts unserer Einschränkungen in unserem System von 300.000 Treibern und dieser benötigten Treiber der Gesamtgewinn, den wir in diesem Szenario erzielen können , etwas mehr als 1 Million US-Dollar beträgt. diese Weise können Sie Solver verwenden, eine viel erweiterte Funktion in Excel um zu verhindern, dass Sie Ausprobieren und dies manuell durchgehen und Solver einfach die Mühe machen müssen . heben, um diese Zahlen zu finden. 8. Schlussgedanken: Vielen Dank, dass Sie an diesem Kurs über erweiterte Excel-Features und -Funktionen teilgenommen haben. Ich hoffe, Sie verfügen über kritischere und analytischere Denkfähigkeiten, wenn es um die Auswertung und Analyse Ihrer Daten in Excel geht. Der wichtigste Teil einiger dieser Merkmale und Funktionen in Excel ist die Kenntnis der spezifischen Anwendungsfälle und Nischenszenarien , in denen sie verwendet werden können. Wenn Sie sie jedoch lernen, können Sie einen Business Case erstellen, um Management oder der C-Suite zurückzugeben ob Sie in ein bestimmtes Geschäft oder eine bestimmte Investitionsmöglichkeit investieren möchten oder nicht . Bitte posten Sie Ihre Fragen und Gedanken im Diskussionsforum der Klasse. Gerne gebe ich dir Feedback und mache auch Screenshots deines Klassenprojekts, während du im Projektbereich dieser Klasse daran arbeitest . Ich hoffe, wir sehen uns in einer meiner anderen Excel-Klassen hier auf Skillshare.