Utilities - Google Sheets

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

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).

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.