Känslighetsanalys i Excel (exempel på datablad)

Processer inom finansområdet är alltid sammankopplade – en faktor beror på en annan och förändras med den. Spåra dessa förändringar och förstå vad du kan förvänta dig i framtiden, kanske med hjälp av Excel-funktioner och kalkylbladsmetoder.

Få flera resultat med en datatabell

Databladsfunktioner är delar av vad-om-analys – ofta gjorda via Microsoft Excel. Detta är det andra namnet för känslighetsanalys.

Översikt

En datatabell är en typ av cellintervall som kan användas för att lösa problem genom att ändra värdena i vissa celler. Den skapas när det är nödvändigt att hålla reda på ändringar i komponenterna i formeln och få uppdateringar av resultaten, enligt dessa ändringar. Låt oss ta reda på hur man använder datatabeller i forskning och vilka typer de är.

Grunder om datatabeller

Det finns två typer av datatabeller, de skiljer sig åt i antalet komponenter. Du måste kompilera en tabell med fokus på antalet värden uXNUMXbuXNUMXbsom du behöver kontrollera med den.

Statistiker använder en enda variabeltabell när det bara finns en variabel i ett eller flera uttryck som kan ändra deras resultat. Till exempel används den ofta i kombination med PMT-funktionen. Formeln är utformad för att beräkna beloppet för den vanliga betalningen och tar hänsyn till den räntesats som anges i avtalet. I sådana beräkningar skrivs variablerna i en kolumn och resultaten av beräkningarna i en annan. Ett exempel på en dataskylt med 1 variabel:

Tänk sedan på plattorna med 2 variabler. De används i fall där två faktorer påverkar förändringen av någon indikator. De två variablerna kan hamna i en annan tabell kopplad till lånet, som kan användas för att bestämma den optimala återbetalningstiden och storleken på den månatliga betalningen. I denna beräkning behöver du även använda PMT-funktionen. Ett exempel på en tabell med 2 variabler:

Skapa en datatabell med en variabel

Överväg analysmetoden med exemplet med en liten bokhandel med endast 100 böcker i lager. Vissa av dem kan säljas dyrare ($50), resten kommer att kosta köpare mindre ($20). Den totala inkomsten från försäljningen av alla varor beräknas – ägaren beslutade att han skulle sälja 60 % av böckerna till ett högt pris. Du måste ta reda på hur intäkterna kommer att öka om du höjer priset på en större volym varor – 70 %, och så vidare.

Var uppmärksam! Den totala intäkten måste beräknas med en formel, annars går det inte att sammanställa en datatabell.

1. Välj en ledig cell bort från kanten av arket och skriv formeln i den: =Cell av totala intäkter. Till exempel, om inkomst skrivs i cell C14 (slumpmässig beteckning anges), måste du skriva detta: =S14.
2. Vi skriver procentandelen av volymen av varor i kolumnen till vänster om denna cell - inte under den, detta är mycket viktigt.
3. Vi väljer intervallet av celler där procentkolumnen och länken till den totala inkomsten finns.

4. Vi hittar på fliken "Data" objektet "Tänk om analys" och klickar på det - i menyn som öppnas, välj alternativet "Datatabell".

5. Ett litet fönster öppnas där du måste ange en cell med andelen böcker som ursprungligen såldes till ett högt pris i kolumnen "Ersätt värden med rader i ...". Detta steg görs för att räkna om den totala intäkten med hänsyn till den ökande andelen.

Efter att ha klickat på "OK"-knappen i fönstret där data matades in för att sammanställa tabellen, kommer resultaten av beräkningarna att visas på raderna.

Lägga till en formel till en datatabell med en enda variabel

Från en tabell som hjälpte till att beräkna en åtgärd med endast en variabel kan du skapa ett sofistikerat analysverktyg genom att lägga till en ytterligare formel. Den måste anges bredvid en redan existerande formel – om tabellen till exempel är radorienterad anger vi uttrycket i cellen till höger om den befintliga. När kolumnorienteringen är inställd skriver vi den nya formeln under den gamla. Följ sedan algoritmen:

1. Välj cellintervallet igen, men nu ska det inkludera den nya formeln.
2. Öppna analysmenyn "vad händer om" och välj "Datablad".
3. Vi lägger till en ny formel i motsvarande fält i rader eller kolumner, beroende på plattans orientering.

Skapa en datatabell med två variabler

Början av en sådan tabell är något annorlunda – du måste lägga en länk till den totala intäkten ovanför procentvärdena. Därefter utför vi dessa steg:

