Practice tests ยท By job role
Excel test for financial analysts
Growth rates, weighted averages, discounting, two-way lookups, ranking and period-end dates are the skills financial analysts are commonly asked to show.
Skills commonly tested for financial analysts
- Growth and percent change. Year-over-year growth, CAGR and percent of total, with the right percent format and a locked total cell.
- Weighted figures. A weighted average price or return with SUMPRODUCT divided by the total weight.
- Discounting. NPV and IRR, including the detail that NPV treats its first cash flow as one period away, so a time-zero investment is added outside it.
- Two-way lookups. Finding a figure by row label and column header with INDEX and two MATCH functions.
- Guarding ratios. Margin and ratio columns that handle a zero denominator on purpose rather than showing an error.
- Period dates and ranking. Quarter-end dates with EOMONTH, and ranking divisions or products with RANK.EQ.
8 sample tasks with answers
1. Calculate year-over-year revenue growth, shown as a percent to one decimal place.
| A | B | C | |
|---|---|---|---|
| 1 | Revenue 2025 | Revenue 2026 | Growth |
| 2 | 1200 | 1380 | 15.0% |
The increase is 1,380 minus 1,200, which is 180, and 180 divided by the starting 1,200 is 0.15. The 0.0% format shows 15.0%.
2. Find the compound annual growth rate when 1,000 grows to 1,500 over 4 years.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | Years | |
| 2 | 1000 | 1500 | 4 | 10.67% |
Divide the end value by the start, raise it to the power of one over the years, and subtract 1. 1.5 to the power of 0.25 is about 1.1067, so the rate is 10.67%.
3. Find the weighted average price for the three purchases.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Lot | Units | Price | ||
| 2 | A | 100 | 10 | 11.5 | |
| 3 | B | 50 | 12 | ||
| 4 | C | 50 | 14 |
SUMPRODUCT multiplies each quantity in B2:B4 by its price in C2:C4 and adds the products: 1,000 plus 600 plus 700 is 2,300. Dividing by the 200 total units gives 11.5.
4. Find the net present value of a 400 investment that returns 100, 200 and 300 over three years at a 10% discount rate.
| A | B | |
|---|---|---|
| 1 | Rate | 0.1 |
| 2 | Year 0 | -400 |
| 3 | Year 1 | 100 |
| 4 | Year 2 | 200 |
| 5 | Year 3 | 300 |
| 6 | 81.59 |
NPV discounts the cash flows in B3:B5 as if the first arrives at the end of year 1, giving about 481.59. The investment in B2 happens today, so it is added outside NPV rather than included in it. Net result: 81.59.
5. Return the Q2 budget for Sales using the labels in A4 and B4.
| A | B | C | |
|---|---|---|---|
| 1 | Q1 | Q2 | |
| 2 | Ops | 100 | 120 |
| 3 | Sales | 200 | 260 |
| 4 | Sales | Q2 | 260 |
The first MATCH finds Sales in the row labels A2:A3, which is row 2 of the table. The second finds the second-quarter label in the headers B1:C1, which is column 2. INDEX then returns the cell where they cross.
6. Show the gross margin percent, and n/a when revenue is zero.
| A | B | C | |
|---|---|---|---|
| 1 | Revenue | COGS | Margin |
| 2 | 0 | 0 | n/a |
Revenue is in A2 and cost of goods in B2. With revenue at zero, a division would return #DIV/0!, so the IF returns the text n/a before it divides.
7. Return the quarter-end date for February 14, 2026.
| A | B | |
|---|---|---|
| 1 | Date | |
| 2 | 2/14/2026 | 3/31/2026 |
February is month 2. MOD(3 minus 2, 3) is 1, so EOMONTH looks one month ahead of February and returns the last day of March: 3/31/2026.
8. Rank each division by revenue, with 1 as the highest.
| A | B | C | |
|---|---|---|---|
| 1 | Division | Revenue | |
| 2 | A | 500 | 3 |
| 3 | B | 720 | |
| 4 | C | 610 |
RANK.EQ compares B2 with the full list B2:B4. The list is locked with dollar signs so it stays the same when filled down. Division A has the smallest revenue, so it ranks 3.
How to prepare in a week
- Day 1. Percent change, CAGR and percent of total. Practice number formats for percents and checking each result by hand.
- Day 2. SUMPRODUCT for weighted averages and for totals from two columns. Rebuild the same figure with a helper column to confirm it.
- Day 3. NPV, IRR and PMT. Learn the timing rule for NPV and the sign convention for PMT, then do three cash-flow tables.
- Day 4. Lookups: INDEX with MATCH in both directions, XLOOKUP, and a two-way lookup in a budget grid.
- Day 5. Guarding ratios with IF and IFERROR, and rank and top-N work with RANK.EQ and LARGE.
- Day 6. Dates: EOMONTH, EDATE, quarter-end and fiscal-year columns. Build a monthly schedule from a start date.
- Day 7. Timed mixed set, then rebuild one model from a blank sheet without notes. Review every miss by skill.
Which of our practice fits
The Analyst and Expert tracks fit this role: lookups, statistics, dates and dynamic arrays. Use Everyday Excel first if conditional totals and absolute references are not yet automatic.
Financial analyst assessments can combine formula questions with a small modeling task, so practice both timed items and one longer build. The provider pages say what practice we have for each test, and what to ask an employer about its format.
Function guides for this role
Questions
- What Excel skills do financial analyst tests check?
- They vary by employer. Growth rates, discounting, lookups, ratios and a modeling exercise are all worth practicing. The job post may name a tool such as Power Query or VBA, which suggests a heavier test.
- Do I need to memorize financial functions?
- Know NPV, IRR and PMT well, including their sign and timing conventions. Rarer ones can be looked up if the test allows help.
- Why is my NPV different from the textbook answer?
- Excel's NPV assumes the first cash flow is one period away. A cash flow at time zero belongs outside the function, added or subtracted separately.
- Are macros or VBA tested?
- Occasionally, for senior roles. Most timed assessments stay with formulas, formatting and pivot tables.