Google Budget Sheets


Too Long; Won't Read


Updated 7/19/2017 - Added Auto Subtotaling (analysis further below)

Here is a Google sheet that makes my life easier and gives me quick, dirty metrics on where I'm spending my budget. It compares monthly and yearly spending and also provides subtotals for monthly credit card processes. Feel free to steal copy* it for your own purposes, business or pleasure. 

If you want to geek out over my formulas, keep reading. Otherwise enjoy the fruits of my labor.


History
When I began employment at my school in 2015, I was provided the remaining budget for the year (with a month left), a quick rundown on how accounting wanted things, and 25 budget codes (for a school this size, it's a bit much).

Despite that many codes, I wasn't able to track spending in meaningful ways and trying to use the financial/accounting system to  that end is akin to taking a sledgehammer to a nail. Like so many other things in life, a new project birthed with the thought "there must be a better way."


After several iterations of Microsoft Excel, a migration to Google, and an evening of work with our Theatre's Technical Director (also looking for meaningful metrics), I churned out a Google Sheet saving me time AND providing the desired metrics.

The Goal

I had two primary objectives with my sheets... 1) output my monthly stats to a spreadsheet like this:


2) Use a 'tags' inspired system to track the things I care about.


If you've spent any time with most blogging platforms, YouTube, web sites, etc then you'll may see where I'm going with this. Basically, give something a tag, and then sum up totals for that tag. Quick example, I buy HDMI, Apple Lightning, and Micro USB cables. So I give each of those purchases a tag of "Cables" and I can see how much I'm spending on cables. Simple, yes? Well...



The Initial Setup

We start off simple at any rate. I have my sheet created so I can fill out months, budget codes, purchase costs, purchase descriptions, tags, payment type (important for reasons revealed later), and then there's a total in the upper right, but it's just for my budget. I occasionally buy things for other departments, but it comes out of their budget with their codes, so I don't want to tally them up. So a quick view of that formula:

=SUMIFS(C3:C,B3:B, "Hardware*")+SUMIFS(C3:C,B3:B, "Printing*")+SUMIFS(C3:C,B3:B, "Repair*")+SUMIFS(C3:C,B3:B, "Software*")+SUMIFS(C3:C,B3:B, "Cats*")


Using the SUMIFS function, only the values of column C are summed if the value of column B contains the specified word. In the case of my actual budget codes, I only have three major codes and numerous sub-codes. The asterisk denotes that anything after "Hardware" is kosher so long as the word 'Hardware' is there. I added as many SUMIFS statements as I do different budgets that belong to me. In this example the only code I'm not including is "Internet".


I name this sheet by year as I intend to be able to compare purchasing trends from year to year.





You may notice all of the downward arrows. Those allow for for some dropdown, point-and-click functionality. I like to remove as much room for error by using data validation; not only will it tell me when something is wrong, the dropdowns protect me from fatfingered typos. 


All of my dropdown validation pulls from the columns of the Dropdowns sheet. Having the validation point to a column (as opposed to a group of cells) allows me to add more items later for my dropdowns. (for example: adding a new budget code later, God forbid). 


With that covered, let's go look at the beginning of my first requirement, the Subtotal sheet.

Subtotal Sheet

I have that credit card form shown above included in a sheet called Credit Card Accounting, but how to fill it out quickly? Excel has a subtotal feature that worked well, and Google has a SUBTOTAL function, but I wasn't a big fan of how it worked. 

As a result I just engineered my own sheet. The way the Subtotal is setup is I select the year, month, payment type, and budget code and it sums up all things that match. I leave all my budget codes and change the month/year as needed. Let's take a look at my formulas in the Subtotals!G:G column: 


