W poprzednich artykułach z cyklu Prosty budżet domowy opisywałam całą drogę gromadzenia informacji o przychodach i wydatkach oraz pokazywałam sposób na ich podsumowywanie (oczywiście z wykorzystaniem tabeli przestawnej). Do tej pory koncentrowałam się więc na gromadzeniu danych i ich analizie. Było to konieczne, gdyż – aby kontrolować przepływ domowych pieniędzy – musimy wiedzieć, ile ich faktycznie mamy i co z nimi robimy.

Dopiero kiedy jesteśmy z tym zaznajomieni i wpisywanie paragonów weszło nam w krew, a przede wszystkim zgromadziliśmy jakiekolwiek DANE, jest nam łatwiej planować, co zrobimy ze swoimi pieniędzmi – czyli właśnie budżetować. W tym artykule pokażę metodę budżetowania miesięcznego, czyli planowania, co zrobimy z naszymi pieniędzmi w kolejnym miesiącu.

Ten artykuł jest długi, ponieważ opisuję w nim 5 tabelek. Jednak zapewniam, że warto dotrwać do końca! 🙂

Zacznijmy od stworzenia nowego arkusza w naszym pliku z budżetem. Swój arkusz nazwałam po prostu Budżet.

Arkusz ten podzielony będzie na 3 sekcje:

1. Dane wejściowe: zarobki w poprzednim miesiącu, miesiąc budżetowania, rok budżetowania i procent, o jaki chcemy zwiększyć prognozę wydatków.
2. Planowanie: w tej sekcji będziemy decydować, na co przeznaczymy pieniądze w danym miesiącu.
3. Podsumowanie miesiąca: fakty, czyli na co faktycznie wydaliśmy pieniądze i jak w związku z tym wyglądają nasze oszczędności.

A oto miniaturka gotowego arkusza:

Arkusz budżetowania

Teraz omówię oddzielnie każdą sekcję arkusza.

Dane wejściowe

Tutaj mamy 4 informacje:

1. Zarobki w poprzednim miesiącu – komórka E1.
2. Miesiąc budżetowania – komórka J1.
3. Rok budżetowania – komórka O1.
4. Procent, o jaki chcemy zwiększyć prognozę wydatków – komórka S1.

Trzem z tych komórek nadałam nazwy. Mianowicie:

• J1 to Mc,
• O1 to Rok,
• S1 to Procent_prognoz.

Do tych nazw odwołuję się w dalszych formułach. Najprostszą metodą na nazwanie komórki jest zaznaczenie jej i wpisanie w polu nazwy (po lewej stronie od paska formuły) wybranej nazwy. Zatwierdzamy oczywiście Enterem.

Zarobki (E1) wyliczy następująca formuła:

=SUMA.WARUNKÓW(tb_DANE[PLN];tb_DANE[In/Out];"Przychód";tb_DANE[Rok-mc];Rok&"-"&Mc-1)

Funkcja ta sumuje tylko te wartości w kolumnie PLN tabeli danych, które dotyczą przychodu za poprzedni miesiąc (jeśli ktoś dostaje pensję na początku miesiąca, to ostatni argument niech wpisze taki: Rok&”-„&Mc). Zobaczcie, że zastosowałam tutaj funkcję SUMA.WARUNKÓW (zakochałam się w niej ostatnio!). Można powiedzieć, że jest to rozszerzona wersja funkcji SUMA.JEŻELI. Ta ostatnia sumuje dane pod względem tylko jednego warunku, zaś pierwsza – może ich sprawdzić aż 127! Co prawda tutaj w zupełności wystarczą nam 2 warunki.

Miesiąc (J1) i rok (O1) wpisz samodzielnie z palca. Oczywiście można się tutaj pokusić o formułę, która dane te wpisywałaby automatycznie, jednak myślę, że to przerost formy nad treścią. Poza tym, lepiej mieć kontrolę nad uzupełnianiem wydatków z danego miesiąca – każdy może przecież w innym dniu tworzyć budżet.

