Jutības analīze programmā Excel (datu lapas paraugs)

Procesi finanšu jomā vienmēr ir savstarpēji saistīti – viens faktors ir atkarīgs no cita un mainās līdz ar to. Izsekojiet šīm izmaiņām un izprotiet, ko sagaidīt nākotnē, iespējams, izmantojot Excel funkcijas un izklājlapu metodes.

Vairāku rezultātu iegūšana, izmantojot datu tabulu

Datu lapu iespējas ir elementi analīzei, kas notiek, ja to bieži veic, izmantojot Microsoft Excel. Šis ir jutīguma analīzes otrais nosaukums.

Pārskats

Datu tabula ir šūnu diapazona veids, ko var izmantot, lai atrisinātu problēmas, mainot vērtības dažās šūnās. Tas tiek izveidots, kad nepieciešams sekot līdzi izmaiņām formulas komponentos un saņemt rezultātu atjauninājumus atbilstoši šīm izmaiņām. Noskaidrosim, kā datu tabulas izmantot pētījumos un kādi ir tās veidi.

Pamatinformācija par datu tabulām

Ir divu veidu datu tabulas, tās atšķiras pēc komponentu skaita. Jums ir jāsastāda tabula, koncentrējoties uz vērtību skaitu uXNUMXbuXNUMXb, kas jums jāpārbauda ar to.

Statistiķi izmanto vienu mainīgo tabulu, ja vienā vai vairākās izteiksmēs ir tikai viens mainīgais, kas var mainīt to rezultātu. Piemēram, to bieži izmanto kopā ar PMT funkciju. Formula paredzēta regulārā maksājuma summas aprēķināšanai un ņem vērā līgumā noteikto procentu likmi. Šādos aprēķinos mainīgie tiek ierakstīti vienā kolonnā, bet aprēķinu rezultāti - citā. Datu plāksnes piemērs ar 1 mainīgo:

Tālāk apsveriet plāksnes ar 2 mainīgajiem. Tos izmanto gadījumos, kad jebkura rādītāja izmaiņas ietekmē divi faktori. Abi mainīgie var nonākt citā ar kredītu saistītā tabulā, pēc kuras var noteikt optimālo atmaksas termiņu un ikmēneša maksājuma apmēru. Šajā aprēķinā ir jāizmanto arī PMT funkcija. Tabulas piemērs ar 2 mainīgajiem:

Datu tabulas izveide ar vienu mainīgo

Apsveriet analīzes metodi, izmantojot piemēru ar nelielu grāmatnīcu, kurā ir tikai 100 grāmatas. Dažus no tiem var pārdot dārgāk (50 USD), pārējās pircējiem maksās mazāk (20 USD). Tiek aprēķināti kopējie ienākumi no visu preču pārdošanas – īpašnieks nolēma, ka 60% grāmatu pārdos par augstu cenu. Jānoskaidro, kā pieaugs ieņēmumi, ja palielināsiet cenu lielākam preču apjomam – par 70% un tā tālāk.

Pievērs uzmanību! Kopējie ieņēmumi jāaprēķina pēc formulas, pretējā gadījumā nevarēs sastādīt datu tabulu.

1. Atlasiet brīvu šūnu prom no lapas malas un ierakstiet tajā formulu: =Kopējo ieņēmumu šūna. Piemēram, ja ienākumi ir ierakstīti šūnā C14 (ir norādīts nejaušs apzīmējums), jums jāraksta: =S14.
2. Mēs rakstām procentus no preču apjoma kolonnā pa kreisi no šīs šūnas – ne zem tās, tas ir ļoti svarīgi.
3. Mēs atlasām šūnu diapazonu, kurā atrodas procentu kolonna un saite uz kopējiem ienākumiem.

4. Cilnē “Dati” atrodam vienumu “Ko darīt, ja analīze” un noklikšķiniet uz tā – atvērtajā izvēlnē atlasiet opciju “Datu tabula”.

5. Tiks atvērts neliels logs, kurā kolonnā “Aizvietot vērtības pēc rindām …” jānorāda šūna ar sākotnēji par augstu cenu pārdoto grāmatu procentuālo daļu. Šis solis tiek veikts, lai pārrēķinātu kopējos ieņēmumus, ņemot vērā pieaugošo procentuālo daļu.

