Analiza osjetljivosti u Excelu (primjer podatkovne tablice)

Procesi u području financija uvijek su međusobno povezani – jedan čimbenik ovisi o drugom i mijenja se s njim. Pratite ove promjene i shvatite što možete očekivati ​​u budućnosti, možda pomoću Excel funkcija i metoda proračunskih tablica.

Dobivanje više rezultata s podatkovnom tablicom

Mogućnosti podatkovne tablice elementi su analize što-ako—često se radi putem programa Microsoft Excel. Ovo je drugi naziv za analizu osjetljivosti.

Pregled

Podatkovna tablica je vrsta raspona ćelija koje se mogu koristiti za rješavanje problema mijenjanjem vrijednosti u nekim ćelijama. Nastaje kada je potrebno pratiti promjene u komponentama formule i primati ažuriranja rezultata, u skladu s tim promjenama. Otkrijmo kako koristiti podatkovne tablice u istraživanju i koje su vrste.

Osnove podatkovnih tablica

Postoje dvije vrste tablica podataka, razlikuju se po broju komponenti. Morate sastaviti tablicu s fokusom na broj vrijednosti uXNUMXbuXNUMXbkoje trebate provjeriti s njom.

Statističari koriste tablicu jedne varijable kada postoji samo jedna varijabla u jednom ili više izraza koja može promijeniti njihov rezultat. Na primjer, često se koristi u kombinaciji s PMT funkcijom. Formula je dizajnirana za izračun iznosa redovnog plaćanja i uzima u obzir kamatnu stopu navedenu u ugovoru. U takvim izračunima varijable se zapisuju u jedan stupac, a rezultati izračuna u drugi. Primjer pločice s podacima s 1 varijablom:

Zatim, razmotrite ploče s 2 varijable. Koriste se u slučajevima kada dva čimbenika utječu na promjenu bilo kojeg pokazatelja. Dvije varijable mogu završiti u drugoj tablici povezanoj s kreditom, koja se može koristiti za određivanje optimalnog roka otplate i iznosa mjesečne otplate. U ovom izračunu također trebate koristiti funkciju PMT. Primjer tablice s 2 varijable:

Izrada podatkovne tablice s jednom varijablom

Razmotrimo metodu analize na primjeru male knjižare sa samo 100 knjiga na zalihama. Neki od njih mogu se prodati skuplje ($50), ostali će koštati kupce manje ($20). Izračunava se ukupan prihod od prodaje sve robe – vlasnik je odlučio da će 60% knjiga prodati po visokoj cijeni. Morate saznati kako će se prihod povećati ako povećate cijenu veće količine robe – 70% i tako dalje.

Obratiti pažnju! Ukupni prihod mora se izračunati pomoću formule, inače neće biti moguće sastaviti podatkovnu tablicu.

1. Odaberite slobodnu ćeliju udaljenu od ruba lista i u nju upišite formulu: = Ćelija ukupnog prihoda. Na primjer, ako je prihod napisan u ćeliji C14 (navedena je slučajna oznaka), trebate napisati ovo: =S14.
2. Zapisujemo postotak volumena robe u stupac lijevo od ove ćelije - ne ispod nje, ovo je vrlo važno.
3. Odaberemo raspon ćelija gdje se nalazi stupac postotaka i poveznica na ukupni prihod.

4. Na kartici "Podaci" nalazimo stavku "Što ako analiza" i kliknemo na nju - u izborniku koji se otvori odaberite opciju "Tablica podataka".

5. Otvorit će se mali prozor u kojem trebate navesti ćeliju s postotkom knjiga koje su inicijalno prodane po visokoj cijeni u stupcu "Zamjena vrijednosti po redovima u ...". Ovaj korak se radi kako bi se preračunao ukupni prihod uzimajući u obzir rastući postotak.

Nakon klika na gumb "OK" u prozoru u koji su uneseni podaci za sastavljanje tablice, u redovima će se pojaviti rezultati izračuna.

Dodavanje formule jednoj tablici podataka varijable

Od tablice koja je pomogla izračunati radnju sa samo jednom varijablom, možete napraviti sofisticirani alat za analizu dodavanjem dodatne formule. Mora se unijeti uz već postojeću formulu – npr. ako je tablica orijentirana prema retku, izraz upisujemo u ćeliju desno od postojeće. Kada je postavljena orijentacija stupca, novu formulu upisujemo ispod stare. Zatim slijedite algoritam:

1. Ponovno odaberite raspon ćelija, ali sada bi trebao uključivati ​​novu formulu.
2. Otvorite izbornik analize "što ako" i odaberite "Podatkovna tablica".
3. Novu formulu dodajemo u odgovarajuće polje u redovima ili stupcima, ovisno o orijentaciji ploče.

Napravite podatkovnu tablicu s dvije varijable