Warta omówienia jest ostatnia wartość – procent do prognoz. Jest to procent, o który zakładamy, że zwiększą się nasze wydatki w następnym miesiącu (budżetowanym). Np. ostatnio na jedzenie wydawaliśmy średnio 1 000 zł, a zakładamy wzrost wydatków do 1 100 zł. Musimy więc ustalić procent prognoz na 10%. Uważam, że warto założyć większy limit (prognozę), aby później na wszystko starczyło nam pieniędzy. Procent prognoz możecie oczywiście dowolnie zmienić – 10% to tylko przykład.

Planowanie

W tej sekcji będziemy prognozować wydatki i planować, co zrobić z pieniędzmi, które nam zostaną. Sekcja planowania wygląda następująco:

Sekcja Planowanie

Jak widzicie, są to 3 tabelki:

1. Prognoza wydatków,
2. Plan oszczędzania,
3. Stan oszczędności.

Szczegółowo omówię każdą z nich.

Prognoza wydatków

Tabelka ta pokazuje nam, ile w tym miesiącu wyniosą nas wydatki stałe (jedzenie, czynsz, kredyt, itp.) oraz ile wydatki dorywcze (ubrania, kino, itp.). Duże wydatki, tzw. „strzały”, na razie pomijamy.

Tylko tyle.

Zarówno prognoza wydatków stałych, jak i dorywczych będzie średnią arytmetyczną obliczaną z ostatnich 12 miesięcy. Zobaczymy, ile wydaliśmy w ciągu ostatniego roku, i podzielimy tę kwotę przez 12, aby uzyskać wartość dla miesiąca. Całość zwiększymy następnie o procent prognoz (komórka Procent_prognoz z danych wejściowych), a na koniec zaokrąglimy w górę do pełnych setek. Wersja dość ostrożna, jednak lepsza niż niedoszacowana.

Dodatkowo zauważcie, że prognozujemy tylko wydatki stałe i dorywcze. Wydatków dużych, które zdarzają się raz na jakiś czas, np. lodówka, komputer czy meble, tutaj nie rozpatrujemy. Są to bowiem wydatki, które klasyfikuję jako cele, czyli coś, na co zbieramy pieniądze oddzielnie. Zbieramy np. co miesiąc, a wydajemy z pieniędzy uzbieranych na dany cel i po prostu wpisujemy w tabeli z danymi jako wydatek z kategorią cel .

Jak już poznamy prognozowane wartości wydatków stałych i dorywczych, poznamy również kwotę pozostałą do rozdysponowania. Od razu zaznaczę, że zakładam, że coś nam zostanie i te pieniądze będziemy dalej alokować. Oczywiście zdarzają się sytuacje, kiedy nic nam nie zostaje. Wtedy po prostu nie alokujemy pieniędzy, bo nie mamy czego.

Ok. Obliczmy zatem prognozowane wydatki stałe.

Informację o tym, czy dana pozycja jest wydatkiem, znajdziemy po kolumnie IN/Out. Będzie tam informacja: Wydatek. O tym, czy jest on stały, dowiemy się z kolumny Rodzaj z wpisem Stały. Pytanie tylko, jak sprawdzić, że wydatek został poniesiony w ciągu ostatniego roku? Zwróć uwagę, że każda pozycja ma datę. Jeśli określę dwie daty graniczne: datę od i datę do (początek i koniec 12 miesięcy, które mnie interesują), to data każdego wydatku z ostatniego roku będzie zarówno większa od daty od i mniejsza od daty do. Wszystko, czego teraz potrzebujemy, to ustalić datę od i datę do.

Żeby moja formuła była bardziej czytelna, wspomniane daty wyliczę w oddzielnych komórkach i się do nich odwołam. Niech więc data od będzie o komórce X1, a data do w X2.

Załóżmy, że planujemy czerwiec 2015 (w danych wejściowych w komórce Mc będzie 6, a w komórce Rok będzie 2015). Ostatni rok będzie więc zaczynał się pierwszego dnia czerwca 2014 (data od) i kończył ostatniego dnia maja 2015 (data do). Formuły będą zatem takie:

Data od:

=DATA(Rok-1;Mc;1)

Data do:

=DATA(Rok;Mc-1;30)

