Lookup, conditional logic, summarisation, and charting over a year of regional sales orders.
Download the starter workbook and complete each of the eight tasks in the exact sheet and cell specified. All answers must be formula-driven — typing a number directly into a cell will not earn credit even if the value is correct. Where a formula must be filled down a column, every cell in that range is subject to grading. Submit your completed .xlsx file through the course portal before the deadline; late submissions will not be accepted.
You have just joined the analytics team at Northwind Traders, a specialty foods distributor serving four regional markets. Management needs a clear picture of sales performance by region, product, and representative so they can allocate resources for the upcoming quarter. Your job is to transform the raw order data into a structured analysis workbook and a visual dashboard that executives can read at a glance.
1. 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. (8 pts)
=SUM(Revenue)Each row in H2:H81 holds =F2*G2 (filled down). The named range 'Revenue' covers H2:H81. H82 sums that named range.
2. 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. (7 pts)
=COUNTIF(Orders!$C$2:$C$81,B2)COUNTIF scans the Region column of the Orders sheet and counts rows matching the label in column B. Absolute reference on the data range allows fill-down.
3. 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. (12 pts)
Hint: Make sure the Revenue helper column (H) on the Orders sheet is complete before writing this formula.
=SUMIF(Orders!$C$2:$C$81,E2,Orders!$H$2:$H$81)SUMIF evaluates the Region column against the label in E2 and accumulates matching Revenue values. Absolute references on both data ranges allow fill-down. F6 totals F2:F5.
4. 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. (12 pts)
Hint: Use FALSE as the last VLOOKUP argument for an exact match, and lock the lookup table range with absolute references.
=IFERROR(VLOOKUP(E2,Products!$A$2:$D$6,2,FALSE),"Unknown")VLOOKUP finds the ProductCode from column E in the Products table and returns the second column (ProductName). IFERROR catches any missing codes and substitutes 'Unknown'. FALSE ensures exact match.
5. 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. (13 pts)
Hint: You can call XLOOKUP twice — once in the IF test and once in the margin calculation — or nest it in a LET function if you prefer.
=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)XLOOKUP retrieves the StandardCost using an exact match. The outer IF tests whether the result is the sentinel 'N/A'. If not found, the cell shows 'No Cost Data'; otherwise the margin formula (UnitPrice − StandardCost) / UnitPrice is evaluated.
6. 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. (13 pts)
Hint: Think about how MAXIFS can be used with the same range for both the criteria range and criteria values to find the overall maximum. Then use that maximum value inside MATCH.
=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))MAXIFS computes the maximum revenue for each rep when matched against itself, effectively returning the global maximum revenue value. MATCH finds the row of that maximum in the Revenue column, and INDEX retrieves the corresponding Rep name. Conditional Formatting on F2:F5 visually highlights the top regional revenue.
7. 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'. (17 pts)
Hint: Insert the Pivot Table from the Insert ribbon. After building it, select the Pivot Table, then Insert > Chart and choose Clustered Bar. Move the chart to the Dashboard sheet using 'Move Chart'.
A Pivot Table with Region in Rows, ProductCode in Columns, and Sum of Revenue as Values summarises multi-dimensional sales data. A Bar Chart on the Dashboard sheet renders the cross-tabulation visually for management.
8. 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. (18 pts)
Hint: Lock the reference to the average cell (L6) with $ signs so it does not shift when you fill the tier formula down. Think carefully about the order of your IF conditions — test the highest threshold first.
=IFERROR(IF(L2>$L$6*1.1,"Gold",IF(L2>$L$6*0.9,"Silver","Bronze")),"Error")L2:L5 uses SUMIF to sum revenue per region. L6 averages those four totals. M2:M5 subtracts the average from each region's total using an absolute reference on L6. N2:N5 applies a nested IF comparing each revenue to 110% and 90% of the average to assign Gold, Silver, or Bronze tiers. IFERROR protects against unexpected errors.