Kućni budžet - Kućni proračun - Kako planirati, voditi evidenciju financijskih troškova budžeta

Kada je u pitanju vođenje i planiranje evidencije troškova budžeta svjesni smo da na internetu ima dosta primjera, no evo još jedan tutorijal sa primjerom kojega možete downloadati i svim funkcionalnim formulama.
U ovom tutorijalu prikazati ću kako možemo planirati, voditi, evidentirati financijske troškove dostupnih novčanih sredstava u Excelu. Nadam se da znate da na više načina možemo planirati osobne troškove i voditi evidenciju potrošenih novčanih sredstava bilo da se radi o kućnom budžetu, budžetu obrta ili udruge ili sportskog kluba i slično. Isto tako možemo planirati troškove i evidenciju na godišnjoj ili mjesečnoj bazi (mogućnosti su raznolike)

Ja ću u ovom primjeru prikazati jedan od načina planiranja i vođenja financijskih troškova kućnog budžeta. Ovaj primjer možete iskoristit i za druge svrhe u kojima su osnove

• Ulaz novčanih sredstava
• Izlazni troškovi prema namjeni
• Stanje financija na raspolaganju

Na slici ispod uočite radne listove unutar radne knjige koje sam isplanirao za vođenje kućnog budžeta u Excelu. U samom početku krenuo sam od imenovanja nekih raspona ćelija da bi ih mogao kasnije koristiti u padajućim izbornicima. Tako uočite da sam imenovao naziv raspona za; 'mjesece' i 'godine'. Dakle poželjno je imenovati određene raspone ćelija na jednom mjestu i onda ih pozivati i korisitit na ostalim radnim listovima. Ovo je dobro zato što u slučaju da trebamo neki naziv izmjeniti, izmjenu izvršimo na jednom mjestu a Excel izvrši kalkulaciju i napravi ostale izmjene na drugim radnim listovima.

Radni list za 2015 godinu - evidencija unosa troškova po mjesecima i datumima

Na ovom radnom listu za 2015 godinu uočite slijedeće (slika ispod):

U ćeliji 'A1' nalazi se slijedeća formula: =MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256)
Ova formula automatski povlači naziv radnog lista

U ćeliama 'C1:N1' linkani su nazivi troškova sa radnog lista "imenovanja". Sjećate se da sam napomenuo da na jednom mjestu imamo nazive troškova i samo ih linkamo na ostale radne listove.

U stupcu 'A' ćeliji 'A2' nalazzi se slijedeća formula: =TEXT(IF(B2<>"";B2;"");"MMMM")
Ova formula kao rezultat vraća naziv mjeseca u godini na osnovu upisanog datuma u ćeliji 'B2'. Ovu formulu kopiramo prema dolje i možemo zaključati stupac 'A' po potrebi radi sigurnosti da ga ne slučajno ne izmjenimo jer sadrži formulu a ovaj stupac 'A' možemo i sakriti da nam vizualno ne smeta.

U ćeliju 'B2' upisujemo datum kada je nmastao financijski trošak

U ćelijama 'C2:K2' upisujemo određeni novčani iznos za određenu stavku troška.

U ćeliji 'L2' nalazi se formula koja zbraja sve vrijednosti troškova za dotični datum (formulu kopiramo prema dolje do željenog zadnjeg reda)
=SUM(C2:K2)

U ćeliju 'M2' upisujemo novčani iznos prihoda tj. ulaz na financijsko stanje (plaća, najam, loto dobitak :-)

U ćeliji 'N2' nalazi se formula koja zbraja sve vrijednosti troškova za dotični datum u Row2
=SUM(M2-L2)

U ćeliji 'N3' nalazi se formula koja pribraja ulaz novčane vrijednosti sa stanjem prethodnog datuma i od ovog zbroja oduzima novi trošak iz istog reda. (formulu kopiramo prema dolje do željenog zadnjeg reda)
=(M3+N2)-L3

Na ovom radnom listu uočite da su istom bojom označeni redovi koji pripadaju istom mjesecu u godini. Ovo sam riješio koristeći "Conditional Formatting" ali više o tome u narednom dijelu tutorijala.

Bojanje redova iz istog mjeseca u toku godine pomoću uvjetnog oblikovanja

Radni list za godinu 2016 identičan je radnom listu za godinu 2015. Ovaj radni list iskoristiti ću da vam objasnim kako sam koristio "Uvjetno oblikovanje u Excelu 2013" da bih obojao ili označio sve redove koji pripadaju istom mjesecu u godini.

Označavanje redova za isti naziv mjeseca u godini na slici iznad urađeno je pomoću Uvjetnog oblikovanja koristeći imenovane ćelije za određeni mjesec. Na slici ispod uočite radnje slijedećim redoslijedom:

Na radnom listu "imenovanja" svaki mjesec posebno imenovao sam određenim nazivom (siječanj=Month1, veljača=Month2, ožujak=Month3 itd...)