W dacie do założyłam ostatni dzień miesiąca jako 30. Nie chcę się bawić w ustalanie dokładnej daty końca miesiąca. Byłoby to dość skomplikowane, a 1 czy 2 dni w porównaniu do całego roku, nie mają znaczenia. Ułatwmy więc sobie życie! 🙂

Ok. Jak już ustaliliśmy daty graniczne ostatniego roku, możemy policzyć nasze wydatki stałe. Do komórki E5 wpisz taką megaformułę:

=ZAOKR.W.GÓRĘ(SUMA.WARUNKÓW(tb_DANE[PLN];tb_DANE[In/Out];"Wydatek";tb_DANE[Rodzaj];"Stały";tb_DANE[Data];">="&X1;tb_DANE[Data];"<="&X2)/12*(1+Procent_prognoz);100)

Co robi ta formuła? Funkcja SUMA.WARUNKÓW (mówiłam, że będzie jej dziś dużo:)) sumuje wartości z tabeli tb_DANE, które:

• są wydatkami (IN/Out to Wydatek),
• są wydatkami stałymi (Rodzaj to Stały),
• zostały poniesione w ciągu ostatnich 12 miesięcy (między datą od i datą do).

Następnie suma tych wydatków jest dzielona przez 12 i powiększana o Procent_prognoz. Na koniec za pomocą funkcji ZAOKR.W.GÓRĘ wynik jest zaokrąglany w górę do pełnych setek. Z moich roboczych danych wyszło 2 900 zł.

Tak samo liczone są wydatki dorywcze (komórka E6). Jedyną różnicą w formule jest zamiana słowa „Stałe” na „Dorywcze”. Zobaczcie:

=ZAOKR.W.GÓRĘ(SUMA.WARUNKÓW(tb_DANE[PLN];tb_DANE[In/Out];"Wydatek";tb_DANE[Rodzaj];"Dorywczy";tb_DANE[Data];">="&X1;tb_DANE[Data];"<="&X2)/12*(1+Procent_prognoz);100)

W moim roboczym pliku wyszło 900 zł. Jakoś mało realistycznie. 🙂

Ostatnim krokiem jest policzenie, ile pieniędzy nam zostanie do rozłożenia na oszczędności. Tutaj formuła jest bardzo prosta – zwykłe odejmowanie: zarobki minus suma wydatków. Formuła jest taka (komórka E7):

=E1-SUMA(E5:E6)

Tak wygląda uzupełniona tabelka:

Tabelka planowania

Tym sposobem mamy już stworzoną tabelkę prognozy wydatków. Teraz pozostaje nam ją tylko sformatować, i gitara!

Przejdźmy więc do planu oszczędzania.

Plan oszczędzania

Tutaj decydujemy, co zrobimy z pieniędzmi, które nam zostały. Pusta tabelka wygląda tak:

Tabelka do planowania oszczędności

Jeśli zaistnieje taka konieczność, mamy możliwość przeznaczenia dodatkowych pieniędzy na wydatki stałe i dorywcze (np. za chwilę będzie wrzesień, więc trzeba będzie dzieciom kupić podręczniki szkolne itp.). Dodatkowo alokujemy tutaj pieniądze na sytuacje awaryjne (warto mieć jakieś zaskórniaki na nieprzewidziane wydatki!), inwestycje i cele (np. kupno nowej lodówki). Oczywiście powiększajcie tę tabelkę w razie potrzeb. Powyżej zaproponowałam jedynie jej wygląd. Być może będziecie chcieli rozbudować pozycję Inwestycje o konkretne rodzaje (akcje, lokaty, obligacje itp.), a celów zapewne będziecie mieli więcej niż jeden. 🙂 Także pełna dowolność. Pamiętajcie tylko o odpowiedniej modyfikacji formuł (większe zakresy).

Najważniejsze jest to, że w żółte pola w tabelce z palca wpisujemy kwoty, które chcemy przeznaczyć na daną pozycję budżetu.

Formuły? Tak, dwie. Jedna informuje o ilości pieniędzy, które mamy do alokacji (K4), a druga to zwykła suma ulokowanych pieniędzy (K10).

