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