### Create a worksheet to determine the sale persons commission

Assignment Help Basic Computer Science
##### Reference no: EM13861060

Part 1

In ABC Plumbing, sales have to be greater than or equal to \$100,000 and less than \$200,000 for a salesperson to receive a bonus of 12% on their sales. Create a worksheet to determine the sale persons' commission. Your spreadsheet will look like the following. You must use the "IF" and "AND" function in Excel to calculate the bonus. Save the worksheet and name it ABC Plumbing.

 Bonus Minimum amount \$100,000 12% Maximum amount \$200,000 Sales Bonus Peter O'Toole \$ 87,925 No Bonus Robin B. Williams \$100,000 12% Richard C. Burton \$145,000 12% James F. Gardner \$200,750 No Bonus Mickey J. Rooney \$178,650 12% Richard N. Attenborough \$ 99,555 No Bonus Bob S. hastings \$147,000 12% Casey L. Kasem \$199,000 12% Bob A. Hoskins \$122,680 12% Dane R. Witherspoon \$ 92,500 No Bonus

Part 2

PMT Function

After graduating, you landed a great job in Atlanta, Georgia. You decided to buy a new car to fit your life style. Research the web and pick out a car that you really like. You need to work out the finances for your new purchases. Following are some examples of what a cool car looks like.

Create the following information and format the cells accordingly.

 Cost of your car Down payment Interest Rate Number of Payments Monthly Payment

Use the PMT function to calculate your monthly payment. Save the work sheet as My Car

Goal Seek

After looking over your finances you discovered that you can only afford to make a monthly payment of about a \$1,000. But one of your wealthy uncle has agreed to help out by giving you the down payment to make it possible.

You will use Goal Seek to determine what amount you will need for a down payment in order to obtain a monthly payment of \$1000.00 for the number of months that you have decided to finance your car.

Be sure that I can check your Goal seek in your worksheet. In other words, you need to check your worksheet after you have completed it and check if all select cells in Goal seek is selected.

Memo

Since your wealthy uncle is a very busy businessman he will need you to write a memo telling him how you arrived at the down payment that you will need from him. This means that you will provide him with details as to what the variables are in your goal seek (terms, interest rate payment etc..)

You may use any memo style (Microsoft Word template). To see a list of memo style: In Microsoft Word click on Edit, New, select an appropriate memo format to use.

Part 3

ACME Inc.

ACME Inc. is a wholesaler of widgets both in the UK and USA. The Marketing department would like to motivate their sales team to increase their sales volume. The Director of sales would like you to create a spread sheet to calculate the commission that each sales person earned last year as a bonus to the sales team. Below are the preliminary data.

The business rules are as follows:

1. Each sales person's commission is calculated by comparing their sales with the Average Sales volume that you calculated. If their sales is more than or equal to the average, their commission is 3%, of their sales volume, if it is less than the average, the rate is 2% of their sales volume.

2. The bonus for the year is 5% of their own Sales Volume plus the commission they earned.

3. The Director would like to know how many salesperson sold more than average amount.

4. The Director would like to know what amount of the sales are from the UK and US.

5. How many sale person performed above the average sales volume?

 Business Statistics Sales Person Country Sales Volume (Thousand) CommIsion Rate Bonus (Thousand) John Smith UK 5283.00 3% 5                 22.64 Phung Winsett UK \$139.00 2% \$                  9.73 Glinda Wagner US \$180.00 2% \$                 12.60 Pearle Buttery US 5314.00 3% 5                 25.12 Bettye Dixon UK \$140.00 2% \$                  9.80 Hannelore Bohm UK \$280.00 3% 5                 22.40 Olga Swoboda UK \$297.00 3% \$                 23.76 Florine Hollaway US 5279.00 3% \$                 22.32 Marx Rick US 5294.00 3% \$                 23.52 Rebecka Barnes US \$261.00 3% \$                 20.88 Cheryle Cowherd US \$183.00 2% 5                 12.81 Shwa Lesley US \$260.00 3% \$                 20.80 Merlene Weldy US 5295.00 3% 5                 23.60 Hulda Velga US \$151.00 2% 5                 10.57 Katharyn Blrge US 5203.00 2% \$                 14.21 Randee Erdmann US \$111.00 2% \$                  7.77 Kyoko Welland US \$132.00 2% \$                  9.24 Jona Corbett US \$190.00 2% 5                 13.30 Jonathon Kitchens US 5225.00 3% \$                 18.00 Chante Trexler UK \$189.00 2% \$                 13.23 Jolynn Clasen UK 5307.00 3% \$                 24.56 Average Sales \$224.43 Total Amount UK Business 51,635.00 Total Amount US Business \$3,078.00 How Many sales person above average 11

You MUST use the following functions for the above calculations: AVERAGE, IF, COUNTIF, and SUMIF. No points will be given if this requirement is not satisfied.

Deliverables: The above workbook with the 3 work sheets named appropriately i.e. ACME Inc, My Car, etc.

#### Determine the membership cost

Your program must contain a function that displays the general information about the fitness center and its charges, a function to get all of the necessary information to de

#### Create a class deck that represents a deck of cards

Blackjack (twenty-one) is a casino game played with cards. The goal of the game is to draw cards that total as close to 21 points as possible without going over. All face ca

#### Decrypt the message without computing bob''s private key

We investigate the weaknesses that arise in Elgamal encryption if a public key of small order is used. We look at the following example. Assume Bob uses the group Z∗ 29 with

#### Discuss the impact of the roles on health care organization

Discuss the impact of the roles on the health care organizations. Create a diagram with the career choice as the focal point and identifying the work roles within one of the

#### How much larger would the sample size have to be

Suppose you draw a random sample of size n from a normal distribution with unknown mean μ and known standard deviation σ and construct a 95% confidence interval for μ. If yo

#### Evaluating project performance

"Evaluating Project Performance" Please respond to the following: Suggest activities that can be performed by the project manager in order to evaluate individual performance

#### Explain the function of input controls

Explain the function of input controls. Identify four (4) types of input control and explain the function of each. Provide an example of a data integrity error that could occu

#### How many clock cycles are needed

Indicate data dependency in the code below. Assuming there is no forwarding in the pipeline, howmany clock cycles are needed to finish the program? If full forwarding is use