Decrease your costs and carbon footprint by efficiently tracking your utilities expenses with this Utilities Expense Tool. This utility bill template in excel is intended to support people/business’ goals of having a better track of their Utilities Expenses and to better understand steps that can be done to improve the bottom-line.
The free solutions comprises of 4 key segments: Guide, Dashboard, Dataset, Factors.
Technical: The green cells in I2 and O3 are designed to be changed manually. It helps to determine the amount of savings made within 10 years based on expenditure cuts and annual interest compound. The formula is:
10 year Return on Investment (ROI) = (Expenditure * Expected % saved) * (1 + Annual Compound Interest) ^ (10 years)
Visual Basic Programming Language.
7. Expenditure by Category – Provides a % breakdown of all the relevant categories to better understand where the expenses are going to.
In this example, only the building is filtered into ‘House 1’, now if there is an interest in understanding the costs associated with category “Technology”, it is possible to click the filter accordingly. In this case, it is now possible to see that within Technology, 23% is spent on Wireless, 17% on Home Phone, 20% on Internet, 26% on TV & 14% on Smart Phone in the past year and a half. By cutting the ‘Home phone’, it would be possible to save around $1,315.08 in the future (1.5 years).
8. By Month – Category Expenditure
The graph to the right provides insight on changes to Expenditure per category on a month to month basis. It helps to visualize (un)planned changes to expenditure, helping the user to decide whether there is an action that should be taken to resolve the matter.
9. Expenditure by Building & Year – Provides an overview of the expenses incurred per year per building. This graph can help users notice major changes to expenditure on a year-to-year basis, which may have different implications to the owner of the building. It is worth noting that if there is not enough data for a certain year, the information may be skewed.
10. Year-over-Year Expenditure Comparison – Compares the expenditures in a month to the expenditures that occurred 12 months prior (or 24 months, etc). It helps the user to dig into cyclical changes that may occur throughout the year to help understand whether there has been a discrepancy. For example, the use of electricity may increase throughout the winter and summer periods due to increased use of heaters and air conditioners.
11. Green House Gas Emission – US Government website to help users understand their Green House Emissions. This can be useful for individuals/groups interested in understanding their GHG emission and to help consider potential energy efficient solutions.
12. Top of the Dashboard – A clickable button used to facilitate going to the top of the ‘Dashboard’ tab.
13. The ‘Dashboard button’ leads back to the ‘Dashboard’ tab.
14. Slicers – Serves to quickly filter all the data within the Dataset, helping to dig into the data that matters at the time.
Building – The building to which the rest of the data will be tied to.
Year – A drop-down for the year in relation to expenditures.
Month – A drop-down for the month associated with the expenditure.
Category – Systematic division of items such as Electricity, Water, Sewage, Waste, Technology, etc.
Sub-Category – Subdivision of items listed under category or to facilitate definition of categories.
Amount – Amount to be paid for the expenses incurred.
Due Date – Date in which an expense has to be paid [make sure to input YEAR/MONTH/DAY].
Status – Serves to keep track of whether an expenditure has been paid or not. It is a drop-down where 1=Paid, 0=In-Progress, -1=Behind and N/A = Not Applicable.
Days Until Due Date – An auto-populated formula based on the ‘Status’ and ‘Due Date’ columns.
Technical: If (Status = 1 or N/A, then leave it blank), otherwise if status is Blank, then let the user know that the “Status is Unavailable”. If none of the points raised before have occurred, then take the Due Date minus Today’s Date.
Factors Impacting the Cost of Utilities – Refer to over 30 factors that may be impacting the cost of utilities.
Slicers – Serves to quickly filter all the data within the ‘Factors Impacting the Cost of Utilities’, helping to dig into the data that matters at the time.
Q: Is there any macros on the excel file?
A: No, although macros can be extremely useful (Auto-refresh, automatization of processes, etc) it can also be challenging to some users and it can be considered to have a higher degree of risk to some users. Therefore, the file was created with simplicity and safety to any user.
Q: I have added data to the ‘Dataset’ but it doesn’t seem to be updating the dashboard, am I doing something wrong?
A: If you are having difficulties with this, open the ‘Data’ tab (top ribbon) and press “Refresh All”.
Q: Is this the final version of the Utilities Report?
A: The utilities tool provided is a simplified version intended for users of all Excel level. To request for an advanced solution, please reach out to the GPetrium team with your inquiry.
Q: Can the report be used for other things?
A: The report has enough flexibility for it to be used for revenue control, revenue/expense, and individual/business/government among other uses.
Q: After I changed file name, the pivots seem to be connecting to the previous version.
A: This is a known issue in some Excel versions [Refer to Stackoverflow]. The simplest solution is to leave the file name As-Is.