Excel Test in an Interview

2007Grad's picture
Rank: Senior Baboon | 194

Hey guys, as I've posted on here before, I've been laid off for a few months now. I have an interview coming up where I'm going to have to take a 20 minute excel test. Since I've been out of work for a few months, I want to brush up on my excel skills. Anyone have any templates I could use to get some practice ? Also, any tips ? The interviewer mentioned VLookup and formatting as some of the stuff they would test.


excel test for interview

The excel test will include key functions that are used within your sector. However, there are some basic functions in excel that are standard across most industries. Focus on the basics of Excel, then more industry-specific functions. The only way to become proficient at this is to practice within the program. Here's an example of an Excel test for two different sectors.
from certified user @acrew09

I've taken 2 of these for F50s, one for tech and one for manufacturing.

The tech one, they gave you a bunch of random data on prices and click-through rates. They wanted you to then come up with recommended pricing and a sales forecast. This might be useful for an FP&A role? However it seemed like they intentionally left out key information to see what assumptions you would make and whether you could deal with the ambiguity.

The manufacturing one, they provided an investment scenario and gave key inputs (initial investment, cost of production, etc) and wanted a go or no-go decision. You needed to build out an income statement and cashflow and account for things like changes in FX. From what I've heard, this is a much more standard type of excel test for corp fin.

Ready to improve your modeling skills? Hone your financial modeling skills and ace your interview. Click the button now to start your training.

Recommended Reading

Comments (47)

Jan 25, 2010

Sounds like you're interviewing for some kind of BO role... otherwise I can't see why they would care as much about vlookup/pivot table skills vs. actual modeling skills.

Brush up on what they told you you'll be tested on and a little bit more.

Jan 25, 2010
Marcus_Halberstram:

Sounds like you're interviewing for some kind of BO role... otherwise I can't see why they would care as much about vlookup/pivot table skills vs. actual modeling skills.

Brush up on what they told you you'll be tested on and a little bit more.

Well, its a PE/VC FoF role, so not sure if too much actual financial modeling will be involved.

Jan 25, 2010
2007Grad:
Marcus_Halberstram:

Sounds like you're interviewing for some kind of BO role... otherwise I can't see why they would care as much about vlookup/pivot table skills vs. actual modeling skills.

Brush up on what they told you you'll be tested on and a little bit more.

Well, its a PE/VC FoF role, so not sure if too much actual financial modeling will be involved.

My guess is that it does not involve too much actual modeling. Otherwise, they will test your modeling skills instead of VLookup and formatting.

Jan 25, 2010

"Microsoft Excel data analysis and business modeling" by Wayne Winston is useful.

Jan 25, 2010
wookie102:

"Microsoft Excel data analysis and business modeling" by Wayne Winston is useful.

Thanks. Any online templates I could also look at ?

Jan 26, 2010

"What we can, we must; and because we can, we must"

Jan 27, 2010

Here are some WallStreetPrep excel shortcuts and useful references. http://www.wallstreetprep.com/blog/excel_resources.php

Jan 27, 2010
Kanon:

Here are some WallStreetPrep excel shortcuts and useful references. http://www.wallstreetprep.com/blog/excel_resources.php

Thanks Kanon. The macro one is pretty useful. I'm trying to find an Excel test I took during the Wall St. Prep training program during my analyst training, anyone have an ideas where I could find it online ?

Jan 30, 2010

Hi everyone,

I'm a newbie here. Would any of you please explain what type of position in a PE sector would test for Excel skills?

The reason I'm asking is my background is accounting, pretty good at Excel and have some VB programming experiences. However, I'm stuck in a very laid-back industry, trying hard to get out. Want to see where I should try to get a shot.

Thanks.

Jan 30, 2010

I have no experience with these tests, but being in Corp Fin for a while the most useful Excel functions are:

Vlookup
Sumif
Sumifs
Pivot Tables

Other than that, obviously know linking and working within a workbook and maybe light modeling of financial statements.

Again, I don't have direct experience with these tests, but this would be a good (relevant) place to start if you're looking for direction.

