Herkkyysanalyysi Excelissä (esimerkkitietolomake)

Rahoitusalan prosessit ovat aina yhteydessä toisiinsa – yksi tekijä riippuu toisesta ja muuttuu sen mukana. Seuraa näitä muutoksia ja ymmärrä, mitä odottaa tulevaisuudessa, ehkä käyttämällä Excelin toimintoja ja taulukkolaskentamenetelmiä.

Useiden tulosten saaminen tietotaulukon avulla

Tietotaulukko-ominaisuudet ovat osa mitä jos -analyysiä, joka tehdään usein Microsoft Excelin kautta. Tämä on herkkyysanalyysin toinen nimi.

Yleiskatsaus

Tietotaulukko on eräänlainen solualue, jota voidaan käyttää ongelmien ratkaisemiseen muuttamalla joidenkin solujen arvoja. Se luodaan, kun on tarpeen seurata kaavan komponenttien muutoksia ja saada päivityksiä tuloksiin näiden muutosten mukaisesti. Selvitetään, miten datataulukoita käytetään tutkimuksessa ja minkä tyyppisiä ne ovat.

Tietotaulukoiden perustiedot

Tietotaulukoita on kahdenlaisia, ne eroavat toisistaan ​​​​komponenttien lukumäärässä. Sinun on laadittava taulukko, jossa keskitytään uXNUMXbuXNUMXb-arvojen määrään, jotka sinun on tarkistettava sen avulla.

Tilastomiehet käyttävät yhtä muuttujataulukkoa, kun yhdessä tai useammassa lausekkeessa on vain yksi muuttuja, joka voi muuttaa niiden tulosta. Esimerkiksi sitä käytetään usein yhdessä PMT-toiminnon kanssa. Kaava on suunniteltu laskemaan varsinaisen maksun suuruus ja ottaa huomioon sopimuksessa määritellyn koron. Tällaisissa laskelmissa muuttujat kirjoitetaan yhteen sarakkeeseen ja laskelmien tulokset toiseen. Esimerkki arvokilvestä, jossa on 1 muuttuja:

Tarkastellaan seuraavaksi levyjä, joissa on 2 muuttujaa. Niitä käytetään tapauksissa, joissa kaksi tekijää vaikuttaa minkä tahansa indikaattorin muutokseen. Nämä kaksi muuttujaa voivat päätyä toiseen lainaan liittyvään taulukkoon, jonka avulla voidaan määrittää optimaalinen takaisinmaksuaika ja kuukausierän suuruus. Tässä laskelmassa sinun on käytettävä myös PMT-toimintoa. Esimerkki taulukosta, jossa on 2 muuttujaa:

Tietotaulukon luominen yhdellä muuttujalla

Harkitse analyysimenetelmää pienessä kirjakaupassa, jossa on vain 100 kirjaa varastossa. Jotkut niistä voidaan myydä kalliimmin (50 dollaria), loput maksavat ostajille vähemmän (20 dollaria). Kaikkien tavaroiden myynnistä saadut kokonaistulot lasketaan – omistaja päätti myydä 60 % kirjoista korkeaan hintaan. Sinun on selvitettävä, kuinka tulot kasvavat, jos nostat suuremman tavaramäärän hintaa - 70% ja niin edelleen.

Kiinnitä huomiota! Kokonaistulot on laskettava kaavalla, muuten tietotaulukkoa ei voida laatia.

1. Valitse vapaa solu arkin reunasta ja kirjoita siihen kaava: =Kokonaistulojen solu. Esimerkiksi, jos tulot kirjoitetaan soluun C14 (satunnainen nimitys on merkitty), sinun on kirjoitettava tämä: =S14.
2. Kirjoitamme prosenttiosuuden tavaroiden määrästä sarakkeeseen tämän solun vasemmalla puolella – ei sen alapuolelle, tämä on erittäin tärkeää.
3. Valitsemme solualueen, jossa prosenttisarake ja linkki kokonaistuloihin sijaitsevat.

4. Etsimme "Data" -välilehdeltä kohdan "Entä jos analyysi" ja napsautamme sitä - valitse avautuvasta valikosta vaihtoehto "Datataulukko".

