Sensitivity Analysis mu Excel (Sample Datasheet)

Njira mu gawo lazachuma nthawi zonse zimalumikizana - chinthu chimodzi chimadalira china ndikusintha nacho. Tsatani zosinthazi ndikumvetsetsa zomwe mungayembekezere m'tsogolo, mwina pogwiritsa ntchito ntchito za Excel ndi njira zamasamba.

Kupeza zotsatira zingapo ndi tebulo la data

Kuthekera kwa datasheet ndi zinthu zomwe-ngati kusanthula-nthawi zambiri kumachitika kudzera mu Microsoft Excel. Ili ndi dzina lachiwiri la sensitivity analysis.

mwachidule

Deta ya data ndi mtundu wamagulu osiyanasiyana omwe angagwiritsidwe ntchito kuthetsa mavuto posintha ma cell ena. Zimapangidwa pamene kuli kofunikira kuyang'anira kusintha kwa zigawo za chilinganizo ndi kulandira zosintha ku zotsatira, malinga ndi kusintha kumeneku. Tiyeni tiwone momwe tingagwiritsire ntchito matebulo a data pofufuza, ndi mitundu yanji.

Zofunikira pamatebulo a data

Pali mitundu iwiri ya matebulo a data, amasiyana ndi chiwerengero cha zigawo. Muyenera kupanga tebulo ndikuyang'ana kuchuluka kwa zinthu zamtengo wapatali uXNUMXbuXNUMXbzomwe muyenera kuyang'ana nazo.

Owerengera amagwiritsa ntchito tebulo losinthika limodzi pakakhala kusintha kumodzi kapena zingapo zomwe zingasinthe zotsatira zake. Mwachitsanzo, nthawi zambiri amagwiritsidwa ntchito limodzi ndi ntchito ya PMT. Ndondomekoyi idapangidwa kuti iwerengere kuchuluka kwa malipiro anthawi zonse ndikuganiziranso chiwongola dzanja chomwe chafotokozedwa mu mgwirizano. M’mawerengedwe oterowo, zosinthazo zimalembedwa m’danga limodzi, ndipo zotsatira za kuŵerengerazo zimalembedwa m’gawo lina. Chitsanzo cha mbale ya data yokhala ndi 1 variable:

Kenaka, ganizirani mbale zomwe zili ndi 2 zosinthika. Amagwiritsidwa ntchito pamene zinthu ziwiri zimakhudza kusintha kwa chizindikiro chilichonse. Zosintha ziwirizi zitha kukhala patebulo lina logwirizana ndi ngongoleyo, yomwe ingagwiritsidwe ntchito kudziwa nthawi yoyenera yobweza komanso kuchuluka kwa malipiro a mwezi uliwonse. Pakuwerengera uku, muyeneranso kugwiritsa ntchito ntchito ya PMT. Chitsanzo cha tebulo lokhala ndi 2 zosinthika:

Kupanga tebulo la data ndi kusintha kumodzi

Ganizirani njira yosanthula pogwiritsa ntchito chitsanzo cha kasitolo kakang'ono ka mabuku komwe kamakhala ndi mabuku 100 okha. Zina mwa izo zitha kugulitsidwa zodula ($50), zotsalazo zimawononga ogula ndalama zochepa ($20). Ndalama zonse zogulitsa katundu zonse zimawerengedwa - mwiniwakeyo adaganiza kuti agulitse 60% ya mabuku pamtengo wapamwamba. Muyenera kudziwa momwe ndalama zidzakulirakulira ngati muwonjezera mtengo wa katundu wokulirapo - 70%, ndi zina zotero.

Tcherani khutu! Ndalama zonse ziyenera kuwerengedwa pogwiritsa ntchito ndondomeko, apo ayi sizingatheke kupanga tebulo la deta.

1. Sankhani selo laulere kutali ndi m'mphepete mwa pepala ndikulemba fomula mmenemo: = Selo la ndalama zonse. Mwachitsanzo, ngati ndalama zalembedwa mu selo C14 (kutchulidwa mwachisawawa), muyenera kulemba izi: =S14.
2. Timalemba kuchuluka kwa kuchuluka kwa katundu kumanzere kwa selo ili - osati pansi pake, izi ndizofunikira kwambiri.
3. Timasankha kuchuluka kwa maselo komwe gawo la magawo ndi ulalo wa ndalama zonse zilipo.

4. Timapeza pa tabu "Data" chinthu "Bwanji ngati kusanthula" ndikudina pa izo - mu menyu yomwe imatsegulidwa, sankhani kusankha "Data table".

