Last week I released to you two
free budgeting Excel worksheets that
work together to help you keep track of your expenses and stay within your
proposed budget.
***
Note: I found an error in one of the files. If
you downloaded the files before Dec 14, 2015 you may want to
re-download them.
***
Q: How do I set up my budget plan?
A: Here is a basic run-through:
- FamilyBudget_Balance2016.xlsx: go to the “Start
Here” tab.
- Modify, replace, or add new categories that fit your
family’s needs.
- Modify, replace, or add new accounts that your family uses.
- Modify, replace, or add new transaction descriptions that
you know you will be likely to use frequently over the year.
- Modify, replace, or add new locations that you know you
will be likely to use frequently over the year.
- Enter the starting balances for each account.
- FamilyBudget_Plan2016.xlsx: go to the
“Budget” tab. (Keep other file open.)
- Copy category items related to income (eg. “Income
(Gifts)”) from FamilyBudget_Balance2016.xlsx to cell A7 on
“Budget” tab of FamilyBudget_Plan2016.xlsx.
- Copy remaining category items (ie. not related to income) from
FamilyBudget_Balance2016.xlsx to the first cell in column A below
“Expenses” on the “Budget” tab of FamilyBudget_Plan2016.xlsx.
- Repeat step 2 for copying category items to the “Actual”
tab of FamilyBudget_Plan2016.xlsx.
There is a bug with the copy-paste method above that I currently don’t know
how to get around. If it doesn’t work properly, undo paste, press tab at the
end of the table, and add each new item individually or modify existing items.
Right click to delete table rows for any excess categories you don’t want. If
you desire, you can sort the list alphabetically by selecting all cells under
“Income Type” or
“Expenses” (as appropriate),
right click, and select
Sort –> Sort A to Z.
Q: How do I edit or add new categories, descriptions and/or
locations?
A: Add items as needed by pressing tab at the end of each
list. Modify and delete items as needed. After you have entered all items,
sort the edited list alphabetically by clicking the gray box to the right of the
header and select
Sort A to Z.
Q: How specific should my categories be?
A: As specific as you would like them to be. We decided to
combine groceries, personal hygiene supplies, and basic home supplies (such as
batteries) all under “Grocery.”
Check out this blogger’s article about 20 categories that are often overlooked when making a budget.
Q: Why do I need to list these account names, categories,
descriptions and locations? Am I wasting my time?
A: In the long run, this is a time-saver! When a cell
under Description, Location, Category or Account is selected, there is a down
arrow that appears to the right of the cell that allows you to choose an item
from a drop-down list.
 |
| Default drop down list for Location. |
For Description and Location, this is just more
convenient than always re-typing the same information when you complete similar
transactions on a regular basis (buying groceries or paying rent, for example).
It is necessary, however, to list category and account names because this information
links to various parts of both worksheets in order to make calculations.
Q: What happens if I don’t do as you say and end up
deleting or renaming a worksheet tab (for example)?
A: You will most likely break it. All tabs draw from data
on different sheets, so it is important that none are deleted or renamed because
many functions won’t be able to find that data anymore.
Q: There are only 5 lines for each month’s balance sheet
and I have more than 5 transactions to enter for this month. What do I do?
A: The default number of lines for each month was set to
5. To add more lines to the end of the table:
- Select the bottom right cell.
- Press tab.
Q: It’s the end of the month and I just realized that I
forgot to include one transaction in the middle of the month. Do I have to
delete all of the rows of data I entered after that date and pretty much start
all over? Can I add a row in the middle of the table, and if so, how do I do
it?
A: No, thankfully you don’t have to undo all that work
you’ve already done in entering your transaction history!
To add a line in the middle of the table:
- Select any cell in the first row that passes the date of the transaction you
missed.
- Right click the selected cell and select Insert --> Table Rows
Above.
- You will notice that the cell containing the balance at the end of the row
below the new line will have a small green triangle in the top left corner.
Select the cell and notice the warning symbol that appears to the left of the
cell.
- Click on the symbol and select Restore to Calculated Column
Formula. If you do not restore the formula to that cell, the new
transaction amount you enter will not be added to the current balance.
Q: What is this “Cash Available” table all about in
FamilyBudget_Plan2016.xlsx?
A: This table shows your cash flow and has a slightly
different purpose on the “Budget” and “Actual” tabs in FamilyBudget_Plan.xlsx.
When planning your budget (ie. on the
“Budget” tab), ideally
you want the numbers here to be equal to $0. This table is helpful for setting
up or adjusting your budget because it helps you to know how much money you have
left to set aside for each category. When you enter your expected income in the
“Income” table, the values in the “Cash Available” table will be positive.
Then, as each expected expense is entered, the value decreases. If the value
drops below 0, your expected expenses are greater than your expected income –
ie. you will not be “living within your means.” Either you need to find a way
to make more money each month or cut some of your expenses.
When analyzing your budget (ie. on the
“Actual” tab),
ideally you want the numbers here to be greater than or equal to $0. A negative
number (which might appear within parentheses) means you are losing money and a
positive number means you are making money. A balance of $0 (whether for the
month or by the end of the year) means you are keeping exactly to your budget.
Q: A cell turned red in the “Expenses” table on the
“Actual” tab in the file FamilyBudget_Plan2016.xlsx. What did I do? Did I
break it?
 |
| A red-highlighted cell means you have spent more than your budget allowed for that month. |
A: Don’t worry, you didn’t break anything! In fact, it is
very important to note if a cell has turned red. It means that within that
category, you have spent more than what your budget allowed. You may need to
revisit your budget plan and make a few adjustments to that and other categories
to decrease the chances of another month of overspending. It would also be
advisable to review what your money was spent on within that category (ie.
review your budget balance for that month) to see if you could cut out any of
those expenses next month.
Q: Little green triangles keep showing up in the top right
corner of the cells under Description and Location on my balance sheets in
FamilyBudget_Balance2016.xlsx. What are they and why won’t they go away?
A: These triangles alert you that there is an error with
the contents of that cell. Blank entries or entries that are different from
those listed under
Frequently Used Descriptions and
Frequently Used Locations from the
“Start
Here” tab will appear as errors. You can ignore those. If you really
don’t want to see them, select the cell with the error, click the warning symbol
that appears to the left of the cell, and select
Ignore Error.
 |
| How to ignore error for non-frequently used description. |
Q: I have an Apple computer. Can I still make these files
work for me?
A: If you have Microsoft Office for Mac 2011 or 2016, I
assume everything should work fine. If you are only able to use the Mac
program, Numbers – comparable to Microsoft Excel – it
might be
compatible, but may not look the same. I’m not sure if all of the formulas or
other functions I used are available in Numbers. I do not have a Mac to test
this out on, so if any of you fellow readers have tried this out, please share
your experience with it!
Q: Can I delete the instructional text boxes in the
worksheets? They’re in my way.
A: Of course! Everything you need to know is on this page
for you to refer back to.
If you have a question about these budgeting worksheets that was not answered
above, please comment below!
Next week’s topic: [
The Christmas Tree Letters]
*This page will continue to be updated as needed.*
