Tundlikkuse analüüs Excelis (näidisandmeleht)

Finantsvaldkonna protsessid on alati omavahel seotud – üks tegur sõltub teisest ja muutub koos sellega. Jälgige neid muudatusi ja mõistke, mida tulevikus oodata, kasutades võib-olla Exceli funktsioone ja arvutustabeli meetodeid.

Andmetabeliga mitme tulemuse saamine

Andmelehe võimalused on mis-kui-analüüsi elemendid – seda tehakse sageli Microsoft Exceli kaudu. See on tundlikkusanalüüsi teine ​​nimi.

Ülevaade

Andmetabel on teatud tüüpi lahtrite vahemik, mida saab kasutada probleemide lahendamiseks, muutes mõne lahtri väärtusi. See luuakse siis, kui on vaja jälgida valemi komponentide muutusi ja saada tulemuste värskendusi vastavalt nendele muudatustele. Uurime, kuidas andmetabeleid uurimistöös kasutada ja mis tüüpi need on.

Andmetabelite põhitõed

Andmetabeleid on kahte tüüpi, need erinevad komponentide arvu poolest. Peate koostama tabeli, mis keskendub väärtuste arvule uXNUMXbuXNUMXb, mida peate sellega kontrollima.

Statistikud kasutavad ühte muutujate tabelit, kui ühes või mitmes avaldises on ainult üks muutuja, mis võib nende tulemust muuta. Näiteks kasutatakse seda sageli koos funktsiooniga PMT. Valem on loodud tavamakse summa arvutamiseks ja arvestab lepingus määratud intressimäära. Sellistes arvutustes kirjutatakse muutujad ühte veergu ja arvutuste tulemused teise. Näide 1 muutujaga andmeplaadist:

Järgmisena kaaluge 2 muutujaga plaate. Neid kasutatakse juhtudel, kui mis tahes näitaja muutust mõjutavad kaks tegurit. Need kaks muutujat võivad sattuda teise laenuga seotud tabelisse, mille abil saab määrata optimaalse tagasimakseperioodi ja kuumakse suuruse. Selles arvutuses peate kasutama ka PMT funktsiooni. Kahe muutujaga tabeli näide:

Andmetabeli loomine ühe muutujaga

Mõelge analüüsimeetodile väikese raamatupoe näitel, kus laos on vaid 100 raamatut. Mõnda neist saab müüa kallimalt (50 dollarit), ülejäänud maksavad ostjatele vähem (20 dollarit). Arvestatakse kokku kogu kauba müügitulu – omanik otsustas, et müüb 60% raamatutest kallilt maha. Peate uurima, kuidas tulud suurenevad, kui tõstate suurema kaubakoguse hinda – 70% ja nii edasi.

Pöörake tähelepanu! Kogutulu tuleb arvutada valemiga, muidu ei saa andmetabelit koostada.

1. Valige lehe servast eemal vaba lahter ja kirjutage sinna valem: =Kogutulu lahter. Näiteks kui sissetulek on kirjutatud lahtrisse C14 (näidatud on juhuslik tähistus), peate kirjutama selle: =S14.
2. Selle lahtri vasakpoolsesse veergu kirjutame kauba mahu protsendi – mitte alla, see on väga oluline.
3. Valime lahtrite vahemiku, kus asub protsendiveerg ja link kogutulule.

4. Leiame vahekaardilt “Andmed” üksuse “Mis siis, kui analüüs” ja klõpsame sellel – avanevas menüüs valime “Andmetabel”.

5. Avaneb väike aken, kus peate veerus „Asenda väärtused ridade kaupa …” määrama lahtri algselt kõrge hinnaga müüdud raamatute protsendiga. See samm tehakse kogutulu ümberarvutamiseks, võttes arvesse kasvavat protsenti.

Pärast tabeli koostamiseks andmete sisestamise aknas nupu “OK” vajutamist ilmuvad ridadele arvutuste tulemused.

Valemi lisamine ühe muutuja andmetabelisse

Tabelist, mis aitas arvutada toimingu ainult ühe muutujaga, saate täiendava valemi lisamisega luua keeruka analüüsitööriista. See tuleb sisestada juba olemasoleva valemi kõrvale – näiteks kui tabel on reaorienteeritud, siis sisestame avaldise olemasolevast paremal asuvasse lahtrisse. Kui veeru orientatsioon on määratud, kirjutame uue valemi vana alla. Järgmisena järgige algoritmi:

1. Valige uuesti lahtrite vahemik, kuid nüüd peaks see sisaldama uut valemit.
2. Avage analüüsimenüü "mis siis, kui" ja valige "Andmeleht".
3. Lisame uue valemi vastavale väljale ridade või veergude kaupa, olenevalt plaadi orientatsioonist.

Looge kahe muutujaga andmetabel

Sellise tabeli algus on veidi erinev – protsentuaalsete väärtuste kohale tuleb panna link kogutulu juurde. Järgmisena teeme järgmised sammud:

1. Kirjutage hinnavalikud ühele reale koos lingiga tulule – iga hinna kohta üks lahter.
2. Valige lahtrite vahemik.

3. Ava andmetabeli aken, nagu ühe muutujaga tabeli koostamisel – tööriistariba vahekaardi “Andmed” kaudu.
4. Asendage veerus "Asenda väärtused veergude järgi …" algse kõrge hinnaga lahter.
5. Lisage veergu "Asenda väärtused ridade järgi …" lahter kallite raamatute müügi esialgse protsendiga ja klõpsake "OK".

Selle tulemusena täitub kogu tabel võimalike sissetulekute summadega erinevate kaupade müügitingimustega.

Kiirendage andmetabeleid sisaldavate töölehtede arvutusi

Kui vajate andmetabelis kiireid arvutusi, mis ei käivita kogu töövihiku ümberarvutamist, saate protsessi kiirendamiseks teha mõned asjad.

1. Avage valikute aken, valige parempoolsest menüüst üksus "Valemid".
2. Valige jaotises "Arvutused töövihikus" üksus "Automaatne, välja arvatud andmetabelid".

3. Arvutame tabelis olevad tulemused käsitsi ümber. Selleks valige valemid ja vajutage klahvi F.

Muud tööriistad tundlikkuse analüüsi tegemiseks

Programmis on ka teisi tööriistu, mis aitavad teil tundlikkusanalüüsi teha. Need automatiseerivad mõned toimingud, mida muidu tuleks käsitsi teha.

1. Funktsioon “Parameetrite valik” sobib siis, kui soovitud tulemus on teada ja sellise tulemuse saamiseks on vaja teada muutuja sisendväärtust.
2. "Otsi lahendust" on lisandmoodul probleemide lahendamiseks. Vaja on seada piirid ja osutada neile, misjärel süsteem vastuse leiab. Lahendus määratakse väärtuste muutmise teel.
3. Tundlikkuse analüüsi saab teha stsenaariumihalduri abil. Selle tööriista leiate vahekaardi Andmed all olevast mis-kui-analüüsi menüüst. See asendab väärtusi mitmes lahtris – arv võib ulatuda 32-ni. Dispetšer võrdleb neid väärtusi nii, et kasutaja ei peaks neid käsitsi muutma. Skriptihalduri kasutamise näide:

Investeerimisprojekti tundlikkuse analüüs Excelis

Mis-kui-analüüs on eriti kasulik olukordades, kus on vaja prognoosida, näiteks investeerida. Analüütikud kasutavad seda meetodit, et selgitada välja, kuidas muutub ettevõtte aktsia väärtus mõne teguri muutumise tagajärjel.

Investeeringute tundlikkuse analüüsi meetod

“Mis siis, kui” analüüsimisel kasutage loendust – käsitsi või automaatset. Väärtuste vahemik on teada ja need asendatakse valemiga ükshaaval. Tulemuseks on väärtuste kogum. Valige nende hulgast sobiv arv. Vaatleme nelja näitajat, mille tundlikkusanalüüsi finantsvaldkonnas tehakse:

1. Nüüdispuhasväärtus – arvutatakse tulusummast investeeringu summa lahutamisel.
2. Sisemine tootlus/kasum – näitab, kui palju kasumit on vaja investeeringult aastas saada.
3. Tasuvuskordaja on kogu kasumi suhe alginvesteeringusse.
4. Diskonteeritud kasumi indeks – näitab investeeringu efektiivsust.

Valem

Manustamise tundlikkust saab arvutada järgmise valemi abil: Väljundparameetri muutus % / Sisendparameetri muutus %.

Väljund- ja sisendparameetrid võivad olla varem kirjeldatud väärtused.

1. Sa pead teadma tulemust standardtingimustes.
2. Asendame ühe muutujatest ja jälgime tulemuse muutusi.
3. Arvutame mõlema parameetri muutuse protsentides kehtestatud tingimuste suhtes.
4. Saadud protsendid sisestame valemisse ja määrame tundlikkuse.

Näide investeerimisprojekti tundlikkuse analüüsist Excelis

Analüüsi metoodika paremaks mõistmiseks on vaja näidet. Analüüsime projekti järgmiste teadaolevate andmetega:

1. Projekti analüüsimiseks täitke tabel.

2. Arvutame rahavoo funktsiooni OFFSET abil. Algstaadiumis on voog võrdne investeeringutega. Järgmisena rakendame valemit: =IF(OFFSET(Arv,1;)=2;SUM(Sissevool 1:Väljavool 1); SUM(Sissevool 1:Väljavool 1)+$B$ 5)
   Lahtrite tähistused valemis võivad olenevalt tabeli paigutusest olla erinevad. Lõpus lisatakse algandmete väärtus – päästeväärtus.