5. Zenera laling'ono lidzatsegulidwa pomwe muyenera kutchula selo yokhala ndi kuchuluka kwa mabuku omwe adagulitsidwa pamtengo wokwera mugawo la "M'malo mwa mizere mu ...". Izi zimachitika pofuna kuwerengeranso ndalama zonse poganizira kuchuluka kwa ndalama zomwe zikuwonjezeka.

Pambuyo kuwonekera batani "Chabwino" pa zenera kumene deta analowa kulemba tebulo, zotsatira za mawerengedwe adzaoneka mizere.

Kuwonjeza Fomula ku Table ya Data Yosinthika Imodzi

Kuchokera patebulo lomwe lathandizira kuwerengera zochita ndi kusintha kumodzi kokha, mutha kupanga chida chowunikira chapamwamba powonjezera fomula yowonjezera. Iyenera kulowetsedwa pafupi ndi ndondomeko yomwe ilipo kale - mwachitsanzo, ngati tebulo liri pamzere, timalowetsa mawu mu selo kumanja kwa yomwe ilipo. Pamene ndondomeko ya ndime yakhazikitsidwa, timalemba ndondomeko yatsopano pansi pa yakale. Kenako, tsatirani algorithm:

1. Sankhaninso kuchuluka kwa ma cell, koma tsopano iyenera kuphatikiza fomula yatsopano.
2. Tsegulani menyu yowunikira "bwanji ngati" ndikusankha "Datasheet".
3. Timawonjezera fomula yatsopano kumunda wofananira m'mizere kapena mizati, kutengera mawonekedwe a mbale.

Pangani tebulo la data ndi mitundu iwiri

Chiyambi cha tebulo loterolo ndi chosiyana pang'ono - muyenera kuyika chiyanjano ku ndalama zonse pamwamba pa chiwerengero cha chiwerengero. Kenako, timachita izi:

1. Lembani zosankha zamitengo mumzere umodzi ndi ulalo wopeza ndalama - selo imodzi pamtengo uliwonse.
2. Sankhani magulu osiyanasiyana.

3. Tsegulani zenera la tebulo la deta, monga polemba tebulo ndi kusintha kumodzi - kudzera pa "Data" tabu pazitsulo.
4. Lowetsani m'gawo la "Sinthani m'malo mwa magawo mu ..." cell yokhala ndi mtengo wokwera woyamba.
5. Onjezani foni yokhala ndi gawo loyambirira lazogulitsa mabuku okwera mtengo kugawo la "M'malo mwa mizere mu ..." ndikudina "Chabwino".

Zotsatira zake, tebulo lonse limadzazidwa ndi kuchuluka kwa ndalama zomwe zingatheke ndi zinthu zosiyanasiyana zogulitsa katundu.

Limbikitsani kuwerengera kwamasamba omwe ali ndi matebulo a data

Ngati mukufuna kuwerengera mwachangu patebulo la data lomwe silimayambitsa kuwerengeranso buku lonse, pali zinthu zingapo zomwe mungachite kuti mufulumizitse ntchitoyi.

1. Tsegulani zenera la zosankha, sankhani chinthucho "Mafomula" pamenyu kumanja.
2. Sankhani chinthucho "Automatic, kupatula matebulo a data" mu gawo la "Computations in the workbook".

3. Tiyeni tiwerengenso zotsatira patebulo pamanja. Kuti muchite izi, sankhani mafomuwo ndikudina batani F.

Zida Zina Zopangira Kuwunika Kwachidziwitso

Palinso zida zina mu pulogalamuyi kukuthandizani kusanthula sensitivity. Amapanga zinthu zina zomwe zikanayenera kuchitidwa pamanja.

1. Ntchito ya "Parameter Selection" ndiyoyenera ngati zotsatira zomwe mukufuna zimadziwika, ndipo muyenera kudziwa kuchuluka kwa zosinthazo kuti mupeze zotsatira..
2. "Fufuzani yankho" ndi njira yowonjezera yothetsera mavuto. Ndikofunikira kukhazikitsa malire ndikuwalozera, pambuyo pake dongosolo lidzapeza yankho. Njira yothetsera vutoli imatsimikiziridwa ndi kusintha makhalidwe.
3. Kusanthula kwachangu kutha kuchitidwa pogwiritsa ntchito Scenario Manager. Chida ichi chimapezeka mumenyu yowunika-ngati-ngati pansi pa tabu ya Data. Imalowetsa zikhalidwe m'maselo angapo - chiwerengerocho chikhoza kufika 32. Wotumizayo amafanizira mfundozi kuti wogwiritsa ntchito asasinthe pamanja. Chitsanzo chogwiritsa ntchito script manager:

Sensitivity kusanthula kwa polojekiti yoyika ndalama mu Excel

Kodi kusanthula ngati kuli kothandiza makamaka pakafunika kulosera, monga ndalama. Ofufuza amagwiritsa ntchito njirayi kuti adziwe momwe mtengo wa katundu wa kampani udzasinthira chifukwa cha kusintha kwa zinthu zina.

Investment Sensitivity Analysis Njira

Mukasanthula "bwanji ngati" gwiritsani ntchito kuwerengera - pamanja kapena zokha. Mitundu yosiyanasiyana imadziwika, ndipo imalowetsedwa m'malo amodzi ndi imodzi. Zotsatira zake ndi mndandanda wazinthu. Sankhani nambala yoyenera kwa iwo. Tiyeni tiwone zizindikiro zinayi zomwe kusanthula kwa chidwi kukuchitika pazachuma:

1. Net Present Value - Kuwerengeredwa pochotsa kuchuluka kwa ndalama zomwe mwapeza.
2. Mlingo wamkati wobwerera / phindu - zikuwonetsa kuchuluka kwa phindu lomwe likufunika kulandilidwa kuchokera kubizinesi mchaka chimodzi.
3. Chiŵerengero cha malipiro ndi chiŵerengero cha phindu lonse kwa ndalama zoyamba.
4. Mlozera wa phindu lochotsera - umasonyeza mphamvu ya ndalamazo.

chilinganizo

Kuphatikizika kwa mphamvu kumatha kuwerengedwa pogwiritsa ntchito njira iyi: Sinthani zotuluka mu % / Sinthani pazolowera mu %.

Zotulutsa ndi zolowetsa zimatha kukhala zomwe zafotokozedwa kale.

1. Muyenera kudziwa zotsatira zake pamikhalidwe yokhazikika.
2. Timalowetsa chimodzi mwazosinthika ndikuwunika kusintha kwa zotsatira.
3. Timawerengera kuchuluka kwa kusintha kwa magawo onse awiri mogwirizana ndi zomwe zakhazikitsidwa.
4. Timayika maperesenti omwe tapeza mu fomula ndikuzindikira kukhudzika.

Chitsanzo cha kusanthula kwa chidwi cha polojekiti yogulitsa ndalama mu Excel

Kuti mumvetsetse bwino njira yowunikirayi, pakufunika chitsanzo. Tiyeni tiwunike pulojekitiyi ndi deta yodziwika:

1. Lembani tebulo kuti muwunike ntchitoyo.

2. Timawerengera kuchuluka kwa ndalama pogwiritsa ntchito ntchito ya OFFSET. Pa gawo loyambirira, kuyenda kumakhala kofanana ndi ndalama. Pambuyo pake, timagwiritsa ntchito formula: =IF(OFFSET(Nambala,1;)=2;CHUMUM(Inflow 1: Outflow 1); SUM(Inflow 1: Outflow 1)+$B$ 5)
   Maonekedwe a ma cell mu chilinganizo angakhale osiyana, kutengera masanjidwe a tebulo. Pamapeto pake, mtengo wochokera ku deta yoyambirira ukuwonjezeredwa - mtengo wa salvage.

3. Timasankha nthawi yomwe polojekitiyi idzaperekedwe. Kwa nthawi yoyamba, timagwiritsa ntchito njira iyi: =CHIWIRI (G7 :g17; <0″). Mtundu wa ma cell ndi gawo la ndalama. Kwa nthawi zina, timagwiritsa ntchito fomula iyi: =Nthawi yoyambirira+IF(Yoyamba e.stream>0; Yoyamba e.stream;0). Ntchitoyi ili pachimake pakadutsa zaka 4.

4. Timapanga ndime ya manambala a nthawi zomwe polojekiti ikulipira.

5. Timawerengera kubweza kwa ndalama. Ndikofunikira kupanga mawu pomwe phindu mu nthawi inayake limagawidwa ndi ndalama zoyambira.

6. Timazindikira kuchotsera pogwiritsa ntchito fomula iyi: =1/(1+Disko.%) ^Nambala.

7. Timawerengera mtengo womwe ulipo pogwiritsira ntchito kuchulukitsa - ndalama zoyendetsera ndalama zimachulukitsidwa ndi kuchotsera.

8. Tiyeni tiwerengere PI (profitbility index). Mtengo womwe ulipo pakapita nthawi umagawidwa ndi ndalama zomwe zimayambira poyambira.

9. Tiyeni tifotokozere kuchuluka kwa kubweza kwamkati pogwiritsa ntchito ntchito ya IRR: =IRR(Kuchuluka kwa ndalama).

