Sensitiwiteitsanalise in Excel (voorbeelddatablad)

Prosesse op die gebied van finansies is altyd onderling verbind – een faktor hang van 'n ander af en verander daarmee saam. Volg hierdie veranderinge en verstaan ​​wat om in die toekoms te verwag, miskien met behulp van Excel-funksies en sigbladmetodes.

Kry verskeie resultate met 'n datatabel

Databladvermoëns is elemente van wat-as-analise—dikwels deur Microsoft Excel gedoen. Dit is die tweede naam vir sensitiwiteitsanalise.

Oorsig

'n Datatabel is 'n soort reeks selle wat gebruik kan word om probleme op te los deur die waardes in sommige selle te verander. Dit word geskep wanneer dit nodig is om tred te hou met veranderinge in die komponente van die formule en opdaterings van die resultate te ontvang, volgens hierdie veranderinge. Kom ons vind uit hoe om datatabelle in navorsing te gebruik, en watter tipes dit is.

Basiese beginsels oor datatabelle

Daar is twee tipes datatabelle, hulle verskil in die aantal komponente. Jy moet 'n tabel saamstel met die fokus op die aantal waardes uXNUMXbuXNUMXbwat jy daarmee moet kontroleer.

Statistici gebruik 'n enkele veranderlike tabel wanneer daar net een veranderlike in een of meer uitdrukkings is wat hul resultaat kan verander. Dit word byvoorbeeld dikwels saam met die PMT-funksie gebruik. Die formule is ontwerp om die bedrag van die gereelde betaling te bereken en neem die rentekoers in ag wat in die ooreenkoms gespesifiseer word. In sulke berekeninge word die veranderlikes in een kolom geskryf, en die resultate van die berekeninge in 'n ander. 'n Voorbeeld van 'n dataplaat met 1 veranderlike:

Beskou dan die plate met 2 veranderlikes. Hulle word gebruik in gevalle waar twee faktore die verandering in enige aanwyser beïnvloed. Die twee veranderlikes kan in 'n ander tabel beland wat met die lening geassosieer word, wat gebruik kan word om die optimale terugbetalingstydperk en die bedrag van die maandelikse betaling te bepaal. In hierdie berekening moet jy ook die PMT-funksie gebruik. 'n Voorbeeld van 'n tabel met 2 veranderlikes:

Skep 'n datatabel met een veranderlike

Oorweeg die ontledingsmetode deur die voorbeeld van 'n klein boekwinkel met slegs 100 boeke in voorraad te gebruik. Sommige van hulle kan duurder verkoop word ($50), die res sal kopers minder kos ($20). Die totale inkomste uit die verkoop van alle goedere word bereken – die eienaar het besluit dat hy 60% van die boeke teen 'n hoë prys sal verkoop. Jy moet uitvind hoe inkomste sal toeneem as jy die prys van 'n groter volume goedere verhoog – 70%, ensovoorts.

Gee aandag! Die totale inkomste moet met behulp van 'n formule bereken word, anders sal dit nie moontlik wees om 'n datatabel saam te stel nie.

1. Kies 'n vrye sel weg van die rand van die blad en skryf die formule daarin: =Sel van totale inkomste. Byvoorbeeld, as inkomste in sel C14 geskryf word (ewekansige benaming word aangedui), moet jy dit skryf: =S14.
2. Ons skryf die persentasie van die volume goedere in die kolom aan die linkerkant van hierdie sel – nie onder dit nie, dit is baie belangrik.
3. Ons kies die reeks selle waar die persentasiekolom en die skakel na die totale inkomste geleë is.

4. Ons vind op die "Data"-oortjie die item "Wat as analise" en klik daarop - in die kieslys wat oopmaak, kies die opsie "Datatabel".

5. 'n Klein venster sal oopmaak waar jy 'n sel moet spesifiseer met die persentasie boeke wat aanvanklik teen 'n hoë prys verkoop is in die kolom "Vervang waardes deur rye in ...". Hierdie stap word gedoen om die totale inkomste te herbereken met inagneming van die toenemende persentasie.

