Excel følsomhedsanalyse (datatabellundersøgelse)

Finansprocesser er altid indbyrdes forbundne - en faktor afhænger af den anden og ændrer sig med det. Sporing af disse ændringer og forstår, hvad de skal forvente i fremtiden, er muligt ved hjælp af Excel-funktioner og tabulære metoder.

Indhentning af flere resultater ved hjælp af et datatabell

Databellernes evner er elementer af "hvad hvis" analysen ofte udføres via Microsoft Excel. Dette er det andet navn på følsomhedsanalysen.

Generel

Datatabellen er typen af ​​celleområde, som du kan løse de problemer, der opstår ved at ændre værdierne i nogle celler. Det er lavet, når det er nødvendigt at overvåge ændringer i komponenterne i formlen og modtage opdateringer af resultaterne, ifølge disse ændringer. Find ud af, hvordan du anvender databaser i studier, og hvilke arter de er.

Grundlæggende oplysninger om datatabeller

Der er to typer datatabeller, de adskiller sig i antallet af komponenter. Lav en tabel er nødvendig med orientering med antallet af værdier, der skal kontrolleres med det.

Statistikspecialister anvender en tabel med en variabel, når der kun er en variabel i et eller flere udtryk, hvilket kan påvirke ændringen i deres resultat. For eksempel bruges det ofte i et bundt med PL-funktionen. Formlen er designet til at beregne mængden af ​​regelmæssig løn og tager højde for rentesatsen i kontrakten. Med sådanne beregninger registreres variablerne i en søjle og resultaterne af beregninger til en anden. Et eksempel på dataplade med 1 variabel:

en

Herefter skal du overveje tegn med 2 variabler. De gælder i tilfælde, hvor to faktorer påvirker ændringen i en hvilken som helst indikator. To variabler kan være i en anden tabel forbundet med et lån - med dets hjælp kan du identificere den optimale betalingsperiode og størrelsen af ​​den månedlige betaling. Denne beregning skal også bruge PPT-funktionen. En eksempelplade med 2 variabler:

2 Oprettelse af et datatabell med en variabel

Overvej analysemetoden på eksemplet på en lille boghandel, hvor kun 100 bøger er tilgængelige. Nogle af dem kan sælges dyrere ($ 50), resten vil koste købere billigere ($ 20). Den samlede indtægt fra salget af alle varer er designet - ejeren besluttede at i en høj pris på 60% af bøgerne. Det er nødvendigt at finde ud af, hvordan indtægterne vil vokse, hvis du øger prisen på en større mængde varer - 70% og så videre.

1. Vælg en gratis celleafstand fra kanten af ​​arket og skriv formlen i den: = cellen i de samlede indtægter. For eksempel, hvis indkomsten er optaget i C14-cellen (en tilfældig betegnelse er angivet), er det nødvendigt at skrive sådan: = C14.
2. Vi skriver ned mængden af ​​varer i kolonnen til venstre for denne celle - ikke under det, det er meget vigtigt.
3. Vi tildeler rækkevidden af ​​celler, hvor rentekolonnen er placeret og et link til den samlede indkomst.

3.

1. Vi finder på fanen "Data" i "Analysen", hvad hvis "" og klikker på den - i menuen, der åbnes, skal du vælge "Data Table" -indstillingen.

fire.

1. Et lille vindue åbnes, hvor du skal angive en celle med en procentdel af de bøger, der oprindeligt blev solgt til en høj pris i kolonnen "for at erstatte værdier på linjerne i ...". Dette trin er gjort for at gøre omberegning af generelle indtægter under hensyntagen til den stigende procentdel.

fem

Når du har trykket på "OK" -knappen i vinduet, hvor data blev indtastet for at kompilere tabellen, vises resultaterne af beregningerne i rækkerne.

Tilføjelse af en formel til et datatabell med en variabel

Fra bordet, der hjalp med at beregne handlingen med kun en variabel, kan du lave et kompliceret analyseværktøj ved at tilføje en yderligere formel. Det skal indtastes i nærheden af ​​den allerede eksisterende formel - for eksempel, hvis tabellen er fokuseret på rækker, skal du indtaste udtrykket i cellen til højre for den allerede eksisterende. Når orienteringen er installeret på kolonner, skal du skrive en ny formel under den gamle. Næste skal handle i henhold til algoritmen:

1. Vi fremhæver igen rækkevidden af ​​celler, men nu skal det indeholde en ny formel.
2. Åbn "What hvis" analysemenuen og vælg "Data Table".
3. Tilføj en ny formel til det tilsvarende felt online eller ved kolonner afhængigt af pladens orientering.

Oprettelse af et datatabell med to variabler