=sum(filter(INDIRECT(CHAR(39)&A2&CHAR(39)&"!"&"C"&":"&"C"), (REGEXMATCH(INDIRECT(CHAR(39)&A2&CHAR(39)&"!"&"B"&":"&"B"), CONCATENATE(D2))), (REGEXMATCH(INDIRECT(CHAR(39)&A2&CHAR(39)&"!"&"A"&":"&"A"), CONCATENATE(B2))), (REGEXMATCH(INDIRECT(CHAR(39)&A2&CHAR(39)&"!"&"F"&":"&"F"), CONCATENATE(C2)))))


This looks a little scary, but relax, I got you covered! We start off with a basic SUM to add things up and that leads into FILTER narrowing down our results to what we want. The first set of filtered parenthesis is our column to sum, in this case 2017!C:C. Each comma separated set of parenthesis after that is telling our formula to only sum cells of 2017!C:C if they match the cells of columns B through D on the Subtotal sheet.


Next is REGEXMATCH. We are using a regular expression function to search for values in the columns 2017 sheet and matching them to the CONCATENATEd values of cells in the Subtotal sheet.



The biggest reason this formula looks whack is the INDIRECT and CHAR functions. It's a convoluted workaround for calling another sheet based on cell values via REGEXMATCH. INDIRECT takes our cell reference and returns it as a string and CHAR inserts specific characters into the mix. In this case CHAR(39) is returning a single quote character (the numbers of CHAR's parenthesis are based on ASCII, in case you're wondering).


The reason we use CHAR(39) as opposed to typing single quotes directly into the formula is REGEXMATCH will treat them as part of a string of characters instead of an operator. (I might have that backwards; I consider myself more a dabbling wizard as opposed to an expert when it comes to Google Sheets formula magic).


What you are really looking at is REGEXMATCH's way of understanding "2017!C:C" (or 2017!B:B, 2017!A:A, etc). Don't look at me, I didn't make the rules, I just work here.


To recap... for every row we are summing the results of specific budget codes, made by credit cards, on any given month of any given year. Their totals (Subtotal!G1) should match the balance of my credit card.


