Financial Analyst Excel Test: Tasks and Practice

A financial analyst Excel test checks whether you can turn financial data into accurate answers quickly: growth rates, margins, totals by account, budget variances and lookups from a table. Many firms use one for analyst, FP&A and finance associate roles, sometimes alongside a case study. This guide shows the tasks that come up most, works each one on a small profit and loss table and explains how to prepare.

What a financial analyst Excel test looks like#

Most tests give you a dataset, often a small profit and loss (P&L) statement, sales data or a budget, and a list of tasks against a timer. Some run in real Excel; others in a browser spreadsheet that checks answers automatically. Advanced versions add a short model or a written recommendation.

The guide to what Excel assessments test and how they're scored explains the common formats. Accounting-focused tests overlap a lot; see the Excel test for accountants for reconciliations and aging.

The examples use this P&L data in A1:D7:

COGS is cost of goods sold; Opex is operating expenses.

Task 1: percent change#

Percent change compares a new value with an old one. The formula is (new − old) ÷ old:

=(C3-C2)/C2

Revenue grew from 120,000 in Q1 to 132,000 in Q2, so the result is 0.10, or 10% once formatted as a percentage (Ctrl+Shift+%; Mac: Control+Shift+%). Common mistakes are dividing by the new value and multiplying by 100 on top of percentage format.

Task 2: totals by account and period with SUMIFS#

SUMIFS adds the values that meet every condition. The range to add comes first:

=SUMIFS(C2:C7,A2:A7,"Revenue")

returns 252,000, actual revenue for both quarters. Add a second condition for one period: =SUMIFS(C2:C7,A2:A7,"COGS",B2:B7,"Q2") returns 58,000. In a test, point the conditions at cells holding the account and quarter, so one formula fills a whole summary table. See the SUMIFS function guide. If the task asks for only one condition, SUMIF works too.

Task 3: budget vs actual variance#

Variance is actual minus budget. Variance % divides that by the budget:

The formulas are =C3-D3 and =(C3-D3)/D3. Interpretation matters: revenue 7,000 above budget is favourable, but Opex 1,500 above budget is unfavourable. Say so in a label or a note, because a positive number isn't always good news. One clean way is a Status column: =IF(C3>=D3,"Favourable","Unfavourable") for revenue lines and the opposite test for cost lines. To total the full-year variance for one account, combine two SUMIFS: =SUMIFS(C2:C7,A2:A7,"Opex")-SUMIFS(D2:D7,A2:A7,"Opex") returns -500, because Opex was 2,000 under budget in Q1 and 1,500 over in Q2.

Task 4: margins#

Gross profit is revenue minus COGS, and gross margin divides gross profit by revenue. For Q2:

=(C3-C5)/C3

returns about 56.1% ((132,000 − 58,000) ÷ 132,000). Operating profit subtracts Opex as well: 132,000 − 58,000 − 34,500 = 39,500, an operating margin of about 29.9%. Compare the two quarters as well: Q1's gross margin is (120,000 − 54,000) ÷ 120,000 = 55%, so margin improved by about a point while revenue grew 10%. Spotting and stating a change like that is often what separates a good test answer from a correct one.

Task 5: lookups with INDEX MATCH#

Finance data often comes as a grid of accounts by period. To pull one figure, use INDEX with two MATCH functions. With accounts in A11:A13, quarters in B10:C10 and values in B11:C13:

=INDEX(B11:C13,MATCH("Opex",A11:A13,0),MATCH("Q2",B10:C10,0))

The first MATCH finds the Opex row, the second finds the Q2 column, and INDEX returns the value where they meet: 34,500 for the same figures laid out as a grid. The 0 asks for an exact match. See the INDEX function guide, the MATCH function guide and the full guide to INDEX MATCH. In Excel 2021 or Microsoft 365, XLOOKUP is an alternative for one-way lookups.

Task 6: growth rates, NPV and loan payments#

Some tests add finance functions. Know these three:

• CAGR (compound annual growth rate): =(520000/400000)^(1/3)-1 returns about 9.1% for revenue growing from 400,000 to 520,000 over three years.
• NPV: =NPV(8%,3000,4000,5000)-10000 returns about 176.29. NPV discounts future cash flows; the initial investment at time 0 is subtracted separately, because NPV assumes its first value comes one period from now.
• PMT: =PMT(6%/12,360,-400000) returns about 2,398.20, the monthly payment on a 400,000 loan at 6% over 30 years.

Keep rates and assumptions in labelled input cells and lock them with $ (F4 on Windows; ⌘T on Mac). Graders and interviewers look for this. For other techniques analysts are expected to know, see advanced Excel skills for jobs.

How to prepare for a financial analyst Excel test#

Practise each task above on small tables until it takes a couple of minutes. Timing matters: if one task stalls you for more than a few minutes, move on and come back, because the easy points later in the test count just as much. Then work on habits that protect accuracy under time pressure: label every answer, keep inputs separate, round only where asked, and check that totals tie out (for example, the quarters should add to the full-year figure). If the role leans towards data work, the data analyst Excel test guide covers cleaning and summarising tasks too.

Practise it#

Start with percentages and % change, then practise SUMIF totals by group and INDEX MATCH lookups in a live spreadsheet: you type the formula, and it checks it instantly. When those feel quick, take the timed finance Excel mock test to practise under real conditions.