ADVANCED Excel Interview Test

Рет қаралды 132,429

Күн бұрын

Advanced Excel Interview Test: Can You Pass?
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
🆓 Excel file for this video: view.flodesk.com/pages/625058...
This is an advanced excel interview test you may encounter while recruiting for a business or finance job such as a business analyst or financial analyst. It consists of 3 questions, which can be solved using the following formulas, and making sure to lock the appropriate cells for efficiency (by pressing F4 for the $ signs):
Q1: IF statement and multiplications for growth rate and percentage of revenues.
Q2: Large function, small function, and xlookup (can also be done with an index match)
Q3: A combination of nested formulas. More specifically, nested if statements, and date functions such as emonth, day, month, etc.
For questions three, this can be simplified by breaking down the solution into several steps as opposed to doing it all in one cell.
Throughout the video, you'll get a breakdown of what the question is asking for, and an in-depth explanation of how to solve it.
Note: there is more than one correct way to do these questions. If you have any other ideas, feel free to comment them below!
LEARN:
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
📊 Get 25% OFF Financial Edge Using Code KENJI25: bit.ly/3Ds47vS
SOCIALS:
📸 Instagram - / kenji_explains
🤳 TikTok - www.tiktok.com/@kenjiexplains...
GEAR:
📹 My Favorite Books & Gear: kit.co/kenjiexplains
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Question 1
4:24 - Question 2
7:05 - Question 3 (hardest)
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.

Пікірлер: 130

@MetalBreakdown Жыл бұрын

I got my job as a financial analyst. This helped so much, thank you from the bottom of my heart.

@casonalimudgal7986 Ай бұрын

Every video is full of leaning. Thanks a lot , Kenji for a wonderful explanation.

@Marcusmaker1234 2 жыл бұрын

Literally practiced your excel interview test the day before my interview, and got the job as a result. 10/10 Recommended channel.

@seancarnell1503 2 жыл бұрын

I'm a fan of array functions. For Q1, I'd use orginal amount times (1+r)^SEQUENCE to return 1 row and x columns where x is the number of periods. Then reference all subsequent steps to that array. Though, since not everyone is on 2019/365 just yet, my method may cause issues with compatibility...

@peterd9624 2 жыл бұрын

the xlookup is great but the way you've used it assumes that each brand has a unique GM. If there were two brands with the same GM %, then xlookup will by default only populate your list with the first brand

@KenjiExplains 2 жыл бұрын

Good point! You can use a combination of index + sort + sequence, but it's gonna get a bit messy haha

@brunocaruso1082 2 жыл бұрын

Was thinking the same. I would add a column with a rank function and then use the unique rank reference with the xlookup.

@fosufredrick6280 Жыл бұрын

You have really helped me a lot I always look for your videos anytime I come to utube. Keep it up and continue the good job

@ChimezieOsuoha 19 күн бұрын

Nice and loaded, keep it up Mr Kenji

@benjaminlouislonnen1678 2 жыл бұрын

Very much enjoy watching your videos and looking forward for more to come! Keep up the great work!

@KenjiExplains 2 жыл бұрын

Thank you for the nice comment Benjamin!

@ramdaberopalla7525 Жыл бұрын

This is very informative video. Thank You Kenji for sharing your knowledge and skills.

@josephmutale4824 2 жыл бұрын

Hi Kenji, I am always looking forward for your videos every time am on you tube. I am learning a lot from you brother keep up the good works.

@KenjiExplains 2 жыл бұрын

Thank you! I appreciate the kind comment Joseph :)

@Soritaaa 2 жыл бұрын

This is definitely gonna come in handy in my next interview! Thank you!!!

@KenjiExplains 2 жыл бұрын

Great to hear!

@gospelmoto2833 Жыл бұрын

Great! Im learning new things. Thanks Kenjie!

@KenjiExplains 2 жыл бұрын

🏦 Learn finance & valuation: www.careerprinciples.com/courses/finance-valuation-course 🧑‍💻 Easier excel interview test: kzfaq.info/get/bejne/a5aIZad1tb28n40.html

@bettertoday2783 2 жыл бұрын

Many thanks! The video is useful!!!! Keep making the great content👍🏻

