Sensitivitetsanalyse i Excel (eksempel på dataark)

Prosesser innen finans henger alltid sammen – en faktor avhenger av en annen og endrer seg med den. Spor disse endringene og forstå hva du kan forvente i fremtiden, kanskje ved å bruke Excel-funksjoner og regnearkmetoder.

Få flere resultater med en datatabell

Dataarkfunksjoner er elementer i hva-hvis-analyse – ofte utført gjennom Microsoft Excel. Dette er det andre navnet for sensitivitetsanalyse.

Oversikt

En datatabell er en type celleområde som kan brukes til å løse problemer ved å endre verdiene i noen celler. Den opprettes når det er nødvendig å holde styr på endringer i komponentene i formelen og motta oppdateringer til resultatene, i henhold til disse endringene. La oss finne ut hvordan du bruker datatabeller i forskning, og hvilke typer de er.

Grunnleggende om datatabeller

Det er to typer datatabeller, de er forskjellige i antall komponenter. Du må kompilere en tabell med fokus på antall verdier uXNUMXbuXNUMXb som du må sjekke med den.

Statistikere bruker en enkelt variabeltabell når det bare er én variabel i ett eller flere uttrykk som kan endre resultatet. For eksempel brukes den ofte sammen med PMT-funksjonen. Formelen er utformet for å beregne beløpet for den vanlige betalingen og tar hensyn til renten spesifisert i avtalen. I slike beregninger skrives variablene i én kolonne, og resultatene av beregningene i en annen. Et eksempel på et dataskilt med 1 variabel:

Deretter vurderer platene med 2 variabler. De brukes i tilfeller der to faktorer påvirker endringen i en indikator. De to variablene kan havne i en annen tabell knyttet til lånet, som kan brukes til å bestemme den optimale nedbetalingstiden og størrelsen på den månedlige betalingen. I denne beregningen må du også bruke PMT-funksjonen. Et eksempel på en tabell med 2 variabler:

Opprette en datatabell med én variabel

Vurder analysemetoden ved å bruke eksemplet med en liten bokhandel med bare 100 bøker på lager. Noen av dem kan selges dyrere ($50), resten vil koste kjøpere mindre ($20). Den totale inntekten fra salg av alle varer er beregnet – eieren bestemte at han ville selge 60 % av bøkene til en høy pris. Du må finne ut hvordan inntektene vil øke hvis du øker prisen på et større varevolum – 70 %, og så videre.

Vær oppmerksom! Den totale inntekten må beregnes ved hjelp av en formel, ellers vil det ikke være mulig å sette sammen en datatabell.

1. Velg en ledig celle vekk fra kanten av arket og skriv formelen i den: =Celle av total inntekt. For eksempel, hvis inntekt er skrevet i celle C14 (tilfeldig betegnelse er angitt), må du skrive dette: =S14.
2. Vi skriver prosentandelen av varevolumet i kolonnen til venstre for denne cellen - ikke under den, dette er veldig viktig.
3. Vi velger celleområdet der prosentkolonnen og koblingen til den totale inntekten er plassert.

4. Vi finner på "Data"-fanen elementet "Hva om analyse" og klikker på det - i menyen som åpnes, velg alternativet "Datatabell".

5. Et lite vindu åpnes der du må spesifisere en celle med prosentandelen av bøker som opprinnelig ble solgt til en høy pris i kolonnen "Erstatt verdier med rader i ...". Dette trinnet gjøres for å beregne den totale inntekten på nytt under hensyntagen til den økende prosentandelen.

Etter å ha klikket på "OK"-knappen i vinduet der dataene ble lagt inn for å kompilere tabellen, vil resultatene av beregningene vises i linjene.

Legge til en formel til en enkelt variabeldatatabell

Fra en tabell som hjalp til med å beregne en handling med bare én variabel, kan du lage et sofistikert analyseverktøy ved å legge til en ekstra formel. Den må skrives inn ved siden av en allerede eksisterende formel – hvis for eksempel tabellen er radorientert, legger vi inn uttrykket i cellen til høyre for den eksisterende. Når kolonneorienteringen er satt, skriver vi den nye formelen under den gamle. Følg deretter algoritmen:

1. Velg celleområdet igjen, men nå skal det inkludere den nye formelen.
2. Åpne "hva hvis"-analysemenyen og velg "Dataark".
3. Vi legger til en ny formel i det tilsvarende feltet i rader eller kolonner, avhengig av platens orientering.

Lag en datatabell med to variabler

Starten på en slik tabell er litt annerledes - du må sette en lenke til den totale inntekten over prosentverdiene. Deretter utfører vi disse trinnene:

