8 tasks | 100 points total
| # | Pts | Location | Requirement | Automated checks |
|---|---|---|---|---|
| 1 | 8 | Analysis!B2 |
On the 'Analysis' sheet, first create a Named Range called 'LaborCost' that refers to the calculated labor cost for every job. Labor cost per job = HoursWorked × HourlyRate × CrewSize (columns I, J, and H on the Jobs sheet). Then, in cell B2 of the 'Analysis' sheet, use a SUM formula referencing the named range 'LaborCost' to display the total labor cost across all 80 jobs. (Skills: sum, named_ranges) | sheet exists: 'Analysis' named range: 'LaborCost' formula in cell: Analysis!B2 uses SUM calculated, not typed: Analysis!B2 |
| 2 | 7 | Analysis!B3 |
In cell B3 of the 'Analysis' sheet, use a COUNTIF formula to count the number of jobs in the 'Jobs' sheet where the PaymentStatus (column M) is 'Overdue'. (Skills: countif) | sheet exists: 'Analysis' formula in cell: Analysis!B3 uses COUNTIF calculated, not typed: Analysis!B3 |
| 3 | 12 | Analysis!B6, B7, B8, B9 |
In cells B6, B7, B8, and B9 of the 'Analysis' sheet, use SUMIF formulas to calculate the total InvoiceAmount (column L of the 'Jobs' sheet) for each quarter: Q1 in B6, Q2 in B7, Q3 in B8, and Q4 in B9. The Quarter field is in column E of the 'Jobs' sheet. Use absolute references for the criteria range and sum range. (Skills: sumif) | sheet exists: 'Analysis' formula in cell: Analysis!B6 uses SUMIF formula in cell: Analysis!B7 uses SUMIF formula in cell: Analysis!B8 uses SUMIF formula in cell: Analysis!B9 uses SUMIF calculated, not typed: Analysis!B6, B7, B8, B9 |
| 4 | 12 | Jobs!O2 |
In cell O2 of the 'Jobs' sheet, enter a formula using IFERROR wrapped around VLOOKUP to retrieve the ContractTier (column C of 'SiteDirectory') for each job, matching on JobSiteID (column N of 'Jobs' against column A of 'SiteDirectory'). If no match is found, display the text 'Unknown'. Fill the formula down through O81 to cover all 80 data rows. (Skills: vlookup, iferror) | sheet exists: 'Jobs' formula in cell: Jobs!O2 uses IFERROR, VLOOKUP calculated, not typed: Jobs!O2 |
| 5 | 12 | Jobs!P2 |
In cell P2 of the 'Jobs' sheet, write a nested IF formula to assign a Profitability Rating to each job based on the profit margin. First calculate the gross profit as InvoiceAmount (L) minus MaterialCost (K) minus labor cost (CrewSize H × HoursWorked I × HourlyRate J). Then divide gross profit by InvoiceAmount (L) to get the margin. If the margin is greater than 0.40 display 'High', if greater than 0.20 display 'Medium', otherwise display 'Low'. Fill the formula down through P81. (Skills: if, nested_if) | sheet exists: 'Jobs' formula in cell: Jobs!P2 uses IF calculated, not typed: Jobs!P2 |
| 6 | 12 | Analysis!B12 |
In cell B12 of the 'Analysis' sheet, use an XLOOKUP formula to find the SiteName from the 'SiteDirectory' sheet for the job site with JobSiteID 'SITE-042'. Look up 'SITE-042' in column A of 'SiteDirectory' and return the corresponding value from column B (SiteName). If not found, return 'Site Not Found'. (Skills: xlookup, index_match) | sheet exists: 'Analysis' formula in cell: Analysis!B12 uses XLOOKUP calculated, not typed: Analysis!B12 |
| 7 | 17 | Dashboard!A1 |
On the 'Dashboard' sheet, create a PivotTable using the data from the 'Jobs' sheet (A1:N81). Place the PivotTable with its top-left corner at cell A1 of the 'Dashboard' sheet. Configure the PivotTable so that Region (column B) is the Row field, JobType (column C) is the Column field, and the Values area shows the Sum of InvoiceAmount (column L). Then, based on the PivotTable results, insert a Clustered Bar Chart on the 'Dashboard' sheet that visualises total InvoiceAmount by Region. Finally, apply Conditional Formatting to the PivotTable's value cells so that the top 10% of values are highlighted with a green fill. (Skills: pivot_table, charts, conditional_formatting) | sheet exists: 'Dashboard' pivot table present on 'Dashboard' chart present: BarChart on 'Dashboard' conditional formatting on 'Dashboard' |
| 8 | 20 | Analysis!B15 |
In cell B15 of the 'Analysis' sheet, write a formula using INDEX and MATCH (with IFERROR for error handling) to find the InvoiceAmount (column L of 'Jobs') for the job that has the single highest MaterialCost (column K of 'Jobs') across all 80 rows. The formula should first identify which row contains the maximum MaterialCost, then return that row's InvoiceAmount. If no value is found, display 'N/A'. Do not use a helper column — the entire logic must be contained in cell B15. (Skills: index_match, iferror, sumif) | sheet exists: 'Analysis' formula in cell: Analysis!B15 uses INDEX, MATCH, IFERROR, MAX calculated, not typed: Analysis!B15 |
Checks listed as automated are verified programmatically against the submitted workbook. Requirements without automated checks are assessed by review.
=SUM(LaborCost)=COUNTIF(Jobs!M2:M81,"Overdue")=SUMIF(Jobs!$E$2:$E$81,"Q1",Jobs!$L$2:$L$81)=IFERROR(VLOOKUP(N2,SiteDirectory!$A$2:$E$81,3,FALSE),"Unknown")=IF((L2-K2-(H2*I2*J2))/L2>0.40,"High",IF((L2-K2-(H2*I2*J2))/L2>0.20,"Medium","Low"))=XLOOKUP("SITE-042",SiteDirectory!A2:A81,SiteDirectory!B2:B81,"Site Not Found")=IFERROR(INDEX(Jobs!L2:L81,MATCH(MAX(Jobs!K2:K81),Jobs!K2:K81,0)),"N/A")