Dinamički grafikoni u financijskom modeliranju

Kada stvarate grafikone u financijskim modelima ili izvješćima, ipak biste trebali slijediti najbolju praksu i pokušati svoje modele učiniti što fleksibilnijim i dinamičnijim. Uvijek biste trebali povezati što je više moguće u svojim modelima, a to vrijedi i za grafikone. Ima smisla da kada promijenite jedan od ulaza u svoj model, to se mora odraziti na podatke grafikona, kao i na naslove i oznake.

Izgradnja grafikona na podacima vođenim formulom

Preuzmite datoteku 0901.xlsx . Otvorite ga i odaberite karticu s oznakom 9-23 da biste je sami isprobali.

Promjena padajućeg okvira.

Ako sakrijete podatke u izvornom listu, oni se neće prikazati na grafikonu. Testirajte to tako da sakrijete jedan od stupaca na tablici Financije i provjerite je li mjesec nestao na grafikonu. Možete promijeniti opcije pod Odabir izvora podataka tako da prikazuje skrivene ćelije.

Povezivanje naslova grafikona s formulama

Budući da su svi podaci povezani s padajućim okvirom, možete jednostavno stvoriti dinamički naslov u grafikonu stvaranjem formule za naslov, a zatim povezivanjem tog naslova s ​​grafikonom. Prati ove korake:

U ćeliji A1 ovog modela promijenite naslov u sljedeće: =“Pet godina strateškog predviđanja troškova za pozivni centar – “&F1.

Ampersand (&) služi kao konektor koji će nizati tekst i vrijednosti iz formula zajedno.

Umjesto znaka ampersanda, možete koristiti i funkciju CONCATENATE, koja radi vrlo slično spajanjem pojedinačnih ćelija, ili funkcija TEXTJOIN novi je dodatak Excelu 2016, koji će spojiti velike količine podataka.

Kada formula u ćeliji A1 radi, trebate povezati naslov na grafikonu sa ćelijom A1.

Kliknite naslov grafikona.

Ovaj dio može biti nezgodan. Provjerite jeste li odabrali samo naslov grafikona.

Kliknite traku formule.

Upišite =, a zatim kliknite ćeliju A1.

Pritisni enter.

Naslov grafikona se mijenja kako bi pokazao što se nalazi u ćeliji A1.

Povezivanje naslova grafikona s formulama.

Ne možete umetnuti formule u grafikon. S njom možete povezati samo jednu ćeliju. Svi izračuni moraju se izvršiti u jednoj ćeliji, a zatim povezati s naslovom kao što je prikazano.

Izrada dinamičkog teksta

Pogledajte mjesečno izvješće o proračunu. Već smo izradili formule u stupcima F i G, koje će se automatski ažurirati kako se podaci mijenjaju i prikazati kako idemo u usporedbi s proračunom.

Izrada grupiranog stupnog grafikona.

Sada ćete izraditi grafikon na temelju tih podataka i svaki put kada se brojevi promijene, željet ćete moći vidjeti koliko je stavki retka prekoračeno. Prati ove korake:

Istaknite podatke koji prikazuju vrijednosti računa, stvarne i proračunske vrijednosti u stupcima B, C, odnosno D.

Odaberite prvu opciju 2-D stupca u odjeljku Grafikoni na kartici Umetanje na vrpci da biste stvorili grupirani stupni grafikon.

U ćeliji A1 stvorite naslov s dinamičkim datumom.

Povežite naslov grafikona s formulom u ćeliji A1.

Uredite grafikon tako da naslovi budu vodoravno poravnati i promijenite boje.

Ovaj će grafikon izgledati puno bolje ako je sortiran tako da su veće trake na lijevoj strani.

Označite sve podatke uključujući naslove i kliknite gumb Sortiraj (u odjeljku Sortiraj i filtriraj na kartici Podaci na vrpci).

Pojavljuje se dijaloški okvir Sortiranje.

Poredaj po stvarnom od najvećeg do najmanjeg.

Razvrstavanje podataka grafikona.

Vrlo je lako zabrljati formule prilikom razvrstavanja, stoga svakako označite sve stupce od stupaca A do G prije nego što primijenite sortiranje.

Sada dodajte tekstualni komentar na grafikon. To možete učiniti dodavanjem komentara u jednu ćeliju, koja je dinamički povezana s vrijednostima u modelu i povezati ćeliju s tekstnim okvirom kako bi se komentar prikazao na grafikonu.

U ćeliji A15 izradite formulu koja će automatski izračunati koliko je stavki iznad proračuna.

To možete učiniti s formulom =COUNTA(G3:G12)-COUNT(G3:G12), koja izračunava koliko ćelija nije praznih u stupcu G.

Možete vidjeti da su dvije stavke retka iznad proračuna, pa ga pretvorite u dinamički tekst s formulom =COUNTA(G3:G12)-COUNT(G3:G12)&" Stavke iznad proračuna."

Dovršen grafikon s dinamičkim tekstnim okvirom.

Umetnite tekstni okvir u grafikon pritiskom na gumb Tekstni okvir u grupi Tekst na kartici Umetanje na vrpci.

Kliknite na grafikon jednom.

Pojavljuje se tekstni okvir.

Pažljivo odaberite vanjsku stranu tekstualnog okvira mišem, baš kao što ste učinili u zadnjem odjeljku kada ste povezivali naslove grafikona.

Sada idite na traku formule i upišite =.

Kliknite ćeliju A15 i pritisnite Enter.

Po potrebi promijenite veličinu i pozicionirajte tekstni okvir.

Testirajte model promjenom brojeva tako da više stavki prelazi proračun i provjerite mijenja li se komentar u tekstualnom okviru.