Native formulas and Python in Excel side by side over a year of transit operations data.
Download the starter workbook and complete each task in the exact sheet and cell indicated. Every answer must be a formula or a Python in Excel expression — a typed-in number will not receive credit even if it is correct. Do not rename, delete, or restructure any existing sheets or columns, as the autograder depends on the original layout. Save your work frequently and submit the completed .xlsx file to the course portal before the deadline.
Tri-Valley Regional Transit Authority (TVRTA) has hired you as a junior data analyst to evaluate one quarter of daily bus operations. Management needs a clear picture of ridership patterns, fuel efficiency, and on-time performance across all four service zones before presenting findings to the regional transportation board. Your Excel workbook combines raw operational logs with route reference data so you can build the summaries, pivot analyses, and visualisations the board requires.
1. In cell B2 of the Analysis sheet, calculate the total number of passenger boardings (TotalRiders) across all 80 log entries in the Operations sheet. (5 pts)
2. In cell B3 of the Analysis sheet, calculate the average fuel consumption in gallons (FuelUsedGallons) across all 80 log entries in the Operations sheet. Round to 2 decimal places using the ROUND function. (5 pts)
3. In cells B6 through B9 of the Analysis sheet, use SUMIF to calculate the total TotalRiders for each of the four zones. Cell B6 should hold the total for the 'North' zone, B7 for 'South', B8 for 'East', and B9 for 'West'. Use the zone labels already entered in cells A6:A9 as the criteria. Reference the Zone column (C2:C81) and TotalRiders column (K2:K81) on the Operations sheet. (10 pts)
Hint: Use absolute references on the Operations ranges so the formula can be filled down for all four zones.
4. In cell B12 of the Analysis sheet, calculate the on-time performance rate for routes where an incident was reported. The on-time performance rate is defined as the sum of OnTimeTrips divided by the sum of CompletedTrips, expressed as a percentage, but only for rows where IncidentReported equals 'Yes' on the Operations sheet. Use SUMIF functions referencing Operations!P2:P81 as the criteria range, and format the result as a percentage. (10 pts)
Hint: Think of on-time rate as a ratio: (sum of on-time trips) ÷ (sum of completed trips), filtered to incident rows only. You will need two SUMIF expressions.
5. Create a PivotTable on the Analysis sheet starting at cell D2. Use the Operations sheet data (A1:P81) as the source. Configure the PivotTable with: RouteCode as Row labels, VehicleType as Column labels, and the Average of FuelUsedGallons as the Values field. Format the values in the PivotTable to show 2 decimal places. (15 pts)
Hint: Use Insert > PivotTable and point the data source at the full Operations table range including the header row.
6. On the Dashboard sheet, create a Bar Chart (clustered bar) that visualises total TotalRiders by Zone. Use the zone totals you calculated in Analysis!A6:B9 as the chart data source. Title the chart 'Total Ridership by Zone'. Place the chart within the range A2:H20 on the Dashboard sheet. (15 pts)
Hint: Select your zone summary data on the Analysis sheet first, then use Insert > Chart and choose Clustered Bar. Move and resize the chart onto the Dashboard sheet.
7. In cell A22 of the Dashboard sheet, write a Python in Excel formula that reads all Operations data and returns a cleaned summary DataFrame showing, for each RouteCode, the total CompletedTrips, total TotalRiders, and average FuelUsedGallons (rounded to 2 decimal places). The result should be a DataFrame with columns named 'RouteCode', 'CompletedTrips', 'TotalRiders', and 'AvgFuelGallons', sorted ascending by RouteCode. (15 pts) Python in Excel
Hint: Use xl() to load the Operations range as a DataFrame with headers=True, then use groupby and agg to compute the three metrics per route.
8. In cell A42 of the Dashboard sheet, write a Python in Excel formula that computes a correlation matrix between the following numeric columns from the Operations sheet: ScheduledTrips, CompletedTrips, TotalRiders, FuelUsedGallons, and AvgSpeedMPH. Return the correlation matrix as a DataFrame rounded to 3 decimal places. (10 pts) Python in Excel
Hint: Load the Operations data with xl(), select only the five relevant columns by name, and call the pandas method that computes pairwise correlations.
9. In cell A55 of the Dashboard sheet, write a Python in Excel formula that produces a grouped bar chart image showing average TotalRiders by Zone and VehicleType. Use the Operations sheet data as the source. The chart should have a title of 'Avg Ridership by Zone and Vehicle Type', labelled axes (x-axis: 'Zone', y-axis: 'Average Riders'), and a legend. Return the matplotlib figure so Excel renders it as an embedded image. (15 pts) Python in Excel
Hint: Group the data by two categorical columns and unstack one of them to create a wide DataFrame suitable for a grouped bar chart. Return the figure object, not plt.show().