🏆 Utilities Expense Tracker - Google Sheets

Google Sheets Show & Tell
1

Utilities, including electricity, natural gas and water/sewer, can make up a large part of your budget. It’s good to understand what they’re costing you, and why they cost that much. The Utilities template does a few things:

  • Provides a place to keep track of consumption/usage and cost of each service for up to five years
  • Shows in graph form your consumption and cost so you can compare over time
  • Shows the amount you’ve paid for each utility for up to five years based on past transactions
  • Shows in graph form your payments so you can compare over time
  • Allows you to generate budget amounts based on last years usage or usage over all your logged data

1
11149×745 89.5 KB

Installation

Copy the Utilities sheet from the demo template below to your Tiller Foundation template.

Setup

  1. Expand the Settings by clicking the + above column AB.

  2. Set each of the “Transactions Based On” to either “Category” or “Description”. This determines how the template will find your utility transactions on your Transactions sheet.

  3. In Transaction Categories, for each utility that you chose “Category” in step 2, choose which category from the drop down menus.

  4. In Transaction Descriptions, for each utility that you chose “Description” in step 2, enter the “Description” of the transaction (note that you’ll need to be sure all your transactions for this utility have the same description, a perfect job for AutoCAT!).

  5. In Budget Type, decide how you want to determine this years budget numbers:

    • Last Year: Each month of this year should be the same as each month of last year.
    • Last Year - AVG: Each month of this year should be the same as the average of all of last year.
    • Last Year - MAX: Each month of this year should be the same as the maximum amount from last year.
    • AVG Per Month: Each month of this year should be the same as the average of the same month over the past five years (or however many years you have data for).
    • MAX Per Month: Each month of this year should be the same as the maximum of the same month over the past five years (or however many years you have data for).

  6. In Budget Start, enter the first date of your budget period. For example, 1/1/2023.

4

Usage

Make changes only in the green cells, using past utility statements as a reference.

  • For each utility, enter the consumption for each month that you have data in the green cells in the leftmost section (Consumption). This can be useful so you can see how consumption varies over the year, as well as how it varies from year to year.
  • Enter the amount you paid in the green cells of the center section (Cost based on Invoices). This can be useful so you can see what each utility is costing you in the month where the usage occurred.
  • The right-most section (Cost based on Transactions) will be automatic, finding totals from your transactions based on the information you entered in the settings. This can be useful to see when the cost of your consumption will affect your budget.

Budgeting

Your budget information, based on the settings you entered, is displayed in the External References section (click the + above column AE to expand).

3
31223×249 23.3 KB

Budget Plan: To use your budget information in the Budget Plan template, create a new Budget Item in the Budget plan sheet, choose the proper category for the utility you’re setting up, set the frequency to “ExternalSource”, and in the Notes column, enter:

  • For Electricity: Utilities!$AF$4:$AQ$5
  • For Gas: Utilities!$AF$8:$AQ$9
  • For Water/Sewer: Utilities!$AF$12:$AQ$13

Categories: To use your budget information in the Categories template (only use if you are not using the Budget Plan template), for each utility category, use the following formula in the first budget period month (eg. Jan 2023):

  • For Electricity: =Utilities!AF$5
  • For Gas: =Utilities!AF$9
  • For Water/Sewer: =Utilities!AF$13

Once those are entered, grab the ‘fill handle’ in the lower right corner of each of those cells and drag to the right to copy the formula across the remaining months of the year.

Notes
You don’t necessarily have to manually enter the consumption and invoice costs, you could just use this template for the ability to better budget your utilities based on past transactions. You also don’t need to use all three. In my case, I track all three, but I only use this sheet to budget electricity and gas, since my water bill invoice date isn’t very reliable (it’s billed every three months, so I use Budget Plan to do a monthly frequency with a multiplier of 3 based on the maximum value that I manually enter from last year). If you run into any problems, or have ideas on how to improve this template, let me know! If there is interest, I’ll try to create an Excel version.

2

I have often wondered about the ebb and flow of utilities— winter heating, landscape watering, etc.
What a cool tool! :chart_with_downwards_trend:

Congratulations to @jpfieber for another clever and well-designed tool. I love this template’s mix of focus— utilities!— but also swiss-army :hocho: functionality around tracking, budgeting, charting, and forecasting. It’s also really impressive how Joseph has offered several ways to capture the relevant expenses.

