Question 1 Calculate using Excel functions the missing values in Table 1 for each client (see...

 

Transcribed Image Text:

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. 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. Table 1 Smith Jones MacDougal Law Gillis Deveaux Zhang Petry Baker Client Table 4 Agent's Commission Jeff Paul Commission Calculations Loan Down Selling Price Term Payment Agent Jeff Paul Jeff Paul Jeff Paul Jeff Paul Jeff $258,900 20 $328,950 15 $198,000 15 30 $178,350 $333,000 30 $768,650 20 $358,000 15 $458,000 20 $168,900 30 TOTAL (S) AVERAGE (S) Amount to be Commission Financed (%) Commission (S) Table 2 Loan Term 15 20 30 % Required Down Pmt 5% 10% 15% Table 3 Commission (%) 1.50% 2.00% 2.50% Trigger (5) 200000 300000 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. 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. Table 1 Smith Jones MacDougal Law Gillis Deveaux Zhang Petry Baker Client Table 4 Agent's Commission Jeff Paul Commission Calculations Loan Down Selling Price Term Payment Agent Jeff Paul Jeff Paul Jeff Paul Jeff Paul Jeff $258,900 20 $328,950 15 $198,000 15 30 $178,350 $333,000 30 $768,650 20 $358,000 15 $458,000 20 $168,900 30 TOTAL (S) AVERAGE (S) Amount to be Commission Financed (%) Commission (S) Table 2 Loan Term 15 20 30 % Required Down Pmt 5% 10% 15% Table 3 Commission (%) 1.50% 2.00% 2.50% Trigger (5) 200000 300000