5. Pieni ikkuna avautuu, jossa sinun on määritettävä solu, jossa on alun perin korkeaan hintaan myytyjen kirjojen prosenttiosuus "Korvaa arvot riveillä …" -sarakkeessa. Tämä vaihe tehdään kokonaistulon laskemiseksi uudelleen ottaen huomioon kasvava prosenttiosuus.

Kun olet napsauttanut ”OK”-painiketta ikkunassa, johon tiedot syötettiin taulukon laatimista varten, laskelmien tulokset näkyvät riveillä.

Kaavan lisääminen yhden muuttujan tietotaulukkoon

Taulukosta, joka auttoi laskemaan toiminnon vain yhdellä muuttujalla, voit luoda kehittyneen analyysityökalun lisäämällä ylimääräisen kaavan. Se on syötettävä jo olemassa olevan kaavan viereen – esimerkiksi jos taulukko on rivisuuntautunut, kirjoitamme lausekkeen olemassa olevan kaavan oikealla puolella olevaan soluun. Kun sarakkeen suunta on asetettu, kirjoitamme uuden kaavan vanhan alle. Noudata seuraavaksi algoritmia:

1. Valitse solualue uudelleen, mutta nyt sen pitäisi sisältää uusi kaava.
2. Avaa "mitä jos" -analyysivalikko ja valitse "Datasheet".
3. Lisäämme uuden kaavan vastaavaan kenttään riveinä tai sarakkeina levyn suunnasta riippuen.

Luo datataulukko kahdella muuttujalla

Tällaisen taulukon alku on hieman erilainen – sinun on laitettava linkki kokonaistuloon prosenttiarvojen yläpuolelle. Seuraavaksi suoritamme nämä vaiheet:

1. Kirjoita hintavaihtoehdot yhdelle riville tulolinkin kera – yksi solu jokaista hintaa kohden.
2. Valitse solualue.

3. Avaa tietotaulukkoikkuna, kuten käännettäessäsi taulukkoa yhdellä muuttujalla – työkalupalkin "Data"-välilehden kautta.
4. Korvaa sarakkeessa "Korvaa arvot sarakkeilla ..." solu, jonka alkuperäinen hinta on korkea.
5. Lisää "Korvaa arvot riveillä …" -sarakkeeseen solu kalliiden kirjojen myynnin alkuperäisellä prosenttiosuudella ja napsauta "OK".

Tämän seurauksena koko taulukko on täynnä mahdollisia tuloja erilaisilla tavaroiden myyntiehdoilla.

Nopeuta laskelmia tietotaulukoita sisältäville laskentataulukoille

Jos tarvitset tietotaulukossa nopeita laskelmia, jotka eivät käynnistä koko työkirjan uudelleenlaskentaa, voit nopeuttaa prosessia muutamilla tavoilla.

1. Avaa asetusikkuna, valitse oikealla olevasta valikosta kohta ”Kaavat”.
2. Valitse "Työkirjan laskelmat" -osiossa "Automaattinen, paitsi tietotaulukot".

3. Lasketaan taulukon tulokset uudelleen manuaalisesti. Voit tehdä tämän valitsemalla kaavat ja painamalla F-näppäintä.

Muut työkalut herkkyysanalyysin suorittamiseen

Ohjelmassa on muita työkaluja, joiden avulla voit suorittaa herkkyysanalyysin. Ne automatisoivat joitain toimintoja, jotka muuten olisi tehtävä manuaalisesti.

1. "Parametrivalinta" -toiminto sopii, jos haluttu tulos on tiedossa ja sinun on tiedettävä muuttujan syöttöarvo sellaisen tuloksen saamiseksi.
2. "Etsi ratkaisua" on lisäosa ongelmien ratkaisemiseen. On tarpeen asettaa rajat ja osoittaa niihin, minkä jälkeen järjestelmä löytää vastauksen. Ratkaisu määräytyy arvoja muuttamalla.
3. Herkkyysanalyysi voidaan tehdä Scenario Managerilla. Tämä työkalu löytyy Tiedot-välilehden mitä jos -analyysivalikosta. Se korvaa arvot useissa soluissa – luku voi olla 32. Välittäjä vertailee näitä arvoja, jotta käyttäjän ei tarvitse muuttaa niitä manuaalisesti. Esimerkki komentosarjanhallinnan käytöstä:

Investointiprojektin herkkyysanalyysi Excelissä

Mitä jos -analyysi on erityisen hyödyllinen tilanteissa, joissa tarvitaan ennustamista, kuten investointeja. Analyytikot käyttävät tätä menetelmää selvittääkseen, kuinka yrityksen osakkeen arvo muuttuu joidenkin tekijöiden muutosten seurauksena.

Investointiherkkyysanalyysimenetelmä

Analysoidessasi "mitä jos" käytä luettelointia - manuaalista tai automaattista. Arvoalueet tunnetaan ja ne korvataan kaavaan yksitellen. Tuloksena on joukko arvoja. Valitse niistä sopiva numero. Tarkastellaan neljää indikaattoria, joiden herkkyysanalyysi tehdään rahoitusalalla:

1. Nettonykyarvo – Lasketaan vähentämällä sijoituksen määrä tulojen määrästä.
2. Sisäinen tuotto/voittoaste – ilmaisee, kuinka paljon voittoa sijoituksesta vaaditaan vuodessa.
3. Takaisinmaksusuhde on kaikkien tuottojen suhde alkuinvestointeihin.
4. Diskontoitu voittoindeksi – osoittaa sijoituksen tehokkuuden.

Kaava

Upotusherkkyys voidaan laskea tällä kaavalla: Tulosparametrin muutos % / Tuloparametrin muutos %.

Lähtö- ja tuloparametrit voivat olla aiemmin kuvattuja arvoja.

1. Sinun on tiedettävä tulos normaaleissa olosuhteissa.
2. Korvaamme yhden muuttujista ja seuraamme tuloksen muutoksia.
3. Laskemme molempien parametrien prosentuaalisen muutoksen suhteessa määritettyihin olosuhteisiin.
4. Lisäämme saadut prosenttiosuudet kaavaan ja määritämme herkkyyden.

Esimerkki investointiprojektin herkkyysanalyysistä Excelissä

Analyysimenetelmän ymmärtämiseksi paremmin tarvitaan esimerkki. Analysoidaan projektia seuraavilla tunnetuilla tiedoilla:

1. Täytä taulukko analysoidaksesi siihen liittyvää projektia.

2. Laskemme kassavirran OFFSET-funktiolla. Alkuvaiheessa virta on yhtä suuri kuin investoinnit. Seuraavaksi käytämme kaavaa: =JOS(SIIRTO(Numero,1;)=2;SUMMA(tulo 1:lähtö 1); SUM(Tuovirta 1:lähtö 1)+$B$ 5)
   Kaavan solujen nimet voivat olla erilaisia ​​riippuen taulukon asettelusta. Lopuksi lisätään alkutiedon arvo – pelastusarvo.

3. Määritämme ajanjakson, jonka aikana projekti maksaa itsensä takaisin. Alkujaksolla käytämme tätä kaavaa: =SUMMESLY(G7: G17;»<0″). Solualue on kassavirtasarake. Muiden ajanjaksojen osalta käytämme tätä kaavaa: =Alkujakso+IF(Ensimmäinen e.virta>0; Ensimmäinen e.virta;0). Projekti on kannattavuusrajassa 4 vuodessa.

4. Luomme sarakkeen niiden ajanjaksojen numeroille, jolloin projekti kannattaa.

5. Laskemme sijoitetun pääoman tuoton. On tarpeen tehdä lauseke, jossa tietyn ajanjakson voitto jaetaan alkusijoituksella.

6. Määritämme alennuskertoimen käyttämällä tätä kaavaa: =1/(1+levy.%) ^ Numero.

7. Laskemme nykyarvon kertolaskulla – kassavirta kerrotaan diskonttokertoimella.

8. Lasketaan PI (kannattavuusindeksi). Nykyarvo ajan kuluessa jaetaan investoinnilla projektin alussa.

9. Määritetään sisäinen tuottoprosentti käyttämällä IRR-funktiota: =IRR (kassavirran vaihteluväli).

Investointiherkkyysanalyysi käyttäen tietolehteä