Investment Sensitivity Analysis Pogwiritsa Ntchito Datasheet

Pakuwunika ma projekiti pazachuma, njira zina ndizoyenera kuposa tebulo la data. Ogwiritsa ntchito ambiri amasokonezeka akamalemba fomula. Kuti mudziwe kudalira kwa chinthu chimodzi pakusintha kwa ena, muyenera kusankha ma cell olondola owerengera komanso kuwerenga zambiri.

Kusanthula kwa Factor ndi dispersion mu Excel ndi mawerengedwe a automation

Mtundu wina wa sensitivity kusanthula ndi kusanthula zinthu ndi kusanthula kusiyana. Mtundu woyamba umatanthawuza mgwirizano pakati pa manambala, wachiwiri umasonyeza kudalira kwa kusintha kumodzi pa ena.

ANOVA mu Excel

Cholinga cha kusanthula koteroko ndikugawa kusinthasintha kwa mtengowo m'zigawo zitatu:

1. Kusinthasintha chifukwa cha chikoka cha zikhalidwe zina.
2. Zosintha chifukwa cha ubale wamakhalidwe omwe amakhudza.
3. Kusintha kwachisawawa.

Tiyeni tiwunike kusiyanasiyana kudzera muzowonjezera za Excel "Data Analysis". Ngati sichiyatsidwa, imatha kuyimitsa pazokonda.

Gome loyambirira liyenera kutsatira malamulo awiri: pali gawo limodzi la mtengo uliwonse, ndipo deta yomwe ili mmenemo imakonzedwa mokwera kapena kutsika. M'pofunika kufufuza chikoka cha mlingo wa maphunziro pa khalidwe mkangano.

1. Pezani chida cha Data Analysis mu tabu ya Data ndikutsegula zenera lake. Pamndandanda, muyenera kusankha njira imodzi yowunikira kusiyana.

2. Lembani mizere ya bokosi la zokambirana. Nthawi yolowera ndi ma cell onse, kuphatikiza mitu ndi manambala. Gulu ndi mizati. Timawonetsa zotsatira papepala latsopano.

Popeza kuti mtengo mu selo lachikasu ndi lalikulu kuposa limodzi, lingalirolo likhoza kuonedwa kuti ndilolakwika - palibe mgwirizano pakati pa maphunziro ndi khalidwe losagwirizana.

Kusanthula kwa Factor mu Excel: chitsanzo

Tiyeni tiwunikenso ubale wa data pamalonda ogulitsa - ndikofunikira kuzindikira zinthu zodziwika bwino komanso zosavomerezeka. Zoyamba:

1. Tiyenera kudziwa kuti ndi zinthu ziti zomwe zimafunidwa kwambiri m'mwezi wachiwiri. Tikulemba tebulo latsopano kuti tidziwe kukula ndi kuchepa kwa kufunikira. Kukula kumawerengedwa pogwiritsa ntchito njira iyi: =IF((Demand 2-Demand 1)>0; Demand 2- Demand 1;0). Chepetsani chilinganizo: =IF(Kukula=0; Kufuna 1- Kufuna 2;0).

2. Werezerani kukula kwa kufunikira kwa katundu monga peresenti: =IF(Kukula/Chotsatira 2 =0; Kuchepa/Chotsatira 2; Kukula/Chotsatira 2).

3. Tiyeni tipange tchati kuti imveke bwino - sankhani magulu angapo ndikupanga histogram kudzera pa "Ikani" tabu. Muzokonda, muyenera kuchotsa kudzaza, izi zitha kuchitika kudzera mu chida cha Format Data Series.

Kusanthula kwamitundu iwiri ku Excel

Kusanthula kwa kusiyanasiyana kumachitika ndi mitundu ingapo. Ganizirani izi ndi chitsanzo: muyenera kudziwa momwe kumvera kwa mawu amitundu yosiyanasiyana kumawonekera mwa amuna ndi akazi.

1. Timatsegula "Data Analysis", pamndandanda muyenera kupeza njira ziwiri zowunikira kusiyana popanda kubwerezabwereza.
2. Nthawi yolowetsa - maselo omwe ali ndi deta (popanda mutu). Timawonetsa zotsatira papepala latsopano ndikudina "Chabwino".

Mtengo wa F ndi waukulu kuposa F-critical, zomwe zikutanthauza kuti pansi kumakhudza liwiro la kumveka kwa mawu.

Kutsiliza

M'nkhaniyi, kusanthula kwa chidwi mu Excel spreadsheet kunakambidwa mwatsatanetsatane, kuti wogwiritsa ntchito aliyense athe kumvetsetsa njira zomwe amagwiritsira ntchito.