Utilities such as electricity, natural gas, and water/sewer services can account for a significant portion of your household budget.
Understanding your utility costs and usage is important. The Utilities template assists with utility tracking and budgeting by:
- Providing space to record consumption, usage amounts, and costs for each utility service over a period of up to 5 years
- Graphing consumption and cost data over time for comparison
- Calculating total payment amounts for each utility based on past transactions from up to 5 years
- Graphing payment amounts over time for easy visualization of spending trends
- Generating budget estimates based on either the previous year’s usage or full usage history that has been logged
Installation
This template can be installed into Google Sheets with the Tiller Community Solutions add-on for Google Sheets.
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.
Questions and Feedback
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.
Share your questions and feedback here in the Tiller Community.
Setup
- Expand the Settings by clicking the + above column AB.
- 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.
- In Transaction Categories, for each utility that you chose “Category” in step 2, choose which category from the drop down menus.
- 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!).
- 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).
- In Budget Start, enter the first date of your budget period. For example, 1/1/2023.
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 17 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.