1. Skriv prisalternativer på én linje med en lenke til inntekt – én celle for hver pris.
2. Velg et celleområde.

3. Åpne datatabellvinduet, som når du kompilerer en tabell med én variabel – gjennom "Data"-fanen på verktøylinjen.
4. Erstatt i kolonnen "Erstatt verdier etter kolonner i ..." en celle med en innledende høy pris.
5. Legg til en celle med den første prosentandelen av salg av dyre bøker i kolonnen "Erstatt verdier med rader i ..." og klikk "OK".

Som et resultat er hele tabellen fylt med mengder av mulig inntekt med forskjellige betingelser for salg av varer.

Fremskynde beregninger for regneark som inneholder datatabeller

Hvis du trenger raske beregninger i en datatabell som ikke utløser en omberegning av hele arbeidsboken, er det et par ting du kan gjøre for å fremskynde prosessen.

1. Åpne alternativvinduet, velg elementet "Formler" i menyen til høyre.
2. Velg elementet "Automatisk, bortsett fra datatabeller" i delen "Beregninger i arbeidsboken".

3. La oss beregne resultatene i tabellen på nytt manuelt. For å gjøre dette, velg formlene og trykk på F-tasten.

Andre verktøy for å utføre sensitivitetsanalyse

Det er andre verktøy i programmet som hjelper deg med å utføre sensitivitetsanalyse. De automatiserer noen handlinger som ellers måtte gjøres manuelt.

1. Funksjonen "Parametervalg" er egnet hvis ønsket resultat er kjent, og du må kjenne inngangsverdien til variabelen for å oppnå et slikt resultat.
2. "Søk etter en løsning" er et tillegg for å løse problemer. Det er nødvendig å sette grenser og peke på dem, hvoretter systemet finner svaret. Løsningen bestemmes ved å endre verdiene.
3. Sensitivitetsanalyse kan gjøres ved hjelp av Scenario Manager. Dette verktøyet finner du i hva-hvis-analysemenyen under fanen Data. Den erstatter verdier i flere celler – tallet kan nå 32. Avsenderen sammenligner disse verdiene slik at brukeren ikke trenger å endre dem manuelt. Et eksempel på bruk av script manager:

Sensitivitetsanalyse av et investeringsprosjekt i Excel

Hva-hvis-analyse er spesielt nyttig i situasjoner der prognoser er nødvendig, for eksempel investering. Analytikere bruker denne metoden for å finne ut hvordan verdien av et selskaps aksjer vil endre seg som følge av endringer i noen faktorer.

Metode for analyse av investeringssensitivitet

Når du analyserer «hva hvis», bruk oppregning – manuell eller automatisk. Utvalget av verdier er kjent, og de erstattes i formelen én etter én. Resultatet er et sett med verdier. Velg riktig nummer fra dem. La oss vurdere fire indikatorer som sensitivitetsanalyse utføres for innen finans:

1. Netto nåverdi - Beregnes ved å trekke investeringens beløp fra inntektsbeløpet.
2. Internrente / fortjeneste - indikerer hvor mye fortjeneste som kreves for å mottas fra en investering i løpet av et år.
3. Tilbakebetalingsforholdet er forholdet mellom all fortjeneste og den opprinnelige investeringen.
4. Rabattert fortjenesteindeks – indikerer effektiviteten til investeringen.

Formel

Innebyggingsfølsomhet kan beregnes ved å bruke denne formelen: Change in output parameter in % / Change in input parameter in %.

Utgangs- og inngangsparametrene kan være verdiene beskrevet tidligere.

1. Du må vite resultatet under standardforhold.
2. Vi bytter ut en av variablene og overvåker endringene i resultatet.
3. Vi beregner prosentvis endring av begge parametere i forhold til de etablerte forholdene.
4. Vi setter inn de oppnådde prosentene i formelen og bestemmer følsomheten.

Et eksempel på sensitivitetsanalyse av et investeringsprosjekt i Excel

For en bedre forståelse av analysemetodikken trengs et eksempel. La oss analysere prosjektet med følgende kjente data:

1. Fyll ut tabellen for å analysere prosjektet på den.

2. Vi beregner kontantstrømmen ved å bruke OFFSET-funksjonen. I det innledende stadiet er flyten lik investeringene. Deretter bruker vi formelen: =IF(OFFSET(Tall;1;)=2;SUM(Inflow 1:Utflow 1); SUM(Inflow 1:Utflow 1)+$B$ 5)
   Cellebetegnelser i formelen kan være forskjellige, avhengig av oppsettet til tabellen. På slutten blir verdien fra de første dataene lagt til – bergingsverdien.