Investointialan hankkeiden analysointiin muut menetelmät sopivat paremmin kuin tietotaulukko. Monet käyttäjät kokevat hämmennystä kaavaa laatiessaan. Selvittääksesi yhden tekijän riippuvuuden muiden muutoksista, sinun on valittava oikeat solut laskelmien syöttämistä ja tietojen lukemista varten.

Tekijä- ja dispersioanalyysi Excelissä laskentaautomaatiolla

Toinen herkkyysanalyysin tyyppi on tekijäanalyysi ja varianssianalyysi. Ensimmäinen tyyppi määrittelee lukujen välisen suhteen, toinen paljastaa yhden muuttujan riippuvuuden muista.

ANOVA Excelissä

Tällaisen analyysin tarkoituksena on jakaa arvon vaihtelu kolmeen osaan:

1. Vaihtelevuus muiden arvojen vaikutuksesta.
2. Muutoksia, jotka johtuvat siihen vaikuttavien arvojen suhteesta.
3. Satunnaisia ​​muutoksia.

Suoritetaan varianssianalyysi Excelin "Data Analysis" -apuohjelman avulla. Jos se ei ole käytössä, se voidaan ottaa käyttöön asetuksista.

Alkutaulukon tulee noudattaa kahta sääntöä: jokaiselle arvolle on yksi sarake, ja siinä olevat tiedot on järjestetty nousevaan tai laskevaan järjestykseen. On tarpeen tarkistaa koulutustason vaikutus käyttäytymiseen konflikteissa.

1. Etsi Data-analyysityökalu Tiedot-välilehdestä ja avaa sen ikkuna. Valitse luettelosta yksisuuntainen varianssianalyysi.

2. Täytä valintaikkunan rivit. Syöttöväli on kaikki solut, lukuun ottamatta otsikoita ja numeroita. Ryhmittele sarakkeiden mukaan. Näytämme tulokset uudella arkilla.

Koska keltaisen solun arvo on suurempi kuin yksi, oletusta voidaan pitää virheellisenä – koulutuksen ja konfliktin käyttäytymisen välillä ei ole yhteyttä.

Tekijäanalyysi Excelissä: esimerkki

Analysoidaan tietojen suhdetta myynnin alalla – on tarpeen tunnistaa suosittuja ja epäsuosittuja tuotteita. Alustavat tiedot:

1. Meidän on selvitettävä, minkä tavaroiden kysyntä kasvoi eniten toisen kuukauden aikana. Laadimme uuden taulukon kysynnän kasvun ja laskun selvittämiseksi. Kasvu lasketaan seuraavalla kaavalla: =JOS((Kysyntä 2 - Kysyntä 1)>0; Kysyntä 2 - Kysyntä 1; 0). Pienennä kaava: =JOS(Kasvu=0; Kysyntä 1 - Kysyntä 2;0).

2. Laske tavaroiden kysynnän kasvu prosentteina: =JOS(Kasvu/Tulos 2 =0; Vähennys/Tulos 2; Kasvu/Tulos 2).

3. Tehdään kaavio selvyyden vuoksi – valitse solualue ja luo histogrammi "Lisää"-välilehden kautta. Asetuksista sinun on poistettava täyttö, tämä voidaan tehdä Format Data Series -työkalulla.

Kaksisuuntainen varianssianalyysi Excelissä

Varianssianalyysi suoritetaan useilla muuttujilla. Harkitse tätä esimerkillä: sinun on selvitettävä, kuinka nopeasti reaktio eri äänenvoimakkuuteen ilmenee miehissä ja naisissa.

1. Avaamme "Data Analysis", luettelosta sinun on löydettävä kaksisuuntainen varianssianalyysi ilman toistoja.
2. Syöttöväli – solut, jotka sisältävät dataa (ilman otsikkoa). Näytämme tulokset uudella arkilla ja napsautamme "OK".

F-arvo on suurempi kuin F-kriittinen, mikä tarkoittaa, että lattia vaikuttaa äänireaktionopeuteen.

Yhteenveto

Tässä artikkelissa Excel-taulukon herkkyysanalyysiä käsiteltiin yksityiskohtaisesti, jotta jokainen käyttäjä voi ymmärtää sen soveltamismenetelmät.