Pagsusuri ng Sensitivity sa Excel (Sample Datasheet)

Ang mga proseso sa larangan ng pananalapi ay palaging magkakaugnay - ang isang kadahilanan ay nakasalalay sa isa pa at nagbabago kasama nito. Subaybayan ang mga pagbabagong ito at unawain kung ano ang aasahan sa hinaharap, marahil gamit ang mga function ng Excel at mga pamamaraan ng spreadsheet.

Pagkuha ng maraming resulta gamit ang isang talahanayan ng data

Ang mga kakayahan ng datasheet ay mga elemento ng what-if analysis—kadalasang ginagawa sa pamamagitan ng Microsoft Excel. Ito ang pangalawang pangalan para sa pagsusuri ng sensitivity.

Pangkalahatang-ideya

Ang talahanayan ng data ay isang uri ng hanay ng mga cell na maaaring magamit upang malutas ang mga problema sa pamamagitan ng pagbabago ng mga halaga sa ilang mga cell. Ito ay nilikha kapag kinakailangan upang subaybayan ang mga pagbabago sa mga bahagi ng formula at makatanggap ng mga update sa mga resulta, ayon sa mga pagbabagong ito. Alamin natin kung paano gamitin ang mga talahanayan ng data sa pananaliksik, at kung anong mga uri ang mga ito.

Mga pangunahing kaalaman tungkol sa mga talahanayan ng data

Mayroong dalawang uri ng mga talahanayan ng data, naiiba ang mga ito sa bilang ng mga bahagi. Kailangan mong mag-compile ng isang talahanayan na may pagtuon sa bilang ng mga halaga uXNUMXbuXNUMXbna kailangan mong suriin dito.

Gumagamit ang mga statistician ng isang variable na talahanayan kapag mayroon lamang isang variable sa isa o higit pang mga expression na maaaring magbago ng kanilang resulta. Halimbawa, madalas itong ginagamit kasabay ng pag-andar ng PMT. Ang formula ay idinisenyo upang kalkulahin ang halaga ng regular na pagbabayad at isinasaalang-alang ang rate ng interes na tinukoy sa kasunduan. Sa ganitong mga kalkulasyon, ang mga variable ay nakasulat sa isang column, at ang mga resulta ng mga kalkulasyon sa isa pa. Isang halimbawa ng data plate na may 1 variable:

Susunod, isaalang-alang ang mga plato na may 2 variable. Ginagamit ang mga ito sa mga kaso kung saan naiimpluwensyahan ng dalawang salik ang pagbabago sa anumang indicator. Ang dalawang variable ay maaaring mapunta sa isa pang talahanayan na nauugnay sa loan, na maaaring magamit upang matukoy ang pinakamainam na panahon ng pagbabayad at ang halaga ng buwanang pagbabayad. Sa pagkalkula na ito, kailangan mo ring gamitin ang PMT function. Isang halimbawa ng talahanayan na may 2 variable:

Paglikha ng talahanayan ng data na may isang variable

Isaalang-alang ang paraan ng pagsusuri gamit ang halimbawa ng isang maliit na tindahan ng libro na may 100 na libro lamang ang stock. Ang ilan sa kanila ay maaaring ibenta nang mas mahal ($50), ang iba ay mas mababa ang halaga ng mga mamimili ($20). Kinakalkula ang kabuuang kita mula sa pagbebenta ng lahat ng mga kalakal – nagpasya ang may-ari na ibebenta niya ang 60% ng mga aklat sa mataas na presyo. Kailangan mong malaman kung paano tataas ang kita kung tataas mo ang presyo ng mas malaking dami ng mga kalakal – 70%, at iba pa.

Magbayad ng pansin! Ang kabuuang kita ay dapat kalkulahin gamit ang isang formula, kung hindi, hindi posible na mag-compile ng talahanayan ng data.

1. Pumili ng isang libreng cell na malayo sa gilid ng sheet at isulat ang formula dito: =Sell ng kabuuang kita. Halimbawa, kung ang kita ay nakasulat sa cell C14 (ipinahiwatig ang random na pagtatalaga), kailangan mong isulat ito: =S14.
2. Isinulat namin ang porsyento ng dami ng mga kalakal sa column sa kaliwa ng cell na ito - hindi sa ibaba nito, ito ay napakahalaga.
3. Pinipili namin ang hanay ng mga cell kung saan matatagpuan ang column ng porsyento at ang link sa kabuuang kita.

4. Nakita namin sa tab na "Data" ang item na "Paano kung pagsusuri" at mag-click dito - sa menu na bubukas, piliin ang opsyon na "Talahanayan ng data".

