Analiza osjetljivosti u Excelu (uzorak podatkovnog lista)

Procesi u oblasti finansija su uvek međusobno povezani – jedan faktor zavisi od drugog i menja se sa njim. Pratite ove promjene i shvatite što možete očekivati ​​u budućnosti, možda koristeći Excel funkcije i metode proračunskih tablica.

Dobivanje više rezultata s tablicom podataka

Mogućnosti lista sa podacima su elementi šta-ako analize—često se obavljaju putem Microsoft Excel-a. Ovo je drugi naziv za analizu osjetljivosti.

pregled

Tablica podataka je tip raspona ćelija koji se može koristiti za rješavanje problema promjenom vrijednosti u nekim ćelijama. Kreira se kada je potrebno pratiti promjene u komponentama formule i primati ažuriranja rezultata, prema tim promjenama. Hajde da saznamo kako koristiti tabele podataka u istraživanju i koje su vrste.

Osnove o tablicama podataka

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

Statističari koriste jednu tabelu 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 da izračuna iznos redovne isplate i uzima u obzir kamatnu stopu koja je navedena u ugovoru. U takvim proračunima varijable se upisuju u jednu kolonu, a rezultati proračuna u drugu. Primjer pločice s podacima s 1 varijablom:

Zatim razmotrite ploče sa 2 varijable. Koriste se u slučajevima kada dva faktora utiču na promjenu bilo kojeg indikatora. Dvije varijable mogu završiti u drugoj tabeli povezanoj sa kreditom, koja se može koristiti za određivanje optimalnog roka otplate i iznosa mjesečne uplate. U ovom proračunu također morate koristiti funkciju PMT. Primjer tabele sa 2 varijable:

Kreiranje tabele podataka sa jednom promenljivom

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

Obratite pažnju! Ukupni prihod se mora izračunati pomoću formule, inače neće biti moguće sastaviti tabelu podataka.

1. Odaberite slobodnu ćeliju dalje od ruba lista i u nju upišite formulu: =Celija ukupnog prihoda. Na primjer, ako je prihod upisan u ćeliju C14 (naznačena je nasumična oznaka), trebate napisati ovo: =S14.
2. Procenat količine robe upisujemo u kolonu lijevo od ove ćelije – ne ispod nje, ovo je vrlo važno.
3. Odabiremo raspon ćelija u kojima se nalazi stupac postotaka i veza do ukupnog prihoda.

4. Na kartici „Podaci“ nalazimo stavku „Šta ako analiza“ i kliknemo na nju – u meniju koji se otvori izaberite opciju „Tabela podataka“.

5. Otvorit će se mali prozor u kojem trebate navesti ćeliju s postotkom knjiga koje su prvobitno prodate po visokoj cijeni u koloni „Zamijeni vrijednosti po redovima u…”. Ovaj korak se radi kako bi se preračunao ukupni prihod uzimajući u obzir povećanje procenta.

Nakon klika na dugme “OK” u prozoru u koji su uneti podaci za sastavljanje tabele, rezultati proračuna će se pojaviti u redovima.

Dodavanje formule u tablicu podataka jedne varijable

Iz tabele koja je pomogla izračunati radnju sa samo jednom varijablom, možete napraviti sofisticirani alat za analizu dodavanjem dodatne formule. Mora se uneti pored već postojeće formule – na primer, ako je tabela orijentisana na red, unosimo izraz 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 meni za analizu „šta ako“ i izaberite „Tabelu sa podacima“.
3. Dodamo novu formulu u odgovarajuće polje u redovima ili stupcima, ovisno o orijentaciji ploče.

Kreirajte tablicu podataka s dvije varijable

Početak takve tabele je malo drugačiji – potrebno je da stavite link do ukupnog prihoda iznad procentualnih vrednosti. Zatim izvodimo ove korake:

1. Napišite opcije cijena u jednom redu sa vezom do prihoda – jedna ćelija za svaku cijenu.
2. Odaberite raspon ćelija.

3. Otvorite prozor tabele podataka, kao kada sastavljate tabelu sa jednom promenljivom – preko kartice „Podaci“ na traci sa alatkama.
4. Zamijenite u koloni "Zamijeni vrijednosti kolonama u..." ćeliju s početnom visokom cijenom.
5. Dodajte ćeliju s početnim postotkom prodaje skupih knjiga u kolonu "Zamijenite vrijednosti po redovima u..." i kliknite na "OK".

Kao rezultat, cela tabela je ispunjena iznosima mogućeg prihoda sa različitim uslovima prodaje robe.

Ubrzajte proračune za radne listove koji sadrže tabele sa podacima

Ako su vam potrebni brzi proračuni u tabeli podataka koji ne pokreću ponovno izračunavanje cijele radne knjige, postoji nekoliko stvari koje možete učiniti da ubrzate proces.

1. Otvorite prozor sa opcijama, odaberite stavku "Formule" u meniju s desne strane.
2. Odaberite stavku "Automatski, osim tablica podataka" u odjeljku "Proračuni u radnoj knjizi".

3. Hajde da ručno ponovo izračunamo rezultate u tabeli. Da biste to učinili, odaberite formule i pritisnite tipku F.

Drugi alati za izvođenje analize osjetljivosti

Postoje i drugi alati u programu koji će vam pomoći da izvršite analizu osjetljivosti. Oni automatizuju neke radnje koje bi inače morale da se rade ručno.

1. Funkcija “Odabir parametara” je prikladna 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 sistem pronaći odgovor. Rješenje se određuje promjenom vrijednosti.
3. Analiza osjetljivosti može se obaviti pomoću Scenario Managera. Ovaj alat se nalazi u meniju za analizu šta ako na kartici Podaci. Zamjenjuje vrijednosti u nekoliko ćelija – broj može doseći 32. Dispečer upoređuje ove vrijednosti tako da ih korisnik ne mora ručno mijenjati. Primjer korištenja upravitelja skripti:

