Følsomhedsanalyse i Excel (databladeksempel)

Processer på økonomiområdet hænger altid sammen – én faktor afhænger af en anden og ændrer sig med den. Spor disse ændringer og forstå, hvad du kan forvente i fremtiden, måske ved hjælp af Excel-funktioner og regnearksmetoder.

Få flere resultater med en datatabel

Dataarkfunktioner er elementer i hvad-hvis-analysen - ofte udført gennem Microsoft Excel. Dette er det andet navn for følsomhedsanalyse.

Oversigt

En datatabel er en type celleområde, der kan bruges til at løse problemer ved at ændre værdierne i nogle celler. Den oprettes, når det er nødvendigt at holde styr på ændringer i formlens komponenter og modtage opdateringer til resultaterne i henhold til disse ændringer. Lad os finde ud af, hvordan man bruger datatabeller i forskning, og hvilke typer de er.

Grundlæggende om datatabeller

Der er to typer datatabeller, de adskiller sig i antallet af komponenter. Du skal kompilere en tabel med fokus på antallet af værdier uXNUMXbuXNUMXb, som du skal tjekke med den.

Statistikere bruger en enkelt variabeltabel, når der kun er én variabel i et eller flere udtryk, der kan ændre deres resultat. For eksempel bruges det ofte i forbindelse med PMT-funktionen. Formlen er designet til at beregne størrelsen af ​​den almindelige betaling og tager hensyn til den rentesats, der er angivet i aftalen. I sådanne beregninger skrives variablerne i én kolonne, og resultaterne af beregningerne i en anden. Et eksempel på en dataplade med 1 variabel:

Overvej derefter pladerne med 2 variable. De bruges i tilfælde, hvor to faktorer påvirker ændringen i enhver indikator. De to variable kan ende i en anden tabel tilknyttet lånet, som kan bruges til at bestemme den optimale tilbagebetalingsperiode og størrelsen af ​​den månedlige ydelse. I denne beregning skal du også bruge PMT-funktionen. Et eksempel på en tabel med 2 variable:

Oprettelse af en datatabel med én variabel

Overvej analysemetoden ved at bruge eksemplet med en lille boghandel med kun 100 bøger på lager. Nogle af dem kan sælges dyrere ($50), resten vil koste købere mindre ($20). Den samlede indtægt fra salget af alle varer er beregnet - ejeren besluttede, at han ville sælge 60% af bøgerne til en høj pris. Du skal finde ud af, hvordan omsætningen vil stige, hvis du øger prisen på en større mængde varer – 70 %, og så videre.

Vær opmærksom! Den samlede omsætning skal beregnes ved hjælp af en formel, ellers vil det ikke være muligt at udarbejde en datatabel.

1. Vælg en ledig celle væk fra kanten af ​​arket og skriv formlen i den: =Celle af samlet omsætning. For eksempel, hvis indkomst er skrevet i celle C14 (tilfældig betegnelse er angivet), skal du skrive dette: =S14.
2. Vi skriver procentdelen af ​​mængden af ​​varer i kolonnen til venstre for denne celle - ikke under den, dette er meget vigtigt.
3. Vi vælger det celleområde, hvor procentkolonnen og linket til den samlede indkomst er placeret.

4. Vi finder på fanen "Data" punktet "Hvad nu hvis analyse" og klikker på det - i menuen, der åbnes, vælg muligheden "Datatabel".

5. Et lille vindue åbnes, hvor du skal angive en celle med procentdelen af ​​bøger, der oprindeligt blev solgt til en høj pris i kolonnen "Erstat værdier med rækker i ...". Dette trin udføres for at genberegne den samlede omsætning under hensyntagen til den stigende procentdel.

Efter at have klikket på knappen "OK" i vinduet, hvor dataene blev indtastet til kompilering af tabellen, vises resultaterne af beregningerne i linjerne.

Tilføjelse af en formel til en enkelt variabel datatabel

Fra en tabel, der hjalp med at beregne en handling med kun én variabel, kan du lave et sofistikeret analyseværktøj ved at tilføje en ekstra formel. Det skal indtastes ved siden af ​​en allerede eksisterende formel – hvis tabellen for eksempel er rækkeorienteret, indtaster vi udtrykket i cellen til højre for den eksisterende. Når kolonneorienteringen er indstillet, skriver vi den nye formel under den gamle. Følg derefter algoritmen:

1. Vælg celleområdet igen, men nu skal det indeholde den nye formel.
2. Åbn analysemenuen "hvad nu hvis" og vælg "Dataark".
3. Vi tilføjer en ny formel til det tilsvarende felt i rækker eller kolonner, afhængigt af pladens orientering.

Opret en datatabel med to variable

Starten på en sådan tabel er lidt anderledes - du skal sætte et link til den samlede omsætning over procentværdierne. Dernæst udfører vi disse trin:

1. Skriv prismuligheder på én linje med et link til indkomst – en celle for hver pris.
2. Vælg et celleområde.

3. Åbn datatabelvinduet, som når du kompilerer en tabel med én variabel - gennem fanen "Data" på værktøjslinjen.
4. Erstat i kolonnen "Erstat værdier efter kolonner i ..." en celle med en indledende høj pris.
5. Tilføj en celle med den oprindelige procentdel af salg af dyre bøger til kolonnen "Erstat værdier med rækker i ...", og klik på "OK".

Som følge heraf er hele tabellen fyldt med mængder af mulige indtægter med forskellige betingelser for salg af varer.

Fremskynd beregningerne for arbejdsark, der indeholder datatabeller

Hvis du har brug for hurtige beregninger i en datatabel, der ikke udløser en genberegning af hele projektmappen, er der et par ting, du kan gøre for at fremskynde processen.

1. Åbn indstillingsvinduet, vælg punktet "Formler" i menuen til højre.
2. Vælg punktet "Automatisk, undtagen for datatabeller" i afsnittet "Beregninger i projektmappen".

3. Lad os genberegne resultaterne i tabellen manuelt. For at gøre dette skal du vælge formlerne og trykke på F-tasten.

Andre værktøjer til at udføre følsomhedsanalyse

Der er andre værktøjer i programmet til at hjælpe dig med at udføre følsomhedsanalyse. De automatiserer nogle handlinger, som ellers skulle udføres manuelt.

1. Funktionen "Parametervalg" er velegnet, hvis det ønskede resultat er kendt, og du skal kende variablens inputværdi for at opnå et sådant resultat.
2. "Søg efter en løsning" er en tilføjelse til løsning af problemer. Det er nødvendigt at sætte grænser og pege på dem, hvorefter systemet finder svaret. Løsningen bestemmes ved at ændre værdierne.
3. Følsomhedsanalyse kan udføres ved hjælp af Scenario Manager. Dette værktøj findes i menuen hvad hvis-analyse under fanen Data. Det erstatter værdier i flere celler – tallet kan nå 32. Afsenderen sammenligner disse værdier, så brugeren ikke behøver at ændre dem manuelt. Et eksempel på brug af script manager:

Følsomhedsanalyse af et investeringsprojekt i Excel

Hvad hvis-analyse er især nyttig i situationer, hvor prognoser er påkrævet, såsom investering. Analytikere bruger denne metode til at finde ud af, hvordan værdien af ​​en virksomheds aktie vil ændre sig som følge af ændringer i nogle faktorer.

Metode til analyse af investeringsfølsomhed

Når du analyserer "hvad nu hvis" skal du bruge opregning - manuel eller automatisk. Rækken af ​​værdier er kendt, og de substitueres i formlen én efter én. Resultatet er et sæt værdier. Vælg det passende nummer blandt dem. Lad os overveje fire indikatorer, for hvilke der udføres følsomhedsanalyse inden for finansområdet:

1. Nettonutidsværdi – Beregnes ved at trække investeringens størrelse fra indkomstbeløbet.
2. Intern rente/fortjeneste – angiver, hvor meget overskud der skal modtages fra en investering på et år.
3. Tilbagebetalingsforholdet er forholdet mellem alle overskud og den oprindelige investering.
4. Discounted profit index - angiver effektiviteten af ​​investeringen.

Formula

Indlejringsfølsomhed kan beregnes ved hjælp af denne formel: Ændring af udgangsparameter i % / Ændring af indgangsparameter i %.

Output- og inputparametrene kan være de værdier, der er beskrevet tidligere.

1. Du skal kende resultatet under standardforhold.
2. Vi udskifter en af ​​variablerne og overvåger ændringerne i resultatet.
3. Vi beregner den procentvise ændring af begge parametre i forhold til de etablerede forhold.
4. Vi indsætter de opnåede procenter i formlen og bestemmer følsomheden.

Et eksempel på følsomhedsanalyse af et investeringsprojekt i Excel

For en bedre forståelse af analysemetoden er der brug for et eksempel. Lad os analysere projektet med følgende kendte data:

1. Udfyld tabellen for at analysere projektet på den.

2. Vi beregner pengestrømmen ved hjælp af OFFSET-funktionen. I den indledende fase er flowet lig med investeringerne. Dernæst anvender vi formlen: =HVIS(OFFSET(Tal;1;)=2;SUM(Indløb 1:Udløb 1); SUM(Indløb 1:Udløb 1)+$B$ 5)
   Cellebetegnelser i formlen kan være forskellige, afhængigt af tabellens layout. Til sidst tilføjes værdien fra de indledende data - bjærgningsværdien.