Begyndelsen af ​​forberedelsen af ​​et sådant bord er lidt anderledes - du skal placere et link til den samlede indtægt over de procentuelle værdier. Dernæst udfører vi disse trin:

1. Optag muligheder for prisen på en linje med henvisning til indkomst - hver pris er en celle.
2. Vælg rækkevidde af celler.

6.

1. Åbn databordsvinduet, som ved udarbejdelsen af ​​en enkelt variabel - via fanen Data på værktøjslinjen.
2. Erstatte i tællingen "for at erstatte værdier på kolonner i ..." Cell med en indledende høj pris.
3. Tilføj til kolonnen "for at erstatte værdier på strenge i ..." Cell med en indledende interesse for salg af dyre bøger og klik på "OK".

Som følge heraf er hele pladen fyldt med mængderne af mulige indkomster med forskellige salgsbetingelser.

7 Acceleration af beregninger for ark, der indeholder datatabeller

Hvis der kræves hurtige beregninger i datapladen, der ikke kører hele bogensberegning, kan du udføre flere handlinger for at fremskynde processen.

1. Åbn Parameters-vinduet, vælg klausulen "Formula" i menuen til højre.
2. Vælg emnet "automatisk, undtagen datatabeller" i afsnittet "Beregninger i bogen".

otte

1. Udfør omberegning af resultaterne i pladen manuelt. For dette skal du fremhæve formlerne og trykke på f-tasten

Andre værktøjer til udførelse af følsomhedsanalyse

Programmet har andre værktøjer til at hjælpe med at udføre følsomhedsanalyse. De automatiserer nogle handlinger, der ellers skulle gøres manuelt.

1. "Valg af parameter" -funktionen er egnet, hvis det ønskede resultat er kendt, og det er nødvendigt at finde ud af inputværdien af ​​variablen for at opnå et sådant resultat.
2. "Løsning søgning" er en add-in for at løse problemer. Det er nødvendigt at fastlægge begrænsninger og angive dem, hvorefter systemet finder svaret. Opløsningen bestemmes ved at ændre værdierne.
3. Følsomhedsanalyse kan udføres ved hjælp af Script Manager. Dette værktøj er i "What hvis" analysemenuen på fanen Data. Det erstatter værdierne i flere celler - mængden kan nå 32. Dispatcheren sammenligner disse værdier, og brugeren behøver ikke at ændre dem manuelt. Et eksempel på at anvende scripting manager:

ni

Analyse af følsomheden af ​​investeringsprojektet i Excel

Metode til analyse af følsomhed inden for investeringsområdet

Når du analyserer "hvad hvis" bruger busten - manualen eller automatisk. Kendte rækkevidde af værdier, og de er igen substitueret i formlen. Som et resultat opnås et sæt værdier. Af disse skal du vælge en passende figur. Overvej fire indikatorer, for hvilke analysen af ​​følsomheden på feltet Finansiering:

1. Ren nutidsværdi - beregnes ved at trække størrelsen af ​​investeringen fra indkomstens størrelse.
2. Den interne rentabilitet / overskud - angiver, hvilke overskud der kræves af investering for året.
3. Betalingsforholdet er forholdet mellem alle overskud til den oprindelige investering.
4. Rabatteret resultatindeks - angiver investeringens effektivitet.

Formel

Følsomheden af ​​fastgørelsen kan beregnes ved hjælp af denne formel: Skift outputparameteren i% / ændring i indgangsparameteren i%.

Udgangs- og indgangsparameteren kan være de tidligere beskrevne værdier.

1. Det er nødvendigt at kende resultatet under standardbetingelser.
2. Vi erstatter en af ​​variablerne og følger resultaterne af resultatet.
3. Beregn den procentvise ændring i begge parametre i forhold til de etablerede forhold.
4. Vi indsætter procentsatserne opnået i formlen og bestemmer følsomheden.

Et eksempel på en analyse af investeringsprojektets følsomhed i Excel

For en bedre forståelse af analyseteknikkerne kræves et eksempel. Lad os analysere projektet med sådanne kendte data:

10.

1. Fyld bordet for at analysere projektet på det.

elleve

1. Beregn pengestrømmen ved hjælp af forskydningsfunktionen. I startfasen er strømmen lig med investeringer. Derefter bruger vi formlen: = Hvis (forskydning (nummer; 1;) = 2; summere (tilstrømning 1: udstrømning 1); Sumer (tilstrømning 1: Udstrømning 1) + $ B $ 5) Betegnelserne af celler i formlen kan Vær anderledes, det afhænger af placeringstabellen. I slutningen tilføjes værdien fra de oprindelige data - likvidationsværdien.

12.

