Part 1 Acme Hat Corporation
1. Create the Acme Hat corporation worksheet below.
2. Add yourself and two more employees to the spreadsheet (include hours worked and wage rate).
3. Fill in the Gross Pay column by using a formula which multiplies the Hours Worked times the Hourly Wage.
4. Fill in the Taxes column by using a formula which calculates 35% of the Gross Pay.
5. Fill in the Net Pay column with a formula which subtracts the Taxes from the Gross Pay.
6. Using the built-in function 'sum', calculate the totals of the last three columns.
7. Format the hour's column to 1 decimal place and all other numbers to currency with 2 decimal places.
8. Sort the employees into alphabetical order.
9. Format the table with a border, title shading, and graphic of a hat, centered horizontally and vertically.
10. Add your name using a header and the date and time as a footer to the spreadsheet.
11. Be sure your name is on the spreadsheet.
12. Preview the file (make sure each spreadsheet fits on one page).
|
Acme Hat Corporation
|
|
Name
|
Hours
|
Wage
|
Gross
|
Taxes
|
Net
|
|
Lachance
|
40.5
|
15.75
|
|
|
|
|
Warner
|
39.5
|
35.5
|
|
|
|
|
McKaig
|
27.75
|
29.85
|
|
|
|
|
Shaw
|
38
|
32
|
|
|
|
|
Brooksbank
|
29
|
31
|
|
|
|
|
Darrach
|
15
|
18.54
|
|
|
|
|
Ruypers
|
14.75
|
15.75
|
|
|
|
|
|
|
Totals:
|
|
|
|
|
Course: Marketing 1O1
Instructor: Dr. Garrott
Period:3
|
|
Student Name
|
ID
|
KB
|
WP
|
SS
|
DB
|
Total
|
Percentage%
|
|
Avery, Adam
|
1203
|
25
|
31
|
19
|
21
|
|
|
|
Chow, Samuel
|
2219
|
10
|
15
|
10
|
14
|
|
|
|
Dible, Liz
|
1721
|
22
|
30
|
20
|
22
|
|
|
|
Dow, Julia
|
1604
|
24
|
29
|
20
|
20
|
|
|
|
Frank, Joe
|
1798
|
26
|
29
|
22
|
28
|
|
|
|
Gill, Mary
|
1115
|
25
|
30
|
20
|
25
|
|
|
|
Ip, Henry
|
1214
|
29
|
32
|
23
|
27
|
|
|
|
Joe, Sarah
|
2021
|
19
|
21
|
18
|
20
|
|
|
|
Low, John
|
2015
|
22
|
21
|
19
|
18
|
|
|
|
Warn, Suzanne
|
1320
|
12
|
12
|
14
|
9
|
|
|
|
|
Average:
|
|
|
|
|
|
|
|
|
Possible Mark (*)
|
30
|
35
|
25
|
30
|
|
|
1. Prepare the following worksheet in the same workbook (worksheet 2), being sure to use appropriate formulas and formats.
2. To calculate the Percentage, divide the Student's Total Mark by the Total Possible Mark. The Total Possible Mark requires an absolute reference (Note that you need to use absolute reference here for the Total Possible Mark) since you must refer to this same cell for each calculation (the darker cell).
3. Format the Percentage column to Percent with no decimal places.
4. Format the Average row to 1 decimal place.
5. Sort the students by Percent (highest to lowest), and then Student Number (lowest to highest).
6. Add a column titled "Pass/Fail". Using the IF command display the word 'Pass' if the percent is greater than or equal to 50, display the word 'Fail' if the percent is less than 50.
7. Format the table with a border, title shading.
8. Add your name using a header and the date and time as a footer to the spreadsheet, save and print.