@projekts5047 Жыл бұрын

dude ur actually a legend. Concise and succinct too.

@KenjiExplains Жыл бұрын

Thank you for watching!

@craftymongoose 2 жыл бұрын

I like to use Alt + Enter between different parts of a nested IF formula, as it really helps make it easier to read

@KenjiExplains 2 жыл бұрын

Nice thanks for the suggestion!

@doduyle8476 2 жыл бұрын

oh nice, I never thought of that before. But I use it frequently to [Enter] inside a cell when typing long texts

@ohdjrp4 Жыл бұрын

Thanks for this upload!

@michaelquach7046 2 жыл бұрын

The dynamic holding period is a very practical model feature 👍

@KenjiExplains 2 жыл бұрын

Yes sir!

@rishigupta2342 2 жыл бұрын

Great Video. Could you make a video to explain relative & absolute references? I always get confused. Thanks

@PriyankaChaudhary-i6e 16 күн бұрын

amazing man

@DanLeap995 2 жыл бұрын

For Q3 I prefer to calculate DAYS(resignation,month_start)/DAYS(month_end,month_start) and then apply a max of 1 and min of 0. Multiply this by the salary amount and done 😃

@ashleygarvin7177 2 жыл бұрын

I was thinking the excat same but maybe with an additional adjustment for business days in the month for the prorata component.

@abelnyamori 2 жыл бұрын

Great video. I’d maybe have used an IFS function for the last one to reduce the nesting which always trips me up. It would be 3 tests: if it’s less than the resignation month then full amount, if it’s equal to the month then they get partial amount, and if it’s greater than the month then it’s 0. I like your method too though- and I learned a lot from this. Thanks

@KenjiExplains 2 жыл бұрын

Good point!

@nikolafingarov9184 Жыл бұрын

Definitely this one. I would use Kenji's nesting only if I'm using an earlier version of Excel. Otherwise using IFS would be a clearer and simpler solution to the task. Thanks for the great video, Kenji!

@B3Band Жыл бұрын

Just remember that not all versions of Excel suppose IFS and XLOOKUP. If the hiring manager knows that, they may purposely put you on Excel 2019 or 2016 just to see if you can still handle it without making excuses. They might even do it without even realizing if they are at a company that simply hasn't felt the need to upgrade. I've worked at Amazon and UPS, and they never updated their Office suite anywhere near the time that they were released.

@ziomekzedzielni1 6 ай бұрын

but u have microsoft 365 subscriptions for companies like 10-20$ per user per month. You get updates automaticaly. Really weird such big companies dont pay subscriptions to have most up-to-date software? its like pennies. Interesting...@@B3Band

@Locomaid 4 ай бұрын

Beginner here: In this last example, does this formula reflect or differentiate work days and weekends?

@hunterburns 2 жыл бұрын

The most important stuff that I have ever learned hats off to you boss 🤠💖💖💖

@KenjiExplains 2 жыл бұрын

Cheers!

@camlex6310 2 жыл бұрын

Super helpful tips!! Thanks

@KenjiExplains 2 жыл бұрын

Glad it was helpful!

@andrewnielsen3016 Жыл бұрын

Hi Kenji, Great Video - RE: for Salaries - the full story for budgeting / forecasting is both start and stop dates.....do you have a video for this?

@tobih.3278 2 жыл бұрын

Great video. Very practical advice :)

@KenjiExplains 2 жыл бұрын

Thank you 👊

@merewah3612 2 жыл бұрын

For number 3, for better optics and visual checking, i would put the 2nd nested if in the second line. Alt+Enter

@KenjiExplains 2 жыл бұрын

Good point thanks for the suggestion!

@sumeetpatil938 2 жыл бұрын

The last formula was great to learn. Thank you!

@KenjiExplains 2 жыл бұрын

Glad it was helpful!

@andreugiro2807 2 жыл бұрын

I learned a lot in your excel course!

@KenjiExplains 2 жыл бұрын

Appreciate it!

@isaacobomhese4325 2 жыл бұрын

The Datedif function could help with the difference between the two dates. Nice one Kenji

@KenjiExplains 2 жыл бұрын

Good point!

@andymarci6766 2 жыл бұрын

Keep going kenji!