Analiza osjetljivosti investicionog projekta u Excelu

Analiza šta ako je posebno korisna u situacijama kada je potrebno predviđanje, kao što su ulaganja. Analitičari koriste ovu metodu kako bi otkrili kako će se promijeniti vrijednost dionica kompanije kao rezultat promjena u nekim faktorima.

Metoda analize osjetljivosti ulaganja

Kada analizirate „šta ako“ koristite nabrajanje – ručno ili automatsko. Opseg vrijednosti je poznat i one se zamjenjuju u formulu jedna po jedna. Rezultat je skup vrijednosti. Od njih izaberite odgovarajući broj. Razmotrimo četiri indikatora za koje se radi analiza osjetljivosti u oblasti finansija:

1. Neto sadašnja vrijednost – Izračunava se oduzimanjem iznosa investicije od iznosa prihoda.
2. Interna stopa povrata/profita – pokazuje koliko je profita potrebno dobiti od investicije u godini.
3. Koeficijent povrata je omjer svih dobiti i početnog ulaganja.
4. Indeks diskontiranog profita – ukazuje na efektivnost investicije.

formula

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

Izlazni i ulazni parametri mogu biti vrijednosti opisane ranije.

1. Morate znati rezultat pod standardnim uslovima.
2. Zamijenjujemo jednu od varijabli i pratimo promjene u rezultatu.
3. Izračunavamo procentualnu promjenu oba parametra u odnosu na uspostavljene uslove.
4. Dobijene procente unosimo u formulu i određujemo osjetljivost.

Primjer analize osjetljivosti investicionog projekta u Excelu

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

1. Popunite tabelu da analizirate projekat na njoj.

2. Izračunavamo novčani tok koristeći funkciju OFFSET. U početnoj fazi, tok je jednak investicijama. Zatim primjenjujemo formulu: =IF(OFFSET(Broj,1;)=2;SUM(Priliv 1:Odliv 1);SUM(Priliv 1:Odliv 1)+$B$ 5)
   Oznake ćelija u formuli mogu biti različite, ovisno o izgledu tablice. Na kraju se dodaje vrijednost iz početnih podataka – spasonosna vrijednost.

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

4. Kreiramo kolonu za brojeve onih perioda kada se projekat isplati.

5. Izračunavamo povrat ulaganja. Potrebno je napraviti izraz gdje se dobit u određenom vremenskom periodu dijeli sa početnim ulaganjem.

6. Određujemo faktor popusta koristeći ovu formulu: =1/(1+Disk.%) ^Broj.

7. Sadašnju vrijednost izračunavamo korištenjem množenja – novčani tok se množi diskontnim faktorom.

8. Izračunajmo PI (indeks profitabilnosti). Sadašnja vrijednost tokom vremena podijeljena je s investicijom na početku projekta.

9. Definirajmo internu stopu povrata koristeći IRR funkciju: =IRR(Raspon novčanog toka).

Analiza osjetljivosti ulaganja pomoću lista podataka

Za analizu projekata u oblasti investicija, druge metode su prikladnije od tabele sa podacima. Mnogi korisnici doživljavaju konfuziju prilikom sastavljanja formule. Da biste saznali ovisnost jednog faktora od promjena u drugim, potrebno je odabrati ispravne ćelije za unos proračuna i očitavanje podataka.

Analiza faktora i disperzije u Excelu sa automatizacijom proračuna

Druga tipologija analize osjetljivosti je faktorska analiza i analiza varijanse. Prvi tip definira odnos između brojeva, drugi otkriva ovisnost jedne varijable od drugih.

ANOVA u Excelu

Svrha takve analize je podijeliti varijabilnost vrijednosti na tri komponente:

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

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

Početna tabela mora slijediti dva pravila: postoji jedna kolona za svaku vrijednost, a podaci u njoj su raspoređeni u rastućem ili opadajućem redoslijedu. Potrebno je provjeriti uticaj nivoa obrazovanja na ponašanje u konfliktu.

1. Pronađite alat za analizu podataka na kartici Podaci i otvorite njegov prozor. Na listi morate odabrati jednosmjernu analizu varijanse.

2. Popunite linije dijaloškog okvira. Interval unosa su sve ćelije, osim zaglavlja i brojeva. Grupirajte po kolonama. Rezultate prikazujemo na novom listu.

Budući da je vrijednost u žutoj ćeliji veća od jedan, pretpostavka se može smatrati netačnom – nema veze između obrazovanja i ponašanja u konfliktu.

Faktorska analiza u Excelu: primjer

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

1. Moramo otkriti za kojom robom je potražnja najviše porasla tokom drugog mjeseca. Sastavljamo novu tabelu 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 kreirajte histogram preko kartice „Insert“. U postavkama morate ukloniti ispunu, to se može učiniti pomoću alata Format Data Series.

Dvosmjerna analiza varijanse u Excelu

Analiza varijanse se provodi sa nekoliko varijabli. Razmotrite ovo na primjeru: morate saznati koliko brzo se reakcija na zvuk različite jačine manifestira kod muškaraca i žena.

1. Otvaramo „Analizu podataka“, na listi trebate pronaći dvosmjernu analizu varijanse bez ponavljanja.
2. Interval unosa – ćelije koje sadrže podatke (bez zaglavlja). Rezultate prikazujemo na novom listu i kliknemo “OK”.

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 je detaljno razmotrena analiza osjetljivosti u Excel tabeli, tako da će svaki korisnik moći razumjeti metode njene primjene.