Ilość pieniędzy, które mamy do alokacji (K4), obliczymy następująco:

=E7-K10

Ta kwota jest liczona dość śmiesznie. Pamiętajmy, że – gdy przeznaczymy jakieś pieniądze na wybrany cel – pierwotna pula środków nam się kurczy. Stąd właśnie takie odejmowanie.

A to suma przeznaczonych pieniędzy:

=SUMA(K5:K9)

Jeśli przekroczymy kwotę do alokacji, pierwsza formuła zwróci ujemną wartość.

I na koniec przykład uzupełnionej tabelki:

Uzupełniona tabelka do planowania

Stan oszczędności

Ta tabelka pokazuje, ile mamy pieniędzy na poszczególnych kontach (w funduszach). Czyli np. ile już uzbieraliśmy na wydatki stałe, na cele (np. komputer).

Pozycje wydatki stałe i wydatki dorywcze są w pewnym sensie techniczne. Oba te konta co miesiąc zasilamy i z obu zabieramy pieniądze. Są jednak miesiące, w których zabieramy mniej niż wkładamy i wtedy zostaje nam nadwyżka. Tej nadwyżki nie ruszamy, ponieważ może się zdarzyć również i tak, że włożymy mniej niż wydamy. Lepiej w tę stronę, niż miałoby zabraknąć.

Oto, jak wygląda tabelka:

Tabelka ze stanem oszczędności na początku miesiąca

Są w niej dwie kolumny wartości: Było i Jest.

W kolumnie Było znajduje się informacja, ile pieniędzy jest na danym koncie na początku miesiąca (w naszym przykładzie: na początku czerwca 2015).

Kolumna Jest informuje o tym, ile pieniędzy mamy na koncie w danym miesiącu, uwzględniając kwoty zaplanowane w poprzedniej tabelce. Czyli np. na koncie wydatków stałych mieliśmy na początku czerwca 571 zł. Z prognozy na podstawie ostatnich 12 miesięcy wynika, że wydamy na nie 2 900 zł (pierwsza tabelka) i nie planowaliśmy zwiększać tych wydatków (w drugiej tabelce jest zero). Obecnie na koncie wydatków stałych mamy 571 + 2 900 = 3 471 zł. Tyle pieniędzy w czerwcu przeznaczamy na wydatki stałe.

Dane w kolumnie Było uzupełniamy ręcznie na początku miesiąca, więc nie ma tam żadnej formuły. W kolumnie Jest należy zaś wpisać następującą formułę (R5):

=Q5+E5+K5

i skopiować ją do pozostałych komórek (R6:R9). Polecam ci metodę szybkiego wpisywania formuł z zachowaniem formatowania komórek.

Wiersz podsumowujący zawiera oczywiście funkcję SUMA (Q10 i Q11):

=SUMA(Q5:Q9)

I to tyle jeśli chodzi o sekcję planowania.

Przejdźmy do informacji o faktach na koniec miesiąca.

Podsumowanie miesiąca: fakty

Tutaj już mamy tylko dwie tabelki. Pierwsza informuje o poniesionych wydatkach na poszczególnych kontach, druga pokazuje stan oszczędności na koniec miesiąca.

Cała sekcja wygląda tak:

Sekcja podsumowująca miesiąc

Tabela poniesionych wydatków

Tutaj mamy informacje o poniesionych wydatkach, w podziale na poszczególne konta: wydatki stałe, dorywcze, cele i awarie.

Oto tabela:

Tabela poniesionych wydatków

Dane będziemy sumować z tabeli danych (tb_DANE) i użyjemy do tego (znowu!) funkcji SUMA.WARUNKÓW. Do zakresu J15:J18 wpisz następującą formułę:

=SUMA.WARUNKÓW(tb_DANE[PLN];tb_DANE[In/Out];"Wydatek";tb_DANE[Rodzaj];$I15;tb_DANE[Rok-mc];Rok&"-"&Mc)

Komórka J19 oczywiście sumuje wydatki:

=SUMA(J15:J18)

