Instructor Guide: Northwind Traders — Sales Analysis Workbook

• Typing the total revenue as a hard-coded number in H82 instead of writing =SUM(Revenue).
• Defining the named range after writing the SUM formula, causing the formula to still reference a cell range literal rather than the named range.
• Naming the range 'revenue' (lowercase) instead of 'Revenue', which may cause the check to fail depending on case-sensitivity settings.
• Calculating H2:H81 as =F2+G2 (addition) instead of =F2*G2 (multiplication).
• Filling the multiplication formula down only part of the way (e.g., H2:H50) so some rows are blank, making the SUM understated.
• Placing the SUM formula in H81 instead of H82, overwriting the last revenue row.
• Forgetting to add the 'Revenue' header in H1, which means the named range definition may accidentally include the header if the student highlights H1:H81.

• If a student is stuck: What does Excel need to know before it can evaluate =SUM(Revenue) — what object must exist first?
• If a student is stuck: How does Excel know which cells to include when you type a name instead of a cell address in a formula?
• If a student is stuck: If you haven't filled the formula all the way to row 81, how many order lines are you actually summing?
• If a student is stuck: Where in the ribbon can you create, view, and manage named ranges?

Step 1 – Add the header: Click cell H1 on the Orders sheet and type 'Revenue', press Enter. Step 2 – Enter the row formula: Click H2, type =F2*G2, press Enter. Step 3 – Fill down: Select H2, hover over the bottom-right corner until the fill handle appears, double-click to fill down to H81 (Excel auto-stops at the last data row). Verify that H81 contains =F81*G81. Step 4 – Create the named range: Select H2:H81. Go to Formulas ribbon > Define Name. In the Name field type 'Revenue' (exact casing). Scope should be Workbook. Click OK. Alternatively, select H2:H81, click the Name Box (top-left, shows the cell address), type 'Revenue', press Enter. Step 5 – Write the SUM: Click H82, type =SUM(Revenue), press Enter. Verify the result is a positive number consistent with the data.

Award full 8 points only when: (a) named range 'Revenue' exists and covers H2:H81, (b) H2:H81 all contain multiplication formulas, and (c) H82 contains =SUM(Revenue) or an equivalent that references the named range. Deduct 3 points if H82 contains the correct numeric value but it was typed (hard-coded) — award 5/8. Deduct 2 points if the correct SUM formula is present but uses a cell range literal (=SUM(H2:H81)) instead of the named range — award 6/8. Deduct 1 point if the named range exists but is spelled differently (e.g., 'revenue'). Award 0 if H82 is hard-coded AND no named range exists. A correct formula placed in H83 or elsewhere instead of H82 receives at most 4/8.

Why might a business analyst prefer to reference a named range like 'Revenue' in formulas rather than a cell range like H2:H81, especially when the dataset grows or columns are inserted?

• Named range scoped to the Orders sheet only (not Workbook): the formula =SUM(Revenue) may still work on the Orders sheet but will break if referenced from another sheet — accept for full credit but note the limitation.
• Student used =SUM(H2:H81) in H82 with a correctly defined named range: the named range check passes but the formula check may fail — award 6/8 and note the discrepancy.
• H2:H81 partially filled (e.g., only H2:H40): award partial credit proportionally on the row-formula component, but the SUM and named range can still receive their points if correctly structured.

• Using a relative reference for the data range: =COUNTIF(Orders!C2:C81,B2) without dollar signs, so the range shifts when filled down and later rows count wrong subsets.
• Hard-coding the region name as a string literal in the formula (e.g., =COUNTIF(Orders!$C$2:$C$81,"North")) instead of referencing column B.
• Typing region labels with extra spaces or wrong capitalisation (e.g., 'north', 'SOUTH') so COUNTIF finds zero matches.
• Placing the COUNTIF formulas in the wrong column (e.g., B2:B5 instead of C2:C5).
• Omitting the cross-sheet reference and writing =COUNTIF($C$2:$C$81,B2) on the Analysis sheet, which references Analysis!C not Orders!C.
• Forgetting to enter the region labels in B2:B5 and referencing empty cells, producing zeros.

• If a student is stuck: What happens to your data-range reference when you copy a formula one row down — which part of the address changes?
• If a student is stuck: When COUNTIF returns zero for every region, what's the first thing you'd check about the criteria argument?
• If a student is stuck: How do you tell Excel that the data you want to count is on a different sheet from where you're writing the formula?

