Sensitivity Analysis muExcel (Sample Datasheet)

Maitiro mumunda wezvemari anogara akabatana - chimwe chinhu chinoenderana nechimwe uye chinoshanduka nacho. Tevera shanduko idzi uye unzwisise zvekutarisira mune ramangwana, pamwe uchishandisa Excel mabasa uye nzira dzespredishiti.

Kuwana mhinduro dzakawanda netafura yedata

Datasheet kugona zvinhu zvei-kana ongororo-inowanzoitwa kuburikidza neMicrosoft Excel. Iri ndiro zita rechipiri rekuongorora manzwiro.

tsananguro

Tafura yedata imhando yemhando yemasero anogona kushandiswa kugadzirisa matambudziko nekushandura hunhu mune mamwe maseru. Iyo inogadzirwa kana zvichidikanwa kuchengetedza shanduko muzvikamu zvefomula uye kugamuchira zvigadziriso kune zvabuda, zvinoenderana nekuchinja uku. Ngationei kuti tingashandisa sei matafura edata mukutsvaga, uye kuti ndeapi marudzi.

Basics nezve data matafura

Kune marudzi maviri ematafura e data, anosiyana muhuwandu hwezvikamu. Iwe unofanirwa kuunganidza tafura ine tarisiro pane iyo nhamba yehuhuhuhuhudyuXNUMXbyaunofanirwa kutarisa nayo.

Vezviverengero vanoshandisa tafura imwe chete inoshanduka kana paine musiyano mumwechete kana akawanda mataurirwo anogona kushandura mhedzisiro yavo. Semuenzaniso, inowanzoshandiswa pamwe chete nebasa re PMT. Iyo formula yakagadzirirwa kuverenga huwandu hwekubhadhara nguva dzose uye inofunga nezveyero yemubereko inotsanangurwa muchibvumirano. Muchiverengero chakadaro, zvinoshanduka zvinonyorwa mune imwe koramu, uye mhedzisiro yekuverenga mune imwe. Muenzaniso weplate yedata ine 1 musiyano:

Zvadaro, funga mahwendefa ane 2 variables. Izvo zvinoshandiswa mumamiriro ezvinhu apo zvinhu zviviri zvinokonzera kuchinja mune chero chiratidzo. Izvo zvakasiyana-siyana zviviri zvinogona kuguma mune imwe tafura yakabatana nechikwereti, iyo inogona kushandiswa kuona nguva yakakwana yekubhadhara uye chiyero chekubhadhara kwemwedzi. Mukuverenga uku, iwe zvakare unofanirwa kushandisa iyo PMT basa. Muenzaniso wetafura ine 2 variables:

Kugadzira tafura yedata ine musiyano mumwe

Funga nezvenzira yekuongorora uchishandisa muenzaniso wechitoro chidiki chemabhuku chine mabhuku zana chete mustock. Mamwe acho anogona kutengeswa zvinodhura ($100), imwe yacho inodhura vatengi zvishoma ($50). Mari yose kubva pakutengeswa kwezvinhu zvose inoverengwa - muridzi wacho akasarudza kuti aizotengesa 20% yemabhuku pamutengo wakakwirira. Iwe unofanirwa kuziva kuti mari ichawedzera sei kana iwe ukawedzera mutengo wehuwandu hukuru hwezvinhu - 60%, zvichingodaro.

Teerera! Mari yese inofanirwa kuverengerwa uchishandisa formula, zvikasadaro hazvizogone kuunganidza tafura yedata.

1. Sarudza sero remahara kure nekumucheto kwepepa uye nyora fomula mairi: =Sero remari yese. Semuyenzaniso, kana mari yakanyorwa muchitokisi C14 (random zita rinoratidzwa), unofanira kunyora izvi: =S14.
2. Isu tinonyora chikamu chehuwandu hwehuwandu hwezvinhu mumutsara kuruboshwe rwesero iyi - kwete pasi payo, izvi zvakakosha zvikuru.
3. Isu tinosarudza huwandu hwemasero uko chikamu chezana chikamu uye chinongedzo kune iyo yakazara mari inowanikwa.

4. Isu tinowana pane "Data" tebhu chinhu "Ko kana kuongorora" uye tinya pairi - mumenyu inovhura, sarudza sarudzo "Data tafura".

