Grading Rubric: Campus Energy Audit — Analysis with Python in Excel

Task Requirements

#	Pts	Location	Requirement	Automated checks
1	5	`Analysis!B2`	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. (Skills: sum)	sheet exists: 'Analysis' formula in cell: Analysis!B2 uses SUM calculated, not typed: Analysis!B2
2	5	`Analysis!B3`	In cell B3 of the Analysis sheet, write a formula that calculates the average monthly TotalCost (Readings!J2:J121) across all buildings and months. (Skills: average)	sheet exists: 'Analysis' formula in cell: Analysis!B3 uses AVERAGE calculated, not typed: Analysis!B3
3	10	`Analysis!C8, C9, C10, C11, C12, C13`	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. (Skills: sumif)	sheet exists: 'Analysis' formula in cell: Analysis!C8 uses SUMIF formula in cell: Analysis!C13 uses SUMIF calculated, not typed: Analysis!C8, C9, C10, C11, C12, C13
4	10	`Analysis!B16`	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. (Skills: sumif)	sheet exists: 'Analysis' formula in cell: Analysis!B16 uses SUMIF calculated, not typed: Analysis!B16
5	15	`Analysis!E2`	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. (Skills: pivot_table)	sheet exists: 'Analysis' pivot table present on 'Analysis'
6	15	`Dashboard`	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. (Skills: charts)	sheet exists: 'Dashboard' chart present: BarChart on 'Dashboard'
7	10	`Analysis!A2`	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. (Skills: py_basics, py_dataframe)	sheet exists: 'Analysis' formula in cell: Analysis!A2 uses PY
8	15	`Analysis!A25`	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. (Skills: py_aggregation, py_statistics)	sheet exists: 'Analysis' formula in cell: Analysis!A25 uses PY
9	15	`Dashboard!A2`	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. (Skills: py_visualization)	sheet exists: 'Dashboard' formula in cell: Dashboard!A2 uses PY

Checks listed as automated are verified programmatically against the submitted workbook. Requirements without automated checks are assessed by review.

Grading Policy

A cell that holds a typed number where the task required a calculation fails that requirement even if the number is right.
A correct formula in the wrong cell is a placement error. Award most of the credit and note the correct location.
A misspelled sheet name that is clearly the intended sheet costs presentation points, not the whole requirement.

Reference Solutions

Task 1 (5 pts)

=SUM(Readings!F2:F121)

Task 2 (5 pts)

=AVERAGE(Readings!J2:J121)

Task 3 (10 pts)

=SUMIF(Readings!$C$2:$C$121,B8,Readings!$F$2:$F$121)

Task 4 (10 pts)

=SUMIF(Readings!D2:D121,"Academic",Readings!J2:J121)

Task 7 (10 pts)

df = xl("Readings!A1:J121", headers=True)
df[["Building", "ElectricityKWh", "GasTherms", "TotalCost"]]

Task 8 (15 pts)

df = xl("Readings!A1:J121", headers=True)
r_heat = df["HeatingDegreeDays"].corr(df["GasTherms"])
r_cool = df["CoolingDegreeDays"].corr(df["ElectricityKWh"])
pd.DataFrame({"Comparison": ["HDD vs GasTherms", "CDD vs ElectricityKWh"], "Correlation": [r_heat, r_cool]})

Task 9 (15 pts)

df = xl("Readings!A1:J121", headers=True)
intensity = df.groupby("Building").apply(lambda g: g["ElectricityKWh"].sum() / g["SquareFeet"].iloc[0]).sort_values()
fig, ax = plt.subplots(figsize=(8, 5))
ax.barh(intensity.index, intensity.values)
ax.set_xlabel("kWh per Square Foot")
ax.set_title("Electricity Intensity by Building (kWh / sq ft)")
plt.tight_layout()
fig