Nadat u op die “OK”-knoppie in die venster waar die data ingevoer is vir die samestelling van die tabel geklik is, sal die resultate van die berekeninge in die reëls verskyn.

Voeg 'n formule by 'n enkele veranderlike datatabel

Uit 'n tabel wat gehelp het om 'n aksie met slegs een veranderlike te bereken, kan jy 'n gesofistikeerde analise-instrument maak deur 'n bykomende formule by te voeg. Dit moet langs 'n reeds bestaande formule ingevoer word – as die tabel byvoorbeeld rygeoriënteerd is, voer ons die uitdrukking in die sel regs van die bestaande een in. Wanneer die kolomoriëntasie gestel is, skryf ons die nuwe formule onder die ou een. Volg dan die algoritme:

1. Kies weer die reeks selle, maar nou moet dit die nuwe formule insluit.
2. Maak die "wat as"-ontledingskieslys oop en kies "Datablad".
3. Ons voeg 'n nuwe formule by die ooreenstemmende veld in rye of kolomme, afhangende van die oriëntasie van die plaat.

Skep 'n datatabel met twee veranderlikes

Die begin van so 'n tabel is effens anders - jy moet 'n skakel na die totale inkomste bo die persentasiewaardes plaas. Volgende voer ons hierdie stappe uit:

1. Skryf prysopsies in een reël met 'n skakel na inkomste – een sel vir elke prys.
2. Kies 'n reeks selle.

3. Maak die datatabelvenster oop, soos wanneer 'n tabel met een veranderlike saamgestel word - deur die "Data"-oortjie op die nutsbalk.
4. Vervang in die kolom "Vervang waardes deur kolomme in ..." 'n sel met 'n aanvanklike hoë prys.
5. Voeg 'n sel met die aanvanklike persentasie van verkope van duur boeke by die kolom "Vervang waardes deur rye in ..." en klik "OK".

As gevolg hiervan, is die hele tabel gevul met bedrae van moontlike inkomste met verskillende voorwaardes vir die verkoop van goedere.

Bespoedig berekeninge vir werkkaarte wat datatabelle bevat

As jy vinnige berekeninge in 'n datatabel nodig het wat nie 'n herberekening van die hele werkboek veroorsaak nie, is daar 'n paar dinge wat jy kan doen om die proses te bespoedig.

1. Maak die opsievenster oop, kies die item "Formules" in die kieslys aan die regterkant.
2. Kies die item "Outomaties, behalwe vir datatabelle" in die "Berekenings in die werkboek" afdeling.

3. Kom ons herbereken die resultate in die tabel met die hand. Om dit te doen, kies die formules en druk die F-sleutel.

Ander gereedskap om sensitiwiteitsanalise uit te voer

Daar is ander instrumente in die program om jou te help om sensitiwiteitsanalise uit te voer. Hulle outomatiseer sommige aksies wat andersins met die hand gedoen sou moes word.

1. Die "Parameterseleksie"-funksie is geskik as die gewenste resultaat bekend is, en jy moet die insetwaarde van die veranderlike ken om so 'n resultaat te verkry.
2. "Soek vir 'n oplossing" is 'n byvoeging om probleme op te los. Dit is nodig om perke te stel en daarna te wys, waarna die stelsel die antwoord sal vind. Die oplossing word bepaal deur die waardes te verander.
3. Sensitiwiteitsanalise kan met die Scenario Bestuurder gedoen word. Hierdie instrument word gevind in die wat-as-analise-kieslys onder die Data-oortjie. Dit vervang waardes in verskeie selle – die getal kan 32 bereik. Die versender vergelyk hierdie waardes sodat die gebruiker dit nie handmatig hoef te verander nie. 'n Voorbeeld van die gebruik van die skripbestuurder:

Sensitiwiteitsanalise van 'n beleggingsprojek in Excel