I’m excited to award this template $300 from our Tiller Builder Rewards Program. Thanks for continuing to build and share great stuff, @jpfieber!

:trophy:

P.S. Sorry for the delay. How could I miss this for three weeks?

P.P.S. I renamed the topic “Utilities Expense Tracker” since “Utilities” sounded more like it was a generic set of tools and workflows. Feel free to change the topic to something better.

2 Likes
3

I would be very interested in an Excel version of this sheet!

1 Like
4

Thank you, I have integrated this spreadsheet into my Tiller book. I previously tracked energy usage on EPA energy star portfolio manager website and will work on a way to auto-populate.
Additional items to consider tracking on this sheet:
Average use change vs prior year- Gives an opportunity to consider how energy efficiency upgrades, remodels, work from home, or behaviour changes have impacted energy usage as a percentage.
Cost per KWh, therm, gallon, etc - Utility price increases are beyond our control, but as Europe knows, a large factor in budgeting.
Cost per day - Reveal the transparent costs of fine living.
Cost change - My largest motivator for change.

Use Change =(D17-C17)/C17 =(E17-D17)/D17 =(F17-E17)/E17 =(G17-F17)/F17
Cost per KW =L17/C17 =M17/D17 =N17/E17 =O17/F17 =P17/G17
Cost per Day =L17/365 =M17/365 =N17/365 =O17/365 =P17/365
Cost Change =(M17-L17)/L17 =(N17-M17)/M17 =(O17-N17)/N17 =(P17-O17)/O17
5

Finally installed this and set it up. Extremely helpful considering the variability in rates we can have here in Texas from contract to contract. I was also able to see where some duct work improvement work we had done actually showed up in my bill at a very rough savings of $100 per month. Significant to say the least.

One improvement I would make is to add a section that calculates your Energy Charge or rate per kwh you are paying (= Invoice Amount / Energy Usage). That may be more relevant to those of who live in a de-regulated utility market like Texas where we can shop rates. In the meantime, I’m just going to add that section myself.

2 Likes
6

Sounds interesting. I’d be interested to see what you come up with, and if you’re willing to share!

7

Thank you for this excellent template! I don’t have gas service but do have home heating oil, is there any risk in changing any references to “Gas” to “Oil”? Anything I need to look at behind the scenes?

Also, my template starts with 2019 and goes through 2023, is there a quick way to update this (perhaps start later or at least include 2024)?

1 Like
9

Also, we moved in 2023…so the Utility companies prior to 2023 are different. I lump Water, Gas, Electric under Utilities Category, so I have to do this by Description. Is there a way to include more than 1 description for each of the 3 types? Comma delimiter perhaps? Thanks.

1 Like
10

Just change the Budget Start date in M23 to 1/1/2024 and you’ll see 2024 included. It will remove the 2019 data column as it’s just meant to store up to 5 years of information, it appears.

1 Like
11

Ah, ok. Mine is in cell AD23, but I understand. Bummer about this is any data entered in the tables already is now in the wrong year, which would mean there would have to be some extra work to handle the end of the year/move into the next year.

12

Noticed that Columns U and V for the (Transaction) tables don’t include the full dataset for the totals (rows under December row). They are missing the December row for all 3 of those tables. A quick adjustment to include that row will make the data more accurate… e.g. U17 should be:

=sum(U5:U16)

13

Change the Budget Start date in AD23, which will change the column headings and the “Transaction” charts will automatically update. Unfortunately, all the manually entered data will need to be moved by hand. I did mine by selecting the 4 most recent years in the green table area, choose COPY, then click in the top January cell in that area, and chose “Paste Special/Values Only”. Then select the values for the most recent year and delete them. This process helps avoid formatting and charts getting messed up. Do that for each of the 6 green areas with manual data, which will open up a column for your 2024 data.

14

This is great! I already have separate categories set up for each utility so that was easy to set up. My locality has water and sewer separately, so I would love it if there was a way in a future release to add new utility monitoring beyond the three that are in there. Thanks!

2 Likes
15

I just discovered smartmetertexas dot com and wonder if anyone’s figured how to connect to that for kwh autopopulating

16

Does this automatically add a column for a new year once new year’s hits? For example once it’s 2025 will the sheet automatically show a column for 2025 or do I have to add this?

17

No, you’ll have to rearrange stuff manually (delete oldest year then copy/paste other years over one).

18

Would be great to get this automatically built in, as the end of the year approaches…

19

I am also tracking four utilities, is there an easy way to adjust this template?