Step 1 – Enter headers and labels: On the Analysis sheet, click A1 and type 'Region'. Click C1 and type 'Order Count'. Click B2 and type 'North', B3 'South', B4 'East', B5 'West'. Step 2 – Write the first COUNTIF: Click C2. Type =COUNTIF(Orders!$C$2:$C$81,B2) and press Enter. The dollar signs lock the data range; B2 is intentionally relative so it shifts when filled down. Step 3 – Fill down: Select C2. Double-click the fill handle or drag to C5. Verify C3 references B3 ('South'), C4 references B4 ('East'), C5 references B5 ('West'). Step 4 – Sanity check: The four counts in C2:C5 should sum to 80 (total order rows). A quick =SUM(C2:C5) elsewhere confirms this.

Award full 7 points when all four cells C2:C5 contain COUNTIF formulas referencing the correct cross-sheet range and the B-column criteria cells. Deduct 2 points if the data range is not absolute (formulas shift on fill). Deduct 2 points if criteria are hard-coded strings rather than B-column references. Deduct 1 point if the formula is correct but placed in column D or another column instead of C. A correct count typed as a value (not formula) receives 0 points for that cell. Award proportional partial credit (e.g., 2 correct out of 4 = ~3-4/7).

If the company added a fifth region called 'Central' mid-year, how would you update this COUNTIF summary with the least effort? What does that tell you about the value of referencing labels from cells rather than hard-coding them?

• Student writes the formula correctly but types region labels with trailing spaces (e.g., 'North '): COUNTIF returns 0. Check the B-column cells with =LEN() and deduct 1 point for the label error if the formula logic is otherwise correct.
• Student sums to more than 80 or less than 80: investigate whether the Orders data range was extended or truncated (e.g., C2:C82 accidentally includes a blank or extra row).
• Student uses COUNTIFS with a single condition instead of COUNTIF: functionally equivalent — award full credit.

• Swapping the second and third arguments of SUMIF (criteria_range, sum_range) — writing =SUMIF(Orders!$C$2:$C$81,Orders!$H$2:$H$81,E2) instead of the correct order.
• Forgetting absolute references on the criteria range or sum range so the ranges drift when filling down.
• Referencing the wrong column for the sum range (e.g., Orders!$F$2:$F$81 for Units instead of H for Revenue).
• Not completing the Revenue column (H) on Orders first, so all SUMIF results return 0.
• Using SUM instead of SUMIF for each region row, producing the same total for all four regions.
• Omitting F6 or using a hard-coded total rather than =SUM(F2:F5).
• Placing the formulas in column E instead of column F.

• If a student is stuck: SUMIF takes three arguments — what does each one represent, and which column on the Orders sheet holds the values you actually want to add up?
• If a student is stuck: If your four regional revenues are all identical, what does that suggest about which argument you may have confused?
• If a student is stuck: Why should the grand total in F6 match the value in Orders!H82 from Question 1?

Step 1 – Enter headers and labels on Analysis sheet: E1='Region', F1='Total Revenue'. E2='North', E3='South', E4='East', E5='West'. Step 2 – Write SUMIF in F2: Click F2. Type =SUMIF(Orders!$C$2:$C$81,E2,Orders!$H$2:$H$81) and press Enter. Argument 1 is the range to test (Region column), Argument 2 is the criteria (the label in E2), Argument 3 is the range to sum (Revenue column H). Step 3 – Fill down: Select F2, fill to F5. Confirm F3 references E3, etc., while the Orders ranges stay locked. Step 4 – Grand total: Click F6, type =SUM(F2:F5), press Enter. Step 5 – Cross-check: F6 should equal Orders!H82. Open Orders sheet, check H82, then return to Analysis and verify F6 matches.

Award full 13 points when F2:F5 all use SUMIF with the correct arguments and absolute data ranges, and F6 uses SUM. Deduct 3 points if the sum_range argument references Units (F) instead of Revenue (H) — the formula structure is correct but the wrong column is summed. Deduct 2 points for missing absolute references (relative references that happen to produce correct values in the filled range still lose these points because the formula is fragile). Deduct 2 points if F6 is hard-coded. A correct SUMIF in the wrong column (e.g., G2:G5) receives at most 6/13. Award 0 for fully hard-coded values.

The grand total in F6 should equal the total revenue in Orders!H82. If they differ, what are three possible explanations? How would you diagnose which one applies?