Pēc noklikšķināšanas uz pogas “OK” logā, kurā tika ievadīti dati tabulas sastādīšanai, rindās parādīsies aprēķinu rezultāti.

Formulas pievienošana viena mainīgā datu tabulai

No tabulas, kas palīdzēja aprēķināt darbību tikai ar vienu mainīgo, varat izveidot sarežģītu analīzes rīku, pievienojot papildu formulu. Tas jāievada blakus jau esošai formulai – piemēram, ja tabula ir orientēta uz rindu, izteiksmi ievadām šūnā pa labi no esošās. Kad kolonnas orientācija ir iestatīta, mēs ierakstām jauno formulu zem vecās. Tālāk izpildiet algoritmu:

1. Vēlreiz atlasiet šūnu diapazonu, bet tagad tajā jāiekļauj jaunā formula.
2. Atveriet analīzes izvēlni “Ko darīt, ja” un atlasiet “Datu lapa”.
3. Atbilstošajam laukam rindās vai kolonnās pievienojam jaunu formulu atkarībā no plāksnes orientācijas.

Izveidojiet datu tabulu ar diviem mainīgajiem

Šādas tabulas sākums ir nedaudz atšķirīgs – virs procentu vērtībām jāievieto saite uz kopējiem ieņēmumiem. Tālāk mēs veicam šādas darbības:

1. Cenu variantus ierakstiet vienā rindā ar saiti uz ienākumiem – katrai cenai viena šūna.
2. Atlasiet šūnu diapazonu.

3. Atveriet datu tabulas logu, tāpat kā sastādot tabulu ar vienu mainīgo – caur rīkjoslas cilni “Dati”.
4. Slejā “Aizstāt vērtības ar kolonnām …” aizstājiet šūnu ar sākotnējo augsto cenu.
5. Pievienojiet šūnu ar sākotnējo dārgo grāmatu pārdošanas procentuālo daļu kolonnā “Aizstāt vērtības pēc rindām …” un noklikšķiniet uz “OK”.

Rezultātā visa tabula ir aizpildīta ar iespējamo ienākumu summām ar dažādiem preču pārdošanas nosacījumiem.

Paātriniet aprēķinus darblapām, kurās ir datu tabulas

Ja jums ir nepieciešami ātri aprēķini datu tabulā, kas neizraisa visas darbgrāmatas pārrēķinu, ir dažas lietas, ko varat darīt, lai paātrinātu procesu.

1. Atveriet opciju logu, labajā pusē esošajā izvēlnē atlasiet vienumu “Formulas”.
2. Sadaļā “Aprēķini darbgrāmatā” atlasiet vienumu “Automātiski, izņemot datu tabulas”.

3. Rezultātus tabulā pārrēķināsim manuāli. Lai to izdarītu, atlasiet formulas un nospiediet taustiņu F.

Citi rīki jutīguma analīzes veikšanai

Programmā ir arī citi rīki, kas palīdz veikt jutīguma analīzi. Tie automatizē dažas darbības, kas citādi būtu jāveic manuāli.

1. Funkcija “Parametru izvēle” ir piemērota, ja ir zināms vēlamais rezultāts un jums jāzina mainīgā lieluma ievades vērtība, lai iegūtu šādu rezultātu..
2. “Meklējiet risinājumu” ir papildinājums problēmu risināšanai. Ir nepieciešams noteikt ierobežojumus un norādīt uz tiem, pēc kuriem sistēma atradīs atbildi. Risinājumu nosaka, mainot vērtības.
3. Jutīguma analīzi var veikt, izmantojot scenāriju pārvaldnieku. Šis rīks ir atrodams cilnes Dati izvēlnē Ko darīt, ja analīze. Tas aizvieto vērtības vairākās šūnās – to skaits var sasniegt 32. Dispečers salīdzina šīs vērtības, lai lietotājam tās nebūtu jāmaina manuāli. Skriptu pārvaldnieka izmantošanas piemērs:

Investīciju projekta jutīguma analīze programmā Excel