5. Hwindo diki rinovhurika paunoda kutsanangura sero ine muzana yemabhuku akatanga kutengeswa nemutengo wepamusoro mu "Substitute values ​​​​by rows in ..." column. Danho iri rinoitwa kuitira kuverengera zvakare mari yese tichifunga nezvekuwedzera kwezana.

Mushure mekudzvanya bhatani re "OK" muhwindo umo data rakaiswa pakugadzira tafura, mhedzisiro yekuverenga ichaonekwa mumitsetse.

Kuwedzera Formula kune Imwechete Variable Data Tafura

Kubva patafura yakabatsira kuverenga chiitiko chine musiyano mumwe chete, unogona kugadzira chishandiso chekuongorora chakanyanya nekuwedzera imwe fomula. Inofanira kuiswa padivi peiyo yatove iripo formula - semuenzaniso, kana tafura yakatarisana nemutsara, tinoisa chirevo muchitokisi kurudyi rweiyo iripo. Kana iyo column orientation yaiswa, tinonyora fomula nyowani pasi peiyo yekare. Zvadaro, tevera algorithm:

1. Sarudza huwandu hwemasero zvakare, asi ikozvino inofanirwa kusanganisira iyo nyowani fomula.
2. Vhura iyo "chii kana" yekuongorora menyu uye sarudza "Datasheet".
3. Isu tinowedzera mutsva mutsva kumunda unoenderana mumitsara kana makoramu, zvichienderana nekutarisa kweplate.

Gadzira tafura yedata ine mavara maviri

Kutanga kwetafura yakadaro kwakasiyana zvishoma - iwe unoda kuisa chibatanidzo kune mari yose pamusoro pehuwandu hwehuwandu. Zvadaro, tinoita matanho aya:

1. Nyora sarudzo dzemitengo mumutsara mumwe uine chinongedzo chemari - sero rimwe pamutengo wega wega.
2. Sarudza huwandu hwemasero.

3. Vhura hwindo retafura yedata, sekunge uchigadzira tafura ine imwe shanduko - kuburikidza ne "Data" tab pane toolbar.
4. Inotsiva mukoramu "Simba kukosha nemakoramu mu ..." sero rine mutengo wekutanga wepamusoro.
5. Wedzera sero ine yekutanga muzana yekutengesa kwemabhuku anodhura kune "Tsinha tsika nemitsara mu..." column uye tinya "OK".

Nekuda kweizvozvo, tafura yese yakazadzwa nehuwandu hwemari inogoneka ine mamiriro akasiyana ekutengesa zvinhu.

Kurumidza kuverengera mapepa ekushanda ane data matafura

Kana iwe uchida kuverenga nekukurumidza mutafura yedata isingakonzere kuverengerwa kwebhuku rese rebasa, pane zvinhu zvishoma zvaungaita kuti ukurumidze kuita.

1. Vhura hwindo resarudzo, sarudza chinhu "Formulas" mumenyu kurudyi.
2. Sarudza chinhu "Otomatiki, kunze kwematafura e data" muchikamu che "Computations mubhuku rebasa".

3. Ngativerengeizve mhinduro mutafura nemaoko. Kuti uite izvi, sarudza mafomula uye tinya F kiyi.

Zvimwe Zvishandiso Zvekuita Sensitivity Analysis

Pane mamwe maturusi ari muchirongwa anokubatsira kuita ongororo yekunzwa. Vanoita otomatiki zvimwe zviito zvaizofanira kuitwa nemaoko.

1. Iyo "Parameter sarudzo" basa rakakodzera kana mhedzisiro yaunoda ichizivikanwa, uye iwe unofanirwa kuziva kukosha kwekuisa kweiyo vhezheni kuti uwane mhedzisiro yakadaro..
2. "Tsvaga mhinduro" ndeyekuwedzera-yekugadzirisa matambudziko. Izvo zvinodiwa kumisa miganhu uye kunongedzera kwavari, mushure meizvozvo iyo system ichawana mhinduro. Mhinduro inogadziriswa nekushandura maitiro.
3. Sensitivity ongororo inogona kuitwa uchishandisa iyo Scenario Maneja. Ichi chishandiso chinowanikwa mune izvo-kana yekuongorora menyu pasi peData tab. Inotsiva kukosha mumasero akati wandei - iyo nhamba inogona kusvika 32. Iyo dispatcher inofananidza izvi zvakakosha kuitira kuti mushandisi haafanire kuzvishandura nemaoko. Muenzaniso wekushandisa script maneja:

Sensitivity ongororo yepurojekiti yekudyara muExcel