@KenjiExplains 2 жыл бұрын

Cheers!

@willzinner8813 2 жыл бұрын

very creative solving the last one..impressive

@KenjiExplains 2 жыл бұрын

Thank you hope you liked it ✌️

@seanmichael6579 2 жыл бұрын

Just use a Filter( Sortby( Filter() ) ) for question 2. Inner Filter() removes all columns except the two you want. Sortby() the GM. Outer Filter() where any row with GM less than the nth GM is removed.

@sbIvanov 2 жыл бұрын

For the last If function. Should you not actually count the business days, rather than all days? So, if a month has 20 business days, but I quit on the 3rd of the month, I could have worked between 1 or 3 days. (Between $250 and $750 on a $5000 salary) In your scenario, you pay me a fraction of the overall salary, which does not account for that. So, the correct formula to use for that is: =NETWORKDAYS(B3,B4), where B3 is the starting date and B4 is the ending date

@BasitJamilofficial 2 жыл бұрын

You are truly doing a great job.. especially for people like me who are at the entry level of career after ACCA.. learning alot from your videos.. keep making more videos MAY ALLAH BLESS YOU!!

@KenjiExplains 2 жыл бұрын

Thank you for the nice comment Basit! More to come :)

@brunocaruso1082 2 жыл бұрын

Can you do a video about LET and LAMBDA functions?

@asifabassy Жыл бұрын

thanks for such productive instruction but my question is how to eliminat the weakend from the calculation (in short our salary is divided only on working days based on that we do calculation )@Kenji Explains

@jandresronp Жыл бұрын

"Date" is a number format in excel that show you "the number of days since January 1st of 1900". So, you can use inside the if "date_of_resig" cell minus "month_col_name" to get the days worked. And this, divided by day(eomonth(month_col_name, 0)). So, if(date_of_resig > month_col_name -1, (date_of_resig - month_col_name -1)/day(eomonth(month_col_name,0)),). -1 because if you resign 1st of month you worked one day

@LD3funny 2 жыл бұрын

Great video

@KenjiExplains 2 жыл бұрын

Thank you!

@panepanpan 9 ай бұрын

Would be great addition to the videos if the shortcuts pressed are displayed somewhere on the screen

@aneeshssundaram1675 8 күн бұрын

Hi Kenji, Singaporean here. I have a way to shorten the IF formula in the Advanced Excel Interview question IF(MONTH(F$5)

@francishubertovasquez2139 2 ай бұрын

So that if function and the referrences and the true or false etc can be used as an indirect equation because it's made to be precise numerically speaking with a twist on how science things behave per point in time like mapping microthings area for example and mapping science things area per painted or approximated area to create area equation like macro and micro separate focus and equation per area with behavioral diagnosis of it's numericals if compounds or energies range created when clumped.

@MiWill1988 2 жыл бұрын

On question 3 all the fields ended on the 22nd. Not sure if I'm thinking too hard but that would of made me wonder if everyone got paid on the 22nd normally and would need to be their days counted from the 23rd

@giacomoli1187 2 жыл бұрын

Hey Kenji, great video again! I was wondering if in Q2 there were 2 exact same percentages, would XLOOKUP show error or the first that comes of the two?

@KenjiExplains 2 жыл бұрын

Good question! It would just show the first I think...there are alternatives to it but it would just require multiple formulas which I didn't want to get into in the video XD

@keybearer1992 2 жыл бұрын

Nice video Kenji, but had a thought though that came to my mind regarding question 3. Here in my country due to Labour Law legal definition of pro-rated salaried working days, calculation might not be as simple, as some employment work on a 5 days/week and some 6 days/week. As such, what is considered daily rate needs to take into account how many days he/she would actually be required to work that month (e.g. until 7th of the month employee A may need to work 5 days, employee B may need to work 6 days eventhough both resigns on the same date. Salary payment would only be up to those number of working days). Not sure how it is in other countries, just something to think about.

@yjas8904 Жыл бұрын

this is more complicated. you have to take into account the days each date signifies from sunday to monday, and you probably have to count holidays too. what day each employee will work also must be specified. you probably have to work on this month by month like project management table. maybe ask someone who works at payroll on how they do it

@royeffinger4554 2 жыл бұрын

On 3, I use max(min((eomonth-date)/(eomonth()-eomonth(,-1)+1)),1),0)* figure. Conceptually it’s just what’s the % of month max 1, min 0.

