Today, after helping out a reader, it got me thinking about a new feature that could be added to this blog, excel exercise or tests. I will be giving one such exercise today and then publishing the answer next week. Obviously, readers that come here are at all kinds of different levels so it would be a challenge to have an exercise that would work for everyone. This is an exercise that tests your knowledge of the vlookup, sumif and countif functions as well as a special question that will require you to use a nested if & and function.
I would of course invite you to write comments and questions in the comments section of this post. However, please refrain from posting the entire solution:) If you do have it, simply say so, I will communicate with you to get the file by email:)
Ready for the challenge? If so, start here:
Step #1-Download this spreadsheet
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”
Step #3-Calculate the amount to be financed (Selling price – down payment) in column G
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
Step #5-Using one of the formulas, calculate the correct commission for each seller in cells B22-23
Step #6-Using another one of the formulas, count how many sells each agent made in C22-23
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.
That’s it:) Best of luck:) The answer will be published one week from now:)
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
yasser
Hi can i have the answers for this test please to ensure that I have done it correctly
thank you
manish bharti
please send the answer of above question
buggiesandprams.com
Simply want to say your articdle is ass surprising.
The clarity inn your post is simply nice and i could assume
you are an expert on this subject. Well with your permission allow me tto grab your feed to keep up to date with forthcoming post.
Thanks a milllion and please keep up the rewarding work.
Anita
Please send me the solution. Thanks!
Emma
Please send me the answer to this bank spreadsheet. Thanks!
Buckhead
2.
Part 1
=VLOOKUP(D4,$A$15:$C$17,2,FALSE)
Part 2
=VLOOKUP(A4,$A$4:$D$12,3,FALSE)*(VLOOKUP(D4,$A$15:$C$17,3,FALSE))
3.
=C4-F4
4.
=IF(G4>=200000,G4*0.025,IF(G4250000,E4>=0.05)),10000,0)
Buckhead
I posted the answers and it wouldn’t show all of the answers. The answer you see for 4. is actually for is actually for the final question with some parts omitted. Oh well I tried
no wei
Step #5-Using one of the formulas, calculate the correct commission for each seller in cells B22-23
HOW??
Talal
Step #5-Using one of the formulas, calculate the correct commission for each seller in cells B22-23
Answer: =SUMIF($B$4:$H$12,A22,$H$4:$H$12)
uday
please post ans
uday
pls post ans
basu
Please send me the answer to this bank spreadsheet. Thanks!
Rory Bellow
Please send the answers! Thanks!
Anonymous Bob
STEP 6
=COUNTIF($B$4:$G$12,A22)
STEP 7
=IF(AND(G4>250000,D4>=20,E4>=0.05),10000,0)
Rashida Islam
Hey, could you please post an answer to the final question? Thank you!
Sandilya
Hey Could you please let me know how to do the step 7
Miss Naw Poe Lu
Now I have the test file with the answers myself.
Richard White
Can I have the answers?
Can I have the answers?
Can I have the answers?
Jeez, can’t you flock of stupid sheep find anything else to say?
Rahul Gupta
Step # 2 vlookup(D4,$A$14:$C$17,2,FALSE)
Step # 2 vlookup(D4,$A$14:$C$17,3,FALSE)
Step # 3 C4-F4*C4
Step # 4 if(G4250000,E4>=5%,D4>=20),10000,””)
Rahul Gupta
Step # 5 Sumif($B$4:$B$12,A22,$H$4:$H$12)
Step # 6 Sumif($B$4:$B$12,A22)
Step # 7 if(And(G4>250000,E4>=5%,D4>=20),10000,””)
Rahul Gupta
Step # 4 if(G4<200000,1.5%*G4,2.5%*G4)
Corrected
Rahul Sharma
Bank Example
Customer Seller Agent Selling Price Loan Term Interest Rate Down Payment Amount to be Financed Commission Bonus
Sterfield Allan $2,58,900 30 5.25% 25.00% $1,94,175 $2,913 No Bonus
Sceria John $3,28,950 15 4.50% 15.00% $2,79,608 $6,990 No Bonus
Subensklee Allan $1,98,000 15 4.50% 15.00% $1,68,300 $2,525 No Bonus
Sritzen John $1,78,350 30 5.25% 25.00% $1,33,763 $2,006 No Bonus
Sango Allan $3,33,000 30 5.25% 25.00% $2,49,750 $6,244 No Bonus
Satt John $7,68,650 20 5.00% 20.00% $6,14,920 $15,373 $10,000
Sro Allan $3,58,000 15 4.50% 15.00% $3,04,300 $7,608 No Bonus
Sweitzer John $4,58,000 20 5.00% 20.00% $3,66,400 $9,160 $10,000
Ster Allan $1,68,900 30 5.25% 25.00% $1,26,675 $1,900 No Bonus
$54,718
Loan Term Interest Rate % Required Down Pmt
15 4.50% 15%
20 5.00% 20%
30 5.25% 25%
Sellers Commission $ #
John $33,529.63 4
Allan $21,188.50 5
$54,718.13