======================================================================== Verdant Grounds — Job Costing Workbook — Grading Feedback ======================================================================== Student: Jordan Ellis File: Jordan Ellis - Verdant Grounds Job Costing - Mar 3, 2026 1142 AM - submission.xlsx Score: 76.00 / 100.00 (76.0%) Grade: C+ ------------------------------------------------------------------------ Requirement 1 — 3.00 / 8.00 (37.5%) ------------------------------------------------------------------------ Named Range 'LaborCost' and SUM formula in Analysis!B2 Feedback: + Named range 'LaborCost' was successfully created and is present in the workbook. + The Analysis sheet exists as required. Point deductions: ! -3 points: Cell B2 contains a hard-coded value (48250) instead of a SUM formula referencing the LaborCost named range. This fails the 'must use a formula' requirement. ! -2 points: The named range 'LaborCost' refers to 'Analysis'!$B$2:$B$58, which is a range on the Analysis sheet rather than the calculated labor cost values (H×I×J) from the Jobs sheet. The named range does not correctly refer to the labor cost calculation per job. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Named range 'LaborCost' found ('Analysis'!$B$2:$B$58). [MISPLACED] Analysis!B2 does not hold the expected formula, but an equivalent formula was found at Analysis!B6: =SUMIF(Jobs!$E$2:$E$81,"Q1",Jobs!$L$2:$L$81). The calculation is correct; its placement is not. [HARDCODED] Analysis: hard-coded values where formulas were required — B2=48250. ------------------------------------------------------------------------ Requirement 2 — 3.00 / 7.00 (42.9%) ------------------------------------------------------------------------ COUNTIF formula in Analysis!B3 for Overdue payment status Feedback: + A correct COUNTIF formula was found: =COUNTIF(Jobs!M2:M81,"Overdue") — the logic is correct. + The formula correctly references the PaymentStatus column and uses 'Overdue' as the criterion. Point deductions: ! -4 points: The COUNTIF formula is placed in B5 instead of the required cell B3. Cell B3 is empty. This is a significant placement error. Automated checks: [PASS] Sheet 'Analysis' found. [MISPLACED] Analysis!B3 does not hold the expected formula, but an equivalent formula was found at Analysis!B5: =COUNTIF(Jobs!M2:M81,"Overdue"). The calculation is correct; its placement is not. [FAIL] Analysis: expected cells are empty — B3. ------------------------------------------------------------------------ Requirement 3 — 6.00 / 12.00 (50.0%) ------------------------------------------------------------------------ SUMIF formulas in B6, B7, B8, B9 for quarterly InvoiceAmount totals Feedback: + SUMIF formulas are present in all four required cells (B6, B7, B8, B9). + Absolute references are used for the criteria and sum ranges as required. + The formula structure is correct and uses the proper columns. Point deductions: ! -6 points: All four SUMIF formulas (B6, B7, B8, B9) use the same criterion 'Q1' instead of 'Q1', 'Q2', 'Q3', and 'Q4' respectively. B7 should use 'Q2', B8 should use 'Q3', and B9 should use 'Q4'. This means three of the four formulas return incorrect results. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!B6 contains the expected formula: =SUMIF(Jobs!$E$2:$E$81,"Q1",Jobs!$L$2:$L$81) [PASS] Analysis!B7 contains the expected formula: =SUMIF(Jobs!$E$2:$E$81,"Q1",Jobs!$L$2:$L$81) [PASS] Analysis!B8 contains the expected formula: =SUMIF(Jobs!$E$2:$E$81,"Q1",Jobs!$L$2:$L$81) [PASS] Analysis!B9 contains the expected formula: =SUMIF(Jobs!$E$2:$E$81,"Q1",Jobs!$L$2:$L$81) [PASS] Analysis: all checked cells use formulas (B6, B7, B8, B9). ------------------------------------------------------------------------ Requirement 4 — 12.00 / 12.00 (100.0%) ------------------------------------------------------------------------ IFERROR/VLOOKUP formula in Jobs!O2 for ContractTier lookup Feedback: + Cell O2 contains the exact expected formula: =IFERROR(VLOOKUP(N2,SiteDirectory!$A$2:$E$81,3,FALSE),"Unknown") + IFERROR is correctly wrapping VLOOKUP. + The lookup correctly targets column 3 (ContractTier) from SiteDirectory. + Absolute references are used for the lookup table. + The error fallback 'Unknown' is correctly specified. Automated checks: [PASS] Sheet 'Jobs' found. [PASS] Jobs!O2 contains the expected formula: =IFERROR(VLOOKUP(N2,SiteDirectory!$A$2:$E$81,3,FALSE),"Unknown") [PASS] Jobs: all checked cells use formulas (O2). ------------------------------------------------------------------------ Requirement 5 — 12.00 / 12.00 (100.0%) ------------------------------------------------------------------------ Nested IF formula in Jobs!P2 for Profitability Rating Feedback: + Cell P2 contains the exact expected formula: =IF((L2-K2-(H2*I2*J2))/L2>0.40,"High",IF((L2-K2-(H2*I2*J2))/L2>0.20,"Medium","Low")) + The gross profit calculation correctly subtracts MaterialCost and labor cost from InvoiceAmount. + The margin thresholds (0.40 and 0.20) are correctly applied. + The nested IF structure is properly constructed with 'High', 'Medium', and 'Low' outputs. Automated checks: [PASS] Sheet 'Jobs' found. [PASS] Jobs!P2 contains the expected formula: =IF((L2-K2-(H2*I2*J2))/L2>0.40,"High",IF((L2-K2-(H2*I2*J2))/L2>0.20,"Medium","Low")) [PASS] Jobs: all checked cells use formulas (P2). ------------------------------------------------------------------------ Requirement 6 — 12.00 / 12.00 (100.0%) ------------------------------------------------------------------------ XLOOKUP formula in Analysis!B12 for SITE-042 SiteName Feedback: + Cell B12 contains the exact expected formula: =XLOOKUP("SITE-042",SiteDirectory!A2:A81,SiteDirectory!B2:B81,"Site Not Found") + XLOOKUP correctly searches for 'SITE-042' in column A of SiteDirectory. + The return range correctly targets column B (SiteName). + The not-found fallback 'Site Not Found' is correctly specified. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!B12 contains the expected formula: =XLOOKUP("SITE-042",SiteDirectory!A2:A81,SiteDirectory!B2:B81,"Site Not Found") [PASS] Analysis: all checked cells use formulas (B12). ------------------------------------------------------------------------ Requirement 7 — 8.00 / 17.00 (47.1%) ------------------------------------------------------------------------ PivotTable, Clustered Bar Chart, and Conditional Formatting on Dashboard sheet Feedback: + A BarChart is present on the Dashboard sheet (titled 'Jobs overview'), satisfying the chart requirement. + Conditional formatting is present on the Dashboard sheet (applied to A2:A54). + The sheet is clearly intended as the Dashboard sheet. Point deductions: ! -1 point: The sheet is named 'Dashbaord' (misspelled) instead of 'Dashboard'. ! -8 points: No PivotTable was found in the workbook. This is a major missing component worth a significant portion of the requirement. ! Note: The conditional formatting is applied to column A (A2:A54) rather than the PivotTable value cells, and without a PivotTable present, this cannot be verified as correctly applied to PivotTable values. Partial credit awarded for the formatting rule existing. Automated checks: [FUZZY_NAME] Sheet 'Dashboard' not found exactly; 'Dashbaord' is a close match and was treated as the intended sheet. The name is misspelled or non-standard. [FAIL] No pivot tables found in the workbook. [PASS] BarChart found on 'Dashbaord'. [PASS] 1 conditional formatting rule(s) on 'Dashbaord' (). ------------------------------------------------------------------------ Requirement 8 — 20.00 / 20.00 (100.0%) ------------------------------------------------------------------------ INDEX/MATCH/IFERROR/MAX formula in Analysis!B15 for highest MaterialCost InvoiceAmount Feedback: + Cell B15 contains the exact expected formula: =IFERROR(INDEX(Jobs!L2:L81,MATCH(MAX(Jobs!K2:K81),Jobs!K2:K81,0)),"N/A") + INDEX correctly references the InvoiceAmount column. + MATCH correctly finds the position of the maximum MaterialCost. + MAX correctly identifies the highest MaterialCost value. + IFERROR correctly handles potential errors with 'N/A' fallback. + No helper column was used — all logic is contained in B15. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!B15 contains the expected formula: =IFERROR(INDEX(Jobs!L2:L81,MATCH(MAX(Jobs!K2:K81),Jobs!K2:K81,0)),"N/A") [PASS] Analysis: all checked cells use formulas (B15). ======================================================================== Overall Assessment ======================================================================== This submission demonstrates a mixed level of competency. The student excelled on several individual formula tasks — the VLOOKUP/IFERROR (Req 4), nested IF (Req 5), XLOOKUP (Req 6), and INDEX/MATCH (Req 8) were all perfectly executed with the exact expected formulas. However, there are significant issues with the Analysis sheet tasks: the SUM formula in B2 was replaced with a hard-coded value, the COUNTIF was placed in the wrong cell (B5 instead of B3), and all four SUMIF formulas used 'Q1' as the criterion rather than the correct quarter for each row. The Dashboard sheet is missing its PivotTable entirely, which is a major omission, and the sheet name is misspelled. The named range 'LaborCost' also references the wrong range (Analysis column B instead of the Jobs sheet labor cost calculations). Overall, the student shows strong formula-writing ability but struggled with placement accuracy, formula customization across multiple cells, and the PivotTable requirement. Strengths: + Perfect execution of VLOOKUP wrapped in IFERROR (Requirement 4) + Perfect nested IF formula for Profitability Rating (Requirement 5) + Perfect XLOOKUP formula for site lookup (Requirement 6) + Perfect INDEX/MATCH/MAX/IFERROR formula for highest MaterialCost (Requirement 8) + Good use of absolute references in SUMIF formulas + A BarChart was created on the Dashboard sheet + Conditional formatting rule was applied to the Dashboard sheet + Named range 'LaborCost' was created Areas for improvement: - Do not hard-code values where formulas are required — B2 should contain =SUM(LaborCost), not the number 48250 - Ensure formulas are placed in the exact cells specified — COUNTIF belonged in B3, not B5 - When copying SUMIF formulas across multiple cells, update the criteria argument for each quarter (Q1, Q2, Q3, Q4) rather than using the same criterion in all four cells - The named range 'LaborCost' should reference the labor cost calculation from the Jobs sheet (H×I×J columns), not a range on the Analysis sheet - A PivotTable must be created on the Dashboard sheet — this was entirely missing - Correct the sheet name spelling from 'Dashbaord' to 'Dashboard' - Ensure conditional formatting on the Dashboard is applied to the PivotTable value cells as specified