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.

2176_Cool_Cars.png

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.

Reference no: EM13861060

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

Reviews

Write a Review

 
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd