Excel for grad applications
Grad jobs at Big 4 firms, banks and big companies expect you to know your way around Excel. Some firms give you a timed Excel test, and interviews and case studies often come back to it.
These are the lessons I'd do before applying. The early sessions are about getting quick and accurate, and the last two cover the finance that deals and banking teams test. Each lesson is a short task on a spreadsheet, with your formula checked as you go. They take about ten minutes each, so a session is around half an hour.
Session 1: The basics, quicker
If you do get an Excel test, it will be timed, so start by getting quick at the basics. These three are free.
- LESSON 4 · FREE ACCOUNTShortcuts: move, select and sum without the mouseMoving around and selecting with the keyboard instead of the mouse.FREE
- LESSON 2 · FREE ACCOUNTCopying formulas and locking references with $Writing one formula and copying it down without the references moving.FREE
- LESSON 3 · FREE ACCOUNTPercentages and GST: from till takings to revenueTaking GST out and working out each store's share of the total.FREE
Session 2: Percentages and margins
Case studies and Excel tests use a lot of growth rates and margins, and it's easy to divide by the wrong year under time pressure.
- LESSON 7 · PASSPercentage change and marginsGrowth on last year, margins and the change in margin in percentage points.PASS
- LESSON 6 · PASSMixed references: one formula for a whole sensitivity tableA whole table of scenarios from one formula.PASS
- LESSON 8 · PASSRounding properlyRounding so a table still adds up to its total.PASS
Session 3: Summarise a table
A common test question gives you a few hundred rows and asks for totals by region, month or product.
- LESSON 12 · PASSCOUNTIFS and SUMIFS: several conditions and date rangesTotals and counts with more than one condition.PASS
- LESSON 14 · PASSAVERAGEIFS, MINIFS and MAXIFS: averages and extremes with conditionsThe average, smallest and largest value for one group.PASS
- LESSON 15 · PASSSUMPRODUCT: weighted averages, like a company's cost of debtWeighted averages, like an average interest rate across several loans.PASS
Session 4: Lookups
Tests often give you two tables to bring together. Check your lookups as you go, because a wrong row with no error message is easy to miss.
- LESSON 27 · PASSXLOOKUP: lookups with if-not-found and search from the bottomThe modern lookup, including what to show when a code is missing.PASS
- LESSON 26 · PASSINDEX and MATCH: lookups to the left and two-way lookupsLooking to the left and two-way lookups.PASS
- LESSON 20 · PASSIFNA, ISNUMBER, ISBLANK and IFERROR: handling errors in a comps tableHandling #N/A without hiding a real problem.PASS
Session 5: Finance for deals and banking
If you're applying for corporate finance, valuations or banking, expect questions on discounted cash flows. These three lessons cover NPV, IRR and terminal values.
- LESSON 37 · PASSNPV: valuing a project, and the year 0 trap in Excel's NPVValuing a project, and where the year 0 cash flow goes.PASS
- LESSON 38 · PASSIRR and XIRR: the return on an investment, and why real cash flows need datesThe return on an investment, with and without real dates.PASS
- LESSON 39 · PASSPerpetuities: level and growing, and how they become a terminal valueThe terminal value at the end of a forecast.PASS
Session 6: A small valuation
A modelling test usually asks for a discounted cash flow. These four lessons value one business from start to finish, then check it against listed peers.
- LESSON 51 · PASSFree cash flow: from EBITDA to unlevered free cash flowFrom EBITDA to the cash flow a valuation discounts.PASS
- LESSON 52 · PASSWACC, with CAPM and the after-tax cost of debtThe discount rate: cost of equity, cost of debt and the weights.PASS
- LESSON 53 · PASSA simple DCF, from free cash flow to value per shareDiscounting the forecast, a terminal value, and enterprise to equity value.PASS
- LESSON 54 · PASSPeer comparisons: valuing a business on EV/EBITDAEV/EBITDA multiples, the median and quartiles, and an implied value range.PASS
It's worth reading the set-up tips too (a few settings that make the shortcuts work properly), and keeping the shortcut list open while you practise.
Questions
Do Big 4 firms give an Excel test?
Some do. It depends on the firm and the team. Plenty of grad programs have no formal Excel test, but you'll still use Excel in an assessment centre case study and from your first week in the job. Teams that build models, like deals and valuations, are more likely to test it.
What is a bank modelling test?
Usually you're given a set of assumptions and a few hours on a laptop with Excel, and asked to build a small model, often a discounted cash flow. This site doesn't teach full models, but the last two sessions cover the parts a model is built from, ending with a simple DCF.
What should I say when an interviewer asks how good my Excel is?
Give examples rather than a rating out of ten. Say which functions you use and what for, like SUMIFS to total a ledger by month or XLOOKUP to bring two tables together. If you've done the lessons here, you can describe tasks you've actually done.
Does the numerical reasoning test use Excel?
Usually not. It's normally an online test where you can use a calculator. It covers the same percentages and ratios as the second session, though, so the practice still counts.
I've only used Excel on a Mac. Does that matter?
The formulas are the same, but a lot of the shortcuts are different, and most firms use Windows laptops. Switch to Windows in the settings at the top of the page and every lesson shows the Windows keys.
Semester pass
Lesson 1 is free to try and lessons 2 to 4 need a free account. The pass unlocks every lesson on this page and the rest, in Excel and Google Sheets. A$49 every six months; cancel any time. Terms and refunds.