1 2 3 4 Table 1 Client 5 6 Smith 7 Jones 8 MacDougal 9 Law...

Transcribed Image Text:

1 2 3 4 Table 1 Client 5 6 Smith 7 Jones 8 MacDougal 9 Law 10 Gillis 11 Deveaux 12 Zhang 13 Petry 14 Baker 15 16 17 Table 4 18 19 Jeff 20 Paul 21 B Agent's Commission A Question 1 Calculate using Excel functions the missing values in Table 1 for each client (see green highlighted cells) using Excel functions. Specifically use Excel to assign/calculate the required down payment using the values given in Table 2. Then calculate the amount to be financed and the commission paid to each agent. The commission to be paid to each agent is calculated as follows. They get paid only on the amount to be financed, with the amount being: 2.5% if that amount is greater than $300,000; 2.0% if it is between $200,000 and $300,000; and 1.5% if it is less than $200,000. D Commission Calculations Loan Term $258,900 20 $328,950 15 $198,000 15 $178,350 30 $333,000 30 $768,650 20 $358,000 15 $458,000 20 30 $168,900 Agent Selling Price Jeff Paul Jeff Paul Jeff Paul Jeff Paul Jeff E Then, as above using only Excel functions, calculate the total and average commision amounts for each agent in Table 4 (can use whatever functions you choose, but some useful options are the SUMIF, COUNTIF and/or AVERAGEIF functions). Note: do not hard code values in your formulas - if any numbers/values are required and not given, then create a table for these values for use in the formulas. TOTAL ($) AVERAGE ($) F G Down Payment H Amount to be Commission Commission Financed (%) ($) Table 2 M Loan Term 15 20 30 % Required Down Pmt 5% 10% 15% N Table 3 Commission (%) 1.50% 2.00% 2.50% Trigger ($) 200000 300000 1 2 3 4 Table 1 Client 5 6 Smith 7 Jones 8 MacDougal 9 Law 10 Gillis 11 Deveaux 12 Zhang 13 Petry 14 Baker 15 16 17 Table 4 18 19 Jeff 20 Paul 21 B Agent's Commission A Question 1 Calculate using Excel functions the missing values in Table 1 for each client (see green highlighted cells) using Excel functions. Specifically use Excel to assign/calculate the required down payment using the values given in Table 2. Then calculate the amount to be financed and the commission paid to each agent. The commission to be paid to each agent is calculated as follows. They get paid only on the amount to be financed, with the amount being: 2.5% if that amount is greater than $300,000; 2.0% if it is between $200,000 and $300,000; and 1.5% if it is less than $200,000. D Commission Calculations Loan Term $258,900 20 $328,950 15 $198,000 15 $178,350 30 $333,000 30 $768,650 20 $358,000 15 $458,000 20 30 $168,900 Agent Selling Price Jeff Paul Jeff Paul Jeff Paul Jeff Paul Jeff E Then, as above using only Excel functions, calculate the total and average commision amounts for each agent in Table 4 (can use whatever functions you choose, but some useful options are the SUMIF, COUNTIF and/or AVERAGEIF functions). Note: do not hard code values in your formulas - if any numbers/values are required and not given, then create a table for these values for use in the formulas. TOTAL ($) AVERAGE ($) F G Down Payment H Amount to be Commission Commission Financed (%) ($) Table 2 M Loan Term 15 20 30 % Required Down Pmt 5% 10% 15% N Table 3 Commission (%) 1.50% 2.00% 2.50% Trigger ($) 200000 300000