Analiza občutljivosti v Excelu (vzorec podatkovnega lista)

Procesi na področju financ so vedno med seboj povezani – en dejavnik je odvisen od drugega in se z njim spreminja. Spremljajte te spremembe in razumejte, kaj lahko pričakujete v prihodnosti, morda z uporabo Excelovih funkcij in metod preglednic.

Pridobivanje več rezultatov s podatkovno tabelo

Zmogljivosti podatkovnega lista so elementi analize, kaj če, ki se pogosto izvaja prek programa Microsoft Excel. To je drugo ime za analizo občutljivosti.

Pregled

Podatkovna tabela je vrsta obsega celic, ki jih je mogoče uporabiti za reševanje težav s spreminjanjem vrednosti v nekaterih celicah. Ustvari se, ko je treba spremljati spremembe v komponentah formule in prejemati posodobitve rezultatov glede na te spremembe. Ugotovimo, kako uporabljati podatkovne tabele v raziskavah in katere vrste so.

Osnove podatkovnih tabel

Obstajata dve vrsti podatkovnih tabel, ki se razlikujeta po številu komponent. Sestaviti morate tabelo s poudarkom na številu vrednosti uXNUMXbuXNUMXb, ki jih morate preveriti z njo.

Statistiki uporabljajo tabelo z eno spremenljivko, kadar je v enem ali več izrazih samo ena spremenljivka, ki lahko spremeni njihov rezultat. Na primer, pogosto se uporablja v povezavi s funkcijo PMT. Formula je zasnovana za izračun zneska rednega plačila in upošteva obrestno mero, določeno v pogodbi. Pri takšnih izračunih so spremenljivke zapisane v enem stolpcu, rezultati izračunov pa v drugem. Primer tablice s podatki z 1 spremenljivko:

Nato razmislite o ploščah z 2 spremenljivkama. Uporabljajo se v primerih, ko dva dejavnika vplivata na spremembo katerega koli kazalnika. Obe spremenljivki se lahko znajdeta v drugi tabeli, povezani s posojilom, na podlagi katere lahko določite optimalno dobo odplačevanja in višino mesečnega plačila. Pri tem izračunu morate uporabiti tudi funkcijo PMT. Primer tabele z 2 spremenljivkama:

Ustvarjanje podatkovne tabele z eno spremenljivko

Razmislite o metodi analize na primeru majhne knjigarne s samo 100 knjigami na zalogi. Nekatere od njih lahko prodate dražje (50 $), ostale bodo kupce stale manj (20 $). Izračunan je skupni prihodek od prodaje vsega blaga – lastnik se je odločil, da bo 60 % knjig prodal po visoki ceni. Ugotoviti morate, kako se bodo prihodki povečali, če povečate ceno večje količine blaga - 70% in tako naprej.

Bodite pozorni! Skupni prihodek mora biti izračunan po formuli, sicer podatkovne tabele ne bo mogoče sestaviti.

1. Izberite prosto celico stran od roba lista in vanjo zapišite formulo: =Celica skupnega prihodka. Na primer, če je dohodek zapisan v celici C14 (navedena je naključna oznaka), morate napisati tole: =S14.
2. Odstotek količine blaga zapišemo v stolpec levo od te celice – ne pod njo, to je zelo pomembno.
3. Izberemo obseg celic, kjer se nahaja stolpec z odstotki in povezava do skupnega dohodka.

4. Na zavihku »Podatki« najdemo postavko »Kaj če analiza« in kliknemo nanjo – v meniju, ki se odpre, izberemo možnost »Podatkovna tabela«.

5. Odpre se majhno okno, kjer morate v stolpcu »Nadomestne vrednosti po vrsticah v ...« določiti celico z odstotkom knjig, ki so bile prvotno prodane po visoki ceni. Ta korak se izvede za ponovni izračun celotnega prihodka ob upoštevanju naraščajočega odstotka.

Po kliku na gumb »V redu« v oknu, kjer so bili vneseni podatki za sestavo tabele, se v vrsticah prikažejo rezultati izračunov.

Dodajanje formule v podatkovno tabelo ene same spremenljivke

Iz tabele, ki je pomagala izračunati dejanje s samo eno spremenljivko, lahko naredite prefinjeno orodje za analizo z dodajanjem dodatne formule. Vnesti ga je treba poleg že obstoječe formule – če je tabela na primer vrstično, vnesemo izraz v celico desno od obstoječega. Ko je orientacija stolpca nastavljena, novo formulo zapišemo pod staro. Nato sledite algoritmu:

1. Ponovno izberite obseg celic, vendar bi moral zdaj vključevati novo formulo.
2. Odprite meni za analizo »kaj če« in izberite »Podatkovni list«.
3. V pripadajoče polje v vrsticah ali stolpcih dodamo novo formulo, odvisno od orientacije plošče.

