ADVANCED Excel Interview Test
Вставка
- Опубліковано 7 лип 2024
- 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.
I got my job as a financial analyst. This helped so much, thank you from the bottom of my heart.
Literally practiced your excel interview test the day before my interview, and got the job as a result. 10/10 Recommended channel.
Every video is full of leaning. Thanks a lot , Kenji for a wonderful explanation.
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
Good point! You can use a combination of index + sort + sequence, but it's gonna get a bit messy haha
Was thinking the same. I would add a column with a rank function and then use the unique rank reference with the xlookup.
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...
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
This is very informative video. Thank You Kenji for sharing your knowledge and skills.
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.
Thank you! I appreciate the kind comment Joseph :)
Nice and loaded, keep it up Mr Kenji
Very much enjoy watching your videos and looking forward for more to come! Keep up the great work!
Thank you for the nice comment Benjamin!
This is definitely gonna come in handy in my next interview! Thank you!!!
Great to hear!
Great! Im learning new things. Thanks Kenjie!
dude ur actually a legend. Concise and succinct too.
Thank you for watching!
Many thanks! The video is useful!!!! Keep making the great content👍🏻
I like to use Alt + Enter between different parts of a nested IF formula, as it really helps make it easier to read
Nice thanks for the suggestion!
oh nice, I never thought of that before. But I use it frequently to [Enter] inside a cell when typing long texts
The dynamic holding period is a very practical model feature 👍
Yes sir!
Thanks for this upload!
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
For number 3, for better optics and visual checking, i would put the 2nd nested if in the second line. Alt+Enter
Good point thanks for the suggestion!
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 😃
I was thinking the excat same but maybe with an additional adjustment for business days in the month for the prorata component.
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
Good point!
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!
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.
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
Beginner here: In this last example, does this formula reflect or differentiate work days and weekends?
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.
Keep going kenji!
Cheers!
amazing man
The most important stuff that I have ever learned hats off to you boss 🤠💖💖💖
Cheers!
The Datedif function could help with the difference between the two dates. Nice one Kenji
Good point!
The last formula was great to learn. Thank you!
Glad it was helpful!
Super helpful tips!! Thanks
Glad it was helpful!
very creative solving the last one..impressive
Thank you hope you liked it ✌️
I learned a lot in your excel course!
Appreciate it!
Great Video. Could you make a video to explain relative & absolute references? I always get confused. Thanks
"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
Great video. Very practical advice :)
Thank you 👊
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.
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?
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!!
Thank you for the nice comment Basit! More to come :)
Great video
Thank you!
Can you do a video about LET and LAMBDA functions?
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.
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
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.
Would be great addition to the videos if the shortcuts pressed are displayed somewhere on the screen
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.
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
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
Good point thanks for the suggestions!
I just tried your suggestion, sadly my Excel 2016 cannot support sort and sequence, is there any other way please? thank youuu
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?
Good point!
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
Babe wake up, new Kenji video
hahahah love to see it
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?
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
Shorter alternative for last problem - IFS(MONTH(F$5)$D6,0)
Great video, Is Microsoft excel the main program used in the corporate finance field or do some company’s use google sheets? Thanks
Hey! It's Excel that's by far most popular. Google sheets is often used by startups though
For a university student wanting to specialize in quantitative finance is your complete finance & valuation course necessary for the quantitative field?
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.
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!
Hey there! It’s lifetime access (see the FAQ section on the landing page)
Due to subtitles can't see what short cut keys you telling, other than everything is very helpful and knowledgeable for next level..
6:10 What will happen if there are two or more brands with equal percentages?
how would formula in Q3 looked like if we were to count only working days?
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?
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!
@@KenjiExplains Intriguing! Thank you for your response.
Hello Kenji, Do you offer personal excel tutoring ?
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😅
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.
hi... in the question 2, for finding brand i tried vlookup since in my version of excel there is no function of xlookup, but i am getting error. Let me know the correct formula
for Q2, what will you use if you have 2 or more items with the exact same margins?
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)))
If you ever had an interview question asking about excel, please respond with the question and the job role!
For Q1: why did you used 1+ the rental income growth rate?
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!
Hey! If you can do this on your own you should be covered for most work situations. Very impressive as a high school student!
@@KenjiExplains thank you very much sir!
*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*
@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?
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
No matter what I do I can't get the last formula to work. Any ideas? =IF(MONTH(F$5)
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.
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!
@@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
@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.
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
Hi Adam thank you for the comment. Unfortunately, no you cannot use my worksheets in your videos.
@@KenjiExplains no worries, thanks for your clarification.
sir i am not able to understand anything, specially formula how to apply
check out this video (the one you watched is an advanced excel test): ua-cam.com/video/50X2DDPWXoY/v-deo.html
why im watching this if Im a cs major???
🏦 Learn finance & valuation: www.careerprinciples.com/courses/finance-valuation-course
🧑💻 Easier excel interview test: ua-cam.com/video/50X2DDPWXoY/v-deo.html
How you have done the 3:06 thing?
format cell to Percentage.
@@RBMndzi mean dragging the cells
$250 course...yikes...well, at least there's a $50 discount
Hahaha yeah there are 1000s of courses on the internet that are free. You’re truly a sucker to buy that
So people are learning these things to do a job.They wanted to be a slave.Go for business if have you any kind of wisdom