• Student uses SUMPRODUCT instead of SUMIF and gets correct results: award full credit — it is a valid alternative.
• Student references F2:F5 range for SUM in F6 but one of F2:F5 is hard-coded: dock points for the hard-coded cell but award the SUM formula points.
• Revenue column (H) on Orders is incomplete (Q1 dependency): the SUMIF formula may be structurally correct but produce a wrong answer — note the dependency issue and grade the formula structure, not the numeric result, deducting points only from Q1.

• Using TRUE (approximate match) as the fourth VLOOKUP argument instead of FALSE, which can return wrong product names if the table is not sorted.
• Forgetting to lock the lookup table range with absolute references, causing it to shift when filling down.
• Specifying the wrong column index number (e.g., 1 for ProductCode, 3 for Category, 4 for StandardCost) instead of 2 for ProductName.
• Wrapping IFERROR around the wrong expression or placing 'Unknown' inside the VLOOKUP rather than as the IFERROR fallback.
• Forgetting to include the IFERROR wrapper entirely.
• Placing the formula in column J instead of column I, or starting at I1 instead of I2.
• Typing the lookup table as Products!A2:D6 without dollar signs so rows shift on fill.

• If a student is stuck: VLOOKUP needs four pieces of information — what are they, and which one tells it whether to find an exact or approximate match?
• If a student is stuck: If you fill a formula down 80 rows and the lookup table reference is not locked, what happens to that reference in row 50?
• If a student is stuck: What is IFERROR's role here — when would the VLOOKUP actually fail on this dataset?

Step 1 – Add header: Click Orders!I1, type 'ProductName', press Enter. Step 2 – Write formula in I2: Click I2. Type =IFERROR(VLOOKUP(E2,Products!$A$2:$D$6,2,FALSE),"Unknown") and press Enter. Breakdown: lookup_value=E2 (ProductCode for this row), table_array=Products!$A$2:$D$6 (absolute), col_index=2 (ProductName is the second column), range_lookup=FALSE (exact match). IFERROR wraps the whole VLOOKUP; if it errors, return 'Unknown'. Step 3 – Fill down: Select I2, double-click fill handle to fill to I81. Step 4 – Verify: Spot-check a few rows — I2 should show the product name matching E2's code. Check that I81 is filled.

Award full 12 points when I2 contains a correctly structured IFERROR(VLOOKUP(...,FALSE),"Unknown") with absolute table reference and col_index=2, and the formula is filled to I81. Deduct 3 points for using TRUE instead of FALSE (wrong match type). Deduct 2 points for missing absolute references on the lookup table. Deduct 2 points for missing IFERROR wrapper. Deduct 1 point for wrong column index number (if the result still resolves, check manually). A correct formula in the right cell but not filled down (only I2 has the formula) loses 2 points. Hard-coded product names score 0.

Under what real-world circumstances would the VLOOKUP actually return an error and IFERROR's 'Unknown' fallback would trigger? Why is it good practice to include IFERROR even if you believe the dataset is clean?

• Student uses XLOOKUP instead of VLOOKUP: accept for full credit — XLOOKUP is a valid and arguably superior alternative; check that IFERROR or XLOOKUP's built-in not-found argument is used.
• Student uses col_index=2 but the Products table columns are in a different order than expected: verify the actual Products sheet column order before marking wrong.
• Formula filled only to I80 (missing row 81): deduct 1 point.

• Not locking the XLOOKUP lookup and return arrays with absolute references, causing them to shift when filling down.
• Using the wrong not-found sentinel (e.g., 0 or empty string instead of 'N/A'), breaking the IF test.
• Testing the wrong column in the IF condition (e.g., testing E2='N/A' instead of the XLOOKUP result).
• Calculating the margin incorrectly: using (UnitPrice - StandardCost) / StandardCost (markup) instead of / UnitPrice (margin).
• Calling XLOOKUP only once (in the IF test) and hard-coding the margin formula with a fixed cost, missing the second XLOOKUP call.
• Forgetting to fill the formula down to J81.
• Formatting column J as General instead of Percentage, so margins display as raw decimals (not a formula error, but worth mentioning).

• If a student is stuck: What value does XLOOKUP return when it can't find a match, and how does your IF formula detect that specific value?
• If a student is stuck: What is the mathematical difference between a markup percentage and a margin percentage — which denominator does each use?
• If a student is stuck: If you call XLOOKUP twice in one formula, is that a problem? Is there an Excel function that lets you store an intermediate result to avoid repeating it?