Početak takve tablice malo je drugačiji – iznad postotnih vrijednosti trebate staviti poveznicu na ukupni prihod. Zatim izvodimo ove korake:

1. Napišite opcije cijena u jedan redak s vezom na prihod – jedna ćelija za svaku cijenu.
2. Odaberite raspon ćelija.

3. Otvorite prozor tablice podataka, kao kod sastavljanja tablice s jednom varijablom - kroz karticu "Podaci" na alatnoj traci.
4. Zamijenite u stupcu "Zamijenite vrijednosti stupcima u ..." ćeliju s početnom visokom cijenom.
5. Dodajte ćeliju s početnim postotkom prodaje skupih knjiga u stupac "Zamjena vrijednosti po recima u ..." i kliknite "U redu".

Kao rezultat toga, cijela tablica je ispunjena iznosima mogućih prihoda s različitim uvjetima prodaje robe.

Ubrzajte izračune za radne listove koji sadrže podatkovne tablice

Ako trebate brze izračune u podatkovnoj tablici koji ne pokreću ponovni izračun cijele radne knjige, postoji nekoliko stvari koje možete učiniti kako biste ubrzali proces.

1. Otvorite prozor s opcijama, odaberite stavku "Formule" u izborniku s desne strane.
2. Odaberite stavku "Automatski, osim za podatkovne tablice" u odjeljku "Izračuni u radnoj knjizi".

3. Preračunajmo ručno rezultate u tablici. Da biste to učinili, odaberite formule i pritisnite tipku F.

Ostali alati za izvođenje analize osjetljivosti

Postoje i drugi alati u programu koji vam pomažu u provođenju analize osjetljivosti. Oni automatiziraju neke radnje koje bi se inače morale obaviti ručno.

1. Funkcija "Odabir parametra" prikladna je ako je željeni rezultat poznat, a morate znati ulaznu vrijednost varijable da biste dobili takav rezultat.
2. “Traži rješenje” je dodatak za rješavanje problema. Potrebno je postaviti granice i ukazati na njih, nakon čega će sustav pronaći odgovor. Rješenje se određuje promjenom vrijednosti.
3. Analiza osjetljivosti može se napraviti pomoću Scenario Managera. Ovaj se alat nalazi u izborniku analize što ako pod karticom Podaci. Zamjenjuje vrijednosti u nekoliko ćelija - broj može doseći 32. Dispečer uspoređuje te vrijednosti tako da ih korisnik ne mora mijenjati ručno. Primjer korištenja upravitelja skriptama:

Analiza osjetljivosti investicijskog projekta u Excelu

Analiza što ako je posebno korisna u situacijama kada je potrebno predviđanje, kao što je ulaganje. Analitičari koriste ovu metodu kako bi saznali kako će se promijeniti vrijednost dionica poduzeća kao rezultat promjena nekih čimbenika.

Metoda analize osjetljivosti ulaganja

Kada analizirate "što ako" koristite nabrajanje - ručno ili automatsko. Raspon vrijednosti je poznat i one se zamjenjuju u formulu jednu po jednu. Rezultat je skup vrijednosti. Odaberite odgovarajući broj među njima. Razmotrimo četiri pokazatelja za koje se provodi analiza osjetljivosti u području financija:

1. Neto sadašnja vrijednost – izračunava se oduzimanjem iznosa ulaganja od iznosa prihoda.
2. Interna stopa povrata / profita – pokazuje koliko je profita potrebno dobiti od ulaganja u jednoj godini.
3. Koeficijent povrata je omjer sve dobiti i početnog ulaganja.
4. Indeks diskontirane dobiti – pokazuje učinkovitost ulaganja.

Formula

Osjetljivost ugrađivanja može se izračunati pomoću ove formule: Promjena izlaznog parametra u % / Promjena ulaznog parametra u %.

Izlazni i ulazni parametri mogu biti ranije opisane vrijednosti.

1. Morate znati rezultat u standardnim uvjetima.
2. Zamijenimo jednu od varijabli i pratimo promjene rezultata.
3. Izračunavamo postotnu promjenu oba parametra u odnosu na utvrđene uvjete.
4. Dobivene postotke ubacujemo u formulu i određujemo osjetljivost.

Primjer analize osjetljivosti investicijskog projekta u Excelu

Za bolje razumijevanje metodologije analize potreban je primjer. Analizirajmo projekt sa sljedećim poznatim podacima:

1. Ispunite tablicu kako biste na njoj analizirali projekt.

2. Novčani tok izračunavamo pomoću funkcije OFFSET. U početnoj fazi protok je jednak ulaganjima. Zatim primjenjujemo formulu: =IF(OFFSET(Broj,1;)=2;SUM(Ulaz 1:Izlaz 1); SUM(Ulaz 1:Izlaz 1)+$B$ 5)
   Oznake ćelija u formuli mogu biti različite, ovisno o izgledu tablice. Na kraju se dodaje vrijednost iz početnih podataka – salvage value.