5. Magbubukas ang isang maliit na window kung saan kailangan mong tukuyin ang isang cell na may porsyento ng mga aklat na unang naibenta sa mataas na presyo sa column na "Palitan ang mga halaga ayon sa mga hilera sa ...". Ginagawa ang hakbang na ito upang muling kalkulahin ang kabuuang kita na isinasaalang-alang ang pagtaas ng porsyento.

Matapos i-click ang pindutang "OK" sa window kung saan ipinasok ang data para sa pag-compile ng talahanayan, ang mga resulta ng mga kalkulasyon ay lilitaw sa mga linya.

Pagdaragdag ng Formula sa Isang Talaan ng Data ng Variable

Mula sa isang talahanayan na nakatulong sa pagkalkula ng isang aksyon na may isang variable lamang, maaari kang gumawa ng isang sopistikadong tool sa pagsusuri sa pamamagitan ng pagdaragdag ng karagdagang formula. Dapat itong ilagay sa tabi ng isang umiiral nang formula – halimbawa, kung ang talahanayan ay row-oriented, ipinapasok namin ang expression sa cell sa kanan ng umiiral na isa. Kapag naitakda ang oryentasyon ng column, isinusulat namin ang bagong formula sa ilalim ng luma. Susunod, sundin ang algorithm:

1. Piliin muli ang hanay ng mga cell, ngunit ngayon ay dapat itong isama ang bagong formula.
2. Buksan ang menu ng pagsusuri na "paano kung" at piliin ang "Datasheet".
3. Nagdagdag kami ng bagong formula sa kaukulang field sa mga row o column, depende sa oryentasyon ng plate.

Gumawa ng talahanayan ng data na may dalawang variable

Ang simula ng naturang talahanayan ay bahagyang naiiba - kailangan mong maglagay ng link sa kabuuang kita sa itaas ng mga halaga ng porsyento. Susunod, ginagawa namin ang mga hakbang na ito:

1. Sumulat ng mga opsyon sa presyo sa isang linya na may link sa kita – isang cell para sa bawat presyo.
2. Pumili ng hanay ng mga cell.

3. Buksan ang window ng talahanayan ng data, tulad ng pag-compile ng isang talahanayan na may isang variable - sa pamamagitan ng tab na "Data" sa toolbar.
4. Palitan sa column na “Palitan ang mga value sa pamamagitan ng mga column sa …” isang cell na may paunang mataas na presyo.
5. Magdagdag ng cell na may paunang porsyento ng mga benta ng mga mamahaling aklat sa column na “Palitan ang mga halaga ayon sa mga hilera sa …” at i-click ang “OK”.

Bilang resulta, ang buong talahanayan ay napuno ng mga halaga ng posibleng kita na may iba't ibang mga kondisyon para sa pagbebenta ng mga kalakal.

Pabilisin ang mga kalkulasyon para sa mga worksheet na naglalaman ng mga talahanayan ng data

Kung kailangan mo ng mabilis na pagkalkula sa isang talahanayan ng data na hindi nagti-trigger ng muling pagkalkula ng buong workbook, may ilang bagay na maaari mong gawin upang mapabilis ang proseso.

1. Buksan ang window ng mga pagpipilian, piliin ang item na "Mga Formula" sa menu sa kanan.
2. Piliin ang item na "Awtomatiko, maliban sa mga talahanayan ng data" sa seksyong "Mga Pagkalkula sa workbook".

3. Manu-manong kalkulahin natin ang mga resulta sa talahanayan. Upang gawin ito, piliin ang mga formula at pindutin ang F key.

Iba pang Mga Tool para sa Pagsasagawa ng Sensitivity Analysis

Mayroong iba pang mga tool sa programa upang matulungan kang magsagawa ng pagsusuri sa pagiging sensitibo. I-automate nila ang ilang mga aksyon na kung hindi man ay kailangang gawin nang manu-mano.

1. Ang function na "Parameter selection" ay angkop kung ang nais na resulta ay kilala, at kailangan mong malaman ang input value ng variable upang makakuha ng ganoong resulta.
2. Ang "Maghanap ng solusyon" ay isang add-on para sa paglutas ng mga problema. Kinakailangan na magtakda ng mga limitasyon at ituro ang mga ito, pagkatapos ay mahahanap ng system ang sagot. Natutukoy ang solusyon sa pamamagitan ng pagbabago ng mga halaga.
3. Maaaring gawin ang pagsusuri sa sensitivity gamit ang Scenario Manager. Ang tool na ito ay matatagpuan sa what-if analysis menu sa ilalim ng tab na Data. Pinapalitan nito ang mga halaga sa ilang mga cell – ang bilang ay maaaring umabot sa 32. Inihahambing ng dispatcher ang mga halagang ito upang hindi na kailangang baguhin ng user ang mga ito nang manu-mano. Isang halimbawa ng paggamit ng script manager:

Pagsusuri ng pagiging sensitibo ng isang proyekto sa pamumuhunan sa Excel

Ang what-if analysis ay lalong kapaki-pakinabang sa mga sitwasyon kung saan kinakailangan ang pagtataya, tulad ng pamumuhunan. Ginagamit ng mga analyst ang paraang ito upang malaman kung paano magbabago ang halaga ng stock ng isang kumpanya bilang resulta ng mga pagbabago sa ilang salik.

Paraan ng Pagsusuri ng Sensitivity sa Pamumuhunan

Kapag sinusuri ang "paano kung" gumamit ng enumeration - manu-mano o awtomatiko. Ang hanay ng mga halaga ay kilala, at sila ay pinapalitan sa formula nang paisa-isa. Ang resulta ay isang hanay ng mga halaga. Piliin ang naaangkop na numero mula sa kanila. Isaalang-alang natin ang apat na tagapagpahiwatig kung saan isinasagawa ang pagsusuri sa pagiging sensitibo sa larangan ng pananalapi:

1. Net Present Value - Kinakalkula sa pamamagitan ng pagbabawas ng halaga ng puhunan mula sa halaga ng kita.
2. Panloob na rate ng kita / tubo – nagsasaad kung magkano ang tubo na kailangang matanggap mula sa isang pamumuhunan sa isang taon.
3. Ang payback ratio ay ang ratio ng lahat ng kita sa paunang pamumuhunan.
4. Discounted profit index – nagpapahiwatig ng pagiging epektibo ng pamumuhunan.

Pormula

Maaaring kalkulahin ang sensitivity ng pag-embed gamit ang formula na ito: Baguhin ang parameter ng output sa % / Pagbabago sa parameter ng input sa %.

Ang mga parameter ng output at input ay maaaring ang mga halaga na inilarawan nang mas maaga.

1. Kailangan mong malaman ang resulta sa ilalim ng karaniwang mga kondisyon.
2. Pinapalitan namin ang isa sa mga variable at sinusubaybayan ang mga pagbabago sa resulta.
3. Kinakalkula namin ang porsyento ng pagbabago ng parehong mga parameter na nauugnay sa mga itinatag na kundisyon.
4. Ipinasok namin ang nakuha na mga porsyento sa formula at tinutukoy ang pagiging sensitibo.

Isang halimbawa ng sensitivity analysis ng isang investment project sa Excel

Para sa isang mas mahusay na pag-unawa sa pamamaraan ng pagsusuri, kinakailangan ang isang halimbawa. Suriin natin ang proyekto gamit ang sumusunod na kilalang data:

1. Punan ang talahanayan upang pag-aralan ang proyekto dito.

2. Kinakalkula namin ang cash flow gamit ang OFFSET function. Sa paunang yugto, ang daloy ay katumbas ng mga pamumuhunan. Susunod, inilalapat namin ang formula: =IF(OFFSET(Number,1;)=2;SUM(Inflow 1:Outflow 1); SUM(Inflow 1:Outflow 1)+$B$ 5)
   Ang mga pagtatalaga ng cell sa formula ay maaaring iba, depende sa layout ng talahanayan. Sa dulo, ang halaga mula sa paunang data ay idinagdag - ang halaga ng pagsagip.

3. Tinutukoy namin ang panahon kung kailan magbabayad ang proyekto. Para sa unang panahon, ginagamit namin ang formula na ito: =SUMMESLY(G7: G17;»<0″). Ang hanay ng cell ay ang column ng cash flow. Para sa karagdagang mga panahon, inilalapat namin ang formula na ito: =Initial period+IF(Unang e.stream>0; Unang e.stream;0). Ang proyekto ay nasa break-even point sa loob ng 4 na taon.

4. Gumagawa kami ng column para sa mga bilang ng mga panahong iyon kung kailan nagbayad ang proyekto.

5. Kinakalkula namin ang return on investment. Kinakailangang gumawa ng pagpapahayag kung saan ang tubo sa isang partikular na tagal ng panahon ay nahahati sa paunang pamumuhunan.

6. Tinutukoy namin ang kadahilanan ng diskwento gamit ang formula na ito: =1/(1+Disc.%) ^Bilang.

7. Kinakalkula namin ang kasalukuyang halaga gamit ang multiplikasyon - ang daloy ng pera ay pinarami ng discount factor.

8. Kalkulahin natin ang PI (profitability index). Ang kasalukuyang halaga sa paglipas ng panahon ay nahahati sa pamumuhunan sa simula ng proyekto.

9. Tukuyin natin ang panloob na rate ng pagbabalik gamit ang IRR function: =IRR(Hanay ng cash flow).

Pagsusuri ng Sensitivity sa Pamumuhunan Gamit ang Datasheet

