Excel for FP&A: The Skills Planning Analysts Use Daily

Excel for FP&A is less about rare functions and more about doing a handful of tasks quickly and without errors: summarising ledger data, pulling figures from grids, explaining variances and building forecasts that are easy to change. FP&A stands for financial planning and analysis, the team that builds budgets and forecasts and explains results to managers. This guide covers the skills planning analysts use daily, with worked examples you can copy.

What FP&A analysts do in Excel#

An FP&A analyst turns accounting data into plans and explanations. A typical month includes:

Unlike accounting, which records what happened, FP&A looks forward and asks "why". The broader list of Excel skills for finance jobs covers what both teams share.

SUMIFS: build reports from ledger exports#

SUMIFS adds the numbers that meet several conditions, which is exactly what a report needs: "Marketing, February, all accounts". Here is a small export:

=SUMIFS(D2:D6,B2:B6,"Marketing",A2:A6,"Feb")

The result is 21,500 (15,000 + 6,500). The range to add comes first, then pairs of "column to check, value to match". In a real report, point the criteria at header cells, such as B$1 for the month and $A2 for the department, and fill the formula across a whole grid. The SUMIFS function guide shows each argument.

INDEX MATCH: pull any figure from a grid#

Once you have a department-by-month grid, you often need one number from it, such as Marketing in March for a commentary slide. INDEX with two MATCHes finds it by labels, not by position:

=INDEX(B2:E5,MATCH("Marketing",A2:A5,0),MATCH("Mar",B1:E1,0))

• MATCH("Marketing",A2:A5,0) finds the row position: 2.
• MATCH("Mar",B1:E1,0) finds the column position: 3.
• INDEX(B2:E5,2,3) returns 19,800.

The 0 means exact match, which you almost always want with labels. The INDEX guide and the MATCH guide explain both parts. In Microsoft 365 or Excel 2021, a nested XLOOKUP can do the same job, but INDEX MATCH works in every version, so it still appears in many finance files and tests.

Variance and percent change: explain the numbers#

Most FP&A conversations start with "how far off budget are we, and why?" Two formulas answer the first half:

Variance:    =Actual-Budget
Variance %:  =(Actual-Budget)/Budget

If Marketing's March budget was 20,000 and the actual is 19,800, the variance is -200 and the variance % is -1.0%. For a cost, spending less than budget is favourable. For revenue, a negative variance is unfavourable. Many teams add a column that says "F" or "U" so readers don't have to work out the sign.

Month-over-month change uses the same pattern. Sales grew from 42,000 in January to 45,500 in February: =(C2-B2)/B2 gives 8.3%. Format it as a percentage with Ctrl+Shift+%. The budget vs actual guide builds a full variance report, including how to handle a budget of zero.

Forecasts with driver-based assumptions#

A driver-based forecast calculates results from a few inputs, such as units × price or last month × (1 + growth). Keep every assumption in its own labelled cell and lock it with $:

=B2*(1+$B$10)

If B2 is this month's revenue and B10 holds a 2% growth rate, filling this formula right projects each month from the one before. Change B10, and the whole forecast updates. Hard-typing 1.02 inside formulas is the most common modelling mistake, because nobody can see or change the assumption.

The same structure drives a cash forecast; see how to build a cash flow forecast in Excel.

Scenarios, sensitivity and checks#

Managers ask "what if?", so analysts need quick ways to answer:

• Goal Seek (Data > What-If Analysis > Goal Seek) finds the input that hits a target, such as the growth rate needed for a revenue goal.
• Data tables show a result for many input values at once, such as profit at five prices and five volumes.
• Scenario switches: a cell with 1, 2 or 3 and =CHOOSE($B$1,low,base,high) lets the whole model flip between cases.

Every model also needs checks: a cell that compares the report total to the ledger total, or that confirms the departments add up to the company figure. =ROUND(SUM(B2:B5)-B6,2)=0 returns TRUE when the totals agree. Checks are how you avoid presenting a number you later have to correct.

How FP&A Excel skills are tested in interviews#

FP&A interviews often include a timed Excel task: summarise a data export, build a budget vs actual, or extend a simple forecast. Interviewers look for correct numbers, clear structure, and formulas instead of typed values. Speed matters too, so keyboard shortcuts for filling, formatting and navigating save real minutes.

To prepare, try the finance Excel mock test and read what to expect in a financial analyst Excel test. If you are also applying to banks, compare with the Excel skills for banking. Not sure where you stand? The Excel level test gives you a starting point.

Practise it#

The three skills that carry most FP&A work are conditional sums, percent change and two-way lookups. Practise COUNTIFS and SUMIFS in a live spreadsheet, then practise percent change and INDEX and MATCH: you type each formula and it checks it instantly. Start with the lookup exercise on this page.