Question 1 Calculate using Excel functions the missing values in Table 1 for each client (see...
Fantastic news! We've Found the answer you've been seeking!
Question:
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
Expert Answer:
Related Book For
Introduction to Corporate Finance What Companies Do
ISBN: 978-1111222284
3rd edition
Authors: John Graham, Scott Smart
Posted Date:
Students also viewed these algorithms questions
-
Calculate a 95% confidence interval for the true life of the battery. Does it capture the true H0: = 300
-
Eligibility criteria to participate in CACFP include for-profit child care programs with _______ of the children enrolled who qualify for free and reduced-price meals. Question 39 options: 10% 25%...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Antonio Banderos & Scarves makes headwear that is very popular in the fall/winter season. Units sold are anticipated as: October .......... 1,250 November ......... 2,250 December ......... 4,500...
-
Felix Madison has $10,000 worth of property damage insurance and a $1,000 deductible collision insurance policy. He had a tire blow- out while driving and crashed into a $1,400 fire hydrant. The...
-
Steam at 450oC and 1 MPa is flowing through a nozzle with a velocity of 300 m/s. Determine (a) The total temperature (T) and (b) Pressure (p) of the flow. Use the PG model for steam.
-
Sauer Energy purchased land in fee for $100,000. The fair market values of the surface and mineral rights were determined by a qualified appraiser as follows: Prepare a journal entry to record the...
-
Joe Schmaltz has carried on a retail business for about 20 years. He intends to transfer the business assets and liabilities to a corporation, Schmaltz Enterprises Ltd. (SEL), in which he will own...
-
4. An item is originally priced at P dollars, but is placed on sale at a discount of 20% off. What is an expression for the final sales price, F (P)? (A) F(P)=80P (B) F(P)=0.20P (C) F(P)=20P (D)...
-
Chumpy Lighting Limited manufactures a wide variety of light bulbs which it sells to lighting shops and builders merchants through wholesale distributors. It also sells direct to the big UK...
-
MoonInc. granted their executives incentive stock options on January 1, 2017. On this date, 275,000 shares of the company's $5 par value common stock were granted at an option price of $40 per share....
-
Oslo Company prepared the following contribution format income statement based on a sales volume of 1,000 units (the relevant range of production is 500 units to 1,500 units): Sales Variable expenses...
-
Choose TWO of the following questions to answer. Expand upon the questions and incorporate life experiences where possible. What is the monetary contribution of the poultry industry to the U.S....
-
(a) State one possible project topic of interest to you, being careful to state it in the form of a question about a causal effect (as described in the project description document and in class). :...
-
What is the purpose of using the BSC in the IT strategic planning process? How does the BSC approach differ from previous measurement approaches? What are the four BSC perspectives?
-
Unveiling the Workforce Dynamics: A Comprehensive Analysis of Mining and Labor Economics in Zambia's Copperbelt Region I. Introduction A. Background 1. Overview of Zambia's Copperbelt Region The...
-
A partial trial balance of Carla Corporation is as follows on December 31, 2021. Debit bal: Debit balance supplies 13500 interest receivable on investment 25500 Prepaid Insurance 450000 Unearned Re...
-
Define relevant costs and discuss: (1) whether all future costs are relevant for decision making and (2) whether variable costs are always relevant and fixed costs are always irrelevant
-
What are the potential faults in using the IRR as a capital budgeting technique? Given these faults, why is this technique so popular among corporate managers?
-
What is meant by early-stage and later-stage venture capital investment? What proportions of venture capital have been allocated between the two in recent years? Which stage requires a higher...
-
Define staged financing. Why is this an efficient risk-minimizing mechanism for venture capitalists?
-
Why is it necessary for financial reporting to be subject to (a) mandatory control and (b) statutory control?
-
How is it possible to make shareholders aware of the significance of the exercise of judgement by directors which can turn profits of 6m into losses of 2m?
-
The effective working of the financial aspects of a market economy rests on the validity of the underlying premises of integrity in the conduct of business and reliability in the provision of...
Study smarter with the SolutionInn App