A05: Excel Table and Summary Report

Hide Assignment Information

Context

You are given an ITS Support Log dataset from multiple campuses of a University. You will convert the data into an Excel Table, add calculated columns, and build a Report sheet using structured references and SUMIFS.

Important Notes: The data begins on row 3. Row 1 and 2 contain reference info and must NOT be included in the table:

The data in columns A through K show details of each support ticket. The user seeking support may be a student, faculty or staff. Channel indicates how they contacted support – via phone, email, chat or walked in. A ticket is assigned to a specific support person, who logs their work in minutes.

You need to complete the tasks below and submit the final Excel file with completed worksheets (Data and Report)

Submission: .xlsx file only,. Make sure you are not submitting a file that starts with ~ or $. Also, make sure you are submitting the file that you worked on. Check the file modification date if needed.

Tasks

Task 1 Create an Excel Table (3 pts)

1. Select the dataset starting at A3 (include headers + all rows of data).
   Hint: Ctrl + A or Cmd + A selects all contiguous data.
2. Convert it to an Excel Table (Insert Table).
3. Name the table: SupportTbl
4. Apply a light-colored table style

Task 2 Add calculated columns to the Table (9 pts)

Add these new columns to the right side of the table (as new Table columns). Use structured references (e.g., [@MinutesSpent]).

2A. CostPerMin (4 pts)

Create a new column named CostPerMin and assign a rate based on the support person (AssignedTo)

• Dana 1.20
• Alex 1.10
• Bri 1.15
• Everyone else 1.00

Enter the formula only in the first data row. The remaining rows should fill automatically.

2B. LaborCost (2 pts)

Create a new column named LaborCost:
LaborCost = MinutesSpent * CostPerMin

2C. TotalCost (3 pts)

Create a new column named TotalCost that includes overhead:
TotalCost = LaborCost * (1 + Overhead%)
Overhead% is given in row 1. Make sure all rows refer to the correct overhead %.

Task 3 Use the Total Row with filters (6 pts)

Turn on the Total Row for the table.

Use table filters to obtain values from the Total Row. You will need to filter and unfilter the dataset in different ways.
A. Show Sum of TotalCost in F2 (no filter needed)
B. Show count of Walk-in Student tickets in H2
C. Show the average Satisfaction rating for Faculty users (all channels) in J2
Do not use formulas for these tasks.
Hint: You may copy the results from Total Row and paste Values only in the desired cells.

Task 4 Create and set up Report sheet (3 pts)
Create a new worksheet named Report. You are creating a summary report for Campus and IssueCategory. Example below:

Set up headers as shown
A1: Summary Report for Campus and Issue Category
D2: <– Total Cost –>
Column headers on Row 3: A3: Campus , B3: Total Tickets, C3: Avg Satisfaction Rating

Task 5 Create summary report for Campuses (8 points)

A. Copy unique Campus values into column A starting at A4, Sort alphabetically
Hint: Copy campus values from Data worksheet -> Remove Duplicates -> Sort

B. Use appropriate functions to calculate Total tickets and Average Satisfaction ratings for campuses in column A.
You must use table SupportTbl. Write formulas once in row 4 and copy down.
Note: using entire column range (such as D:D) will earn you no points.

Task 6: Find total cost by campus and IssueCategory (6 points)
A. Copy unique IssueCategories (sorted) on row 3 starting column D
Hint: Copy IssueCategories from Data worksheet -> Remove Duplicate -> Sort -> Copy Transpose from D3

B. Use appropriate functions to calculate TotalCost for Campuses in column A and IssueCategory in row 3.
You must use table SupportTbl. Write formulas once and copy across then down.
Check: Double-click on different cells to make sure that your your formula is referring to the correct ranges and criteria values.
Note: using entire column range (such as D:D) will earn you no points.