======================================================================== Northwind Traders — Sales Analysis Workbook — Grading Feedback ======================================================================== Student: Jordan Ellis File: Jordan Ellis - Northwind Sales Analysis - Mar 3, 2026 1142 AM - submission.xlsx Score: 59.00 / 100.00 (59.0%) Grade: F ------------------------------------------------------------------------ Requirement 1 — 4.00 / 8.00 (50.0%) ------------------------------------------------------------------------ Named range 'Revenue' covering H2:H81, header in H1, revenue formulas in H2:H81, SUM with named range in H82 Feedback: + Named range 'Revenue' is defined (covers H2:H86, slightly extended but present) + The Orders sheet exists Point deductions: ! H82 contains a hard-coded value (48250) instead of a formula using SUM with the named range — this is a critical failure per rubric ! Only 6 formulas are found on the Orders sheet total (I2, J2, I10, J25, I50, J70), suggesting H2:H81 revenue formulas (Units × UnitPrice) are also hard-coded or missing as formulas ! The named range covers H2:H86 instead of H2:H81 (minor issue) Automated checks: [PASS] Sheet 'Orders' found. [PASS] Named range 'Revenue' found ('Orders'!$H$2:$H$86). [HARDCODED] Orders!H82 holds the literal value 48250 instead of a formula. A hard-coded result does not update when inputs change. [HARDCODED] Orders: hard-coded values where formulas were required — H82=48250. ------------------------------------------------------------------------ Requirement 2 — 4.00 / 7.00 (57.0%) ------------------------------------------------------------------------ COUNTIF formulas in C2:C5 on Analysis sheet counting orders by region Feedback: + COUNTIF formulas are present in C3, C4, C5 + C5 contains the expected COUNTIF formula + Analysis sheet exists Point deductions: ! C2 is empty — the COUNTIF formula is missing from C2 (formula starts at C3 instead) ! All COUNTIF formulas reference B2 instead of the corresponding row (C3 references B2, C4 references B2, C5 references B2) — this means all cells count the same region (North) rather than South, East, West respectively, producing incorrect results for C3, C4, C5 Automated checks: [PASS] Sheet 'Analysis' found. [MISPLACED] Analysis!C2 does not hold the expected formula, but an equivalent formula was found at Analysis!C3: =COUNTIF(Orders!$C$2:$C$81,B2). The calculation is correct; its placement is not. [PASS] Analysis!C5 contains the expected formula: =COUNTIF(Orders!$C$2:$C$81,B2) [FAIL] Analysis: formulas present at C3, C4, C5 but C2 empty. ------------------------------------------------------------------------ Requirement 3 — 6.00 / 12.00 (50.0%) ------------------------------------------------------------------------ SUMIF formulas in F2:F5 for regional revenue, SUM in F6 for grand total Feedback: + SUMIF formulas are present in F2, F3, F4, F5 + F2 contains the correct SUMIF formula structure + All cells use formulas (not hard-coded) Point deductions: ! F3, F4, F5 all reference E2 instead of E3, E4, E5 respectively — the formula was not properly adapted for each row, so all four cells calculate the same region's revenue ! F6 uses SUMIF instead of SUM — the requirement specifies =SUM(F2:F5) for the grand total, not another SUMIF; furthermore F6's SUMIF also references E2, making it a duplicate of F2 rather than a grand total Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!F2 contains the expected formula: =SUMIF(Orders!$C$2:$C$81,E2,Orders!$H$2:$H$81) [PASS] Analysis!F6 contains the expected formula: =SUMIF(Orders!$C$2:$C$81,E2,Orders!$H$2:$H$81) [PASS] Analysis: all checked cells use formulas (F2, F3, F4, F5, F6). ------------------------------------------------------------------------ Requirement 4 — 12.00 / 12.00 (100.0%) ------------------------------------------------------------------------ IFERROR(VLOOKUP(...)) formula in I2:I81 to look up ProductName Feedback: + I2 contains the correct IFERROR(VLOOKUP(...)) formula + Formula verified at I2, I10, and I50 + All checked cells use formulas, not hard-coded values + Formula correctly references Products!$A$2:$D$6 with column index 2 and FALSE for exact match Automated checks: [PASS] Sheet 'Orders' found. [PASS] Orders!I2 contains the expected formula: =IFERROR(VLOOKUP(E2,Products!$A$2:$D$6,2,FALSE),"Unknown") [PASS] Orders: all checked cells use formulas (I2, I10, I50). ------------------------------------------------------------------------ Requirement 5 — 13.00 / 13.00 (100.0%) ------------------------------------------------------------------------ IF(XLOOKUP(...)) margin % formula in J2:J81 Feedback: + J2 contains the exact expected formula with XLOOKUP and IF + Formula verified at J2, J25, and J70 + All checked cells use formulas + Correctly handles 'N/A' not-found case and calculates (UnitPrice - StandardCost) / UnitPrice Automated checks: [PASS] Sheet 'Orders' found. [PASS] Orders!J2 contains the expected formula: =IF(XLOOKUP(E2,Products!$A$2:$A$6,Products!$D$2:$D$6,"N/A")="N/A","No Cost Data",(G2-XLOOKUP(E2,Products!$A$2:$A$6,Products!$D$2:$D$6,"N/A"))/G2) [PASS] Orders: all checked cells use formulas (J2, J25, J70). ------------------------------------------------------------------------ Requirement 6 — 11.00 / 13.00 (85.0%) ------------------------------------------------------------------------ INDEX/MATCH formula in H2 to find top rep by revenue, conditional formatting on F2:F5 Feedback: + H2 contains the exact expected INDEX/MATCH/MAXIFS formula + Conditional formatting rule exists on the Analysis sheet + Formula uses formulas, not hard-coded values Point deductions: ! Conditional formatting is applied to A2:N58 (the entire data area) rather than specifically to F2:F5 (the regional revenue figures) as required — this is a placement/scope issue ! The conditional formatting type is 'cellIs' which may not specifically highlight the highest value with green fill as required (top/bottom rule would be more appropriate) Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!H2 contains the expected formula: =INDEX(Orders!$D$2:$D$81,MATCH(MAXIFS(Orders!$H$2:$H$81,Orders!$D$2:$D$81,Orders!$D$2:$D$81),Orders!$H$2:$H$81,0)) [PASS] Analysis: all checked cells use formulas (H2). [PASS] 1 conditional formatting rule(s) on 'Analysis' (). ------------------------------------------------------------------------ Requirement 7 — 5.00 / 17.00 (29.0%) ------------------------------------------------------------------------ Pivot Table on Analysis sheet at A10 with Region rows, ProductCode columns, Sum of Revenue values; Clustered Bar Chart on Dashboard Feedback: + A BarChart exists on the Dashboard sheet (misspelled as 'Dashbaord') Point deductions: ! No pivot table found in the workbook — this is a major missing component worth significant points ! The chart title is 'Orders overview' instead of the required 'Revenue by Region and Product' ! The Dashboard sheet is misspelled as 'Dashbaord' ! Without a pivot table, the chart cannot be properly sourced from pivot table data as required Automated checks: [FAIL] No pivot tables found in the workbook. [PASS] BarChart found on 'Dashbaord'. ------------------------------------------------------------------------ Requirement 8 — 4.00 / 18.00 (22.0%) ------------------------------------------------------------------------ Performance tier summary with SUMIF in L2:L5, AVERAGE in L6, difference in M2:M5, nested IF/IFERROR tier in N2:N5 Feedback: + N2 contains IFERROR and IF functions as required + Formulas are present in L2:L5, L6, M2:M5, N2:N5 (not hard-coded) Point deductions: ! L2:L5 contain the tier IF/IFERROR formula instead of SUMIF formulas for regional revenue — the SUMIF requirement for L2:L5 is completely unmet ! L6 contains the tier IF/IFERROR formula instead of AVERAGE(L2:L5) — the AVERAGE requirement is unmet ! M2:M5 contain the tier IF/IFERROR formula instead of the difference (L - average) calculation — the vs. average difference requirement is unmet ! All cells L2:L6, M2:M5, N2:N5 appear to contain the same tier formula, suggesting the student pasted the same formula everywhere without differentiating the purpose of each column ! The circular reference in L2 (referencing itself) would cause an error in practice Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!N2 contains the expected formula: =IFERROR(IF(L2>$L$6*1.1,"Gold",IF(L2>$L$6*0.9,"Silver","Bronze")),"Error") [PASS] Analysis: all checked cells use formulas (L2, L3, L4, L5, L6, M2, M3, M4, M5, N2, N3, N4, N5). ======================================================================== Overall Assessment ======================================================================== This submission demonstrates partial understanding of Excel formulas but has significant execution errors across multiple requirements. Requirements 4 and 5 are completed perfectly with correct VLOOKUP/IFERROR and XLOOKUP/IF formulas. However, several critical issues bring the score down substantially: Requirement 1 has a hard- coded total instead of a SUM formula; Requirements 2 and 3 have formulas that don't properly adapt references across rows (all pointing to the same cell); Requirement 7 is missing the pivot table entirely and the chart has the wrong title; and Requirement 8 shows a fundamental misunderstanding where the same tier formula was pasted into all columns (L, M, N) instead of implementing SUMIF, AVERAGE, and difference calculations respectively. The student shows ability to write complex formulas when done correctly, but needs to practice filling formulas down with proper relative references and carefully reading multi-part requirements. Strengths: + Perfect implementation of IFERROR(VLOOKUP()) for product name lookup (Requirement 4) + Perfect implementation of IF(XLOOKUP()) for margin percentage calculation (Requirement 5) + Correct INDEX/MATCH/MAXIFS formula for top rep identification (Requirement 6) + Named range 'Revenue' was defined + Good workbook formatting with styled headers + A bar chart was created on the Dashboard sheet Areas for improvement: - Always use formulas instead of hard-coded values — H82 should be =SUM(Revenue), not a typed number - When copying formulas down a column, ensure relative references update correctly (e.g., B2 should become B3, B4, B5 in successive rows) - Read multi-part requirements carefully — each column in Requirement 8 had a distinct purpose (SUMIF, AVERAGE, difference, tier) that was not differentiated - Practice creating Pivot Tables — this was entirely missing from the submission - Ensure chart titles match the exact required text ('Revenue by Region and Product') - F6 should use SUM(F2:F5) not another SUMIF formula - Apply conditional formatting to the specific range requested (F2:F5) rather than the entire sheet area