Family Budget Spreadsheets FAQ

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:

1. FamilyBudget_Balance2016.xlsx: go to the “Start Here” tab.
   1. Modify, replace, or add new categories that fit your family’s needs.
   2. Modify, replace, or add new accounts that your family uses.
   3. Modify, replace, or add new transaction descriptions that you know you will be likely to use frequently over the year.
   4. Modify, replace, or add new locations that you know you will be likely to use frequently over the year.
   5. Enter the starting balances for each account.
2. FamilyBudget_Plan2016.xlsx: go to the “Budget” tab. (Keep other file open.)
1. Copy category items related to income (eg. “Income (Gifts)”) from FamilyBudget_Balance2016.xlsx to cell A7 on “Budget” tab of FamilyBudget_Plan2016.xlsx.
2. 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.
3. 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:

1. Select the bottom right cell.
2. 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:

1. Select any cell in the first row that passes the date of the transaction you missed.
2. Right click the selected cell and select Insert --> Table Rows Above. 
3. 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.
4. 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.*