Step 1 – Add header: Click Orders!J1, type 'Margin %', press Enter. Step 2 – Write formula in J2: Click J2. Type the 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). Breakdown: First XLOOKUP call retrieves StandardCost; the IF tests whether it equals the sentinel "N/A". If true, display "No Cost Data". Otherwise, the second XLOOKUP retrieves StandardCost again for the margin math: (G2 - cost) / G2. Both XLOOKUP arrays are absolute. Step 3 – Alternative with LET (accept if submitted): =LET(cost,XLOOKUP(E2,Products!$A$2:$A$6,Products!$D$2:$D$6,"N/A"),IF(cost="N/A","No Cost Data",(G2-cost)/G2)). Step 4 – Fill down to J81. Step 5 – Optional formatting: Select J2:J81, apply Percentage format from Home ribbon.

Award full 13 points when J2 contains IF and XLOOKUP with correct sentinel, correct margin formula (divided by UnitPrice, not cost), absolute XLOOKUP arrays, and the formula is filled to J81. Deduct 3 points for dividing by StandardCost instead of UnitPrice. Deduct 2 points for missing absolute references. Deduct 2 points for using the wrong sentinel or not testing for 'N/A' (so 'No Cost Data' never appears). Deduct 1 point for not filling to J81. A correct formula using LET receives full credit. Accept IFERROR wrapping the whole expression as equivalent to the IF/XLOOKUP sentinel approach, but check that 'No Cost Data' displays correctly.

Why might a finance team prefer margin percentage (using UnitPrice as denominator) over markup percentage (using cost as denominator) when evaluating product profitability? How would getting this denominator wrong affect business decisions?

• Student uses VLOOKUP instead of XLOOKUP: award partial credit (up to 9/13) since XLOOKUP is explicitly required; check that the IF logic and margin math are correct and award those components.
• Student wraps the entire formula in IFERROR instead of using the IF/sentinel approach: if 'No Cost Data' displays correctly, award full credit — the outcome is equivalent.
• All products have valid StandardCosts in the dataset so 'No Cost Data' never actually appears: grade on formula structure, not on whether the branch is triggered.

• Using MAX instead of MAXIFS, which returns the single maximum revenue value across all rows rather than the maximum per-rep, leading to an incorrect or accidental result.
• Confusing the INDEX array and the MATCH lookup_array — e.g., putting the Rep range in MATCH instead of the Revenue range.
• Using MATCH with match_type=1 (approximate) instead of 0 (exact), which requires a sorted list and may return the wrong position.
• Applying Conditional Formatting to the wrong range (e.g., E2:E5 or F2:F6) instead of F2:F5.
• Choosing 'Highlight Cell Rules > Greater Than' manually rather than using 'Top 1' or a formula-based rule, resulting in incorrect logic.
• Forgetting the Conditional Formatting step entirely (partial credit applies).
• Placing the INDEX/MATCH result in H3 instead of H2.

• If a student is stuck: INDEX needs a range and a position number — what range do you want to pull a name from, and how does MATCH help you find the right position?
• If a student is stuck: If you just used MAX(Orders!H2:H81), what would you actually find — the maximum single order revenue or the maximum rep total revenue?
• If a student is stuck: How can you apply a Conditional Formatting rule that automatically highlights whichever cell is highest without hard-coding a threshold value?

Step 1 – Enter header: Click Analysis!H1, type 'Top Rep by Revenue'. Step 2 – Write INDEX/MATCH in H2: Click H2. Type =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)). Breakdown: MAXIFS(sum_range, criteria_range, criteria) — when criteria_range and criteria are the same range, MAXIFS computes per-rep maxima for every row, and the outer MAX function of MAXIFS returns the single highest value across all reps. MATCH then finds the row index of that maximum in the Revenue column with exact match (0). INDEX retrieves the Rep name at that row. Press Enter. Step 3 – Verify: The result should be a valid rep name. Cross-check by manually filtering the Orders sheet by that rep and summing their revenue. Step 4 – Conditional Formatting on F2:F5: Select F2:F5 on the Analysis sheet. Go to Home ribbon > Conditional Formatting > Top/Bottom Rules > Top 10 Items. Change the count to 1 and set fill to Green (custom: green background). Alternatively: Home > Conditional Formatting > New Rule > 'Format only top or bottom ranked values', set to Top 1, green fill. Click OK.

