RASCI Template
September 21, 2021
RASCI Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp The RASCI matrix is a visual business tool used to define the connection between members and their
Utilities Excel Tracker
Brief
- Free Utility Bill Template used to guide decisions related to utilities expenses allowing for an optimized cost structure & carbon footprint.
- Steps to understand the 4 different segments of the report tool: guide, dashboard, dataset, factors.
- An example of how the tool can be used to help support users.
- Frequently Asked Questions.
Download your Utilities Excel Tracker
Please note that the use of the free version for commercial/ corporate purposes violates our terms of use. This version of the tool is only intended for personal purposes. For corporate use of this tool, kindly refer to our commercial version.
Previous
Next
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.
Join thousands who have taken the next step in improving operational efficiency and insight analytics with GPetrium solutions.
100 +
and counting
The free solutions comprises of 4 key segments: Guide, Dashboard, Dataset, Factors.
- Guide – Aims to provide a quick snapshot of the steps that need to be done to ensure the tool works the way that the user needs it to work.
- Dashboard – Provides a snapshot of the utilities expenses throughout a period in time, it includes savings opportunities overview, unpaid bills, tips & tricks, categorization among others.
- Dataset – The segment where all the data is manually inputted so that the dashboard works as intended. It also provides a quick view (alongside the slicers) of cost lines that are relevant to the users.
- Factors – This segment provides a list of factors that may impact the cost of utilities and steps that can be done to optimize utility use/cost.
Dashboard Tab Overview
- Top-Left Pivot – Shows Unpaid Bills that are Behind payment or In-Progress, allowing for a quick overview of how much is owed.
- Expenditure – This number is based on the filters chosen in the slicer categories. It impacts the numbers provided in the savings section to the right.
- Savings Reminder – The savings reminder helps to think about how much money can be saved/made if there are some changes to the building’s overall expenditure. It serves as a reminder and an incentive to help cut costs (when viable) to improve someone’s financial position.
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)
- Return to Dataset – Button to allow the user to go to the Dataset quickly.
- Tips & Factors that Affect Expenditure – A few tips to help users understand what may be causing a spike in costs associated with a certain category.
- Slicers – Serves to quickly filter all the data within the Dashboard, helping to dig into the data that matters at the time.
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.
Bottom Portion of The Dashboard Section
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.
Dataset 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.
Table Columns
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
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.
FAQ
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.
- Digital Strategy – Help develop the individuals/ organization’s digital strategy to drive true competitive advantage.
- Digital Initiatives – Create and/or enhance digital tools to support key business initiatives and cost optimization solutions.
- Data Analytics – Turn data and digital assets into value driven capabilities.
- Data Visualization – Create meaning in data through clear and directional Visualization solutions
Our Latest Posts
Read More »
Communication Plan Template
September 14, 2021
Communication Plan Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp Communication helps instill important information between different parties, helping to build relations, drive collaboration and much
Team Charter Template
September 7, 2021
Team Charter Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp Team charters can help teams, projects and organizations build the rules of engagement and roadmap needed
Check Register Template
August 31, 2021
Check Register Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp Financial transactions have existed throughout history, helping buyers and sellers exchange goods based on their needs.
Employee Training Tracker
August 24, 2021
Employee Training Tracker Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp Training is a great way to learn or hone the skills relevant to one’s professional career
Bi-Weekly Budget Tool
August 17, 2021
Bi-Weekly Budget Tool Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp There are many cases where employees or contractors are paid bi-weekly for their effort. To some,