Na radnim listovima "2015" i "2016" selektirao sam raspon ćelija 'A2:N700' što možete uočiti na slici ispod u uokvirenom dijelu koji prikazuje dijalog prozor "Conditional Formatting Rules Manager". Prilikom dodavanja svakog novog pravila postavio sam formulu (vidi uokvireni dio na slici ispod) dijalog prozor "Edit Formatting Rule" =IF($A2=Month1;TRUE;FALSE) i postavio format boje za dotično pravilo.

Ovdje želim napomenuti kada kreirate jedan radni list npr. za 2015 godinu, na završetku godine kopirate dotični radni list i preimenujete ga u 2016 godinu. Obrišete nepotrebne podatke trošškova koje ste upisivali, pregledate ostale formule i nastavite voditi kućni budžet u novoj godini.

Znači ovakvih pravila ima dvanaest za svaki mjesec jedno pravilo i različita boja. Koristeći CF možemo vizualno uočiti ako smo zaboravili upisati neki datum i trošak jer će nam Excel bojom prikazati kojem mjesecu pripada bez obzira da li se određeni datum nalazi iza već upisanih prethodnih mjeseci. Ovo može biti slučaj da nam supružnik, djelatnik, šef, ili već bilo tko stvori trošak koji nismo na vrijeme evidentirali. Ovakav slučaj možete vidjeti na radnom listu '2015' za mjesec "siječanj" (druga slika po redu iznad). Nemojte se brinuti za radni list "sumarum" jer će Excel uzeti u obzir i ovaj trošak koji nije po redoslijedu što možete vidjetiu na zadnjoj slici ovog tutorijala.

Pivot Tablica za vođenje i evidenciju troškova kućnog proračuna ili budžeta

Na radnom listu "grafikoni" uočite tablicu u rasponu 'A1:D14'. Ova tablica je izvor podataka za dva grafikona (bar i pie). Naravno vama ostaje za izbor vrsta grafikona. Također uočite Pivot Table pomoću koje vrlo brzo možemo doći do određenih podataka. Uz to ako još koristite Slicers u Pivot Table tada nema kraja željenim rezultatima.

Ukratko ću prikazati formule u tablici (crvena slova)

U ćeliji 'A1' nalazi se linkana formula koja povlači godinu sa radnog lista "sumarum": =sumarum!$A$1

U ćeliji 'A2' nalazi se formula koja fiksira adresu i rezultat formule, dotična formula vraća broj godine iz ćelije 'A1' ali prilagođen za korištenje u formuli uz funkciju INDIRECT. ="'"&A1&"'!" (ćelija je vizualno formatirana fontom bijele boje i neću je posebno objašnjavati jer sam je pojasnio u dijelu tutorijala vezanom za radni list "sumarum")

U ćeliji 'A3' nalazi se formula koja kopira godinu iz ćelije A2: =$A$2 (ćelija je vizualno formatirana fontom bijele boje, ova formula kopirana je prema dolje do zadnjeg reda)

U ćeliji 'B2' nalazi se link-formula koja kopira nazive mjeseca sa radnog lista "imenovanja": =imenovanja!A5 (ova formula kopirana je prema dolje do zadnjeg reda). Ova ćelija 'B2' imenovana je nazivom ShName2 jer se ova ćelija koristi u daljnjim kalkulacijama i formulama u stupcu C i D.

U ćeliji 'C2' nalazi se formula =SUMIF(INDIRECT(ShName2&"A2:L700");B2;INDIRECT(ShName2&"L2:L700"))
Ova formula kao što sam rekao koristi ćeliju 'B2' u kalkulaciji da bi vratila rezultat za određenu godinu i dotični mjesec a to je u ovom slučaju SUM ili Zbroj svih troškova za dotični mjesec. (ova formula kopirana je prema dolje do zadnjeg reda)

U ćeliji 'D2' nalazi se formula =SUMIF(INDIRECT(ShName2&"A2:M700");B2;INDIRECT(ShName2&"M2:M700"))
Ova formula kao što sam rekao koristi ćeliju 'B2' u kalkulaciji da bi vratila rezultat za određenu godinu i dotični mjesec a to je u ovom slučaju ULAZ (prihod) svih financija za dotični mjesec. (ova formula kopirana je prema dolje do zadnjeg reda)

VAŽNO!

U ovom radnom listu "grafikon" korištena je VBA makronaredba za automatsko osvježavanje (auto refresh) Pivot Tablice. Ako ovu makronaredbu i dalje želite koristiti obavezno datoteku (Excel aplikaciju proračuna) snimite sa extenzijom *.XLSM ili snimite kao *.XLS ali u slučaju da datoteku snimate za Excel 2003 tada je potrebno umjesto IFERROR funkcije koristiti kombinaciju IF/ISERROR Excel funkcija, inače može doći do nekompatibilnosti između dvije verzije Excela 2013 i Excela 2003 što može uzrokovati jako dugačku formulu ali nije nemoguće da funkcionira kako treba.