Award full 13 points: 9 points for a correct INDEX/MATCH formula in H2 (using any valid approach to find the top rep), 4 points for Conditional Formatting correctly applied to F2:F5 highlighting the maximum. Deduct 3 points for using MAX instead of MAXIFS if the result happens to be correct (lucky match) — the technique is wrong. Deduct 2 points for missing exact-match argument (0) in MATCH. Deduct 4 points if Conditional Formatting is absent entirely. Deduct 2 points if Conditional Formatting is applied to the wrong range. A correct rep name typed as a hard-coded value in H2 scores 0 for that component. Accept any formula approach (LARGE, AGGREGATE, helper column) that correctly identifies the top rep.

INDEX/MATCH is often described as more powerful than VLOOKUP. Based on this task, what can INDEX/MATCH do that VLOOKUP cannot? In what situations would you still prefer VLOOKUP?

• Two reps have identical maximum revenue (tie): MATCH returns the first occurrence. Accept this as correct — document that ties return the first matching rep.
• Student uses a helper column on the Orders sheet to compute per-rep totals and then applies a simple MAX/INDEX/MATCH: award full credit if the final result in H2 is formula-driven and correct.
• Student applies Conditional Formatting using a formula rule (=F2=MAX($F$2:$F$5)): award full Conditional Formatting points — this is more robust than the Top 1 rule approach.

• Placing the PivotTable source range incorrectly (e.g., only A1:G81 instead of A1:J81, omitting columns H-J).
• Placing the PivotTable top-left corner at A9 or A11 instead of exactly A10.
• Putting ProductCode in Rows and Region in Columns (swapped) rather than Region in Rows and ProductCode in Columns.
• Using Count of Revenue instead of Sum of Revenue in the Values area.
• Inserting the chart on the Analysis sheet instead of moving it to the Dashboard sheet.
• Choosing the wrong chart type (e.g., Column instead of Clustered Bar, or a Line chart).
• Not setting the chart title to exactly 'Revenue by Region and Product'.
• Linking the chart to a manual data range instead of the PivotTable, so it does not update dynamically.

• If a student is stuck: What is the difference between a Bar chart and a Column chart in Excel — which orientation does each use?
• If a student is stuck: How do you move a chart from the sheet where it was created to a different sheet?
• If a student is stuck: If ProductCode appears in Rows in your PivotTable instead of Columns, what would you drag and where to fix it?

Step 1 – Insert PivotTable: Click any cell on the Orders sheet. Go to Insert ribbon > PivotTable. In the dialog, confirm source is Orders!$A$1:$J$81 (adjust if needed to include all 10 columns). Choose 'Existing Worksheet', click the location field, then click Analysis!A10. Click OK. Step 2 – Configure PivotTable fields: In the PivotTable Fields pane, drag 'Region' to the Rows area. Drag 'ProductCode' to the Columns area. Drag 'Revenue' to the Values area — confirm it says 'Sum of Revenue' (right-click > Value Field Settings > Sum if it defaults to Count). Step 3 – Insert chart: Click inside the PivotTable. Go to Insert ribbon > Charts group > Bar Chart (the horizontal bars icon) > Clustered Bar (first option in the 2-D Bar section). A chart appears on the Analysis sheet. Step 4 – Move chart to Dashboard: Right-click the chart border > Move Chart. Select 'Object in' and choose Dashboard from the dropdown, or select 'New Sheet' and rename it — the task says to place it on the existing Dashboard sheet, so choose Object in > Dashboard. Click OK. Step 5 – Set chart title: Click the chart title placeholder and type 'Revenue by Region and Product'. Step 6 – Verify: The Dashboard sheet should contain one chart titled correctly, and the Analysis sheet PivotTable should start at A10.

Award full 17 points: 10 points for a correctly configured PivotTable (correct source, correct placement at A10, correct Row/Column/Value fields, Sum not Count), 7 points for a correctly placed Clustered Bar chart on the Dashboard with the correct title. Deduct 3 points for PivotTable at wrong cell (off by one row/column). Deduct 3 points for swapped Row/Column fields. Deduct 2 points for Count instead of Sum of Revenue. Deduct 3 points for chart on wrong sheet (Analysis instead of Dashboard). Deduct 2 points for wrong chart type (Column vs. Bar). Deduct 1 point for incorrect or missing chart title. If the PivotTable is entirely absent, award 0 for the whole question.

A Clustered Bar chart was specified here. What are the trade-offs between a Clustered Bar, a Stacked Bar, and a 100% Stacked Bar for comparing revenue across regions and products? When would each be most appropriate for a business audience?