Wat-as-analise is veral nuttig in situasies waar vooruitskatting vereis word, soos belegging. Ontleders gebruik hierdie metode om uit te vind hoe die waarde van 'n maatskappy se voorraad sal verander as gevolg van veranderinge in sommige faktore.

Belegging Sensitiwiteit Analise Metode

Wanneer jy "wat as" ontleed, gebruik opsomming - handmatig of outomaties. Die reeks waardes is bekend, en hulle word een vir een in die formule vervang. Die resultaat is 'n stel waardes. Kies die toepaslike nommer uit hulle. Kom ons kyk na vier aanwysers waarvoor sensitiwiteitsanalise op die gebied van finansies uitgevoer word:

1. Netto Huidige Waarde – Bereken deur die bedrag van die belegging van die bedrag van inkomste af te trek.
2. Interne opbrengskoers / wins – dui aan hoeveel wins in 'n jaar uit 'n belegging ontvang moet word.
3. Die terugbetalingsverhouding is die verhouding van alle winste tot die aanvanklike belegging.
4. Verdiskonteerde winsindeks – dui die doeltreffendheid van die belegging aan.

Formule

Inbedding sensitiwiteit kan met behulp van hierdie formule bereken word: Change in output parameter in % / Change in input parameter in %.

Die uitset- en invoerparameters kan die waardes wees wat vroeër beskryf is.

1. U moet die resultaat onder standaardtoestande ken.
2. Ons vervang een van die veranderlikes en monitor die veranderinge in die resultaat.
3. Ons bereken die persentasie verandering van beide parameters relatief tot die gevestigde toestande.
4. Ons voeg die verkryde persentasies in die formule in en bepaal die sensitiwiteit.

'n Voorbeeld van sensitiwiteitsanalise van 'n beleggingsprojek in Excel

Vir 'n beter begrip van die ontledingsmetodologie is 'n voorbeeld nodig. Kom ons ontleed die projek met die volgende bekende data:

1. Vul die tabel in om die projek daarop te ontleed.

2. Ons bereken die kontantvloei deur die OFFSET-funksie te gebruik. In die aanvanklike stadium is die vloei gelyk aan die beleggings. Vervolgens pas ons die formule toe: =IF(AFSET(Getal;1;)=2;SOM(Invloei 1:Uitvloei 1); SOM(Invloei 1:Uitvloei 1)+$B$ 5)
   Selbenamings in die formule kan verskil, afhangende van die uitleg van die tabel. Aan die einde word die waarde van die aanvanklike data bygevoeg – die bergingswaarde.

3. Ons bepaal die tydperk waarvoor die projek sal betaal. Vir die aanvanklike tydperk gebruik ons ​​hierdie formule: =SOMMER(G7: g17;»<0″). Die selreeks is die kontantvloeikolom. Vir verdere tydperke pas ons hierdie formule toe: =Aanvanklike periode+IF(Eerste e.stroom>0; Eerste e.stroom;0). Die projek is by die gelykbreekpunt oor 4 jaar.

4. Ons skep 'n kolom vir die nommers van daardie tydperke wanneer die projek vrugte afwerp.

5. Ons bereken die opbrengs op belegging. Dit is nodig om 'n uitdrukking te maak waar die wins in 'n bepaalde tydperk deur die aanvanklike belegging gedeel word.

6. Ons bepaal die afslagfaktor deur hierdie formule te gebruik: =1/(1+skyf.%) ^Getal.

7. Ons bereken die huidige waarde deur gebruik te maak van vermenigvuldiging – die kontantvloei word vermenigvuldig met die verdiskonteringsfaktor.

8. Kom ons bereken PI (winsgewendheidsindeks). Die huidige waarde oor tyd word gedeel deur die belegging aan die begin van die projek.

9. Kom ons definieer die interne opbrengskoers deur die IRR-funksie te gebruik: =IRR(Omvang van kontantvloei).