VBA makronaredba za automatsko osvježavanje PivotTable prilikom izmjene podataka i prijelaza sa radnog lista na radni list i vraćanja na radni list na kojem se nalazi PivotTable. Vba makronaredbu treba kopirati u Sheet Module "grafikon" i ona se automatski pokreće bez vaše intervencije.

Private Sub Worksheet_Activate()
Sheets("grafikon").PivotTables("PivotTable1").RefreshTable
End Sub

Brzi pregled evidencije troškova budžeta i ispis na pisač

Prvi radni list "sumarum" prikazuje rezultate za jedan mjesec (u ovom slučaju za siječanj-2015). Na radnom listu "sumarum" u ćeliji 'A1' sa izbornika biramo godinu a u ćeliji 'B1' izabiremo naziv mjeseca za koji želimo prikazati podatke evidencije. Također uočite grafikone koji prikazuju troškove po mjesecima u toku izabrane godine.

Na ovom radnom listu definirana je Print_Area za ispis tablice u rasponu "A1:zadnji popunjeni red". Dakle ne moramo se brinuti kod ispisa na papir, Excel će automatski postaviti područje ispisa (Print Area) i ignorirati prazne redove tablice. U drugom redu nalazi se zbroj za svaki stupac pojedinačno. Pomoću ovog zbroja možemo uočiti koliko smo za dotičnu stavku u određenom mjesecu potrošili novaca.

U trećem redu nalaze se nazivi određenih stavki za koje imamo financijske izdatke tj. troškove. Ovi nazivi linkani su sa radnog lista "imenovanja" i ako ih želite izmjeniti tada izmjene izvršite na radnom listu "imenovanja". Vidi sliku iznad i uoči plavkastom bojom ćelije za nazive, a isto tako i nazive "trošak, ulaz, stanje".

Sliku grafikona koji se prikazuju na desnoj strani radnog lista u odnosu na tablicu je kreiran pomoću Camera Tool a u stvarnosti se nalaze na radnom listu "grafikoni".

U ćeliji 'A4' nalazi se slijedeća ARRAY formula (formula mora biti u jednoj liniji)

=IF(IFERROR(INDEX(INDIRECT(ShName&"B$2:B$700");SMALL(IF($B$1=INDIRECT(ShName&"$A$2:$A$700");ROW
(INDIRECT(ShName&"$A$2:$A$700"))-MIN(ROW(INDIRECT(ShName&"$A$2:$A$700")))+1;"");ROW(B1)));"")="";"";IFERROR
(INDEX(INDIRECT(ShName&"B$2:B$700");SMALL(IF($B$1=INDIRECT(ShName&"$A$2:$A$700");ROW(INDIRECT(ShName&"
$A$2:$A$700"))-MIN(ROW(INDIRECT(ShName&"$A$2:$A$700")))+1;"");ROW(B1)));""))

=IF(IFERROR(INDEX(INDIRECT(ShName&"C$2:C$700");SMALL(IF($B$1=INDIRECT(ShName&"$A$2:$A$700");ROW
(INDIRECT(ShName&"$A$2:$A$700"))-MIN(ROW(INDIRECT(ShName&"$A$2:$A$700")))+1;"");ROW(B1)));"")="";"";IFERROR
(INDEX(INDIRECT(ShName&"C$2:C$700");SMALL(IF($B$1=INDIRECT(ShName&"$A$2:$A$700");ROW(INDIRECT(ShName&
"$A$2:$A$700"))-MIN(ROW(INDIRECT(ShName&"$A$2:$A$700")))+1;"");ROW(B1)));""))

Ovu formulu možemo kopirati iz ćelije 'A4' u desnu stranu ali u svakom stupcu 'B4:M4' moramo ručno izvršiti ispravku dijela formule koji je obojan plavom bojom. Tako će formula uvijek vraćati podatke iz odgovarajućeg stupca. Kada završimo uređivanje formula u četvrtom retku tada selektiramo raspon 'A4:M4' i kopiramo prema dolje do zadnjeg željenog reda.

I na kraju evo onoga što ste nestrpljivo čekali :-)
Besplatan DOWNLOAD datoteke iz primjera ovog tutorijala možete skinuti na dotičnom linku. Obratite pažnju da su neki radni listovi zaštićeni ali bez passworda. Da bi ih otključali potrebno je kliknuti na "Unprotect Sheet". BTW: Ja se nadam da uz ovoliko pisanja nisam negdje pogriješio ali i ako jesam već ćete se vi snaći jer imate završeni primjer koji možete prilagoditi svojim potrebama :D
VAŽNO! ZIP datoteku i Excel aplikaciju iz primjera ovog tutorijala ZABRANJENO je distribuirati na drugim web stranicama, portalima, forumima i sl. za download (skidanje). (© - copyright)

Evo još jedan tutorijal za vođenje: Kućni budžet - Kućni proračun - Home Budget ..........