Excel Supreme
Search

Project track · Project 4 of 4 Paid

Interview assessment

Report a quarter's revenue, its gross, and the money the discounts cost.

Advanced

The brief

  1. The file

    interview-assessment.xlsx holds one sheet, Orders, with twelve orders booked in the first quarter. Columns A to F are the order number, the date, the rep, the region, the units and the unit price. Column G is the discount as a fraction. Column H is Net, a formula that multiplies units by price, takes the discount off and rounds to two decimals.

  2. The work

    You are doing the take-home half of an interview. A workbook lands, and you have twenty minutes to say what the quarter was worth and who produced it. Rows 15 to 18 are a report block for revenue, order count, mean order and largest order. Everything below them is a question an interviewer would ask aloud, and the sheet answers each one without a new helper column.

  3. What the interviewer checks

    The question is whether you notice that Net is already net. Summing the discount column tells you nothing about revenue; the money given away is what the gross would have been minus what was booked. Two of the eight tasks need exactly that distinction.

interview-assessment.xlsx · Orders

screen 1 of 8 · answer in the bar

What net revenue did the quarter book? B15 holds it.

Download the dataset (.xlsx) It opens in Excel, Sheets or LibreOffice.

Preview the sheet
ABCDEFGH
1OrderDateRepRegionUnitsUnit priceDiscountNet
210012024-01-08ChenNorth40300.11080
310022024-01-15DiazSouth254001000
410032024-01-22DiazNorth308202460
510042024-02-05OkonkwoEast12550.05627
610052024-02-12ChenNorth50220.15935
710062024-02-19ReyesSouth18900.021587.6
810072024-02-26OkonkwoWest60150900
910082024-03-04DiazNorth20700.081288
1010092024-03-11ChenEast35260910
1110102024-03-18ChenNorth2837.50.2840
1210112024-03-25ReyesSouth16450.1648
1310122024-04-01ChenWest453201440
14
15Revenue13715.6
16Orders12
17Mean order1142.97
18Largest2460

The sheet computes H2 =ROUND(E2*F2*(1-G2),2), H3 =ROUND(E3*F3*(1-G3),2), H4 =ROUND(E4*F4*(1-G4),2), H5 =ROUND(E5*F5*(1-G5),2), H6 =ROUND(E6*F6*(1-G6),2), H7 =ROUND(E7*F7*(1-G7),2), H8 =ROUND(E8*F8*(1-G8),2), H9 =ROUND(E9*F9*(1-G9),2), H10 =ROUND(E10*F10*(1-G10),2), H11 =ROUND(E11*F11*(1-G11),2), H12 =ROUND(E12*F12*(1-G12),2), H13 =ROUND(E13*F13*(1-G13),2), B15 =ROUND(SUM(H2:H13),2), B16 =COUNTA(B2:B13), B17 =ROUND(AVERAGE(H2:H13),2), B18 =MAX(H2:H13)

What it does

The Net column already has the discount applied, so revenue is SUM of H and nothing else. Gross revenue multiplies units by price row by row, which is SUMPRODUCT, and the money given away is that gross minus the net you just summed. That is why the answer starts with SUMPRODUCT and ends by subtracting B15.

COUNTA over the date column counts orders, AVERAGE divided by twelve gives the mean order, and MAX names the single best one. Every other task works on a subset of those columns: filter by region or by rep, then add.

Syntax

=ROUND(SUM(H2:H13),2), =ROUND(SUMPRODUCT(E2:E13,F2:F13),2)-B15

Worked example

Common errors

ErrorWhat it means and how to fix it
Summing the discount columnG holds fractions, not money. Adding 0.1, 0.05 and 0.15 gives 0.73, which is not the 744.40 the discounts cost.
Adding gross to netSUMPRODUCT of units and price is revenue before discounts. It is 14460, not the 13715.60 that was booked.

At work

A take-home assessment and a real quarterly review ask the same thing: what came in, from whom, and what did we give up to get it. Gross, net and discount are three different numbers and confusing any two of them is the most common spreadsheet error in commercial reporting.