@Fredriken 2 жыл бұрын

Q2: Xlookup for Brand works only if all values are unique. I would use rank and then xlookup from that. But maybe there are better ways?

@KenjiExplains 2 жыл бұрын

Good point!

@gitrox412 Жыл бұрын

how would formula in Q3 looked like if we were to count only working days?

@ercik4725 2 жыл бұрын

Question 2 - x.lookup / index match wont work if you have the same value in multiple cells so the formula is incorrect. Try using INDEX(SORT(B6:F17,6, -1), SEQUENCE(5), {1,5}). In the future when they'll implement new functions TAKE and CHOOSECOLS, it'll be even more intuitive

@KenjiExplains 2 жыл бұрын

Good point thanks for the suggestions!

@victoriousmale1430 Жыл бұрын

I just tried your suggestion, sadly my Excel 2016 cannot support sort and sequence, is there any other way please? thank youuu

@seanmahoney2671 Жыл бұрын

Wouldn't you use the WORKDAY function in the last example to make sure they are getting the right percentage based on actual days worked? For example, April 17, 2022 is a Sunday... assuming a standard 5-day work week M-F, they shouldn't getting paid for the weekends.

@stephen5092 2 жыл бұрын

Great video, Is Microsoft excel the main program used in the corporate finance field or do some company’s use google sheets? Thanks

@KenjiExplains 2 жыл бұрын

Hey! It's Excel that's by far most popular. Google sheets is often used by startups though

@segilolasolomon7300 6 ай бұрын

Hello Kenji, Do you offer personal excel tutoring ?

@Breaking_Even 2 жыл бұрын

For a university student wanting to specialize in quantitative finance is your complete finance & valuation course necessary for the quantitative field?

@DarkGT Жыл бұрын

6:10 What will happen if there are two or more brands with equal percentages?

@muhammadfaisalfurqon2089 2 жыл бұрын

Hi Kenji! I was wondering whether those paid courses on the link has a lifetime access or a limited one (e.g only accessible in one year, etc). Thanks!

@KenjiExplains 2 жыл бұрын

Hey there! It’s lifetime access (see the FAQ section on the landing page)

@camhoag7916 2 жыл бұрын

Babe wake up, new Kenji video

@KenjiExplains 2 жыл бұрын

hahahah love to see it

@ajaymb4658 2 жыл бұрын

Due to subtitles can't see what short cut keys you telling, other than everything is very helpful and knowledgeable for next level..

@Mynamenotjeff95 7 ай бұрын

This was incredibly helpful!... But in regards to Q2 - when you are creating the XLOOKUP formula for the brand when sorting - is there a workaround when there are two lookup values that are the same? If you apply this formula when there are two brand with the same value, it just puts the same brand in twice, rather than the two different brands that have the same value.

@kiongakamau 2 жыл бұрын

If one doesn't have an XLookup function try this =index($B$6:$B$17,match(I15,$F$6:$F$17,0)). For sequence function just use google sheets.

@dennishodges4281 Жыл бұрын

For Q1: why did you used 1+ the rental income growth rate?

@talhamushtaqofficial5279 7 күн бұрын

In this video you calculate salary for resigning employees during the month What formula will be used if someone is joining during the month . Means we cant calculate salaries of all employees including new employees who joined during month

@gabgab7935 2 жыл бұрын

i just sort (ALT D S) everything when asked about getting the highest to lowest figures or vice versa in a set of data aheheh😅

@singhhimmattoor3862 9 ай бұрын

Shorter alternative for last problem - IFS(MONTH(F$5)$D6,0)

@BrennenL 2 жыл бұрын

Question 2 was a lot easier than Question 1, IMO. Now, I've only had 1 'excel interview test' (intermediate/new grad), but I had a couple of days to do it. Is that common? Or would a 'test' like the one in this video be something you'd complete on the spot?

@KenjiExplains 2 жыл бұрын

I've had some due in a couple days (especially for startup roles) and also some on the spot for big tech (30min-ish) where you need to explain your thought process as well. I guess both are possible!

