Excel Test in an Interview

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

 
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.

 
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.
 
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 ?

 

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.

 

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
 
Best Response

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.

 

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

 

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 ?

 
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.
 

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

 
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.

 

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.

 

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).

 

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)

 

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

 

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

 

Neque perferendis quibusdam tempore. Mollitia dolores similique rerum ut qui. Consequuntur voluptatibus quo amet labore quia dolor magni. Reprehenderit voluptas neque odio enim est. In nostrum doloribus velit earum perspiciatis quis.

 

Et aut assumenda laudantium. Consequatur sit aut soluta consequuntur est.

Vero et blanditiis excepturi. Quo dolores ut cupiditate sit delectus numquam. Sed voluptatem voluptatem accusamus ut vel iste blanditiis commodi. Nam veritatis aut numquam dolorem dolores inventore.

Voluptatem reiciendis quis reiciendis et. Natus odit et error nulla. Eveniet est iusto sint sit. Et molestiae occaecati velit animi veritatis quis facere. Voluptatem quod minus rerum cum maiores.

Est minus minus aliquid. Doloribus aut dignissimos molestias voluptatem id ducimus explicabo.

"It is hard to fail, but it is worse never to have tried to succeed." Theodore Roosevelt

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
BankonBanking's picture
BankonBanking
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
Secyh62's picture
Secyh62
99.0
5
GameTheory's picture
GameTheory
98.9
6
dosk17's picture
dosk17
98.9
7
DrApeman's picture
DrApeman
98.9
8
CompBanker's picture
CompBanker
98.9
9
kanon's picture
kanon
98.9
10
numi's picture
numi
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”