On that note, the reason I choose to match on payment method is because we occasionally have to pay for things by checks (I know, that's so 1st Century BCE). While I still need to know what I've spent in my overall budget in the 2017 sheet, I don't want them mucking up my subtotals, which are specific to my monthly credit card balancing act. Speaking of...

Credit Card Accounting

So now we understand how Subtotal sheet works... whereto? I copy them to my Credit Card Accounting sheet to give my business office! I could copy/paste each row as needed (some budget codes don't get used every month), but I'm lazy. And outside of this sample sheet I have 25 budget codes. So I just filter Subtotal:G:G by any results less than 0. That pretty much removes all those #N/A entries so I can copy/paste just once. 

Quick note on paste; use "Paste special > paste values only" otherwise it's trying to copy formulas and not formula results. 




You may notice I have Budget Code and Account Name separated. This is just so I could adhere to the way the business office has it formatted on their sheet. In actuality it doesn't matter much. Since I used another function in Subtotal!E:E (SPLIT) I figured I cover that real quick:


=split(D2,":")


Pretty simple, the formula grabs the values in Subtotals!D:D and uses the colon as a delimiter. Just note that you have to have an empty column to the right of E:E to have a place for the all the account numbers after the colon to go.


So, now on to the fun stuff; the metrics.

Yearly and Monthly Analysis through Tags

We'll use my cables example mentioned above. Here's a filtered screen grab of my 2017 sheet. 


What we can expect to see if we fill out our Yearly Analysis sheet is $150 so far for the year. Our example sheet only contains 2017, but you could easily duplicate 2017 sheet, rename it '2018', add '2018' to Dropdowns and off to the races you go. On to the cables tag.


Off the bat there's an issue... It's only showing $100 when it should be $150... The short of it is that our tags are case sensitive. If you notice the example above, one of my tags is "cables" and not "Cables". Rename 2017!E15 to 'Cables' and problem fixed. Now let's look at the formula in Yearly Analysis!D:D.

=sum(filter(INDIRECT(CHAR(39)&A12&CHAR(39)&"!"&"C"&":"&"C"), (REGEXMATCH(INDIRECT(CHAR(39)&A12&CHAR(39)&"!"&"E"&":"&"E"), CONCATENATE(B12)))))

If you read through the Subtotals section above you'll notice this is pretty similar as before, so I won't go into those detail again. The important thing to note here is the REGEXMATCH function. Regular expressions very much care about case sensitivity.

Unless I figure out another method for referencing other sheets based on cell values, it's just a limitation to live with. There is a REGEXREPLACE function, but it's more meant for specific letters and is more trouble than it's worth for this situation.

Good news though, REGEXMATCH looks for ANYTHING that matches which has a couple implications. The first is partial matching; so lets say I use "Printing" for a tag and then query "Print" - that would return results! However if I had another tag called "Printed" it would also return those results; so I have to be very distinct with my tags.

The second implication is far more useful; we can give any particular item multiple tags. For example, I have many items that are annual purchases, and some of those items are software purchases. So 2017!E11 lists "Adobe Software Annual". If I use "Adobe", "Software", or "Annual" in the analysis sheets, it will include 2017:E11 in the sum total. Pretty nice for reporting.

Also pretty nifty, you could query "Software Annual" and that would pull results in the example above... However! REGEXMATCH is looking for matching patterns meaning that "Annual Software" would not return results. If you intend to use multiple tags this way, consistency will be key. 

Monthly Analysis sheet takes Yearly Analysis a little further and filters down the month cells from our 2017 sheet. I like using it to track how much we are spending on toner each month. As you might expect of a school, our toner goes up in the beginning of the year as well as at the end of each quarter/semester. Fun side note: if you leave month blank, it will return results in the same way Yearly Analysis does.


Auto Subtotal

After messing around with some other functions, I figured out how auto pull from the Subtotal sheet so the user doesn't have filter or copy/paste anything to the Credit Card Accounting sheet. Once a month during card reconciliation, update the month (or year if needed) in the Subtotals sheet and Credit Card Accounting is ready to print:

=FILTER(Subtotal!E2:G7,NOT(ISERROR(Subtotal!G2:G7)))

Essentially, it says to filter the three columns of the Subtotal sheet, but ONLY if the cells don't contain an error ("#N/A"). Basically, any budget code that doesn't get used for the month won't produce a value, hence the error.

The one issue with depends on how many budget codes you may or use in any given month. In this example sheet I only have 6, but in my job's budget sheet, I have over 40! In any given month I don't use more than maybe 10 so I just pick 15 for good measure and called it a day.

So the actual issue here is how the filter function operates. If the out put of the function would overwrite existing data in a cell, it produces an error instead. In this case, the Total in the Credit Card Accounting sheet exists below the filter function (which pushes data downward in this case). 

One way to adjust for this would be to just move the "Total" row to being above the account names. A little unusual for a total to output above the results, but not the end of the world. But since most of my constituents only manage about 10 codes at best I'm just going to account for that with enough space.

Future Plans

It feels pretty functional at this point but I've got a few planned additions to these forms if I ever get a wild hair.
  • turn tags into form validation dropdowns
    • in the interest of removing user error, it'd be nice to pull out the unique values from the tags and list them in one of the dropdown columns; this should highlight any misspelled words and then allow us to use them dropdown style on the analysis page
  • automagically pull results in the subtotals page to the credit card accounting page
    • basically something that only shows results if it is less than zero or not including #N/A results
  • maybe include some predictive math formulas in there for business managers purposes?
    • the stats I run tend to be averaging previous years/months or looking at current year/months, but it would be interesting to start doing some advanced analysis with the results

*You are free to use my shared sheet for personal or business uses. I ask that you do not sell it as it is my work and, if used publicly, please credit my work by email address via james.huval@gmail.com.

Comments

Popular Posts