The Excel test for finance and accounting jobs.

8 tasks finance Excel tests set

1. 1. Variance to budget

   Budget in B2, actual in C2.

   =C2-B2   and   =(C2-B2)/B2

   Variance is actual minus budget; the percentage divides by budget. Say which sign convention you use — for costs, a positive variance is bad news.

2. 2. Gross margin

   Revenue in B2, cost of sales in C2.

   =(B2-C2)/B2

   Margin divides by revenue, mark-up divides by cost. Tests often check you know the difference.

3. 3. Totals by account and month

   Transactions: Month in A, Account in B, Amount in D. Account codes down G, months across row 1.

   =SUMIFS($D:$D, $B:$B, $G2, $A:$A, H$1)

   Mixed references ($G2, H$1) let one formula fill the whole grid — the single most tested finance skill.

4. 4. Reconciliation: what's missing

   Bank references in B; ledger references on sheet Ledger, column B.

   =IF(COUNTIF(Ledger!B:B, B2)=0, "Missing", "Matched")

   Run it both ways (bank → ledger and ledger → bank) to find every unmatched item.

5. 5. Invoice ageing

   Due date in C2, as-of date in H1.

   =MAX(0, $H$1-C2)   then   =IF(D2>90,"91+",IF(D2>60,"61–90",IF(D2>30,"31–60","0–30")))

   Use a fixed as-of date rather than TODAY() so the report doesn't change every time it's opened.

6. 6. Year-to-date across months

   Monthly figures across B2:M2.

   =SUM($B2:B2)   (fill right)

   Anchor the start column only. Management packs run across columns, so you fill right, not down.

7. 7. Currency conversion

   Amount in B2, currency code in C2, a rates table with codes in K and rates in L.

   =B2*XLOOKUP(C2, $K$2:$K$10, $L$2:$L$10)

   Keep rates in one table and look them up; never type a rate into a formula.

8. 8. Rounding to the cent

   Quantity in B2, unit price in C2.

   =ROUND(B2*C2, 2)

   Formatting hides decimals but keeps them in totals. ROUND changes the stored value, which matters when totals must tie.