Ko-kana kuongorora kunonyanya kubatsira mumamiriro ezvinhu apo kufanotaura kunodiwa, zvakadai sekudyara. Vanoongorora vanoshandisa nzira iyi kuti vaone kuti kukosha kwemari yekambani kuchachinja sei nekuda kwekuchinja mune zvimwe zvinhu.

Investment Sensitivity Analysis Method

Paunenge uchiongorora "ko kana" shandisa kuverenga - manual kana otomatiki. Huwandu hwehunhu hunozivikanwa, uye hunotsiviwa mufomula imwe neimwe. Mhedzisiro yacho iboka rezvikoshi. Sarudza nhamba yakakodzera kubva kwavari. Ngatitarisei zviratidzo zvina izvo kuongororwa kwekunzwa kunoitwa mumunda wemari:

1. Net Present Value - Yakaverengerwa nekubvisa huwandu hwekudyara kubva pahuwandu hwemari.
2. Yemukati mwero wekudzoka / purofiti - inoratidza kuti yakawanda sei purofiti inodiwa kuti igamuchirwe kubva mukudyara mugore.
3. The payback reshiyo ndiyo reshiyo purofiti yose kune yekutanga mari.
4. Discounted profit index - inoratidza kushanda kwekudyara.

fomura

Embedding senitivity inogona kuverengerwa uchishandisa iyi formula: Shandura mukubuda paramende mu% / Shandura mune yekuisa paramende mu%.

Iyo inobuda uye yekuisa paramita inogona kuve iyo yakakosha yakatsanangurwa kare.

1. Iwe unofanirwa kuziva mhedzisiro pasi pemamiriro akajairwa.
2. Isu tinotsiva imwe yemhando uye tinotarisisa shanduko mumhedzisiro.
3. Isu tinoverenga shanduko yezana yezvikamu zviviri maererano nemamiriro akaiswa.
4. Isu tinoisa mapeji akawanikwa muchimiro uye tinoona kunzwisiswa.

Muenzaniso wekunzwa kuongororwa kweprojekti yekudyara muExcel

Kuti unzwisise zviri nani nzira yekuongorora, muenzaniso unodiwa. Ngationgororei purojekiti neiyo inotevera inozivikanwa data:

1. Zadza tafura kuti uongorore purojekiti pairi.

2. Isu tinoverenga kuyerera kwemari tichishandisa iyo OFFSET basa. Padanho rekutanga, kuyerera kwakaenzana nekudyara. Zvadaro, tinoshandisa formula: =KANA(OFFSET(Nhamba,1;)=2;SUM(Inflow 1:Kubuda 1); SUM(Inflow 1:Kubuda 1)+$B$ 5)
   Mazita emasero mune fomula anogona kunge akasiyana, zvichienderana nekurongeka kwetafura. Pakupedzisira, kukosha kubva kune data yekutanga inowedzerwa - kukosha kwekununura.

3. Isu tinosarudza nguva iyo purojekiti ichabhadhara. Kwenguva yekutanga, tinoshandisa iyi fomula: =KUPFUURA(G7: G17;» <0″). Rudzi rwesero ndiyo chikamu chekuyerera kwemari. Kune dzimwe nguva, isu tinoshandisa iyi fomula: =Nguva yekutanga+KANA(Yekutanga e.stream>0; Kutanga e.stream;0). Iyo purojekiti iri padanho rekuzorora mumakore mana.

4. Isu tinogadzira koramu yenhamba dzenguva idzodzo apo purojekiti inobhadhara.

5. Isu tinoverenga kudzoka pakudyara. Izvo zvinodikanwa kuita chirevo apo purofiti mune imwe nguva yenguva yakakamurwa nekutanga investimendi.

6. Isu tinosarudza the discount factor tichishandisa iyi formula: =1/(1+Disc.%) ^Chiverengo.

7. Isu tinoverenga kukosha kwazvino tichishandisa kuwanda - kuyerera kwemari kunowedzerwa neiyo discount factor.

8. Ngativerengei PI (profitbility index). Iko kukosha kwezvino nekufamba kwenguva kwakakamurwa nekudyara pakutanga kweprojekiti.

9. Ngatitsanangure chiyero chemukati chekudzoka tichishandisa IRR basa: =IRR(Range yekuyerera kwemari).

Investment Sensitivity Analysis Uchishandisa Datasheet