3. Vi bestemmer den periode, projektet skal betale sig for. For den indledende periode bruger vi denne formel: =SUMMESLY(G7: G17;»<0″). Celleområdet er pengestrømskolonnen. For yderligere perioder anvender vi denne formel: =Initial periode+IF(Første e.stream>0; Første e.stream;0). Projektet er ved break-even-punktet om 4 år.

4. Vi opretter en kolonne for tallene for de perioder, hvor projektet betaler sig.

5. Vi beregner investeringsafkastet. Det er nødvendigt at lave et udtryk, hvor overskuddet i en bestemt periode divideres med den oprindelige investering.

6. Vi bestemmer diskonteringsfaktoren ved hjælp af denne formel: =1/(1+Disk.%) ^ Nummer.

7. Vi beregner nutidsværdien ved hjælp af multiplikation – pengestrømmen ganges med diskonteringsfaktoren.

8. Lad os beregne PI (rentabilitetsindeks). Nutidsværdien over tid divideres med investeringen ved projektets start.

9. Lad os definere det interne afkast ved hjælp af IRR-funktionen: =IRR(Range af pengestrømme).

Investeringsfølsomhedsanalyse ved hjælp af dataark

Til analyse af projekter på investeringsområdet er andre metoder bedre egnede end en datatabel. Mange brugere oplever forvirring, når de udarbejder en formel. For at finde ud af afhængigheden af ​​en faktor af ændringer i andre, skal du vælge de korrekte celler til indtastning af beregninger og til læsning af data.

Faktor- og spredningsanalyse i Excel med kalkulationsautomatisering

En anden typologi af følsomhedsanalyse er faktoranalyse og variansanalyse. Den første type definerer forholdet mellem tal, den anden afslører afhængigheden af ​​en variabel af andre.

ANOVA i Excel

Formålet med en sådan analyse er at opdele værdiens variabilitet i tre komponenter:

1. Variabilitet som følge af påvirkning af andre værdier.
2. Ændringer på grund af forholdet mellem de værdier, der påvirker det.
3. Tilfældige ændringer.

Lad os udføre variansanalyse gennem Excel-tilføjelsen "Dataanalyse". Hvis det ikke er aktiveret, kan det aktiveres i indstillingerne.

Den indledende tabel skal følge to regler: der er én kolonne for hver værdi, og dataene i den er arrangeret i stigende eller faldende rækkefølge. Det er nødvendigt at kontrollere uddannelsesniveauets indflydelse på adfærd i konflikt.

1. Find værktøjet Dataanalyse på fanen Data, og åbn dets vindue. På listen skal du vælge en-vejs variansanalyse.

2. Udfyld linjerne i dialogboksen. Indtastningsintervallet er alle celler, eksklusive overskrifter og tal. Gruppér efter kolonner. Vi viser resultaterne på et nyt ark.

Da værdien i den gule celle er større end én, kan antagelsen betragtes som forkert - der er ingen sammenhæng mellem uddannelse og adfærd i konflikt.

Faktoranalyse i Excel: et eksempel

Lad os analysere forholdet mellem data inden for salg - det er nødvendigt at identificere populære og upopulære produkter. Indledende oplysninger:

1. Vi skal finde ud af, hvilke varer efterspørgslen steg mest i løbet af den anden måned. Vi er ved at udarbejde en ny tabel for at bestemme væksten og faldet i efterspørgslen. Væksten beregnes ved hjælp af denne formel: =HVIS((Demand 2-Demand 1)>0; Demand 2- Demand 1;0). Formindsk formel: =HVIS(Vækst=0; Krav 1- Krav 2;0).

2. Beregn væksten i efterspørgsel efter varer i procent: =HVIS(Vækst/Resultat 2 =0; Fald/Resultat 2; Vækst/Resultat 2).

3. Lad os lave et diagram for klarhedens skyld – vælg en række celler og lav et histogram gennem fanen "Indsæt". I indstillingerne skal du fjerne udfyldningen, dette kan gøres gennem Format Data Series-værktøjet.

To-vejs variansanalyse i Excel

Variansanalyse udføres med flere variable. Overvej dette med et eksempel: du skal finde ud af, hvor hurtigt reaktionen på en lyd af forskellig lydstyrke manifesterer sig hos mænd og kvinder.

1. Vi åbner "Dataanalyse", i listen skal du finde en tovejs variansanalyse uden gentagelser.
2. Input interval – celler, der indeholder data (uden overskrift). Vi viser resultaterne på et nyt ark og klikker på "OK".

F-værdien er større end F-kritisk, hvilket betyder, at gulvet påvirker reaktionshastigheden på lyd.

Konklusion

I denne artikel blev følsomhedsanalyse i et Excel-regneark diskuteret i detaljer, så hver bruger vil være i stand til at forstå metoderne til dens anvendelse.