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.