Hello, in this video we'll be creating an
effective budget using Google Sheets
Google Sheets is a web-based spreadsheet
application similar to Microsoft Excel
we're using Sheets because it's free and
doesn't require you to download and
install any software. Please feel free to
use Excel or your spreadsheet
application of choice as the exercises
we'll be performing should work in them as well
In order to follow along with this
tutorial you'll need the following things;
a gmail or google email account, a list of
your weekly, monthly and yearly expenses,
a track record of your spending for, at
least, the last week and it'll help if
you got some spreadsheet experience as well
For this tutorial, I'll be using an
example household with two income
earners, each with a salary of $36k
after tax, this will give them a
combined total household income of $72k
In order to get started, let's firstly go
through to google.com, you'll want to sign
into your Google account, then Google or
look up "spreadsheet", go to Google's link for
their spreadsheet application and finally
create a new spreadsheet
Let's do that now
[Go to google.com]
[Sign into your Google account]
[Google or look up "spreadsheet"]
[Click on Google's link for their spreadsheet app]
[Create a new spreadsheet]
Before we begin, here's a quick preview
of what will be making. The Blue section
is a list of all of our expenses ordered
from the top down by yearly, monthly and
weekly amounts. They've been calculated into
fortnightly amounts for my 52-week savings
program. The colored sections indicate
the amount entered as is whereas the
grey boxes have been adjusted. The Red
section is a list of all the bank
accounts we'll be distributing our funds into
There's two expense accounts; one for weekly,
fortnightly and monthly payments and the
other is for yearly payments. Below
the experiences you'll see there's four
savings accounts. The percentage for how
much each account gets adds up to a
hundred percent. The total percentage of
the savings accounts get is based on the
remainder of any funds after all
expenses have been accounted for
Finally, the Green section is a list of
all income sources. For our example, it is
our two income earners. You can see how much
they both get as weekly or fortnightly
amounts. Now that you've seen what we'll
be making let's get started. We'll begin
by adding our headers starting with
Budget for the whole page, then the Blue
section, Green section and Red section
Under the Green section for Income
Sources you'll want to put "Earner #1"
and "Earner #2"
Both of our income earners get $45,000
before tax. The value that you enter into this field can
be turned into a currency value just by
clicking the dollar sign in the menu
likewise the Tax Rate can be adjusted
into a percentage value by clicking the percentage sign
in the menu as well
The Tax Amount is a formula based on
the value in the Before Tax times [multiplied] by the
Tax Rate Amount. To do this you type in
equals then select the first value Before
Tax, the symbol for times [*] and then the
second value for the Tax Rate
Press enter
The After Tax value is a similar
formula. In this case you'll type in equals,
then select the value for Before Tax and
minus it by the Tax Amount. Repeat the
process for Earner #2. For our
example we only have two sources of
income but you could add additional
sources of income depending on your
circumstances
Let's leave a few blank spaces and
we're going to add up the total of our
sources of income. The best way to do
that is to do a SUM formula. What we'll
do is we'll type in equals, then we'll type in
SUM and then within brackets you'll want
to select the rows that you'll want the
total of. Do that now. We're going to call
this value Total Income After Tax
Next we're going to work out our Weekly Income
which is a formula based on our Total
Income After Tax value divided by 52
or 52-weeks. The Fortnightly Income value is
based on the Weekly Income value times
by two. This completes our exercise for
the Green section. Now we're going to
move back to the Blue section and
enter in our expenses
Once you've got all your expenses listed
then you'll want to reorder them starting
with the yearly expenses grouped at the
top, followed by the monthly expenses
grouped in the middle and the weekly
expenses group at the bottom. For my example
I've listed two savings funds in within the
expenses. You may have a similar
situation. For the fields that don't
already have values in them you can
calculate them like so;
where there is a Monthly value then
Yearly value can be entered in as the
Monthly value times by 12, where there's a
Yearly value but no monthly value then
the Monthly value can be calculated by the
Yearly value divided 12, where there's a
Monthly value but no Weekly value then
Weekly value can be calculated by the
Monthly value times by 12 divided by 52
and when there's a Weekly value but no Fortnightly
value it is the Weekly value times by 2
We can calculate the total of all expenses
by Yearly, Monthly, Weekly and Fortnightly by
using the SUM formula
and we can calculate how much we've got
left over for our savings by minusing [subtracting]
the Weekly or Fortnightly Expense
Amounts from the Weekly or Fortnightly
Income Amount. And that completes
this exercise for the Blue section
And finally, the Red section and that's
where you'll manage your funds transferring
either between a Check/Cheque or Savings account
For my budget, I have two Expense
accounts; one which handles my Weekly,
Fortnightly and Monthly expenses and a
separate one which handles my Annual
payments. To calculate what your total
should be between the two types of
Expense accounts, you'll just do a SUM of
the Yearly fields and then a separate
SUM for the non-Yearly fields. The total
of these two fields should be the same
as the one above, giving you the total of
your expenses. Next we'll want to work out
what we want to save towards. For my
example, I've got four savings accounts this
makes it easier to calculate a
percentage by. For example, for our four Savings
accounts you could divide it by
twenty-five percent each. You can of
course have more or less just as long as
the total equals a hundred percent
You'll notice that I've added some
percentage values starting with 50%, 20%, 20%
and 10% all of which add up to a
hundred percent. These are the percentage
values I want to use based on the
remainder for the Savings Amounts so to
do the calculation we'll enter equals
then the Remainder for the Savings Amount
times by the percentage value. The sum
of these should equal the Remainder of
the Savings Amount. And finally, we'll
want to add the Expenses Total plus the
Savings Total and then you should have
the same amount as the Income Total
If these values don't match then you may
have an error in one of your formulas
please review them again
To summarize, this is what each of the
sections do; the Green section shows
money going into your household while
the Blue section [generally] shows money going out of
the household and then finally the Red
section is how you manage your funds by
transferring from one account and into the other
This should give you a good picture of
your overall situation and if you find a
discrepancy with your numbers you may
have an expense you're not accounting for or some
of your numbers may be lower than what
they actually are so you'll want to review
those again. Once you've got an accurate picture
of your experiences and how you're
handling your funds then you can make
adjustments as needed. And that completes
this tutorial for Creating an Effective
Budget using Google Sheets. I hope you
enjoyed it, I'd appreciate your feedback
in the comments section below. My name is
Clifton please Like, Follow and Subscribe
to my YouTube channel which is 52WeekHSC
I also have a Facebook, Twitter and Instagram account
and I'll see you in the next video. Bye
Không có nhận xét nào:
Đăng nhận xét