Ko darīt, ja analīze ir īpaši noderīga situācijās, kad ir nepieciešama prognozēšana, piemēram, investīcijas. Analītiķi izmanto šo metodi, lai noskaidrotu, kā mainīsies uzņēmuma akciju vērtība dažu faktoru izmaiņu rezultātā.

Investīciju jutīguma analīzes metode

Analizējot “kā būtu, ja būtu”, izmantojiet uzskaiti – manuāli vai automātiski. Vērtību diapazons ir zināms, un tie tiek aizstāti formulā pa vienam. Rezultāts ir vērtību kopums. Izvēlieties no tiem atbilstošo numuru. Apskatīsim četrus rādītājus, kuriem finanšu jomā tiek veikta jutīguma analīze:

1. Pašreizējā neto vērtība – tiek aprēķināta, no ienākumu summas atņemot ieguldījumu summu.
2. Iekšējā atdeves/peļņas norma – norāda, cik liela peļņa ir jāsaņem no ieguldījuma gadā.
3. Atmaksas koeficients ir visas peļņas attiecība pret sākotnējo ieguldījumu.
4. Diskontētās peļņas indekss – norāda uz ieguldījuma efektivitāti.

Formula

Iegulšanas jutību var aprēķināt, izmantojot šo formulu: Izejas parametra izmaiņas % / Ievades parametra izmaiņas %.

Izejas un ievades parametri var būt iepriekš aprakstītās vērtības.

1. Jums jāzina rezultāts standarta apstākļos.
2. Mēs nomainām vienu no mainīgajiem un uzraugām rezultāta izmaiņas.
3. Mēs aprēķinām abu parametru procentuālās izmaiņas attiecībā pret noteiktajiem apstākļiem.
4. Iegūtos procentus ievietojam formulā un nosaka jutību.

Investīciju projekta jutīguma analīzes piemērs programmā Excel

Lai labāk izprastu analīzes metodoloģiju, ir nepieciešams piemērs. Analizēsim projektu ar šādiem zināmiem datiem:

1. Aizpildiet tabulu, lai analizētu tajā esošo projektu.

2. Mēs aprēķinām naudas plūsmu, izmantojot funkciju OFFSET. Sākotnējā posmā plūsma ir vienāda ar investīcijām. Tālāk mēs izmantojam formulu: =IF(OFFSET(Numurs,1;)=2;SUM(Ieplūde 1:Izplūde 1); SUM(Ieplūde 1:Izplūde 1)+$B$ 5)
   Šūnu apzīmējumi formulā var atšķirties atkarībā no tabulas izkārtojuma. Beigās tiek pievienota vērtība no sākotnējiem datiem – glābšanas vērtība.

3. Mēs nosakām periodu, kurā projekts atmaksāsies. Sākotnējā periodā mēs izmantojam šo formulu: =SUMMESLY(G7: G17;»<0″). Šūnu diapazons ir naudas plūsmas kolonna. Turpmākajiem periodiem mēs izmantojam šo formulu: =Sākotnējais periods+IF(Pirmā e.straume>0; Pirmā e.straume;0). Projekts ir rentabilitātes punktā pēc 4 gadiem.

4. Mēs izveidojam kolonnu to periodu skaitļiem, kad projekts atmaksājas.

5. Mēs aprēķinām ieguldījumu atdevi. Nepieciešams veikt izteiksmi, kur peļņa noteiktā laika periodā tiek dalīta ar sākotnējo ieguldījumu.

6. Mēs nosakām atlaides koeficientu, izmantojot šo formulu: =1/(1+disks.%) ^Numurs.

7. Pašreizējo vērtību aprēķinām, izmantojot reizināšanu – naudas plūsmu reizina ar diskonta koeficientu.

8. Aprēķināsim PI (rentabilitātes indeksu). Pašreizējā vērtība laika gaitā tiek dalīta ar ieguldījumu projekta sākumā.

9. Definēsim iekšējo atdeves likmi, izmantojot funkciju IRR: = IRR (naudas plūsmas diapazons).

Investīciju jutīguma analīze, izmantojot datu lapu