3. Određujemo razdoblje za koje će se projekt isplatiti. Za početno razdoblje koristimo ovu formulu: =SUMMESLY(G7: G17;»<0″). Raspon ćelija je stupac novčanog toka. Za daljnja razdoblja primjenjujemo ovu formulu: =Početno razdoblje+IF(Prvi e.stream>0; Prvi e.stream;0). Projekt je na pragu rentabilnosti za 4 godine.

4. Kreiramo stupac za brojeve onih razdoblja kada se projekt isplati.

5. Izračunavamo povrat investicije. Potrebno je napraviti izraz gdje se dobit u određenom vremenskom razdoblju dijeli s početnim ulaganjem.

6. Faktor popusta određujemo pomoću ove formule: =1/(1+Disk.%) ^Broj.

7. Sadašnju vrijednost izračunavamo množenjem – novčani tijek se množi s diskontnim faktorom.

8. Izračunajmo PI (indeks profitabilnosti). Sadašnja vrijednost tijekom vremena dijeli se s ulaganjem na početku projekta.

9. Definirajmo internu stopu povrata pomoću IRR funkcije: =IRR(raspon novčanog toka).

Analiza osjetljivosti ulaganja pomoću podatkovne tablice

Za analizu projekata u području ulaganja druge su metode prikladnije od podatkovne tablice. Mnogi korisnici imaju zabunu pri sastavljanju formule. Da biste saznali ovisnost jednog faktora o promjenama u drugima, morate odabrati ispravne ćelije za unos izračuna i za čitanje podataka.

Faktorska i disperzijska analiza u Excelu s automatizacijom izračuna

Druga tipologija analize osjetljivosti je faktorska analiza i analiza varijance. Prva vrsta definira odnos između brojeva, druga otkriva ovisnost jedne varijable o drugima.

ANOVA u Excelu

Svrha takve analize je podijeliti varijabilnost vrijednosti na tri komponente:

1. Varijabilnost kao rezultat utjecaja drugih vrijednosti.
2. Promjene zbog odnosa vrijednosti koje na njega utječu.
3. Slučajne promjene.

Izvršimo analizu varijance kroz Excel dodatak “Analiza podataka”. Ako nije omogućeno, može se omogućiti u postavkama.

Početna tablica mora slijediti dva pravila: za svaku vrijednost postoji jedan stupac, a podaci u njemu raspoređeni su uzlaznim ili silaznim redoslijedom. Potrebno je provjeriti utjecaj stupnja obrazovanja na ponašanje u sukobu.

1. Pronađite alat za analizu podataka na kartici Podaci i otvorite njegov prozor. U popisu je potrebno odabrati jednosmjernu analizu varijance.

2. Ispunite retke dijaloškog okvira. Interval unosa su sve ćelije, isključujući zaglavlja i brojeve. Grupirajte po stupcima. Rezultate prikazujemo na novom listu.

Budući da je vrijednost u žutoj ćeliji veća od jedan, pretpostavka se može smatrati netočnom – ne postoji odnos između obrazovanja i ponašanja u sukobu.

Faktorska analiza u Excelu: primjer

Analizirajmo odnos podataka u području prodaje – potrebno je identificirati popularne i nepopularne proizvode. Početne informacije:

1. Moramo saznati za kojom je robom potražnja najviše porasla tijekom drugog mjeseca. Sastavljamo novu tablicu za utvrđivanje rasta i pada potražnje. Rast se izračunava pomoću ove formule: =IF((Zahtjev 2-Zahtjev 1)>0; Zahtjev 2- Zahtjev 1;0). Formula smanjenja: =IF(Rast=0; Potražnja 1- Potražnja 2;0).

2. Izračunajte rast potražnje za robom kao postotak: =IF(Rast/Ishod 2 =0; Smanjenje/Ishod 2; Rast/Ishod 2).

3. Napravimo grafikon radi jasnoće - odaberite raspon ćelija i izradite histogram putem kartice "Umetni". U postavkama morate ukloniti ispunu, to se može učiniti pomoću alata Format Data Series.

Dvosmjerna analiza varijance u Excelu

Analiza varijance provodi se s nekoliko varijabli. Razmotrite ovo na primjeru: morate saznati koliko se brzo reakcija na zvuk različite glasnoće manifestira kod muškaraca i žena.

1. Otvaramo "Analizu podataka", na popisu morate pronaći dvosmjernu analizu varijance bez ponavljanja.
2. Interval unosa – ćelije koje sadrže podatke (bez zaglavlja). Prikazujemo rezultate na novom listu i kliknemo "U redu".

F vrijednost je veća od F-kritične, što znači da pod utječe na brzinu reakcije na zvuk.

Zaključak

U ovom članku detaljno je obrađena analiza osjetljivosti u Excel tablici, tako da će svaki korisnik moći razumjeti metode njezine primjene.