in this video we'll go over an advanced excel interview test that you might encounter recruiting for a business role or a finance role and you can download the file in the description in case you want to test yourself so let's get into it there's a total of three questions and they're gonna get progressively harder so make sure you stick around until the end for now let's get started with q1 and under the instructions it says to calculate net operating income and create a dynamic model that updates based on the holding period of two to ten years and it also says to assume no sales proceeds at the end of the holding period so once you sell the asset we're assuming you're not going to make anything off it and here's the different assumptions that we've got the rental income growth rate and all of these which seem to be just a percentage of the revenue so they're all the expenses down over here we have everything we need to get to the net operating income towards the bottom this question is probably for a real estate asset manager or something like that based on a terminology net operating income is simply the revenue of a property minus any operating expenses that you might have so first to make the model dynamic let's go ahead and create a drop down list over here for the holding period for that we can go to alt avv that's going to give us the data validation we want to create a list and that list source is going to be the 2 to 10 year period that we've got unspecified under the instructions so let's go ahead and do that press ok from there we can see we've got a drop down and let's say we leave it up 5 years for now from there let's go ahead and start working on the rental income and so we've got the growth rate out here so that means that we need to grow it by that much that being said we need to be wary of the holding period so if if we hold this asset for a five year period anything after that there's obviously not going to be any rental income because we won't have it anymore and so we need to create a condition that's going to say that for this we can do an if statement so we'll go equals if press the tab key the logical test is that this period over here should be less than or equals to the holding period over here make sure we press the f4 key press the comma key and from there if the value is true if that is indeed the case then we want to try grow this period so we want to grow it by this multiply that by brackets one plus the growth rate which is going to be up over here press the f4 key there close the brackets and then if the value is false meaning that if you've already sold a property then there's no rental income to be made so we'll just put a 0 in there and hit enter from there we can go ahead and drag this across and we're just gonna move it there and so now it should be all dynamic so when we change the holding period say we change it to nine years then this should change automatically as well now let's work on the other line items to reach the net operating income so for a total revenue well we only have the rental revenue so we're just gonna equal it to that one and we can drag that across then over here for the operating expenses for repairs and maintenance all of these seem to be a percentage of revenue so let's go ahead and do that we're going to go equals select the total revenue over here we're going to press the f4 key twice there such that it's only locked on the row and then we're going to multiply that by the percent over here and this one we want to press the f4 key three times such that it's only on the letter there this way we can just copy it and drag it across the different years there we go and then for the total operating expenses it's just going to be the sum of them so go to alt equals hit enter and the net operating income is just gonna be the total revenue minus the total operating expenses we can just drag these across and paste over here and so now if we change the holding period say to five years then everything should automatically update like so hopefully that's what they mean by create a dynamic model and if you're liking this video you can also check out our complete finance evaluation course where an investment banker a financial analyst and myself teach everything we know about finance valuation and financial modelling first we cover financial statement analysis using apple's real annual report as an example then we get into financial modelling through a three statement model after that we begin the valuation phase where you learn to do a discounted cash flow a comparable company's valuation and a present transactions valuation on adobe looking at their real financial statements to eventually derive a valuation range lastly we'll show you how to present an investment thesis using a stock pitch format so if you're interested in checking it out head to the link in the description below moving on to question 2 and over here it says to fill light blue area to show the top five and bottom five brands by gross margin percentage so over here this is the light blue area they're referring to and based on the brand names it seems like they're probably all subsidiaries of coca-cola company and so we've got the margin percentage they want us to find the best margin ones and the brand name for that same thing over here with the worst margin ones so intuitively you might think of doing something like a max function where you go equals max press the tab key and then we'll select all of the gross margin percentages here press enter and that's going to give us the highest one which works for number one and the main would work here as well but the problem is that this doesn't work for the second max or in the second best in that case or the third best so you can't really specify that the formula that's probably gonna work best here is the large function so we'll go equals large press the tab key the array is all the gross margin percentages press the f4 key there to lock it comma and then the k there means the rank so you want the the very best one the second best etc so for that we can just select the rank over here and we'll press enter then we can just go ahead and drag all of these down over here now we've got the top five looking good then for the brands over here basically we want to create some sort of a match that's going to say that hey if the margin is 60 give me the brand name for that one now for this you can either do something like an index match or maybe a slightly easier formula is an x lookup so let's go about that equals x lookup press the tab key the lookup value is we want that 60 comma the lookup array is this whole gross margin area f4 key there comma the return array is going to be what we're looking for so we're looking for the brands over here select all of them press the f4 key and press enter so now you can see that the one with the highest gross margin is actually coca-cola that seems to align with what it says over here from there we can also just drag all of these down nice and to autofit that column width we can just press the alt hoi for the bottom five it's gonna be fairly similar but instead of using the large function to find the maximums you're gonna look for the small function which is gonna get the minimums so we'll go equals small press the tab key and the array is going to be that same array over here press the f4 comma and the k is going to be the rank for us then we can just drag that down and we'll get from the bottom five so the worst all the way to the best out of those five and for the brands we can just copy and paste um the x lookup function that we've got up here now you can see that it says diet coke and we can drag that all the way down and now you have the the worst performing in terms of gross margin percentage where diet coke seems to be the worst and let's try to find it over here and that seems to correlate with that too moving on to question three and this is by far the hardest one over here it says to create a monthly salary expense table in light blue so in this area over here and please consider the prorated expense amounts given a specific resignation date so over here we see that there's all of these employees that seem to be resigning from their roles and over here we've got the salaries per month so how much they are making and the date that they're resigning so i think this last part where it says the pro-rated expense amounts is basically saying that hey if these people only worked say for the first two three days of the month then obviously they shouldn't be paid for that whole month so make sure you account for that this question is probably gonna require quite a few nested formulas so on the one hand we need to a condition that's gonna say that if this person is already quit their job then they obviously shouldn't get paid for it and if it's a current month that they've quit in then they should only get paid for the days that they've worked out of the whole month firstly let's create an if statement so we'll go equals if the logical test is that this month up here we just care about the month we don't really want to know the date or the year so we'll just put the month function there press the f4 key twice there then close the brackets is less than or equals to the resignation month so type the month there again and we'll select the resonation month press the f4 key three times there close the brackets press the comma key the value if true they should get a salary so we'll press the f4 key there three times again comma and the value of false is zero so basically what we're saying here is that hey they should get a salary if they still haven't quit while if they've already quit they obviously shouldn't so we can just drag that formula across and as you can see over here this person quit in february and so that's why they only got a salary for the two first months but hey it's not quite fair that they get a full full salary for the month of february if they only actually worked for three days so that's why we need to get back in the formula and try to tweak things a bit there the second part here is gonna say that if the current month is the month that they actually resigned if that's the scenario then we're gonna have to create a fraction that says out of the total days in that month how many days that you work for and then multiply that by the total salary so let's get into it firstly press the f2 key to get inside there and we're gonna do an if statement right after that first comma so if the value is true put an if there press the top key and we want to put the month function so we're saying that if this month up here press the f4 key twice close the brackets is equal to the month of the resignation press the f4 key three times there close the brackets so then we're going to do another comma there so the value if true here's where we want to do the day function press the top key first day that we're interested in is the resignation date day press the f4 key three times there from there we're gonna close those brackets and we're going to divide that by the total number of days in that month so we'll do the day function press the tab key to find the total number of days we're going to do the end of month function which is this one over here press the tab key and we'll select the january period press the f4 key twice comma and 0 is because we want the query month close the brackets and close the brackets again the ones for the end of the month and the other one is for the day the reason we need that end of month function and the day function together is because the end of month gives you the whole day range so it gives you the day the month the year but to do a fraction here we're only needing the day and so that's why we need both functions in there press the comma key so actually we need to multiply that by the salary per month which is what we what we've got right over here press the f4 key to lock it we'll press it three times there press the comma key and the value if false meaning that if the current month is not the month that they've that they've actually quit in then they obviously should get a salary because they're still working and lastly we need to close that nested if statement and that should work for us press enter from there we should be able if we've locked everything correctly and we don't have any mistakes to just drag this up over across all of the different years and just as a sanity check i guess over here this person only worked for for three days so obviously their salary is very small while say down over here this person worked for 29 days so their salary should be pretty much almost equal to their full salary per month as it seems to be the case here so it seems like everything locked correctly now i realized the last function was quite long and it wasn't particularly easy to do generally if you want to make it a bit easier for yourself you would split it so you would do one if statement first then do a second one somewhere else and so on that way it's a bit easier for you to read what's going on that being said let me know in the comments if you have any other solutions to that function i'd be interested to hear about that also make sure they're on excel and on a different platform which is obviously a little easier to do check out the link over here if you want to find out more about our course or this video over here if you want to learn about excel shortcuts with this other video over here if you want to learn about company evaluation on excel that's all for this video hit the like hit that subscribe and i'll catch you in the next one