Para sa pagsusuri ng mga proyekto sa larangan ng pamumuhunan, ang ibang mga pamamaraan ay mas angkop kaysa sa talahanayan ng data. Maraming user ang nakakaranas ng pagkalito kapag nag-compile ng isang formula. Upang malaman ang pag-asa ng isang kadahilanan sa mga pagbabago sa iba, kailangan mong piliin ang tamang mga cell para sa pagpasok ng mga kalkulasyon at para sa pagbabasa ng data.

Factor at dispersion analysis sa Excel na may automation ng pagkalkula

Ang isa pang tipolohiya ng sensitivity analysis ay ang factor analysis at analysis ng variance. Ang unang uri ay tumutukoy sa relasyon sa pagitan ng mga numero, ang pangalawa ay nagpapakita ng pag-asa ng isang variable sa iba.

ANOVA sa Excel

Ang layunin ng naturang pagsusuri ay hatiin ang pagkakaiba-iba ng halaga sa tatlong bahagi:

1. Pagkakaiba-iba bilang resulta ng impluwensya ng iba pang mga halaga.
2. Mga pagbabago dahil sa kaugnayan ng mga halaga na nakakaapekto dito.
3. Random na pagbabago.

Magsagawa tayo ng pagsusuri ng variance sa pamamagitan ng Excel add-in na “Data Analysis”. Kung hindi ito pinagana, maaari itong paganahin sa mga setting.

Ang unang talahanayan ay dapat sumunod sa dalawang panuntunan: mayroong isang column para sa bawat halaga, at ang data sa loob nito ay nakaayos sa pataas o pababang pagkakasunud-sunod. Kinakailangang suriin ang impluwensya ng antas ng edukasyon sa pag-uugali sa kontrahan.

1. Hanapin ang tool sa Pagsusuri ng Data sa tab na Data at buksan ang window nito. Sa listahan, kailangan mong pumili ng one-way analysis ng variance.

2. Punan ang mga linya ng dialog box. Ang input interval ay lahat ng mga cell, hindi kasama ang mga header at numero. Igrupo ayon sa mga hanay. Ipinapakita namin ang mga resulta sa isang bagong sheet.

Dahil ang halaga sa dilaw na cell ay mas malaki kaysa sa isa, ang palagay ay maaaring ituring na hindi tama - walang kaugnayan sa pagitan ng edukasyon at pag-uugali na nagkakasalungatan.

Factor analysis sa Excel: isang halimbawa

Suriin natin ang kaugnayan ng data sa larangan ng pagbebenta – kinakailangan upang matukoy ang mga sikat at hindi sikat na produkto. Paunang impormasyon:

1. Kailangan nating malaman kung aling mga produkto ang tumaas nang higit sa ikalawang buwan. Nag-iipon kami ng bagong talahanayan upang matukoy ang paglago at pagbaba ng demand. Ang paglago ay kinakalkula gamit ang formula na ito: =IF((Demand 2-Demand 1)>0; Demand 2- Demand 1;0). Bawasan ang formula: =IF(Growth=0; Demand 1- Demand 2;0).

2. Kalkulahin ang paglaki ng demand para sa mga kalakal bilang isang porsyento: =IF(Paglago/Kinalabasan 2 =0; Pagbaba/Kinalabasan 2; Paglago/Kinalabasan 2).

3. Gumawa tayo ng tsart para sa kalinawan – pumili ng hanay ng mga cell at lumikha ng histogram sa pamamagitan ng tab na “Insert”. Sa mga setting, kailangan mong alisin ang punan, maaari itong gawin sa pamamagitan ng tool na Format Data Series.

Dalawang-daan na pagsusuri ng pagkakaiba-iba sa Excel

Ang pagsusuri ng pagkakaiba-iba ay isinasagawa gamit ang ilang mga variable. Isaalang-alang ito sa isang halimbawa: kailangan mong malaman kung gaano kabilis ang reaksyon sa isang tunog ng iba't ibang dami ay nagpapakita mismo sa mga lalaki at babae.

1. Binuksan namin ang "Pagsusuri ng Data", sa listahan na kailangan mong maghanap ng dalawang-daan na pagsusuri ng pagkakaiba-iba nang walang pag-uulit.
2. Input interval – mga cell na naglalaman ng data (walang header). Ipinapakita namin ang mga resulta sa isang bagong sheet at i-click ang "OK".

Ang halaga ng F ay mas malaki kaysa sa F-kritikal, na nangangahulugan na ang sahig ay nakakaapekto sa bilis ng reaksyon sa tunog.

Konklusyon

Sa artikulong ito, tinalakay nang detalyado ang pagsusuri ng sensitivity sa isang spreadsheet ng Excel, upang maunawaan ng bawat user ang mga pamamaraan ng aplikasyon nito.