Ustvarite podatkovno tabelo z dvema spremenljivkama

Začetek takšne tabele je nekoliko drugačen – nad odstotnimi vrednostmi morate postaviti povezavo do skupnega prihodka. Nato izvedemo naslednje korake:

1. Napišite cenovne možnosti v eno vrstico s povezavo do dohodka – ena celica za vsako ceno.
2. Izberite obseg celic.

3. Odprite okno podatkovne tabele, kot pri sestavljanju tabele z eno spremenljivko – prek zavihka »Podatki« v orodni vrstici.
4. Zamenjajte v stolpcu »Nadomesti vrednosti po stolpcih v ...« celico z začetno visoko ceno.
5. V stolpec »Nadomestne vrednosti po vrsticah v ...« dodajte celico z začetnim odstotkom prodaje dragih knjig in kliknite »V redu«.

Posledično je celotna tabela napolnjena z zneski možnega dohodka z različnimi pogoji za prodajo blaga.

Pospešite izračune za delovne liste, ki vsebujejo podatkovne tabele

Če potrebujete hitre izračune v podatkovni tabeli, ki ne sprožijo ponovnega izračuna celotnega delovnega zvezka, lahko naredite nekaj stvari, da pospešite postopek.

1. Odprite okno z možnostmi, v meniju na desni izberite postavko »Formule«.
2. V razdelku »Izračuni v delovnem zvezku« izberite element »Samodejno, razen podatkovnih tabel«.

3. Ročno preračunajmo rezultate v tabeli. Če želite to narediti, izberite formule in pritisnite tipko F.

Druga orodja za izvajanje analize občutljivosti

V programu so še druga orodja, ki vam pomagajo pri izvedbi analize občutljivosti. Avtomatizirajo nekatera dejanja, ki bi jih sicer morali opraviti ročno.

1. Funkcija "Izbira parametrov" je primerna, če je želeni rezultat znan in morate poznati vhodno vrednost spremenljivke, da dobite tak rezultat.
2. “Išči rešitev” je dodatek za reševanje problemov. Treba je postaviti meje in pokazati nanje, potem pa bo sistem našel odgovor. Rešitev se določi s spreminjanjem vrednosti.
3. Analizo občutljivosti lahko izvedete z upraviteljem scenarijev. To orodje najdete v meniju za analizo kaj-če pod zavihkom Podatki. Zamenja vrednosti v več celicah - število lahko doseže 32. Dispečer primerja te vrednosti, tako da jih uporabniku ni treba spreminjati ročno. Primer uporabe upravitelja skriptov:

Analiza občutljivosti investicijskega projekta v Excelu

Analiza kaj če je še posebej uporabna v situacijah, kjer je potrebno napovedovanje, kot je na primer naložba. Analitiki uporabljajo to metodo, da ugotovijo, kako se bo vrednost delnice podjetja spremenila zaradi sprememb nekaterih dejavnikov.

Metoda analize občutljivosti naložb

Pri analizi "kaj če" uporabite naštevanje - ročno ali samodejno. Razpon vrednosti je znan in v formulo se nadomestijo ena za drugo. Rezultat je nabor vrednosti. Med njimi izberite ustrezno številko. Poglejmo štiri kazalnike, za katere se izvaja analiza občutljivosti na področju financ:

1. Neto sedanja vrednost – izračuna se tako, da se znesek naložbe odšteje od zneska dohodka.
2. Notranja stopnja donosa/dobiček – označuje, koliko dobička je treba prejeti od naložbe v enem letu.
3. Stopnja povračila je razmerje med vsemi dobički in začetno naložbo.
4. Indeks diskontiranega dobička – kaže učinkovitost naložbe.

Formula

Občutljivost vdelave je mogoče izračunati s to formulo: Sprememba izhodnega parametra v % / Sprememba vhodnega parametra v %.

Izhodni in vhodni parametri so lahko prej opisane vrednosti.

1. Morate poznati rezultat v standardnih pogojih.
2. Zamenjamo eno od spremenljivk in spremljamo spremembe rezultata.
3. Izračunamo odstotek spremembe obeh parametrov glede na vzpostavljene pogoje.
4. Dobljene odstotke vstavimo v formulo in določimo občutljivost.

Primer analize občutljivosti investicijskega projekta v Excelu

Za boljše razumevanje metodologije analize je potreben primer. Analizirajmo projekt z naslednjimi znanimi podatki:

1. Izpolnite tabelo, da analizirate projekt na njej.

2. Denarni tok izračunamo s funkcijo OFFSET. V začetni fazi je tok enak naložbam. Nato uporabimo formulo: =IF(OFFSET(število,1;)=2;SUM(pritok 1:odtok 1); SUM(pritok 1:odtok 1)+$B$ 5)
   Oznake celic v formuli so lahko različne, odvisno od postavitve tabele. Na koncu se prišteje vrednost iz začetnih podatkov – preostala vrednost.

3. Določimo obdobje, za katerega se bo projekt izplačal. Za začetno obdobje uporabljamo to formulo: =SUMMESLY(G7: G17;»<0″). Obseg celic je stolpec denarnega toka. Za nadaljnja obdobja uporabljamo to formulo: =Začetno obdobje+IF(Prvi e.tok>0; Prvi e.tok;0). Projekt je na točki preloma v 4 letih.

4. Ustvarimo stolpec za številke tistih obdobij, ko se projekt izplača.

5. Izračunamo donosnost naložbe. Treba je narediti izraz, kjer se dobiček v določenem časovnem obdobju deli z začetnim vložkom.

6. Faktor popusta določimo s to formulo: =1/(1+disk.%) ^Številka.

7. Sedanjo vrednost izračunamo z množenjem – denarni tok pomnožimo z diskontnim faktorjem.

8. Izračunajmo PI (indeks dobičkonosnosti). Sedanja vrednost v času se deli z naložbo na začetku projekta.

9. Določimo interno stopnjo donosa s funkcijo IRR: =IRR(razpon denarnega toka).

Analiza občutljivosti naložb z uporabo podatkovnega lista

Za analizo projektov na področju investicij so primernejše druge metode kot podatkovna tabela. Mnogi uporabniki se pri sestavljanju formule soočajo z zmedo. Če želite ugotoviti odvisnost enega faktorja od sprememb drugih, morate izbrati pravilne celice za vnos izračunov in branje podatkov.

Faktorska in disperzijska analiza v Excelu z avtomatizacijo izračuna

Druga tipologija analize občutljivosti je faktorska analiza in analiza variance. Prva vrsta določa razmerje med številkami, druga razkriva odvisnost ene spremenljivke od drugih.

ANOVA v Excelu

Namen takšne analize je razdeliti variabilnost vrednosti na tri komponente:

1. Spremenljivost kot posledica vpliva drugih vrednosti.
2. Spremembe zaradi razmerja vrednosti, ki vplivajo na to.
3. Naključne spremembe.

Izvedimo analizo variance preko Excelovega dodatka “Analiza podatkov”. Če ni omogočeno, ga lahko omogočite v nastavitvah.

Začetna tabela mora upoštevati dve pravili: za vsako vrednost je en stolpec, podatki v njej pa so razvrščeni v naraščajočem ali padajočem vrstnem redu. Preveriti je treba vpliv stopnje izobrazbe na vedenje v konfliktu.

1. Na zavihku Podatki poiščite orodje za analizo podatkov in odprite njegovo okno. Na seznamu morate izbrati enosmerno analizo variance.

2. Izpolnite vrstice pogovornega okna. Vnosni interval so vse celice, razen glav in številk. Združi po stolpcih. Rezultate prikažemo na novem listu.

Ker je vrednost v rumeni celici večja od ena, se domneva lahko šteje za napačno – med izobraževanjem in vedenjem v konfliktu ni povezave.

Faktorska analiza v Excelu: primer

Analizirajmo razmerje podatkov na področju prodaje – treba je prepoznati priljubljene in nepriljubljene izdelke. Začetne informacije:

1. Ugotoviti moramo, po katerem blagu se je povpraševanje najbolj povečalo v drugem mesecu. Sestavljamo novo tabelo za ugotavljanje rasti in upada povpraševanja. Rast se izračuna po tej formuli: =IF((Zahteva 2-Zahteva 1)>0; Zahteva 2- Zahteva 1;0). Formula zmanjšanja: =ČE(Rast=0; Povpraševanje 1- Povpraševanje 2;0).

2. Izračunajte rast povpraševanja po blagu v odstotkih: =ČE(Rast/Rezultat 2 =0; Zmanjšanje/Rezultat 2; Rast/Rezultat 2).

3. Zaradi jasnosti naredimo grafikon – izberite obseg celic in ustvarite histogram prek zavihka »Vstavi«. V nastavitvah morate odstraniti polnilo, to lahko storite z orodjem Format Data Series.

Dvosmerna analiza variance v Excelu

Analiza variance se izvaja z več spremenljivkami. Razmislite o tem na primeru: ugotoviti morate, kako hitro se reakcija na zvok različne glasnosti manifestira pri moških in ženskah.

1. Odpremo "Analizo podatkov", na seznamu morate najti dvosmerno analizo variance brez ponovitev.
2. Vnosni interval – celice, ki vsebujejo podatke (brez glave). Rezultate prikažemo na novem listu in kliknemo »V redu«.

Vrednost F je večja od F-kritične, kar pomeni, da tla vplivajo na hitrost reakcije na zvok.

zaključek

V tem članku je bila podrobno obravnavana analiza občutljivosti v preglednici Excel, tako da bo vsak uporabnik lahko razumel metode njene uporabe.