@BrennenL 2 жыл бұрын

@@KenjiExplains Intriguing! Thank you for your response.

@SL-og1qf Жыл бұрын

No matter what I do I can't get the last formula to work. Any ideas? =IF(MONTH(F$5)

@InsaneDark Жыл бұрын

The Q3 isn't correct as the function takes into consideration all days in the month however this analysis should consider only working days. Last time I checked no country in the world pays for weekends if you aren't working or if your contract doesn't say so

@socalbum1971 Жыл бұрын

for Q2, what will you use if you have 2 or more items with the exact same margins?

@DarkGT Жыл бұрын

One solution I can think off is to use some logical expression to check if the top brand isn't like the next match from the xlookup. Not sure ho actually Excel behave in that instance, does it go for the first match or throws an error? And I was wrong. From searching online I found that Xlook will return only the first matching instance and not the rest. People use this instead : For reference, =INDEX([array], SMALL(IF([search value] = [search column], ROW([search column])-1,""), ROW(1:1)))

@konstantinradu4399 2 жыл бұрын

I had received an email for Warsaw Goldman Sachs, pre recorded interview. For internal auditing position, any advice? Excel skills are really basic and never done auditing in my life only some bookeping and partial balance sheets and partial pre compilating taxes, but things that are really basic.

@KenjiExplains 2 жыл бұрын

Hey there! the prerecorded interviews are usually more generic so they don’t assume previous knowledge of the subject(audit/accounting in this case). So you can expect more behavioral questions. That said, the questions may have changed since my colleagues and I did it so be prepared for both!

@konstantinradu4399 2 жыл бұрын

@@KenjiExplains above compiling some income statements and balance sheet from high school and university I don’t know how is it in audit. Beyond that I think I made a mistake writing “economics and accounting” bachelor and not “economics and innovation” which is its real name. But it’s just a translation mistake. Thank you very much

@konstantinradu4399 2 жыл бұрын

@Kenji Explains Hi Kenji can I ask you if Goldman was selective or was giving everybody a chance of doing hirevue? Are chances bigger to get hired once a candidate received a hirevue pre recorded to do or chances remain the same? I'm really nervous but I think it's common given the reason.

@AdamStartup 2 жыл бұрын

Hi Kenji, great video!!! Anyway can I use your worksheets on my video? I'll give credit to you, just it seems very good if I could localize this material. I also make an Excel video on my channel. Feel free to check that out

@KenjiExplains 2 жыл бұрын

Hi Adam thank you for the comment. Unfortunately, no you cannot use my worksheets in your videos.

@AdamStartup 2 жыл бұрын

@@KenjiExplains no worries, thanks for your clarification.

@FandangoJepZ 2 жыл бұрын

Hey Kenji, I might be landing a BA job soon (I'm in my last few months of high school), and I completed this test. I haven't practiced much excel, apart from what I remember from school and searching for formulas throughout the test, so I'm wondering what level of experience this test was; is it entry level for a job or pretty good for most situations? Thanks for the test file!

@KenjiExplains 2 жыл бұрын

Hey! If you can do this on your own you should be covered for most work situations. Very impressive as a high school student!

@FandangoJepZ 2 жыл бұрын

@@KenjiExplains thank you very much sir!

@djsvrlaivwfofj 2 жыл бұрын

If you ever had an interview question asking about excel, please respond with the question and the job role!

@hash_caul976 2 жыл бұрын

sir i am not able to understand anything, specially formula how to apply

@KenjiExplains 2 жыл бұрын

check out this video (the one you watched is an advanced excel test): kzfaq.info/get/bejne/a5aIZad1tb28n40.html

@rafaelabreu1007 2 жыл бұрын

why im watching this if Im a cs major???

@MariaGarcia-gv8hj 2 жыл бұрын

*When it comes to investing, I'm so ignorant about it, I hereby ask; What's your say or thought for anyone with over $20k looking for the best ways to make good returns off it? I will appreciate any help here on how to do this*

@MariaGarcia-gv8hj 2 жыл бұрын

@Lucy Bernardi Interesting, I've always been fascinated with investing and I need to start now. Could you possibly give more information on your Investment Professional and how I can reach out to her?