• Student uses source range A1:G81 (omitting Revenue column H): PivotTable won't include Revenue — they must add the calculated field or fix the source. Deduct 4 points if Revenue values are absent.
• Student inserts a PivotChart directly (skipping a standalone PivotTable): award full PivotTable points if the underlying PivotTable exists behind the chart; award chart points normally.
• Chart type is '3-D Clustered Bar': acceptable — award full chart type points.
• Student creates a regular (non-pivot) bar chart by manually selecting PivotTable output cells: functionally similar visually but loses dynamic update capability — deduct 2 points.

• Forgetting to use an absolute reference on L6 in the M and N formulas, so the average shifts as formulas are filled down.
• Reversing the IF conditions — testing 90% before 110%, which causes some 'Gold' regions to be incorrectly classified as 'Silver'.
• Using >= instead of > in the threshold comparisons (acceptable mathematically, but watch for boundary cases).
• Computing the average in L6 as AVERAGE(Orders!H2:H81) (average of all 80 order lines) instead of AVERAGE(L2:L5) (average of the four regional totals).
• Duplicating the SUMIF formulas from F2:F5 instead of writing new ones in L2:L5, which works but may confuse graders — accept if correct.
• Omitting IFERROR wrapper on the N-column formulas.
• Placing the entire block in wrong columns (e.g., starting at J1 instead of K1).
• Using L6 as a relative reference in M2:M5 (=$L$6 needed but written as =L6), which shifts when filled.

• If a student is stuck: If you test whether revenue is greater than 90% of average before testing 110%, what happens to a region that qualifies for Gold?
• If a student is stuck: When you fill a formula from M2 down to M5, what happens to an unlocked reference like L6 — which row does it point to by row 5?
• If a student is stuck: AVERAGE of the four regional totals versus AVERAGE of all 80 order lines — would these give the same number? Why or why not?

Step 1 – Headers: Click Analysis!K1 type 'Region', L1 'Total Revenue', M1 'vs. Average', N1 'Tier'. Step 2 – Region labels: K2='North', K3='South', K4='East', K5='West'. Step 3 – SUMIF in L2: Click L2, type =SUMIF(Orders!$C$2:$C$81,K2,Orders!$H$2:$H$81), press Enter. Fill L2 down to L5. Step 4 – Average in L6: Click L6, type =AVERAGE(L2:L5), press Enter. Step 5 – Difference in M2: Click M2, type =L2-$L$6, press Enter. Fill M2 down to M5. Note the absolute reference on L6. Step 6 – Tier in N2: Click N2. Type =IFERROR(IF(L2>$L$6*1.1,"Gold",IF(L2>$L$6*0.9,"Silver","Bronze")),"Error"). Press Enter. The outer condition tests Gold threshold (>110% of average), the inner IF tests Silver (>90%), else Bronze. IFERROR catches any unexpected errors. Fill N2 down to N5. Step 7 – Verify: Confirm that the Gold region(s) have revenue clearly above 110% of L6; review L6 to ensure it is the average of four regional totals (not 80 raw rows).

Award full 18 points across components: 4 points for L2:L5 (SUMIF formulas, correct ranges); 2 points for L6 (AVERAGE of L2:L5); 3 points for M2:M5 (correct subtraction with absolute L6 reference); 9 points for N2:N5 (correct nested IF order, correct thresholds, absolute L6 reference, IFERROR wrapper). Deduct 2 points for reversed IF condition order (Silver tested before Gold). Deduct 2 points for missing absolute reference on L6 in the tier formulas. Deduct 1 point for missing IFERROR. Deduct 2 points if L6 averages all 80 order rows instead of the 4 regional totals (wrong average base). Hard-coded tier labels score 0 for column N. A correct SUMIF placed in the wrong column (e.g., column F duplicated here) still earns the SUMIF points if the formulas are correct.

The 'Gold/Silver/Bronze' tier thresholds here are 110% and 90% of the average. Who in a real business would decide these thresholds, and how might changing them (e.g., 120%/80%) affect decision-making and team motivation?

• Student references F2:F5 (from Q3) in L2:L5 as =F2 rather than writing new SUMIF formulas: award partial credit (2/4 for L column) since no SUMIF function is present, but the values are correct.
• All four regions fall in the same tier (plausible with real data): grade on formula correctness, not on variety of tier outcomes.
• Student uses IFS function instead of nested IF: award full credit — IFS is a valid alternative if the condition order is correct.
• IFERROR is applied around only the outer IF but not the inner IF: award IFERROR points — a single IFERROR wrapping the entire expression is correct.