3. Vi bestemmer perioden prosjektet skal lønne seg for. For den første perioden bruker vi denne formelen: =SUMMESLY(G7: G17;»<0″). Celleområdet er kontantstrømkolonnen. For ytterligere perioder bruker vi denne formelen: =Startperiode+IF(Første e.stream>0; Første e.stream;0). Prosjektet er ved break-even-punktet om 4 år.

4. Vi lager en kolonne for tallene for de periodene da prosjektet lønner seg.

5. Vi beregner avkastningen på investeringen. Det er nødvendig å lage et uttrykk der overskuddet i en bestemt tidsperiode er delt på den opprinnelige investeringen.

6. Vi bestemmer diskonteringsfaktoren ved å bruke denne formelen: =1/(1+Disc.%) ^Nummer.

7. Vi beregner nåverdien ved hjelp av multiplikasjon – kontantstrømmen multipliseres med diskonteringsfaktoren.

8. La oss beregne PI (lønnsomhetsindeks). Nåverdien over tid deles på investeringen ved oppstart av prosjektet.

9. La oss definere den interne avkastningen ved å bruke IRR-funksjonen: =IRR(Rekkevidde for kontantstrøm).

Investeringssensitivitetsanalyse ved hjelp av dataark

For analyse av prosjekter innen investeringsfeltet er andre metoder bedre egnet enn en datatabell. Mange brukere opplever forvirring når de sammenstiller en formel. For å finne ut avhengigheten av en faktor på endringer i andre, må du velge de riktige cellene for å legge inn beregninger og for å lese data.

Faktor- og spredningsanalyse i Excel med kalkulasjonsautomatisering

En annen typologi for sensitivitetsanalyse er faktoranalyse og variansanalyse. Den første typen definerer forholdet mellom tall, den andre avslører avhengigheten av en variabel av andre.

ANOVA i Excel

Hensikten med en slik analyse er å dele variabiliteten til verdien inn i tre komponenter:

1. Variabilitet som følge av påvirkning av andre verdier.
2. Endringer på grunn av forholdet mellom verdiene som påvirker det.
3. Tilfeldige endringer.

La oss utføre variansanalyse gjennom Excel-tillegget "Dataanalyse". Hvis det ikke er aktivert, kan det aktiveres i innstillingene.

Den første tabellen må følge to regler: det er én kolonne for hver verdi, og dataene i den er ordnet i stigende eller synkende rekkefølge. Det er nødvendig å kontrollere påvirkningen av utdanningsnivået på atferd i konflikt.

1. Finn dataanalyseverktøyet i kategorien Data og åpne vinduet. I listen må du velge enveis variansanalyse.

2. Fyll ut linjene i dialogboksen. Inndataintervallet er alle celler, unntatt overskrifter og tall. Grupper etter kolonner. Vi viser resultatene på et nytt ark.

Siden verdien i den gule cellen er større enn én, kan antakelsen anses som feil – det er ingen sammenheng mellom utdanning og atferd i konflikt.

Faktoranalyse i Excel: et eksempel

La oss analysere forholdet mellom data i salgsfeltet - det er nødvendig å identifisere populære og upopulære produkter. Innledende informasjon:

1. Vi må finne ut hvilke vareetterspørselen økte mest i løpet av den andre måneden. Vi setter sammen en ny tabell for å bestemme veksten og nedgangen i etterspørselen. Veksten beregnes ved hjelp av denne formelen: =HVIS((Demand 2-Demand 1)>0; Demand 2- Demand 1;0). Reduser formel: =HVIS(Vekst=0; Krav 1- Krav 2;0).

2. Beregn veksten i etterspørselen etter varer i prosent: =HVIS(Vekst/Utfall 2 =0; Reduser/Utfall 2; Vekst/Utfall 2).

3. La oss lage et diagram for klarhet – velg en rekke celler og lag et histogram gjennom "Sett inn"-fanen. I innstillingene må du fjerne fyllingen, dette kan gjøres gjennom Format Data Series-verktøyet.

Toveis variansanalyse i Excel

Variansanalyse utføres med flere variabler. Vurder dette med et eksempel: du må finne ut hvor raskt reaksjonen på en lyd med forskjellig volum manifesterer seg hos menn og kvinner.

1. Vi åpner "Dataanalyse", i listen må du finne en toveis variansanalyse uten repetisjoner.
2. Inndataintervall – celler som inneholder data (uten overskrift). Vi viser resultatene på et nytt ark og klikker på "OK".

F-verdien er større enn F-kritisk, noe som betyr at gulvet påvirker reaksjonshastigheten på lyd.

konklusjonen

I denne artikkelen ble sensitivitetsanalyse i et Excel-regneark diskutert i detalj, slik at hver bruker vil være i stand til å forstå metodene for applikasjonen.