Today, I am following up on last week’s excel quiz where I challenged all of you to take part in a little test of your understanding of some key excel functions, you can do it here if you have not done so.
Step #1-Download this spreadsheet
To remind you, here are the steps with the answers! This is what the spreadsheet looked like to start off:
Step #2-Using one of the functions described, get the correct number in column “E”. It should be one formula that you can then drag down that will use the correct %. Then, using the chart at the bottom left and the same function, fill out column “F”
=VLOOKUP(D4,$A$15:$B$17,2,FALSE)
=C4*VLOOKUP(D4,$A$14:$C$17,3,FALSE)
Step #3-Calculate the amount to be financed (Selling price – down payment) in column G
=C4-F4
Step #4-Fill out the commission to be paid out to each agent. It should be calculated as follows. They get paid only on the amount to be financed. They actually get paid:
2.5% if that amount is over or equal to $200,000
1.5% if it is lower than $200,000
=IF(G4>200000,F4*0.025,F4*0.015)
Step #5-Using one of the formulas, calculate the correct commission for each seller in cells B22-23
=SUMIF($B$4:$B$12,A22,$H$4:$H$12)
Step #6-Using another one of the formulas, count how many sells each agent made in C22-23
=COUNTIF($B$4:$B$12,A22)
Step #7-The branch has an extra bonus where the bank manager will receive $10,000 for any loan that is over $250,000 with a loan term (amount to be financed) of 20 years or more AND an interest rate of 5% or more. Please enter a formula in I4 that you can drag that will give you the bonus amount for each loan.
=IF(G4>250000,IF(AND(D4>=20,E4>=0.05),10000,0),0)
That’s it:) Here is what the answer looks like:
You can also download the answer here:)
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
Adrian
In the commission file the formula is wrong.
It should read:
=IF(G4>200000,G4*0.025,G4*0.015)
Otherwise they will quit from sales.
Joseph
Simplier IF function:
=IF(AND(G4>250000;E4>=5%;D4>=20);10000;0)
serg
Thank’s for the exercise!!
S
Yes the formula for commission is wrong. It should be IF(G4>=200000,2.5%*G4,1.5%*G4)
Why? Because
– They get paid only on the amount to be financed (which is column G and not F)
– 2.5% if that amount is over or equal to $200,000 (so equal to sign is required)
fjr advisors llc
That is really attention-grabbing, You’re an overly skilled blogger. I have joined your feed and look ahead to searching for extra of your magnificent post. Additionally, I’ve shared your website
in my social networks
ritika arora
excellent exercise!!!
Sarah
Great exercise- best I’ve found so far! Thanks
salahudheen
Can I have the question for this pls? I dont understand the commission calculation formula (sumif).