3. Määrame perioodi, mille jooksul projekt end ära tasub. Esialgsel perioodil kasutame järgmist valemit: =SUMMESLY(G7: G17;»<0″). Lahtrivahemik on rahavoogude veerg. Edasiste perioodide puhul rakendame järgmist valemit: =Algperiood+IF(Esimene e.voog>0; Esimene e.voog;0). Projekt on 4 aasta pärast tasuvuspunktis.

4. Loome veeru nende perioodide numbrite jaoks, mil projekt end ära tasub.

5. Arvutame investeeringutasuvuse. Vaja on teha avaldis, kus teatud perioodi kasum jagatakse alginvesteeringuga.

6. Määrame allahindlusteguri järgmise valemi abil: =1/(1+ketas.%) ^Arv.

7. Nüüdisväärtuse arvutame korrutise abil – rahavoog korrutatakse diskontoteguriga.

8. Arvutame PI (kasumlikkuse indeks). Nüüdisväärtus ajas jagatakse projekti alguses tehtud investeeringuga.

9. Määratleme sisemise tulumäära funktsiooni IRR abil: =IRR (rahavoo vahemik).

Investeeringute tundlikkuse analüüs andmelehe abil

Investeerimisvaldkonna projektide analüüsimiseks sobivad andmetabelist paremini muud meetodid. Paljud kasutajad kogevad valemi koostamisel segadust. Et teada saada ühe teguri sõltuvust teiste muutustest, tuleb arvutuste sisestamiseks ja andmete lugemiseks valida õiged lahtrid.

Faktor- ja dispersioonanalüüs Excelis koos arvutusautomaatikaga

Teine tundlikkusanalüüsi tüpoloogia on faktoranalüüs ja dispersioonanalüüs. Esimene tüüp määratleb arvudevahelise seose, teine ​​paljastab ühe muutuja sõltuvuse teistest.

ANOVA Excelis

Sellise analüüsi eesmärk on jagada väärtuse varieeruvus kolmeks komponendiks:

1. Muutlikkus muude väärtuste mõju tulemusena.
2. Muutused, mis tulenevad seda mõjutavate väärtuste suhtest.
3. Juhuslikud muutused.

Teeme dispersioonanalüüsi läbi Exceli lisandmooduli “Andmeanalüüs”. Kui see pole lubatud, saab selle seadetes lubada.

Esialgne tabel peab järgima kahte reeglit: iga väärtuse jaoks on üks veerg ja selles olevad andmed on järjestatud kasvavas või kahanevas järjekorras. Vaja on kontrollida haridustaseme mõju konfliktikäitumisele.

1. Otsige vahekaardilt Andmed üles andmeanalüüsi tööriist ja avage selle aken. Loendis peate valima ühesuunalise dispersioonanalüüsi.

2. Täitke dialoogiboksi read. Sisestusintervall hõlmab kõiki lahtreid, välja arvatud päised ja numbrid. Rühmitamine veergude järgi. Tulemused kuvame uuel lehel.

Kuna kollases lahtris on väärtus suurem kui üks, võib eeldust pidada vääraks – hariduse ja konfliktikäitumise vahel puudub seos.

Faktoranalüüs Excelis: näide

Analüüsime müügivaldkonna andmete seost – vaja on tuvastada populaarsed ja ebapopulaarsed tooted. Esialgne teave:

1. Peame välja selgitama, milliste kaupade järele nõudlus teisel kuul kõige enam kasvas. Nõudluse kasvu ja languse määramiseks koostame uue tabeli. Kasv arvutatakse järgmise valemi abil: =IF((nõudlus 2-nõudlus 1)>0; nõudlus 2- nõudlus 1;0). Vähendamise valem: =IF(Kasv=0; Nõudlus 1- Nõudlus 2;0).

2. Arvutage kaupade nõudluse kasv protsentides: =IF(kasv/tulemus 2 =0; langus/tulemus 2; kasv/tulemus 2).

3. Teeme selguse huvides diagrammi – valige lahtrite vahemik ja looge vahekaardi „Sisesta” kaudu histogramm. Seadetes peate täidise eemaldama, seda saab teha tööriista Format Data Series kaudu.

Kahesuunaline dispersioonanalüüs Excelis

Dispersioonanalüüs viiakse läbi mitme muutujaga. Mõelge sellele näitega: peate välja selgitama, kui kiiresti reaktsioon erineva helitugevusega helile meestel ja naistel avaldub.

1. Avame "Andmeanalüüsi", loendist peate leidma kahesuunalise kordusteta dispersioonanalüüsi.
2. Sisestusintervall – andmeid sisaldavad lahtrid (ilma päiseta). Kuvame tulemused uuel lehel ja klõpsame "OK".

F väärtus on suurem kui F-kriitiline väärtus, mis tähendab, et põrand mõjutab helile reageerimise kiirust.

Järeldus

Selles artiklis käsitleti üksikasjalikult Exceli arvutustabeli tundlikkuse analüüsi, et iga kasutaja mõistaks selle rakendusmeetodeid.