Das Video kommt von YouTube: erst beim Abspielen verbindet sich die Seite mit YouTube (Google).
Sensitivity & Scenario Analysis Model
Das Wichtigste aus dem Video
Tipp auf eine Zeit – das Video springt genau dorthin.
Transkriptautomatisch erstellt · 44 Zeilen
- scenario and sensitivity analysis model let's look at the components of the scenario and sensitivity model it has everything in the three statement model
- plus everything in the DCF model and then on top of that is got additional sets of assumptions so that we can run multiple scenarios through the model the
- scenarios will be selected using a choose formula we'll also layer on to that a direct and indirect method of performing sensitivity analysis we can
- then use data tables and goal seek to see how the model performs under different scenarios and different assumptions let's look at the purpose of
- this type of model it helps evaluate multiple economic and operational scenarios it lets us assess the upside and downside or risk and reward of
- different strategic alternatives that a company may have it helps us measure how sensitive a model is as inputs change how much do outputs like free cash flow
- or share price move around as the drivers of the model change and finally instead of arriving in just one value for what we think the company is worth
- we can arrive at a reasonable range of values that we think are likely for the company to be between if you want to learn how to build this model from
- scratch you can take the scenario and sensitivity analysis course here we are inside the scenario and sensitivity analysis model we have a cover page as
- always describing what the model contains and on the table of contents we can click through to the actual model itself we've got all the sections as we
- always do in a model starting with assumptions then the three financial statements supporting schedules and additional calculations then the DCF
- model and finally the sensitivity analysis let's open up all the sections here one thing you'll notice right away is that
- unlike the basic models that only have one set of assumptions this one has two distinct scenarios and we can switch between the scenarios by using a toggle
- here to type in either one or two the number that we enter there flows down into the live case let's switch this back to scenario 1 for sensitivity
- analysis purposes scenario 1 only uses a single revenue growth rate assumption whereas scenario 2 has different revenue growth rates in different years we'll
- explain why that matters later but for now we're leaving it on scenario 1 which is flowing through the model beneath that we get the three financial
- statements income statement balance sheet and cash flow we get the supporting schedules and then we can have our DCF analysis these assumptions
- here drive the DCF model and we arrive at the company's intrinsic value per share which we can compare to the market value per share and thus calculate the
- internal rate of return of buying the stock at its current market price below that we have the sensitivity analysis section we can see what happens to the
- share price of the company as we have different revenue growth rate assumptions and different terminal value assumptions for the ev2 ebody exit
- multiple so this is the range of share prices from lowest in the top left corner to highest in the bottom right corner and you can see that is quite a
- wide range stocks are very sensitive to growth rates and to valuation multiples then below that we have some calculations here that drive the tornado
- chart below the tornado chart is showing us visually how sensitive the share prices to changes in underlying assumptions so we can see that revenue
- growth really moves the share price as well as the cost of goods sold EVD yada exit' multiple and the discount rate so we're going to show you in the following
- lesson some examples of how all of this flows through if you want to learn how to perform this analysis step by step every calculation then you can take the
- scenario and sensitivity analysis course let's take a closer look at how this model functions the scenario is chosen using a choose function that links to a
- switch in the switch we can type in number 1 or 2 and based on the number that's in that cell it will then pick option number one or
- option number two and place it in the live scenario so we've structured the model to have this live scenario category that mirrors the way the
- scenarios are set up so all that happens here is you're bringing forward whatever scenario you want to run through the model then all of the statements are
- linked to the live scenario and nothing is directly linked to each of these individual scenarios this is by far the easiest and most effective way to set up
- scenarios and scrolling down to the sensitivity analysis you'll see that we have two different kinds the first kind here is what we call direct sensitivity
- analysis and the way direct sensitivity analysis works is it takes the numbers that we have in this table here such as these different revenue growth rates and
- these different exit multiple assumptions and it plugs these assumptions directly into the model hence the term direct sensitivity
- analysis so it directly takes whatever revenue growth rate assumption it is and it plugs it in to this cell right here
- so that's how these data tables are working and then below that we have what we call the indirect method which is these numbers here in the way that these
- indirect methods work is we'll show you using formulas and trace dependence so what happens is that number that's in that cell gets pushed up to the live
- revenue number and you can see that the reference is actually contained in the formula here so we're saying that the revenue growth is going to be equal to
- the prior year multiplied by the revenue growth rate in the scenario plus some other variable so it's sort of indirectly impacting the revenue growth
- and it's set to zero initially because we don't want it to affect the live case in the model but what happens is Excel then takes this number and this number
- and this number and plugs them in as discrete scenarios and we can see what happens to the share price so as you'd expect of course when
- it's a zero there's no impact when the revenue growth is 5% less than the live scenario the share price goes down and when it's higher the share price goes up
- of course the same thing happens with cost of goods sold the discount rate that's used for calculating the share price and the eggs and multiple
- assumption and those then flow down to populate this tornado chart so as you can see this is a very powerful setup for performing extensive sensitivity and
- scenario analysis in a DCF model please refer to the full sensitivity analysis course to learn how to build it step by step
Zum Nachlesen
SensitivitätsanalyseZiel der Sensitivitätsanalyse ist es also zu ermitteln, welchen Einfluss die Varianz der Eingangsvariablen auf die Varianz der Ausgangsvariablen hat.
InvestitionsrechnungEine Investitionsrechnung ist eine Kalkulation, mit der geprüft wird, ob eine Investition wirtschaftlich sinnvoll ist. Abzinsung (Beispielhafte Übersicht).
KapitalwertDer Kapitalwert ist der Barwert aller mit der Investition verbundenen Ein- und Auszahlungen über den zu erwartenden Nutzungszeitraum der Investition. Die …
Cash Flow Return on InvestmentDer Bruttocashflow (BCF) soll die Ertragskraft des Unternehmens abbilden, weniger die Finanzkraft. · Die Bruttoinvestitionsbasis (BIB) ist das bis zu einem …