Excel test for budgeting and planning
Budget analysts, FP&A analysts and anyone who follows up on a budget work in Excel every day, and many employers test it before they hire. This practice test has 20 budgeting tasks, from variances and remaining budget to an inflation uplift, a salary budget from FTEs and a full-year forecast, in a live grid with a 30-minute clock.
20 questions · 30 minutes · passing score 70%
Question 1 of 20
In E2, calculate how far the actual total for the six months is above the budget total (a negative result means under budget).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Budget | Actual | Actual minus budget | ||
| 2 | Jan | 50000 | 48200 | |||
| 3 | Feb | 50000 | 53100 | |||
| 4 | Mar | 55000 | 54600 | |||
| 5 | Apr | 55000 | 58900 | |||
| 6 | May | 60000 | 57400 | |||
| 7 | Jun | 60000 | 61800 | |||
| 8 |
No tips during the test. I'm back with help when the clock stops.
The free preview is the first 10 questions in 15 minutes. It isn't saved.
Premium gets you the full 20-question test, your score history and a certificate when you pass.
What budget roles test
A budgeting Excel test checks the work of the planning cycle: building next year's budget from this year's, spreading it over the months, and following up on it as the actuals come in. Expect variances in amount and in percent, totals up to a chosen month with SUMIF, salary budgets with SUMPRODUCT, and status flags with nested IF.
The questions that separate candidates are the forecast ones: the actuals so far plus the budget for the rest of the year, a quarter picked out by a number in a cell, and the department furthest over budget found without a helper column.
How this practice test works
You have 30 minutes for 20 questions. Each question has its own small sheet, written for this test only: a budget by department, monthly budget and actuals, seasonal weights or a list of positions. Click the answer cell, type your formula and press Enter, as in Excel.
Nothing is marked until you hand in. Every answer has to be a formula that still gives the right result when the figures change, so a typed number does not count. Afterwards you see your score, every question you missed and where to practice it.
How to prepare
Take the free preview of the first 10 questions to see where you stand. Most misses on budget tests come from criteria built from a cell, like "<="&E2, so practice SUMIF and SUMIFS with the condition in a cell. Then take the full test against the clock.
What the test covers
Each question practices one of these skills. Follow a link to practice it before you start.
- 1Over or under budget for the half year
- 2Budget left after spending and commitments
- 3Growth from last year
- 4Next year's budget uplifted for prices and wages
- 5Year to date up to a chosen month
- 6Spread a year budget by season
- 7Variance in percent for one department
- 8One category's share of costs
- 9Traffic-light status for a budget
- 10Salary budget from FTEs
- 11One region's share of the total
- 12Labor cost for a week
- 13Count the months above average
- 14Expected result for the year
- 15Budget for a chosen quarter
- 16Sales for one region in one quarter
- 17Price per unit without #DIV/0!
- 18Count the departments over budget
- 19Two-way lookup: product and month
- 20Department with the largest overrun
Questions
What Excel skills do budget analysts need?
Variances in amount and percent, SUMIF and SUMIFS with conditions from cells, SUMPRODUCT, rounding, nested IF for status flags, and lookups with VLOOKUP or INDEX and MATCH. This practice test covers all of them.
How do I forecast the full year from the actuals so far?
Add the actuals up to the last closed month to the budget for the months after it. With month numbers in a column, two SUMIF formulas with "<="&month and ">"&month do it in one cell.
How long is this practice test?
30 minutes for 20 questions. The free preview has the first 10 questions in 15 minutes.
What score should I aim for?
The passing score here is 70%, 14 of 20. Aim for 80% or more before the real test.
Do I get a certificate?
Yes. Pass the full test with a verified email and you get a certificate with a public link for your résumé or LinkedIn.