Cellwise Start free

Cellwise Hire · Financial analysis preset

Excel test for financial analysts

Candidates work in a monthly P&L against budget: operating profit, variance in amount and percent, growth on last year, year-to-date totals and margins, a forecast from a growth rate in a cell, and a loan payment with PMT. Then they debug a colleague's workbook. Every answer is checked on hidden versions with different numbers.

Create a free financial analyst testSee a sample task

Free for your first 3 candidates. The Financial analysis preset is preselected for you.

The test at a glance

Workbooks
Monthly P&L + Rep bonus sheet
Tasks
8 hands-on tasks at the level you pick
Levels
Basic, Intermediate or Advanced
Time
You choose; 20 to 40 minutes suits most
Grading
By result, on the visible sheet and 2 hidden versions

Good for: Financial analysts and FP&A analysts, Junior finance and commercial analysts, Management accountants who build monthly packs, Business analysts who own a budget.

What financial analysts actually do in Excel

Month-end for an analyst means a P&L export and a set of questions: where are we against budget, how does that compare with last year, what is the margin, and what does the rest of the year look like at the current growth rate. The answers go into a pack that the finance lead reads in two minutes, so every percentage has to be built the right way round.

Assumptions live in input cells. A growth rate, a loan amount, an interest rate, a "through" month for year to date. Change one and the whole model should follow. The analyst who types 1.5% into a forecast formula builds a model that only works this month.

What the test checks

