8 tasks | 100 points total
| # | Pts | Location | Requirement | Automated checks |
|---|---|---|---|---|
| 1 | 8 | Orders!H82 |
On the Orders sheet, first define a named range called 'Revenue' that covers H2:H81. Then in cell H1 type the header 'Revenue', and in cells H2:H81 calculate each order line's revenue as Units × UnitPrice (columns F and G). Finally, in cell H82 of the Orders sheet, use SUM with the named range 'Revenue' to compute total revenue across all 80 order lines. (Skills: sum, named_ranges) | sheet exists: 'Orders' named range: 'Revenue' formula in cell: Orders!H82 uses SUM calculated, not typed: Orders!H82 |
| 2 | 7 | Analysis!C2, C3, C4, C5 |
On the Analysis sheet, in cell B2 enter the label 'North', B3 'South', B4 'East', B5 'West' in column B (rows 2–5). In cell A1 enter 'Region' and in cell C1 enter 'Order Count'. Then in cell C2 of the Analysis sheet, write a COUNTIF formula that counts how many orders in Orders!C2:C81 belong to the region listed in cell B2. Copy or adapt the formula to cells C3, C4, and C5 for the remaining regions. (Skills: countif) | sheet exists: 'Analysis' formula in cell: Analysis!C2 uses COUNTIF formula in cell: Analysis!C5 uses COUNTIF calculated, not typed: Analysis!C2, C3, C4, C5 |
| 3 | 12 | Analysis!F2, F3, F4, F5, F6 |
On the Analysis sheet, in cell E1 enter 'Region', F1 enter 'Total Revenue'. In cells E2:E5 list the four regions (North, South, East, West). Then in cell F2 of the Analysis sheet, write a SUMIF formula that sums the Revenue column (Orders!H2:H81) for all orders whose Region (Orders!C2:C81) matches the region in cell E2. Adapt the formula for F3, F4, and F5. In cell F6, calculate the grand total of F2:F5 using SUM. (Skills: sumif) | sheet exists: 'Analysis' formula in cell: Analysis!F2 uses SUMIF formula in cell: Analysis!F6 uses SUM calculated, not typed: Analysis!F2, F3, F4, F5, F6 |
| 4 | 12 | Orders!I2 |
On the Orders sheet, in cell I1 type the header 'ProductName'. In cells I2:I81, write a formula using IFERROR wrapped around VLOOKUP that looks up the ProductCode in column E against the Products reference table (Products!$A$2:$D$6) and returns the ProductName (column 2 of that table). If the lookup fails for any reason, display 'Unknown'. Fill the formula down to I81. (Skills: vlookup, iferror) | sheet exists: 'Orders' formula in cell: Orders!I2 uses IFERROR, VLOOKUP calculated, not typed: Orders!I2, I10, I50 |
| 5 | 13 | Orders!J2 |
On the Orders sheet, add a header 'Margin %' in cell J1. In cell J2, write a formula that first uses XLOOKUP to retrieve the StandardCost for the order's ProductCode (column E) from the Products sheet (look in Products!$A$2:$A$6, return from Products!$D$2:$D$6, and use 'N/A' as the not-found value). Then calculate the margin percentage as (UnitPrice − StandardCost) / UnitPrice. Wrap the entire expression in an IF so that if the XLOOKUP returns the text 'N/A' the cell displays 'No Cost Data', otherwise it shows the decimal margin. Fill the formula down to J81. (Skills: xlookup, if, nested_if) | sheet exists: 'Orders' formula in cell: Orders!J2 uses XLOOKUP, IF calculated, not typed: Orders!J2, J25, J70 |
| 6 | 13 | Analysis!H2 |
On the Analysis sheet, in cell H1 enter 'Top Rep by Revenue'. In cell H2, use an INDEX/MATCH formula to find the name of the sales representative (Orders!D2:D81) who generated the single highest total revenue. Because each Rep appears multiple times, your formula should use MATCH with the MAXIFS function to find the position of the maximum per-rep revenue entry, then INDEX to return that rep's name. After writing the formula, apply Conditional Formatting to the range F2:F5 (the regional revenue figures) so that the cell with the highest value is highlighted with a green fill. (Skills: index_match, conditional_formatting) | sheet exists: 'Analysis' formula in cell: Analysis!H2 uses INDEX, MATCH calculated, not typed: Analysis!H2 conditional formatting on 'Analysis' |
| 7 | 17 | Analysis!A10 |
On the Analysis sheet, insert a Pivot Table using the Orders sheet (columns A through J, rows 1–81) as its source. Place the Pivot Table with its top-left corner at cell A10. Configure the Pivot Table so that: Rows show Region, Columns show ProductCode, and Values show the Sum of Revenue (column H). Then on the Dashboard sheet, insert a Clustered Bar Chart that visualises the Pivot Table data (Region × ProductCode revenue breakdown). Give the chart the title 'Revenue by Region and Product'. (Skills: pivot_table, charts) | pivot table present on 'Analysis' chart present: BarChart on 'Dashboard' |
| 8 | 18 | Analysis!L2, L3, L4, L5, L6, M2, M3, M4, M5, N2, N3, N4, N5 |
On the Analysis sheet, build a performance tier summary starting at cell K1. In K1 enter 'Region', L1 enter 'Total Revenue', M1 enter 'vs. Average', N1 enter 'Tier'. In K2:K5 list the four regions (North, South, East, West). In L2:L5, use SUMIF to calculate each region's total revenue from Orders!C2:C81 and Orders!H2:H81. In cell L6, compute the average of L2:L5 using AVERAGE. In M2:M5, calculate the difference between each region's revenue (column L) and the overall average in L6 (use an absolute reference). Finally, in N2:N5, write a nested IF formula that assigns a tier label based on each region's revenue: if revenue is greater than 110% of the average (L6) display 'Gold', if revenue is greater than 90% of the average display 'Silver', otherwise display 'Bronze'. Wrap each nested IF in IFERROR to display 'Error' if the calculation fails. (Skills: nested_if, sumif, iferror) | sheet exists: 'Analysis' formula in cell: Analysis!L2 uses SUMIF formula in cell: Analysis!L6 uses AVERAGE formula in cell: Analysis!N2 uses IFERROR, IF calculated, not typed: Analysis!L2, L3, L4, L5, L6, M2, M3, M4, M5, N2, N3, N4, N5 |
Checks listed as automated are verified programmatically against the submitted workbook. Requirements without automated checks are assessed by review.
=SUM(Revenue)=COUNTIF(Orders!$C$2:$C$81,B2)=SUMIF(Orders!$C$2:$C$81,E2,Orders!$H$2:$H$81)=IFERROR(VLOOKUP(E2,Products!$A$2:$D$6,2,FALSE),"Unknown")=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)=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))=IFERROR(IF(L2>$L$6*1.1,"Gold",IF(L2>$L$6*0.9,"Silver","Bronze")),"Error")