Projektu analīzei investīciju jomā citas metodes ir labāk piemērotas nekā datu tabula. Daudzi lietotāji, sastādot formulu, saskaras ar neskaidrībām. Lai noskaidrotu viena faktora atkarību no izmaiņām citos, ir jāizvēlas pareizās šūnas aprēķinu ievadīšanai un datu nolasīšanai.

Faktoru un dispersijas analīze programmā Excel ar aprēķinu automatizāciju

Vēl viena jutīguma analīzes tipoloģija ir faktoru analīze un dispersijas analīze. Pirmais veids nosaka attiecības starp skaitļiem, otrais atklāj viena mainīgā atkarību no citiem.

ANOVA programmā Excel

Šādas analīzes mērķis ir sadalīt vērtības mainīgumu trīs komponentos:

1. Mainīgums citu vērtību ietekmes rezultātā.
2. Izmaiņas to ietekmējošo vērtību attiecību dēļ.
3. Nejaušas izmaiņas.

Veiksim dispersijas analīzi, izmantojot Excel pievienojumprogrammu “Datu analīze”. Ja tas nav iespējots, to var iespējot iestatījumos.

Sākotnējā tabulā ir jāievēro divi noteikumi: katrai vērtībai ir viena kolonna, un tajā esošie dati ir sakārtoti augošā vai dilstošā secībā. Nepieciešams pārbaudīt izglītības līmeņa ietekmi uz uzvedību konfliktā.

1. Cilnē Dati atrodiet datu analīzes rīku un atveriet tā logu. Sarakstā ir jāizvēlas vienvirziena dispersijas analīze.

2. Aizpildiet dialoglodziņa rindiņas. Ievades intervāls ir visas šūnas, izņemot galvenes un ciparus. Grupēšana pēc kolonnām. Mēs parādām rezultātus jaunā lapā.

Tā kā vērtība dzeltenajā šūnā ir lielāka par vienu, pieņēmumu var uzskatīt par nepareizu – nav attiecības starp izglītību un uzvedību konfliktā.

Faktoru analīze programmā Excel: piemērs

Analizēsim datu attiecības pārdošanas jomā – nepieciešams identificēt populārus un nepopulārus produktus. Sākotnējā informācija:

1. Jānoskaidro, pēc kurām precēm otrajā mēnesī pieprasījums pieauga visvairāk. Mēs veidojam jaunu tabulu, lai noteiktu pieprasījuma pieaugumu un kritumu. Izaugsmi aprēķina pēc šādas formulas: =IF((pieprasījums 2-pieprasījums 1)>0; pieprasījums 2- pieprasījums 1;0). Samazināšanas formula: =IF(Izaugsme=0; Pieprasījums 1- Pieprasījums 2;0).

2. Aprēķiniet preču pieprasījuma pieaugumu procentos: =IF(Izaugsme/2. rezultāts =0; Samazinājums/2. rezultāts; Izaugsme/2. rezultāts).

3. Skaidrības labad izveidosim diagrammu – atlasiet šūnu diapazonu un izveidojiet histogrammu, izmantojot cilni “Ievietot”. Iestatījumos jums ir jānoņem aizpildījums, to var izdarīt, izmantojot rīku Format Data Series.

Divvirzienu dispersijas analīze programmā Excel

Dispersijas analīze tiek veikta ar vairākiem mainīgajiem. Apsveriet to ar piemēru: jums ir jānoskaidro, cik ātri reakcija uz dažāda skaļuma skaņu izpaužas vīriešiem un sievietēm.

1. Mēs atveram “Datu analīzi”, sarakstā jāatrod divvirzienu dispersijas analīze bez atkārtojumiem.
2. Ievades intervāls – šūnas, kas satur datus (bez galvenes). Mēs parādām rezultātus jaunā lapā un noklikšķiniet uz “OK”.

F vērtība ir lielāka par F-kritisko vērtību, kas nozīmē, ka grīda ietekmē reakcijas uz skaņu ātrumu.

Secinājumi

Šajā rakstā tika detalizēti apskatīta jutīguma analīze Excel izklājlapā, lai katrs lietotājs varētu saprast tās pielietošanas metodes.