Nekuongororwa kwemapurojekiti mumunda wekudyara, dzimwe nzira dzakakodzerwa zviri nani pane tafura yedata. Vazhinji vashandisi vanosangana nekuvhiringidzika kana vachigadzira fomula. Kuti uwane kutsamira kwechimwe chinhu pane shanduko mune vamwe, unofanirwa kusarudza masero akakodzera ekupinda masvomhu uye ekuverenga data.

Factor uye dispersion analysis muExcel ine calculation automation

Imwe typology yekunzwa kwekuongorora ndeye factor analysis uye kuongororwa kwekusiyana. Rudzi rwekutanga runotsanangura hukama pakati penhamba, yechipiri inoratidza kutsamira kweimwe shanduko pane dzimwe.

ANOVA muExcel

Chinangwa chekuongorora kwakadaro ndechekuparadzanisa kusiyanisa kwekukosha muzvikamu zvitatu:

1. Kusiyana-siyana semugumisiro wehumwe hutsika.
2. Shanduko nekuda kwehukama hwehunhu hunoikanganisa.
3. Random shanduko.

Ngatiitei ongororo yekusiyanisa kuburikidza neExcel yekuwedzera-mu "Data Analysis". Kana iyo isina kugoneswa, inogona kugoneswa muzvirongwa.

Tafura yekutanga inofanira kutevera mitemo miviri: pane imwe mbiru kune imwe neimwe kukosha, uye data mairi inorongwa mukukwira kana kuburuka. Izvo zvinodikanwa kutarisa pesvedzero yedanho redzidzo pane maitiro mukukonana.

1. Tsvaga iyo Data Analysis chishandiso muData tab uye vhura hwindo rayo. Mune rondedzero, iwe unofanirwa kusarudza imwe-nzira yekuongorora yekusiyana.

2. Dzadza mitsetse yebhokisi rehurukuro. Nguva yekupinda masero ese, kusasanganisa misoro nenhamba. Boka nezvikamu. Isu tinoratidza zvabuda pabepa idzva.

Sezvo kukosha muyero sero yakakura kudarika imwe, kufungidzira kunogona kuonekwa kusina kururama - hapana hukama pakati pedzidzo uye maitiro mukukakavadzana.

Factor analysis muExcel: muenzaniso

Ngationgororei hukama hwe data mumunda wekutengesa - zvakakosha kuziva zvigadzirwa zvakakurumbira uye zvisingafadzi. Ruzivo rwekutanga:

1. Isu tinofanirwa kuona kuti ndezvipi kudiwa kwezvinhu zvakawedzera zvakanyanya mumwedzi wechipiri. Tiri kunyora tafura itsva kuti tione kukura uye kuderera kwekuda. Kukura kunoverengerwa uchishandisa iyi formula: =IF((Demand 2-Demand 1)>0; Demand 2- Demand 1;0). Deredza formula: =KANA(Kukura=0; Kudiwa 1- Kudiwa 2;0).

2. Verenga kukura kwekudiwa kwezvinhu sechikamu: =KANA(Kukura/Kubuda 2 =0; Kuderera/Chigumisiro 2; Kukura/Chigumisiro 2).

3. Ngatigadzirei chati kuti ijekese - sarudza huwandu hwemasero uye gadzira histogram kuburikidza ne "Isa" tab. Mune zvigadziriso, iwe unofanirwa kubvisa kuzadza, izvi zvinogona kuitwa kuburikidza neFormat Data Series chishandiso.

Kuongorora-nzira mbiri dzekusiyana muExcel

Kuongororwa kwekusiyana kunoitwa nemhando dzakasiyana-siyana. Funga izvi nemuenzaniso: iwe unofanirwa kuziva kuti nekukurumidza sei kuita kune ruzha rwemhando dzakasiyana kunozviratidza mune varume nevakadzi.

1. Isu tinovhura "Data Analysis", mune iyo rondedzero iwe unofanirwa kuwana nzira mbiri yekuongorora yekusiyana pasina kudzokorora.
2. Input interval - masero ane data (asina musoro). Isu tinoratidza mhinduro pane pepa idzva uye tinya "OK".

Kukosha kweF kwakakura kudarika F-yakakosha, zvinoreva kuti pasi inokanganisa kumhanya kwekuita kune ruzha.

mhedziso

Muchinyorwa chino, kuongororwa kwekunzwa muExcel spreadsheet kwakakurukurwa zvakadzama, kuitira kuti mushandisi wega wega akwanise kunzwisisa nzira dzekushandisa kwayo.