Blair Bristow

X

The Account Summary

Your Account Summary should be formatted as a table with 4 columns, Account, BOM (Beginning of Month), EOM (End of Month), and Change. In this example we have 3 different accounts with some filler values for BOM and a formula in the Change column that subtracts EOM from BOM. I have also added a Total row at the bottom which is a SUM() formula for each column.

For now, BOM will remain empty. We will come back to this in a later tab. As you can see I also added light formatting by making all cells that contain numbers into dollar values which I will continue to do.

X

The Budget Build

Your first step to making a budget is putting down the amount of money you expect to bring in each month. Whether this is consistent or not, I recommend starting with the amount brought in last month for ease of use.

Next, we will list out our expenses and their amounts. These amounts can either be fixed, such as monthly rent or mortgage, or they can be variable, like grocery spending. You can have as many or as few categories as you would like. I have 8 in this example as you can see on the right, but this guide will work no matter how many categories you have.

• Tip: Enter your amount estimates as negative numbers so that your remainder calculation is a SUM() formula for the whole column.

X

The Funds Tracker

This is the heart of the budget sheet, where you put in all the money you bring in and all of the money you spend each month. To do this I recommend using a two table structure, one for cash inflows and one for outflows. In this example below, you can see each table has four columns:

• Date
• Amount
• Categoty
• Account

X

Putting it Together

Now we have a perfectly fine layout, but much like a web page with HTML and no JavaScript, it doesn't really do much. Sure we can fill out the Funds Tracker for the whole month perfectly, but our Account Summary wouldn’t be altered and we would have no idea if we kept to our budget.

To make this a working sheet, we need to add formulas to the Account Summary and Budget sections. On the face of it, you may think that since the Budget section has 8 categories and the Account Summary has 3 sections that we will need to make 11 formulas. However, thanks to the power of computers we only need 3!

A quick note before we dive in, Google Sheets is cell-based for it’s formulas so if your sheet does not look exactly like the example then you may have to adjust your cell identifiers.

Formula for the Account Summary

For the account summary, we need a formula that can tell when we take money out or add money to an account based on the Funds Tracker to fill in the EOM column. I have added the below transactions to the Funds tracker.

The main element of our formula will be SUMIF(). To learn the ins and out of this formula, follow this LINK, but since this is a budget tutorial, not a Google Sheets one, you can copy and paste the following into the EOM-Checking cell:

G2+ sumif($I$10:$I$1000,F2, $G$10:$G$1000)- sumif($N$10:$N$1000,F2, $L$10:$L$1000)

Then, since the formula uses ABSOLUTE REFERENCES we can drag it down into the two remaining cells. If all goes well this should be your result!

Formula for the Budget

This formula is just a slight modification of the one from before. All we need to do is remove the first cell reference and then shift from looking at the account the transaction would affect to looking at the category. Paste this formula into cell C1:

sumif($H$10:$H$1000,A1, $G$10:$G$1000)-sumif($M$10:$M$1000,A1, $L$10:$L$1000)

Then dragging down should give you this result when you also drag over the remainder formula!

The Investment category will be an exception because it is also an account. Use this formula for cell C12 and then your final result will now be correct!

=-sumif($H$10:$H$1000,A12, $G$10:$G$1000)

X

X

Define The Priorities

My first step when I’m styling is to identify which elements are the most important for myself or whoever I am making the sheet for. In our example we built a sheet with 3 sections and undoubtedly the end client has a ranking of importance for them. Determining that order of importance is key to properly direct the creative process.

For my own budget sheet, my ranking of importance is as follows: the change from BOM to EOM for each of my accounts, whether or not I kept within budget on the individual categories in my budget, and finally the organization of the funds tracker.

X

Showing Importance

Now that we have our order of importance, we need to determine how best to demonstrate that importance through style and creative direction. For a personal creation, this would be up to ourselves but for a product we would have to consider the clients point of view.

For a Budget Sheet, I view fully highlighted cells as the best identifier so that is what I will use to show if the BOM to EOM change in each of my accounts is positive or negative. The best way to accompish this with Google Sheets is with CONDITIONAL FORMATTING. This can be simply explained as a way to provide Google Sheets with styling rules that it will automatically apply to different cells. The video below provides an excellent breakdown on the topic as well.

X

Making Style Intuitive

As we start formatting, we need to consider INTUITIVE DESIGN. When we define rules for what a positive change will be highlighted in and what a negative change will be highlighted in we will need to think about the color. In theory we could use any two colors, such as purple for a positive change and gold for a negative change, but that's unnecessarily confusing to a third party. Even when creating something just for yourself, I recommend using intuitive design and existing conventions because it is both good practice and easier to understand, even if subconsciously.

As you can see in the image, I ultimately decided to use green for positive changes and red for negative changes. This keeps the style simple and easy to understand by relying on existing conventions.

X

Showing Secondary

As a reminder, my second priority for the budget sheet was seeing if I kept within each budget category. Since this is of a lower importance, I do not want to use the same style, highlights, as I did for the Account Summary so instead I will be using text color. From an artistic perspective we could choose any color once again, however that would just add another new element for the user to understand. In my creations I appreciate simplicity and consistency; since we are doing the same thing here as before, checking if one number is greater or less than another, I believe we should use the same green and red color scheme. In this case green would represent that we have spent less than we budgeted and red would represent that we spent more.

This makes our sheet easy to read while increasing its utility. At a glance anyone can now see our Budget and Account Summary status and since they both use the same color rule there is little chance of a communication failure.

X

Accepting Unimportance

Finally we get to the Funds Tracker. Although this is very important for the budget sheet to function, I don’t really like or see value in looking at individual transactions so anything that draws the eye wouldn’t be a good idea. Therfore, I will be leaving that section as is. See the completed form HERE.

This is an important step; recognizing elements of our creation that do not need to be prominetly displayed. Even if an element is important for the overall tool to function, since the end user, me, does not see value in it from a display standpoint, it is best to not insist on a style or format that draws the eye. Instead, you should focus on making that element as cleanly displayed as possible. This will allow it to keep its function and useability while not having it contend for attention with more important elements.