1. Skriv prisalternativ på en rad med en länk till inkomst – en cell för varje pris.
2. Välj ett cellintervall.

3. Öppna datatabellfönstret, som när du kompilerar en tabell med en variabel - via fliken "Data" i verktygsfältet.
4. Ersätt i kolumnen "Ersätt värden med kolumner i ..." en cell med ett initialt högt pris.
5. Lägg till en cell med den initiala procentandelen av försäljningen av dyra böcker i kolumnen "Ersätt värden med rader i ..." och klicka på "OK".

Som ett resultat är hela tabellen fylld med mängder av möjliga inkomster med olika villkor för försäljning av varor.

Snabba upp beräkningarna för kalkylblad som innehåller datatabeller

Om du behöver snabba beräkningar i en datatabell som inte utlöser en omräkning av hela arbetsboken, finns det några saker du kan göra för att påskynda processen.

1. Öppna alternativfönstret, välj objektet "Formler" i menyn till höger.
2. Välj objektet "Automatisk, utom för datatabeller" i avsnittet "Beräkningar i arbetsboken".

3. Låt oss räkna om resultaten i tabellen manuellt. För att göra detta, välj formlerna och tryck på F-tangenten.

Andra verktyg för att utföra känslighetsanalys

Det finns andra verktyg i programmet som hjälper dig att utföra känslighetsanalys. De automatiserar vissa åtgärder som annars skulle behöva göras manuellt.

1. Funktionen "Parameterval" är lämplig om det önskade resultatet är känt och du behöver känna till variabelns indatavärde för att få ett sådant resultat.
2. "Sök efter en lösning" är ett tillägg för att lösa problem. Det är nödvändigt att sätta gränser och peka på dem, varefter systemet hittar svaret. Lösningen bestäms genom att ändra värdena.
3. Känslighetsanalys kan göras med hjälp av Scenario Manager. Det här verktyget finns i vad-om-analysmenyn under fliken Data. Den ersätter värden i flera celler – antalet kan nå 32. Avsändaren jämför dessa värden så att användaren inte behöver ändra dem manuellt. Ett exempel på hur man använder skripthanteraren:

Känslighetsanalys av ett investeringsprojekt i Excel

Vad-om-analys är särskilt användbar i situationer där prognoser krävs, till exempel investeringar. Analytiker använder denna metod för att ta reda på hur värdet på ett företags aktie kommer att förändras som ett resultat av förändringar i vissa faktorer.

Metod för analys av investeringskänslighet

När du analyserar "vad händer om" använd uppräkning - manuell eller automatisk. Värdeintervallet är känt, och de ersätts i formeln en efter en. Resultatet är en uppsättning värden. Välj lämpligt nummer bland dem. Låt oss överväga fyra indikatorer för vilka känslighetsanalyser utförs inom finansområdet:

1. Nettonuvärde – Beräknas genom att subtrahera investeringsbeloppet från inkomstbeloppet.
2. Internränta/vinst – anger hur mycket vinst som krävs för att erhållas från en investering under ett år.
3. Återbetalningsgraden är förhållandet mellan all vinst och den initiala investeringen.
4. Rabatterat vinstindex – indikerar investeringens effektivitet.

Formel

Inbäddningskänslighet kan beräknas med denna formel: Ändring av utgångsparameter i % / Ändring av ingångsparameter i %.

Utgångs- och ingångsparametrarna kan vara de värden som beskrivits tidigare.

1. Du måste känna till resultatet under standardförhållanden.
2. Vi byter ut en av variablerna och övervakar förändringarna i resultatet.
3. Vi beräknar den procentuella förändringen av båda parametrarna i förhållande till de etablerade förhållandena.
4. Vi infogar de erhållna procenttalen i formeln och bestämmer känsligheten.

Ett exempel på känslighetsanalys av ett investeringsprojekt i Excel

För en bättre förståelse av analysmetodiken behövs ett exempel. Låt oss analysera projektet med följande kända data:

1. Fyll i tabellen för att analysera projektet på den.

2. Vi beräknar kassaflödet med OFFSET-funktionen. I inledningsskedet är flödet lika med investeringarna. Därefter tillämpar vi formeln: =OM(OFFSET(tal;1;)=2;SUM(Inflöde 1:Utflöde 1); SUM(Inflöde 1:Utflöde 1)+$B$ 5)
   Cellbeteckningar i formeln kan vara olika, beroende på tabellens layout. I slutet läggs värdet från initialdata till – räddningsvärdet.