Pod tabelką właściwą jest miejsce na wpisanie informacji o wydatkach na konkretne cele. Ponieważ w tabeli danych nie określamy, na jaki cel wydajemy pieniądze, a jedynie zaznaczamy rodzaj wydatku jako Cel, Excel nie odróżni, na co konkretnie wydaliśmy pieniądze. Musimy to zrobić ręcznie w żółtych polach. W komórce K26 dorzuciłam jeszcze kontrolkę, czy suma wydatków wpisanych w żółte pola zgadza się z ogólną sumą wydatków na cele (komórka J17). Formuła jest następująca:

=JEŻELI(J24=J17;"ok";"błąd")

Lubię takie kontrolki, więc stosuję. Wy – według uznania.

Tabela ze stanem oszczędności

To piąta, ostatnia tabelka. Ma ona na celu pokazanie, ile pieniędzy na jakich kontach nam zostało po odjęciu wydatków w danym miesiącu. I taka też będzie formuła: stan oszczędności na początku miesiąca minus poniesione wydatki w tym miesiącu. Wpisz ją w zakresie R15:R19:

=R5-J15

A na koniec podsumuj (R20):

=SUMA(R15:R19)

Tak wygląda uzupełniona tabelka:

Tabela pokazująca stan oszczędności na koniec miesiąca

Technicznie to tyle. Teraz jestem wam jeszcze winna wyjaśnienie, jak i kiedy to wszystko uzupełniać.

Kolejność uzupełniania danych

Długo się zastanawiałam, w jaki sposób to opisać. Nie było to oczywiste, ponieważ uzupełnianie arkusza budżetowania to trochę jak jajko i kura: najpierw zamknąć miesiąc, czy go otworzyć? Zdecydowałam jednak, że opiszę to tak, jak będziecie to najczęściej robić, czyli najpierw zamknę miesiąc, a potem otworzę kolejny. Za pierwszym razem, jak będziecie uzupełniać budżet, zacznijcie oczywiście od otwierania miesiąca.

Aby poprawnie zadziałały nam wszystkie tabelki stworzone przed chwilą – koniecznie musimy uzupełnić dane w tabeli danych (tb_DANE). To właśnie z niej pobierane są informacje o przychodach i wydatkach. Dane uzupełniamy najlepiej na bieżąco, jak tylko pojawi się dany przychód czy wydatek.

Załóżmy więc, że mamy koniec maja 2015, dostaliśmy pensję, znamy już swoje majowe wydatki. Możemy zamykać miesiąc. Interesuje nas więc tabelka 4. Poniesione wydatki. Jedyne, co musimy tutaj uzupełnić, to żółte pola, informujące o wydatkach na konkretne cele, jeśli takie ponieśliśmy. I tyle.

Więcej pracy będzie z początkiem miesiąca. Działamy według następującego schematu:

1. Uaktualnij stan oszczędności na początku miesiąca. Oszczędności na początku miesiąca są oczywiście takie, jakie były na końcu poprzedniego miesiąca. Skopiuj więc zakres R15:R19 (tabelka 5.) i wklej go specjalnie jako wartości do zakresu Q5:Q9 (tabelka 3.).
2. Usuń dane z planu oszczędzania: K5:K9 (tabelka 2.).
3. Usuń dane z wydatków na cel J22:J23 (tabelka 4.).
4. Uzupełnij miesiąc w J1 (dla omawianego przykładu: 6) i rok w O1 (2015).
5. Uzupełnij tabelkę 2. plan oszczędzania.

Trochę się rozpisałam… Ten artykuł jest najdłuższy na moim blogu! Mam nadzieję jednak, że wart poświęconego czasu i że wam się przyda. Zależało mi, aby proces budżetowania było możliwie łatwy i mam nadzieję, że udało mi się to osiągnąć. Co myślicie? A może macie jakieś pomysły na usprawnienia? Jeśli tak, jestem bardzo ciekawa jakie. Śmiało piszcie w komentarzach!

Artykuł najdłuższy i jednocześnie ostatni z cyklu „Prosty budżet domowy”. Na koniec więc umieszczam plik, który tak skrupulatnie opisywałam:

Enjoy!

Redakcja językowa: Aleksandra Wasiak