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.