Product Tutorial: Monthly Budget StencilBook

This blog post describes how to set up and use the Monthly Budget template, how to customize it to fit your needs, and how to troubleshoot if anything gets wonky. Haven’t purchased the template yet? You still may enjoy reviewing this tutorial. But really, what are you waiting for? Go pick it up on our Products page!

April 29, 2020 · 16 min read

Congratulations on your purchase of the StencilBook for Monthly Budgeting! We are so excited you took this step to get your finances organized. Let’s dive in!


Open the StencilBook

After you’ve downloaded the StencilBook (aka spreadsheet template), you can go ahead and save it somewhere safe, then open it up. When you open the file, you might get a warning saying:

This workbook contains macros. Do you want to disable macros before opening the file?

Click Enable Macros

By the way
  • Why does this exist? Because we put a few lines of code together to automate the spreadsheet and make it prettier for you. If you don’t want Macros, you can still use this workbook, just some of the buttons won’t work and you’ll have to do some manual steps.
  • Did you already move past this step without clicking the right button? No biggie, just close it and re-open. You’ll get this pop up each time you open the workbook.

Basic Set Up

When you open the spreadsheet, you should be on the Start Here tab, but if not, go there now. The following instructions correspond to the text identified in the spreadsheet:

Step 1:

Enter the year that you’d like to track the budget on the dotted line.

Step 2:

Use the option buttons to pick the structure of your budget categories.

  • If you only have a handful of categories for expenses, use the Simplified option
  • If you have many categories for expenses, use the Expanded option

You’ll see when you click between these buttons, you’ll first get a pop-up warning you before going forward. The purpose of this warning is to let you know that if you’ve already entered data in other cells, it may be overwritten by changing these options. We’ll come back to this later, but for now you can feel free to switch back and forth. If you click OK after the warning, certain rows will be hidden or unhidden.

Step 3:

Enter the categories you’d like to use to organize your budget on the dotted line(s).

If you chose Simplified in the previous step, you’ll only see one line. You’ll only be able to enter one category name for income and up to twelve category names for expenses.

If you chose Expanded in the previous step, you’ll see multiple lines. In the top line, you can create a primary category for income and expenses. In the following lines, you can add many secondary categories.

Step 4:

Now it’s time to add the transactions so click on the Transactions tab.

You can download your transactions from your bank and/or credit card so you don’t have to write down each item manually. You may have to do a bit of editing on the file you bank provides to ensure the columns match up with the Transactions columns. The first three columns must be Date, Description, and Amount. If you use cash, be sure to add those transactions as well (be sure to save your receipts so you have accurate records).

Let’s talk more about cleaning the transaction data.

If your estimates are already in tip-top shape or you’re entering it all manually, feel free to skip to Step 5.

All amounts must be entered as positive values because that is what will make the calculations work accurately. You can use the absolute value feature abs() to make all numbers positive as shown in the example below.

Why positive? We found that every bank institution does something different (income is negative, expense is positive, or visa versa) so we’re sticking with a blanket statement here - keep it all positive.

Another item you’ll want to keep in mind all the accounts you’re tracking and how they relate. For example, if you want to look at everywhere you spend money and use a credit card for some transactions, then pay off that credit card with a debit card, you don’t want to count those transactions twice. For this reason, you may opt to delete line items for both the payment going out to pay the credit card (shown on the debit card’s transaction list) and the payment coming in to pay off the credit card (shown on the credit card’s transaction list). Its like an internal transfer - no new money is being made or spent so there is no reason to count it here.

An exception to this is if you want to file all your credit card transactions under one category without looking at specific transactions (like maybe you only use the credit card for groceries), then you’ll want to keep the line item for paying off the credit card in your overall transactions so that money is accounted for.

Long story short:
  1. Match the column headings in the Transactions tab
  2. Keep all transactions positive
  3. Delete internal transfers

Step 5:

Ok, let’s move to the Template tab and get that set up! For now, we can ignore the top summary section - you may see some errors (e.g. #VALUE!) but that is to be expected.

Under the Income section, in the cells with the dotted line(s) add your estimate(s) for the income you expect on a given month.

Step 6:

Sticking with the Template tab, under the Expenses section go through each category (and subcategory, if applicable) and enter your estimates for the expenses you expect on a given month on the dotted lines.

Step 7:

Once you’ve got your template filled in with estimates (don’t worry, you can totally make changes later), it’s time to prepare the template for a month of budgeting. To do this, you’ll make a copy of the Template tab. Right click on the tab that says Template. Click Move or Copy. In the menu that pops up, check the box for Create a copy and then click OK.

You’ll see a new tab appear that says Template(2). Now, right click on that tab and click Rename (alternatively, you can also double-click on the tab name and it will allow you to edit it). Now you’ll be allowed to type in whatever you want. For everything to work correctly, enter the name of a month you wish to begin budgeting. Make sure you spell it correctly and no abbreviations (Reminder that this is how you spell February).

By the way

If you choose to be a rebel and not use the entire month name or spell it incorrectly, the template’s formulas won’t work quite right. Please make sure to use the full month name.

Once you have done this (and have entered data for the same month in the Transactions tab) you’ll see that the Actual and Difference data has been populated. The summary at the top of the page and Overview graphs at the bottom of the page should all be updated. Feel free to re-order the tabs by dragging and dropping (click and hold) them as necessary. Each copy of the Template w/ the month name, I’ll refer to as monthly budget sheet going forward.

Now your budget is all set up! Check out the Summary tab for different visualizations of your data. You can pick different date ranges, view only one category (or subcategory if applicable), or a combination. Feel free to play and learn about how your money comes in and out. As a reminder, the Summary tab only shows actual numbers (from the Transaction tab).

Monthly maintenance:

When you make updates prior to the start of a month or on another cadence, here’s a summary of what you’ll have to do:

  • Update your transactions using notes from Step 4.
  • Make a copy of the template using notes from Step 7. If you changed any of your estimates after you had copied it into a new month, you could make a copy of that tab, instead of copying the Template tab.

Preferences

Here are some ways you can customize the StencilBook template to meet your needs.

Changing the color or font

You can make changes to the look and feel of the entire template file by changing the Theme. First, you’ll need to unprotect each sheet. Next, in the Excel ribbon, click on the Page Layout tab, then you can click Theme to change both the font and color scheme, or choose them independently using the Colors and Fonts buttons to the right. Don’t forget to re-protect each sheet

Unprotect the sheet

When a spreadsheet is protected, it means that all or certain parts of it can’t be edited (unless you unprotect it). Most of this template has been protected to ensure you don’t change anything that doesn’t need to be changed, but you do have freedom to change specific things to use the template appropriately. The option to Protect is done on an individual sheet (aka tab) basis. If you look at the sheet name on the bottom left, you’ll see a little lock symbol next to the name showing that it is protected. Or maybe you got a pop-up when you tried to do something that said:

Alert: The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password

That’s because the sheet is protected.

To unprotect a sheet, check out the Excel ribbon at the top of the screen. Click the Review tab, then click Unprotect Sheet.That’s it! Now you’re free and clear to change anything on that sheet.We’d recommend you re-protect the sheet once you’ve made the changes you wanted by going back to the Review tab in the ribbon and clicking Protect Sheet (not to be confused with the Protect Workbook button - that’s totally different unfortunately).

Switching from simplified to expanded

So you started your budget using the simplified budget structure and now you want to switch to expanded? Great! Here’s what will happen when you toggle that button from simplified to expanded on the Start Here tab:

  • On the Start Here tab, new rows are revealed for you to add subcategories to your existing categories

    • You don’t have to add subcategories for each category, but each list of subcategories must have the name of a category above it for the spreadsheet to work properly
  • On the Transactions tab, a column is revealed for you to add subcategories to each subcategory for each line of the transaction

    • If you don’t add subcategories to each transaction row, those transactions will still be pulled into and added to the monthly budget sheets, but their values won’t be visible in the subcategory listing on the monthly budget sheets. So, for transparency, it’s probably best to go back and add subcategories to each transaction.
  • On the Template tab, new rows are revealed under each category table

    • This is where the warning box comes in! In the Estimate columns for income and expenses, the cell (aka box where you enter numbers) goes from being empty to being a formula that adds up the values of the subcategories. This means that the numbers you used to have in the estimates will be overwritten, so that you can add the more detailed estimates to each subcategory.
    • Want to hang on to the estimates you previously entered? Jot them down on a piece of paper or maybe make a copy of your old spreadsheet to refer back to.
    • Keep in mind, this change is only made on the Template tab. If you have made copies for each individual month, those will not be edited. You could revise previous months by making new copies of the Template, but you may have different estimates for things in the past compared to now and that won’t be carried through. Just something to keep in mind.
  • On the Summary tab, after you click the refresh button, You’ll see the subcategories you can now filter in/out to view on the table and graph. Feel free to play around!

Switching from expanded to simplified

So you started your budget using the expanded budget structure and now you want to switch to simplified? Great! Here’s what will happen when you toggle that button from expanded to simplified on the Start Here tab:

  • On the Start Here tab, rows of subcategories are hidden, so the only row visible is the existing categories

    • You don’t have to delete the values before switching to simplified, but you could. If you ever want to view the subcategories again, just ungroup them using the +button to the right of the row numbers.
  • On the Transactions tab, the column for subcategories is hidden, and the categories column remains

    • Again, you don’t have to delete the subcategories before switching to simplified. But if you do, it will clean up the Summary tab down the road.
  • On the Template tab, new rows are revealed under each category table

    • This is where the warning box comes in! In the Estimate columns for income and expenses, the cells (aka box where you enter numbers) go from being a formula that adds up the values of the subcategories, to a blank cell. You’ll have to manually enter the values for estimates of each category.
    • Want to hang on to the estimates you previously entered in the subcategories? Those numbers are still there if you ungroup them using the - button to the right of the row numbers. You’ll just have to manually add them or replace them completely in the category row.
    • Keep in mind, this change is only made on the Template tab. If you have made copies for each individual month, those will not be edited. You could revise previous months by making new copies of the Template, but you may have different estimates for things in the past compared to now and that won’t be carried through. Just something to keep in mind.
  • On the Summary tab, after you click the refresh button, if you didn’t remove the subcategory values before you switched to simplified, you’ll still see them here but you can filter them out if desired.

Grouping and ungrouping data

By default, certain rows are hidden or unhidden based on if you chose the Simplified or Expanded category structure when you set up the template. If you want to show or hide these rows to suit your preference, you can use the Group and Ungroup buttons. You may have noticed the gray plus and minus signs to the left of the row numbers. You can click those to show (+) or hide (-) detail. You can also highlight the rows in a group (denoted by the gray bar spanning the rows) and go to the Data tab on the Excel ribbon, then click Show Detail or Hide Detail.

Don’t want to enter individual transaction data?

Maybe you just want a quick and dirty budget, maybe your bank of choice already groups your transactions into categories - no problem. I’d still recommend you place that info directly into the Transactions tab so all the formulas still work (otherwise you’ll have to unprotect some sheets, overwrite some formulas, and the summary graphs won’t work). If you do this, just ensure you add a date with the correct month and you don’t necessarily need to fill in the Description column. Once you identify the category and subcategory associated with each amount, you’re off! Still follow Steps 5, 6, and 7.


FAQ / Troubleshooting

Run into something wonky? Check out these questions first.

What happens if you rename the categories after using them?

Categories and/or subcategories you entered on the Transactions tab will NOT be automatically updated if you change a category and/or subcategory name. The Template tab will be updated to reflect the name change. Previously created monthly budget sheets will NOT be updated.

It is recommended that you always add categories and/or subcategories instead of trying to make changes, unless you are willing to revise previous entries. If you run out of categories and/or subcategories, email Stencilbook and we can help you out.

How to deal with ####?

This text will show up in a cell and it looks scary, but all it means is that the width of the cell isn’t big enough to show all the digits that are supposed to be visible. Simply make the column width larger by dragging and dropping the width. You can also highlight the column, right click on it, click Column width, and enter a new number for the column width.

How to enable macros?

The easiest way is to save your work, close the document, re-open it, and click Enable Macros.

You can also enable/disable all macros in the Preferences (Mac) or Excel Options (PC) menu, but that gives you very all-or-nothing options so we’d recommend doing it on a per-file basis using the warning when your opening the file.

You may get any of the following errors if you didn’t enable macros when you opened the workbook:

Alert: The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password

Alert: Cannot run the macro ‘MonthlyBudget.xlsm!UseSimplifiedMode’. The macro may not be available in this workbook or all macros may be disabled.

Alert: Cannot run the macro ‘MonthlyBudget.xlsm!UseExpandedMode’. The macro may not be available in this workbook or all macros may be disabled.

Alert: Cannot run the macro ‘MonthlyBudget.xlsm!RefreshWorksheetPivots’. The macro may not be available in this workbook or all macros may be disabled.

What updates have been made?

The notes for any updates that have been made to this product can be found in the Change Log.

Anything else?

Send an email to help@stencilbook.com