Aircon Analysis Dashboard

My Filipino mom always scolded me for using the Air Conditioner as a child. It became more of a display at some point. Recently, I wondered how expensive is using an Air Conditioner for an hour. I also wondered the energy cost comparison of an Aircon vs an Electric Fan. Is it more efficient to use four electric fans than to use an Aircon? Surprisingly, no one has made a visual report about this topic yet.

The data were taken from estimates of official Meralco websites. The data were categorized into Brand Capacity (hp), Description Model, Est. Energy Consumption Per Hour (kWh) and, Es. Cost Per Hour (Php). Most data were already provided in Meralco's data source, however unit cost was not included. I had to manually search for the unit costs using online sources.

I adjusted the power rate to P10.2 which is the current power rate. I removed the data where unit costs cannot be determined. I also removed null values and error values to avoid errors in Microsoft Power BI. Another problem I encountered was that I could not find the Philippine Peso in the Currency table which led me to convert the data to USD.

For data analysis, I used SQL to analyze the data. Considering the size of our dataset, Excel can also be used and uploaded directly to PowerBI. I'll be using SQL for this project to demonstrate my SQL abilities. Using SQL, I created the table with their corresponding headers and data type and then inserted the data from the CSV file to the created table. Although I could directly import the CSV file, I feel that this method is also better to demonstrate that I can create tables from scratch. After checking the data using the query 'SELECT * FROM Raw_Data_Electricity', the data seems to be complete.

*Note: the 'if' query at the first line is for automation purposes. It basically states to delete the table Raw_Data_Electricity if it is not blank, and reinsert the new data. I plan to add the automation process in the future.

After that, we can now compute for the calculations. To promote data integrity, I also did the calculations in Excel to verify the value and all the results match.

The first calculation is the Average Energy Cost of Aircon per hour. The SQL query for this is simply selecting the average of the estimated cost per hour flex-column where Horsepower is equal to 1.

The second calculation is Energy Cost Difference. This calculation would show the difference in terms of energy used (kWh) for Aircon vs Electric Fans. For the energy cost difference, I created a view that selects the Estimated Cost per hour for Aircons with a Horsepower of 1 and divided that by the Estimated Cost per Hour for Electric Fans. Estimated Cost per hour for Aircons with a Horsepower of 1 is aliased q1 while Estimated Cost per Hour for Electric Fans is aliased q2. From the result, it seems that the energy cost difference of 1HP Aircon vs Electric Fan is 469% (4.69 converted to percentage).

The third calculation is Energy Efficiency Difference. This calculation would show the difference in terms of energy consumption (kWh) for Conventional Aircon vs Inverted Aircon. Using the same process as Calculation 1, the data states that Inverter Airconditioners are 40.06% more energy efficient than Conventional Airconditioners.

After the calculations, we then add the calculations to our original table. We first add another column for the calculations and then insert the data using UNION query.

From there, we then add the SQL Server data to PowerBI.

To summarize, the results of data analysis suggest that the average energy cost per hour of Aircon is $0.0704 (≈₱3.52). Energy cost difference between 1 HP Aircon and Electric fan is 4.69x (469%). Lastly, using an inverter is 40.06% more cost efficient than conventional Aircons.

In my case, Data visualization came first before Data Analysis. I had to determine what information I should put in the dashboard and how the layout would be designed. I included graphs of unit cost by horsepower and estimated cost per hour by type. I also included splicers and strong visuals for this personal project. Splicers give the opportunity for users to filter information based on what they want to know. Unfortunately, the splicers and filters will not work because the file shown above is only a PDF due to restrictions in my Microsoft Account.

If you want to take a look at the raw file including the working splicers and database, you can download it here:

Download File

Hold 'Ctrl' and press left-click to choose numerous items in the splicer list.

Overall, the project was fulfilling because I identified a problem and solved it using data analytics. It is important to take note that the data and output are all estimates by Meralco Corporation. Real consumption data fluctuates based on different factors such as room temperature, increasing electricity price rates, different aircon models, wear and tear, etc.

Upon speculation, it seems that the cost per hour from the data of Meralco was low. It showed that the cost per hour is only around P4 while I was expecting a cost of around P8 per hour. The scenario reminded me of the importance of 'Garbage in, Garbage out'. If the data is wrong, the output will be wrong. Hence, data validation and data cleaning is an essential skill of a Data Analyst.

References:

https://meralcomain.s3.ap-southeast-1.amazonaws.com/images/ckeditor-documents/Orange%20Tag%20Appliances%202019_Fans.pdf?null
https://meralcomain.s3.ap-southeast-1.amazonaws.com/images/ckeditor-documents/Air%20Conditioners%20June%202020_0.pdf?null&fbclid=IwAR3DB6jVL40iDofQT_xl19_klS5nVhRGH-DvxHJurbKPMrdo2Q-vREUmsPk