twitter: @CorpFin_Guy

Jan 30, 2010

If it's anything like Excel tests I have taken in the past, then it is a joke. Mine was 30 questions of basic formatting with one question on pivots. But that was for low level accounting jobs. I'm sure this one would be much more advanced.

"Yeah, you know whatcha doin."

Jan 30, 2010

I've taken 2 of these for F50s, one for tech and one for manufacturing.

The tech one, they gave you a bunch of random data on prices and click-through rates. They wanted you to then come up with recommended pricing and a sales forecast. This might be useful for an FP&A role? However it seemed like they intentionally left out key information to see what assumptions you would make and whether you could deal with the ambiguity.

The manufacturing one, they provided an investment scenario and gave key inputs (initial investment, cost of production, etc) and wanted a go or no-go decision. You needed to build out an income statement and cashflow and account for things like changes in FX. From what I've heard, this is a much more standard type of excel test for corp fin.

    • 1
Jan 30, 2010

Thanks Guys, that's really helpful.

@acrew - Yes, they want me to build a Model for Impairment testing - Now that is when i am hired, but before that they would want to test my excel as well as corp fin knowledge.

Again ambiguity is the key in Corp fin and they always want to test you on that. so it may involve making some assumptions.

Any more inputs are welcome.

Thanks

Jan 30, 2010

I took one for my corpdev position which was actually building a 3 statement forward projection model from scratch and then adding an IRR model for an investment property.

Jan 30, 2010

Allday - What were your key takeaways from it ? was it like growth figures for next year were given and based on it you keep building P&L and BS and CS statmnt.?

Also i now have a bit more clarity - They will take a Case study and later followed by modelling exercise.

Any idea on corporate finance case study guys ?

Jan 30, 2010
Nishesh-Ved:

Allday - What were your key takeaways from it ? was it like growth figures for next year were given and based on it you keep building P&L and BS and CS statmnt.?

Also i now have a bit more clarity - They will take a Case study and later followed by modelling exercise.

Any idea on corporate finance case study guys ?

As much as anything it's a test on whether you understand your accounting and how the three statements flow together. I was given some different figures that were specific to healthcare (since it was a healthcare company) and was expected to be able to decipher some vague movements into how it might affect the growth rates. It's a look into how you think, how you model, and your understanding of accounting.

Jan 30, 2010

Thanks.

I had my case study interview today.
Its a utility company so it had a case of company trying to raise capital and then suddenly receives 3 different set of news and how the analyst would want to adjust their valuation based on this new information.

It also had questions on what part of analysis would change and basically testing how i would approach problem and try resolving it along with some technical knowledge.

Thanks everyone...now over to excel modelling

Jan 30, 2010

I had this once for a MM bank, but I had to do it before the superday and then email them the Excel file. I was given a company and asked to creating an operating model and a DCF. I also had to put together some trading and M&A comps.

Jan 30, 2010

I can't add much in the way of answers for you, but I think people will be better able to help out if you give some more detail. What industry/group is it for?

Jan 30, 2010

Healthcare boutique. Interviews are only from noon-5 i believe, so I won't be doing anything like creating a model I don't think.

Jan 30, 2010
ek412:

Healthcare boutique. Interviews are only from noon-5 i believe, so I won't be doing anything like creating a model I don't think.

Then don't sweat it. Brush up on some Excel shortcuts and basic modeling skills, but a boutique (assuming this is not a "boutique" like Lazard or Moelis) won't have the time to spend an hour testing on modeling.

Jan 30, 2010

For the banks I interviewed with, this did not happen too often (maybe can recall once when it happened). Sil's comment is apt for what the exam would consist of.

Jan 30, 2010

Had one for a similar interview. Basically just checked whether you could handle large raw data sets in a limited amount of time. Just had to use very basic functions like VLOOKUP and implement some conditional statements.

Jan 30, 2010

Be familiar with VLOOKUP and HLOOKUP, the basic finance functions (PMT, RATE, NPER, etc), be familiar with pivot tables tables and charts. If it involves large data sets then matrix functions are incredible useful.

