Mixed native-formula and Python in Excel analysis of campus building energy consumption.
Download the starter workbook (EnergyReadings_Starter.xlsx) and complete each of the nine tasks in the exact sheet and cell specified. All answers must be live formulas or Python in Excel cells — typing a static number will receive zero credit even if the value is correct. Tasks 1–6 use native Excel functions; Tasks 7–9 use Python in Excel (available via the Formulas ribbon on supported Microsoft 365 builds). Save your completed file as LastName_FirstName_Energy.xlsx and upload it to the course portal before the deadline.
The university sustainability office has commissioned an analysis of 120 monthly energy readings spanning six campus buildings to identify inefficiencies and prioritise retrofit spending. As a student analyst, you have been given a raw meter-reading workbook and must build summary statistics, a building-level dashboard, and weather-correlation evidence to guide the capital planning committee. Your completed workbook will be presented to senior administrators at the end of the semester.
1. In cell B2 of the Analysis sheet, write a formula that sums all values in the ElectricityKWh column (Readings!F2:F121) to find the total electricity consumed across all buildings and months. (5 pts)
2. In cell B3 of the Analysis sheet, write a formula that calculates the average monthly TotalCost (Readings!J2:J121) across all buildings and months. (5 pts)
3. In cells C8 through C13 of the Analysis sheet, first type the six building names in B8:B13 (Wells Hall, Engineering, Library, Union, Chemistry, Recreation — one per row). Then in C8, write a SUMIF formula that totals ElectricityKWh (Readings!F2:F121) where the Building column (Readings!C2:C121) matches the name in B8. Copy the formula down through C13 so each row shows that building's total electricity consumption. (10 pts)
Hint: Use absolute references on the Readings ranges so they do not shift when you fill the formula down. The building name in column B should remain a relative reference.
4. In cell B16 of the Analysis sheet, write a SUMIF formula that sums TotalCost (Readings!J2:J121) only for rows where BuildingType (Readings!D2:D121) equals "Academic". This will reveal how much of the total utility budget is spent on academic buildings. (10 pts)
Hint: The criteria argument should be the text string "Academic" (with quotes inside the formula). Remember SUMIF takes the range to check, the criteria, and then the range to sum — in that order.
5. In the Analysis sheet, beginning at cell E2, create a PivotTable sourced from Readings!A1:J121. Configure it so that Building names appear as Row Labels, the column field is empty, and the Values area shows the Sum of ElectricityKWh and the Sum of GasTherms — one column for each measure. This table will become the data foundation for the sustainability dashboard. (15 pts)
Hint: Insert the PivotTable from the Insert ribbon. Drag 'Building' to the Rows area, then drag 'ElectricityKWh' and 'GasTherms' to the Values area. Make sure both are set to Sum, not Count.
6. In the Dashboard sheet, insert a Bar Chart (clustered) that visualises total ElectricityKWh and total GasTherms per building, using the PivotTable data you created in Task 5 (Analysis!E2 and surrounding cells) as its source. The chart should have a descriptive title such as 'Energy Consumption by Building'. Place and resize the chart so it fits neatly within the Dashboard sheet. (15 pts)
Hint: Select the building names and both energy columns in your PivotTable summary, then use Insert > Charts > Bar to create a clustered bar chart. Right-click the chart title to rename it.
7. In cell A2 of the Analysis sheet, write a Python in Excel formula that reads the entire Readings data (Readings!A1:J121) as a DataFrame with headers, then returns a new DataFrame containing only the columns Building, ElectricityKWh, GasTherms, and TotalCost. The resulting object will spill into surrounding cells and serve as a clean reference table for further analysis. (10 pts) Python in Excel
Hint: Use xl() with headers=True to load the data. Select multiple columns from a DataFrame by passing a list of column name strings inside double brackets.
8. In cell A25 of the Analysis sheet, write a Python in Excel formula that loads Readings!A1:J121 as a DataFrame, computes the Pearson correlation coefficient between HeatingDegreeDays and GasTherms and the Pearson correlation between CoolingDegreeDays and ElectricityKWh, and returns a two-row DataFrame with columns 'Comparison' and 'Correlation' labelling each result. This will tell sustainability managers how strongly weather drives energy use. (15 pts) Python in Excel
Hint: Use the pandas Series .corr() method to get the Pearson coefficient. Build a small DataFrame from dictionaries with two keys to return both results cleanly.
9. In cell A2 of the Dashboard sheet, write a Python in Excel formula that loads Readings!A1:J121 as a DataFrame, computes each building's average energy intensity as total ElectricityKWh divided by SquareFeet (aggregated per building), and produces a horizontal bar chart (using matplotlib) with buildings on the y-axis and average kWh per square foot on the x-axis. Title the chart 'Electricity Intensity by Building (kWh / sq ft)' and label the x-axis 'kWh per Square Foot'. The chart image will spill into the Dashboard sheet to highlight the worst-performing buildings. (15 pts) Python in Excel
Hint: Use groupby on 'Building' with an apply lambda to compute total kWh / SquareFeet for each group. Return the matplotlib figure object (fig) as the last expression so Excel renders it as an image.