The Financial analysis preset uses two workbooks: Monthly P&L (a company's monthly profit and loss for January to October: revenue, the revenue budget, cost of sales, operating expenses and revenue in the same month last year) and Rep bonus sheet (a quarterly sales and bonus sheet a colleague built. Several of its formulas are broken: find what is wrong and fix them). Here is what that covers for financial analysts, with the real task names and the level each one appears at.

Profit and variance to budget

Operating profit down the column, variance as actual minus budget so a positive number means ahead, months over budget where a month exactly on budget does not count, and variance divided by budget rather than by actual.

  • Operating profitBasic
  • Variance vs budgetBasic
  • Months over budgetBasic
  • Variance % vs budgetIntermediate

Growth, running totals and margins

Growth on the same month last year, a year-to-date running total with a locked start ($B$2:B2), the margin for the whole period as total profit over total revenue (not an average of monthly margins), and monthly margins with a blank for the month the business was closed.

  • Year-over-year growthIntermediate
  • Year-to-date revenueIntermediate
  • Year-to-date operating marginBasic
  • Monthly operating marginAdvanced

Forecasts and year to date

Next month from the last actual and a growth rate in a cell, three months ahead compounding with ^, and year-to-date variance through a month typed in a cell, built with SUMIFS and "<="&O1. Advanced level for the last two.

  • Next month forecastIntermediate
  • Three-month forecastAdvanced
  • Year-to-date variance %Advanced

Loan payment

A monthly payment with PMT from a loan amount, a yearly rate and a term in years: the rate divided by 12, the term times 12, and the sign handled so the payment shows as a positive number.

  • Loan paymentAdvanced

Debugging a colleague's workbook

The second workbook is a quarterly sales and bonus sheet someone else built: a total that stops a row short, an average divided by a fixed 10, #DIV/0! on new rows, a threshold typed into a formula, a MATCH without its match type, and a COUNTIF that tries to compare two columns. The kind of review work that happens before a model goes to the CFO.

  • Fix the totalBasic
  • Fix the averageBasic
  • Fix the #DIV/0! errorsIntermediate
  • Make the count follow K2Intermediate
  • Fix the top repAdvanced
  • Fix the target countAdvanced
  • Fix the averageAdvanced
Every task in this test, by level
Every task in the Financial analysis preset, by level and workbook
LevelMonthly P&LRep bonus sheet
BasicOperating profit
Variance vs budget
Months over budget
Year-to-date operating margin
Fix the bonus column
Fix the total
Fix "Hit target?"
Fix the average
IntermediateVariance % vs budget
Year-over-year growth
Year-to-date revenue
Next month forecast
Fix the manager lookup
Fix the East total
Fix the #DIV/0! errors
Make the count follow K2
AdvancedMonthly operating margin
Loan payment
Three-month forecast
Year-to-date variance %
Use the rate table
Fix the top rep
Fix the target count
Fix the average

Each test uses one level: four tasks from each workbook.

Which level to choose

A real task from this test

This is one of the tasks in the Financial analysis preset at the Advanced level, on the sheet candidates see. The answers below were run through the same grader that marks real tests: on this sheet, and on two hidden versions with different data and an extra row inserted.

Sample task · Monthly P&L · Advanced

Three-month forecast (in L7, filled down to L9). Forecast revenue 1, 2 and 3 months after the last month of actuals, growing by the monthly rate in L5 and compounding each month. The months ahead are in K7:K9. One formula, filled down.

RowABCDEFGHIJKL
1Month endingRevenueBudgetCost of salesOperating expensesRevenue last yearOperating profitOperating marginLoan amount$50,000.00
2Jan 31, 2026$84,200.00$80,000.00$33,700.00$38,100.00$76,500.00$12,400.00Annual interest rate6%
3Feb 28, 2026$79,600.00$82,000.00$31,800.00$37,400.00$71,200.00$10,400.00Term (years)5
4Mar 31, 2026$91,300.00$88,000.00$36,500.00$39,200.00$80,100.00$15,600.00Monthly loan payment
5Apr 30, 2026$0.00$45,000.00$0.00$21,500.00$83,900.00-$21,500.00Monthly growth rate2%
6May 31, 2026$95,700.00$92,000.00$38,300.00$40,100.00$86,400.00$17,300.00Months aheadForecast revenue
7Jun 30, 2026$90,400.00$94,000.00$36,200.00$39,900.00$88,800.00$14,300.001
8Jul 31, 2026$86,900.00$90,000.00$34,800.00$39,500.00$84,700.00$12,600.002
9Aug 31, 2026$98,200.00$93,000.00$39,300.00$40,600.00$87,300.00$18,300.003
10Sep 30, 2026$102,500.00$97,000.00$41,000.00$41,800.00$92,600.00$19,700.00
11Oct 31, 2026$99,800.00$100,000.00$39,900.00$41,200.00$95,100.00$18,700.00
Answers to the sample task, what each returns on the visible sheet, and the hidden checks
Formula in L7Sheet they seeHidden checksResult
=$B$11*(1+$L$5)^K7Last actual and growth rate locked, months ahead as the power 3 of 3 rows right right Right on bothSolvedUsed locked references ($) correctly
=$B$11*(1+0.02)^K7The growth rate typed in 3 of 3 rows right right Wrong on new numbersNot solvedRight on the visible data only. Typed the number 0.02 into the formula instead of pointing to L5
=$B$11*(1+$L$5*K7)Simple growth instead of compounding 1 of 3 rows right wrongNot needed: already wrongNot solved
=B11*(1+L5)^K7No $ at all 1 of 3 rows right wrongNot needed: already wrongNot solvedMissing $: the first formula breaks when it is filled down
  • =$B$11*(1+0.02)^K7 Right on today's sheet, because L5 says 2%. Change the growth assumption and the forecast stays where it is, which is the opposite of what a forecast is for. The hidden version uses a different rate and a different last month.
  • =$B$11*(1+$L$5*K7) Month 1 is right, then it falls behind: 3 months at 2% is 6.12% growth when compounded, not 6%. Small here, but it is the kind of difference that makes a model disagree with the one the finance lead built.
  • =B11*(1+L5)^K7 The first row is right. Filled down, B11 slides to the empty B12 and B13 and L5 slides to the heading below it, so month 2 shows #VALUE! and month 3 forecasts zero.
Row by row
Each answer's result in every row of the sample
Row=$B$11*(1+$L$5)^K7=$B$11*(1+0.02)^K7=$B$11*(1+$L$5*K7)=B11*(1+L5)^K7
7$101,796.00$101,796.00$101,796.00$101,796.00
8$103,831.92$103,831.92$103,792.00 (wrong)#VALUE! (wrong)
9$105,908.56$105,908.56$105,788.00 (wrong)$0.00 (wrong)

Grading is by result, so =$B$11*POWER(1+$L$5,K7) passes too. The hidden checks change the growth rate, the revenue and the number of rows, so only a formula that reads the assumption cell and the last row passes.

Results calculated by the Cellwise formula engine. The hidden versions stay on our server, so this page shows only whether each answer passed them.

See the screen your financial analyst candidates work in: try a 3-minute sample test and read the report you would get. No signup.

What the report shows

When a candidate finishes, you get an overall score and every task: solved or not, the exact formula they wrote, the time spent and the number of tries, plain-English notes, and a replay of every edit with the sheet as it looked at each step. For financial analysts, two things are worth a look:

You can also add 8 multiple-choice knowledge questions at the same level, scored separately. Candidates never see their score.

What this test does not cover

Pricing

Free for your first 3 candidates, no card needed. Then Starter is $79 a month or $790 a year for 25 completed candidates a month, and Agency is $199 a month or $1,990 a year for unlimited candidates. Only candidates who complete a test count.

Founding price: locked for as long as you stay subscribed (first 10 customers)

Create a free financial analyst testCompare plans

FAQ

What Excel skills does a financial analyst need?

Variance to budget in amount and percent, growth rates, SUMIFS and SUMPRODUCT for year-to-date and conditional totals, margins with a divide-by-zero guard, absolute references so a model fills correctly, forecasts driven by assumption cells, finance functions such as PMT, exact and approximate lookups, and the habit of checking other people's formulas before trusting their output.

Which level should I choose for a financial analyst?

Basic suits interns and finance assistants. Intermediate suits most financial analyst roles. Choose Advanced for FP&A and senior analysts, because the loan payment, compounding forecast and year-to-date-through-a-month tasks only appear at that level.

Does it test NPV and IRR?

No. The test covers budget variance, growth, margins, forecasting from a growth rate, PMT and debugging. If discounted cash flow is central to the role, add an NPV or IRR question in the interview.

How long should a financial analyst Excel test take?

Each test has 8 tasks across two workbooks. We suggest 20 minutes for Basic, 30 for Intermediate and 40 for Advanced, plus about 8 minutes if you add the optional knowledge-check questions. You can set anything from 5 to 120 minutes.

Is it fair to give finance candidates an Excel test?

A short test that reflects the real work is a common part of hiring. Give every candidate the same test and time, follow local hiring law, and keep tests job-related.

Related

Looking to learn Excel yourself? Start the free Cellwise course.