Jan 30, 2010
trisomy18:

Be familiar with VLOOKUP and HLOOKUP, the basic finance functions (PMT, RATE, NPER, etc), be familiar with pivot tables tables and charts. If it involves large data sets then matrix functions are incredible useful.

Personally think the index(match function is much more efficient than the v and h lookups..

Jan 30, 2010

Just learn how to index, sumif, countif, iferror, multiple layered if statements, min/max functions and indirect if you want to get fancy.

Jan 30, 2010

I had a different experience with an excel test, it was long enough that no-one had ever finished it, involved reversing out netbacks and calculating cash flows from a set of financials. The last question had two data sets and asked you to make a graph that showed the data effectivley (two axis, one line, one column).

Jan 30, 2010

Whats a vlookup?

I prefer index(match())

JK

One thing about vlookups() I always use the columns() funciton instead of actually trying to count out columns if it is a truly big data set as that gets tiring... Also super easy

vlookup(x,A1:AD300,clumns(a1:ad300),0)

    • 1
Jan 30, 2010

Wow.

Jan 30, 2010

I do not even understand the question...are you asking how to do present value in Excel?

If so I am agahst and here you go:

B C D
5 Future Value ("FV") $1000.0
6 Interest Rate ("I") 0.05
7 Period ("N") 2.00
8 Payment ("PMT") 0.00

10 Present Value ("PV") =D5/(1+D6)^D7

Jan 30, 2010

I cant think of what firm would find this a useful way to judge candidates --- i guess it works similar to those pointless consulting firm interviews where it becomes more like "Read my mind so that we can go to slide 2" lol..... But yea what tbroker put is correct....
Present Value = Value in Future / [(1+ interest rate) ^ number of periods]
Future value is just solving for value in future from that formula
Future Value = Present Value * [(1+ interest rate) ^ number of periods]
If its a terminal value (the present value of a stream of cash flows that grow at the same rate forever) Then you are dealing with a perpituity which is usually done like this
Present Value of Perpituity = Amount of first cash flow / Interest rate - growth rate
Where interest rate is the rate of interest (a WACC in a DCF) and the growth rate is the constant rate of growth of the cash flow that goes on forever.
WACC = a weighted average of the type of capital and its rate of interest
Equity typically higher rate maybe like 14 or 15 %
Different debt amounts should be weighted by their percent of capital structure..... works kinda like this
Equity / [Equity + Debt (total market cap) ] * rate of return on equity + Debt / [Equity + Debt (total market cap)] * rate of return on debt (interest rate)
As you can see, this is exactly what the name implies -- weighted average cost of capital

I dont remember off the top of my head the formulas for annuities (present value of a constant payment) but if its excel just create the same discount formula and copy it across and then add all the discounted cash flows to get the present value of all the separate cash flows.

This is most of what I could see them asking..... good luck --- that sux that they are doing pointless shit like that in their interviews

Jan 30, 2010

Thank you... yes I was quite surprised when I read it myself... just wanted to be sure to take people's inputs who go to super days and deal with this stuff day in and day out.

Kind Regards

Jan 30, 2010

Can you build a 3 statement financial model, DCF etc? If not (those are the basics) you should probably learn how to.

Jan 30, 2010
notthehospitalER:

Can you build a 3 statement financial model, DCF etc? If not (those are the basics) you should probably learn how to.

Any good resource where we can learn how to build a 3 statement model?

Jan 30, 2010

Does anyone have some practice models that they wouldn't mind sending my way?

Jan 30, 2010

Yeah the BIWS modeling courses are good, I used them and liked them.

Jan 30, 2010

Sorry if it wasn't clear. Does anyone working in banking have a sample model that I could practice off of?

Jan 30, 2010

Try building your own, it'll be a much better learning experience than just looking and playing with someone's model- especially if you'll be tested on it. Check out macabacus, I haven't been there but have heard they have good (free) practice.

Jan 30, 2010

I have access to BIWS so working off that for now. If anyone else can provide some helpful assistance I'd appreciate it. Thanks btw

Jan 30, 2010
Comment
Jan 30, 2010