3. Vi bestämmer under vilken period projektet ska löna sig. För den inledande perioden använder vi denna formel: =SUMMELY(G7: G17;»<0″). Cellintervallet är kassaflödeskolumnen. För ytterligare perioder använder vi denna formel: =Initial period+IF(Första e.ström>0; Första e.ström;0). Projektet är på nollpunkten om fyra år.

4. Vi skapar en kolumn för siffrorna för de perioder då projektet lönar sig.

5. Vi beräknar avkastningen på investeringen. Det är nödvändigt att göra ett uttryck där vinsten under en viss tidsperiod delas med den initiala investeringen.

6. Vi bestämmer diskonteringsfaktorn med hjälp av denna formel: =1/(1+skiva.%) ^Nummer.

7. Vi beräknar nuvärdet med multiplikation – kassaflödet multipliceras med diskonteringsfaktorn.

8. Låt oss beräkna PI (lönsamhetsindex). Nuvärdet över tid divideras med investeringen vid projektets start.

9. Låt oss definiera den interna avkastningen med hjälp av IRR-funktionen: =IRR(Kassaflödesintervall).

Investeringskänslighetsanalys med hjälp av datablad

För analys av projekt inom investeringsområdet är andra metoder bättre lämpade än en datatabell. Många användare upplever förvirring när de sammanställer en formel. För att ta reda på beroendet av en faktor på förändringar i andra måste du välja rätt celler för att ange beräkningar och för att läsa data.

Faktor- och spridningsanalys i Excel med beräkningsautomation

En annan typologi för känslighetsanalys är faktoranalys och variansanalys. Den första typen definierar förhållandet mellan tal, den andra avslöjar beroendet av en variabel av andra.

ANOVA i Excel

Syftet med en sådan analys är att dela upp värdets variabilitet i tre komponenter:

1. Variabilitet till följd av påverkan av andra värden.
2. Förändringar på grund av förhållandet mellan de värden som påverkar det.
3. Slumpmässiga förändringar.

Låt oss utföra variansanalys genom Excel-tillägget "Dataanalys". Om det inte är aktiverat kan det aktiveras i inställningarna.

Den initiala tabellen måste följa två regler: det finns en kolumn för varje värde, och data i den är ordnade i stigande eller fallande ordning. Det är nödvändigt att kontrollera utbildningsnivåns inflytande på beteende i konflikt.

1. Hitta verktyget Dataanalys på fliken Data och öppna dess fönster. I listan måste du välja envägsanalys av varians.

2. Fyll i raderna i dialogrutan. Inmatningsintervallet är alla celler, exklusive rubriker och siffror. Gruppera efter kolumner. Vi visar resultaten på ett nytt ark.

Eftersom värdet i den gula cellen är större än ett, kan antagandet anses felaktigt – det finns inget samband mellan utbildning och beteende i konflikt.

Faktoranalys i Excel: ett exempel

Låt oss analysera förhållandet mellan data inom försäljningsområdet - det är nödvändigt att identifiera populära och impopulära produkter. Inledande information:

1. Vi måste ta reda på vilka varuefterfrågan som ökade mest under den andra månaden. Vi sammanställer en ny tabell för att fastställa tillväxten och nedgången i efterfrågan. Tillväxten beräknas med denna formel: =OM((Krävan 2-Begäran 1)>0; Begäran 2- Begäran 1;0). Minska formel: =OM(Tillväxt=0; Efterfrågan 1- Efterfrågan 2;0).

2. Beräkna tillväxten i efterfrågan på varor i procent: =OM(Tillväxt/Utfall 2 =0; Minska/Utfall 2; Tillväxt/Utfall 2).

3. Låt oss göra ett diagram för tydlighetens skull – välj ett cellintervall och skapa ett histogram via fliken "Infoga". I inställningarna måste du ta bort fyllningen, detta kan göras genom verktyget Format Data Series.

Tvåvägs variansanalys i Excel

Variansanalys utförs med flera variabler. Tänk på detta med ett exempel: du måste ta reda på hur snabbt reaktionen på ett ljud med olika volym manifesterar sig hos män och kvinnor.

1. Vi öppnar "Dataanalys", i listan behöver du hitta en tvåvägsanalys av varians utan upprepningar.
2. Inmatningsintervall – celler som innehåller data (utan rubrik). Vi visar resultaten på ett nytt ark och klickar på "OK".

F-värdet är större än F-kritiskt, vilket innebär att golvet påverkar reaktionshastigheten på ljud.

Slutsats

I den här artikeln diskuterades känslighetsanalys i ett Excel-kalkylblad i detalj, så att varje användare kommer att kunna förstå metoderna för dess tillämpning.