1. Vi definerer den frist, som projektet vil betale af. For den indledende periode bruger vi denne formel: = Silent (G7: G17; "0; første D.Potok; 0). Projektet er i break-jævn punkt i 4 år.

13.

1. Opret en kolonne for antallet af de perioder, når projektet betaler sig.

fjorten

1. Beregne rentabiliteten af ​​investeringer. Det er nødvendigt at skabe et udtryk, hvor overskuddet i en bestemt periode er opdelt i de første investeringer.

femten

1. Bestem diskonteringskoefficienten for denne formel: = 1 / (1 + disk.%) ^ Nummer.

seksten

1. Beregn nutidsværdien ved multiplikation - pengestrømmen multipliceres med diskonteringsrenten.

17.

1. Beregn PI (rentabilitetsindeks). Den givne værdi i tidens segment er opdelt i vedhæftede filer i begyndelsen af ​​projektudviklingen.

atten

1. Vi definerer den interne resultat af overskud ved hjælp af EMD-funktionen: = FMR (pengestrømsinterval).

Analyse af investeringsfølsomhed ved hjælp af datatabellen

Til analyse af projekter inden for investering er andre metoder bedre egnet end datatabellen. Mange brugere har forvirring ved udarbejdelsen af ​​formlen. For at finde ud af afhængigheden af ​​en faktor fra ændringer i andre, skal du vælge de korrekte beregningsceller og at læse dataene.

Faktor og dispersionsanalyse i Excel med automatisering af beregninger

Dispersionsanalyse i Excel

Formålet med en sådan analyse er at opdele variabiliteten af ​​størrelsen af ​​tre komponenter:

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

Udfør en dispersionsanalyse gennem en Excel-add-on "dataanalyse". Hvis det ikke er aktiveret, kan det tilsluttes i parametre.

Startbordet skal overholde de to regler: Hver værdi tegner sig for en kolonne, og dataene i den er arrangeret i stigende eller faldende. Det er nødvendigt at teste virkningen af ​​uddannelsesniveauet på adfærd i konflikt.

nitten

1. Vi finder "Data" -fanen "Data" -værktøjet og åbner vinduet. Listen skal vælge enkeltfaktor dispersionsanalyse.

tyve

1. Udfyld rækkerne i dialogboksen. Indløbsintervallet er alle celler uden at tage hensyn til caps og tal. Vi grupperer på kolonner. Fortæl resultaterne på et nyt ark.

21.

Da værdien i den gule celle er større end enheden, kan vi påtage os antagelsen om forkert - der er ikke noget forhold mellem uddannelse og adfærd i konflikten.

Faktoranalyse i Excel: Eksempel

Vi analyserer forholdet mellem data inden for salg - det er nødvendigt at identificere populære og upopulære varer. Indledende information:

22.

1. Det er nødvendigt at finde ud af, hvilke varer efterspørgslen efter den anden måned er vokset i den anden måned. Vi laver et nyt bord for at bestemme væksten og reduktionen af ​​efterspørgslen. Væksten beregnes i henhold til denne formel: = hvis ((((efterspørgsel 2-efterspørgsel 1)> 0; efterspørgsel 2 - efterspørgsel 1; 0). Formlen for nedgangen: = Hvis (vækst = 0; efterspørgsel er 1-efterspørgsel 2; 0).

23.

1. Beregn væksten i efterspørgslen efter varer i procent: = Hvis (Vækst / I alt 2 = 0; Reduktion / I alt 2; Højde / I alt 2).

24.

1. Vi vil lave et kort for klarhed - allokér rækkevidden af ​​celler og opret et histogram gennem fanen "Indsæt". I de indstillinger, du skal fjerne fyldningen, kan det ske via værktøjet "Data Format Format".

25 to-faktor dispersionsanalyse i Excel

Dispersionsanalyse udføres med flere variabler. Overvej dette på eksemplet: Du skal finde ud af, hvor hurtigt reaktionen på lyden af ​​forskellige volumener hos mænd og kvinder er manifesteret.

26.

1. Åbn en "dataanalyse", du skal finde en to-faktor dispersionsanalyse uden gentagelser.
2. Indgangsinterval - celler, hvor data er indeholdt (uden en hat). Vi bringer resultaterne til det nye ark og klikker på "OK".

27.

Indikatoren F er større end F-Critical - det betyder, at gulvet påvirker reaktionshastigheden til lyden.

28.

Konklusion.

Denne artikel beskrev detaljeret følsomhedsanalysen i Excel-bordprocessoren, således at hver bruger kan finde ud af metoderne til dens anvendelse.

Meddelelse Følsomhedsanalysen i Excel (Sample Data Table) viste sig først til informationsteknologi.