Belegging Sensitiwiteit Analise Gebruik Datablad

Vir die ontleding van projekte op die gebied van belegging is ander metodes beter geskik as 'n datatabel. Baie gebruikers ervaar verwarring wanneer hulle 'n formule saamstel. Om die afhanklikheid van een faktor van veranderinge in ander uit te vind, moet jy die korrekte selle kies om berekeninge in te voer en data te lees.

Faktor- en verspreidingsanalise in Excel met berekeningsoutomatisering

Nog 'n tipologie van sensitiwiteitsanalise is faktoranalise en variansieanalise. Die eerste tipe definieer die verhouding tussen getalle, die tweede toon die afhanklikheid van een veranderlike van ander.

ANOVA in Excel

Die doel van so 'n ontleding is om die veranderlikheid van die waarde in drie komponente te verdeel:

1. Veranderlikheid as gevolg van die invloed van ander waardes.
2. Veranderinge as gevolg van die verhouding van die waardes wat dit beïnvloed.
3. Willekeurige veranderinge.

Kom ons voer variansieanalise uit deur die Excel-byvoeging “Data-analise”. As dit nie geaktiveer is nie, kan dit in die instellings geaktiveer word.

Die aanvanklike tabel moet twee reëls volg: daar is een kolom vir elke waarde, en die data daarin is in stygende of dalende volgorde gerangskik. Dit is nodig om die invloed van die vlak van onderwys op gedrag in konflik na te gaan.

1. Vind die Data-analise-instrument in die Data-oortjie en maak sy venster oop. In die lys moet jy eenrigtinganalise van variansie kies.

2. Vul die reëls van die dialoogkassie in. Die invoerinterval is alle selle, uitgesluit kopskrifte en nommers. Groepeer volgens kolomme. Ons vertoon die resultate op 'n nuwe blad.

Aangesien die waarde in die geel sel groter as een is, kan die aanname as verkeerd beskou word – daar is geen verband tussen opvoeding en gedrag in konflik nie.

Faktorontleding in Excel: 'n voorbeeld

Kom ons ontleed die verhouding van data op die gebied van verkope – dit is nodig om gewilde en ongewilde produkte te identifiseer. Aanvanklike inligting:

1. Ons moet uitvind na watter goederevraag die meeste gedurende die tweede maand toegeneem het. Ons stel 'n nuwe tabel saam om die groei en afname in vraag te bepaal. Groei word bereken met behulp van hierdie formule: =IF((Demand 2-Demand 1)>0; Demand 2- Demand 1;0). Verminder formule: =IF(Groei=0; Vraag 1- Vraag 2;0).

2. Bereken die groei in vraag na goedere as 'n persentasie: =IF(Groei/Uitkoms 2 =0; Verminder/Uitkoms 2; Groei/Uitkoms 2).

3. Kom ons maak 'n grafiek vir duidelikheid – kies 'n reeks selle en skep 'n histogram deur die "Voeg in" oortjie. In die instellings moet jy die vul verwyder, dit kan gedoen word deur die Format Data Series-instrument.

Tweerigting-analise van variansie in Excel

Variansie-analise word met verskeie veranderlikes uitgevoer. Oorweeg dit met 'n voorbeeld: jy moet uitvind hoe vinnig die reaksie op 'n klank van verskillende volume by mans en vroue manifesteer.

1. Ons maak "Data-analise" oop, in die lys moet u 'n tweerigting-analise van variansie vind sonder herhalings.
2. Invoerinterval – selle wat data bevat (sonder 'n kopskrif). Ons vertoon die resultate op 'n nuwe blad en klik "OK".

Die F-waarde is groter as die F-kritiese, wat beteken dat die vloer die spoed van reaksie op klank beïnvloed.

Gevolgtrekking

In hierdie artikel is sensitiwiteitsanalise in 'n Excel-sigblad breedvoerig bespreek, sodat elke gebruiker die metodes van die toepassing daarvan sal kan verstaan.